MySQL Lists are EOL. Please join:

List:Internals« Previous MessageNext Message »
From:Patrick Galbraith Date:October 14 2005 1:09am
Subject:bk commit into 5.0 tree (pgalbraith:1.2044) BUG#12838
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of mysqldev. When mysqldev does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2044 05/10/14 03:09:19 pgalbraith@stripped +3 -0
  ChangeSet
    BUG# 12838 - changed how temp tables are created so that
    mysqldump -x can work with a DB with views.
  mysqldump.result:
    BUG# 12838
    New test results for mysqldump -x on a DB with views
  mysqldump.test:
    BUG# 12838
    New test to run mysqldump -x on a DB with views
  mysqldump.c:
    BUG# 12838
    Removed/Changed code which created tables to be put into the dump
    (For loading views of views) by creating temp tables and then using
    the CREATE TABLE information in those temp tables. The problem with this
    is that when mysqldump -x is called, it locks all tables, so the
    temp tables could not be created, causing the mysqldump to exit with
    failure. The code was changed to use SHOW FIELDS to get the column
    names and type to build CREATE TABLE text used to create these tables
    that views need in the dump.

  mysql-test/r/mysqldump.result
    1.76 05/10/14 03:06:07 pgalbraith@stripped +105 -16
    BUG# 12838
    New test results for mysqldump -x on a DB with views

  mysql-test/t/mysqldump.test
    1.70 05/10/14 03:05:06 pgalbraith@stripped +24 -0
    BUG# 12838
    New test t
    New test to run mysqldump -x on a DB with views

  client/mysqldump.c
    1.207 05/10/14 02:59:31 pgalbraith@stripped +85 -66
    BUG# 12838
    Removed/Changed code which created tables to be put into the dump
    (For loading views of views) by creating temp tables and then using
    the CREATE TABLE information in those temp tables. The problem with this
    is that when mysqldump -x is called, it locks all tables, so the
    temp tables could not be created, causing the mysqldump to exit with
    failure. The code was changed to use SHOW FIELDS to get the column
    names and type to build CREATE TABLE text used to create these tables
    that views need in the dump.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	pgalbraith
# Host:	production.mysql.com
# Root:	/data0/mysqldev/patg/mysql-5.0

--- 1.206/client/mysqldump.c	2005-10-13 07:44:16 +02:00
+++ 1.207/client/mysqldump.c	2005-10-14 02:59:31 +02:00
@@ -1312,17 +1312,17 @@
 static uint get_table_structure(char *table, char *db, char *table_type,
                                 char *ignore_flag)
 {
-  MYSQL_RES  *tableRes;
-  MYSQL_ROW  row;
   my_bool    init=0, delayed, write_data, complete_insert;
   uint       num_fields;
   char	     *result_table, *opt_quoted_table;
   const char *insert_option;
   char	     name_buff[NAME_LEN+3],table_buff[NAME_LEN*2+3];
-  char	     table_buff2[NAME_LEN*2+3];
-  char       query_buff[512];
+  char	     table_buff2[NAME_LEN*2+3], query_buff[512];
   FILE       *sql_file = md_result_file;
   int        len;
+  MYSQL_RES  *result;
+  MYSQL_ROW  row;
+
   DBUG_ENTER("get_table_structure");
   DBUG_PRINT("enter", ("db: %s  table: %s", db, table));
 
@@ -1408,71 +1408,90 @@
 	check_io(sql_file);
       }
 
-      tableRes= mysql_store_result(sock);
-      field= mysql_fetch_field_direct(tableRes, 0);
+      result= mysql_store_result(sock);
+      field= mysql_fetch_field_direct(result, 0);
       if (strcmp(field->name, "View") == 0)
       {
         if (verbose)
           fprintf(stderr, "-- It's a view, create dummy table for view\n");
 
-        mysql_free_result(tableRes);
+        mysql_free_result(result);
 
-        /* Create a dummy table for the view. ie. a table  which has the
-           same columns as the view should have. This table is dropped
-           just before the view is created. The table is used to handle the
-           case where a view references another view, which hasn't yet been
-           created(during the load of the dump). BUG#10927 */
-
-        /* Create temp table by selecting from the view */
+        /* 
+           PMG - changed how create table info for tables that views need 
+           when being created, is derived.
+
+           The code previously created a dummy table for the view (BUG #10927)
+           to deal with views that are views of views, that when reloading 
+           the dump, if the view is created prior to the view that it points to,
+           then the data load files.
+           It would create this table by creating a temp table that the create 
+           table info would be parsed from to create a real table in the dump
+           file. The problem with this in the case that -x is supplied (lock
+           all tables) which the temp table would not be able to be created.
+           (BUG 12838)
+           Simple solution, use 'SHOW FIELDS', which doesn't require a temp
+           table. Ultimately, it would be good to have information of what a
+           view is dependent upon, and sort that into the dump file so that
+           views are dumped in the order in which they can be created without
+           being created prior to what the view depends on.
+        */
+           
+        /* Use show fields supply info to create a table */
         my_snprintf(query_buff, sizeof(query_buff),
-                    "CREATE TEMPORARY TABLE %s SELECT * FROM %s WHERE 0",
-                    result_table, result_table);
+                    "SHOW FIELDS FROM %s", result_table);
         if (mysql_query_with_error_report(sock, 0, query_buff))
         {
           safe_exit(EX_MYSQLERR);
           DBUG_RETURN(0);
         }
 
-        /* Get CREATE statement for the temp table */
-        my_snprintf(query_buff, sizeof(query_buff), "SHOW CREATE TABLE %s",
-                    result_table);
-        if (mysql_query_with_error_report(sock, 0, query_buff))
+        if ((result= mysql_store_result(sock)))
         {
-          safe_exit(EX_MYSQLERR);
-          DBUG_RETURN(0);
+          if (mysql_num_rows(result))
+          {
+            if (opt_drop)
+              fprintf(sql_file, "/*!50001 DROP VIEW IF EXISTS %s*/;\n",
+                      opt_quoted_table);
+            check_io(sql_file);
+
+            fprintf(sql_file, "/*!50001 CREATE TABLE %s (\n", result_table);
+            check_io(sql_file);
+            /*
+               Get first row, following loop will prepend comma - keeps
+               from having to know if the row being printed is last to
+               determine if there should be a _trailing_ comma.
+            */
+            row= mysql_fetch_row(result);
+
+            fprintf(sql_file, "  %s %s", quote_name(row[0], name_buff, 0), row[1]);
+            check_io(sql_file);
+
+            while((row= mysql_fetch_row(result)))
+            {
+              /* col name, col type */
+              fprintf(sql_file, ",\n  %s %s",
+                      quote_name(row[0], name_buff, 0), row[1]);
+              check_io(sql_file);
+            }
+            fprintf(sql_file, "\n) */;\n");
+            check_io(sql_file);
+          }
         }
-        tableRes= mysql_store_result(sock);
-        row= mysql_fetch_row(tableRes);
+        mysql_free_result(result);
 
-        if (opt_drop)
-          fprintf(sql_file, "/*!50001 DROP VIEW IF EXISTS %s*/;\n",
-                  opt_quoted_table);
-
-        /* Print CREATE statement but remove TEMPORARY */
-        fprintf(sql_file, "/*!50001 CREATE %s*/;\n", row[1]+17);
-        check_io(sql_file);
-
-        mysql_free_result(tableRes);
-
-        /* Drop the temp table */
-        my_snprintf(buff, sizeof(buff),
-                    "DROP TEMPORARY TABLE %s", result_table);
-        if (mysql_query_with_error_report(sock, 0, buff))
-        {
-          safe_exit(EX_MYSQLERR);
-          DBUG_RETURN(0);
-        }
         was_views= 1;
         DBUG_RETURN(0);
       }
-      row= mysql_fetch_row(tableRes);
+
+      row= mysql_fetch_row(result);
       fprintf(sql_file, "%s;\n", row[1]);
       check_io(sql_file);
-      mysql_free_result(tableRes);
+      mysql_free_result(result);
     }
     my_snprintf(query_buff, sizeof(query_buff), "show fields from %s",
 		result_table);
-    if (mysql_query_with_error_report(sock, &tableRes, query_buff))
+    if (mysql_query_with_error_report(sock, &result, query_buff))
     {
       if (path)
 	my_fclose(sql_file, MYF(MY_WME));
@@ -1504,7 +1523,7 @@
       }
     }
 
-    while ((row=mysql_fetch_row(tableRes)))
+    while ((row=mysql_fetch_row(result)))
     {
       if (complete_insert)
       {
@@ -1517,8 +1536,8 @@
                       quote_name(row[SHOW_FIELDNAME], name_buff, 0));
       }
     }
-    num_fields= (uint) mysql_num_rows(tableRes);
-    mysql_free_result(tableRes);
+    num_fields= (uint) mysql_num_rows(result);
+    mysql_free_result(result);
   }
   else
   {
@@ -1529,7 +1548,7 @@
 
     my_snprintf(query_buff, sizeof(query_buff), "show fields from %s",
 		result_table);
-    if (mysql_query_with_error_report(sock, &tableRes, query_buff))
+    if (mysql_query_with_error_report(sock, &result, query_buff))
     {
       safe_exit(EX_MYSQLERR);
       DBUG_RETURN(0);
@@ -1579,9 +1598,9 @@
       }
     }
 
-    while ((row=mysql_fetch_row(tableRes)))
+    while ((row=mysql_fetch_row(result)))
     {
-      ulong *lengths=mysql_fetch_lengths(tableRes);
+      ulong *lengths=mysql_fetch_lengths(result);
       if (init)
       {
         if (!opt_xml && !tFlag)
@@ -1600,7 +1619,7 @@
       {
 	if (opt_xml)
 	{
-	  print_xml_row(sql_file, "field", tableRes, &row);
+	  print_xml_row(sql_file, "field", result, &row);
 	  continue;
 	}
 
@@ -1624,15 +1643,15 @@
 	check_io(sql_file);
       }
     }
-    num_fields = (uint) mysql_num_rows(tableRes);
-    mysql_free_result(tableRes);
+    num_fields = (uint) mysql_num_rows(result);
+    mysql_free_result(result);
     if (!tFlag)
     {
       /* Make an sql-file, if path was given iow. option -T was given */
       char buff[20+FN_REFLEN];
       uint keynr,primary_key;
       my_snprintf(buff, sizeof(buff), "show keys from %s", result_table);
-      if (mysql_query_with_error_report(sock, &tableRes, buff))
+      if (mysql_query_with_error_report(sock, &result, buff))
       {
         if (mysql_errno(sock) == ER_WRONG_OBJECT)
         {
@@ -1651,7 +1670,7 @@
       /* Find first which key is primary key */
       keynr=0;
       primary_key=INT_MAX;
-      while ((row=mysql_fetch_row(tableRes)))
+      while ((row=mysql_fetch_row(result)))
       {
         if (atoi(row[3]) == 1)
         {
@@ -1667,13 +1686,13 @@
 	  }
         }
       }
-      mysql_data_seek(tableRes,0);
+      mysql_data_seek(result,0);
       keynr=0;
-      while ((row=mysql_fetch_row(tableRes)))
+      while ((row=mysql_fetch_row(result)))
       {
 	if (opt_xml)
 	{
-	  print_xml_row(sql_file, "key", tableRes, &row);
+	  print_xml_row(sql_file, "key", result, &row);
 	  continue;
 	}
 
@@ -1714,7 +1733,7 @@
         my_snprintf(buff, sizeof(buff), "show table status like %s",
 		    quote_for_like(table, show_name_buff));
 
-        if (mysql_query_with_error_report(sock, &tableRes, buff))
+        if (mysql_query_with_error_report(sock, &result, buff))
         {
 	  if (mysql_errno(sock) != ER_PARSE_ERROR)
 	  {					/* If old MySQL version */
@@ -1724,7 +1743,7 @@
 		      result_table,mysql_error(sock));
 	  }
         }
-        else if (!(row=mysql_fetch_row(tableRes)))
+        else if (!(row=mysql_fetch_row(result)))
         {
 	  fprintf(stderr,
 		  "Error: Couldn't read status information for table %s (%s)\n",
@@ -1733,18 +1752,18 @@
         else
         {
 	  if (opt_xml)
-	    print_xml_row(sql_file, "options", tableRes, &row);
+	    print_xml_row(sql_file, "options", result, &row);
 	  else
 	  {
 	    fputs("/*!",sql_file);
-	    print_value(sql_file,tableRes,row,"engine=","Engine",0);
-	    print_value(sql_file,tableRes,row,"","Create_options",0);
-	    print_value(sql_file,tableRes,row,"comment=","Comment",1);
+	    print_value(sql_file,result,row,"engine=","Engine",0);
+	    print_value(sql_file,result,row,"","Create_options",0);
+	    print_value(sql_file,result,row,"comment=","Comment",1);
 	    fputs(" */",sql_file);
 	    check_io(sql_file);
 	  }
         }
-        mysql_free_result(tableRes);		/* Is always safe to free */
+        mysql_free_result(result);		/* Is always safe to free */
       }
 continue_xml:
       if (!opt_xml)

--- 1.75/mysql-test/r/mysqldump.result	2005-10-13 07:44:16 +02:00
+++ 1.76/mysql-test/r/mysqldump.result	2005-10-14 03:06:07 +02:00
@@ -1459,8 +1459,8 @@
 DROP TABLE IF EXISTS `v2`;
 /*!50001 DROP VIEW IF EXISTS `v2`*/;
 /*!50001 CREATE TABLE `v2` (
-  `a` varchar(30) default NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1*/;
+  `a` varchar(30)
+) */;
 /*!50001 DROP TABLE IF EXISTS `v2`*/;
 /*!50001 DROP VIEW IF EXISTS `v2`*/;
 /*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t2`.`a` AS `a` from `t2` where (`t2`.`a` like _latin1'a%') WITH CASCADED CHECK OPTION*/;
@@ -1702,8 +1702,8 @@
 DROP TABLE IF EXISTS `v1`;
 /*!50001 DROP VIEW IF EXISTS `v1`*/;
 /*!50001 CREATE TABLE `v1` (
-  `a` int(11) default NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1*/;
+  `a` int(11)
+) */;
 /*!50001 DROP TABLE IF EXISTS `v1`*/;
 /*!50001 DROP VIEW IF EXISTS `v1`*/;
 /*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1`*/;
@@ -1757,8 +1757,8 @@
 DROP TABLE IF EXISTS `v2`;
 /*!50001 DROP VIEW IF EXISTS `v2`*/;
 /*!50001 CREATE TABLE `v2` (
-  `a` varchar(30) default NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1*/;
+  `a` varchar(30)
+) */;
 /*!50001 DROP TABLE IF EXISTS `v2`*/;
 /*!50001 DROP VIEW IF EXISTS `v2`*/;
 /*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t2`.`a` AS `a` from `t2` where (`t2`.`a` like _latin1'a%') WITH CASCADED CHECK OPTION*/;
@@ -1846,22 +1846,22 @@
 DROP TABLE IF EXISTS `v1`;
 /*!50001 DROP VIEW IF EXISTS `v1`*/;
 /*!50001 CREATE TABLE `v1` (
-  `a` int(11) default NULL,
-  `b` int(11) default NULL,
-  `c` varchar(30) default NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1*/;
+  `a` int(11),
+  `b` int(11),
+  `c` varchar(30)
+) */;
 DROP TABLE IF EXISTS `v2`;
 /*!50001 DROP VIEW IF EXISTS `v2`*/;
 /*!50001 CREATE TABLE `v2` (
-  `a` int(11) default NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1*/;
+  `a` int(11)
+) */;
 DROP TABLE IF EXISTS `v3`;
 /*!50001 DROP VIEW IF EXISTS `v3`*/;
 /*!50001 CREATE TABLE `v3` (
-  `a` int(11) default NULL,
-  `b` int(11) default NULL,
-  `c` varchar(30) default NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1*/;
+  `a` int(11),
+  `b` int(11),
+  `c` varchar(30)
+) */;
 /*!50001 DROP TABLE IF EXISTS `v1`*/;
 /*!50001 DROP VIEW IF EXISTS `v1`*/;
 /*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `v3`.`a` AS `a`,`v3`.`b` AS `b`,`v3`.`c` AS `c` from `v3` where (`v3`.`b` in (1,2,3,4,5,6,7))*/;
@@ -2309,3 +2309,92 @@
 drop table t1;
 set global time_zone=default;
 set time_zone=default;
+drop table if exists t1;
+create table t1 (a int, b varchar(32), c varchar(32));
+insert into t1 values (1, 'first value', 'xxxx');
+insert into t1 values (2, 'second value', 'tttt');
+insert into t1 values (3, 'third value', 'vvv vvv');
+create view v1 as select * from t1;
+create view v0 as select * from v1;
+create view v2 as select * from v0;
+select * from v2;
+a	b	c
+1	first value	xxxx
+2	second value	tttt
+3	third value	vvv vvv
+
+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
+/*!40101 SET NAMES utf8 */;
+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
+/*!40103 SET TIME_ZONE='+00:00' */;
+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
+
+CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
+
+USE `test`;
+DROP TABLE IF EXISTS `t1`;
+CREATE TABLE `t1` (
+  `a` int(11) default NULL,
+  `b` varchar(32) default NULL,
+  `c` varchar(32) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+
+/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
+LOCK TABLES `t1` WRITE;
+INSERT INTO `t1` VALUES (1,'first value','xxxx'),(2,'second value','tttt'),(3,'third value','vvv vvv');
+UNLOCK TABLES;
+/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
+DROP TABLE IF EXISTS `v0`;
+/*!50001 DROP VIEW IF EXISTS `v0`*/;
+/*!50001 CREATE TABLE `v0` (
+  `a` int(11),
+  `b` varchar(32),
+  `c` varchar(32)
+) */;
+DROP TABLE IF EXISTS `v1`;
+/*!50001 DROP VIEW IF EXISTS `v1`*/;
+/*!50001 CREATE TABLE `v1` (
+  `a` int(11),
+  `b` varchar(32),
+  `c` varchar(32)
+) */;
+DROP TABLE IF EXISTS `v2`;
+/*!50001 DROP VIEW IF EXISTS `v2`*/;
+/*!50001 CREATE TABLE `v2` (
+  `a` int(11),
+  `b` varchar(32),
+  `c` varchar(32)
+) */;
+
+CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
+
+USE `test`;
+/*!50001 DROP TABLE IF EXISTS `v0`*/;
+/*!50001 DROP VIEW IF EXISTS `v0`*/;
+/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v0` AS select `v1`.`a` AS `a`,`v1`.`b` AS `b`,`v1`.`c` AS `c` from `v1`*/;
+/*!50001 DROP TABLE IF EXISTS `v1`*/;
+/*!50001 DROP VIEW IF EXISTS `v1`*/;
+/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`c` AS `c` from `t1`*/;
+/*!50001 DROP TABLE IF EXISTS `v2`*/;
+/*!50001 DROP VIEW IF EXISTS `v2`*/;
+/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `v0`.`a` AS `a`,`v0`.`b` AS `b`,`v0`.`c` AS `c` from `v0`*/;
+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
+
+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
+
+drop view v2;
+drop view v0;
+drop view v1;
+drop table t1;

--- 1.69/mysql-test/t/mysqldump.test	2005-10-13 07:44:16 +02:00
+++ 1.70/mysql-test/t/mysqldump.test	2005-10-14 03:05:06 +02:00
@@ -928,3 +928,27 @@
 drop table t1;
 set global time_zone=default;
 set time_zone=default;
+
+#
+# BUG# 12838 mysqldump -x with views exits with error 
+#
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (a int, b varchar(32), c varchar(32));
+insert into t1 values (1, 'first value', 'xxxx');
+insert into t1 values (2, 'second value', 'tttt');
+insert into t1 values (3, 'third value', 'vvv vvv');
+
+create view v1 as select * from t1;
+create view v0 as select * from v1;
+create view v2 as select * from v0;
+
+select * from v2;
+--exec $MYSQL_DUMP --skip-comments --databases test
+
+drop view v2;
+drop view v0;
+drop view v1;
+drop table t1;
Thread
bk commit into 5.0 tree (pgalbraith:1.2044) BUG#12838Patrick Galbraith14 Oct