Row size too large depends on history of table

[ausi]

We once tried to tackle the 1118 (42000): Row size too large issue in #4179 but it seems that this error still occurs quite often.

Today I was able to reproduce this on a MariaDB 10.6.16 server and a MariaDB 10.6.15 from a different hosting company using the following SQL code:

DROP TABLE IF EXISTS test_row_size;
CREATE TABLE test_row_size (`1` binary(255) NULL, `2` binary(255) NULL, `3` binary(255) NULL, `4` binary(255) NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

ALTER TABLE test_row_size ADD `5` binary(255) NULL, ADD `6` binary(255) NULL, ADD `7` binary(255) NULL, ADD `8` binary(255) NULL;
ALTER TABLE test_row_size DROP `5`, DROP `6`, DROP `7`, DROP `8`;
ALTER TABLE test_row_size ADD `5` binary(255) NULL, ADD `6` binary(255) NULL, ADD `7` binary(255) NULL, ADD `8` binary(255) NULL;
ALTER TABLE test_row_size DROP `5`, DROP `6`, DROP `7`, DROP `8`;
ALTER TABLE test_row_size ADD `5` binary(255) NULL, ADD `6` binary(255) NULL, ADD `7` binary(255) NULL, ADD `8` binary(255) NULL;
ALTER TABLE test_row_size DROP `5`, DROP `6`, DROP `7`, DROP `8`;
ALTER TABLE test_row_size ADD `5` binary(255) NULL, ADD `6` binary(255) NULL, ADD `7` binary(255) NULL, ADD `8` binary(255) NULL;
ALTER TABLE test_row_size DROP `5`, DROP `6`, DROP `7`, DROP `8`;
ALTER TABLE test_row_size ADD `5` binary(255) NULL, ADD `6` binary(255) NULL, ADD `7` binary(255) NULL, ADD `8` binary(255) NULL;
ALTER TABLE test_row_size DROP `5`, DROP `6`, DROP `7`, DROP `8`;
ALTER TABLE test_row_size ADD `5` binary(255) NULL, ADD `6` binary(255) NULL, ADD `7` binary(255) NULL, ADD `8` binary(255) NULL;
ALTER TABLE test_row_size DROP `5`, DROP `6`, DROP `7`, DROP `8`;
ALTER TABLE test_row_size ADD `5` binary(255) NULL, ADD `6` binary(255) NULL, ADD `7` binary(255) NULL, ADD `8` binary(255) NULL;
ALTER TABLE test_row_size DROP `5`, DROP `6`, DROP `7`, DROP `8`;
ALTER TABLE test_row_size ADD `5` binary(255) NULL, ADD `6` binary(255) NULL, ADD `7` binary(255) NULL, ADD `8` binary(255) NULL;
ALTER TABLE test_row_size DROP `5`, DROP `6`, DROP `7`, DROP `8`;
ALTER TABLE test_row_size ADD `5` binary(255) NULL, ADD `6` binary(255) NULL, ADD `7` binary(255) NULL, ADD `8` binary(255) NULL;

DROP TABLE IF EXISTS test_row_size;

It looks like the row size not only includes the currently existing columns but also all removed columns from the past.

A possible fix could be calling ALTER TABLE test_row_size FORCE or OPTIMIZE TABLE test_row_size. At least it fixed it in my test case:

DROP TABLE IF EXISTS test_row_size;
CREATE TABLE test_row_size (`1` binary(255) NULL, `2` binary(255) NULL, `3` binary(255) NULL, `4` binary(255) NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

ALTER TABLE test_row_size ADD `5` binary(255) NULL, ADD `6` binary(255) NULL, ADD `7` binary(255) NULL, ADD `8` binary(255) NULL;
ALTER TABLE test_row_size DROP `5`, DROP `6`, DROP `7`, DROP `8`;
ALTER TABLE test_row_size ADD `5` binary(255) NULL, ADD `6` binary(255) NULL, ADD `7` binary(255) NULL, ADD `8` binary(255) NULL;
ALTER TABLE test_row_size DROP `5`, DROP `6`, DROP `7`, DROP `8`;
ALTER TABLE test_row_size ADD `5` binary(255) NULL, ADD `6` binary(255) NULL, ADD `7` binary(255) NULL, ADD `8` binary(255) NULL;
ALTER TABLE test_row_size DROP `5`, DROP `6`, DROP `7`, DROP `8`;
ALTER TABLE test_row_size ADD `5` binary(255) NULL, ADD `6` binary(255) NULL, ADD `7` binary(255) NULL, ADD `8` binary(255) NULL;
ALTER TABLE test_row_size DROP `5`, DROP `6`, DROP `7`, DROP `8`;

OPTIMIZE TABLE test_row_size;

ALTER TABLE test_row_size ADD `5` binary(255) NULL, ADD `6` binary(255) NULL, ADD `7` binary(255) NULL, ADD `8` binary(255) NULL;
ALTER TABLE test_row_size DROP `5`, DROP `6`, DROP `7`, DROP `8`;
ALTER TABLE test_row_size ADD `5` binary(255) NULL, ADD `6` binary(255) NULL, ADD `7` binary(255) NULL, ADD `8` binary(255) NULL;
ALTER TABLE test_row_size DROP `5`, DROP `6`, DROP `7`, DROP `8`;
ALTER TABLE test_row_size ADD `5` binary(255) NULL, ADD `6` binary(255) NULL, ADD `7` binary(255) NULL, ADD `8` binary(255) NULL;
ALTER TABLE test_row_size DROP `5`, DROP `6`, DROP `7`, DROP `8`;
ALTER TABLE test_row_size ADD `5` binary(255) NULL, ADD `6` binary(255) NULL, ADD `7` binary(255) NULL, ADD `8` binary(255) NULL;
ALTER TABLE test_row_size DROP `5`, DROP `6`, DROP `7`, DROP `8`;
ALTER TABLE test_row_size ADD `5` binary(255) NULL, ADD `6` binary(255) NULL, ADD `7` binary(255) NULL, ADD `8` binary(255) NULL;

DROP TABLE IF EXISTS test_row_size;

Can someone reproduce this? (First script failing, second script running successfully)

If so, please report the version of your SQL server.

[leofeyer]

I could not reproduce it. Tested with MySQL 8.2.0 and MariaDB 10.5.21.

[ausi]

It looks like all MariaDB versions are affected by this since version 10.4 where Instant DROP COLUMN was introduced.
(Tested versions 10.3.36, 10.4.26, 10.5.17, 10.6.9, 10.7.5, 10.7.8, 10.8.4, 10.9.2)

MySQL does not seem to be affected.
(Tested versions 5.5.62, 5.6.51, 5.7.38, 5.7.39, 8.0.27, 8.0.30, 8.0.32, 8.0.33)

I could not reproduce it. Tested with MySQL 8.2.0 and MariaDB 10.5.21.

Can you please check the value of SELECT @@innodb_strict_mode; in your tested MariaDB? And set it to 1 if it is 0?
And also innodb_instant_alter_column_allowed and alter_algorithm.

[ausi]

According to MariaDB this is not considered to be a bug: https://jira.mariadb.org/browse/MDEV-28760

[ausi]

Using SET GLOBAL innodb_instant_alter_column_allowed=add_last; or SET SESSION alter_algorithm=COPY; would also fix this issue.

However we probably only want interfere with this if we absolutely have to, so we need to catch 1118 (42000): Row size too large errors, try to fix the table (OPTIMIZE TABLE …) and reapply the statement again afterwards.

[fritzmg]

I was able to reproduce it with my local MariaDB 11.2.2 server. First script causes the error, second script works with the following result:

             Table        Op  Msg_type                                                           Msg_text  
------------------  --------  --------  -------------------------------------------------------------------
test.test_row_size  optimize      note    Table does not support optimize, doing recreate + analyze instead
test.test_row_size  optimize    status                                                                   OK

[Toflar]

The way I read https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html that running OPTIMIZE TABLE from time to time makes sense anyway? Would it take too much time to simply just always run it?

[ausi]

Would it take too much time to simply just always run it?

I think so, yes. In some cases this does a full copy of the table, so always executing it is not a good solution IMO.

[fritzmg]

Would it make sense to introduce a contao:optimize-tables command (regardless of whether we automatically optimize specific tables on error)?

[ausi]

Would it make sense to introduce a contao:optimize-tables command

Not sure if this is needed. You could just use doctrine:query:sql 'OPTIMIZE TABLE tl_module' for example.

[fritzmg]

Yes, though I was thinking more in the line of automatically optimising all tables of the database.

[Toflar]

I still do think doing it in contao:migrate from time to time would be okay. Something like if (0 === random_int(0, 10)) or so. But that's out of scope for this problem here.

[aschempp]

I also think this could be done in contao:migrate, but not sure to what interval. Maybe always optimize a table if it was adjusted? Does not really make sense for contao:migrate to remember this was done e.g. a month ago and offer it again (especially since this would not be skippable…).

[aschempp]

Thinking again, if this really should be done e.g. once a month, shouldn't it just be a cron job?

[fritzmg]

Cronjob makes sense to me (if we are sure that this cannot break anything). The cronjob can then also be used to directly be executed on the command line, if you need to.

[ausi]

…if this really should be done e.g. once a month…
…if we are sure that this cannot break anything…

I think it’s too dangerous to do that automatically for every table in a cron job. For large tables this might take some time to complete which could result in downtimes. If the table is very large and there is not enough disk space available it can end up in a disaster (according to some reports on the internet).