ORM engine initialization Error: migrate: Row size too large

[Whisprin]

• Gitea version (or commit ref): 1.10.0
• Git version: 2.24.1
• Operating system: Linux (Arch)
• Database (use [x]):
  • PostgreSQL
  • MySQL
  • MSSQL
  • SQLite
• Can you reproduce the bug at https://try.gitea.io:
  • Yes (provide example URL)
  • No
  • Not relevant
• Log gist:
  https://gist.github.com/Whisprin/69ec124abec9fb94fbe713e32f5a652a

Description

After upgrading from Gitea v1.9.5 to 1.10.0 I'm getting a database migration error:
ORM engine initialization attempt #8/10 failed. Error: migrate: do migrate: 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

[zeripath]

What version of MySQL are you running?

[Whisprin]

@zeripath

% mariadb --version
mariadb  Ver 15.1 Distrib 10.4.11-MariaDB, for Linux (x86_64) using readline 5.1

gitea database collation: utf8mb4_unicode_ci
some tables are utf8mb4_unicode_ci, most are: utf8_general_ci

I remember there being a similar issue in Nextcloud before, requiring changing the table row format to DYNAMIC.

edit: This worked for me. Manually changing the row format and character set of each table:

# take a backup of the database

MariaDB> USE INFORMATION_SCHEMA;

# change row format
# this creates a list of SQL commands, run each manually
MariaDB> SELECT CONCAT("ALTER TABLE `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` ROW_FORMAT=DYNAMIC;") AS MySQLCMD FROM TABLES WHERE TABLE_SCHEMA = "gitea";

# change collation, character set
# this creates a list of SQL commands, run each manually
MariaDB> SELECT CONCAT("ALTER TABLE `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` CONVERT TO CHARACTER SET utf8mb4;") AS MySQLCMD FROM TABLES WHERE TABLE_SCHEMA = "gitea";

Adapted from: https://docs.nextcloud.com/server/11/admin_manual/maintenance/mysql_4byte_support.html#mariadb-support

[Whisprin]

@zeripath Why did you close this issue? I only described a manual workaround, the issue persists

This should be part of the database migration / maintenance script of gitea (see Nextcloud example). Apparently gitea requires a certain row format which is not explicitly specified during table creation.

Some more info: The mariadb defaults have changed in 10.2.2 and gitea required the newer values. However, previously existing tables need to be converted manually.
https://mariadb.com/kb/en/library/innodb-row-formats-overview/
https://mariadb.com/kb/en/library/innodb-file-format/

[zeripath]

As far as I could see the issue is specific to old versions of MariaDB. I'm but certain there's really any way we can go about detecting this.

[Whisprin]

The issue is independent of the MariaDB version in a sense that upgrading the database doesn't help. Specific MariaDB default configurations are required and tables which existed before need to be converted manually.
From my point of view gitea requires certain db features which are not explicitly specified or documented.

• MariaDB specific documentation can be added in the docs
  • that's something I could work on
  • to mention the database server default ROW_FORMAT=DYNAMIC;
• Prior to running migrations gitea can check the INFORMATION_SCHEMA table and print a meaningful warning
  • may be something I can contribute
• An automatic migration can convert all gitea tables. There's no harm in converting tables, that are already in the correct format
  • all CREATE TABLE queries in gitea can be extend by ROW_FORMAT=DYNAMIC;

Only now (after I know the problem and workaround) I found the convert command:
https://github.com/go-gitea/gitea/pull/7144/files
A similar approach can be taken for the row format. (I can draft a PR)

The MySQL default charset could be changed to utf8mb4:
https://github.com/go-gitea/gitea/blob/master/custom/conf/app.ini.sample#L325

[stale]

This issue has been automatically marked as stale because it has not had recent activity. I am here to help clear issues left open even if solved or waiting for more insight. This issue will be closed if no further activity occurs during the next 2 weeks. If the issue is still valid just add a comment to keep it alive. Thank you for your contributions.

[jacksgt]

I just had the same issue upgrading from Gitea 1.10.2 to 1.11.1 with MariaDB version 10.3.22

This should really be part of the automatic upgrade process.

Thanks to @Whisprin for the SQL commands, they worked for me.

[sapk]

This should be added to the docs and maybe the check could be in doctor command first and if stable move to the web startup process.

[guillep2k]

We could also add a function (step independent) to the migration function that attempts to create row with a deliberately big length, then delete it. If that fails, we could abort the process, warn the user and point them to the docs.

[leetNightshade]

I upgraded from 1.6 to 1.11 and am having this issue. I tried running the manual fix as suggested above, but I'm still seeing the same error.

2020/03/04 06:45:24 ...ations/migrations.go:330:Migrate() [I] Migration[77]: add theme to users 2020/03/04 06:45:24 ...orm/dialect_mysql.go:431:GetTables() [I] [SQL] SELECT TABLE_NAME, ENGINE, TABLE_ROWS, AUTO_INCREMENT, TABLE_COMMENTfromINFORMATION_SCHEMA.TABLESWHERETABLE_SCHEMA=? AND (ENGINE='MyISAM' OR ENGINE= 'InnoDB' ORENGINE= 'TokuDB') [gitea] 2020/03/04 06:45:24 ...orm/dialect_mysql.go:320:GetColumns() [I] [SQL] SELECTCOLUMN_NAME, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_TYPE, COLUMN_KEY, EXTRA,COLUMN_COMMENTFROMINFORMATION_SCHEMA. COLUMNSWHERETABLE_SCHEMA= ? ANDTABLE_NAME= ? [gitea user] 2020/03/04 06:45:24 ...orm/dialect_mysql.go:460:GetIndexes() [I] [SQL] SELECTINDEX_NAME, NON_UNIQUE, COLUMN_NAMEFROMINFORMATION_SCHEMA.STATISTICSWHERETABLE_SCHEMA= ? ANDTABLE_NAME= ? [gitea user] 2020/03/04 06:45:24 .../xorm/session_raw.go:196:exec() [I] [SQL] ALTER TABLEuserADDtheme VARCHAR(30) DEFAULT '' NOT NULL ; - took: 3.750008ms 2020/03/04 06:45:24 routers/init.go:72:initDBEngine() [E] ORM engine initialization attempt #1/10 failed. Error: migrate: do migrate: 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

[guillep2k]

@leetNightshade Can I suggest looking at these answers from SO?

[leetNightshade]

@leetNightshade Can I suggest looking at these answers from SO?

"Variable 'innodb_file_format' is a read only variable." Those answers aren't helpful to someone who doesn't know what they're doing. Gitea created this problem, why aren't you guys solving it?

[6543]

@leetNightshade what version of MySql do you run?

[guillep2k]

@leetNightshade I'm sorry you didn't find that link useful. Unfortunately that's a limitation of MySQL/MariaDB. AFAICT the problem you are facing is very common for databases created with old versions of those products. "Modern" versions of MySQL/MariaDB don't have this problem. What you need to do is to upgrade your engine and look for a way of converting your tables to a newer format.

I'd suggest you perform a backup and upgrade to the latest database engine version possible (e.g. MariaDB 10.4.12). You can then export your database with mysqldump, modify the backup as instructed here, and import it back (you'll need to update the script and change DB="" for DB="gitea" or whatever is the name of your database).

[leetNightshade]

I'm running: mariadb Ver 15.1 Distrib 10.4.12-MariaDB, for Linux (x86_64) using readline 5.1

[6543]

@leetNightshade just try what guillep2k suggested and the try it again - would be nice if you can tell us the result

[guillep2k]

I'm running: mariadb Ver 15.1 Distrib 10.4.12-MariaDB, for Linux (x86_64) using readline 5.1

Nice! 😄. The original poster had one, but I didn't know what you had.

So, you need to stop Gitea (make sure no gitea processes keep running, even if it seems to have stopped), and export/transform/re-import the database (assuming your database name is gitea):

mysqldump -u root -p gitea > original.sql
sed 's:ROW_FORMAT=COMPACT:ROW_FORMAT=DYNAMIC:g' original.sql > modified.sql
mysql -u root -p gitea < modified.sql

You will be prompted for the root user's password two times. The original.sql script remains as a normal backup in case of trouble. Save a copy of original.sql for safekeeping purposes.

[leetNightshade]

That did the trick, thank you @guillep2k !!

[zeripath]

So we can finally close this issue - which is a fault with MySQL not Gitea - I guess we should put this little script in the FAQ.

[Whisprin]

It's not a fault with MySQL, it works as intended. It's a fault of how Gitea is using MySQL and a missing migration. If you need ROW_FORMAT=DYNAMIC specify it explicitly during table creation or at least please mention it in the documentation that dynamic is the expected row format. @zeripath @lunny

[lafriks]

I created PR to set that for new installations and new tables. Existing tables won't be fixed and @guillep2k script should be used instead.

[Whisprin]

Awesome @lafriks
Do you think it would be possible to add the migration or just a message to prompt the user to the doctor command as @sapk suggested earlier?

[lafriks]

Migration is not really possible to implement as it requires dumping database and reimport. It would be enough to add that to FAQ as @zeripath suggested imho

[Whisprin]

Using ALTER_TABLE the row format can be changed with MySQL built in commands without dumping and reimporting. See comment: #9347 (comment)

[devurandom]

# take a backup of the database

MariaDB> USE INFORMATION_SCHEMA;

# change row format
# this creates a list of SQL commands, run each manually
MariaDB> SELECT CONCAT("ALTER TABLE `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` ROW_FORMAT=DYNAMIC;") AS MySQLCMD FROM TABLES WHERE TABLE_SCHEMA = "gitea";

# change collation, character set
# this creates a list of SQL commands, run each manually
MariaDB> SELECT CONCAT("ALTER TABLE `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` CONVERT TO CHARACTER SET utf8mb4;") AS MySQLCMD FROM TABLES WHERE TABLE_SCHEMA = "gitea";

Thanks, @Whisprin, that worked and made my Gitea server usable again!

[zeripath]

Or you could have just used gitea convert

https://docs.gitea.io/en-us/command-line/#convert

[mlthlschr]

did not work for me, neither using gitea convert, nor using the proposal of @Whisprin. Using gitea 1.12.3 and mariadb Ver 15.1 Distrib 10.4.12-MariaDB. EDIT: now it works, after the n-th try.