diff options
Diffstat (limited to 'mysql-test')
44 files changed, 664 insertions, 133 deletions
diff --git a/mysql-test/README b/mysql-test/README index d925964fed5..f1c38f11716 100644 --- a/mysql-test/README +++ b/mysql-test/README @@ -51,7 +51,7 @@ options with which the server is started, restart the server during execution, etc.) You can create your own test cases. To create a test case, create a new -file in the t subdirectory using a text editor. The file should have a .test +file in the main subdirectory using a text editor. The file should have a .test extension. For example: # xemacs t/test_case_name.test @@ -60,7 +60,7 @@ In the file, put a set of SQL statements that create some tables, load test data, and run some queries to manipulate it. Your test should begin by dropping the tables you are going to create and -end by dropping them again. This ensures that you can run the test over +end by dropping them again. This ensures that you can run the test over and over again. If you are using mysqltest commands in your test case, you should create @@ -81,7 +81,7 @@ comments, you can create the result file in one of the following ways: # mysqltest --record --database test --result-file=r/test_case_name.result < t/test_case_name.test -When this is done, take a look at r/test_case_name.result . +When this is done, take a look at r/test_case_name.result. If the result is incorrect, you have found a bug. In this case, you should edit the test result to the correct results so that we can verify that the bug is corrected in future releases. diff --git a/mysql-test/include/switch_to_mysql_user.inc b/mysql-test/include/switch_to_mysql_user.inc index f5801db6114..4d67f30a10b 100644 --- a/mysql-test/include/switch_to_mysql_user.inc +++ b/mysql-test/include/switch_to_mysql_user.inc @@ -48,7 +48,7 @@ CREATE TABLE mysql.user ( is_role enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, default_role char(80) binary DEFAULT '' NOT NULL, max_statement_time decimal(12,6) DEFAULT 0 NOT NULL, - PRIMARY KEY Host (Host,User) + PRIMARY KEY (Host,User) ) engine=Aria transactional=1 CHARACTER SET utf8 COLLATE utf8_bin comment='Users and global privileges'; insert mysql.user select * from mysql.user_bak; rename table mysql.global_priv to mysql.global_priv_bak; diff --git a/mysql-test/main/create.result b/mysql-test/main/create.result index ea9014498e0..d8e9272e930 100644 --- a/mysql-test/main/create.result +++ b/mysql-test/main/create.result @@ -2008,3 +2008,7 @@ create table t1 (i int, j int, key(i), key(i)) as select 1 as i, 2 as j; Warnings: Note 1831 Duplicate index `i_2`. This is deprecated and will be disallowed in a future release drop table t1; +CREATE TABLE t1 ( id1 INT, id2 INT, CONSTRAINT `foo` PRIMARY KEY (id1), CONSTRAINT `bar` UNIQUE KEY(id2)); +Warnings: +Warning 1280 Name 'foo' ignored for PRIMARY key. +DROP TABLE t1; diff --git a/mysql-test/main/create.test b/mysql-test/main/create.test index 4bf6ce99504..8f7b06f5281 100644 --- a/mysql-test/main/create.test +++ b/mysql-test/main/create.test @@ -1864,3 +1864,10 @@ create table t1; # create table t1 (i int, j int, key(i), key(i)) as select 1 as i, 2 as j; drop table t1; + +# +# MDEV-17544 No warning when trying to name a primary key constraint. +# +CREATE TABLE t1 ( id1 INT, id2 INT, CONSTRAINT `foo` PRIMARY KEY (id1), CONSTRAINT `bar` UNIQUE KEY(id2)); +DROP TABLE t1; + diff --git a/mysql-test/main/ctype_collate.result b/mysql-test/main/ctype_collate.result index 5e8c5adac8f..93bf07908b5 100644 --- a/mysql-test/main/ctype_collate.result +++ b/mysql-test/main/ctype_collate.result @@ -732,6 +732,8 @@ CREATE TABLE t1 ( b LONGTEXT CHARACTER SET "latin1" COLLATE "latin1_bin", PRIMARY KEY b (b(32)) ); +Warnings: +Warning 1280 Name 'b' ignored for PRIMARY key. INSERT INTO t1 (b) VALUES ('a'), (_binary 0x1), (_binary 0x0), (''); explain select hex(b) from t1 force index (PRIMARY) where b<'zzz'; diff --git a/mysql-test/main/ctype_latin2_ch.result b/mysql-test/main/ctype_latin2_ch.result index a396bc77fb6..962c29f7ab4 100644 --- a/mysql-test/main/ctype_latin2_ch.result +++ b/mysql-test/main/ctype_latin2_ch.result @@ -10,6 +10,8 @@ tt char(255) not null insert into t1 values (1,'Aa'); insert into t1 values (2,'Aas'); alter table t1 add primary key aaa(tt); +Warnings: +Warning 1280 Name 'aaa' ignored for PRIMARY key. select * from t1 where tt like 'Aa%'; id tt 1 Aa diff --git a/mysql-test/main/func_hybrid_type.result b/mysql-test/main/func_hybrid_type.result index 664a872cf4c..83965bc6db5 100644 --- a/mysql-test/main/func_hybrid_type.result +++ b/mysql-test/main/func_hybrid_type.result @@ -3915,6 +3915,21 @@ t1 CREATE TABLE `t1` ( DROP TABLE t1; SET sql_mode=DEFAULT; # +# MDEV-18456 Assertion `item->maybe_null' failed in Type_handler_temporal_result::make_sort_key +# +CREATE TABLE t1 (t TIME NOT NULL); +INSERT INTO t1 VALUES ('00:20:11'),('14:52:05'); +SELECT GREATEST('9999', t) FROM t1 ORDER BY 1; +GREATEST('9999', t) +NULL +NULL +Warnings: +Warning 1292 Incorrect time value: '9999' +Warning 1292 Incorrect time value: '9999' +Warning 1292 Incorrect time value: '9999' +Warning 1292 Incorrect time value: '9999' +DROP TABLE t1; +# # End of 10.3 tests # # diff --git a/mysql-test/main/func_hybrid_type.test b/mysql-test/main/func_hybrid_type.test index 71536339b4b..31a096c3305 100644 --- a/mysql-test/main/func_hybrid_type.test +++ b/mysql-test/main/func_hybrid_type.test @@ -711,6 +711,16 @@ DROP TABLE t1; SET sql_mode=DEFAULT; --echo # +--echo # MDEV-18456 Assertion `item->maybe_null' failed in Type_handler_temporal_result::make_sort_key +--echo # + +CREATE TABLE t1 (t TIME NOT NULL); +INSERT INTO t1 VALUES ('00:20:11'),('14:52:05'); +SELECT GREATEST('9999', t) FROM t1 ORDER BY 1; +DROP TABLE t1; + + +--echo # --echo # End of 10.3 tests --echo # diff --git a/mysql-test/main/func_str.result b/mysql-test/main/func_str.result index f916aae7e9d..c637274e3cc 100644 --- a/mysql-test/main/func_str.result +++ b/mysql-test/main/func_str.result @@ -5068,6 +5068,90 @@ NULL DROP TABLE t1; # +# MDEV-20292 REPEAT(x,-1) returns a wrong data type +# +CREATE OR REPLACE TABLE t1 (i BIGINT); +INSERT INTO t1 VALUES (42); +CREATE OR REPLACE TABLE t2 AS SELECT +REPEAT(i,NULL) AS cn, +REPEAT(i,0) AS c0, +REPEAT(i,-1) AS c1 +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `cn` char(0) CHARACTER SET utf8 DEFAULT NULL, + `c0` char(0) CHARACTER SET utf8 DEFAULT NULL, + `c1` char(0) CHARACTER SET utf8 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1, t2; +# +# MDEV-20303 SPACE(-1) returns a wrong data type +# +CREATE TABLE t1 (i BIGINT); +INSERT INTO t1 VALUES (42); +CREATE TABLE t2 AS SELECT +SPACE(NULL) AS cn, +SPACE(0) AS c0, +SPACE(-1) AS c1 +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `cn` char(0) CHARACTER SET utf8 DEFAULT NULL, + `c0` char(0) CHARACTER SET utf8 DEFAULT NULL, + `c1` char(0) CHARACTER SET utf8 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1, t2; +CREATE TABLE t1 (i BIGINT); +INSERT INTO t1 VALUES (42); +CREATE TABLE t2 AS SELECT +LPAD(i,NULL,'a') AS cn, +LPAD(i,0,'a') AS c0, +LPAD(i,-1,'a') AS c1 +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `cn` char(0) CHARACTER SET utf8 DEFAULT NULL, + `c0` char(0) CHARACTER SET utf8 DEFAULT NULL, + `c1` char(0) CHARACTER SET utf8 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1, t2; +CREATE TABLE t1 (i BIGINT); +INSERT INTO t1 VALUES (42); +CREATE TABLE t2 AS SELECT +RPAD(i,NULL,'a') AS cn, +RPAD(i,0,'a') AS c0, +RPAD(i,-1,'a') AS c1 +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `cn` char(0) CHARACTER SET utf8 DEFAULT NULL, + `c0` char(0) CHARACTER SET utf8 DEFAULT NULL, + `c1` char(0) CHARACTER SET utf8 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1, t2; +CREATE TABLE t1 (i BIGINT); +INSERT INTO t1 VALUES (42); +CREATE TABLE t2 AS SELECT +LEFT(i,NULL) AS cn, +LEFT(i,0) AS c0, +LEFT(i,18446744073709551615) AS c1 +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `cn` char(0) CHARACTER SET utf8 DEFAULT NULL, + `c0` char(0) CHARACTER SET utf8 DEFAULT NULL, + `c1` varchar(20) CHARACTER SET utf8 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT c1 FROM t2; +c1 +42 +DROP TABLE t1, t2; +# # End of 10.3 tests # # diff --git a/mysql-test/main/func_str.test b/mysql-test/main/func_str.test index 1abc36de910..bc4ab2396f6 100644 --- a/mysql-test/main/func_str.test +++ b/mysql-test/main/func_str.test @@ -2028,6 +2028,66 @@ SELECT LPAD( c, 0, '?' ) FROM t1; SELECT RPAD( c, 0, '?' ) FROM t1; DROP TABLE t1; +--echo # +--echo # MDEV-20292 REPEAT(x,-1) returns a wrong data type +--echo # + +CREATE OR REPLACE TABLE t1 (i BIGINT); +INSERT INTO t1 VALUES (42); +CREATE OR REPLACE TABLE t2 AS SELECT + REPEAT(i,NULL) AS cn, + REPEAT(i,0) AS c0, + REPEAT(i,-1) AS c1 +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t1, t2; + + +--echo # +--echo # MDEV-20303 SPACE(-1) returns a wrong data type +--echo # + +CREATE TABLE t1 (i BIGINT); +INSERT INTO t1 VALUES (42); +CREATE TABLE t2 AS SELECT + SPACE(NULL) AS cn, + SPACE(0) AS c0, + SPACE(-1) AS c1 +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t1, t2; + +CREATE TABLE t1 (i BIGINT); +INSERT INTO t1 VALUES (42); +CREATE TABLE t2 AS SELECT + LPAD(i,NULL,'a') AS cn, + LPAD(i,0,'a') AS c0, + LPAD(i,-1,'a') AS c1 +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t1, t2; + +CREATE TABLE t1 (i BIGINT); +INSERT INTO t1 VALUES (42); +CREATE TABLE t2 AS SELECT + RPAD(i,NULL,'a') AS cn, + RPAD(i,0,'a') AS c0, + RPAD(i,-1,'a') AS c1 +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t1, t2; + +CREATE TABLE t1 (i BIGINT); +INSERT INTO t1 VALUES (42); +CREATE TABLE t2 AS SELECT + LEFT(i,NULL) AS cn, + LEFT(i,0) AS c0, + LEFT(i,18446744073709551615) AS c1 +FROM t1; +SHOW CREATE TABLE t2; +SELECT c1 FROM t2; +DROP TABLE t1, t2; + --echo # --echo # End of 10.3 tests diff --git a/mysql-test/main/gis.result b/mysql-test/main/gis.result index 2f80afe37dd..09afb6033b3 100644 --- a/mysql-test/main/gis.result +++ b/mysql-test/main/gis.result @@ -816,11 +816,15 @@ POINT(1 1) drop function fn3; create table t1(pt POINT); alter table t1 add primary key pti(pt); +Warnings: +Warning 1280 Name 'pti' ignored for PRIMARY key. drop table t1; create table t1(pt GEOMETRY); alter table t1 add primary key pti(pt); ERROR 42000: BLOB/TEXT column 'pt' used in key specification without a key length alter table t1 add primary key pti(pt(20)); +Warnings: +Warning 1280 Name 'pti' ignored for PRIMARY key. drop table t1; create table t1 select GeomFromText('point(1 1)'); desc t1; diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index cc536126332..0aad061f299 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -3108,6 +3108,8 @@ CREATE TABLE t2 ( a2 int, b2 int, filler2 char(64) default ' ', PRIMARY KEY idx(a2,b2,filler2) ) ; +Warnings: +Warning 1280 Name 'idx' ignored for PRIMARY key. CREATE TABLE t3 (b3 int, c3 int, INDEX idx(b3)); INSERT INTO t1(a1) VALUES (4), (7), (1), (9), (8), (5), (3), (6), (2); diff --git a/mysql-test/main/system_mysql_db_fix40123.result b/mysql-test/main/system_mysql_db_fix40123.result index 6ec1e35b0b7..ad7341a2a5c 100644 --- a/mysql-test/main/system_mysql_db_fix40123.result +++ b/mysql-test/main/system_mysql_db_fix40123.result @@ -1,10 +1,16 @@ use test; set storage_engine=myisam; CREATE TABLE db ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db,User), KEY User (User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Database privileges'; +Warnings: +Warning 1280 Name 'Host' ignored for PRIMARY key. INSERT INTO db VALUES ('%','test','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y'); INSERT INTO db VALUES ('%','test\_%','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y'); CREATE TABLE host ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Host privileges; Merged with database privileges'; +Warnings: +Warning 1280 Name 'Host' ignored for PRIMARY key. CREATE TABLE user ( Host char(60) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Password char(41) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Reload_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Shutdown_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Process_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci DEFAULT '' NOT NULL, ssl_cipher BLOB NOT NULL, x509_issuer BLOB NOT NULL, x509_subject BLOB NOT NULL, max_questions int(11) unsigned DEFAULT 0 NOT NULL, max_updates int(11) unsigned DEFAULT 0 NOT NULL, max_connections int(11) unsigned DEFAULT 0 NOT NULL, PRIMARY KEY Host (Host,User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Users and global privileges'; +Warnings: +Warning 1280 Name 'Host' ignored for PRIMARY key. INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0); INSERT INTO user VALUES ('localhost','','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0); CREATE TABLE func ( name char(64) binary DEFAULT '' NOT NULL, ret tinyint(1) DEFAULT '0' NOT NULL, dl char(128) DEFAULT '' NOT NULL, type enum ('function','aggregate') COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (name) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='User defined functions'; @@ -15,10 +21,20 @@ CREATE TABLE help_category ( help_category_id smallint unsigned not null, name v CREATE TABLE help_relation ( help_topic_id int unsigned not null references help_topic, help_keyword_id int unsigned not null references help_keyword, primary key (help_keyword_id, help_topic_id) ) engine=MyISAM CHARACTER SET utf8 comment='keyword-topic relation'; CREATE TABLE help_keyword ( help_keyword_id int unsigned not null, name varchar(64) not null, primary key (help_keyword_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8 comment='help keywords'; CREATE TABLE time_zone_name ( Name char(64) NOT NULL, Time_zone_id int unsigned NOT NULL, PRIMARY KEY Name (Name) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone names'; +Warnings: +Warning 1280 Name 'Name' ignored for PRIMARY key. CREATE TABLE time_zone ( Time_zone_id int unsigned NOT NULL auto_increment, Use_leap_seconds enum('Y','N') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY TzId (Time_zone_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zones'; +Warnings: +Warning 1280 Name 'TzId' ignored for PRIMARY key. CREATE TABLE time_zone_transition ( Time_zone_id int unsigned NOT NULL, Transition_time bigint signed NOT NULL, Transition_type_id int unsigned NOT NULL, PRIMARY KEY TzIdTranTime (Time_zone_id, Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transitions'; +Warnings: +Warning 1280 Name 'TzIdTranTime' ignored for PRIMARY key. CREATE TABLE time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, Transition_type_id int unsigned NOT NULL, Offset int signed DEFAULT 0 NOT NULL, Is_DST tinyint unsigned DEFAULT 0 NOT NULL, Abbreviation char(8) DEFAULT '' NOT NULL, PRIMARY KEY TzIdTrTId (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types'; +Warnings: +Warning 1280 Name 'TzIdTrTId' ignored for PRIMARY key. CREATE TABLE time_zone_leap_second ( Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY TranTime (Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Leap seconds information for time zones'; +Warnings: +Warning 1280 Name 'TranTime' ignored for PRIMARY key. show tables; Tables_in_db column_stats diff --git a/mysql-test/main/system_mysql_db_fix50030.result b/mysql-test/main/system_mysql_db_fix50030.result index 9f2729b86d1..842f9aca087 100644 --- a/mysql-test/main/system_mysql_db_fix50030.result +++ b/mysql-test/main/system_mysql_db_fix50030.result @@ -1,10 +1,16 @@ use test; set storage_engine=myisam; CREATE TABLE db ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db,User), KEY User (User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Database privileges'; +Warnings: +Warning 1280 Name 'Host' ignored for PRIMARY key. INSERT INTO db VALUES ('%','test','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N'); INSERT INTO db VALUES ('%','test\_%','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N'); CREATE TABLE host ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Host privileges; Merged with database privileges'; +Warnings: +Warning 1280 Name 'Host' ignored for PRIMARY key. CREATE TABLE user ( Host char(60) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Password char(41) character set latin1 collate latin1_bin DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Reload_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Shutdown_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Process_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_user_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci DEFAULT '' NOT NULL, ssl_cipher BLOB NOT NULL, x509_issuer BLOB NOT NULL, x509_subject BLOB NOT NULL, max_questions int(11) unsigned DEFAULT 0 NOT NULL, max_updates int(11) unsigned DEFAULT 0 NOT NULL, max_connections int(11) unsigned DEFAULT 0 NOT NULL, max_user_connections int(11) unsigned DEFAULT 0 NOT NULL, PRIMARY KEY Host (Host,User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Users and global privileges'; +Warnings: +Warning 1280 Name 'Host' ignored for PRIMARY key. INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0); INSERT INTO user VALUES ('localhost','','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0, 0); CREATE TABLE func ( name char(64) binary DEFAULT '' NOT NULL, ret tinyint(1) DEFAULT '0' NOT NULL, dl char(128) DEFAULT '' NOT NULL, type enum ('function','aggregate') COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (name) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='User defined functions'; @@ -15,10 +21,20 @@ CREATE TABLE help_category ( help_category_id smallint unsigned not null, name c CREATE TABLE help_relation ( help_topic_id int unsigned not null references help_topic, help_keyword_id int unsigned not null references help_keyword, primary key (help_keyword_id, help_topic_id) ) engine=MyISAM CHARACTER SET utf8 comment='keyword-topic relation'; CREATE TABLE help_keyword ( help_keyword_id int unsigned not null, name char(64) not null, primary key (help_keyword_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8 comment='help keywords'; CREATE TABLE time_zone_name ( Name char(64) NOT NULL, Time_zone_id int unsigned NOT NULL, PRIMARY KEY Name (Name) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone names'; +Warnings: +Warning 1280 Name 'Name' ignored for PRIMARY key. CREATE TABLE time_zone ( Time_zone_id int unsigned NOT NULL auto_increment, Use_leap_seconds enum('Y','N') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY TzId (Time_zone_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zones'; +Warnings: +Warning 1280 Name 'TzId' ignored for PRIMARY key. CREATE TABLE time_zone_transition ( Time_zone_id int unsigned NOT NULL, Transition_time bigint signed NOT NULL, Transition_type_id int unsigned NOT NULL, PRIMARY KEY TzIdTranTime (Time_zone_id, Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transitions'; +Warnings: +Warning 1280 Name 'TzIdTranTime' ignored for PRIMARY key. CREATE TABLE time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, Transition_type_id int unsigned NOT NULL, Offset int signed DEFAULT 0 NOT NULL, Is_DST tinyint unsigned DEFAULT 0 NOT NULL, Abbreviation char(8) DEFAULT '' NOT NULL, PRIMARY KEY TzIdTrTId (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types'; +Warnings: +Warning 1280 Name 'TzIdTrTId' ignored for PRIMARY key. CREATE TABLE time_zone_leap_second ( Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY TranTime (Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Leap seconds information for time zones'; +Warnings: +Warning 1280 Name 'TranTime' ignored for PRIMARY key. CREATE TABLE proc ( db char(64) collate utf8_bin DEFAULT '' NOT NULL, name char(64) DEFAULT '' NOT NULL, type enum('FUNCTION','PROCEDURE') NOT NULL, specific_name char(64) DEFAULT '' NOT NULL, language enum('SQL') DEFAULT 'SQL' NOT NULL, sql_data_access enum('CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA' ) DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL, security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list blob DEFAULT '' NOT NULL, returns char(64) DEFAULT '' NOT NULL, body longblob DEFAULT '' NOT NULL, definer char(77) collate utf8_bin DEFAULT '' NOT NULL, created timestamp, modified timestamp, sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'NOT_USED', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE' ) DEFAULT '' NOT NULL, comment char(64) collate utf8_bin DEFAULT '' NOT NULL, PRIMARY KEY (db,name,type) ) engine=MyISAM character set utf8 comment='Stored Procedures'; CREATE TABLE procs_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Routine_name char(64) binary DEFAULT '' NOT NULL, Routine_type enum('FUNCTION','PROCEDURE') NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Proc_priv set('Execute','Alter Routine','Grant') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Timestamp timestamp, PRIMARY KEY (Host,Db,User,Routine_name,Routine_type), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Procedure privileges'; CREATE TABLE servers ( Server_name char(64) NOT NULL DEFAULT '', Host char(64) NOT NULL DEFAULT '', Db char(64) NOT NULL DEFAULT '', Username char(64) NOT NULL DEFAULT '', Password char(64) NOT NULL DEFAULT '', Port INT(4) NOT NULL DEFAULT '0', Socket char(64) NOT NULL DEFAULT '', Wrapper char(64) NOT NULL DEFAULT '', Owner char(64) NOT NULL DEFAULT '', PRIMARY KEY (Server_name)) CHARACTER SET utf8 comment='MySQL Foreign Servers table'; diff --git a/mysql-test/main/system_mysql_db_fix50117.result b/mysql-test/main/system_mysql_db_fix50117.result index 08f334e9bcc..078cdcbbe03 100644 --- a/mysql-test/main/system_mysql_db_fix50117.result +++ b/mysql-test/main/system_mysql_db_fix50117.result @@ -1,24 +1,24 @@ use test; -CREATE TABLE IF NOT EXISTS db ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Event_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db,User), KEY User (User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Database privileges'; -CREATE TABLE IF NOT EXISTS host ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Host privileges; Merged with database privileges'; -CREATE TABLE IF NOT EXISTS user ( Host char(60) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Password char(41) character set latin1 collate latin1_bin DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Reload_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Shutdown_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Process_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_user_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Event_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci DEFAULT '' NOT NULL, ssl_cipher BLOB NOT NULL, x509_issuer BLOB NOT NULL, x509_subject BLOB NOT NULL, max_questions int(11) unsigned DEFAULT 0 NOT NULL, max_updates int(11) unsigned DEFAULT 0 NOT NULL, max_connections int(11) unsigned DEFAULT 0 NOT NULL, max_user_connections int(11) unsigned DEFAULT 0 NOT NULL, PRIMARY KEY Host (Host,User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Users and global privileges'; +CREATE TABLE IF NOT EXISTS db ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Event_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY (Host,Db,User), KEY User (User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Database privileges'; +CREATE TABLE IF NOT EXISTS host ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY (Host,Db) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Host privileges; Merged with database privileges'; +CREATE TABLE IF NOT EXISTS user ( Host char(60) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Password char(41) character set latin1 collate latin1_bin DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Reload_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Shutdown_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Process_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_user_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Event_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci DEFAULT '' NOT NULL, ssl_cipher BLOB NOT NULL, x509_issuer BLOB NOT NULL, x509_subject BLOB NOT NULL, max_questions int(11) unsigned DEFAULT 0 NOT NULL, max_updates int(11) unsigned DEFAULT 0 NOT NULL, max_connections int(11) unsigned DEFAULT 0 NOT NULL, max_user_connections int(11) unsigned DEFAULT 0 NOT NULL, PRIMARY KEY (Host,User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Users and global privileges'; CREATE TABLE IF NOT EXISTS func ( name char(64) binary DEFAULT '' NOT NULL, ret tinyint(1) DEFAULT '0' NOT NULL, dl char(128) DEFAULT '' NOT NULL, type enum ('function','aggregate') COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (name) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='User defined functions'; CREATE TABLE IF NOT EXISTS plugin ( name char(64) binary DEFAULT '' NOT NULL, dl char(128) DEFAULT '' NOT NULL, PRIMARY KEY (name) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='MySQL plugins'; CREATE TABLE IF NOT EXISTS servers ( Server_name char(64) NOT NULL DEFAULT '', Host char(64) NOT NULL DEFAULT '', Db char(64) NOT NULL DEFAULT '', Username char(64) NOT NULL DEFAULT '', Password char(64) NOT NULL DEFAULT '', Port INT(4) NOT NULL DEFAULT '0', Socket char(64) NOT NULL DEFAULT '', Wrapper char(64) NOT NULL DEFAULT '', Owner char(64) NOT NULL DEFAULT '', PRIMARY KEY (Server_name)) CHARACTER SET utf8 comment='MySQL Foreign Servers table'; CREATE TABLE IF NOT EXISTS tables_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Table_name char(64) binary DEFAULT '' NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Table privileges'; CREATE TABLE IF NOT EXISTS columns_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Table_name char(64) binary DEFAULT '' NOT NULL, Column_name char(64) binary DEFAULT '' NOT NULL, Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name,Column_name) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Column privileges'; -CREATE TABLE IF NOT EXISTS help_topic ( help_topic_id int unsigned not null, name char(64) not null, help_category_id smallint unsigned not null, description text not null, example text not null, url char(128) not null, primary key (help_topic_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8 comment='help topics'; +CREATE TABLE IF NOT EXISTS help_topic ( help_topic_id int unsigned not null, name char(64) not null, help_category_id smallint unsigned not null, description text not null, example text not null, url char(128) not null, primary key (help_topic_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8 comment='help topics'; CREATE TABLE IF NOT EXISTS help_category ( help_category_id smallint unsigned not null, name char(64) not null, parent_category_id smallint unsigned null, url char(128) not null, primary key (help_category_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8 comment='help categories'; -CREATE TABLE IF NOT EXISTS help_relation ( help_topic_id int unsigned not null references help_topic, help_keyword_id int unsigned not null references help_keyword, primary key (help_keyword_id, help_topic_id) ) engine=MyISAM CHARACTER SET utf8 comment='keyword-topic relation'; +CREATE TABLE IF NOT EXISTS help_relation ( help_topic_id int unsigned not null references help_topic, help_keyword_id int unsigned not null references help_keyword, primary key (help_keyword_id, help_topic_id) ) engine=MyISAM CHARACTER SET utf8 comment='keyword-topic relation'; CREATE TABLE IF NOT EXISTS help_keyword ( help_keyword_id int unsigned not null, name char(64) not null, primary key (help_keyword_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8 comment='help keywords'; -CREATE TABLE IF NOT EXISTS time_zone_name ( Name char(64) NOT NULL, Time_zone_id int unsigned NOT NULL, PRIMARY KEY Name (Name) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone names'; -CREATE TABLE IF NOT EXISTS time_zone ( Time_zone_id int unsigned NOT NULL auto_increment, Use_leap_seconds enum('Y','N') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY TzId (Time_zone_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zones'; -CREATE TABLE IF NOT EXISTS time_zone_transition ( Time_zone_id int unsigned NOT NULL, Transition_time bigint signed NOT NULL, Transition_type_id int unsigned NOT NULL, PRIMARY KEY TzIdTranTime (Time_zone_id, Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transitions'; -CREATE TABLE IF NOT EXISTS time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, Transition_type_id int unsigned NOT NULL, Offset int signed DEFAULT 0 NOT NULL, Is_DST tinyint unsigned DEFAULT 0 NOT NULL, Abbreviation char(8) DEFAULT '' NOT NULL, PRIMARY KEY TzIdTrTId (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types'; -CREATE TABLE IF NOT EXISTS time_zone_leap_second ( Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY TranTime (Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Leap seconds information for time zones'; -CREATE TABLE IF NOT EXISTS proc ( db char(64) collate utf8_bin DEFAULT '' NOT NULL, name char(64) DEFAULT '' NOT NULL, type enum('FUNCTION','PROCEDURE') NOT NULL, specific_name char(64) DEFAULT '' NOT NULL, language enum('SQL') DEFAULT 'SQL' NOT NULL, sql_data_access enum('CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA' ) DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL, security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list blob NOT NULL, returns char(64) DEFAULT '' NOT NULL, body longblob NOT NULL, definer char(77) collate utf8_bin DEFAULT '' NOT NULL, created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'NOT_USED', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE' ) DEFAULT '' NOT NULL, comment char(64) collate utf8_bin DEFAULT '' NOT NULL, PRIMARY KEY (db,name,type) ) engine=MyISAM character set utf8 comment='Stored Procedures'; +CREATE TABLE IF NOT EXISTS time_zone_name ( Name char(64) NOT NULL, Time_zone_id int unsigned NOT NULL, PRIMARY KEY (Name) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone names'; +CREATE TABLE IF NOT EXISTS time_zone ( Time_zone_id int unsigned NOT NULL auto_increment, Use_leap_seconds enum('Y','N') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY (Time_zone_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zones'; +CREATE TABLE IF NOT EXISTS time_zone_transition ( Time_zone_id int unsigned NOT NULL, Transition_time bigint signed NOT NULL, Transition_type_id int unsigned NOT NULL, PRIMARY KEY (Time_zone_id, Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transitions'; +CREATE TABLE IF NOT EXISTS time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, Transition_type_id int unsigned NOT NULL, Offset int signed DEFAULT 0 NOT NULL, Is_DST tinyint unsigned DEFAULT 0 NOT NULL, Abbreviation char(8) DEFAULT '' NOT NULL, PRIMARY KEY (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types'; +CREATE TABLE IF NOT EXISTS time_zone_leap_second ( Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY (Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Leap seconds information for time zones'; +CREATE TABLE IF NOT EXISTS proc ( db char(64) collate utf8_bin DEFAULT '' NOT NULL, name char(64) DEFAULT '' NOT NULL, type enum('FUNCTION','PROCEDURE') NOT NULL, specific_name char(64) DEFAULT '' NOT NULL, language enum('SQL') DEFAULT 'SQL' NOT NULL, sql_data_access enum('CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA' ) DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL, security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list blob NOT NULL, returns char(64) DEFAULT '' NOT NULL, body longblob NOT NULL, definer char(77) collate utf8_bin DEFAULT '' NOT NULL, created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'NOT_USED', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE' ) DEFAULT '' NOT NULL, comment char(64) collate utf8_bin DEFAULT '' NOT NULL, PRIMARY KEY (db,name,type) ) engine=MyISAM character set utf8 comment='Stored Procedures'; CREATE TABLE IF NOT EXISTS procs_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Routine_name char(64) binary DEFAULT '' NOT NULL, Routine_type enum('FUNCTION','PROCEDURE') NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Proc_priv set('Execute','Alter Routine','Grant') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (Host,Db,User,Routine_name,Routine_type), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Procedure privileges'; -CREATE TABLE IF NOT EXISTS event ( db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', name char(64) CHARACTER SET utf8 NOT NULL default '', body longblob NOT NULL, definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', execute_at DATETIME default NULL, interval_value int(11) default NULL, interval_field ENUM('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') default NULL, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', last_executed DATETIME default NULL, starts DATETIME default NULL, ends DATETIME default NULL, status ENUM('ENABLED','DISABLED') NOT NULL default 'ENABLED', on_completion ENUM('DROP','PRESERVE') NOT NULL default 'DROP', sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') DEFAULT '' NOT NULL, comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', time_zone char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', PRIMARY KEY (db, name) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'Events'; +CREATE TABLE IF NOT EXISTS event ( db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', name char(64) CHARACTER SET utf8 NOT NULL default '', body longblob NOT NULL, definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', execute_at DATETIME default NULL, interval_value int(11) default NULL, interval_field ENUM('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') default NULL, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', last_executed DATETIME default NULL, starts DATETIME default NULL, ends DATETIME default NULL, status ENUM('ENABLED','DISABLED') NOT NULL default 'ENABLED', on_completion ENUM('DROP','PRESERVE') NOT NULL default 'DROP', sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') DEFAULT '' NOT NULL, comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', time_zone char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', PRIMARY KEY (db, name) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'Events'; show tables; Tables_in_db column_stats diff --git a/mysql-test/main/system_mysql_db_fix50117.test b/mysql-test/main/system_mysql_db_fix50117.test index 9755415c6e4..28b8621a638 100644 --- a/mysql-test/main/system_mysql_db_fix50117.test +++ b/mysql-test/main/system_mysql_db_fix50117.test @@ -22,26 +22,26 @@ if (!$MYSQL_FIX_PRIVILEGE_TABLES) use test; # create system tables as in mysql-5.1.17 -CREATE TABLE IF NOT EXISTS db ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Event_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db,User), KEY User (User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Database privileges'; -CREATE TABLE IF NOT EXISTS host ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Host privileges; Merged with database privileges'; -CREATE TABLE IF NOT EXISTS user ( Host char(60) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Password char(41) character set latin1 collate latin1_bin DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Reload_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Shutdown_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Process_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_user_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Event_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci DEFAULT '' NOT NULL, ssl_cipher BLOB NOT NULL, x509_issuer BLOB NOT NULL, x509_subject BLOB NOT NULL, max_questions int(11) unsigned DEFAULT 0 NOT NULL, max_updates int(11) unsigned DEFAULT 0 NOT NULL, max_connections int(11) unsigned DEFAULT 0 NOT NULL, max_user_connections int(11) unsigned DEFAULT 0 NOT NULL, PRIMARY KEY Host (Host,User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Users and global privileges'; +CREATE TABLE IF NOT EXISTS db ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Event_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY (Host,Db,User), KEY User (User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Database privileges'; +CREATE TABLE IF NOT EXISTS host ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY (Host,Db) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Host privileges; Merged with database privileges'; +CREATE TABLE IF NOT EXISTS user ( Host char(60) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Password char(41) character set latin1 collate latin1_bin DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Reload_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Shutdown_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Process_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_user_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Event_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci DEFAULT '' NOT NULL, ssl_cipher BLOB NOT NULL, x509_issuer BLOB NOT NULL, x509_subject BLOB NOT NULL, max_questions int(11) unsigned DEFAULT 0 NOT NULL, max_updates int(11) unsigned DEFAULT 0 NOT NULL, max_connections int(11) unsigned DEFAULT 0 NOT NULL, max_user_connections int(11) unsigned DEFAULT 0 NOT NULL, PRIMARY KEY (Host,User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Users and global privileges'; CREATE TABLE IF NOT EXISTS func ( name char(64) binary DEFAULT '' NOT NULL, ret tinyint(1) DEFAULT '0' NOT NULL, dl char(128) DEFAULT '' NOT NULL, type enum ('function','aggregate') COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (name) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='User defined functions'; CREATE TABLE IF NOT EXISTS plugin ( name char(64) binary DEFAULT '' NOT NULL, dl char(128) DEFAULT '' NOT NULL, PRIMARY KEY (name) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='MySQL plugins'; CREATE TABLE IF NOT EXISTS servers ( Server_name char(64) NOT NULL DEFAULT '', Host char(64) NOT NULL DEFAULT '', Db char(64) NOT NULL DEFAULT '', Username char(64) NOT NULL DEFAULT '', Password char(64) NOT NULL DEFAULT '', Port INT(4) NOT NULL DEFAULT '0', Socket char(64) NOT NULL DEFAULT '', Wrapper char(64) NOT NULL DEFAULT '', Owner char(64) NOT NULL DEFAULT '', PRIMARY KEY (Server_name)) CHARACTER SET utf8 comment='MySQL Foreign Servers table'; CREATE TABLE IF NOT EXISTS tables_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Table_name char(64) binary DEFAULT '' NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Table privileges'; CREATE TABLE IF NOT EXISTS columns_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Table_name char(64) binary DEFAULT '' NOT NULL, Column_name char(64) binary DEFAULT '' NOT NULL, Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name,Column_name) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Column privileges'; -CREATE TABLE IF NOT EXISTS help_topic ( help_topic_id int unsigned not null, name char(64) not null, help_category_id smallint unsigned not null, description text not null, example text not null, url char(128) not null, primary key (help_topic_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8 comment='help topics'; +CREATE TABLE IF NOT EXISTS help_topic ( help_topic_id int unsigned not null, name char(64) not null, help_category_id smallint unsigned not null, description text not null, example text not null, url char(128) not null, primary key (help_topic_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8 comment='help topics'; CREATE TABLE IF NOT EXISTS help_category ( help_category_id smallint unsigned not null, name char(64) not null, parent_category_id smallint unsigned null, url char(128) not null, primary key (help_category_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8 comment='help categories'; -CREATE TABLE IF NOT EXISTS help_relation ( help_topic_id int unsigned not null references help_topic, help_keyword_id int unsigned not null references help_keyword, primary key (help_keyword_id, help_topic_id) ) engine=MyISAM CHARACTER SET utf8 comment='keyword-topic relation'; +CREATE TABLE IF NOT EXISTS help_relation ( help_topic_id int unsigned not null references help_topic, help_keyword_id int unsigned not null references help_keyword, primary key (help_keyword_id, help_topic_id) ) engine=MyISAM CHARACTER SET utf8 comment='keyword-topic relation'; CREATE TABLE IF NOT EXISTS help_keyword ( help_keyword_id int unsigned not null, name char(64) not null, primary key (help_keyword_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8 comment='help keywords'; -CREATE TABLE IF NOT EXISTS time_zone_name ( Name char(64) NOT NULL, Time_zone_id int unsigned NOT NULL, PRIMARY KEY Name (Name) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone names'; -CREATE TABLE IF NOT EXISTS time_zone ( Time_zone_id int unsigned NOT NULL auto_increment, Use_leap_seconds enum('Y','N') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY TzId (Time_zone_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zones'; -CREATE TABLE IF NOT EXISTS time_zone_transition ( Time_zone_id int unsigned NOT NULL, Transition_time bigint signed NOT NULL, Transition_type_id int unsigned NOT NULL, PRIMARY KEY TzIdTranTime (Time_zone_id, Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transitions'; -CREATE TABLE IF NOT EXISTS time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, Transition_type_id int unsigned NOT NULL, Offset int signed DEFAULT 0 NOT NULL, Is_DST tinyint unsigned DEFAULT 0 NOT NULL, Abbreviation char(8) DEFAULT '' NOT NULL, PRIMARY KEY TzIdTrTId (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types'; -CREATE TABLE IF NOT EXISTS time_zone_leap_second ( Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY TranTime (Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Leap seconds information for time zones'; -CREATE TABLE IF NOT EXISTS proc ( db char(64) collate utf8_bin DEFAULT '' NOT NULL, name char(64) DEFAULT '' NOT NULL, type enum('FUNCTION','PROCEDURE') NOT NULL, specific_name char(64) DEFAULT '' NOT NULL, language enum('SQL') DEFAULT 'SQL' NOT NULL, sql_data_access enum('CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA' ) DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL, security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list blob NOT NULL, returns char(64) DEFAULT '' NOT NULL, body longblob NOT NULL, definer char(77) collate utf8_bin DEFAULT '' NOT NULL, created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'NOT_USED', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE' ) DEFAULT '' NOT NULL, comment char(64) collate utf8_bin DEFAULT '' NOT NULL, PRIMARY KEY (db,name,type) ) engine=MyISAM character set utf8 comment='Stored Procedures'; +CREATE TABLE IF NOT EXISTS time_zone_name ( Name char(64) NOT NULL, Time_zone_id int unsigned NOT NULL, PRIMARY KEY (Name) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone names'; +CREATE TABLE IF NOT EXISTS time_zone ( Time_zone_id int unsigned NOT NULL auto_increment, Use_leap_seconds enum('Y','N') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY (Time_zone_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zones'; +CREATE TABLE IF NOT EXISTS time_zone_transition ( Time_zone_id int unsigned NOT NULL, Transition_time bigint signed NOT NULL, Transition_type_id int unsigned NOT NULL, PRIMARY KEY (Time_zone_id, Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transitions'; +CREATE TABLE IF NOT EXISTS time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, Transition_type_id int unsigned NOT NULL, Offset int signed DEFAULT 0 NOT NULL, Is_DST tinyint unsigned DEFAULT 0 NOT NULL, Abbreviation char(8) DEFAULT '' NOT NULL, PRIMARY KEY (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types'; +CREATE TABLE IF NOT EXISTS time_zone_leap_second ( Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY (Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Leap seconds information for time zones'; +CREATE TABLE IF NOT EXISTS proc ( db char(64) collate utf8_bin DEFAULT '' NOT NULL, name char(64) DEFAULT '' NOT NULL, type enum('FUNCTION','PROCEDURE') NOT NULL, specific_name char(64) DEFAULT '' NOT NULL, language enum('SQL') DEFAULT 'SQL' NOT NULL, sql_data_access enum('CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA' ) DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL, security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list blob NOT NULL, returns char(64) DEFAULT '' NOT NULL, body longblob NOT NULL, definer char(77) collate utf8_bin DEFAULT '' NOT NULL, created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'NOT_USED', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE' ) DEFAULT '' NOT NULL, comment char(64) collate utf8_bin DEFAULT '' NOT NULL, PRIMARY KEY (db,name,type) ) engine=MyISAM character set utf8 comment='Stored Procedures'; CREATE TABLE IF NOT EXISTS procs_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Routine_name char(64) binary DEFAULT '' NOT NULL, Routine_type enum('FUNCTION','PROCEDURE') NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Proc_priv set('Execute','Alter Routine','Grant') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (Host,Db,User,Routine_name,Routine_type), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Procedure privileges'; -CREATE TABLE IF NOT EXISTS event ( db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', name char(64) CHARACTER SET utf8 NOT NULL default '', body longblob NOT NULL, definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', execute_at DATETIME default NULL, interval_value int(11) default NULL, interval_field ENUM('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') default NULL, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', last_executed DATETIME default NULL, starts DATETIME default NULL, ends DATETIME default NULL, status ENUM('ENABLED','DISABLED') NOT NULL default 'ENABLED', on_completion ENUM('DROP','PRESERVE') NOT NULL default 'DROP', sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') DEFAULT '' NOT NULL, comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', time_zone char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', PRIMARY KEY (db, name) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'Events'; +CREATE TABLE IF NOT EXISTS event ( db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', name char(64) CHARACTER SET utf8 NOT NULL default '', body longblob NOT NULL, definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', execute_at DATETIME default NULL, interval_value int(11) default NULL, interval_field ENUM('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') default NULL, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', last_executed DATETIME default NULL, starts DATETIME default NULL, ends DATETIME default NULL, status ENUM('ENABLED','DISABLED') NOT NULL default 'ENABLED', on_completion ENUM('DROP','PRESERVE') NOT NULL default 'DROP', sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') DEFAULT '' NOT NULL, comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', time_zone char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', PRIMARY KEY (db, name) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'Events'; -- disable_result_log # Run the mysql_fix_privilege_tables.sql using "mysql --force" diff --git a/mysql-test/main/type_int.result b/mysql-test/main/type_int.result index 1a76f438d48..dfdff29f083 100644 --- a/mysql-test/main/type_int.result +++ b/mysql-test/main/type_int.result @@ -236,6 +236,19 @@ DROP FUNCTION sint32; DROP FUNCTION uint64; DROP FUNCTION sint64; # +# MDEV-20285 Wrong result on INSERT..SELECT when converting from SIGNED to UNSIGNED +# +CREATE TABLE t1 (a TINYINT UNSIGNED); +CREATE TABLE t2 (a TINYINT); +INSERT INTO t1 VALUES (255); +INSERT IGNORE INTO t2 SELECT a FROM t1; +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +SELECT * FROM t2; +a +127 +DROP TABLE t1, t2; +# # End of 10.3 tests # # diff --git a/mysql-test/main/type_int.test b/mysql-test/main/type_int.test index 96b07bb8759..ea662254efb 100644 --- a/mysql-test/main/type_int.test +++ b/mysql-test/main/type_int.test @@ -177,6 +177,17 @@ DROP FUNCTION uint64; DROP FUNCTION sint64; --echo # +--echo # MDEV-20285 Wrong result on INSERT..SELECT when converting from SIGNED to UNSIGNED +--echo # + +CREATE TABLE t1 (a TINYINT UNSIGNED); +CREATE TABLE t2 (a TINYINT); +INSERT INTO t1 VALUES (255); +INSERT IGNORE INTO t2 SELECT a FROM t1; +SELECT * FROM t2; +DROP TABLE t1, t2; + +--echo # --echo # End of 10.3 tests --echo # diff --git a/mysql-test/main/warnings.result b/mysql-test/main/warnings.result index db3ef5f17d2..aa4cab37741 100644 --- a/mysql-test/main/warnings.result +++ b/mysql-test/main/warnings.result @@ -115,6 +115,7 @@ insert ignore into t2 select b,c from t1; Warnings: Warning 1265 Data truncated for column 'b' at row 1 Warning 1265 Data truncated for column 'b' at row 2 +Warning 1264 Out of range value for column 'a' at row 3 Warning 1265 Data truncated for column 'b' at row 3 Warning 1048 Column 'a' cannot be null Warning 1265 Data truncated for column 'b' at row 4 diff --git a/mysql-test/main/win_percentile.result b/mysql-test/main/win_percentile.result index 4a918bad17f..a2ca775fcfb 100644 --- a/mysql-test/main/win_percentile.result +++ b/mysql-test/main/win_percentile.result @@ -36,23 +36,23 @@ Tata 4.0000000000 Tatiana 4.0000000000 select name, percentile_disc(0.5) within group(order by score) over (partition by name) as c from t1; name c -Chun 3.0000000000 -Chun 3.0000000000 -Kaolin 4.0000000000 -Kaolin 4.0000000000 -Kaolin 4.0000000000 -Tata 4.0000000000 -Tatiana 4.0000000000 +Chun 3.0000 +Chun 3.0000 +Kaolin 4.0000 +Kaolin 4.0000 +Kaolin 4.0000 +Tata 4.0000 +Tatiana 4.0000 # no partition clause select name, percentile_disc(0.5) within group(order by score) over () from t1; name percentile_disc(0.5) within group(order by score) over () -Chun 4.0000000000 -Chun 4.0000000000 -Kaolin 4.0000000000 -Kaolin 4.0000000000 -Kaolin 4.0000000000 -Tata 4.0000000000 -Tatiana 4.0000000000 +Chun 4.0000 +Chun 4.0000 +Kaolin 4.0000 +Kaolin 4.0000 +Kaolin 4.0000 +Tata 4.0000 +Tatiana 4.0000 select name, percentile_cont(0.5) within group(order by score) over () from t1; name percentile_cont(0.5) within group(order by score) over () Chun 4.0000000000 @@ -79,13 +79,13 @@ Tata 4.0000000000 Tatiana 4.0000000000 select * from ( select name , percentile_disc(0.5) within group ( order by score) over (partition by name ) from t1 ) as t; name percentile_disc(0.5) within group ( order by score) over (partition by name ) -Chun 3.0000000000 -Chun 3.0000000000 -Kaolin 4.0000000000 -Kaolin 4.0000000000 -Kaolin 4.0000000000 -Tata 4.0000000000 -Tatiana 4.0000000000 +Chun 3.0000 +Chun 3.0000 +Kaolin 4.0000 +Kaolin 4.0000 +Kaolin 4.0000 +Tata 4.0000 +Tatiana 4.0000 select name from t1 a where (select percentile_disc(0.5) within group (order by score) over (partition by name) from t1 b limit 1) >= 0.5; name Chun @@ -118,13 +118,13 @@ ERROR HY000: percentile_disc function only accepts arguments that can be convert #complete query with partition column select name,cume_dist() over (partition by name order by score), percentile_disc(0.5) within group(order by score) over (partition by name) as c from t1; name cume_dist() over (partition by name order by score) c -Chun 0.5000000000 3.0000000000 -Chun 1.0000000000 3.0000000000 -Kaolin 0.3333333333 4.0000000000 -Kaolin 0.6666666667 4.0000000000 -Kaolin 1.0000000000 4.0000000000 -Tata 1.0000000000 4.0000000000 -Tatiana 1.0000000000 4.0000000000 +Chun 0.5000000000 3.0000 +Chun 1.0000000000 3.0000 +Kaolin 0.3333333333 4.0000 +Kaolin 0.6666666667 4.0000 +Kaolin 1.0000000000 4.0000 +Tata 1.0000000000 4.0000 +Tatiana 1.0000000000 4.0000 select name, percentile_cont(0.5) within group(order by score) over (partition by name) as c from t1; name c Chun 5.0000000000 @@ -136,94 +136,94 @@ Tata 4.0000000000 Tatiana 4.0000000000 select name,cume_dist() over (partition by name order by score) as b, percentile_disc(0.1) within group(order by score) over (partition by name) as c from t1; name b c -Chun 0.5000000000 3.0000000000 -Chun 1.0000000000 3.0000000000 -Kaolin 0.3333333333 3.0000000000 -Kaolin 0.6666666667 3.0000000000 -Kaolin 1.0000000000 3.0000000000 -Tata 1.0000000000 4.0000000000 -Tatiana 1.0000000000 4.0000000000 +Chun 0.5000000000 3.0000 +Chun 1.0000000000 3.0000 +Kaolin 0.3333333333 3.0000 +Kaolin 0.6666666667 3.0000 +Kaolin 1.0000000000 3.0000 +Tata 1.0000000000 4.0000 +Tatiana 1.0000000000 4.0000 select name,cume_dist() over (partition by name order by score) as b, percentile_disc(0.2) within group(order by score) over (partition by name) as c from t1; name b c -Chun 0.5000000000 3.0000000000 -Chun 1.0000000000 3.0000000000 -Kaolin 0.3333333333 3.0000000000 -Kaolin 0.6666666667 3.0000000000 -Kaolin 1.0000000000 3.0000000000 -Tata 1.0000000000 4.0000000000 -Tatiana 1.0000000000 4.0000000000 +Chun 0.5000000000 3.0000 +Chun 1.0000000000 3.0000 +Kaolin 0.3333333333 3.0000 +Kaolin 0.6666666667 3.0000 +Kaolin 1.0000000000 3.0000 +Tata 1.0000000000 4.0000 +Tatiana 1.0000000000 4.0000 select name,cume_dist() over (partition by name order by score) as b, percentile_disc(0.3) within group(order by score) over (partition by name) as c from t1; name b c -Chun 0.5000000000 3.0000000000 -Chun 1.0000000000 3.0000000000 -Kaolin 0.3333333333 3.0000000000 -Kaolin 0.6666666667 3.0000000000 -Kaolin 1.0000000000 3.0000000000 -Tata 1.0000000000 4.0000000000 -Tatiana 1.0000000000 4.0000000000 +Chun 0.5000000000 3.0000 +Chun 1.0000000000 3.0000 +Kaolin 0.3333333333 3.0000 +Kaolin 0.6666666667 3.0000 +Kaolin 1.0000000000 3.0000 +Tata 1.0000000000 4.0000 +Tatiana 1.0000000000 4.0000 select name,cume_dist() over (partition by name order by score) as b, percentile_disc(0.4) within group(order by score) over (partition by name) as c from t1; name b c -Chun 0.5000000000 3.0000000000 -Chun 1.0000000000 3.0000000000 -Kaolin 0.3333333333 4.0000000000 -Kaolin 0.6666666667 4.0000000000 -Kaolin 1.0000000000 4.0000000000 -Tata 1.0000000000 4.0000000000 -Tatiana 1.0000000000 4.0000000000 +Chun 0.5000000000 3.0000 +Chun 1.0000000000 3.0000 +Kaolin 0.3333333333 4.0000 +Kaolin 0.6666666667 4.0000 +Kaolin 1.0000000000 4.0000 +Tata 1.0000000000 4.0000 +Tatiana 1.0000000000 4.0000 select name,cume_dist() over (partition by name order by score) as b, percentile_disc(0.5) within group(order by score) over (partition by name) as c from t1; name b c -Chun 0.5000000000 3.0000000000 -Chun 1.0000000000 3.0000000000 -Kaolin 0.3333333333 4.0000000000 -Kaolin 0.6666666667 4.0000000000 -Kaolin 1.0000000000 4.0000000000 -Tata 1.0000000000 4.0000000000 -Tatiana 1.0000000000 4.0000000000 +Chun 0.5000000000 3.0000 +Chun 1.0000000000 3.0000 +Kaolin 0.3333333333 4.0000 +Kaolin 0.6666666667 4.0000 +Kaolin 1.0000000000 4.0000 +Tata 1.0000000000 4.0000 +Tatiana 1.0000000000 4.0000 select name,cume_dist() over (partition by name order by score) as b, percentile_disc(0.6) within group(order by score) over (partition by name) as c from t1; name b c -Chun 0.5000000000 7.0000000000 -Chun 1.0000000000 7.0000000000 -Kaolin 0.3333333333 4.0000000000 -Kaolin 0.6666666667 4.0000000000 -Kaolin 1.0000000000 4.0000000000 -Tata 1.0000000000 4.0000000000 -Tatiana 1.0000000000 4.0000000000 +Chun 0.5000000000 7.0000 +Chun 1.0000000000 7.0000 +Kaolin 0.3333333333 4.0000 +Kaolin 0.6666666667 4.0000 +Kaolin 1.0000000000 4.0000 +Tata 1.0000000000 4.0000 +Tatiana 1.0000000000 4.0000 select name,cume_dist() over (partition by name order by score) as b, percentile_disc(0.7) within group(order by score) over (partition by name) as c from t1; name b c -Chun 0.5000000000 7.0000000000 -Chun 1.0000000000 7.0000000000 -Kaolin 0.3333333333 7.0000000000 -Kaolin 0.6666666667 7.0000000000 -Kaolin 1.0000000000 7.0000000000 -Tata 1.0000000000 4.0000000000 -Tatiana 1.0000000000 4.0000000000 +Chun 0.5000000000 7.0000 +Chun 1.0000000000 7.0000 +Kaolin 0.3333333333 7.0000 +Kaolin 0.6666666667 7.0000 +Kaolin 1.0000000000 7.0000 +Tata 1.0000000000 4.0000 +Tatiana 1.0000000000 4.0000 select name,cume_dist() over (partition by name order by score) as b, percentile_disc(0.8) within group(order by score) over (partition by name) as c from t1; name b c -Chun 0.5000000000 7.0000000000 -Chun 1.0000000000 7.0000000000 -Kaolin 0.3333333333 7.0000000000 -Kaolin 0.6666666667 7.0000000000 -Kaolin 1.0000000000 7.0000000000 -Tata 1.0000000000 4.0000000000 -Tatiana 1.0000000000 4.0000000000 +Chun 0.5000000000 7.0000 +Chun 1.0000000000 7.0000 +Kaolin 0.3333333333 7.0000 +Kaolin 0.6666666667 7.0000 +Kaolin 1.0000000000 7.0000 +Tata 1.0000000000 4.0000 +Tatiana 1.0000000000 4.0000 select name,cume_dist() over (partition by name order by score) as b, percentile_disc(0.9) within group(order by score) over (partition by name) as c from t1; name b c -Chun 0.5000000000 7.0000000000 -Chun 1.0000000000 7.0000000000 -Kaolin 0.3333333333 7.0000000000 -Kaolin 0.6666666667 7.0000000000 -Kaolin 1.0000000000 7.0000000000 -Tata 1.0000000000 4.0000000000 -Tatiana 1.0000000000 4.0000000000 +Chun 0.5000000000 7.0000 +Chun 1.0000000000 7.0000 +Kaolin 0.3333333333 7.0000 +Kaolin 0.6666666667 7.0000 +Kaolin 1.0000000000 7.0000 +Tata 1.0000000000 4.0000 +Tatiana 1.0000000000 4.0000 select name,cume_dist() over (partition by name order by score) as b, percentile_disc(1) within group(order by score) over (partition by name) as c from t1; name b c -Chun 0.5000000000 7.0000000000 -Chun 1.0000000000 7.0000000000 -Kaolin 0.3333333333 7.0000000000 -Kaolin 0.6666666667 7.0000000000 -Kaolin 1.0000000000 7.0000000000 -Tata 1.0000000000 4.0000000000 -Tatiana 1.0000000000 4.0000000000 +Chun 0.5000000000 7.0000 +Chun 1.0000000000 7.0000 +Kaolin 0.3333333333 7.0000 +Kaolin 0.6666666667 7.0000 +Kaolin 1.0000000000 7.0000 +Tata 1.0000000000 4.0000 +Tatiana 1.0000000000 4.0000 select median(score) over (partition by name), percentile_cont(0) within group(order by score) over (partition by name) as c from t1; median(score) over (partition by name) c 5.0000000000 3.0000000000 @@ -366,3 +366,102 @@ median(val) OVER () 2.0000000000 drop table t1; drop view v1; +# +# MDEV-20278 PERCENTILE_DISC() returns a wrong data type +# +# INT variants +CREATE TABLE t1 (name CHAR(30), star_rating INT); +INSERT INTO t1 VALUES ('Lord of the Ladybirds', 5); +INSERT INTO t1 VALUES ('Lord of the Ladybirds', 3); +INSERT INTO t1 VALUES ('Lady of the Flies', 1); +INSERT INTO t1 VALUES ('Lady of the Flies', 2); +INSERT INTO t1 VALUES ('Lady of the Flies', 5); +CREATE OR REPLACE TABLE t2 AS SELECT name, PERCENTILE_DISC(0.5) +WITHIN GROUP (ORDER BY star_rating) +OVER (PARTITION BY name) AS pc FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `name` char(30) DEFAULT NULL, + `pc` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2, t1; +# UNSIGNED INT variants +CREATE OR REPLACE TABLE t1 (name CHAR(30), star_rating BIGINT UNSIGNED); +INSERT INTO t1 VALUES ('Lord of the Ladybirds', 0x8000000000000005); +INSERT INTO t1 VALUES ('Lord of the Ladybirds', 0x8000000000000003); +INSERT INTO t1 VALUES ('Lady of the Flies', 0x8000000000000001); +INSERT INTO t1 VALUES ('Lady of the Flies', 0x8000000000000002); +INSERT INTO t1 VALUES ('Lady of the Flies', 0x8000000000000003); +CREATE OR REPLACE TABLE t2 AS SELECT name, PERCENTILE_DISC(0.5) +WITHIN GROUP (ORDER BY star_rating) +OVER (PARTITION BY name) AS pc FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `name` char(30) DEFAULT NULL, + `pc` bigint(20) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT name, pc, HEX(pc) FROM t2 ORDER BY name, pc; +name pc HEX(pc) +Lady of the Flies 9223372036854775810 8000000000000002 +Lady of the Flies 9223372036854775810 8000000000000002 +Lady of the Flies 9223372036854775810 8000000000000002 +Lord of the Ladybirds 9223372036854775811 8000000000000003 +Lord of the Ladybirds 9223372036854775811 8000000000000003 +DROP TABLE t2, t1; +# FLOAT variants +CREATE TABLE t1 (name CHAR(30), star_rating FLOAT); +INSERT INTO t1 VALUES ('Lord of the Ladybirds', 5); +INSERT INTO t1 VALUES ('Lord of the Ladybirds', 3); +INSERT INTO t1 VALUES ('Lady of the Flies', 1); +INSERT INTO t1 VALUES ('Lady of the Flies', 2); +INSERT INTO t1 VALUES ('Lady of the Flies', 5); +CREATE TABLE t2 AS SELECT name, PERCENTILE_DISC(0.5) +WITHIN GROUP (ORDER BY star_rating) +OVER (PARTITION BY name) AS pc FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `name` char(30) DEFAULT NULL, + `pc` float DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2, t1; +# DECIMAL variants +CREATE OR REPLACE TABLE t1 (name CHAR(30), star_rating DECIMAL(30,2)); +INSERT INTO t1 VALUES ('Lord of the Ladybirds', 50000000000); +INSERT INTO t1 VALUES ('Lord of the Ladybirds', 30000000000); +INSERT INTO t1 VALUES ('Lady of the Flies', 10000000000); +INSERT INTO t1 VALUES ('Lady of the Flies', 20000000000); +INSERT INTO t1 VALUES ('Lady of the Flies', 50000000000); +CREATE OR REPLACE TABLE t2 AS SELECT name, PERCENTILE_DISC(0.5) +WITHIN GROUP (ORDER BY star_rating) +OVER (PARTITION BY name) AS pc FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `name` char(30) DEFAULT NULL, + `pc` decimal(30,2) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t2 ORDER BY name, pc; +name pc +Lady of the Flies 20000000000.00 +Lady of the Flies 20000000000.00 +Lady of the Flies 20000000000.00 +Lord of the Ladybirds 30000000000.00 +Lord of the Ladybirds 30000000000.00 +DROP TABLE t2, t1; +# +# MDEV-20272 PERCENTILE_DISC() crashes on a temporal type input +# +CREATE OR REPLACE TABLE t1 (name CHAR(30), star_rating TIME); +INSERT INTO t1 VALUES ('Lord of the Ladybirds', 5); +INSERT INTO t1 VALUES ('Lord of the Ladybirds', 3); +INSERT INTO t1 VALUES ('Lady of the Flies', 1); +INSERT INTO t1 VALUES ('Lady of the Flies', 2); +INSERT INTO t1 VALUES ('Lady of the Flies', 5); +SELECT name, PERCENTILE_DISC(0.5) +WITHIN GROUP (ORDER BY star_rating) +OVER (PARTITION BY name) AS pc FROM t1; +ERROR HY000: Numeric datatype is required for percentile_disc function +DROP TABLE t1; diff --git a/mysql-test/main/win_percentile.test b/mysql-test/main/win_percentile.test index d36b365dd9b..8705be123ff 100644 --- a/mysql-test/main/win_percentile.test +++ b/mysql-test/main/win_percentile.test @@ -146,3 +146,83 @@ select * from v1; select median(val) OVER () FROM t1; drop table t1; drop view v1; + + +--echo # +--echo # MDEV-20278 PERCENTILE_DISC() returns a wrong data type +--echo # + +--echo # INT variants + +CREATE TABLE t1 (name CHAR(30), star_rating INT); +INSERT INTO t1 VALUES ('Lord of the Ladybirds', 5); +INSERT INTO t1 VALUES ('Lord of the Ladybirds', 3); +INSERT INTO t1 VALUES ('Lady of the Flies', 1); +INSERT INTO t1 VALUES ('Lady of the Flies', 2); +INSERT INTO t1 VALUES ('Lady of the Flies', 5); +CREATE OR REPLACE TABLE t2 AS SELECT name, PERCENTILE_DISC(0.5) + WITHIN GROUP (ORDER BY star_rating) + OVER (PARTITION BY name) AS pc FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2, t1; + +--echo # UNSIGNED INT variants + +CREATE OR REPLACE TABLE t1 (name CHAR(30), star_rating BIGINT UNSIGNED); +INSERT INTO t1 VALUES ('Lord of the Ladybirds', 0x8000000000000005); +INSERT INTO t1 VALUES ('Lord of the Ladybirds', 0x8000000000000003); +INSERT INTO t1 VALUES ('Lady of the Flies', 0x8000000000000001); +INSERT INTO t1 VALUES ('Lady of the Flies', 0x8000000000000002); +INSERT INTO t1 VALUES ('Lady of the Flies', 0x8000000000000003); +CREATE OR REPLACE TABLE t2 AS SELECT name, PERCENTILE_DISC(0.5) + WITHIN GROUP (ORDER BY star_rating) + OVER (PARTITION BY name) AS pc FROM t1; +SHOW CREATE TABLE t2; +SELECT name, pc, HEX(pc) FROM t2 ORDER BY name, pc; +DROP TABLE t2, t1; + +--echo # FLOAT variants + +CREATE TABLE t1 (name CHAR(30), star_rating FLOAT); +INSERT INTO t1 VALUES ('Lord of the Ladybirds', 5); +INSERT INTO t1 VALUES ('Lord of the Ladybirds', 3); +INSERT INTO t1 VALUES ('Lady of the Flies', 1); +INSERT INTO t1 VALUES ('Lady of the Flies', 2); +INSERT INTO t1 VALUES ('Lady of the Flies', 5); +CREATE TABLE t2 AS SELECT name, PERCENTILE_DISC(0.5) + WITHIN GROUP (ORDER BY star_rating) + OVER (PARTITION BY name) AS pc FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2, t1; + +--echo # DECIMAL variants + +CREATE OR REPLACE TABLE t1 (name CHAR(30), star_rating DECIMAL(30,2)); +INSERT INTO t1 VALUES ('Lord of the Ladybirds', 50000000000); +INSERT INTO t1 VALUES ('Lord of the Ladybirds', 30000000000); +INSERT INTO t1 VALUES ('Lady of the Flies', 10000000000); +INSERT INTO t1 VALUES ('Lady of the Flies', 20000000000); +INSERT INTO t1 VALUES ('Lady of the Flies', 50000000000); +CREATE OR REPLACE TABLE t2 AS SELECT name, PERCENTILE_DISC(0.5) + WITHIN GROUP (ORDER BY star_rating) + OVER (PARTITION BY name) AS pc FROM t1; +SHOW CREATE TABLE t2; +SELECT * FROM t2 ORDER BY name, pc; +DROP TABLE t2, t1; + + +--echo # +--echo # MDEV-20272 PERCENTILE_DISC() crashes on a temporal type input +--echo # + +CREATE OR REPLACE TABLE t1 (name CHAR(30), star_rating TIME); +INSERT INTO t1 VALUES ('Lord of the Ladybirds', 5); +INSERT INTO t1 VALUES ('Lord of the Ladybirds', 3); +INSERT INTO t1 VALUES ('Lady of the Flies', 1); +INSERT INTO t1 VALUES ('Lady of the Flies', 2); +INSERT INTO t1 VALUES ('Lady of the Flies', 5); +--error ER_WRONG_TYPE_FOR_PERCENTILE_FUNC +SELECT name, PERCENTILE_DISC(0.5) + WITHIN GROUP (ORDER BY star_rating) + OVER (PARTITION BY name) AS pc FROM t1; +DROP TABLE t1; diff --git a/mysql-test/suite/binlog_encryption/multisource.result b/mysql-test/suite/binlog_encryption/multisource.result index d99a377f0c5..928c9799854 100644 --- a/mysql-test/suite/binlog_encryption/multisource.result +++ b/mysql-test/suite/binlog_encryption/multisource.result @@ -47,6 +47,8 @@ drop database if exists db1; create database db1; use db1; create table t1 (i int auto_increment, f1 varchar(16), primary key pk (i,f1)) engine=MyISAM; +Warnings: +Warning 1280 Name 'pk' ignored for PRIMARY key. insert into t1 (f1) values ('one'),('two'); connection slave; select * from db1.t1; diff --git a/mysql-test/suite/compat/oracle/r/column_compression.result b/mysql-test/suite/compat/oracle/r/column_compression.result index 4eb55f47e51..8f643ad2a06 100644 --- a/mysql-test/suite/compat/oracle/r/column_compression.result +++ b/mysql-test/suite/compat/oracle/r/column_compression.result @@ -515,7 +515,7 @@ CREATE TABLE t1 (a TEXT COMPRESSED BYTE); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE "t1" ( - "a" blob /*!100301 COMPRESSED*/ DEFAULT NULL + "a" blob(65535) /*!100301 COMPRESSED*/ DEFAULT NULL ) DROP TABLE t1; CREATE TABLE t1 (a TEXT COMPRESSED ASCII); @@ -547,7 +547,7 @@ CREATE TABLE t1 (a TEXT COMPRESSED BYTE DEFAULT ''); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE "t1" ( - "a" blob /*!100301 COMPRESSED*/ DEFAULT '' + "a" blob(65535) /*!100301 COMPRESSED*/ DEFAULT '' ) DROP TABLE t1; CREATE TABLE t1 (a TEXT COMPRESSED BINARY DEFAULT ''); @@ -605,7 +605,7 @@ Warning 1287 '<data type> <character set clause> ... COMPRESSED...' is deprecate SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE "t1" ( - "a" blob /*!100301 COMPRESSED*/ DEFAULT NULL + "a" blob(65535) /*!100301 COMPRESSED*/ DEFAULT NULL ) DROP TABLE t1; # diff --git a/mysql-test/suite/compat/oracle/r/sp.result b/mysql-test/suite/compat/oracle/r/sp.result index 1d088a98ab7..6bcdf520edb 100644 --- a/mysql-test/suite/compat/oracle/r/sp.result +++ b/mysql-test/suite/compat/oracle/r/sp.result @@ -2064,7 +2064,7 @@ t1 CREATE TABLE "t1" ( "tables_table_name" varchar(64) CHARACTER SET utf8 DEFAULT NULL, "tables_table_rows" bigint(21) unsigned DEFAULT NULL, "processlist_info" longtext CHARACTER SET utf8 DEFAULT NULL, - "processlist_info_binary" blob DEFAULT NULL + "processlist_info_binary" blob(65535) DEFAULT NULL ) DROP TABLE t1; DROP PROCEDURE p1; diff --git a/mysql-test/suite/compat/oracle/r/type_blob.result b/mysql-test/suite/compat/oracle/r/type_blob.result index fe0a78e6ceb..27740947a10 100644 --- a/mysql-test/suite/compat/oracle/r/type_blob.result +++ b/mysql-test/suite/compat/oracle/r/type_blob.result @@ -6,3 +6,21 @@ t1 CREATE TABLE "t1" ( "a" longblob DEFAULT NULL ) DROP TABLE t1; +# +# MDEV-20263 sql_mode=ORACLE: BLOB(65535) should not translate to LONGBLOB +# +CREATE TABLE t1 ( +c1 BLOB(100), +c2 BLOB(65535), +c3 BLOB(16777215), +c4 BLOB(16777216) +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "c1" tinyblob DEFAULT NULL, + "c2" blob(65535) DEFAULT NULL, + "c3" mediumblob DEFAULT NULL, + "c4" longblob DEFAULT NULL +) +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/t/type_blob.test b/mysql-test/suite/compat/oracle/t/type_blob.test index 2bfaa77560d..989581ee4f6 100644 --- a/mysql-test/suite/compat/oracle/t/type_blob.test +++ b/mysql-test/suite/compat/oracle/t/type_blob.test @@ -2,3 +2,16 @@ SET sql_mode=ORACLE; CREATE TABLE t1 (a BLOB); SHOW CREATE TABLE t1; DROP TABLE t1; + +--echo # +--echo # MDEV-20263 sql_mode=ORACLE: BLOB(65535) should not translate to LONGBLOB +--echo # + +CREATE TABLE t1 ( + c1 BLOB(100), + c2 BLOB(65535), + c3 BLOB(16777215), + c4 BLOB(16777216) +); +SHOW CREATE TABLE t1; +DROP TABLE t1; diff --git a/mysql-test/suite/galera/r/GAL-382.result b/mysql-test/suite/galera/r/GAL-382.result index 137efe4efba..2150326757c 100644 --- a/mysql-test/suite/galera/r/GAL-382.result +++ b/mysql-test/suite/galera/r/GAL-382.result @@ -2,8 +2,12 @@ connection node_2; connection node_1; connection node_1; create table t1 (i int, j int, k int, primary key pk(i)) engine=innodb; +Warnings: +Warning 1280 Name 'pk' ignored for PRIMARY key. insert into t1 values (1, 1, 1), (2, 2, 2), (3, 3, 3); create table t2 (i int, j int, k int, primary key pk(i, j, k), index idx(i, k, j)) engine=innodb; +Warnings: +Warning 1280 Name 'pk' ignored for PRIMARY key. replace into t2 (i, j, k) select /*!99997 */ i, k, j from t1; DROP TABLE t1; DROP TABLE t2; diff --git a/mysql-test/suite/innodb/r/innodb-index-online-fk.result b/mysql-test/suite/innodb/r/innodb-index-online-fk.result index 47fc8e8b840..854afd95aaf 100644 --- a/mysql-test/suite/innodb/r/innodb-index-online-fk.result +++ b/mysql-test/suite/innodb/r/innodb-index-online-fk.result @@ -326,6 +326,8 @@ ALTER TABLE child ADD PRIMARY KEY idx (a3), CHANGE a1 a3 INT, ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b) ON DELETE SET NULL ON UPDATE CASCADE, ALGORITHM = INPLACE; +Warnings: +Warning 1280 Name 'idx' ignored for PRIMARY key. SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; ID FOR_NAME REF_NAME N_COLS TYPE test/fk_1 test/child test/parent 1 6 @@ -363,6 +365,8 @@ ALTER TABLE child ADD PRIMARY KEY idx (a1), ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b) ON DELETE SET NULL ON UPDATE CASCADE, ALGORITHM = INPLACE; +Warnings: +Warning 1280 Name 'idx' ignored for PRIMARY key. SELECT * from information_schema.INNODB_SYS_FOREIGN; ID FOR_NAME REF_NAME N_COLS TYPE test/fk_1 test/child test/parent 1 6 @@ -556,6 +560,8 @@ ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1) REFERENCES parent(b), ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2) REFERENCES parent(a), ADD CONSTRAINT fk_new_3 FOREIGN KEY (a3) REFERENCES parent(a), ALGORITHM = INPLACE; +Warnings: +Warning 1280 Name 'idx' ignored for PRIMARY key. SHOW CREATE TABLE child; Table Create Table child CREATE TABLE `child` ( diff --git a/mysql-test/suite/innodb/r/innodb-virtual-columns.result b/mysql-test/suite/innodb/r/innodb-virtual-columns.result index bf21e352681..4b2df7eb287 100644 --- a/mysql-test/suite/innodb/r/innodb-virtual-columns.result +++ b/mysql-test/suite/innodb/r/innodb-virtual-columns.result @@ -6,6 +6,8 @@ wdraw_rsn varchar(4) NOT NULL DEFAULT '', admit_term char(4) NOT NULL DEFAULT '', CONSTRAINT gso_grad_supr_pky PRIMARY KEY (uw_id, term) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +Warnings: +Warning 1280 Name 'gso_grad_supr_pky' ignored for PRIMARY key. INSERT INTO `gso_grad_supr` VALUES ('1031',2,'CSM','','1009'); INSERT INTO `gso_grad_supr` VALUES ('1035',2,'CSM','ACAD','1009'); CREATE TABLE IF NOT EXISTS grad_degree ( @@ -23,6 +25,8 @@ deg_start_term char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginn deg_as_of_term char(4) NOT NULL COMMENT 'In most cases also end term', CONSTRAINT grad_degree_stu_plan_admit_pky PRIMARY KEY (student_id, plan, admit_term) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +Warnings: +Warning 1280 Name 'grad_degree_stu_plan_admit_pky' ignored for PRIMARY key. SHOW CREATE TABLE grad_degree; Table Create Table grad_degree CREATE TABLE `grad_degree` ( @@ -129,6 +133,8 @@ deg_start_term char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginn deg_as_of_term char(4) NOT NULL COMMENT 'In most cases also end term', CONSTRAINT grad_degree_stu_plan_admit_pky PRIMARY KEY (student_id, plan, admit_term) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +Warnings: +Warning 1280 Name 'grad_degree_stu_plan_admit_pky' ignored for PRIMARY key. SHOW CREATE TABLE grad_degree; Table Create Table grad_degree CREATE TABLE `grad_degree` ( @@ -263,6 +269,8 @@ deg_start_term char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginn deg_as_of_term char(4) NOT NULL COMMENT 'In most cases also end term', CONSTRAINT grad_degree_stu_plan_admit_pky PRIMARY KEY (student_id, plan, admit_term) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +Warnings: +Warning 1280 Name 'grad_degree_stu_plan_admit_pky' ignored for PRIMARY key. SHOW CREATE TABLE grad_degree; Table Create Table grad_degree CREATE TABLE `grad_degree` ( diff --git a/mysql-test/suite/innodb/r/innodb_bug51378.result b/mysql-test/suite/innodb/r/innodb_bug51378.result index 532fd703af6..2ffd533279b 100644 --- a/mysql-test/suite/innodb/r/innodb_bug51378.result +++ b/mysql-test/suite/innodb/r/innodb_bug51378.result @@ -28,6 +28,8 @@ bug51378 CREATE TABLE `bug51378` ( UNIQUE KEY `idx2` (`col1`,`col2`(31)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 alter table bug51378 add primary key idx3(col1, col2(31)); +Warnings: +Warning 1280 Name 'idx3' ignored for PRIMARY key. SHOW CREATE TABLE bug51378; Table Create Table bug51378 CREATE TABLE `bug51378` ( diff --git a/mysql-test/suite/innodb/r/innodb_bug60229.result b/mysql-test/suite/innodb/r/innodb_bug60229.result index a3971876193..588f3855764 100644 --- a/mysql-test/suite/innodb/r/innodb_bug60229.result +++ b/mysql-test/suite/innodb/r/innodb_bug60229.result @@ -4,6 +4,8 @@ DOB VARCHAR(50) NOT NULL, NAME NVARCHAR(255) NOT NULL, CONSTRAINT PK_PERSON PRIMARY KEY (PERSON_ID, DOB) )Engine=InnoDB; +Warnings: +Warning 1280 Name 'PK_PERSON' ignored for PRIMARY key. CREATE TABLE PHOTO ( PERSON_ID VARCHAR(50) NOT NULL, DOB VARCHAR(50) NOT NULL, @@ -11,6 +13,8 @@ PHOTO_DETAILS VARCHAR(50) NULL, CONSTRAINT PK_PHOTO PRIMARY KEY (PERSON_ID, DOB), CONSTRAINT FK_PHOTO_2_PERSON FOREIGN KEY (PERSON_ID, DOB) REFERENCES PERSON (PERSON_ID, DOB) )Engine=InnoDB; +Warnings: +Warning 1280 Name 'PK_PHOTO' ignored for PRIMARY key. CREATE TABLE ADDRESS ( PERSON_ID VARCHAR(50) NOT NULL, DOB VARCHAR(50) NOT NULL, @@ -19,6 +23,8 @@ ADDRESS_DETAILS NVARCHAR(250) NULL, CONSTRAINT PK_ADDRESS PRIMARY KEY (PERSON_ID, DOB, ADDRESS_ID), CONSTRAINT FK_ADDRESS_2_PERSON FOREIGN KEY (PERSON_ID, DOB) REFERENCES PERSON (PERSON_ID, DOB) ON DELETE CASCADE )Engine=InnoDB; +Warnings: +Warning 1280 Name 'PK_ADDRESS' ignored for PRIMARY key. INSERT INTO PERSON VALUES("10", "11011999", "John"); INSERT INTO PHOTO VALUES("10", "11011999", "new photo"); DROP TABLE PHOTO; diff --git a/mysql-test/suite/innodb_gis/r/1.result b/mysql-test/suite/innodb_gis/r/1.result index 8de9fd30894..f4ac87ec56e 100644 --- a/mysql-test/suite/innodb_gis/r/1.result +++ b/mysql-test/suite/innodb_gis/r/1.result @@ -796,11 +796,15 @@ POINT(1 1) drop function fn3; create table t1(pt POINT); alter table t1 add primary key pti(pt); +Warnings: +Warning 1280 Name 'pti' ignored for PRIMARY key. drop table t1; create table t1(pt GEOMETRY); alter table t1 add primary key pti(pt); ERROR 42000: BLOB/TEXT column 'pt' used in key specification without a key length alter table t1 add primary key pti(pt(20)); +Warnings: +Warning 1280 Name 'pti' ignored for PRIMARY key. drop table t1; create table t1 select ST_GeomFromText('point(1 1)'); desc t1; diff --git a/mysql-test/suite/innodb_gis/r/gis.result b/mysql-test/suite/innodb_gis/r/gis.result index 6929afd4228..f4a973ecdc5 100644 --- a/mysql-test/suite/innodb_gis/r/gis.result +++ b/mysql-test/suite/innodb_gis/r/gis.result @@ -792,11 +792,15 @@ POINT(1 1) drop function fn3; create table t1(pt POINT); alter table t1 add primary key pti(pt); +Warnings: +Warning 1280 Name 'pti' ignored for PRIMARY key. drop table t1; create table t1(pt GEOMETRY); alter table t1 add primary key pti(pt); ERROR 42000: BLOB/TEXT column 'pt' used in key specification without a key length alter table t1 add primary key pti(pt(20)); +Warnings: +Warning 1280 Name 'pti' ignored for PRIMARY key. drop table t1; create table t1 select ST_GeomFromText('point(1 1)'); desc t1; diff --git a/mysql-test/suite/innodb_zip/r/page_size.result b/mysql-test/suite/innodb_zip/r/page_size.result index 6355b5e76b6..90018e2c8c2 100644 --- a/mysql-test/suite/innodb_zip/r/page_size.result +++ b/mysql-test/suite/innodb_zip/r/page_size.result @@ -515,6 +515,8 @@ pk09,pk10,pk11,pk12,pk13,pk14,pk15,pk16), KEY pk(sk01,sk02,sk03,sk04,sk05,sk06,sk07,sk08, sk09,sk10,sk11,sk12,sk13,sk14,sk15,sk16)) ROW_FORMAT=Redundant ENGINE=InnoDB; +Warnings: +Warning 1280 Name 'pk' ignored for PRIMARY key. SET @r = repeat('a', 48); INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r, @r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r); @@ -546,6 +548,8 @@ pk09,pk10,pk11,pk12,pk13,pk14,pk15,pk16), KEY pk(sk01,sk02,sk03,sk04,sk05,sk06,sk07,sk08, sk09,sk10,sk11,sk12,sk13,sk14,sk15,sk16)) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4 ENGINE=InnoDB; +Warnings: +Warning 1280 Name 'pk' ignored for PRIMARY key. SET @r = repeat('a', 48); INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r, @r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r); diff --git a/mysql-test/suite/innodb_zip/r/prefix_index_liftedlimit.result b/mysql-test/suite/innodb_zip/r/prefix_index_liftedlimit.result index 96e51e506f2..f2bbd86e9c8 100644 --- a/mysql-test/suite/innodb_zip/r/prefix_index_liftedlimit.result +++ b/mysql-test/suite/innodb_zip/r/prefix_index_liftedlimit.result @@ -1070,6 +1070,8 @@ CREATE TABLE worklog5743 ( col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , PRIMARY KEY `prefix_primary` (col_1_varchar(3072)) ) ROW_FORMAT=DYNAMIC, engine = innodb; +Warnings: +Warning 1280 Name 'prefix_primary' ignored for PRIMARY key. INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072)); INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); @@ -1101,6 +1103,8 @@ CREATE TABLE worklog5743 ( col_1_varchar VARCHAR (4000) , col_2_varchar VARCHAR (4000) , PRIMARY KEY `prefix_primary` (col_1_varchar(3072)) ) ROW_FORMAT=DYNAMIC, engine = innodb; +Warnings: +Warning 1280 Name 'prefix_primary' ignored for PRIMARY key. INSERT INTO worklog5743 VALUES(REPEAT("a", 4000) , REPEAT("o", 4000)); CREATE INDEX prefix_idx ON worklog5743(col_1_varchar (3072)); INSERT INTO worklog5743 VALUES(REPEAT("b", 4000) , REPEAT("p", 4000)); diff --git a/mysql-test/suite/multi_source/disabled.def b/mysql-test/suite/multi_source/disabled.def index 45c8a6c8891..e69de29bb2d 100644 --- a/mysql-test/suite/multi_source/disabled.def +++ b/mysql-test/suite/multi_source/disabled.def @@ -1 +0,0 @@ -mdev-8874 : MDEV-20101 Assertion failure diff --git a/mysql-test/suite/multi_source/multisource.result b/mysql-test/suite/multi_source/multisource.result index d99a377f0c5..928c9799854 100644 --- a/mysql-test/suite/multi_source/multisource.result +++ b/mysql-test/suite/multi_source/multisource.result @@ -47,6 +47,8 @@ drop database if exists db1; create database db1; use db1; create table t1 (i int auto_increment, f1 varchar(16), primary key pk (i,f1)) engine=MyISAM; +Warnings: +Warning 1280 Name 'pk' ignored for PRIMARY key. insert into t1 (f1) values ('one'),('two'); connection slave; select * from db1.t1; diff --git a/mysql-test/suite/parts/r/alter_data_directory_innodb.result b/mysql-test/suite/parts/r/alter_data_directory_innodb.result index d0ad8cd074c..8a43588ea52 100644 --- a/mysql-test/suite/parts/r/alter_data_directory_innodb.result +++ b/mysql-test/suite/parts/r/alter_data_directory_innodb.result @@ -18,6 +18,8 @@ t CREATE TABLE `t` ( (PARTITION `p1` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB, PARTITION `p2` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB) ALTER TABLE t ADD PRIMARY KEY pk(a), ALGORITHM=INPLACE; +Warnings: +Warning 1280 Name 'pk' ignored for PRIMARY key. SHOW CREATE TABLE t; Table Create Table t CREATE TABLE `t` ( @@ -39,6 +41,8 @@ t CREATE TABLE `t` ( SET @TMP = @@GLOBAL.INNODB_FILE_PER_TABLE; SET GLOBAL INNODB_FILE_PER_TABLE=OFF; ALTER TABLE t ADD PRIMARY KEY pk(a), ALGORITHM=INPLACE; +Warnings: +Warning 1280 Name 'pk' ignored for PRIMARY key. SHOW CREATE TABLE t; Table Create Table t CREATE TABLE `t` ( diff --git a/mysql-test/suite/rpl/r/rpl_blackhole.result b/mysql-test/suite/rpl/r/rpl_blackhole.result index 178c23b9f98..a87ba9a9d2d 100644 --- a/mysql-test/suite/rpl/r/rpl_blackhole.result +++ b/mysql-test/suite/rpl/r/rpl_blackhole.result @@ -42,6 +42,8 @@ COUNT(*) >>> Something was written to binary log <<< connection master; ALTER TABLE t1 ADD PRIMARY KEY pk_t1 (a,b); +Warnings: +Warning 1280 Name 'pk_t1' ignored for PRIMARY key. connection slave; connection master; INSERT INTO t1 VALUES (1,2,1),(2,2,2),(3,2,3),(4,2,4); diff --git a/mysql-test/suite/rpl/r/rpl_blackhole_row_annotate.result b/mysql-test/suite/rpl/r/rpl_blackhole_row_annotate.result index d92c3dce55b..3c5711855d8 100644 --- a/mysql-test/suite/rpl/r/rpl_blackhole_row_annotate.result +++ b/mysql-test/suite/rpl/r/rpl_blackhole_row_annotate.result @@ -47,6 +47,8 @@ COUNT(*) >>> Something was written to binary log <<< connection master; ALTER TABLE t1 ADD PRIMARY KEY pk_t1 (a,b); +Warnings: +Warning 1280 Name 'pk_t1' ignored for PRIMARY key. connection slave; connection master; INSERT INTO t1 VALUES (1,2,1),(2,2,2),(3,2,3),(4,2,4); diff --git a/mysql-test/suite/sys_vars/r/replicate_ignore_table_basic.result b/mysql-test/suite/sys_vars/r/replicate_ignore_table_basic.result index db97ce14c93..a1701635f0e 100644 --- a/mysql-test/suite/sys_vars/r/replicate_ignore_table_basic.result +++ b/mysql-test/suite/sys_vars/r/replicate_ignore_table_basic.result @@ -46,3 +46,13 @@ SELECT @@GLOBAL.replicate_ignore_table; # Cleanup. SET @@GLOBAL.replicate_ignore_table = @save_replicate_ignore_table; +# +# MDEV-20101 Assertion failure on select @@global.'m2'.replicate_ignore_table +# +SET NAMES latin1; +SELECT @@global.'m2'.replicate_ignore_table; +@@global.'m2'.replicate_ignore_table +NULL +Warnings: +Warning 1617 There is no master connection 'm2' +Warning 1617 There is no master connection 'm2' diff --git a/mysql-test/suite/sys_vars/t/replicate_ignore_table_basic.test b/mysql-test/suite/sys_vars/t/replicate_ignore_table_basic.test index 56cf7f17c7f..1cf6f010eca 100644 --- a/mysql-test/suite/sys_vars/t/replicate_ignore_table_basic.test +++ b/mysql-test/suite/sys_vars/t/replicate_ignore_table_basic.test @@ -49,3 +49,10 @@ SELECT @@GLOBAL.replicate_ignore_table; --echo # Cleanup. SET @@GLOBAL.replicate_ignore_table = @save_replicate_ignore_table; + +--echo # +--echo # MDEV-20101 Assertion failure on select @@global.'m2'.replicate_ignore_table +--echo # + +SET NAMES latin1; +SELECT @@global.'m2'.replicate_ignore_table; diff --git a/mysql-test/suite/versioning/r/online.result b/mysql-test/suite/versioning/r/online.result index 41a2556628a..36251ddd550 100644 --- a/mysql-test/suite/versioning/r/online.result +++ b/mysql-test/suite/versioning/r/online.result @@ -66,7 +66,9 @@ alter table t2 change a a int with system versioning, add primary key pk (a); affected rows: 0 -info: Records: 0 Duplicates: 0 Warnings: 0 +info: Records: 0 Duplicates: 0 Warnings: 1 +Warnings: +Warning 1280 Name 'pk' ignored for PRIMARY key. # restart update t1 set a=2; select count(*) from t1 for system_time all; diff --git a/mysql-test/suite/versioning/r/trx_id.result b/mysql-test/suite/versioning/r/trx_id.result index 333b8b3efa0..5d4669c46e2 100644 --- a/mysql-test/suite/versioning/r/trx_id.result +++ b/mysql-test/suite/versioning/r/trx_id.result @@ -437,7 +437,9 @@ ALTER TABLE t CHANGE a a INT WITH SYSTEM VERSIONING, ADD PRIMARY KEY pk(a); affected rows: 0 -info: Records: 0 Duplicates: 0 Warnings: 0 +info: Records: 0 Duplicates: 0 Warnings: 1 +Warnings: +Warning 1280 Name 'pk' ignored for PRIMARY key. SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t ON c.table_id=t.table_id |