MySQL migration problems: Row size too large

[jinnko]

Issue details

Update to latest docker image with digest 8007ce2cf647d6ef4f2168edbcc520d71e9beaa001db2de5f09189f31bb540fa has problems with DB migration and therefore doesn't start up.

Environment

• wallabag version (or git revision) that exhibits the issue: Upgrade from pre-2.4.0 to 2.4.0
• How did you install wallabag? Via git clone or by downloading the package? Using the official docker image.
• Last wallabag version that did not exhibit the issue (if applicable): Previous "latest" docker image, which seems to have been 2.3.8
• php version:
• OS:
• type of hosting (shared or dedicated): dedicated
• which storage system you choose at install (SQLite, MySQL/MariaDB or PostgreSQL): MySQL

Steps to reproduce/test case

1. Start up the container
2. Run the DB migration script
3. See the logs for output...

host $ docker exec -u nobody wallabag /bin/sh
container $ /var/www/wallabag/bin/console doctrine:migrations:migrate --env=prod

                    Application Migrations


WARNING! You are about to execute a database migration that could result in schema changes and data loss. Are you sure you wish to continue? (y/n)y
Migrating up to 20200428072628 from 20190511165128

  ++ migrating 20190601125843

     -> ALTER TABLE wallabag_entry ADD given_url LONGTEXT DEFAULT NULL, ADD hashed_given_url TINYTEXT DEFAULT NULL
Migration 20190601125843 failed during Execution. Error An exception occurred while executing 'ALTER TABLE wallabag_entry ADD given_url LONGTEXT DEFAULT NULL, ADD hashed_given_url TINYTEXT DEFAULT NULL':

SQLSTATE[42000]: Syntax error or access violation: 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
09:30:45 ERROR     [console] Error thrown while running command "doctrine:migrations:migrate --env=prod". Message: "An exception occurred while executing 'ALTER TABLE wallabag_entry ADD given_url LONGTEXT DEFAULT NULL, ADD hashed_given_url TINYTEXT DEFAULT NULL':

SQLSTATE[42000]: Syntax error or access violation: 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" ["exception" => Doctrine\DBAL\Exception\DriverException { …},"command" => "doctrine:migrations:migrate --env=prod","message" => """  An exception occurred while executing 'ALTER TABLE wallabag_entry ADD given_url LONGTEXT DEFAULT NULL, ADD hashed_given_url TINYTEXT DEFAULT NULL':\n  \n  SQLSTATE[42000]: Syntax error or access violation: 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  """]

In AbstractMySQLDriver.php line 110:

  An exception occurred while executing 'ALTER TABLE wallabag_entry ADD given_url LONGTEXT DEFAULT NULL, ADD hashed_given_url TINYTEXT DEFAULT NULL':

  SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, c
  heck the manual. You have to change some columns to TEXT or BLOBs


In PDOConnection.php line 91:

  SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, c
  heck the manual. You have to change some columns to TEXT or BLOBs


In PDOConnection.php line 86:

  SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, c
  heck the manual. You have to change some columns to TEXT or BLOBs


doctrine:migrations:migrate [--write-sql [WRITE-SQL]] [--dry-run] [--query-time] [--allow-no-migration] [--configuration [CONFIGURATION]] [--db-configuration [DB-CONFIGURATION]] [--db DB] [--em EM] [--shard SHARD] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--] <command> [<version>]

[ghost]

same error here. Without docker, just a regular git-clone from april 2018 (and kept up to date, most of the time on 2.3.8).

first error with make update was something about a problem replacing some mail related settings, but this I fixed by setting the mailer_transport from mail to null, so no showstopper here...

but now I get exactly the same error as posted above. Same migration, same scripts, same lines...: 1118 Row size too large[...]

Database is MySQL.

[Olchie]

I've had a similar problem. It seems that the problem is not wallabag but mariadb. The following link helped me to fix it:

https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-innodb/

[ghost]

yes, right now I just fixed it by adding a line in my /etc/mysql/mariadb.conf.d/50-server.cnf file (debian server) unter the [mariadb]-section innodb_strict_mode=OFF

then a quick systemctl restart mariadb.service and now make update worked :)

reference: https://docs.w3cub.com/mariadb/innodb-strict-mode/

[jinnko]

I'm reluctant to disable innodb_strict_mode as this is a shared DB server and the implications are not clear.

I dug a little deeper and it seems that adjusting the innodb-page-size may help, but this is not something that can be done on an existing server without dumping, changing config, then re-importing all the databases.

It's not clear to me exactly why this is happening on upgrade. Looking at the DB backups I can see that longtext fields existed prior to wallabag v2.4.0, so it doesn't seem to be those columns.

For now I've rolled back to 2.3.8 and hope this can be resolved within Wallabag rather than by disabling innodb-strict-mode in the DB.

[biva]

@jinnko I disabled innodb_strict_mode so that the update works, and then I enabled it again. And wallabag 2.4 works fine :)

[JimboJoe]

Hi, just to let you know that this issue happens also on YunoHost package when working on 2.4.0 update (cc @lapineige )

[HLFH]

After having this issue and following this workaround, I get the following issue during my upgrade from 2.3.x to 2.4.1: https://github.com/wallabag/wallabag/issues/4979

[Kdecherf]

@jinnko do you still have this issue?