migration 10.0.6 => 11.0.2 - Row size too large

[WhilelM]

Bug

Tried to upgrade dolibarr from deb 10.0.6 to 11.0.2

Error DB_ERROR_1118: ALTER TABLE llx_mailing ADD COLUMN tms timestamp;
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

[WhilelM]

Corrected with "ALTER TABLE llx_mailing ROW_FORMAT=DYNAMIC;"

[eldy]

What is your database and version ?

[WhilelM]

mariadb-server 10.3 (buster version 1:10.3.22-0+deb10u1)

[eldy]

Hum strange.
Do you succeed in creating a table manually like this

create table llx_mailing_test_to_delete_after
(
rowid integer AUTO_INCREMENT PRIMARY KEY,
statut smallint DEFAULT 0, --
titre varchar(128), -- Ref of mailing
entity integer DEFAULT 1 NOT NULL, -- multi company id
sujet varchar(128), -- Sujet of mailing
body mediumtext,
bgcolor varchar(8), -- Backgroud color of mailing
bgimage varchar(255), -- Backgroud image of mailing
cible varchar(60),
nbemail integer,
email_from varchar(160), -- Email address of sender
email_replyto varchar(160), -- Email address for reply
email_errorsto varchar(160), -- Email addresse for errors
tag varchar(128) NULL,
date_creat datetime, -- creation date
date_valid datetime, --
date_appro datetime, --
date_envoi datetime, -- date d'envoi
tms timestamp,
fk_user_creat integer, -- user creator
fk_user_valid integer, -- user validator
fk_user_appro integer, -- not used
extraparams varchar(255), -- for stock other parameters with json format
joined_file1 varchar(255),
joined_file2 varchar(255),
joined_file3 varchar(255),
joined_file4 varchar(255)
)ENGINE=innodb;

Can you provide the structure of your current llx_mailing table ("DESC llx_mailing from mysql") ?

[WhilelM]

create table [...] :
Query OK, 0 rows affected (0.202 sec)

MariaDB [dolibarrdebian]> DESC llx_mailing;
+----------------+--------------+------+-----+---------------------+-------------------------------+
| Field          | Type         | Null | Key | Default             | Extra                         |
+----------------+--------------+------+-----+---------------------+-------------------------------+
| rowid          | int(11)      | NO   | PRI | NULL                | auto_increment                |
| statut         | smallint(6)  | YES  |     | 0                   |                               |
| titre          | varchar(128) | YES  |     | NULL                |                               |
| entity         | int(11)      | NO   |     | 1                   |                               |
| sujet          | varchar(128) | YES  |     | NULL                |                               |
| body           | mediumtext   | YES  |     | NULL                |                               |
| bgcolor        | varchar(8)   | YES  |     | NULL                |                               |
| bgimage        | varchar(255) | YES  |     | NULL                |                               |
| cible          | varchar(60)  | YES  |     | NULL                |                               |
| nbemail        | int(11)      | YES  |     | NULL                |                               |
| email_from     | varchar(160) | YES  |     | NULL                |                               |
| email_replyto  | varchar(160) | YES  |     | NULL                |                               |
| email_errorsto | varchar(160) | YES  |     | NULL                |                               |
| tag            | varchar(128) | YES  |     | NULL                |                               |
| date_creat     | datetime     | YES  |     | NULL                |                               |
| date_valid     | datetime     | YES  |     | NULL                |                               |
| date_appro     | datetime     | YES  |     | NULL                |                               |
| date_envoi     | datetime     | YES  |     | NULL                |                               |
| fk_user_creat  | int(11)      | YES  |     | NULL                |                               |
| fk_user_valid  | int(11)      | YES  |     | NULL                |                               |
| fk_user_appro  | int(11)      | YES  |     | NULL                |                               |
| extraparams    | varchar(255) | YES  |     | NULL                |                               |
| joined_file1   | varchar(255) | YES  |     | NULL                |                               |
| joined_file2   | varchar(255) | YES  |     | NULL                |                               |
| joined_file3   | varchar(255) | YES  |     | NULL                |                               |
| joined_file4   | varchar(255) | YES  |     | NULL                |                               |
| tms            | timestamp    | NO   |     | current_timestamp() | on update current_timestamp() |
+----------------+--------------+------+-----+---------------------+-------------------------------+
27 rows in set (0.001 sec)

[eldy]

What is your value of innodb_default_row_format in your database ?
By default, it is "dynamic" so this is why the "ROW_FORMAT=DYNAMIC;" is not set anywhere (but we need it everywhere in fact)

[WhilelM]

MariaDB [(none)]> SELECT @@GLOBAL.innodb_default_row_format;
+------------------------------------+
| @@GLOBAL.innodb_default_row_format |
+------------------------------------+
| dynamic                            |
+------------------------------------+
1 row in set (0.001 sec)

[oas-services]

Hi Laurent,
i have the same issue... On my server, debian was migrated from 9 to 10 and PHP from 7.3 to 7.4. Don"t know about mariadb prior version.
default row format is dynamic too.
Olivier (Aplose - OAS)

[oas-services]

workaround : alter table llx_mailing ROW_FORMAT=DYNAMIC;

note : even if you make a global alter on all tables of all databases you have, you need to make this alter again for each Dolibarr... strange...

note bis : global alter done :
USE INFORMATION_SCHEMA; SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA,".", TABLE_NAME, " ROW_FORMAT=DYNAMIC;") AS MySQLCMD FROM TABLES WHERE ENGINE='innodb' AND ROW_FORMAT != 'DYNAMIC' AND ROW_FORMAT !='COMPRESSED';

[seblu]

Got similar issue with another table llx_actioncomm when migrating from 12.0.4 to 13.0.0 or 13.0.1.

Request 119 sql='ALTER TABLE llx_actioncomm ADD COLUMN reply_to varchar(255);'
Erreur DB_ERROR_1118: ALTER TABLE llx_actioncomm ADD COLUMN reply_to varchar(255);
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
Request 120 sql='ALTER TABLE llx_paiement ADD pos_change DOUBLE(24,8) DEFAULT 0 AFTER fk_export_compta;'

Table llx_actioncomm is empty. mardiadb is in version 10.3.
Using the alter table workaround allow upgrade script to complete.

[javieralapps4up]

New instalation Wampserver 3.2.5
Update to mariadb 10.6.3 (latest and all options default)
New instalation Dolibarr 14

Error DB_ERROR_1118: xxxxx when CREATE TABLE in:

llx_actioncomm
llx_adherent
llx_commande
llx_cronjob
llx_ecm_files
llx_facture
llx_facture_fourn
llx_mailing
llx_societe
llx_societe_rib
llx_socpeople
llx_user
llx_website_page

I changed ENGINE=innodb;
to: ENGINE=innodb ROW_FORMAT=DYNAMIC; in all table files

Then, all was ok

[github-actions]

This issue is stale because it has been open 1 year with no activity. If this is a bug, please comment to confirm it is still present on latest stable version. if this is a feature request, please comment to notify the request is still relevant and not yet covered by latest stable version. This issue may be closed automatically by stale bot in 10 days (you should still be able to re-open it if required).