diff options
author | Daniel Black <daniel@mariadb.org> | 2022-07-30 13:29:17 +1000 |
---|---|---|
committer | Daniel Black <daniel@mariadb.org> | 2022-08-03 02:51:11 +1000 |
commit | 92b0a367aa9f4602f99d28eae5776c679984ac88 (patch) | |
tree | 06db25f01eab21fdec02d0b77872cffb83c8a1ea | |
parent | 53c4e4d054cb5ac895535abc9815e70378dbf1b5 (diff) | |
download | mariadb-git-92b0a367aa9f4602f99d28eae5776c679984ac88.tar.gz |
MDEV-26447: mysqldump to use temporary view instead of tables.
This is particularly important for Azure where there is no
MyISAM support in their MariaDB cloud product.
Like mysqldumper does, a view can satisfy the requirement
like a table, without constraints. The views in frm files are
text form and don't have column limits.
Thanks Thomas Casteleyn for the suggestion.
-rw-r--r-- | client/mysqldump.c | 51 | ||||
-rw-r--r-- | mysql-test/main/lock_view.result | 49 | ||||
-rw-r--r-- | mysql-test/main/mysqldump-nl.result | 11 | ||||
-rw-r--r-- | mysql-test/main/mysqldump.result | 117 | ||||
-rw-r--r-- | mysql-test/suite/roles/definer.result | 47 |
5 files changed, 103 insertions, 172 deletions
diff --git a/client/mysqldump.c b/client/mysqldump.c index 0d9b48e585e..437677f3638 100644 --- a/client/mysqldump.c +++ b/client/mysqldump.c @@ -3072,9 +3072,8 @@ static uint get_table_structure(const char *table, const char *db, char *table_t if (strcmp(field->name, "View") == 0) { char *scv_buff= NULL; - my_ulonglong n_cols; - verbose_msg("-- It's a view, create dummy table for view\n"); + verbose_msg("-- It's a view, create dummy view for view\n"); /* save "show create" statement for later */ if ((row= mysql_fetch_row(result)) && (scv_buff=row[1])) @@ -3083,9 +3082,9 @@ static uint get_table_structure(const char *table, const char *db, char *table_t mysql_free_result(result); /* - Create a table with the same name as the view and with columns of + Create a view with the same name as the view and with columns of the same name in order to satisfy views that depend on this view. - The table will be removed when the actual view is created. + The view will be removed when the actual view is created. The properties of each column, are not preserved in this temporary table, because they are not necessary. @@ -3117,23 +3116,9 @@ static uint get_table_structure(const char *table, const char *db, char *table_t else my_free(scv_buff); - n_cols= mysql_num_rows(result); - if (0 != n_cols) + if (mysql_num_rows(result) != 0) { - /* - The actual formula is based on the column names and how the .FRM - files are stored and is too volatile to be repeated here. - Thus we simply warn the user if the columns exceed a limit we - know works most of the time. - */ - if (n_cols >= 1000) - fprintf(stderr, - "-- Warning: Creating a stand-in table for view %s may" - " fail when replaying the dump file produced because " - "of the number of columns exceeding 1000. Exercise " - "caution when replaying the produced dump file.\n", - table); if (opt_drop) { /* @@ -3149,7 +3134,7 @@ static uint get_table_structure(const char *table, const char *db, char *table_t fprintf(sql_file, "SET @saved_cs_client = @@character_set_client;\n" "SET character_set_client = utf8;\n" - "/*!50001 CREATE TABLE %s (\n", + "/*!50001 CREATE VIEW %s AS SELECT\n", result_table); /* @@ -3161,28 +3146,21 @@ static uint get_table_structure(const char *table, const char *db, char *table_t row= mysql_fetch_row(result); /* - The actual column type doesn't matter anyway, since the table will + The actual column value doesn't matter anyway, since the view will be dropped at run time. - We do tinyint to avoid hitting the row size limit. */ - fprintf(sql_file, " %s tinyint NOT NULL", + fprintf(sql_file, " 1 AS %s", quote_name(row[0], name_buff, 0)); while((row= mysql_fetch_row(result))) { /* col name, col type */ - fprintf(sql_file, ",\n %s tinyint NOT NULL", + fprintf(sql_file, ",\n 1 AS %s", quote_name(row[0], name_buff, 0)); } - /* - Stand-in tables are always MyISAM tables as the default - engine might have a column-limit that's lower than the - number of columns in the view, and MyISAM support is - guaranteed to be in the server anyway. - */ fprintf(sql_file, - "\n) ENGINE=MyISAM */;\n" + " */;\n" "SET character_set_client = @saved_cs_client;\n"); check_io(sql_file); @@ -6642,15 +6620,8 @@ static my_bool get_view_structure(char *table, char* db) "\n--\n-- Final view structure for view %s\n--\n\n", fix_for_comment(result_table)); - /* Table might not exist if this view was dumped with --tab. */ - fprintf(sql_file, "/*!50001 DROP TABLE IF EXISTS %s*/;\n", opt_quoted_table); - if (opt_drop) - { - fprintf(sql_file, "/*!50001 DROP VIEW IF EXISTS %s*/;\n", - opt_quoted_table); - check_io(sql_file); - } - + /* View might not exist if this view was dumped with --tab. */ + fprintf(sql_file, "/*!50001 DROP VIEW IF EXISTS %s*/;\n", opt_quoted_table); my_snprintf(query, sizeof(query), "SELECT CHECK_OPTION, DEFINER, SECURITY_TYPE, " diff --git a/mysql-test/main/lock_view.result b/mysql-test/main/lock_view.result index 48c45dcf23d..6cc788f86f1 100644 --- a/mysql-test/main/lock_view.result +++ b/mysql-test/main/lock_view.result @@ -32,9 +32,8 @@ CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysqltest2` /*!40100 DEFAULT CHARACTER USE `mysqltest2`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE TABLE `v2` ( - `a` tinyint NOT NULL -) ENGINE=MyISAM */; +/*!50001 CREATE VIEW `v2` AS SELECT + 1 AS `a` */; SET character_set_client = @saved_cs_client; CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysqltest3` /*!40100 DEFAULT CHARACTER SET latin1 */; @@ -42,39 +41,34 @@ CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysqltest3` /*!40100 DEFAULT CHARACTER USE `mysqltest3`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE TABLE `v3` ( - `a` tinyint NOT NULL -) ENGINE=MyISAM */; +/*!50001 CREATE VIEW `v3` AS SELECT + 1 AS `a` */; SET character_set_client = @saved_cs_client; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE TABLE `v3i` ( - `a` tinyint NOT NULL -) ENGINE=MyISAM */; +/*!50001 CREATE VIEW `v3i` AS SELECT + 1 AS `a` */; SET character_set_client = @saved_cs_client; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE TABLE `v3is` ( - `schema_name` tinyint NOT NULL -) ENGINE=MyISAM */; +/*!50001 CREATE VIEW `v3is` AS SELECT + 1 AS `schema_name` */; SET character_set_client = @saved_cs_client; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE TABLE `v3nt` ( - `1` tinyint NOT NULL -) ENGINE=MyISAM */; +/*!50001 CREATE VIEW `v3nt` AS SELECT + 1 AS `1` */; SET character_set_client = @saved_cs_client; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE TABLE `v3ps` ( - `user` tinyint NOT NULL -) ENGINE=MyISAM */; +/*!50001 CREATE VIEW `v3ps` AS SELECT + 1 AS `user` */; SET character_set_client = @saved_cs_client; USE `mysqltest1`; USE `mysqltest2`; -/*!50001 DROP TABLE IF EXISTS `v2`*/; +/*!50001 DROP VIEW IF EXISTS `v2`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; @@ -89,7 +83,7 @@ USE `mysqltest2`; /*!50001 SET collation_connection = @saved_col_connection */; USE `mysqltest3`; -/*!50001 DROP TABLE IF EXISTS `v3`*/; +/*!50001 DROP VIEW IF EXISTS `v3`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; @@ -102,7 +96,7 @@ USE `mysqltest3`; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -/*!50001 DROP TABLE IF EXISTS `v3i`*/; +/*!50001 DROP VIEW IF EXISTS `v3i`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; @@ -115,7 +109,7 @@ USE `mysqltest3`; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -/*!50001 DROP TABLE IF EXISTS `v3is`*/; +/*!50001 DROP VIEW IF EXISTS `v3is`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; @@ -128,7 +122,7 @@ USE `mysqltest3`; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -/*!50001 DROP TABLE IF EXISTS `v3nt`*/; +/*!50001 DROP VIEW IF EXISTS `v3nt`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; @@ -141,7 +135,7 @@ USE `mysqltest3`; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -/*!50001 DROP TABLE IF EXISTS `v3ps`*/; +/*!50001 DROP VIEW IF EXISTS `v3ps`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; @@ -243,11 +237,10 @@ disconnect con1; connection default; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE TABLE `v1` ( - `id` tinyint NOT NULL -) ENGINE=MyISAM */; +/*!50001 CREATE VIEW `v1` AS SELECT + 1 AS `id` */; SET character_set_client = @saved_cs_client; -/*!50001 DROP TABLE IF EXISTS `v1`*/; +/*!50001 DROP VIEW IF EXISTS `v1`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; diff --git a/mysql-test/main/mysqldump-nl.result b/mysql-test/main/mysqldump-nl.result index 89fb3144867..bac1ccb6ea0 100644 --- a/mysql-test/main/mysqldump-nl.result +++ b/mysql-test/main/mysqldump-nl.result @@ -53,11 +53,10 @@ raboof` int(11) DEFAULT NULL SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE TABLE `v1 -1v` ( - `foobar -raboof` tinyint NOT NULL -) ENGINE=MyISAM */; +/*!50001 CREATE VIEW `v1 +1v` AS SELECT + 1 AS `foobar +raboof` */; SET character_set_client = @saved_cs_client; -- @@ -95,7 +94,7 @@ USE `mysqltest1 -- 1v` -- -/*!50001 DROP TABLE IF EXISTS `v1 +/*!50001 DROP VIEW IF EXISTS `v1 1v`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; diff --git a/mysql-test/main/mysqldump.result b/mysql-test/main/mysqldump.result index 1ddda58e368..8babe0239b8 100644 --- a/mysql-test/main/mysqldump.result +++ b/mysql-test/main/mysqldump.result @@ -2068,11 +2068,9 @@ DROP TABLE IF EXISTS `v2`; /*!50001 DROP VIEW IF EXISTS `v2`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE TABLE `v2` ( - `a` tinyint NOT NULL -) ENGINE=MyISAM */; +/*!50001 CREATE VIEW `v2` AS SELECT + 1 AS `a` */; SET character_set_client = @saved_cs_client; -/*!50001 DROP TABLE IF EXISTS `v2`*/; /*!50001 DROP VIEW IF EXISTS `v2`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; @@ -2162,11 +2160,9 @@ DROP TABLE IF EXISTS `v1`; /*!50001 DROP VIEW IF EXISTS `v1`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE TABLE `v1` ( - `a` tinyint NOT NULL -) ENGINE=MyISAM */; +/*!50001 CREATE VIEW `v1` AS SELECT + 1 AS `a` */; SET character_set_client = @saved_cs_client; -/*!50001 DROP TABLE IF EXISTS `v1`*/; /*!50001 DROP VIEW IF EXISTS `v1`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; @@ -2236,11 +2232,9 @@ DROP TABLE IF EXISTS `v2`; /*!50001 DROP VIEW IF EXISTS `v2`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE TABLE `v2` ( - `a` tinyint NOT NULL -) ENGINE=MyISAM */; +/*!50001 CREATE VIEW `v2` AS SELECT + 1 AS `a` */; SET character_set_client = @saved_cs_client; -/*!50001 DROP TABLE IF EXISTS `v2`*/; /*!50001 DROP VIEW IF EXISTS `v2`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; @@ -2350,31 +2344,27 @@ DROP TABLE IF EXISTS `v1`; /*!50001 DROP VIEW IF EXISTS `v1`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE TABLE `v1` ( - `a` tinyint NOT NULL, - `b` tinyint NOT NULL, - `c` tinyint NOT NULL -) ENGINE=MyISAM */; +/*!50001 CREATE VIEW `v1` AS SELECT + 1 AS `a`, + 1 AS `b`, + 1 AS `c` */; SET character_set_client = @saved_cs_client; DROP TABLE IF EXISTS `v2`; /*!50001 DROP VIEW IF EXISTS `v2`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE TABLE `v2` ( - `a` tinyint NOT NULL -) ENGINE=MyISAM */; +/*!50001 CREATE VIEW `v2` AS SELECT + 1 AS `a` */; SET character_set_client = @saved_cs_client; DROP TABLE IF EXISTS `v3`; /*!50001 DROP VIEW IF EXISTS `v3`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE TABLE `v3` ( - `a` tinyint NOT NULL, - `b` tinyint NOT NULL, - `c` tinyint NOT NULL -) ENGINE=MyISAM */; +/*!50001 CREATE VIEW `v3` AS SELECT + 1 AS `a`, + 1 AS `b`, + 1 AS `c` */; SET character_set_client = @saved_cs_client; -/*!50001 DROP TABLE IF EXISTS `v1`*/; /*!50001 DROP VIEW IF EXISTS `v1`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; @@ -2388,7 +2378,6 @@ SET character_set_client = @saved_cs_client; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -/*!50001 DROP TABLE IF EXISTS `v2`*/; /*!50001 DROP VIEW IF EXISTS `v2`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; @@ -2402,7 +2391,6 @@ SET character_set_client = @saved_cs_client; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -/*!50001 DROP TABLE IF EXISTS `v3`*/; /*!50001 DROP VIEW IF EXISTS `v3`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; @@ -3107,35 +3095,31 @@ DROP TABLE IF EXISTS `v0`; /*!50001 DROP VIEW IF EXISTS `v0`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE TABLE `v0` ( - `a` tinyint NOT NULL, - `b` tinyint NOT NULL, - `c` tinyint NOT NULL -) ENGINE=MyISAM */; +/*!50001 CREATE VIEW `v0` AS SELECT + 1 AS `a`, + 1 AS `b`, + 1 AS `c` */; SET character_set_client = @saved_cs_client; DROP TABLE IF EXISTS `v1`; /*!50001 DROP VIEW IF EXISTS `v1`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE TABLE `v1` ( - `a` tinyint NOT NULL, - `b` tinyint NOT NULL, - `c` tinyint NOT NULL -) ENGINE=MyISAM */; +/*!50001 CREATE VIEW `v1` AS SELECT + 1 AS `a`, + 1 AS `b`, + 1 AS `c` */; SET character_set_client = @saved_cs_client; DROP TABLE IF EXISTS `v2`; /*!50001 DROP VIEW IF EXISTS `v2`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE TABLE `v2` ( - `a` tinyint NOT NULL, - `b` tinyint NOT NULL, - `c` tinyint NOT NULL -) ENGINE=MyISAM */; +/*!50001 CREATE VIEW `v2` AS SELECT + 1 AS `a`, + 1 AS `b`, + 1 AS `c` */; SET character_set_client = @saved_cs_client; USE `test`; -/*!50001 DROP TABLE IF EXISTS `v0`*/; /*!50001 DROP VIEW IF EXISTS `v0`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; @@ -3149,7 +3133,6 @@ USE `test`; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -/*!50001 DROP TABLE IF EXISTS `v1`*/; /*!50001 DROP VIEW IF EXISTS `v1`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; @@ -3163,7 +3146,6 @@ USE `test`; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -/*!50001 DROP TABLE IF EXISTS `v2`*/; /*!50001 DROP VIEW IF EXISTS `v2`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; @@ -3401,7 +3383,7 @@ insert into t values(5, 51); create view v1 as select qty, price, qty*price as value from t; create view v2 as select qty from v1; mysqldump { -/*!50001 DROP TABLE IF EXISTS `v1`*/; +/*!50001 DROP VIEW IF EXISTS `v1`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; @@ -3416,7 +3398,7 @@ mysqldump { /*!50001 SET collation_connection = @saved_col_connection */; } mysqldump { -/*!50001 DROP TABLE IF EXISTS `v2`*/; +/*!50001 DROP VIEW IF EXISTS `v2`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; @@ -3509,13 +3491,11 @@ DROP TABLE IF EXISTS `v1`; /*!50001 DROP VIEW IF EXISTS `v1`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE TABLE `v1` ( - `id` tinyint NOT NULL -) ENGINE=MyISAM */; +/*!50001 CREATE VIEW `v1` AS SELECT + 1 AS `id` */; SET character_set_client = @saved_cs_client; USE `mysqldump_test_db`; -/*!50001 DROP TABLE IF EXISTS `v1`*/; /*!50001 DROP VIEW IF EXISTS `v1`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; @@ -3569,15 +3549,14 @@ CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysqldump_views` /*!40100 DEFAULT CHAR USE `mysqldump_views`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE TABLE `nasishnasifu` ( - `id` tinyint NOT NULL -) ENGINE=MyISAM */; +/*!50001 CREATE VIEW `nasishnasifu` AS SELECT + 1 AS `id` */; SET character_set_client = @saved_cs_client; USE `mysqldump_tables`; USE `mysqldump_views`; -/*!50001 DROP TABLE IF EXISTS `nasishnasifu`*/; +/*!50001 DROP VIEW IF EXISTS `nasishnasifu`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; @@ -3978,11 +3957,9 @@ DROP TABLE IF EXISTS `v2`; /*!50001 DROP VIEW IF EXISTS `v2`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE TABLE `v2` ( - `c` tinyint NOT NULL -) ENGINE=MyISAM */; +/*!50001 CREATE VIEW `v2` AS SELECT + 1 AS `c` */; SET character_set_client = @saved_cs_client; -/*!50001 DROP TABLE IF EXISTS `v2`*/; /*!50001 DROP VIEW IF EXISTS `v2`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; @@ -4400,13 +4377,11 @@ DROP TABLE IF EXISTS `v1`; /*!50001 DROP VIEW IF EXISTS `v1`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE TABLE `v1` ( - `id` tinyint NOT NULL -) ENGINE=MyISAM */; +/*!50001 CREATE VIEW `v1` AS SELECT + 1 AS `id` */; SET character_set_client = @saved_cs_client; USE `mysqldump_test_db`; -/*!50001 DROP TABLE IF EXISTS `v1`*/; /*!50001 DROP VIEW IF EXISTS `v1`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; @@ -5494,9 +5469,8 @@ CREATE TABLE `nonunique_table_name` ( /*!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 */; +/*!50001 CREATE VIEW `nonunique_table_view_name` AS SELECT + 1 AS `1` */; SET character_set_client = @saved_cs_client; CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db2` /*!40100 DEFAULT CHARACTER SET utf8 */; @@ -5519,7 +5493,7 @@ CREATE TABLE `nonunique_table_view_name` ( INSERT INTO `nonunique_table_view_name` VALUES (3),(4); USE `db1`; -/*!50001 DROP TABLE IF EXISTS `nonunique_table_view_name`*/; +/*!50001 DROP VIEW 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 */; @@ -5595,15 +5569,14 @@ CREATE TABLE `nonunique_table_name` ( 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 */; +/*!50001 CREATE VIEW `nonunique_table_view_name` AS SELECT + 1 AS `1` */; SET character_set_client = @saved_cs_client; USE `db2`; USE `db1`; -/*!50001 DROP TABLE IF EXISTS `nonunique_table_view_name`*/; +/*!50001 DROP VIEW 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 */; diff --git a/mysql-test/suite/roles/definer.result b/mysql-test/suite/roles/definer.result index a5551e7dede..bc79ad8a98c 100644 --- a/mysql-test/suite/roles/definer.result +++ b/mysql-test/suite/roles/definer.result @@ -280,39 +280,34 @@ CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET l USE `test`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE TABLE `v1` ( - `a+b` tinyint NOT NULL, - `c` tinyint NOT NULL -) ENGINE=MyISAM */; +/*!50001 CREATE VIEW `v1` AS SELECT + 1 AS `a+b`, + 1 AS `c` */; SET character_set_client = @saved_cs_client; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE TABLE `v2` ( - `a+b` tinyint NOT NULL, - `c` tinyint NOT NULL, - `current_role()` tinyint NOT NULL -) ENGINE=MyISAM */; +/*!50001 CREATE VIEW `v2` AS SELECT + 1 AS `a+b`, + 1 AS `c`, + 1 AS `current_role()` */; SET character_set_client = @saved_cs_client; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE TABLE `v3` ( - `a+b` tinyint NOT NULL, - `c` tinyint NOT NULL -) ENGINE=MyISAM */; +/*!50001 CREATE VIEW `v3` AS SELECT + 1 AS `a+b`, + 1 AS `c` */; SET character_set_client = @saved_cs_client; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE TABLE `v4` ( - `a+b` tinyint NOT NULL, - `c` tinyint NOT NULL -) ENGINE=MyISAM */; +/*!50001 CREATE VIEW `v4` AS SELECT + 1 AS `a+b`, + 1 AS `c` */; SET character_set_client = @saved_cs_client; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE TABLE `v5` ( - `a+b` tinyint NOT NULL, - `c` tinyint NOT NULL -) ENGINE=MyISAM */; +/*!50001 CREATE VIEW `v5` AS SELECT + 1 AS `a+b`, + 1 AS `c` */; SET character_set_client = @saved_cs_client; CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysqltest1` /*!40100 DEFAULT CHARACTER SET latin1 */; @@ -536,7 +531,7 @@ DELIMITER ; /*!50003 SET collation_connection = @saved_col_connection */ ; USE `test`; -/*!50001 DROP TABLE IF EXISTS `v1`*/; +/*!50001 DROP VIEW IF EXISTS `v1`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; @@ -547,7 +542,7 @@ USE `test`; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -/*!50001 DROP TABLE IF EXISTS `v2`*/; +/*!50001 DROP VIEW IF EXISTS `v2`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; @@ -558,7 +553,7 @@ USE `test`; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -/*!50001 DROP TABLE IF EXISTS `v3`*/; +/*!50001 DROP VIEW IF EXISTS `v3`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; @@ -571,7 +566,7 @@ USE `test`; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -/*!50001 DROP TABLE IF EXISTS `v4`*/; +/*!50001 DROP VIEW IF EXISTS `v4`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; @@ -584,7 +579,7 @@ USE `test`; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -/*!50001 DROP TABLE IF EXISTS `v5`*/; +/*!50001 DROP VIEW IF EXISTS `v5`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; |