zp_images table is too wide for InnoDB strict mode

[kyl191]

I'm restoring a database export to a standard AWS RDS instance. The restore fails with

#1118 - Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

The actual error is in the database logs:

[ERROR] InnoDB: Cannot add field `VideoDataformat` in table `zen_gallery_db`.`zp_images` because after adding it, the row size is 8194 which is greater than maximum allowed size (8126 bytes) for a record on index leaf page.

The maximum row size is determined by the innodb page size: https://dev.mysql.com/doc/mysql-reslimits-excerpt/8.0/en/column-count-limit.html#row-size-limits

There's a lot of columns in the table, this is the truncated output of describe zp_images;:

...
| EXIFGPSAltitudeRef        | varchar(52)      | YES  |     | NULL    |                |
| IPTCOriginatingProgram    | varchar(32)      | YES  |     | NULL    |                |
| IPTCProgramVersion        | varchar(10)      | YES  |     | NULL    |                |
| VideoFormat               | varchar(10)      | YES  |     | NULL    |                |
| VideoSize                 | varchar(32)      | YES  |     | NULL    |                |
| VideoArtist               | mediumtext       | YES  |     | NULL    |                |
| VideoTitle                | mediumtext       | YES  |     | NULL    |                |
| VideoBitrate              | varchar(32)      | YES  |     | NULL    |                |
| VideoBitrate_mode         | varchar(32)      | YES  |     | NULL    |                |
| VideoBits_per_sample      | varchar(32)      | YES  |     | NULL    |                |
| VideoCodec                | varchar(32)      | YES  |     | NULL    |                |
| VideoCompression_ratio    | varchar(32)      | YES  |     | NULL    |                |
| VideoDataformat           | varchar(32)      | YES  |     | NULL    |                |
| VideoEncoder              | varchar(10)      | YES  |     | NULL    |                |
| VideoSamplerate           | varchar(32)      | YES  |     | NULL    |                |
| VideoChannelmode          | varchar(32)      | YES  |     | NULL    |                |
| VideoChannels             | varchar(10)      | YES  |     | NULL    |                |
| VideoFramerate            | varchar(32)      | YES  |     | NULL    |                |
| VideoResolution_x         | varchar(32)      | YES  |     | NULL    |                |
| VideoResolution_y         | varchar(32)      | YES  |     | NULL    |                |
| VideoAspect_ratio         | varchar(32)      | YES  |     | NULL    |                |
| VideoPlaytime             | varchar(10)      | YES  |     | NULL    |                |
| XMPrating                 | varchar(10)      | YES  |     | NULL    |                |
+---------------------------+------------------+------+-----+---------+----------------+
116 rows in set (0.001 sec)

MariaDB has a few fixes, mainly either make the columns 256 bytes or longer, or split the table into separate tables (eg shunt the video metadata off to another table).

The workaround I used was to prepend the mysqldump file with SET SESSION innodb_strict_mode=OFF;, which allowed the import to continue with warnings.

This isn't exactly safe for everyone, since adding rows could fail in the future:

if a DML statement is executed that attempts to write a row that the table's InnoDB row format can't store, then InnoDB will raise an error with this message. This creates a somewhat unsafe situation, because it means that the application has the chance to encounter an additional error while executing DML.

[acrylian]

We heard about this issue before but rarely. Did you try to use the database backup tool included? It does not backup the database itself but the contents so may workaround this somehow. Note that these fields are optionally and (and not all might have contents even if) its usage can be disabled on the options.

We could change the fields to text/blob probably but as far as I know this might cause performance issues with that many columns as these data types are a bit "outside" and their content needs to temporarily copied to a temp table internally.

The long term plan is to move (image) item meta data to its own separate table. Since this is quite a change it will not be for 1.x.

[kyl191]

No, I used mysqldump, not the internal tool.

Note that these fields are optionally and (and not all might have contents even if) its usage can be disabled on the options.

I think this is my workaround is fine, I don't have any videos so these columns are empty and don't hit the row limit.

The long term plan is to move (image) item meta data to its own separate table. Since this is quite a change it will not be for 1.x.

This sounds fine 👍
I've got a workaround, and it sounds like the fix is on the roadmap for v2. so I don't think there's anything that needs addressing other than properly tagging/closing this issue :)

[acrylian]

Yes, your workaround was probably fine. Thanks for the feedback!

[kyl191]

Actually, I fiddled around with it a bit more, and I think it might be because I missed at least one of the point upgrades.
Despite setup saying the upgrade to 1.5.7 was fine, I didn't have the lastchange column.

I altered some of the database columns to be text to get rid of the error, and reran setup.

Now my database looks like

...
| EXIFGPSAltitudeRef        | mediumtext       | YES  |     | NULL    |                |
| IPTCOriginatingProgram    | mediumtext       | YES  |     | NULL    |                |
| IPTCProgramVersion        | mediumtext       | YES  |     | NULL    |                |
| VideoFormat               | mediumtext       | YES  |     | NULL    |                |
| VideoSize                 | varchar(255)     | YES  |     | NULL    |                |
| VideoArtist               | mediumtext       | YES  |     | NULL    |                |
| VideoTitle                | mediumtext       | YES  |     | NULL    |                |
| VideoBitrate              | varchar(255)     | YES  |     | NULL    |                |
| VideoBitrate_mode         | mediumtext       | YES  |     | NULL    |                |
| VideoBits_per_sample      | varchar(255)     | YES  |     | NULL    |                |
| VideoCodec                | mediumtext       | YES  |     | NULL    |                |
| VideoCompression_ratio    | varchar(255)     | YES  |     | NULL    |                |
| VideoDataformat           | mediumtext       | YES  |     | NULL    |                |
| VideoEncoder              | mediumtext       | YES  |     | NULL    |                |
| VideoSamplerate           | varchar(255)     | YES  |     | NULL    |                |
| VideoChannelmode          | mediumtext       | YES  |     | NULL    |                |
| VideoChannels             | varchar(255)     | YES  |     | NULL    |                |
| VideoFramerate            | varchar(255)     | YES  |     | NULL    |                |
| VideoResolution_x         | varchar(255)     | YES  |     | NULL    |                |
| VideoResolution_y         | varchar(255)     | YES  |     | NULL    |                |
| VideoAspect_ratio         | varchar(255)     | YES  |     | NULL    |                |
| VideoPlaytime             | varchar(255)     | YES  |     | NULL    |                |
| XMPrating                 | mediumtext       | YES  |     | NULL    |                |
| lastchange                | datetime         | YES  |     | NULL    |                |
| lastchangeuser            | varchar(64)      | YES  |     | NULL    |                |
| IPTCKeywords              | mediumtext       | YES  |     | NULL    |                |
+---------------------------+------------------+------+-----+---------+----------------+
119 rows in set (0.002 sec)

I suddenly have a lot of mediumtext and varchar columns compared to the initial database, and the new lastchange, lastchangeuser, and IPTCKeywords columns.

The alter table $prefix_images seem to have been silently failing, which sounds more like a bug.

I restored the database and files from my backup, and retried the setup.

Then I altered the columns and reran setup.

The successful setup has a lot more checkmarks.

Looking at the upgrade code I can't figure out why it wouldn't raise a failure message.

[acrylian]

I don't remember offhand exactly when the lastchange column was added to all tables (articles and pages had it for longer already) but surely some 1.5.x release. I assume you looked in all error logs incl. the mysql one fro failed queries? From what version did you upgrade?

And yes, we did raised the size of some columns because they were too small for usages quite some time back.

[kyl191]

Based on https://github.com/zenphoto/zenphoto/blob/master/zp-core/setup/index.php#L2418, I think it was added in v1.5.2

I upgraded from 1.5.0 to 1.5.7

[acrylian]

Thanks for searching for it. No idea why it then was missing. Setup actually still goes through everything to not miss anything.