Thank you for the report.

On a quick read, it seems to me that the rsync command was executed on files that were not in a ‘clean’ state. Only after the database server had been shut down normally (or mariadb-backup --prepare run on a previously made backup) it is safe to copy data files. Alternatively, you could possibly execute

FLUSH TABLES db1.* FOR EXPORT;

before executing the rsync command and

UNLOCK TABLES;

after the db1/*.ibd files had been copied.

The only other scenario where a file copying tool like rsync is safe to use is when writes to ib_logfile0 and all InnoDB data files are blocked (as in Galera snapshot transfer with wsrep_sst_method=rsync), or when using a file system snapshot. On such a copy, InnoDB crash recovery needs to be invoked by starting MariaDB on the copy of the data directory. That is only possible if you also copy the ib_logfile0 and the InnoDB system tablespace (named ibdata1 by default).

Possibly you could get this workflow a little safer (still not safe) by configuring InnoDB so that changed data files are written more often back to the data files. See MDEV-30000 for some discussion around that.

Can you check if this works when using FLUSH TABLES…FOR EXPORT?

The SIGFPE (signal 8) could be the result of a division by zero. We would need a resolved stack trace of the crashing thread to find out the cause. https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mariadbd/ explains how. I think that this bug could be still there in the currently latest 10.6.17 release; MDEV-29972 had fixed a SIGSEGV, not SIGFPE.

For importing tablespaces, you should also copy the .cfg files so that some additional consistency checks can be performed. InnoDB tables are always index-organized, so an incorrect definition of a PRIMARY KEY will lead to total failure to access data files. When there is no PRIMARY KEY, InnoDB will create a hidden one, using a 48-bit counter as the primary key.

Ok, new test case with "clean state" (sorry, for missing that, I should actually know that).

#3 Test Case

• Check database integrity

mysqlcheck db1 questionnaire

db1.questionnaire: OK

• Stop MariaDB

• Backup the database files:

rsync -av /var/lib/mysql/db1 /var/tmp/

• Start MariaDB

• Recreate database db1:

DROP DATABASE db1;

CREATE DATABASE db1;

• Create the database schema without secondary indexes (excerpt):

CREATE TABLE `questionnaire` (

  `id` varchar(64) NOT NULL,

   .....

  `created` datetime DEFAULT NULL,

  `updated` datetime DEFAULT NULL,

  `deleted` tinyint(1) unsigned NOT NULL DEFAULT 0,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `questionnaire` DISCARD TABLESPACE;

cp -p /var/tmp/db1/*.ibd /var/lib/mysql/db1/

ALTER TABLE `questionnaire` IMPORT TABLESPACE;

• Check database integrity:

mysqlcheck db1 questionnaire

Warning  : InnoDB: The B-tree of index PRIMARY is corrupted.

error    : Corrupt

Is there any way to debug this further? Why could the PRIMARY key gets corrupted? It happens on two tables.

For the second case I've attached a stack trace.

stacktrace.txt

I think marko was saying that when you do export ,
you have to have having
FLUSH TABLES db1.* FOR EXPORT; runnning and not executed UNLOCK TABLES; in same session. If session ends UNLOCK TABLES is executed automatically.

Check that you get also *.cfg - files. I think those are deleted when tables are unlocked.

Second, when you create table. I think that you must have all things similar in table definition as in source.
I have seen cases (with old tables) that `show create table ... ` doesn't give enough information for CRETE TABLE like missing ROW_FORMAT (can get from information_schema.tables).

Also DATIME/TIME/TIMESTAMP columns have been using pre-mysql.5.6 format, which cannot be told in create table - statement.

Our primary issue is that the restoration of our partial backups, created with mariabackup, fails.

The restoration process briefly:

1. /usr/bin/mariabackup --prepare --target-dir $RESTORE_TMP_PATH --incremental-dir $RESTORE_TMP_PATH/$INCREMENTAL_DIR/
2. Creating a fresh database with a schema exported from mysqldump.
3. Discarding tablespace for tables.
4. Moving .ibd files from the restore temporary path to the database directory.
5. Importing tablespace.
6. Performing a row count for verification: questionnaire 167446
7. Doing a mysqlcheck for verification: db1.questionnaire OK
8. Everything seems fine, but after restarting MariaDB, mysqlcheck for two tables fails with the same reason:

db1.questionnaire Warning : InnoDB: Index PRIMARY is marked as corrupted error : Corrupt

With my test cases above, I aimed to investigate the root cause of this problem.

The source databases are fine. We have tested numerous backups from various times, and it's always the same two tables that present issues, both in the test and production databases, with data differing between them.

Side note: You can create a complete dump of the restored database without any issue. After restarting you have no chance due the corrupted indexes.

bluebike, thank you for stepping in. I should have remembered MDEV-15225, which was fixed by MDEV-26137 (MariaDB Server 11.2.1) by making it possible to import tables based on an existing .frm file. That is, the CREATE TABLE and ALTER TABLE…DISCARD TABLESPACE steps would be optional, and ALTER TABLE…IMPORT TABLESPACE can work based on a .frm file that you copy to the data directory.

When using the CREATE TABLESPACE workflow, you have to set mysql56_temporal_format in the same way as it was when the table is originally created. If I remember correctly, SHOW CREATE TABLE will not in any way distinguish the actual column type. You may even have date or time columns in different formats if ALTER TABLE…ADD COLUMN was executed using different values of mysql56_temporal_format.

I would hope that the inconsistent column type is caught when you also copy the .cfg file. Is that the case?

wursterje, thank you for the stacktrace.txt. The SIGFPE is triggered here:

#4  0x00005643929f34f3 in dict_stats_index_set_n_diff (index_stats=@0x7f3a3c567850: {stats = std::vector of length 1, capacity 1 = {{n_diff_key_vals = 0, n_sample_sizes = 1, n_non_null_key_vals = 0}}, index_size = 804, n_leaf_pages = 725}, n_diff_data=<optimized out>) at ./storage/innobase/dict/dict0stats.cc:2651

        data = 0x7f39d4150df8

        n_ordinary_leaf_pages = <optimized out>

        n_prefix = <optimized out>

#5  dict_stats_analyze_index (index=<optimized out>) at ./storage/innobase/dict/dict0stats.cc:2979

It would seem to be a division by 0 (data->n_recs_on_level).

Even if we could claim that the rest is not a bug but the expected result of ‘misuse’ of a badly designed interface, this crash is something that we can fix.

Just for my understanding: If a different value for mysql56_temporal_format is the reason, than I'd expect that every table with a datetime column gets corrupted, right? I ask this because we have tables with datetime columns where everything is fine.

On both systems (server and recovery test) mysql56_temporal_format is set to ON. And both systems are fresh MariaDB 10.6 instances. Data was imported via dumps so there is no underlying upgrade history.

Here are the table definitions from the affecting columns (for internal reasons I obfuscated the real column names):

CREATE TABLE `questionnaire` (

  `id` varchar(64) NOT NULL,

  `column2` varchar(64) DEFAULT NULL,

  `column3` varchar(64) DEFAULT NULL,

  `column4` varchar(64) DEFAULT NULL,

  `column5` tinyint(1) unsigned DEFAULT NULL,

  `column6` tinyint(1) unsigned DEFAULT NULL,

  `column7` varchar(250) DEFAULT NULL,

  `column8` text DEFAULT NULL,

  `column9` text DEFAULT NULL,

  `column10` text DEFAULT NULL,

  `column11` text DEFAULT NULL,

  `column12` text DEFAULT NULL,

  `column13` text DEFAULT NULL,

  `column14` varchar(50) DEFAULT NULL,

  `column15` datetime DEFAULT NULL,

  `column16` varchar(50) DEFAULT NULL,

  `column17` char(1) DEFAULT NULL,

  `column18` varchar(50) DEFAULT NULL,

  `column19` varchar(50) DEFAULT NULL,

  `column20` varchar(50) DEFAULT NULL,

  `column21` varchar(50) DEFAULT NULL,

  `column22` varchar(300) DEFAULT NULL,

  `column23` varchar(300) DEFAULT NULL,

  `column24` varchar(300) DEFAULT NULL,

  `column25` varchar(300) DEFAULT NULL,

  `column26` varchar(300) DEFAULT NULL,

  `column27` varchar(300) DEFAULT NULL,

  `column28` varchar(300) DEFAULT NULL,

  `column29` varchar(300) DEFAULT NULL,

  `column30` varchar(300) DEFAULT NULL,

  `column31` varchar(500) DEFAULT NULL,

  `column32` varchar(500) DEFAULT NULL,

  `column33` varchar(500) DEFAULT NULL,

  `column34` varchar(500) DEFAULT NULL,

  `column35` varchar(500) DEFAULT NULL,

  `column36` varchar(500) DEFAULT NULL,

  `column37` varchar(500) DEFAULT NULL,

  `column38` varchar(500) DEFAULT NULL,

  `column39` varchar(500) DEFAULT NULL,

  `column40` varchar(300) DEFAULT NULL,

  `column41` varchar(500) DEFAULT NULL,

  `column42` varchar(300) DEFAULT NULL,

  `column43` int(11) unsigned DEFAULT NULL,

  `column44` tinyint(1) unsigned NOT NULL DEFAULT 0,

  `column45` int(11) unsigned NOT NULL DEFAULT 0,

  `column46` longblob DEFAULT NULL,

  `column47` int(11) unsigned NOT NULL DEFAULT 0,

  `column48` datetime DEFAULT NULL,

  `column49` datetime DEFAULT NULL,

  `column50` tinyint(1) unsigned NOT NULL DEFAULT 0,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `survey` (

  `id` varchar(64) NOT NULL,

  `column2` varchar(64) DEFAULT NULL,

  `column3` varchar(64) DEFAULT NULL,

  `column4` varchar(64) DEFAULT NULL,

  `column5` varchar(250) DEFAULT NULL,

  `column6` datetime DEFAULT NULL,

  `column7` datetime DEFAULT NULL,

  `column8` char(1) DEFAULT NULL,

  `column9` varchar(10) DEFAULT NULL,

  `column10` varchar(50) DEFAULT NULL,

  `column11` int(11) unsigned DEFAULT NULL,

  `column12` int(11) unsigned DEFAULT NULL,

  `column13` int(11) unsigned DEFAULT NULL,

  `column14` int(11) unsigned NOT NULL DEFAULT 0,

  `column15` int(11) unsigned NOT NULL DEFAULT 0,

  `column16` int(11) unsigned NOT NULL DEFAULT 0,

  `column17` int(11) unsigned NOT NULL DEFAULT 0,

  `column18` int(11) unsigned NOT NULL DEFAULT 0,

  `column19` int(11) unsigned NOT NULL DEFAULT 0,

  `column20` int(11) unsigned NOT NULL DEFAULT 0,

  `column21` int(11) unsigned NOT NULL DEFAULT 0,

  `column22` int(11) unsigned NOT NULL DEFAULT 0,

  `column23` int(11) unsigned NOT NULL DEFAULT 0,

  `column24` tinyint(1) unsigned NOT NULL DEFAULT 0,

  `column25` int(11) unsigned DEFAULT NULL,

  `column26` int(11) unsigned NOT NULL DEFAULT 0,

  `column27` tinyint(1) unsigned NOT NULL DEFAULT 0,

  `column28` tinyint(1) unsigned NOT NULL DEFAULT 0,

  `column29` datetime DEFAULT NULL,

  `column30` datetime DEFAULT NULL,

  `column31` tinyint(1) unsigned NOT NULL DEFAULT 0,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

And I'm still wondering why the PRIMARY Index gets corrupted after restarting MariaDB.

I've tested the command "FLUSH TABLES db1.* FOR EXPORT;" to retrieve the configuration files. I realized that I was using a schema that was newer than the database, leading to a mismatch between the columns. After resolving this issue, everything is working as expected. My apologies for the confusion.

Currently, I'm investigating why the MariaDB backup restore is failing and causing PRIMARY index corruptions after restarting MariaDB. After the preparation phase, I am unable to locate the *.cfg files, so I can only copy back the *.ibd files, right?

wursterje, there is an option mariadb-backup --prepare --export that should be used as part of restoring a partial backup.

Off the top of my head, I can’t tell what would go wrong if you copied files after executing mariadb-backup --prepare. I think that it should be possible to import the .ibd file, but only if there are no secondary indexes in the table. We added this restriction to fix MDEV-26131. Only if you have .cfg files you can import tables that contain secondary indexes.

If you are copying .ibd files from a backup on which no mariadb-backup --prepare was executed (so that the changes from ib_logfile0 would be applied), then those files may be severely corrupted.

wursterje, did you try mariadb-backup --prepare --export so that you would have .cfg files too? Would the schema validation raise an error in that case?

@marko The --export option is very useful and makes the restore process a lot easier. Sadly our main problem is still there:

Before restart:

db1_prod.questionnaire               OK

After restart:

db1_prod.questionnaire

Warning  : InnoDB: Index PRIMARY is marked as corrupted

Warning  : InnoDB: Index questionnaire_FK is marked as corrupted

Warning  : InnoDB: Index questionnaire_FK_1 is marked as corrupted

Warning  : InnoDB: Index questionnaire_FK_2 is marked as corrupted

error    : Corrupt 

I've attached the complete log of our automatic restore process for this database.

mariadb-restore.log

I found some more information in the MariaDB error.log:

2024-02-27 10:47:35 39 [ERROR] InnoDB: Metadata record is missing

2024-02-27 10:47:35 39 [ERROR] InnoDB: Apparent corruption in space 24 page 9 of index `PRIMARY` of table `db1_prod`.`questionnaire`

2024-02-27 10:47:35 39 [ERROR] InnoDB: In page 9 of index `PRIMARY` of table `db1_prod`.`questionnaire`

2024-02-27 10:48:47 39 [ERROR] InnoDB: Metadata record is missing

2024-02-27 10:48:47 39 [ERROR] InnoDB: Apparent corruption in space 31 page 4094 of index `PRIMARY` of table `db1_prod`.`survey`

2024-02-27 10:48:47 39 [ERROR] InnoDB: In page 4094 of index `PRIMARY` of table `db1_prod`.`survey`

2024-02-27 11:18:43 5 [ERROR] InnoDB: Table `db1_prod`.`questionnaire` is missing instant ALTER metadata

2024-02-27 11:19:49 5 [ERROR] InnoDB: Table `db1_prod`.`survey` is missing instant ALTER metadata

wursterje, thank you, we are narrowing down the problem. It would be great if you could upload some (as small as possible) files so that we can repeat the steps and then diagnose it in detail. Ideally, you would supply a script where the data is first loaded, then the file exported and imported.

The Metadata record that the error log mentions is related to MDEV-11369 or MDEV-15562, that is, instant ADD COLUMN or DROP COLUMN or changing the ordering of columns. Exporting and importing such files is supposed to work. The data dictionary will correspond to the latest definition of the table, and inside the data file, at the start of the PRIMARY KEY index (or clustered index) we have a hidden record that identifies the original format of the index. Some records in the clustered index may be in the original format, while others may contain added columns at the end, identified by an additional record header. The metadata record is necessary for being able to interpret other records in the table.

I quickly tried to see if this bug can be easily reproduced by editing a regression test:

diff --git a/mysql-test/suite/innodb/t/instant_alter_import.test b/mysql-test/suite/innodb/t/instant_alter_import.test

index 87a36186150..2a70a1bcf03 100644

--- a/mysql-test/suite/innodb/t/instant_alter_import.test

+++ b/mysql-test/suite/innodb/t/instant_alter_import.test

@@ -61,6 +61,8 @@ unlock tables;

 --echo # The instant ADD COLUMN has to be removed from the metadata.

 alter table t1 import tablespace;

 select * from t1;

+--source include/restart_mysqld.inc

+select * from t1;

 --echo # Remove metadata for instant DROP COLUMN, then import

 alter table t1 drop x, add column x int first, algorithm instant;

Unfortunately, it is not that simple: the test will return the same result also after the additional restart.

The schema was fresh created last year, only two alter statements where applied after that. But the tables where the alter statements where applied are the problematic ones, at least in the production not in the testing database. The statement looked like:

ALTER TABLE survey ADD archived TINYINT(1) UNSIGNED DEFAULT 0 NOT NULL;

ALTER TABLE survey CHANGE archived archived TINYINT(1) UNSIGNED DEFAULT 0 NOT NULL AFTER columnx;

Not sure if this messed up something internally.

Unfortunately, I'm unsure which files I should provide to you. The backup of these tables are quite big. I could run a customized MariaDB server with some debugging outputs during the restore process of the backups?

wursterje, I would need the .frm, .cfg and .ibd files so that I could execute the problematic import myself and debug it in detail.

I assume you're referring only to the problematic tables, correct? I can provide a download (approximately 2 GB) for you and send the link to your email address. I just need to speak with our customer and obtain permission.

We are trying to track down repeated index corruption in 10.11.5 (and 10.11.6). This seems like the closest bug report I have been able to find. We are seeing this in both our testing environment and in our production environment.

Do we think this is still likely in 10.11.x?

If so, what would be useful for us to provide?

Sorry, this is actually waiting for me to test with the data files that wursterje provided. I downloaded them some weeks ago but did not have time to check them yet. I will try to do that soon, now that I am done with some more urgent work.

Good news: I can reproduce this easily with the files that wursterje provided. I used the 11.2 version so that I have a simpler MDEV-26137 workflow, simply moving the files questionnaire.* to the data directory and executing

ALTER TABLE questionnaire IMPORT TABLESPACE;

CHECK TABLE questionnaire EXTENDED;

This did not report any errors. After I restarted the server and executed the CHECK TABLE again, I got the following error:

2024-04-09 16:22:46 3 [ERROR] InnoDB: Table `test`.`questionnaire` is missing instant ALTER metadata

mariadbd: /mariadb/11/storage/innobase/dict/dict0load.cc:2529: dict_table_t* dict_load_table_one(const st_::span<const char>&, dict_err_ignore_t, dict_names_t&): Assertion `!table || (ignore_err & ~DICT_ERR_IGNORE_FK_NOKEY) || !table->is_readable() || !table->corrupted' failed.

The table->corrupted had been set here:

#0  0x000055664c9154b0 in btr_cur_instant_init_low (index=index@entry=0x7fbc3004a040, mtr=mtr@entry=0x7fbff3dfc738) at /mariadb/11/storage/innobase/btr/btr0cur.cc:255

#1  0x000055664c915c74 in btr_cur_instant_init (table=0x7fbc300379e0) at /mariadb/11/storage/innobase/btr/btr0cur.cc:463

#2  0x000055664c94721c in dict_load_table_one (name=@0x7fbff3dfd250: {data_ = 0x7fbff3dfda10 "test/questionnaire", size_ = 18}, ignore_err=ignore_err@entry=DICT_ERR_IGNORE_FK_NOKEY, fk_tables=std::deque with 0 elements) at /mariadb/11/storage/innobase/dict/dict0load.cc:2493

#3  0x000055664c9475e9 in dict_sys_t::load_table (this=this@entry=0x55664cd81140 <dict_sys>, name=@0x7fbff3dfd250: {data_ = 0x7fbff3dfda10 "test/questionnaire", size_ = 18}, ignore=ignore@entry=DICT_ERR_IGNORE_FK_NOKEY) at /mariadb/11/storage/innobase/dict/dict0load.cc:2563

#4  0x000055664c8970ca in dict_table_open_on_name (table_name=table_name@entry=0x7fbff3dfda10 "test/questionnaire", dict_locked=dict_locked@entry=false, ignore_err=ignore_err@entry=DICT_ERR_IGNORE_FK_NOKEY) at /mariadb/11/storage/innobase/dict/dict0dict.cc:1065

#5  0x000055664c85eab4 in ha_innobase::open_dict_table (norm_name=norm_name@entry=0x7fbff3dfda10 "test/questionnaire", is_partition=is_partition@entry=false, ignore_err=ignore_err@entry=DICT_ERR_IGNORE_FK_NOKEY) at /mariadb/11/storage/innobase/handler/ha_innodb.cc:6162

#6  0x000055664c873422 in ha_innobase::open (this=0x7fbc30033160, name=0x7fbc3003a158 "./test/questionnaire") at /mariadb/11/storage/innobase/handler/ha_innodb.cc:5867

I see that at the time of the crash, page 9 seems to be the leftmost leaf page of the clustered index. The first record payload starts at byte offset 0xc5. This looks like a normal user record, not the hidden metadata record that should describe the instantly added columns. I checked the difference of the page 9 image before and after the IMPORT TABLESPACE, and it includes the following:

-000060 02 00 22 69 6e 66 69 6d 75 6d 00 03 00 0b 00 00

+000060 02 00 62 69 6e 66 69 6d 75 6d 00 02 00 0b 00 00

 000070 73 75 70 72 65 6d 75 6d 00 7f ff ff ff ff ff 01

-000080 30 00 14 00 40 00 00 00 00 00 00 80 00 00 00 00

-000090 00 00 00 00 06 d6 00 02 8e b8 00 00 00 26 00 00

-0000a0 00 00 00 00 00 6a 00 00 00 00 00 00 00 00 00 00

+000080 30 00 14 00 00 00 00 00 00 00 00 00 00 00 00 00

+000090 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00

+*

 0000b0 5b 9b 01 01 00 04 18 24 20 24 07 ff ff fd 8f 9a

 0000c0 00 00 18 ff ab 30 30 30 30 31 35 33 38 2d 64 64

The successor of the infimum pseudo-record used to point to 0x85 and not 0xc5. At 0x80 we can see the MDEV-15562 metadata record marker. It looks like the IMPORT TABLESPACE code wrongly removed this record. I will debug this in more detail tomorrow, covering the IMPORT execution in rr record (https://rr-project.org/).

The fix of MDEV-18543 was incomplete. I can reproduce this with the following change to an existing test:

diff --git a/mysql-test/suite/innodb/t/instant_alter_import.test b/mysql-test/suite/innodb/t/instant_alter_import.test

index 7bd5645436c..50eda83f4b0 100644

--- a/mysql-test/suite/innodb/t/instant_alter_import.test

+++ b/mysql-test/suite/innodb/t/instant_alter_import.test

@@ -79,6 +79,7 @@ flush tables t2 for export;

 unlock tables;

 alter table t1 import tablespace;

+--source include/restart_mysqld.inc

 select * from t1;

 drop table t2;

CURRENT_TEST: innodb.instant_alter_import

mysqltest: At line 83: query 'select * from t1' failed: 2013: Lost connection to MySQL server during query

…

mysqld: /mariadb/10.4/storage/innobase/include/dict0mem.h:2373: bool dict_index_t::is_instant() const: Assertion `n_core_fields <= n_fields || table->n_dropped()' failed.

I filed https://github.com/MariaDB/server/pull/3190 for the fix.

thanks for approving the changes on github. What does the "Stalled" status here mean?

The "stalled" ticket state in this system can mean almost anything. The states do not even distinguish between "review approved" and "review rejected". In this case, we thought that the fix can be applied without subjecting it to additional stress testing.

Thanks @Marko.

Will there also be a fix for our affecting version 10.6. (our main problem exists on that version)?

Yes, the 10.4 branch will be merged to all other branches before the upcoming maintenance releases, which should be out in a couple of weeks.