[Commits] 2828c2b: MDEV-9124 mysqldump does not dump data if table name is same as view earlier on

elenst at montyprogram.com elenst at montyprogram.com
Fri Nov 13 03:32:09 EET 2015


revision-id: 2828c2be554b62646fc990ac28b4aef20cd9b9d2
parent(s): a430df3aba59c57b0756c25b1586d880d19286df
committer: Elena Stepanova
branch nick: 10.1
timestamp: 2015-11-13 03:23:22 +0200
message:

MDEV-9124 mysqldump does not dump data if table name is same as view earlier on

While querying INFORMATION SCHEMA, check for a table's engine
only used table name, but not schema name; so, if there were different
rows with the same table name, a wrong one could be retrieved.
The result of the check affected the decision whether the contents
of the table should be dumped, and whether a DELAYED option can be used.
Fixed by adding a clause for table_schema to the query.

---
 client/mysqldump.c            |    2 +-
 mysql-test/r/mysqldump.result |  162 +++++++++++++++++++++++++++++++++++++++++
 mysql-test/t/mysqldump.test   |   35 +++++++++
 3 files changed, 198 insertions(+), 1 deletion(-)

diff --git a/client/mysqldump.c b/client/mysqldump.c
index 05ef70a..cffa5bf 100644
--- a/client/mysqldump.c
+++ b/client/mysqldump.c
@@ -5468,7 +5468,7 @@ char check_if_ignore_table(const char *table_name, char *table_type)
   DBUG_ASSERT(2*sizeof(table_name) < sizeof(show_name_buff));
   my_snprintf(buff, sizeof(buff),
               "SELECT engine FROM INFORMATION_SCHEMA.TABLES "
-              "WHERE table_name = %s",
+              "WHERE table_schema = DATABASE() AND table_name = %s",
               quote_for_equal(table_name, show_name_buff));
   if (mysql_query_with_error_report(mysql, &res, buff))
   {
diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result
index c6f7fdd..bcf099a 100644
--- a/mysql-test/r/mysqldump.result
+++ b/mysql-test/r/mysqldump.result
@@ -5329,3 +5329,165 @@ select * from t1;
 a
 1
 drop table t1;
+#
+# MDEV-9124 mysqldump does not dump data if table name is same as view earlier on
+#
+CREATE DATABASE db1 CHARSET=utf8;
+CREATE DATABASE db2 CHARSET=utf8;
+USE db2;
+CREATE TABLE nonunique_table_name (i1 serial) ENGINE=MEMORY;
+INSERT INTO nonunique_table_name VALUES (1),(2);
+CREATE TABLE nonunique_table_view_name (i2 int) ENGINE=InnoDB;
+INSERT INTO nonunique_table_view_name VALUES (3),(4);
+use db1;
+CREATE TABLE basetable (id smallint) ENGINE=MyISAM;
+CREATE TABLE nonunique_table_name (i3 smallint) ENGINE=MERGE UNION (basetable) INSERT_METHOD=LAST;
+INSERT INTO nonunique_table_name VALUES (5),(6);
+CREATE VIEW nonunique_table_view_name AS SELECT 1;
+
+##################################################
+# --compact --databases db1 db2
+
+CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db1` /*!40100 DEFAULT CHARACTER SET utf8 */;
+
+USE `db1`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `basetable` (
+  `id` smallint(6) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+INSERT INTO `basetable` VALUES (5),(6);
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `nonunique_table_name` (
+  `i3` smallint(6) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`basetable`);
+/*!40101 SET character_set_client = @saved_cs_client */;
+SET @saved_cs_client     = @@character_set_client;
+SET character_set_client = utf8;
+/*!50001 CREATE TABLE `nonunique_table_view_name` (
+  `1` tinyint NOT NULL
+) ENGINE=MyISAM */;
+SET character_set_client = @saved_cs_client;
+
+CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db2` /*!40100 DEFAULT CHARACTER SET utf8 */;
+
+USE `db2`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `nonunique_table_name` (
+  `i1` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+  UNIQUE KEY `i1` (`i1`)
+) ENGINE=MEMORY AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+INSERT INTO `nonunique_table_name` VALUES (1),(2);
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `nonunique_table_view_name` (
+  `i2` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+INSERT INTO `nonunique_table_view_name` VALUES (3),(4);
+
+USE `db1`;
+/*!50001 DROP TABLE IF EXISTS `nonunique_table_view_name`*/;
+/*!50001 SET @saved_cs_client          = @@character_set_client */;
+/*!50001 SET @saved_cs_results         = @@character_set_results */;
+/*!50001 SET @saved_col_connection     = @@collation_connection */;
+/*!50001 SET character_set_client      = utf8 */;
+/*!50001 SET character_set_results     = utf8 */;
+/*!50001 SET collation_connection      = utf8_general_ci */;
+/*!50001 CREATE ALGORITHM=UNDEFINED */
+/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
+/*!50001 VIEW `nonunique_table_view_name` AS select 1 AS `1` */;
+/*!50001 SET character_set_client      = @saved_cs_client */;
+/*!50001 SET character_set_results     = @saved_cs_results */;
+/*!50001 SET collation_connection      = @saved_col_connection */;
+
+USE `db2`;
+
+##################################################
+# --compact db2
+
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `nonunique_table_name` (
+  `i1` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+  UNIQUE KEY `i1` (`i1`)
+) ENGINE=MEMORY AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+INSERT INTO `nonunique_table_name` VALUES (1),(2);
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `nonunique_table_view_name` (
+  `i2` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+INSERT INTO `nonunique_table_view_name` VALUES (3),(4);
+
+##################################################
+# --compact --delayed-insert --no-data-med=0 --databases db2 db1
+
+CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db2` /*!40100 DEFAULT CHARACTER SET utf8 */;
+
+USE `db2`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `nonunique_table_name` (
+  `i1` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+  UNIQUE KEY `i1` (`i1`)
+) ENGINE=MEMORY AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+INSERT  DELAYED INTO `nonunique_table_name` VALUES (1),(2);
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `nonunique_table_view_name` (
+  `i2` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+INSERT INTO `nonunique_table_view_name` VALUES (3),(4);
+
+CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db1` /*!40100 DEFAULT CHARACTER SET utf8 */;
+
+USE `db1`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `basetable` (
+  `id` smallint(6) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+INSERT  DELAYED INTO `basetable` VALUES (5),(6);
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `nonunique_table_name` (
+  `i3` smallint(6) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`basetable`);
+/*!40101 SET character_set_client = @saved_cs_client */;
+INSERT INTO `nonunique_table_name` VALUES (5),(6);
+SET @saved_cs_client     = @@character_set_client;
+SET character_set_client = utf8;
+/*!50001 CREATE TABLE `nonunique_table_view_name` (
+  `1` tinyint NOT NULL
+) ENGINE=MyISAM */;
+SET character_set_client = @saved_cs_client;
+
+USE `db2`;
+
+USE `db1`;
+/*!50001 DROP TABLE IF EXISTS `nonunique_table_view_name`*/;
+/*!50001 SET @saved_cs_client          = @@character_set_client */;
+/*!50001 SET @saved_cs_results         = @@character_set_results */;
+/*!50001 SET @saved_col_connection     = @@collation_connection */;
+/*!50001 SET character_set_client      = utf8 */;
+/*!50001 SET character_set_results     = utf8 */;
+/*!50001 SET collation_connection      = utf8_general_ci */;
+/*!50001 CREATE ALGORITHM=UNDEFINED */
+/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
+/*!50001 VIEW `nonunique_table_view_name` AS select 1 AS `1` */;
+/*!50001 SET character_set_client      = @saved_cs_client */;
+/*!50001 SET character_set_results     = @saved_cs_results */;
+/*!50001 SET collation_connection      = @saved_col_connection */;
+
+DROP DATABASE db1;
+DROP DATABASE db2;
diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test
index 2b9d1e7..684e3ec 100644
--- a/mysql-test/t/mysqldump.test
+++ b/mysql-test/t/mysqldump.test
@@ -2516,3 +2516,38 @@ drop table t1;
 --remove_file $MYSQLTEST_VARDIR/tmp/mysqldump-test.out
 #select * from mysql.user;
 #checksum table mysql.user;
+
+--echo #
+--echo # MDEV-9124 mysqldump does not dump data if table name is same as view earlier on
+--echo #
+
+CREATE DATABASE db1 CHARSET=utf8;
+CREATE DATABASE db2 CHARSET=utf8;
+USE db2;
+CREATE TABLE nonunique_table_name (i1 serial) ENGINE=MEMORY;
+INSERT INTO nonunique_table_name VALUES (1),(2);
+CREATE TABLE nonunique_table_view_name (i2 int) ENGINE=InnoDB;
+INSERT INTO nonunique_table_view_name VALUES (3),(4);
+use db1;
+CREATE TABLE basetable (id smallint) ENGINE=MyISAM;
+CREATE TABLE nonunique_table_name (i3 smallint) ENGINE=MERGE UNION (basetable) INSERT_METHOD=LAST;
+INSERT INTO nonunique_table_name VALUES (5),(6);
+CREATE VIEW nonunique_table_view_name AS SELECT 1;
+
+--echo 
+--echo ##################################################
+--echo # --compact --databases db1 db2
+--exec $MYSQL_DUMP --compact --databases db1 db2
+--echo 
+--echo ##################################################
+--echo # --compact db2
+--echo
+--exec $MYSQL_DUMP --compact db2
+--echo 
+--echo ##################################################
+--echo # --compact --delayed-insert --no-data-med=0 --databases db2 db1
+--exec $MYSQL_DUMP --compact --delayed-insert --no-data-med=0 --databases db2 db1
+--echo 
+
+DROP DATABASE db1;
+DROP DATABASE db2;


More information about the commits mailing list