Question: How do I set my innodb_page_size?

[cleder]

I tried to add this option:

/usr/local/bin/docker-entrypoint.sh --innodb-page-size=64KB --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --sql-mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION --general-log=1 --general-log-file=/var/lib/mysql/general-log.log"

which results in:

2023-11-29T10:19:30.007248Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.35) starting as process 7
2023-11-29T10:19:30.013802Z 0 [Warning] [MY-012363] [InnoDB] innodb-page-size has been changed from the default value 16384 to 65536.
2023-11-29T10:19:30.016753Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-11-29T10:19:30.091725Z 1 [ERROR] [MY-012222] [InnoDB] Data file './ibdata1' uses page size 16384, but the innodb_page_size start-up parameter is 65536
2023-11-29T10:19:30.091762Z 1 [ERROR] [MY-012237] [InnoDB] Corrupted page [page id: space=0, page number=0] of datafile './ibdata1' could not be found in the doublewrite buffer.
2023-11-29T10:19:30.091771Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2023-11-29T10:19:30.590935Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2023-11-29T10:21:13.246613Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2023-11-29T10:21:13.246632Z 0 [ERROR] [MY-010119] [Server] Aborting

Is there a way to set the innodb_page_size?

[tianon]

Data file './ibdata1' uses page size 16384, but the innodb_page_size start-up parameter is 65536

I'm pretty sure this means you can only set this on a new database, not on an existing one. 🙈

[tianon]

Confirmed that it does seem to work as expected:

$ docker run -it --rm --env MYSQL_ROOT_PASSWORD=foo mysql --innodb-page-size=64KB
Unable to find image 'mysql:latest' locally
latest: Pulling from library/mysql
e9f2695d7e5b: Pull complete 
c041cd0148ec: Pull complete 
27c9fbf7aa29: Pull complete 
62fc1efc1f1f: Pull complete 
e1d25a6611c2: Pull complete 
5846de7fe479: Pull complete 
faf13e3256e8: Pull complete 
2217ed684a4f: Pull complete 
45bfd3acf105: Pull complete 
5b68afdb04ae: Pull complete 
Digest: sha256:6057dec95d87a0d7880d9cfc9b3d9292f9c11473a5104b906402a2b73396e377
Status: Downloaded newer image for mysql:latest
2023-12-13 19:29:05+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.2.0-1.el8 started.
2023-12-13 19:29:06+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2023-12-13 19:29:06+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.2.0-1.el8 started.
2023-12-13 19:29:06+00:00 [Note] [Entrypoint]: Initializing database files
2023-12-13T19:29:06.221975Z 0 [System] [MY-015017] [Server] MySQL Server Initialization - start.
2023-12-13T19:29:06.223416Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
2023-12-13T19:29:06.223499Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.2.0) initializing of server in progress as process 80
2023-12-13T19:29:06.229202Z 0 [Warning] [MY-012363] [InnoDB] innodb-page-size has been changed from the default value 16384 to 65536.
2023-12-13T19:29:06.230304Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-12-13T19:29:07.102725Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-12-13T19:29:08.549741Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2023-12-13T19:29:12.055633Z 0 [System] [MY-015018] [Server] MySQL Server Initialization - end.
2023-12-13 19:29:12+00:00 [Note] [Entrypoint]: Database files initialized
2023-12-13 19:29:12+00:00 [Note] [Entrypoint]: Starting temporary server
mysqld will log errors to /var/lib/mysql/92ffd6b46a88.err
mysqld is running as pid 126
2023-12-13T19:29:12.791404Z 0 [System] [MY-015016] [Server] MySQL Server - end.
2023-12-13 19:29:12+00:00 [Note] [Entrypoint]: Temporary server started.
'/var/lib/mysql/mysql.sock' -> '/var/run/mysqld/mysqld.sock'
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.

2023-12-13 19:29:14+00:00 [Note] [Entrypoint]: Stopping temporary server
2023-12-13 19:29:16+00:00 [Note] [Entrypoint]: Temporary server stopped

2023-12-13 19:29:16+00:00 [Note] [Entrypoint]: MySQL init process done. Ready for start up.

2023-12-13T19:29:16.334853Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2023-12-13T19:29:16.510475Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
2023-12-13T19:29:16.511642Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.2.0) starting as process 1
2023-12-13T19:29:16.518862Z 0 [Warning] [MY-012363] [InnoDB] innodb-page-size has been changed from the default value 16384 to 65536.
2023-12-13T19:29:16.520329Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-12-13T19:29:16.766137Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-12-13T19:29:16.961538Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-12-13T19:29:16.961563Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-12-13T19:29:16.963829Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2023-12-13T19:29:16.981882Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2023-12-13T19:29:16.981900Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.2.0'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.