mysqldump and "Can't find record" table errors on restore

Question

I've been using the following mysqldump command to backup my mariadb databases for several years, and never had a problem restoring. Until today. Here is the command I've been using for one of the databases:

/usr/bin/mysqldump --complete-insert --compress --add-drop-table --lock-tables --verbose -u root -ppass --log-error=backup-mydb-2022-10-29-2240418-error.log --result-file=backup-mydb-2022-10-29-2240418.sql mydb

The error file shows no errors.

-- Retrieving table structure for table xu33gc_dictionary_letters...
-- Sending SELECT query...
-- Retrieving rows...

I used the following for restore:

mysql -u root -ppass mydb < backup-mydb-2022-10-29-2240418.sql

Upon restore, it resulted in the following:

ERROR 1032 (HY000) at line 9413: Can't find record in 'xu33gc_dictionary_letters'

A simple grep through the sql backup above finds:

grep xu33gc_dictionary_letters backup-mydb-2022-10-29-2240418.sql
-- Table structure for table `xu33gc_dictionary_letters`
DROP TABLE IF EXISTS `xu33gc_dictionary_letters`;
CREATE TABLE `xu33gc_dictionary_letters` (
-- Dumping data for table `xu33gc_dictionary_letters`
LOCK TABLES `xu33gc_dictionary_letters` WRITE;
/*!40000 ALTER TABLE `xu33gc_dictionary_letters` DISABLE KEYS */;
INSERT INTO `xu33gc_dictionary_letters` (`id`, `letter_name`) VALUES (1,'A'),(2,'B'), 
(3,'C'),(4,'D'),(5,'E'),(6,'F'),(7,'G'),(8,'H'),(9,'I'),(10,'J'),(11,'K'),(12,'L'), 
... [cut for brevity]
/*!40000 ALTER TABLE `xu33gc_dictionary_letters` ENABLE KEYS */;

So the table is there. What could be the problem? Even more strange is that I ran the same backup again, then the same restore, and it produced a different problem:

ERROR 1005 (HY000) at line 21042: Can't create table `mydb`.`xu33gc_tj_notification_user_exclusions` (errno: 150 "Foreign key constraint is incorrectly formed")

I'm also backing up using mariabackup, but it's much more involved and difficult to restore because of having to do all the differentials.

Answer 1

My suspicion is:

• The lack of --single-transaction as a mysqldump option means that an inconsistent snapshot is being created. As theorized below:
• At some point while during the dump, a row and its foreign key where deleted.
• mysqldump completed one table without the row, and the other table with the row.
• on attempting to restore, at the point it enables FKs again, there is no complete relationship hence the ER_KEY_NOT_FOUND error.

Note: could have been an insert too, the insert was done on a table which wasn't backed up, and then on the FK with the backup completed, generating the same inconsistency on restore.

So importantly, use --single-transaction with backups.