0

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.

Alex Regan
  • 477
  • 5
  • 16
  • I could be totally wrong in my answer. What does `show create table xu33gc_tj_notification_user_exclusions` show? Which MariaDB 10.3 version? Is possible that its generating an output it cannot parse. – danblack Nov 01 '22 at 23:45
  • Including the definition of the table it has a FK relationship to could also be useful. – danblack Nov 02 '22 at 01:56

1 Answers1

0

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.

danblack
  • 12,130
  • 2
  • 22
  • 41
  • Very good, thanks. I will test that. Isn't that what --lock-tables is for, to prevent changes from occuring during the dump process? – Alex Regan Nov 01 '22 at 22:28
  • Your right, `--lock-tables` explicitly `LOCK TABLES .... ` (all tables accessed in the backup). If its all innodb, `--single-transaction` leaves data writeable while still obtaining a backup. The problem might be elsewhere. – danblack Nov 01 '22 at 23:47
  • Instead of trying to fix this method, what should I be doing instead? How is mysqlbackup intended to be used properly? – Alex Regan Nov 02 '22 at 01:31