Change innodb_page_size with Parameter

[Username1233423]

Hello, I am using Holland backup on with a MariaDB instance on version 10.4 with an innodb_pgage_size of 32k. When I start the backup process using mysqldump-lvm process I get the following error: [ERROR] InnoDB: Data file './ibdata1' uses page size 32768, but the innodb_page_size start-up parameter is 16384.

Is there a way to set the page size manually?

[soulen3]

It looks like there isn't. I'm I understanding the error correctly. The database engine is failing to start on the lvm-snapshot?

[mikegriffin]

This issue is repeatable if you configure MySQL with innodb_page_size=32k and then launch mysql-lvm with the innodb-recovery=yes option:

[InnoDB] Data file './ibdata1' uses page size 32768, but the innodb_page_size start-up parameter is 16384

I tried to make a patch but didn't get it working

--- a/actions/mysql/_mysqld.py
+++ b/actions/mysql/_mysqld.py
@@ -92,6 +92,7 @@
         "innodb-data-home-dir",
         "innodb-data-file-path",
         "innodb-fast-shutdown",
+        "innodb-page-size",
         "open-files-limit",
         "key-buffer-size",
         "tmpdir",
--- a/plugin/innodb.py
+++ b/plugin/innodb.py
@@ -26,6 +26,7 @@
         "innodb_data_home_dir",
         "innodb_data_file_path",
         "abs_tablespace_paths",
+        "innodb_page_size",
     )

     def __new__(
@@ -36,6 +37,7 @@
         innodb_data_home_dir,
         innodb_data_file_path,
         abs_tablespace_paths,
+        innodb_page_size,
     ):
         return tuple.__new__(
             cls,
@@ -46,6 +48,7 @@
                 innodb_data_home_dir,
                 innodb_data_file_path,
                 abs_tablespace_paths,
+                innodb_page_size,
             ),
         )

@@ -61,7 +64,8 @@
         return (
             "MySQLPathInfo(datadir=%r, innodb_log_group_home_dir=%r, \
                 innodb_log_files_in_group=%r, innodb_data_home_dir=%r, \
-                innodb_data_file_path=%r, abs_tablespace_paths=%r)"
+                innodb_data_file_path=%r, abs_tablespace_paths=%r, \
+                innodb_page_size=%r)"
             % self
         )

@@ -75,6 +79,7 @@
             "innodb_data_home_dir": tmp[3],
             "innodb_data_file_path": tmp[4],
             "abs_tablespace_paths": tmp[5],
+            "innodb_page_size": tmp[6],
         }

     def _replace(self, **kwds):
@@ -90,6 +95,7 @@
                         "innodb_data_home_dir",
                         "innodb_data_file_path",
                         "abs_tablespace_paths",
+                        "innodb_page_size",
                     ),
                     self,
                 )
@@ -108,6 +114,7 @@
     innodb_data_home_dir = property(itemgetter(3))
     innodb_data_file_path = property(itemgetter(4))
     abs_tablespace_paths = property(itemgetter(5))
+    innodb_page_size = property(itemgetter(6))

     @classmethod
     def from_mysql(cls, mysql):
@@ -121,6 +128,7 @@
             innodb_data_home_dir=ibd_homedir,
             innodb_data_file_path=mysql.show_variable("innodb_data_file_path"),
             abs_tablespace_paths=abs_tablespace_paths,
+            innodb_page_size=mysql.show_variable("innodb_page_size"),
         )

     def get_innodb_logdir(self):
--- a/plugin/raw/util.py
+++ b/plugin/raw/util.py
@@ -58,6 +58,8 @@
             mysqld_config["tmpdir"] = tempfile.gettempdir()
         ib_log_size = client.show_variable("innodb_log_file_size")
         mysqld_config["innodb-log-file-size"] = ib_log_size
+        ib_page_size = client.show_variable("innodb_page_size")
+        mysqld_config["innodb-page-size"] = ib_page_size
         act = InnodbRecoveryAction(mysqld_config)
         snapshot.register("post-mount", act, priority=100)
     if config["mysql-lvm"]["archive-method"] == "dir":
--- a/plugin/mysqldump/util.py
+++ b/plugin/mysqldump/util.py
@@ -46,6 +46,9 @@
         ib_log_size = client.show_variable("innodb_log_file_size")
         if ib_log_size:
             mysqld_config["innodb-log-file-size"] = ib_log_size
+        ib_page_size = client.show_variable("innodb_page_size")
+        if ib_page_size:
+            mysqld_config["innodb-page-size"] = ib_page_size

         ibd_home_dir = pathinfo.innodb_data_home_dir
         if ibd_home_dir:

The problem is that the innodb_page_size needs to be read from the running server and land in eg my.innodb_recovery.cnf but the above patch doesn't accomplish it. I guess it doesn't appear in config.items for generate_server_config

[soulen3]

There's a couple of ways we can solve this. Reading the value from the mysql server is a good option, but I'm not sure if that's possible and isn't a generic solution. Another option would be to allow any arbitrary options under [mysqld] in the holland config file

[mikegriffin]

The patch is reading the innodb_page_size from running the MySQL instance, as I copied the behavior of innodb_log_file_size which is using the approach.

Here is general_log evidence of the claim:

2022-02-03T03:10:23.482065Z	   38 Connect	root@localhost on  using TCP/IP
2022-02-03T03:10:23.482356Z	   38 Query	SET AUTOCOMMIT = 0
2022-02-03T03:10:23.482661Z	   38 Query	SHOW GLOBAL VARIABLES LIKE 'datadir'
2022-02-03T03:10:23.533884Z	   39 Connect	root@localhost on  using TCP/IP
2022-02-03T03:10:23.534246Z	   39 Query	SET AUTOCOMMIT = 0
2022-02-03T03:10:23.534713Z	   39 Query	SHOW GLOBAL VARIABLES LIKE 'have_innodb'
2022-02-03T03:10:23.537147Z	   38 Query	SHOW GLOBAL VARIABLES LIKE 'innodb_log_file_size'
2022-02-03T03:10:23.539148Z	   38 Query	SHOW GLOBAL VARIABLES LIKE 'innodb_page_size'
2022-02-03T03:10:23.543728Z	   38 Query	FLUSH /*!40101 LOCAL */ TABLES
2022-02-03T03:10:23.545497Z	   38 Query	FLUSH TABLES WITH READ LOCK
2022-02-03T03:10:23.545781Z	   38 Query	SHOW MASTER STATUS
2022-02-03T03:10:23.546510Z	   38 Query	SHOW SLAVE STATUS
2022-02-03T03:10:23.837610Z	   38 Query	UNLOCK TABLES

The problem seems to be that, in my patch, innodb_page_size is not then passed to the spawned instance.

There are variables which you wouldn't want to read from the running MySQL instance like innodb_buffer_pool_size since your backup instance very likely wants to use a significantly smaller configuration value (only sequential reads by mysqldump and crash recovery doesn't benefit much beyond a few GB in my experience, not to mention that the running instance will typically have 70% or more memory allocated to the buffer pool)

If one wanted to deploy with this variable, it is worth mention that MySQL must be instantiated with the option before creating a datadir. So for example in MySQL 8.0 you would set the variable in my.cnf before calling mysqld --user=mysql --initialize-insecure with an empty writable /var/lib/mysql

As an aside, investigating this bug report shows that innodb_log_files_in_group is not currently read from the server. This omission would affect MySQL 8.0 deployments that opt in to the innodb_dedicated_server setting and some other less common deployments which manually changed this.

Adding proper support for innodb_log_files_in_group would include handling the situation that MariaDB has recently removed this configuration item. The code would need to read a innodb_log_files_in_group value from running service and pass that to backup instance, unless the result of the SHOW GLOBAL VARIABLES is an empty set)

[mikegriffin]

In mysqldump as well as in the raw plugin util.py does similar to what we would need for handling absence of innodb_log_files_in_group configuration variable:

if mysql.show_variable("have_innodb") == "YES":

[mikegriffin]

Upon closer look, the patch seems to work correctly with mysqldump lvm sub-plugin but I was testing with innodb_recovery and didn't notice that other bugs in this sub-plugin were actually causing a failure (I didn't read the log carefully enough.)

The page size was copied to the temporary instance mysqld configuration with the original patch but innodb_recovery sub-plugin was failing for unrelated issues:

1. innodb_recovery uses wrong path for temporary mysqld unix socket (mysqldump does this correctly)
2. innodb_recovery uses --bootstrap which fails in MySQL 8 (it seems that detecting 8.0 and passing in --log-error-verbosity=3, --init-file=/path/to/shutdown/command.txt likely does what we need for this sub-plugin)

This should probably be multiple pull requests so that blame can be properly attributed.

Additionally, during debugging I can up with two feature requests that were easily added in:

1. lvm providers sleep time (eg failed_lvm_debug_sleep_time default 0 or 120) that allows you to glance at the instance datadir/config/log before the mount and snapshot are removed and failed backup is purged (even with auto purge failures debugging was difficult)
2. lvm providers write out each line (less than a dozen) of the configuration file for the temporary mysqld

[Username1233423]

Hi,

is there already a solution for the problem?