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.

[rfay]

I haven't found a solution to this, but ChatGPT suggested ALTER TABLE tablename ROW_FORMAT=COMPRESSED; as an option for this problem. I'd be interested if that affects anything.

Since this is a longstanding (and probably well-understood Craft CMS problem) I assume you've already conferred with the Craft community about it?

[leevigraham]

Craft 5 rewrote their content storage to a large json blob to tackle this issue. There doesn't seem to be solution for v3 / v4

[rfay]

Being an old-school relational database normalization person, I still can't quite approve of the json blob approach :(

[rfay]

Could you please try the artifacts in #6549 (comment) and see if they give you any hope? You'll need to ddev delete or try out in a fresh project.

It may be that the way to do this is by adding a new setting for a DDEV project where it doesn't create the database by default during start on a new project. I'm not sure what the impact of that would be. The reason DDEV precreates the db (at image build time) is that it speeds up startup quite a lot.

[leevigraham]

I'll take a look.