CentOS 7

This is also the case in 11.2.1 with the improvements of MDEV-26137

Can you please post a set of files that demonstrates this problem when executed against a newly initialized server? For example, include one or more SQL files that will be fed to the mariadb client. The test could be driven by a number of Bourne shell commands.

Thanks for getting back to me. Sure, please see attached a number of files. After doing this on clean install with a new datadir, with a dump file imported into mariadb this is still the case.

I have attached the scripts I have made.

I have set the backup dir to be the following with the date being the parent folder for each days of the backup /media/backups/DATA_OF_BACKUP. Also, in the script I have attached the permissions for the user

Once you have imported the fire.sql into MariaDB and added the required user run the mariabackup.bash script bash mariabackup.bash (run twice to get a incremental aswell). Next, prepare the backup with the prepare.bash script run as so bash prepare.bash /media/backups/2023-09-27/ --export this will prepare the full backup and apply any incremental backups to the full backup in order, it will also run another prepare with the --export option.

Once the backup is prepared, run the singledb.bash in the same directory as the grabresults.sql which contains the SQL concat statements that will be imported to Mariadb and output four files to /tmp/, it will then run the process to run the four files in the correct order. To run this script do the following bash singledb.bash /media/backups/2023-09-27/fullbackup fire /media/backups/2023-09-27/tablestructure/fire/2023-09-27-TIMEOFBACKUP.sql This should run fine. Next, repeat the process (Mariabackup.bash > prepare.bash > singledb.bash) on the second run it should fail with the following:

cp: cannot stat ‘/media/backups/2023-09-27/fullbackup//fire/*.cfg’: No such file or directory
ERROR 1030 (HY000) at line 1: Got error 194 "Tablespace is missing for a table" from storage engine InnoDB

You will get this error as mariabackup wasn't able to export the .cfg files from the new incremental backup

Please let me know if you run into any issues

all-dbs.sql grabresults.sql mariabackup.bash prepare.bash singledb.bash

Hello,

Is this currently being investigated?

I had some higher-priority tasks until now. I will check this next.

I had some trouble executing the singledb.bash script. It would generate a grabresults2.sql file but somehow fail to execute it as intended, because none of its output files in /tmp would be created. When I execute SQL statements from grabresults2.sql manually, the output files will be created just fine.

I would also like to note that with MDEV-26137 in MariaDB Server 11.2, you can import tables based on .frm files. The CREATE TABLE and ALTER TABLE…DISCARD TABLESPACE steps would be omitted in that case.

Also, I would advise against using bash in scripts. A more restricted implementation of the POSIX /bin/sh (such as dash) should open a smaller attack surface to security bugs like Shellshock.

I can spot one logic flaw in the script. It’s dropping all secondary indexes (why?), then discarding the tablespace, then copying the .cfg and .ibd files, and finally importing the tablespace. In this way, there will be a mismatch regarding the secondary indexes. It would seem to me that the import operation would simply be rejected in this case:

--source include/have_innodb.inc

CREATE TABLE t1 (a INT PRIMARY KEY, b INT, INDEX(b)) ENGINE=InnoDB;

CREATE TABLE t2 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;

ALTER TABLE t2 DISCARD TABLESPACE ;

FLUSH TABLES t1 FOR EXPORT;

let $datadir=`select @@datadir`;

--copy_file $datadir/test/t1.ibd $datadir/test/t2.ibd

--move_file $datadir/test/t1.cfg $datadir/test/t2.cfg

UNLOCK TABLES;

--error ER_TABLE_SCHEMA_MISMATCH

ALTER TABLE t2 IMPORT TABLESPACE;

DROP TABLE t2,t1;

The above .test file that I tried out resulted in the expected error. The table t2 would be missing a tablespace. I think that this must be happening here as well.

If the issue that is being worked around is that IMPORT TABLESPACE is disallowed when FOREIGN KEY constraints exist, then it should suffice to drop and create the FOREIGN KEY constraints only. Neither the .cfg files nor the .ibd files should contain any information about FOREIGN KEY. I tested this as well:

--source include/have_innodb.inc

CREATE TABLE t(a INT PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t1 (a INT PRIMARY KEY, b INT, INDEX(b),

CONSTRAINT f1 FOREIGN KEY (a) REFERENCES t) ENGINE=InnoDB;

CREATE TABLE t2 (a INT PRIMARY KEY, b INT, INDEX(b)) ENGINE=InnoDB;

ALTER TABLE t2 DISCARD TABLESPACE ;

FLUSH TABLES t1 FOR EXPORT;

let $datadir=`select @@datadir`;

--copy_file $datadir/test/t1.ibd $datadir/test/t2.ibd

--move_file $datadir/test/t1.cfg $datadir/test/t2.cfg

UNLOCK TABLES;

ALTER TABLE t2 IMPORT TABLESPACE;

ALTER TABLE t2 ADD CONSTRAINT f2 FOREIGN KEY(a) REFERENCES t(a);

SHOW CREATE TABLE t1;

SHOW CREATE TABLE t2;

DROP TABLE t2,t1,t;

harrypask, can you confirm that this is an issue with your scripts and not a MariaDB Server bug?