[MDEV-9124] mysqldump does not dump data if table name is same as view earlier on Created: 2015-11-12  Updated: 2015-11-13  Resolved: 2015-11-13

Status: Closed
Project: MariaDB Server
Component/s: Scripts & Clients
Affects Version/s: 10.1.5, 10.1.8, 10.1
Fix Version/s: 10.1.9

Type: Bug Priority: Blocker
Reporter: Michaël de groot Assignee: Elena Stepanova
Resolution: Fixed Votes: 0
Labels: regression


 Description   

Hi everybody,

If mysqldump is run with -A (--all-databases) and a VIEW with the same name is exported as the next schema where it is a table it will not export any of the table's data.

Test script:

DROP DATABASE IF EXISTS dumped_last;
 
DROP DATABASE IF EXISTS dumped_first;
 
CREATE DATABASE dumped_last;
 
CREATE DATABASE dumped_first;
 
USE dumped_last
 
CREATE TABLE nonuniquename (id serial);
 
INSERT INTO nonuniquename VALUES(NULL);
 
use dumped_first
 
CREATE TABLE useless (id serial);
 
CREATE VIEW nonuniquename AS SELECT * FROM useless;

Then, dump the data:

mysqldump -A -uroot -p > dump.sql

And verify that the INSERT INTO uniquename is not present in the dump file. Please verify that 'dump_first' is dumped as first database and 'dumped_last' is dumped as second database. Also -A (or probably --all-databases) is required for this to occur.

The dump file gives a note about the 'Temporary table structure':

--
 
-- Temporary table structure for view `nonuniquename`
 
--
 
 
 
DROP TABLE IF EXISTS `nonuniquename`;
 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 
/*!40101 SET character_set_client = utf8 */;
 
CREATE TABLE `nonuniquename` (
 
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 
  UNIQUE KEY `id` (`id`)
 
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
 
/*!40101 SET character_set_client = @saved_cs_client */;
 
 
 
--
 
-- Current Database: `mysql`
 
--



 Comments   
Comment by Elena Stepanova [ 2015-11-12 ]

Thanks for the report.

The problem was introduced by the following commit:

commit ae4b24340d8f6d23ef7f4a82df3f981d65d9b060
 
Author: Vicențiu Ciorbaru <vicentiu@mariadb.org>
 
Date:   Thu Mar 19 15:16:22 2015 +0200
 
 
 
    MDEV-6714 mysqldump slow with tables in big databases
 
    
    mysqldump now attempts to make use of the INFORMATION_SCHEMA tables.
 
    If the table name is not found with a case sensitive search, it
 
    fallbacks to a case insensitive search.

I'm setting it to blocker because it's a regression and potential data loss.

Comment by Elena Stepanova [ 2015-11-12 ]

I think this should fix it:

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))
 
   {

Comment by Elena Stepanova [ 2015-11-13 ]

wlad��>� elenst, https://mariadb.atlassian.net/browse/MDEV-9124 , I checked the patch it is ok. just an elaborate commit comment would we good. And , maybe an mtr test

Comment by Elena Stepanova [ 2015-11-13 ]

https://github.com/MariaDB/server/commit/2828c2be554b62646fc990ac28b4aef20cd9b9d2

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