ORM engine initialization Error: migrate: Row size too large #4483

@zeripath commented on GitHub (Dec 15, 2019):

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.

@zeripath commented on GitHub (Dec 15, 2019): 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.

GiteaMirror commented

@Whisprin commented on GitHub (Dec 16, 2019):

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

@Whisprin commented on GitHub (Dec 16, 2019): 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

GiteaMirror commented

@stale[bot] commented on GitHub (Feb 14, 2020):

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.

@stale[bot] commented on GitHub (Feb 14, 2020): 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.

GiteaMirror commented

@jacksgt commented on GitHub (Feb 20, 2020):

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.

@jacksgt commented on GitHub (Feb 20, 2020): 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.

GiteaMirror commented

@sapk commented on GitHub (Feb 20, 2020):

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.

@sapk commented on GitHub (Feb 20, 2020): 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.

GiteaMirror commented

@guillep2k commented on GitHub (Feb 20, 2020):

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.

@guillep2k commented on GitHub (Feb 20, 2020): 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.

GiteaMirror commented

@leetNightshade commented on GitHub (Mar 4, 2020):

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

@leetNightshade commented on GitHub (Mar 4, 2020): 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_COMMENT` from `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA`=? AND (`ENGINE`='MyISAM' OR `ENGINE` = 'InnoDB' OR `ENGINE` = 'TokuDB') [gitea] 2020/03/04 06:45:24 ...orm/dialect_mysql.go:320:GetColumns() [I] [SQL] SELECT `COLUMN_NAME`, `IS_NULLABLE`, `COLUMN_DEFAULT`, `COLUMN_TYPE`, `COLUMN_KEY`, `EXTRA`,`COLUMN_COMMENT` FROM `INFORMATION_SCHEMA`. `COLUMNS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? [gitea user] 2020/03/04 06:45:24 ...orm/dialect_mysql.go:460:GetIndexes() [I] [SQL] SELECT `INDEX_NAME`, `NON_UNIQUE`, `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`STATISTICS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? [gitea user] 2020/03/04 06:45:24 .../xorm/session_raw.go:196:exec() [I] [SQL] ALTER TABLE `user` ADD `theme` 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`

GiteaMirror commented

@guillep2k commented on GitHub (Mar 4, 2020):

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

@guillep2k commented on GitHub (Mar 4, 2020): @leetNightshade Can I suggest looking at [these answers](https://stackoverflow.com/a/51255047/225979) from SO?

GiteaMirror commented

@leetNightshade commented on GitHub (Mar 11, 2020):

@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?

@leetNightshade commented on GitHub (Mar 11, 2020): > > > @leetNightshade Can I suggest looking at [these answers](https://stackoverflow.com/a/51255047/225979) 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?

GiteaMirror commented

@6543 commented on GitHub (Mar 11, 2020):

@leetNightshade what version of MySql do you run?

@6543 commented on GitHub (Mar 11, 2020): @leetNightshade what version of MySql do you run?

GiteaMirror commented

@guillep2k commented on GitHub (Mar 11, 2020):

@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).

@guillep2k commented on GitHub (Mar 11, 2020): @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](https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-innodb/) 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](https://stackoverflow.com/a/60172908/225979), 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).

GiteaMirror commented

@leetNightshade commented on GitHub (Mar 12, 2020):

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

@leetNightshade commented on GitHub (Mar 12, 2020): I'm running: mariadb Ver 15.1 Distrib 10.4.12-MariaDB, for Linux (x86_64) using readline 5.1

GiteaMirror commented

@6543 commented on GitHub (Mar 12, 2020):

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

@6543 commented on GitHub (Mar 12, 2020): @leetNightshade just try what guillep2k suggested and the try it again - would be nice if you can tell us the result

GiteaMirror commented

@guillep2k commented on GitHub (Mar 12, 2020):

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.

@guillep2k commented on GitHub (Mar 12, 2020): > > > 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.

GiteaMirror commented

@leetNightshade commented on GitHub (Mar 25, 2020):

That did the trick, thank you @guillep2k !!

@leetNightshade commented on GitHub (Mar 25, 2020): *That* did the trick, thank you @guillep2k !!

GiteaMirror commented

@zeripath commented on GitHub (Mar 25, 2020):

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.

@zeripath commented on GitHub (Mar 25, 2020): 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.

GiteaMirror commented

@Whisprin commented on GitHub (Mar 26, 2020):

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

@Whisprin commented on GitHub (Mar 26, 2020): 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

GiteaMirror commented

@lafriks commented on GitHub (Mar 26, 2020):

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.

@lafriks commented on GitHub (Mar 26, 2020): 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.

GiteaMirror commented

@Whisprin commented on GitHub (Mar 26, 2020):

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?

@Whisprin commented on GitHub (Mar 26, 2020): 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?

GiteaMirror commented

@lafriks commented on GitHub (Mar 26, 2020):

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

@lafriks commented on GitHub (Mar 26, 2020): 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

GiteaMirror commented