List:Commits« Previous MessageNext Message »
From:msvensson Date:January 27 2006 4:20pm
Subject:bk commit into 5.0 tree (msvensson:1.2019) BUG#14871
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of msvensson. When msvensson 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.2019 06/01/27 17:20:04 msvensson@neptunus.(none) +3 -0
  Bug#14871 mysqldump: invalid view dump output
   - Add comments with embeded veriosn info around the parts of the view syntax that are only supported by a certain version of MySQL Server

  mysql-test/t/mysqldump.test
    1.78 06/01/27 17:19:57 msvensson@neptunus.(none) +30 -0
    Add test to see that views can be deumped and reloaded alos when they contain "SECURITY TYPE", "CHECK OPTION" and "DEFINER"

  mysql-test/r/mysqldump.result
    1.88 06/01/27 17:19:57 msvensson@neptunus.(none) +55 -9
    Update results

  client/mysqldump.c
    1.219 06/01/27 17:19:56 msvensson@neptunus.(none) +153 -33
    Use information_schema.views to gather information about the view, then replace some parts of the output from "SHOW CREATE VIEW" with comment markers with version, to make thos parts of the view syntax become parsed only of MySQL servers that supports it.
    Create common function "open_sql_file_for_table" to open the individual .sql file where to dump the table or view.

# 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:	msvensson
# Host:	neptunus.(none)
# Root:	/home/msvensson/mysql/bug14871/my50-bug14871

--- 1.218/client/mysqldump.c	2006-01-11 00:07:34 +01:00
+++ 1.219/client/mysqldump.c	2006-01-27 17:19:56 +01:00
@@ -851,6 +851,27 @@
   return 0;
 }
 
+/*
+  Open a new .sql file to dump the table or view into
+
+  SYNOPSIS
+    open_sql_file_for_table
+    name      name of the table or view
+
+  RETURN VALUES
+    0        Failed to open file
+    > 0      Handle of the open file
+*/
+static FILE* open_sql_file_for_table(const char* table)
+{
+  FILE* res;
+  char filename[FN_REFLEN], tmp_path[FN_REFLEN];
+  convert_dirname(tmp_path,path,NullS);
+  res= my_fopen(fn_format(filename, table, tmp_path, ".sql", 4),
+		O_WRONLY, MYF(MY_WME));
+  return res;
+}
+
 
 static void safe_exit(int error)
 {
@@ -1402,11 +1423,7 @@
 
       if (path)
       {
-        char filename[FN_REFLEN], tmp_path[FN_REFLEN];
-        convert_dirname(tmp_path,path,NullS);
-        sql_file= my_fopen(fn_format(filename, table, tmp_path, ".sql", 4),
-				 O_WRONLY, MYF(MY_WME));
-        if (!sql_file)			/* If file couldn't be opened */
+        if (!(sql_file= open_sql_file_for_table(table)))
         {
 	  safe_exit(EX_MYSQLERR);
 	  DBUG_RETURN(0);
@@ -1568,11 +1585,7 @@
     {
       if (path)
       {
-        char filename[FN_REFLEN], tmp_path[FN_REFLEN];
-        convert_dirname(tmp_path,path,NullS);
-        sql_file= my_fopen(fn_format(filename, table, tmp_path, ".sql", 4),
-				 O_WRONLY, MYF(MY_WME));
-        if (!sql_file)			/* If file couldn't be opened */
+        if (!(sql_file= open_sql_file_for_table(table)))
         {
 	  safe_exit(EX_MYSQLERR);
 	  DBUG_RETURN(0);
@@ -3203,6 +3216,38 @@
 
 
 /*
+  Replace a substring
+
+  SYNOPSIS
+    replace
+    ds_str      The string to search and perform the replace in
+    search_str  The string to search for
+    replace_str The string to replace with
+
+  RETURN
+    0 String replaced
+    1 Could not find search_str in str
+*/
+
+static int replace(DYNAMIC_STRING *ds_str,
+		   const char *search_str, const char *replace_str)
+{
+  const char *start= strstr(ds_str->str, search_str);
+  if (!start)
+    return 1;
+  DYNAMIC_STRING ds_tmp;
+  init_dynamic_string(&ds_tmp, "",
+		      ds_str->length + strlen(replace_str), 256);
+  dynstr_append_mem(&ds_tmp, ds_str->str, start - ds_str->str);
+  dynstr_append_mem(&ds_tmp, replace_str, strlen(replace_str));
+  dynstr_append(&ds_tmp, start + strlen(search_str));
+  dynstr_set(ds_str, ds_tmp.str);
+  dynstr_free(&ds_tmp);
+  return 0;
+}
+
+
+/*
   Getting VIEW structure
 
   SYNOPSIS
@@ -3223,11 +3268,11 @@
   char	     *result_table, *opt_quoted_table;
   char	     table_buff[NAME_LEN*2+3];
   char	     table_buff2[NAME_LEN*2+3];
-  char       buff[20+FN_REFLEN];
+  char       query[QUERY_LENGTH];
   FILE       *sql_file = md_result_file;
   DBUG_ENTER("get_view_structure");
 
-  if (tFlag)
+  if (tFlag) /* Don't write table creation info */
     DBUG_RETURN(0);
 
   if (verbose)
@@ -3241,36 +3286,32 @@
   result_table=     quote_name(table, table_buff, 1);
   opt_quoted_table= quote_name(table, table_buff2, 0);
 
-  sprintf(buff,"show create table %s", result_table);
-  if (mysql_query(sock, buff))
+  snprintf(query, sizeof(query), "show create table %s", result_table);
+  if (mysql_query_with_error_report(sock, &table_res, query))
   {
-    fprintf(stderr, "%s: Can't get CREATE TABLE for view %s (%s)\n",
-            my_progname, result_table, mysql_error(sock));
     safe_exit(EX_MYSQLERR);
     DBUG_RETURN(0);
   }
 
+  /* Check if this is a view */
+  field= mysql_fetch_field_direct(table_res, 0);
+  if (strcmp(field->name, "View") != 0)
+  {
+    if (verbose)
+      fprintf(stderr, "-- It's base table, skipped\n");
+    DBUG_RETURN(0);
+  }
+
+  /* If requested, open separate .sql file for this view */
   if (path)
   {
-    char filename[FN_REFLEN], tmp_path[FN_REFLEN];
-    convert_dirname(tmp_path,path,NullS);
-    sql_file= my_fopen(fn_format(filename, table, tmp_path, ".sql", 4),
-                       O_WRONLY, MYF(MY_WME));
-    if (!sql_file)			/* If file couldn't be opened */
+    if (!(sql_file= open_sql_file_for_table(table)))
     {
       safe_exit(EX_MYSQLERR);
       DBUG_RETURN(1);
     }
     write_header(sql_file, db);
   }
-  table_res= mysql_store_result(sock);
-  field= mysql_fetch_field_direct(table_res, 0);
-  if (strcmp(field->name, "View") != 0)
-  {
-    if (verbose)
-      fprintf(stderr, "-- It's base table, skipped\n");
-    DBUG_RETURN(0);
-  }
 
   if (!opt_xml && opt_comments)
   {
@@ -3287,11 +3328,90 @@
     check_io(sql_file);
   }
 
-  row= mysql_fetch_row(table_res);
-  fprintf(sql_file, "/*!50001 %s*/;\n", row[1]);
-  check_io(sql_file);
-  mysql_free_result(table_res);
+  snprintf(query, sizeof(query),
+	   "select CHECK_OPTION, DEFINER, SECURITY_TYPE " \
+	   "from information_schema.views " \
+	   "where table_name=\"%s\" and table_schema=\"%s\"", table, db);
+  if (mysql_query(sock, query))
+  {
+    /*
+      Use the raw output from SHOW CREATE TABLE if
+       information_schema query fails.
+     */
+    row= mysql_fetch_row(table_res);
+    fprintf(sql_file, "/*!50001 %s */;\n", row[1]);
+    check_io(sql_file);
+    mysql_free_result(table_res);
+  }
+  else
+  {
+    char search_buf[256], replace_buf[256];
+    DYNAMIC_STRING ds_view;
+
+    /* Save the result of SHOW CREATE TABLE in ds_view */
+    row= mysql_fetch_row(table_res);
+    init_dynamic_string(&ds_view, row[1], strlen(row[1]), 1024);
+    mysql_free_result(table_res);
+
+    /* Get the result from "select ... information_schema" */
+    if (!(table_res= mysql_store_result(sock)))
+    {
+      safe_exit(EX_MYSQLERR);
+      DBUG_RETURN(1);
+    }
+    row= mysql_fetch_row(table_res);
+
+    /*
+      "WITH %s CHECK OPTION" is available from 5.0.2
+      Surround it with !50002 comments
+    */
+    if (strcmp(row[0], "NONE"))
+    {
+      snprintf(search_buf, sizeof(search_buf),
+	       "WITH %s CHECK OPTION",
+	       row[0]);
+      snprintf(replace_buf, sizeof(replace_buf),
+	       "*/\n/*!50002 WITH %s CHECK OPTION",
+	       row[0]);
+      replace(&ds_view, search_buf, replace_buf);
+    }
+
+    /*
+      "DEFINER=%s SQL SECURITY %s" is available from 5.0.13
+      Surround it with !50013 comments
+    */
+    {
+      uint       user_name_len;
+      char       user_name_str[USERNAME_LENGTH + 1];
+      char       quoted_user_name_str[USERNAME_LENGTH * 2 + 3];
+      uint       host_name_len;
+      char       host_name_str[HOSTNAME_LENGTH + 1];
+      char       quoted_host_name_str[HOSTNAME_LENGTH * 2 + 3];
+
+      parse_user(row[1], strlen(row[1]), user_name_str, &user_name_len,
+		 host_name_str, &host_name_len);
+
+      snprintf(search_buf, sizeof(search_buf),
+	       "DEFINER=%s@%s SQL SECURITY %s",
+	       quote_name(user_name_str, quoted_user_name_str, FALSE),
+	       quote_name(host_name_str, quoted_host_name_str, FALSE),
+	       row[2]);
+      snprintf(replace_buf, sizeof(replace_buf),
+	       "*/\n/*!50013 DEFINER=%s@%s SQL SECURITY %s */\n/*!50001",
+	       quote_name(user_name_str, quoted_user_name_str, FALSE),
+	       quote_name(host_name_str, quoted_host_name_str, FALSE),
+	       row[2]);
+      replace(&ds_view, search_buf, replace_buf);
+    }
+
+    /* Dump view structure to file */
+    fprintf(sql_file, "/*!50001 %s */;\n", ds_view.str);
+    check_io(sql_file);
+    mysql_free_result(table_res);
+    dynstr_free(&ds_view);
+  }
 
+  /* If a separate .sql file was opened, close it now */
   if (sql_file != md_result_file)
   {
     fputs("\n", sql_file);

--- 1.87/mysql-test/r/mysqldump.result	2006-01-12 01:02:48 +01:00
+++ 1.88/mysql-test/r/mysqldump.result	2006-01-27 17:19:57 +01:00
@@ -1464,7 +1464,10 @@
 ) */;
 /*!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*/;
+/*!50001 CREATE ALGORITHM=UNDEFINED */
+/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
+/*!50001 VIEW `v2` AS select `t2`.`a` AS `a` from `t2` where (`t2`.`a` like _latin1'a%') */
+/*!50002 WITH CASCADED CHECK OPTION */;
 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
@@ -1728,7 +1731,9 @@
 ) */;
 /*!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`*/;
+/*!50001 CREATE ALGORITHM=UNDEFINED */
+/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
+/*!50001 VIEW `v1` AS select `t1`.`a` AS `a` from `t1` */;
 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
@@ -1784,7 +1789,10 @@
 ) */;
 /*!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*/;
+/*!50001 CREATE ALGORITHM=UNDEFINED */
+/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
+/*!50001 VIEW `v2` AS select `t2`.`a` AS `a` from `t2` where (`t2`.`a` like _latin1'a%') */
+/*!50002 WITH CASCADED CHECK OPTION */;
 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
@@ -1890,13 +1898,19 @@
 ) */;
 /*!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))*/;
+/*!50001 CREATE ALGORITHM=UNDEFINED */
+/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
+/*!50001 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)) */;
 /*!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 `v3`.`a` AS `a` from (`v3` join `v1`) where ((`v1`.`a` = `v3`.`a`) and (`v3`.`b` = 3)) limit 1*/;
+/*!50001 CREATE ALGORITHM=UNDEFINED */
+/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
+/*!50001 VIEW `v2` AS select `v3`.`a` AS `a` from (`v3` join `v1`) where ((`v1`.`a` = `v3`.`a`) and (`v3`.`b` = 3)) limit 1 */;
 /*!50001 DROP TABLE IF EXISTS `v3`*/;
 /*!50001 DROP VIEW IF EXISTS `v3`*/;
-/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`c` AS `c` from `t1`*/;
+/*!50001 CREATE ALGORITHM=UNDEFINED */
+/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
+/*!50001 VIEW `v3` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`c` AS `c` from `t1` */;
 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
@@ -2465,13 +2479,19 @@
 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 CREATE ALGORITHM=UNDEFINED */
+/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
+/*!50001 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 CREATE ALGORITHM=UNDEFINED */
+/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
+/*!50001 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`*/;
+/*!50001 CREATE ALGORITHM=UNDEFINED */
+/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
+/*!50001 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 */;
@@ -2611,3 +2631,29 @@
 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
 drop table t1;
+create table t1 (a int);
+insert into t1 values (289), (298), (234), (456), (789);
+create definer = CURRENT_USER view v1 as select * from t1;
+create SQL SECURITY INVOKER view v2 as select * from t1;
+create view v3 as select * from t1 with local check option;
+create algorithm=merge view v4 as select * from t1 with cascaded check option;
+create algorithm =temptable view v5 as select * from t1;
+drop table t1;
+drop view v1, v2, v3, v4, v5;
+show tables;
+Tables_in_test
+t1
+v1
+v2
+v3
+v4
+v5
+select * from v3 order by a;
+a
+234
+289
+298
+456
+789
+drop table t1;
+drop view v1, v2, v3, v4, v5;

--- 1.77/mysql-test/t/mysqldump.test	2005-12-01 21:21:26 +01:00
+++ 1.78/mysql-test/t/mysqldump.test	2006-01-27 17:19:57 +01:00
@@ -1036,3 +1036,33 @@
 drop table t1;
 
 # End of 4.1 tests
+
+#
+# Bug 14871 Invalid view dump output
+#
+
+create table t1 (a int);
+insert into t1 values (289), (298), (234), (456), (789);
+create definer = CURRENT_USER view v1 as select * from t1;
+create SQL SECURITY INVOKER view v2 as select * from t1;
+create view v3 as select * from t1 with local check option;
+create algorithm=merge view v4 as select * from t1 with cascaded check option;
+create algorithm =temptable view v5 as select * from t1;
+
+# dump tables and views
+--exec $MYSQL_DUMP test > var/tmp/bug14871.sql
+
+# drop the db, tables and views
+drop table t1;
+drop view v1, v2, v3, v4, v5;
+
+# Reload dump
+--exec $MYSQL test < var/tmp/bug14871.sql
+
+# check that all tables and views could be created
+show tables;
+select * from v3 order by a;
+
+drop table t1;
+drop view v1, v2, v3, v4, v5;
+
Thread
bk commit into 5.0 tree (msvensson:1.2019) BUG#14871msvensson27 Jan