[MDEV-30065] "mariadb-install-db --enforce-storage-engine=InnoDB" fails with InnoDB errors Created: 2022-11-21  Updated: 2023-07-04

Status: Stalled
Project: MariaDB Server
Component/s: Locking
Affects Version/s: 10.5
Fix Version/s: 10.5

Type: Bug Priority: Major
Reporter: Daniel Black Assignee: Daniel Black
Resolution: Unresolved Votes: 0
Labels: not-10.3

Issue Links:
Problem/Incident
is caused by MDEV-8576 Bootstrap should ignore --enforce-sto... Closed
Relates
relates to MDEV-26372 enforce-storage-engine=InnoDB has no ... Closed
relates to MDEV-31619 dict_stats_persistent_storage_check()... Closed
relates to MDEV-30074 DROP PROCEDURE assertion sql/sql_pars... Open

 Description   

It should be possible to create InnoDB system tables. This gives uses the option for [smaller backups](https://stackoverflow.com/questions/74511347/mariadb-incremental-backup-generates-large-files-for-unchanged-database), and like Azure, to use InnoDB only.

10.3 has inbuilt prevention to avoid this, so not fixing for 10.3

 
$  mkdir -p /tmp/${PWD##*/}-datadir && scripts/mysql_install_db --no-defaults --srcdir=$OLDPWD --builddir=$PWD --datadir=/tmp/${PWD##*/}-datadir --verbose --enforce-storage-engine=InnoDB
 
Installing MariaDB/MySQL system tables in '/tmp/build-mariadb-server-10.3-datadir' ...
 
2022-11-22  9:13:40 0 [Note] /home/dan/repos/build-mariadb-server-10.3/sql/mysqld (mysqld 10.3.38-MariaDB) starting as process 121577 ...
 
...
 
2022-11-22  9:13:40 0 [Note] InnoDB: 10.3.38 started; log sequence number 0; transaction id 7
 
2022-11-22  9:13:40 6 [ERROR] InnoDB: Trying to create a MySQL system table `mysql`.`db` of type InnoDB. MySQL system tables must be of the MyISAM type!
 
ERROR: 1005  Can't create table `mysql`.`db` (errno: 168 "Unknown (generic) error from engine")
 
2022-11-22  9:13:40 0 [ERROR] Aborting

10.4

 
$  mkdir -p /tmp/${PWD##*/}-datadir && scripts/mysql_install_db --no-defaults --srcdir=$OLDPWD --builddir=$PWD --datadir=/tmp/${PWD##*/}-datadir --verbose --enforce-storage-engine=InnoDB
 
Installing MariaDB/MySQL system tables in '/tmp/build-mariadb-server-10.4-datadir' ...
 
2022-11-22  9:16:05 0 [Note] /home/dan/repos/build-mariadb-server-10.4/sql/mysqld (mysqld 10.4.28-MariaDB) starting as process 121834 ...
 
2022-11-22  9:16:05 0 [Note] InnoDB: Using Linux native AIO
 
2022-11-22  9:16:05 0 [Note] InnoDB: The first innodb_system data file 'ibdata1' did not exist. A new tablespace will be created!
 
2022-11-22  9:16:05 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
 
2022-11-22  9:16:05 0 [Note] InnoDB: Uses event mutexes
 
2022-11-22  9:16:05 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
 
2022-11-22  9:16:05 0 [Note] InnoDB: Number of pools: 1
 
2022-11-22  9:16:05 0 [Note] InnoDB: Using SSE2 crc32 instructions
 
2022-11-22  9:16:05 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
 
2022-11-22  9:16:05 0 [Note] InnoDB: Completed initialization of buffer pool
 
2022-11-22  9:16:05 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
 
2022-11-22  9:16:05 0 [Note] InnoDB: Setting file './ibdata1' size to 12 MB. Physically writing the file full; Please wait ...
 
2022-11-22  9:16:05 0 [Note] InnoDB: File './ibdata1' size is now 12 MB.
 
2022-11-22  9:16:05 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 50331648 bytes
 
2022-11-22  9:16:05 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 50331648 bytes
 
2022-11-22  9:16:05 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
 
2022-11-22  9:16:05 0 [Note] InnoDB: New log files created, LSN=11451
 
2022-11-22  9:16:05 0 [Note] InnoDB: Doublewrite buffer not found: creating new
 
2022-11-22  9:16:05 0 [Note] InnoDB: Doublewrite buffer created
 
2022-11-22  9:16:05 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
 
2022-11-22  9:16:05 0 [Note] InnoDB: Creating foreign key constraint system tables.
 
2022-11-22  9:16:05 0 [Note] InnoDB: Creating tablespace and datafile system tables.
 
2022-11-22  9:16:05 0 [Note] InnoDB: Creating sys_virtual system tables.
 
2022-11-22  9:16:05 0 [Note] InnoDB: Creating shared tablespace for temporary tables
 
2022-11-22  9:16:05 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
 
2022-11-22  9:16:05 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
 
2022-11-22  9:16:05 0 [Note] InnoDB: Waiting for purge to start
 
2022-11-22  9:16:05 0 [Note] InnoDB: 10.4.28 started; log sequence number 0; transaction id 7
 
2022-11-22  9:16:05 6 [ERROR] InnoDB: Table `mysql`.`innodb_table_stats` not found.
 
OK

Could remove the InnoDB error in bootstrap. But otherwise works.

10.5

 
$  mkdir -p /tmp/${PWD##*/}-datadir && scripts/mysql_install_db --no-defaults --srcdir=$OLDPWD --builddir=$PWD --datadir=/tmp/${PWD##*/}-datadir --verbose --enforce-storage-engine=InnoDB
 
Installing MariaDB/MySQL system tables in '/tmp/build-mariadb-server-10.5-datadir' ...
 
2022-11-22  9:18:40 0 [Note] /home/dan/repos/build-mariadb-server-10.5/sql/mysqld (mysqld 10.5.19-MariaDB-1:10.5.13+maria~stretch) starting as process 122427 ...
 
..
 
2022-11-22  9:18:40 1 [ERROR] InnoDB: Table `mysql`.`innodb_table_stats` not found.
 
ERROR: 1005  Can't create table `mysql`.`help_relation` (errno: 150 "Foreign key constraint is incorrectly formed")
 
2022-11-22  9:18:40 0 [ERROR] Aborting

10.5+ is a FK sql fix. + once resolved, ER_WRONG_LOCK_OF_SYSTEM_TABLE is triggered for the LOCK TABLES in the scripts/fill_help_tables.sql.

10.5

 
$  mkdir -p /tmp/${PWD##*/}-datadir && scripts/mysql_install_db --no-defaults --srcdir=$OLDPWD --builddir=$PWD --datadir=/tmp/${PWD##*/}-datadir --verbose --enforce-storage-engine=innodb
 
Installing MariaDB/MySQL system tables in '/tmp/build-mariadb-server-10.5-datadir' ...
 
2022-11-22  9:22:07 0 [Note] /home/dan/repos/build-mariadb-server-10.5/sql/mysqld (mysqld 10.5.19-MariaDB-1:10.5.13+maria~stretch) starting as process 122901 
2022-11-22  9:22:07 0 [Note] InnoDB: 10.5.19 started; log sequence number 0; transaction id 7
 
2022-11-22  9:22:07 1 [ERROR] InnoDB: Table `mysql`.`innodb_table_stats` not found.
 
ERROR: 1428  You can't combine write-locking of system tables with other tables or lock types
 
2022-11-22  9:22:07 0 [ERROR] Aborting



 Comments   
Comment by Daniel Black [ 2022-11-22 ]

10.5:

prepare_fk_prelocking_list - adds FK tables referenced in the lock list, to the locking list at TL_READ
Lock_tables_prelocking_strategy::handle_table - resets the open_strategy = TABLE_LIST::OPEN_STUB back to TABLE_LIST::OPEN_NORMAL

By the time lock_tables_check is called its indistinguishable between lock tables help_topic write, help_category write and lock tables help_topic write, help_category write, help_relations read

In 10.4, table->file->referenced_by_foreign_key(), in DML_prelocking_strategy::handle_table (called from Lock_tables_prelocking_strategy::handle_table) returns false.

Comment by Daniel Black [ 2022-11-23 ]

serg, looked at the locking far too much. I put something easy in PR 2342 that relaxes the ER_WRONG_LOCK_OF_SYSTEM_TABLE by a minimal amount. I couldn't differentiate a FK table lock and a normal lock without restructuring a few layers.

Comment by Daniel Black [ 2022-12-19 ]

MDEV-26372 corrected this and it has regressed.

Generated at Thu Feb 08 10:13:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.