XF 2.2 Error when installing an AddOn

[Ivo2]

I tried to install an addon "Easy User ban" from Siropu, but following Server-Errors:

XF\Db\InvalidQueryException: xf_user: MySQL query error [1118]: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs src/XF/Db/AbstractStatement.php:230

Stack-Trace:

ALTER TABLE xf_user
ADD siropu_easy_user_ban_count INT UNSIGNED NOT NULL DEFAULT '0',
ADD siropu_easy_user_ban_forum BLOB NOT NULL,
ADD siropu_easy_user_ban_thread BLOB NOT NULL

#0 src/XF/Db/Mysqli/Statement.php(198): XF\Db\AbstractStatement->getException('MySQL query err...', 1118, '42000')
#1 src/XF/Db/Mysqli/Statement.php(79): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1118, '42000')
#2 src/XF/Db/AbstractAdapter.php(96): XF\Db\Mysqli\Statement->execute()
#3 src/XF/Db/Schema/AbstractDdl.php(158): XF\Db\AbstractAdapter->query('ALTER TABLE `xf...')
#4 src/XF/Db/SchemaManager.php(154): XF\Db\Schema\AbstractDdl->apply()
#5 src/addons/Siropu/EasyUserBan/Setup.php(25): XF\Db\SchemaManager->alterTable('xf_user', Object(Closure))
#6 src/XF/AddOn/StepRunnerInstallTrait.php(62): Siropu\EasyUserBan\Setup->installStep1(Array)
#7 src/XF/AddOn/StepRunnerInstallTrait.php(29): Siropu\EasyUserBan\Setup->installStepRunner(1, Array)
#8 src/XF/Admin/Controller/AddOn.php(419): Siropu\EasyUserBan\Setup->install(Array)
#9 src/XF/Mvc/Dispatcher.php(352): XF\Admin\Controller\AddOn->actionInstall(Object(XF\Mvc\ParameterBag))
#10 src/XF/Mvc/Dispatcher.php(259): XF\Mvc\Dispatcher->dispatchClass('XF:AddOn', 'Install', Object(XF\Mvc\RouteMatch), Object(XF\Admin\Controller\AddOn), NULL)
#11 src/XF/Mvc/Dispatcher.php(115): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(XF\Admin\Controller\AddOn), NULL)
#12 src/XF/Mvc/Dispatcher.php(57): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#13 src/XF/App.php(2487): XF\Mvc\Dispatcher->run()
#14 src/XF.php(524): XF\App->run()
#15 admin.php(13): XF::runApp('XF\\Admin\\App')
#16 {main}

array(4) {
  ["url"] => string(45) "/admin.php?add-ons/Siropu-EasyUserBan/install"
  ["referrer"] => string(69) "https://balkanforum.info/admin.php?add-ons/Siropu-EasyUserBan/install"
  ["_GET"] => array(1) {
    ["add-ons/Siropu-EasyUserBan/install"] => string(0) ""
  }
  ["_POST"] => array(7) {
    ["_xfProcessing"] => string(1) "1"
    ["continue"] => string(1) "1"
    ["confirm"] => string(1) "1"
    ["params"] => string(2) "[]"
    ["count"] => string(1) "1"
    ["finished"] => string(1) "0"
    ["_xfToken"] => string(8) "********"
  }
}

many thanks in advance for your help.

Answer from Siropu:

I don't think the problem is my add-on.
You might need to make some changes to the xf_user table.

KR

 

[TPerry]

This does sound more like a hosting issue... are you on shared hosting by chance?

 

[Ivo2]

I use a virtual server

 

[Ivo2]

Hi @Kirby
have you an idea?

 

[Jeremy P]

This occurs when the new table structure would allow rows which exceed the page size. What is the output of:

SHOW CREATE TABLE xf_user;

If the table was originally created on an older version of MySQL, it might use a legacy row format. Replacing xf_database_name with your database name, what is the output of:

SELECT row_format
FROM information_schema.tables
WHERE table_schema = 'xf_database_name' AND table_name = 'xf_user'
LIMIT 1;

 

[Ivo2]

Good morning Jeremy

This occurs when the new table structure would allow rows which exceed the page size. What is the output of:

SHOW CREATE TABLE xf_user;

SHOW CREATE TABLE xf_user;
xf_user CREATE TABLE xf_user (
user_id int(10) unsig...

If the table was originally created on an older version of MySQL, it might use a legacy row format. What is the output of (replacing xf_database_name with your database name):

SELECT row_format
FROM information_schema.tables
WHERE table_schema='xf_database_name'
AND table_name='xf_user'
LIMIT 1;

row_format
compact

 

[Jeremy P]

SHOW CREATE TABLE xf_user;
xf_user CREATE TABLE xf_user (
user_id int(10) unsig...

I would need to see the full output to see if there's any troublesome non-default columns.

row_format
compact

What MySQL version are you using? The default row format is DYNAMIC as of MySQL 5.7/MariaDB 10.2.2, but the row format of tables created on older versions is not updated automatically.

 

[Ivo2]

I would need to see the full output to see if there's any troublesome non-default columns.

CREATE TABLE xf_user ( user_id int(10) unsigned NOT NULL AUTO_INCREMENT, username varchar(50) NOT NULL, username_date int(10) unsigned NOT NULL DEFAULT 0, username_date_visible int(10) unsigned NOT NULL DEFAULT 0, email varchar(120) NOT NULL, custom_title varchar(50) NOT NULL DEFAULT '', language_id int(10) unsigned NOT NULL, style_id int(10) unsigned NOT NULL COMMENT '0 = use system default', timezone varchar(50) NOT NULL COMMENT 'Example: ''Europe/London''', visible tinyint(3) unsigned NOT NULL DEFAULT 1 COMMENT 'Show browsing activity to others', activity_visible tinyint(3) unsigned NOT NULL DEFAULT 1, user_group_id int(10) unsigned NOT NULL, secondary_group_ids varbinary(255) NOT NULL, display_style_group_id int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'User group ID that provides user styling', permission_combination_id int(10) unsigned NOT NULL, message_count int(10) unsigned NOT NULL DEFAULT 0, question_solution_count int(10) unsigned NOT NULL DEFAULT 0, conversations_unread smallint(5) unsigned NOT NULL DEFAULT 0, register_date int(10) unsigned NOT NULL DEFAULT 0, last_activity int(10) unsigned NOT NULL DEFAULT 0, last_summary_email_date int(10) unsigned DEFAULT NULL, trophy_points int(10) unsigned NOT NULL DEFAULT 0, alerts_unviewed smallint(5) unsigned NOT NULL DEFAULT 0, alerts_unread smallint(5) unsigned NOT NULL DEFAULT 0, avatar_date int(10) unsigned NOT NULL DEFAULT 0, avatar_width smallint(5) unsigned NOT NULL DEFAULT 0, avatar_height smallint(5) unsigned NOT NULL DEFAULT 0, avatar_highdpi tinyint(3) unsigned NOT NULL DEFAULT 0, gravatar varchar(120) NOT NULL DEFAULT '' COMMENT 'If specified, this is an email address corresponding to the user''s ''Gravatar''', user_state enum('valid','email_confirm','email_confirm_edit','moderated','email_bounce','rejected','disabled') NOT NULL DEFAULT 'valid', security_lock enum('','change','reset') NOT NULL DEFAULT '', is_moderator tinyint(3) unsigned NOT NULL DEFAULT 0, is_admin tinyint(3) unsigned NOT NULL DEFAULT 0, is_banned tinyint(3) unsigned NOT NULL DEFAULT 0, reaction_score int(11) NOT NULL DEFAULT 0, warning_points int(10) unsigned NOT NULL DEFAULT 0, is_staff tinyint(3) unsigned NOT NULL DEFAULT 0, secret_key varbinary(32) NOT NULL, privacy_policy_accepted int(10) unsigned NOT NULL DEFAULT 0, terms_accepted int(10) unsigned NOT NULL DEFAULT 0, xfmg_album_count int(10) unsigned NOT NULL DEFAULT 0, xfmg_media_count int(10) unsigned NOT NULL DEFAULT 0, xfmg_media_quota int(10) unsigned NOT NULL DEFAULT 0, siropu_chat_room_id int(10) unsigned NOT NULL DEFAULT 1, siropu_chat_conv_id int(10) unsigned NOT NULL DEFAULT 0, siropu_chat_rooms blob DEFAULT NULL, siropu_chat_conversations blob DEFAULT NULL, siropu_chat_settings blob DEFAULT NULL, siropu_chat_room_join_time blob DEFAULT NULL, siropu_chat_status varchar(255) NOT NULL DEFAULT '', siropu_chat_is_sanctioned tinyint(3) unsigned NOT NULL DEFAULT 0, siropu_chat_message_count int(10) unsigned NOT NULL DEFAULT 0, siropu_chat_last_activity int(11) NOT NULL DEFAULT -1, andy_most_reactions_count int(10) unsigned NOT NULL DEFAULT 0, andy_most_posts_count int(10) unsigned NOT NULL DEFAULT 0, sv_sent_reactions blob DEFAULT NULL, sv_reactions blob DEFAULT NULL, vote_score int(11) NOT NULL DEFAULT 0, andy_country_flag_select varchar(50) NOT NULL DEFAULT '', andy_cover_photo int(10) unsigned NOT NULL DEFAULT 0, xc_pv_profile_view_count int(10) unsigned NOT NULL DEFAULT 0, th_unco_user_name_data blob DEFAULT NULL, ozzmodz_gui_is_ignored tinyint(3) unsigned NOT NULL DEFAULT 0, PRIMARY KEY (user_id), UNIQUE KEY username (username), KEY email (email), KEY user_state (user_state), KEY last_activity (last_activity), KEY message_count (message_count), KEY trophy_points (trophy_points), KEY reaction_score (reaction_score), KEY register_date (register_date), KEY staff_username (is_staff,username), KEY xengallery_album_count (xfmg_album_count), KEY xengallery_media_count (xfmg_media_count), KEY siropu_chat_room_id (siropu_chat_room_id), KEY siropu_chat_message_count (siropu_chat_message_count), KEY siropu_chat_last_activity (siropu_chat_last_activity), KEY vote_score (vote_score), KEY last_summary_email_date (last_summary_email_date), KEY permission_combination_id (permission_combination_id), KEY question_solution_count (question_solution_count)) ENGINE=InnoDB AUTO_INCREMENT=33710 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

What MySQL version are you using? The default row format is DYNAMIC as of MySQL 5.7/MariaDB 10.2.2, but the row format of tables created on older versions is not updated automatically.

10.3.39 (10.3.39-MariaDB-0+deb10u1)

 

[Jeremy P]

You might try converting the row format, which should be able to store more data on the overflow pages. As always with manual database operations, you should backup your database and check that the backup works beforehand. It is probably best to run this in a shell rather than a web interface too, if possible.

Spoiler: I have backed up my database or do not care if I break my forum beyond repair

ALTER TABLE xf_user ROW_FORMAT=DYNAMIC;

 

[Ivo2]

You might try converting the row format, which should be able to store more data on the overflow pages. As always with manual database operations, you should backup your database and check that the backup works beforehand. It is probably best to run this in a shell rather than a web interface too, if possible.

Spoiler: I have backed up my database or do not care if I break my forum beyond repair

ALTER TABLE xf_user ROW_FORMAT=DYNAMIC;

Thanks, that was it and as I can say no, no problems