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/in...urce-tree.html

ChangeSet
1.1971 05/06/16 15:58:17 msvensson (AT) neptunus (DOT) (none) +3 -0
BUG#10927 mysqldump: Can't reload dump with view that consist of other view
- Create a small dummy table that will take care of the problem of creating a view dependent of another view which hasn't yet been created.

mysql-test/t/mysqldump.test
1.39 05/06/16 15:58:13 msvensson (AT) neptunus (DOT) (none) +34 -1
Add tests for bug#10927

mysql-test/r/mysqldump.result
1.45 05/06/16 15:58:13 msvensson (AT) neptunus (DOT) (none) +32 -1
Add tests for bug#10927

client/mysqldump.c
1.182 05/06/16 15:58:13 msvensson (AT) neptunus (DOT) (none) +49 -1
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).

# 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/bug10927

--- 1.181/client/mysqldump.c 2005-05-26 12:19:36 +02:00
+++ 1.182/client/mysqldump.c 2005-06-16 15:58:13 +02:00
@@ -1212,7 +1212,54 @@
if (strcmp(field->name, "View") == 0)
{
if (verbose)
- fprintf(stderr, "-- It's a view, skipped\n");
+ fprintf(stderr, "-- It's a view, create dummy table for view\n");
+
+ mysql_free_result(tableRes);
+
+ /* 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 */
+ my_snprintf(query_buff, sizeof(query_buff),
+ "create temporary table %s select * from %s where 1=0",
+ result_table, 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))
+ {
+ safe_exit(EX_MYSQLERR);
+ DBUG_RETURN(0);
+ }
+ tableRes= mysql_store_result(sock);
+ row= mysql_fetch_row(tableRes);
+
+ if (opt_drop)
+ fprintf(sql_file, "DROP VIEW IF EXISTS %s;\n",opt_quoted_table);
+
+ /* Print CREATE statement but remove TEMPORARY */
+ fprintf(sql_file, "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);
}
@@ -2752,6 +2799,7 @@
}
if (opt_drop)
{
+ fprintf(sql_file, "DROP TABLE IF EXISTS %s;\n", opt_quoted_table);
fprintf(sql_file, "DROP VIEW IF EXISTS %s;\n", opt_quoted_table);
check_io(sql_file);
}

--- 1.44/mysql-test/r/mysqldump.result 2005-05-26 12:19:36 +02:00
+++ 1.45/mysql-test/r/mysqldump.result 2005-06-16 15:58:13 +02:00
@@ -1,6 +1,6 @@
DROP TABLE IF EXISTS t1, `"t"1`, t1aa, t2, t2aa;
drop database if exists mysqldump_test_db;
-drop view if exists v1;
+drop view if exists v1, v2, v3;
CREATE TABLE t1(a int);
INSERT INTO t1 VALUES (1), (2);
<?xml version="1.0"?>
@@ -379,6 +379,11 @@
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
DROP TABLE IF EXISTS `v1`;
DROP VIEW IF EXISTS `v1`;
+CREATE TABLE `v1` (
+ `a` bigint(11) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+DROP TABLE IF EXISTS `v1`;
+DROP VIEW IF EXISTS `v1`;
CREATE ALGORITHM=UNDEFINED VIEW `test`.`v1` AS select `test`.`t1`.`a` AS `a` from `test`.`t1`;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
@@ -1422,3 +1427,29 @@
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

DROP TABLE t1;
+create table t1(a int, b int, c varchar(30));
+insert into t1 values(1, 2, "one"), (2, 4, "two"), (3, 6, "three");
+create view v3 as
+select * from t1;
+create view v1 as
+select * from v3 where b in (1, 2, 3, 4, 5, 6, 7);
+create view v2 as
+select v3.a from v3, v1 where v1.a=v3.a and v3.b=3 limit 1;
+drop view v1, v2, v3;
+drop table t1;
+show full tables;
+Tables_in_test Table_type
+t1 BASE TABLE
+v1 VIEW
+v2 VIEW
+v3 VIEW
+show create view v1;
+View Create View
+v1 CREATE ALGORITHM=UNDEFINED VIEW `test`.`v1` AS select `test`.`v3`.`a` AS `a`,`test`.`v3`.`b` AS `b`,`test`.`v3`.`c` AS `c` from `test`.`v3` where (`test`.`v3`.`b` in (1,2,3,4,5,6,7))
+select * from v1;
+a b c
+1 2 one
+2 4 two
+3 6 three
+drop view v1, v2, v3;
+drop table t1;

--- 1.38/mysql-test/t/mysqldump.test 2005-05-26 12:19:38 +02:00
+++ 1.39/mysql-test/t/mysqldump.test 2005-06-16 15:58:13 +02:00
@@ -4,7 +4,7 @@
--disable_warnings
DROP TABLE IF EXISTS t1, `"t"1`, t1aa, t2, t2aa;
drop database if exists mysqldump_test_db;
-drop view if exists v1;
+drop view if exists v1, v2, v3;
--enable_warnings

# XML output
@@ -563,3 +563,36 @@
INSERT INTO t1 VALUES (1),(2),(3);
--exec $MYSQL_DUMP --add-drop-database --skip-comments --databases test
DROP TABLE t1;
+
+
+#
+# Bug #10927 mysqldump: Can't reload dump with view that consist of other view
+#
+
+create table t1(a int, b int, c varchar(30));
+
+insert into t1 values(1, 2, "one"), (2, 4, "two"), (3, 6, "three");
+
+create view v3 as
+select * from t1;
+
+create view v1 as
+select * from v3 where b in (1, 2, 3, 4, 5, 6, 7);
+
+create view v2 as
+select v3.a from v3, v1 where v1.a=v3.a and v3.b=3 limit 1;
+
+--exec $MYSQL_DUMP test > var/tmp/bug10927.sql
+drop view v1, v2, v3;
+drop table t1;
+--exec $MYSQL test < var/tmp/bug10927.sql
+
+# Without dropping the original tables in between
+--exec $MYSQL_DUMP test > var/tmp/bug10927.sql
+--exec $MYSQL test < var/tmp/bug10927.sql
+show full tables;
+show create view v1;
+select * from v1;
+
+drop view v1, v2, v3;
+drop table t1;

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?uns...ie.nctu.edu.tw