Increase innodb_page_size for mysql

[leevigraham]

Is there a way to configure the innodb_page_size setting in mysql so that it applies before the mysql container is created and the db is created?

I tried to add a custom mysql.cnf but I received an error:

The error is caused by a mismatch between the page size in your existing InnoDB data files and the innodb_page_size value that you're trying to start MySQL with. Specifically, your data file (./ibdata1) was created with a page size of 16KB (16384), but you're now trying to start MySQL with a page size of 64KB (65536). InnoDB cannot change the page size for existing data files.

When importing I get the following error:

 error: The shell command "mysql --defaults-extra-file="/tmp/vxwivaqumtvg.cnf" db < "./tests/db.sql"" failed with exit code 1: ERROR 1118 (42000) at line 1723: 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

by default the innodb_page_size is 16k

mysql> SHOW VARIABLES LIKE "innodb_page_size";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.01 sec)

[rfay]

Can you say why you want to change innodb_page_size ?

• I imagine you could in fact fundamentally override it with a .ddev/db_build/Dockerfile.innodb that changed the /etc/my.cnf.
• You might be able to do it with your current strategy when creating a new database, perhaps with ddev import-db --database

The problem is that the db database is precreated and bundled into the image, so that's why you're not getting access to change it.

But I doubt that changing this value is a good idea. Interested in why you think it is.

[leevigraham]

Related craftcms/cms#7750 craftcms/cms#2009

[leevigraham]

Raised a discussion here with the plugin devs: solspace/craft-freeform#1544

[rfay]

Thanks for the work chasing this. @brandonkelly 's argument on the PR was certainly valid, if you can't solve this on local you're going to have a hard time solving it for a hosting environment.

[leevigraham]

@rfay After some more investigation with the client (who manages their own mysql docker container) I found that they have set innodb_strict_mode=OFF in production.

[rfay]

Are you able to add that in your .ddev/mysql/.cnf and make it work for you?

[leevigraham]

Yep in this case it resolves the row size "error". I still think it would be nice to have a way to update innodb_page_size as a config in ddev… although it's not an immediate need for me atm.

[rfay]

It would, but that would make every ddev start significantly slower for every user of DDEV, like at least 10-20 seconds slower, while it did the things that are normally prebuilt in the database.