[MDEV-10846] Running mysqldump backup twice returns error: Table 'mysql.proc' doesn't exist Created: 2016-09-20  Updated: 2016-10-27  Resolved: 2016-10-26

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Procedure, Scripts & Clients
Affects Version/s: 10.1.17, 10.0, 10.1
Fix Version/s: 10.0.28

Type: Bug Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Alexey Botchkov
Resolution: Fixed Votes: 0
Labels: mysqldump

Issue Links:
Duplicate
is duplicated by MDEV-11098 main.mysqldump is not re-executable Closed
PartOf
includes MDEV-11105 Table named 'db' has weird side effect. Closed
Sprint: 10.0.28

 Description   

Before doing anything, I recreated a fresh datadir with the following commands:

sudo systemctl stop mariadb
 
sudo rm -fr /var/lib/mysql/*
 
sudo mysql_install_db --user=mysql
 
sudo systemctl start mariadb

And then I took a backup with the following command:

mysqldump -u root --all-databases --events --routines --triggers --add-drop-database --single-transaction > /tmp/backupTest.sql

And now watch what happens if I try to load this backup twice:

$ mysql -u root --execute="show tables;" mysql
 
+---------------------------+
 
| Tables_in_mysql           |
 
+---------------------------+
 
| column_stats              |
 
| columns_priv              |
 
| db                        |
 
| event                     |
 
| func                      |
 
| general_log               |
 
| gtid_slave_pos            |
 
| help_category             |
 
| help_keyword              |
 
| help_relation             |
 
| help_topic                |
 
| host                      |
 
| index_stats               |
 
| innodb_index_stats        |
 
| innodb_table_stats        |
 
| plugin                    |
 
| proc                      |
 
| procs_priv                |
 
| proxies_priv              |
 
| roles_mapping             |
 
| servers                   |
 
| slow_log                  |
 
| table_stats               |
 
| tables_priv               |
 
| time_zone                 |
 
| time_zone_leap_second     |
 
| time_zone_name            |
 
| time_zone_transition      |
 
| time_zone_transition_type |
 
| user                      |
 
+---------------------------+
 
$ mysql -u root < /tmp/backupTest.sql
 
$ mysql -u root --execute="show tables;" mysql
 
+---------------------------+
 
| Tables_in_mysql           |
 
+---------------------------+
 
| column_stats              |
 
| columns_priv              |
 
| db                        |
 
| event                     |
 
| func                      |
 
| general_log               |
 
| gtid_slave_pos            |
 
| help_category             |
 
| help_keyword              |
 
| help_relation             |
 
| help_topic                |
 
| host                      |
 
| index_stats               |
 
| innodb_index_stats        |
 
| innodb_table_stats        |
 
| plugin                    |
 
| proc                      |
 
| procs_priv                |
 
| proxies_priv              |
 
| roles_mapping             |
 
| servers                   |
 
| slow_log                  |
 
| table_stats               |
 
| tables_priv               |
 
| time_zone                 |
 
| time_zone_leap_second     |
 
| time_zone_name            |
 
| time_zone_transition      |
 
| time_zone_transition_type |
 
| user                      |
 
+---------------------------+
 
$ mysql -u root < /tmp/backupTest.sql
 
ERROR 1146 (42S02) at line 22: Table 'mysql.proc' doesn't exist
 
$ mysql -u root --execute="show tables;" mysql

The error message above says that the error occurs at line 22. The statement at this line is:

$ sed -n '22p' /tmp/backupTest.sql
 
/*!40000 DROP DATABASE IF EXISTS `mysql`*/;

Why does the MariaDB think the mysql.proc table doesn't exist at this point?

Why does the backup work the first time, but not the second time?

The backup works when I run it a third time.

$ mysql -u root < /tmp/backupTest.sql
 
$ mysql -u root < /tmp/backupTest.sql
 
ERROR 1146 (42S02) at line 22: Table 'mysql.proc' doesn't exist
 
$ mysql -u root < /tmp/backupTest.sql



 Comments   
Comment by Elena Stepanova [ 2016-09-25 ]

Reproducible also on 10.0, even on 10.0.10.
Not reproducible on 5.5, MySQL 5.6.

holyfoot, could you please take a look at it?

Comment by Sergey Vojtovich [ 2016-10-21 ]

See also MDEV-11098

Comment by Alexey Botchkov [ 2016-10-22 ]

Patch proposal:
http://lists.askmonty.org/pipermail/commits/2016-October/010005.html

Comment by Alexey Botchkov [ 2016-10-22 ]

The fix itself:
http://lists.askmonty.org/pipermail/commits/2016-October/010005.html

and small addition for the test
http://lists.askmonty.org/pipermail/commits/2016-October/010006.html

Generated at Thu Feb 08 07:45:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.