While restoring from mysqldump file, how do I write the table names to a log file?

Question

Sometimes I've run into situations where developers have changed structures, added/removed columns on different DB servers, environments, etc for the same table and this causes errors such as ERROR 1136 (21S01): Column count doesn't match value count at row 1.

At other times, there are data type mismatch errors. I usually write the output of a mysqldump restore to a log file, but I cannot find the table names in this log, just Query OK, Warning and/or Error messages.

I'd like to be able to instantly see which table caused the errors. Of course I can write a custom bash script, but it would be easier to be able to log the current table name into the log file.

Edit (20230619) :

I understand that mysqldump restore is done through the standard mysql client, which basically runs any queries in the dump sql file, which is why expecting it to log table names is not logical. My question is still relevant, maybe there is a tool out there that does this?

Answer 1

If the structure has changed you shouldn't restore from an old dump, you will overwrite the changes.

I suggest use the mysqldump command as follows

sudo mysqldump -u root --complete-insert --no-create-info db_name > test_dump.sql

test_dump.sql now will have the following structure

LOCK TABLES `my_table` WRITE;
/*!40000 ALTER TABLE `my_table` DISABLE KEYS */;
INSERT INTO `my_table` (`id`, `phone_number`, `start_epoch`, `status_code`, `status_name`, `end_epoch`, `cid`, `reg_date`) VALUES (1,'xxxxxxxxx','25/11/2020 09:01:37','120','KO - Irreperibile/Spento Irraggiungibile/fax/segre','25/11/2020 09:01:37','xxxxxxxx','2020-11-26 10:48:42'),(2,'xxxxxxxxxxxx','25/11/2020 09:01:45','106','KO - Gia Cliente','25/11/2020 09:02:50','xxxxxxx','2020-11-26 10:48:42'),(3,'xxxxxxxxxx','25/11/2020 09:03:02','102','KO - Rifiuta Ascolto Offerta','25/11/2020 09:04:03','xxxxxxx','2020-11-26 10:48:42')

• --complete-insert

Use complete INSERT statements that include column names.

• --no-create-info, -t

Do not write CREATE TABLE statements that create each dumped table.

Note This option does not exclude statements creating log file groups or tablespaces from mysqldump output; however, you can use the --no-tablespaces option for this purpose.

Note. If the insert statement contains same data (primary key/unique key) , duplicate value... error will be thrown