diff options
Diffstat (limited to 'mysql-test')
57 files changed, 7156 insertions, 123 deletions
diff --git a/mysql-test/extra/rpl_tests/rpl_lower_case_table_names.test b/mysql-test/extra/rpl_tests/rpl_lower_case_table_names.test new file mode 100644 index 00000000000..fa48142ee91 --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_lower_case_table_names.test @@ -0,0 +1,141 @@ +# BUG#37656 +# +# This test aims at checking whether lower_case_table_names=1 option works +# for database names and table names. +# +# This test checks the following (when lower_case_table_names=1 is set on slave): +# (i) creating a database on upper case on master results in lower case +# database name on slave +# (ii) creating tables with upper case names on master results in lower case +# table names on slave +# (iii) loading data infile into capitalized table name on master replicates to +# lower case table name on slave +# (iv) Propagating changes from upper case table names on into correspondent +# lower case table names on slave works. + + +# setup: create database and tables +-- echo ******** [ MASTER ] ******** +-- let $dbname_upper= BUG_37656 +-- let $dbname_lower= `SELECT LOWER('$dbname_upper')` +-- eval CREATE DATABASE $dbname_upper +-- eval use $dbname_upper + +# assert: database names are in upper case in master and lower +# case in slave +-- eval show databases like '$dbname_upper' +sync_slave_with_master; +-- echo ******** [ SLAVE ] ******** +--eval show databases like '$dbname_lower' + +-- connection master +-- echo ******** [ MASTER ] ******** +CREATE TABLE T1 (a int); +-- eval CREATE TABLE T2 (b int) ENGINE=$engine +CREATE TABLE T3 (txt TEXT); + +# assert: that tables exist on master with upper case names +show tables; + +# assert: that tables exist on slave but with lower case names +-- sync_slave_with_master +-- echo ******** [ SLAVE ] ******** +-- eval use $dbname_lower +show tables; + +# action: lets create t1 for asserting below that t1 does not get changes +# from master (slave configured with --replicate-ignore-db=$dbname_lower.t1) +CREATE TABLE t1 (a INT); + +# action: fill data into tables +-- connection master +-- echo ******** [ MASTER ] ******** +-- eval use $dbname_upper +INSERT INTO T1 VALUES (1); +INSERT INTO T2 VALUES (1); +if (`SELECT @@session.binlog_format != 'ROW'`) +{ + -- eval LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE $dbname_upper.T3 +} + +if (`SELECT @@session.binlog_format = 'ROW'`) +{ + use test; + -- eval INSERT INTO $dbname_upper.T1 VALUES (2) + -- eval INSERT INTO $dbname_upper.T2 VALUES (2) + -- eval LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE $dbname_upper.T3 +} +# assert: lower case tables on lower case database on slave +# get updates from upper case tables on upper case +# database on master +-- sync_slave_with_master +-- echo ******** [ SLAVE ] ******** + +# assert: changes for slave's t1 were filterd out +if (`SELECT count(*) != 0 FROM t1`) +{ + -- echo UNEXPECTED DATA on $dbname_lower.t1 as table is filtered by replicate-ignore-table rules +} + +-- let $diff_tables=master:$dbname_upper.T2, slave:$dbname_lower.t2 +-- source include/diff_tables.inc + +-- let $diff_tables=master:$dbname_upper.T3, slave:$dbname_lower.t3 +-- source include/diff_tables.inc + +# clean up +-- connection master +-- echo ******** [ MASTER ] ******** +-- eval DROP DATABASE $dbname_upper +-- sync_slave_with_master + + +# +# BUG#50653: drop procedure implicitely treats db name in a case sensitive way +# + +-- connection master + +-- let $dbname= B50653 +-- let $procname= b50653_proc + +-- eval CREATE DATABASE $dbname +-- eval USE $dbname +-- eval CREATE PROCEDURE $procname() BEGIN SELECT 1; END + +if (`SELECT count(*) = 1 FROM mysql.proc WHERE name like '$dbname'`) +{ + -- die Procedure not created on MASTER +} + +-- sync_slave_with_master +if (`SELECT count(*) = 1 FROM mysql.proc WHERE name like '$dbname'`) +{ + -- die Procedure not created on SLAVE +} + +-- connection master +-- eval DROP PROCEDURE $procname + +if (`SELECT count(*) FROM mysql.proc WHERE name like '$dbname'`) +{ + -- die Procedure not dropped on MASTER +} + +-- sync_slave_with_master +if (`SELECT count(*) FROM mysql.proc WHERE name like '$dbname'`) +{ + -- die Procedure not dropped on SLAVE +} + +-- let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1) +if ($last_error) +{ + -- die UNEXPECTED SLAVE SQL error: $last_error +} + +-- connection master +-- eval DROP DATABASE $dbname +-- sync_slave_with_master + +-- source include/rpl_end.inc diff --git a/mysql-test/lib/My/Config.pm b/mysql-test/lib/My/Config.pm index a343a0ef688..4af59a178de 100644 --- a/mysql-test/lib/My/Config.pm +++ b/mysql-test/lib/My/Config.pm @@ -195,14 +195,10 @@ sub value { my ($self, $option_name)= @_; my $option= $self->option($option_name); - if (! defined($option) and defined $ENV{$option_name}) { + if (! defined($option)) { my $value= $ENV{$option_name}; $option= My::Config::Option->new($option_name, $value); } - - croak "No option named '$option_name' in group '$self->{name}'" - if ! defined($option); - return $option->value(); } diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index d5758abb03e..63801357dd5 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -137,8 +137,6 @@ my $opt_start_dirty; my $opt_start_exit; my $start_only; -our @global_suppressions; - END { if ( defined $opt_tmpdir_pid and $opt_tmpdir_pid == $$ ) { @@ -190,8 +188,6 @@ my @DEFAULT_SUITES= qw( sys_vars- unit- vcol- - wsrep- - galera- ); my $opt_suites; @@ -363,7 +359,6 @@ my $opt_max_test_fail= env_or_val(MTR_MAX_TEST_FAIL => 10); my $opt_core_on_failure= 0; my $opt_parallel= $ENV{MTR_PARALLEL} || 1; -my $opt_port_group_size = $ENV{MTR_PORT_GROUP_SIZE} || 20; # lock file to stop tests my $opt_stop_file= $ENV{MTR_STOP_FILE}; @@ -1140,7 +1135,6 @@ sub command_line_setup { # Specify ports 'build-thread|mtr-build-thread=i' => \$opt_build_thread, 'port-base|mtr-port-base=i' => \$opt_port_base, - 'port-group-size=s' => \$opt_port_group_size, # Test case authoring 'record' => \$opt_record, @@ -1469,7 +1463,7 @@ sub command_line_setup { foreach my $fs (@tmpfs_locations) { - if ( -d $fs && ! -l $fs ) + if ( -d $fs && ! -l $fs && -w $fs ) { my $template= "var_${opt_build_thread}_XXXX"; $opt_mem= tempdir( $template, DIR => $fs, CLEANUP => 0); @@ -1833,16 +1827,16 @@ sub set_build_thread_ports($) { $ENV{MTR_BUILD_THREAD}= $build_thread; # Calculate baseport - $baseport= $build_thread * $opt_port_group_size + 10000; - if ( $baseport < 5001 or $baseport + $opt_port_group_size >= 32767 ) + $baseport= $build_thread * 20 + 10000; + if ( $baseport < 5001 or $baseport + 19 >= 32767 ) { mtr_error("MTR_BUILD_THREAD number results in a port", "outside 5001 - 32767", - "($baseport - $baseport + $opt_port_group_size)"); + "($baseport - $baseport + 19)"); } mtr_report("Using MTR_BUILD_THREAD $build_thread,", - "with reserved ports $baseport..".($baseport+($opt_port_group_size-1))); + "with reserved ports $baseport..".($baseport+19)); } @@ -3190,6 +3184,7 @@ sub ndbcluster_start ($) { return 0; } + sub mysql_server_start($) { my ($mysqld, $tinfo) = @_; @@ -3394,8 +3389,8 @@ sub kill_leftovers ($) { sub check_ports_free ($) { my $bthread= shift; - my $portbase = $bthread * $opt_port_group_size + 10000; - for ($portbase..$portbase+($opt_port_group_size-1)){ + my $portbase = $bthread * 10 + 10000; + for ($portbase..$portbase+9){ if (mtr_ping_port($_)){ mtr_report(" - 'localhost:$_' was not free"); return 0; # One port was not free @@ -4817,7 +4812,6 @@ sub extract_warning_lines ($$) { # Perl code. my @antipatterns = ( - @global_suppressions, qr/error .*connecting to master/, qr/Plugin 'ndbcluster' will be forced to shutdown/, qr/InnoDB: Error: in ALTER TABLE `test`.`t[12]`/, @@ -6531,8 +6525,6 @@ Options that specify ports build-thread=# Can be set in environment variable MTR_BUILD_THREAD. Set MTR_BUILD_THREAD="auto" to automatically aquire a build thread id that is unique to current host - port-group-size=N Reserve groups of TCP ports of size N for each MTR thread - Options for test case authoring diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index c6e3c7e31d9..b0c5664c92a 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -1402,6 +1402,56 @@ t1 CREATE TABLE `t1` ( `consultant_id` bigint(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 DROP TABLE t1; +# +# BUG#27788685: NO WARNING WHEN TRUNCATING A STRING WITH DATA LOSS +# +SET GLOBAL max_allowed_packet=17825792; +CREATE TABLE t1 (t1_fld1 TEXT); +CREATE TABLE t2 (t2_fld1 MEDIUMTEXT); +CREATE TABLE t3 (t3_fld1 LONGTEXT); +INSERT INTO t1 VALUES (REPEAT('a',300)); +INSERT INTO t2 VALUES (REPEAT('b',65680)); +INSERT INTO t3 VALUES (REPEAT('c',16777300)); +SELECT LENGTH(t1_fld1) FROM t1; +LENGTH(t1_fld1) +300 +SELECT LENGTH(t2_fld1) FROM t2; +LENGTH(t2_fld1) +65680 +SELECT LENGTH(t3_fld1) FROM t3; +LENGTH(t3_fld1) +16777300 +# With strict mode +SET SQL_MODE='STRICT_ALL_TABLES'; +ALTER TABLE t1 CHANGE `t1_fld1` `my_t1_fld1` TINYTEXT; +ERROR 22001: Data too long for column 'my_t1_fld1' at row 1 +ALTER TABLE t2 CHANGE `t2_fld1` `my_t2_fld1` TEXT; +ERROR 22001: Data too long for column 'my_t2_fld1' at row 1 +ALTER TABLE t3 CHANGE `t3_fld1` `my_t3_fld1` MEDIUMTEXT; +ERROR 22001: Data too long for column 'my_t3_fld1' at row 1 +# With non-strict mode +SET SQL_MODE=''; +ALTER TABLE t1 CHANGE `t1_fld1` `my_t1_fld1` TINYTEXT; +Warnings: +Warning 1265 Data truncated for column 'my_t1_fld1' at row 1 +ALTER TABLE t2 CHANGE `t2_fld1` `my_t2_fld1` TEXT; +Warnings: +Warning 1265 Data truncated for column 'my_t2_fld1' at row 1 +ALTER TABLE t3 CHANGE `t3_fld1` `my_t3_fld1` MEDIUMTEXT; +Warnings: +Warning 1265 Data truncated for column 'my_t3_fld1' at row 1 +SELECT LENGTH(my_t1_fld1) FROM t1; +LENGTH(my_t1_fld1) +255 +SELECT LENGTH(my_t2_fld1) FROM t2; +LENGTH(my_t2_fld1) +65535 +SELECT LENGTH(my_t3_fld1) FROM t3; +LENGTH(my_t3_fld1) +16777215 +DROP TABLE t1, t2, t3; +SET SQL_MODE=default; +SET GLOBAL max_allowed_packet=default; CREATE TABLE t1 ( id INT(11) NOT NULL, x_param INT(11) DEFAULT NULL, diff --git a/mysql-test/r/alter_table.test b/mysql-test/r/alter_table.test new file mode 100644 index 00000000000..aea1a880a81 --- /dev/null +++ b/mysql-test/r/alter_table.test @@ -0,0 +1,1908 @@ +if (`select plugin_auth_version < "5.6.26" from information_schema.plugins where plugin_name='innodb'`) +{ + --skip Not fixed in XtraDB below 5.6.26 +} +--source include/have_innodb.inc +# +# Test of alter table +# +--disable_warnings +drop table if exists t1,t2; +drop database if exists mysqltest; +--enable_warnings + +create table t1 ( +col1 int not null auto_increment primary key, +col2 varchar(30) not null, +col3 varchar (20) not null, +col4 varchar(4) not null, +col5 enum('PENDING', 'ACTIVE', 'DISABLED') not null, +col6 int not null, to_be_deleted int); +insert into t1 values (2,4,3,5,"PENDING",1,7); +alter table t1 +add column col4_5 varchar(20) not null after col4, +add column col7 varchar(30) not null after col5, +add column col8 datetime not null, drop column to_be_deleted, +change column col2 fourth varchar(30) not null after col3, +modify column col6 int not null first; +select * from t1; +drop table t1; + +create table t1 (bandID MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, payoutID SMALLINT UNSIGNED NOT NULL); +insert into t1 (bandID,payoutID) VALUES (1,6),(2,6),(3,4),(4,9),(5,10),(6,1),(7,12),(8,12); +alter table t1 add column new_col int, order by payoutid,bandid; +select * from t1; +alter table t1 order by bandid,payoutid; +select * from t1; +drop table t1; + +# Check that pack_keys and dynamic length rows are not forced. + +CREATE TABLE t1 ( +GROUP_ID int(10) unsigned DEFAULT '0' NOT NULL, +LANG_ID smallint(5) unsigned DEFAULT '0' NOT NULL, +NAME varchar(80) DEFAULT '' NOT NULL, +PRIMARY KEY (GROUP_ID,LANG_ID), +KEY NAME (NAME)); +#show table status like "t1"; +ALTER TABLE t1 CHANGE NAME NAME CHAR(80) not null; +--replace_column 8 # +SHOW FULL COLUMNS FROM t1; +DROP TABLE t1; + +# +# Test of ALTER TABLE ... ORDER BY +# + +create table t1 (n int); +insert into t1 values(9),(3),(12),(10); +alter table t1 order by n; +select * from t1; +drop table t1; + +CREATE TABLE t1 ( + id int(11) unsigned NOT NULL default '0', + category_id tinyint(4) unsigned NOT NULL default '0', + type_id tinyint(4) unsigned NOT NULL default '0', + body text NOT NULL, + user_id int(11) unsigned NOT NULL default '0', + status enum('new','old') NOT NULL default 'new', + PRIMARY KEY (id) +) ENGINE=MyISAM; + +ALTER TABLE t1 ORDER BY t1.id, t1.status, t1.type_id, t1.user_id, t1.body; +DROP TABLE t1; + +# +# The following combination found a hang-bug in MyISAM +# + +CREATE TABLE t1 (AnamneseId int(10) unsigned NOT NULL auto_increment,B BLOB,PRIMARY KEY (AnamneseId)) engine=myisam; +insert into t1 values (null,"hello"); +LOCK TABLES t1 WRITE; +ALTER TABLE t1 ADD Column new_col int not null; +UNLOCK TABLES; +OPTIMIZE TABLE t1; +DROP TABLE t1; + +# +# Drop and add an auto_increment column +# + +create table t1 (i int unsigned not null auto_increment primary key); +insert into t1 values (null),(null),(null),(null); +alter table t1 drop i,add i int unsigned not null auto_increment, drop primary key, add primary key (i); +select * from t1; +drop table t1; + +# +# Bug #2628: 'alter table t1 rename mysqltest.t1' silently drops mysqltest.t1 +# if it exists +# +create table t1 (name char(15)); +insert into t1 (name) values ("current"); +create database mysqltest; +create table mysqltest.t1 (name char(15)); +insert into mysqltest.t1 (name) values ("mysqltest"); +select * from t1; +select * from mysqltest.t1; +--error ER_TABLE_EXISTS_ERROR +alter table t1 rename mysqltest.t1; +select * from t1; +select * from mysqltest.t1; +drop table t1; +drop database mysqltest; + +# +# ALTER TABLE ... ENABLE/DISABLE KEYS + +create table t1 (n1 int not null, n2 int, n3 int, n4 float, + unique(n1), + key (n1, n2, n3, n4), + key (n2, n3, n4, n1), + key (n3, n4, n1, n2), + key (n4, n1, n2, n3) ); +alter table t1 disable keys; +show keys from t1; +#let $1=10000; +let $1=10; +--disable_query_log +begin; +while ($1) +{ + eval insert into t1 values($1,RAND()*1000,RAND()*1000,RAND()); + dec $1; +} +commit; +--enable_query_log +alter table t1 enable keys; +show keys from t1; +drop table t1; + +# +# Alter table and rename +# + +create table t1 (i int unsigned not null auto_increment primary key); +alter table t1 rename t2; +alter table t2 rename t1, add c char(10) comment "no comment"; +show columns from t1; +drop table t1; + +# implicit analyze + +create table t1 (a int, b int); +let $1=100; +--disable_query_log +begin; +while ($1) +{ + eval insert into t1 values(1,$1), (2,$1), (3, $1); + dec $1; +} +commit; +--enable_query_log +alter table t1 add unique (a,b), add key (b); +show keys from t1; +analyze table t1; +show keys from t1; +drop table t1; + +# +# Test of ALTER TABLE DELAYED +# + +CREATE TABLE t1 (i int(10), index(i) ) ENGINE=MyISAM; +ALTER TABLE t1 DISABLE KEYS; +INSERT DELAYED INTO t1 VALUES(1),(2),(3); +ALTER TABLE t1 ENABLE KEYS; +drop table t1; + +# +# Test ALTER TABLE ENABLE/DISABLE keys when things are locked +# + +CREATE TABLE t1 ( + Host varchar(16) binary NOT NULL default '', + User varchar(16) binary NOT NULL default '', + PRIMARY KEY (Host,User) +) ENGINE=MyISAM; + +ALTER TABLE t1 DISABLE KEYS; +LOCK TABLES t1 WRITE; +INSERT INTO t1 VALUES ('localhost','root'),('localhost',''),('games','monty'); +SHOW INDEX FROM t1; +ALTER TABLE t1 ENABLE KEYS; +UNLOCK TABLES; +CHECK TABLES t1; +DROP TABLE t1; + +# +# Test with two keys +# + +CREATE TABLE t1 ( + Host varchar(16) binary NOT NULL default '', + User varchar(16) binary NOT NULL default '', + PRIMARY KEY (Host,User), + KEY (Host) +) ENGINE=MyISAM; + +ALTER TABLE t1 DISABLE KEYS; +SHOW INDEX FROM t1; +LOCK TABLES t1 WRITE; +INSERT INTO t1 VALUES ('localhost','root'),('localhost',''); +SHOW INDEX FROM t1; +ALTER TABLE t1 ENABLE KEYS; +SHOW INDEX FROM t1; +UNLOCK TABLES; +CHECK TABLES t1; + +# Test RENAME with LOCK TABLES +LOCK TABLES t1 WRITE; +ALTER TABLE t1 RENAME t2; +UNLOCK TABLES; +select * from t2; +DROP TABLE t2; + +# +# Test disable keys with locking +# +CREATE TABLE t1 ( + Host varchar(16) binary NOT NULL default '', + User varchar(16) binary NOT NULL default '', + PRIMARY KEY (Host,User), + KEY (Host) +) ENGINE=MyISAM; + +LOCK TABLES t1 WRITE; +ALTER TABLE t1 DISABLE KEYS; +SHOW INDEX FROM t1; +DROP TABLE t1; + +# +# BUG#4717 - check for valid table names +# +create table t1 (a int); +--error ER_WRONG_TABLE_NAME +alter table t1 rename to ``; +--error ER_WRONG_TABLE_NAME +rename table t1 to ``; +drop table t1; + +# +# BUG#6236 - ALTER TABLE MODIFY should set implicit NOT NULL on PK columns +# +drop table if exists t1, t2; +create table t1 ( a varchar(10) not null primary key ) engine=myisam; +create table t2 ( a varchar(10) not null primary key ) engine=merge union=(t1); +flush tables; +alter table t1 modify a varchar(10); +show create table t2; +flush tables; +alter table t1 modify a varchar(10) not null; +show create table t2; +drop table if exists t1, t2; + +# The following is also part of bug #6236 (CREATE TABLE didn't properly count +# not null columns for primary keys) + +create table t1 (a int, b int, c int, d int, e int, f int, g int, h int,i int, primary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM; +insert into t1 (a) values(1); +--replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X +show table status like 't1'; +alter table t1 modify a int; +--replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X +show table status like 't1'; +drop table t1; +create table t1 (a int not null, b int not null, c int not null, d int not null, e int not null, f int not null, g int not null, h int not null,i int not null, primary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM; +insert into t1 (a) values(1); +--replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X +show table status like 't1'; +drop table t1; + +# +# Test that data get converted when character set is changed +# Test that data doesn't get converted when src or dst is BINARY/BLOB +# +set names koi8r; +create table t1 (a char(10) character set koi8r); +insert into t1 values ('ÔÅÓÔ'); +select a,hex(a) from t1; +alter table t1 change a a char(10) character set cp1251; +select a,hex(a) from t1; +alter table t1 change a a binary(4); +select a,hex(a) from t1; +alter table t1 change a a char(10) character set cp1251; +select a,hex(a) from t1; +alter table t1 change a a char(10) character set koi8r; +select a,hex(a) from t1; +alter table t1 change a a varchar(10) character set cp1251; +select a,hex(a) from t1; +alter table t1 change a a char(10) character set koi8r; +select a,hex(a) from t1; +alter table t1 change a a text character set cp1251; +select a,hex(a) from t1; +alter table t1 change a a char(10) character set koi8r; +select a,hex(a) from t1; +delete from t1; + +# +# Test ALTER TABLE .. CHARACTER SET .. +# +show create table t1; +alter table t1 DEFAULT CHARACTER SET latin1; +show create table t1; +alter table t1 CONVERT TO CHARACTER SET latin1; +show create table t1; +alter table t1 DEFAULT CHARACTER SET cp1251; +show create table t1; + +drop table t1; + +# +# Bug#2821 +# Test that table CHARACTER SET does not affect blobs +# +create table t1 (myblob longblob,mytext longtext) +default charset latin1 collate latin1_general_cs; +show create table t1; +alter table t1 character set latin2; +show create table t1; +drop table t1; + +# +# Bug 2361 (Don't drop UNIQUE with DROP PRIMARY KEY) +# + +CREATE TABLE t1 (a int PRIMARY KEY, b INT UNIQUE); +ALTER TABLE t1 DROP PRIMARY KEY; +SHOW CREATE TABLE t1; +--error ER_CANT_DROP_FIELD_OR_KEY +ALTER TABLE t1 DROP PRIMARY KEY; +DROP TABLE t1; + +# BUG#3899 +create table t1 (a int, b int, key(a)); +insert into t1 values (1,1), (2,2); +--error ER_CANT_DROP_FIELD_OR_KEY +alter table t1 drop key no_such_key; +alter table t1 drop key a; +drop table t1; + +# +# BUG 12207 alter table ... discard table space on MyISAM table causes ERROR 2013 (HY000) +# +# Some platforms (Mac OS X, Windows) will send the error message using small letters. +CREATE TABLE T12207(a int) ENGINE=MYISAM; +--replace_result t12207 T12207 +--error ER_ILLEGAL_HA +ALTER TABLE T12207 DISCARD TABLESPACE; +DROP TABLE T12207; + +# +# Bug #6479 ALTER TABLE ... changing charset fails for TEXT columns +# +# The column's character set was changed but the actual data was not +# modified. In other words, the values were reinterpreted +# as UTF8 instead of being converted. +create table t1 (a text) character set koi8r; +insert into t1 values (_koi8r'ÔÅÓÔ'); +select hex(a) from t1; +alter table t1 convert to character set cp1251; +select hex(a) from t1; +drop table t1; + +# +# Test for bug #7884 "Able to add invalid unique index on TIMESTAMP prefix" +# MySQL should not think that packed field with non-zero decimals is +# geometry field and allow to create prefix index which is +# shorter than packed field length. +# +create table t1 ( a timestamp ); +--error ER_WRONG_SUB_KEY +alter table t1 add unique ( a(1) ); +drop table t1; + +# +# Bug #24395: ALTER TABLE DISABLE KEYS doesn't work when modifying the table +# +# This problem happens if the data change is compatible. +# Changing to the same type is compatible for example. +# +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (a int, key(a)); +show indexes from t1; +--echo "this used not to disable the index" +alter table t1 modify a int, disable keys; +show indexes from t1; + +alter table t1 enable keys; +show indexes from t1; + +alter table t1 modify a bigint, disable keys; +show indexes from t1; + +alter table t1 enable keys; +show indexes from t1; + +alter table t1 add b char(10), disable keys; +show indexes from t1; + +alter table t1 add c decimal(10,2), enable keys; +show indexes from t1; + +--echo "this however did" +alter table t1 disable keys; +show indexes from t1; + +desc t1; + +alter table t1 add d decimal(15,5); +--echo "The key should still be disabled" +show indexes from t1; + +drop table t1; + +--echo "Now will test with one unique index" +create table t1(a int, b char(10), unique(a)); +show indexes from t1; +alter table t1 disable keys; +show indexes from t1; +alter table t1 enable keys; + +--echo "If no copy on noop change, this won't touch the data file" +--echo "Unique index, no change" +alter table t1 modify a int, disable keys; +show indexes from t1; + +--echo "Change the type implying data copy" +--echo "Unique index, no change" +alter table t1 modify a bigint, disable keys; +show indexes from t1; + +alter table t1 modify a bigint; +show indexes from t1; + +alter table t1 modify a int; +show indexes from t1; + +drop table t1; + +--echo "Now will test with one unique and one non-unique index" +create table t1(a int, b char(10), unique(a), key(b)); +show indexes from t1; +alter table t1 disable keys; +show indexes from t1; +alter table t1 enable keys; + + +--echo "If no copy on noop change, this won't touch the data file" +--echo "The non-unique index will be disabled" +alter table t1 modify a int, disable keys; +show indexes from t1; +alter table t1 enable keys; +show indexes from t1; + +--echo "Change the type implying data copy" +--echo "The non-unique index will be disabled" +alter table t1 modify a bigint, disable keys; +show indexes from t1; + +--echo "Change again the type, but leave the indexes as_is" +alter table t1 modify a int; +show indexes from t1; +--echo "Try the same. When data is no copied on similar tables, this is noop" +alter table t1 modify a int; +show indexes from t1; + +drop table t1; + + +# +# Bug#11493 - Alter table rename to default database does not work without +# db name qualifying +# +create database mysqltest; +create table t1 (c1 int); +# Move table to other database. +alter table t1 rename mysqltest.t1; +# Assure that it has moved. +--error ER_BAD_TABLE_ERROR +drop table t1; +# Move table back. +alter table mysqltest.t1 rename t1; +# Assure that it is back. +drop table t1; +# Now test for correct message if no database is selected. +# Create t1 in 'test'. +create table t1 (c1 int); +# Change to other db. +use mysqltest; +# Drop the current db. This de-selects any db. +drop database mysqltest; +# Now test for correct message. +--error ER_NO_DB_ERROR +alter table test.t1 rename t1; +# Check that explicit qualifying works even with no selected db. +alter table test.t1 rename test.t1; +# Go back to standard 'test' db. +use test; +drop table t1; + +# +# BUG#23404 - ROW_FORMAT=FIXED option is lost is an index is added to the +# table +# +CREATE TABLE t1(a INT) ROW_FORMAT=FIXED; +CREATE INDEX i1 ON t1(a); +SHOW CREATE TABLE t1; +DROP INDEX i1 ON t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +# +# Bug#24219 - ALTER TABLE ... RENAME TO ... , DISABLE KEYS leads to crash +# +--disable_warnings +DROP TABLE IF EXISTS bug24219; +DROP TABLE IF EXISTS bug24219_2; +--enable_warnings + +CREATE TABLE bug24219 (a INT, INDEX(a)); + +SHOW INDEX FROM bug24219; + +ALTER TABLE bug24219 RENAME TO bug24219_2, DISABLE KEYS; + +SHOW INDEX FROM bug24219_2; + +DROP TABLE bug24219_2; + +# +# Bug#24562 (ALTER TABLE ... ORDER BY ... with complex expression asserts) +# + +--disable_warnings +drop table if exists table_24562; +--enable_warnings + +create table table_24562( + section int, + subsection int, + title varchar(50)); + +insert into table_24562 values +(1, 0, "Introduction"), +(1, 1, "Authors"), +(1, 2, "Acknowledgements"), +(2, 0, "Basics"), +(2, 1, "Syntax"), +(2, 2, "Client"), +(2, 3, "Server"), +(3, 0, "Intermediate"), +(3, 1, "Complex queries"), +(3, 2, "Stored Procedures"), +(3, 3, "Stored Functions"), +(4, 0, "Advanced"), +(4, 1, "Replication"), +(4, 2, "Load balancing"), +(4, 3, "High availability"), +(5, 0, "Conclusion"); + +select * from table_24562; + +alter table table_24562 add column reviewer varchar(20), +order by title; + +select * from table_24562; + +update table_24562 set reviewer="Me" where section=2; +update table_24562 set reviewer="You" where section=3; + +alter table table_24562 +order by section ASC, subsection DESC; + +select * from table_24562; + +alter table table_24562 +order by table_24562.subsection ASC, table_24562.section DESC; + +select * from table_24562; + +--error ER_PARSE_ERROR +alter table table_24562 order by 12; +--error ER_PARSE_ERROR +alter table table_24562 order by (section + 12); +--error ER_PARSE_ERROR +alter table table_24562 order by length(title); +--error ER_PARSE_ERROR +alter table table_24562 order by (select 12 from dual); + +--error ER_BAD_FIELD_ERROR +alter table table_24562 order by no_such_col; + +drop table table_24562; + +# End of 4.1 tests + +# +# Bug #14693 (ALTER SET DEFAULT doesn't work) +# + +create table t1 (mycol int(10) not null); +alter table t1 alter column mycol set default 0; +desc t1; +drop table t1; + +# +# Bug#25262 Auto Increment lost when changing Engine type +# + +create table t1(id int(8) primary key auto_increment) engine=heap; + +insert into t1 values (null); +insert into t1 values (null); + +select * from t1; + +# Set auto increment to 50 +alter table t1 auto_increment = 50; + +# Alter to myisam +alter table t1 engine = myisam; + +# This insert should get id 50 +insert into t1 values (null); +select * from t1; + +# Alter to heap again +alter table t1 engine = heap; +insert into t1 values (null); +select * from t1; + +drop table t1; + +# +# Bug#27507: Wrong DATETIME value was allowed by ALTER TABLE in the +# NO_ZERO_DATE mode. +# +set @orig_sql_mode = @@sql_mode; +set sql_mode="no_zero_date"; +create table t1(f1 int); +alter table t1 add column f2 datetime not null, add column f21 date not null; +insert into t1 values(1,'2000-01-01','2000-01-01'); +--error 1292 +alter table t1 add column f3 datetime not null; +--error 1292 +alter table t1 add column f3 date not null; +--error 1292 +alter table t1 add column f4 datetime not null default '2002-02-02', + add column f41 date not null; +alter table t1 add column f4 datetime not null default '2002-02-02', + add column f41 date not null default '2002-02-02'; +select * from t1; +drop table t1; +set sql_mode= @orig_sql_mode; + +# +# Some additional tests for new, faster alter table. Note that most of the +# whole alter table code is being tested all around the test suite already. +# + +create table t1 (v varchar(32)); +insert into t1 values ('def'),('abc'),('hij'),('3r4f'); +select * from t1; +# Fast alter, no copy performed +alter table t1 change v v2 varchar(32); +select * from t1; +# Fast alter, no copy performed +alter table t1 change v2 v varchar(64); +select * from t1; +update t1 set v = 'lmn' where v = 'hij'; +select * from t1; +# Regular alter table +alter table t1 add i int auto_increment not null primary key first; +select * from t1; +update t1 set i=5 where i=3; +select * from t1; +alter table t1 change i i bigint; +select * from t1; +alter table t1 add unique key (i, v); +select * from t1 where i between 2 and 4 and v in ('def','3r4f','lmn'); +drop table t1; + +# +# Bug#6073 "ALTER table minor glich": ALTER TABLE complains that an index +# without # prefix is not allowed for TEXT columns, while index +# is defined with prefix. +# +create table t1 (t varchar(255) default null, key t (t(80))) +engine=myisam default charset=latin1; +alter table t1 change t t text; +drop table t1; + +# +# Bug #26794: Adding an index with a prefix on a SPATIAL type breaks ALTER +# TABLE +# +CREATE TABLE t1 (a varchar(500)); + +ALTER TABLE t1 ADD b GEOMETRY NOT NULL, ADD SPATIAL INDEX(b); +SHOW CREATE TABLE t1; +ALTER TABLE t1 ADD KEY(b(50)); +SHOW CREATE TABLE t1; + +ALTER TABLE t1 ADD c POINT; +SHOW CREATE TABLE t1; + +--error ER_WRONG_SUB_KEY +CREATE TABLE t2 (a INT, KEY (a(20))); + +ALTER TABLE t1 ADD d INT; +--error ER_WRONG_SUB_KEY +ALTER TABLE t1 ADD KEY (d(20)); + +# the 5.1 part of the test +--error ER_WRONG_SUB_KEY +ALTER TABLE t1 ADD e GEOMETRY NOT NULL, ADD SPATIAL KEY (e(30)); + +DROP TABLE t1; + +# +# Bug#18038 MySQL server corrupts binary columns data +# + +CREATE TABLE t1 (s CHAR(8) BINARY); +INSERT INTO t1 VALUES ('test'); +SELECT LENGTH(s) FROM t1; +ALTER TABLE t1 MODIFY s CHAR(10) BINARY; +SELECT LENGTH(s) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (s BINARY(8)); +INSERT INTO t1 VALUES ('test'); +SELECT LENGTH(s) FROM t1; +SELECT HEX(s) FROM t1; +ALTER TABLE t1 MODIFY s BINARY(10); +SELECT HEX(s) FROM t1; +SELECT LENGTH(s) FROM t1; +DROP TABLE t1; + +# +# Bug#19386: Multiple alter causes crashed table +# The trailing column would get corrupted data, or server could not even read +# it. +# + +CREATE TABLE t1 (v VARCHAR(3), b INT); +INSERT INTO t1 VALUES ('abc', 5); +SELECT * FROM t1; +ALTER TABLE t1 MODIFY COLUMN v VARCHAR(4); +SELECT * FROM t1; +DROP TABLE t1; + + +# +# Bug#31291 ALTER TABLE CONVERT TO CHARACTER SET does not change some data types +# +create table t1 (a tinytext character set latin1); +alter table t1 convert to character set utf8; +show create table t1; +drop table t1; +create table t1 (a mediumtext character set latin1); +alter table t1 convert to character set utf8; +show create table t1; +drop table t1; + +--echo End of 5.0 tests + +# +# Extended test coverage for ALTER TABLE behaviour under LOCK TABLES +# It should be consistent across all platforms and for all engines +# (Before 5.1 this was not true as behavior was different between +# Unix/Windows and transactional/non-transactional tables). +# See also innodb_mysql.test +# +--disable_warnings +drop table if exists t1, t2, t3; +--enable_warnings +create table t1 (i int); +create table t3 (j int); +insert into t1 values (); +insert into t3 values (); +# Table which is altered under LOCK TABLES it should stay in list of locked +# tables and be available after alter takes place unless ALTER contains RENAME +# clause. We should see the new definition of table, of course. +lock table t1 write, t3 read; +# Example of so-called 'fast' ALTER TABLE +alter table t1 modify i int default 1; +insert into t1 values (); +select * from t1; +# And now full-blown ALTER TABLE +alter table t1 change i c char(10) default "Two"; +insert into t1 values (); +select * from t1; +# If table is renamed then it should be removed from the list +# of locked tables. 'Fast' ALTER TABLE with RENAME clause: +alter table t1 modify c char(10) default "Three", rename to t2; +--error ER_TABLE_NOT_LOCKED +select * from t1; +--error ER_TABLE_NOT_LOCKED +select * from t2; +select * from t3; +unlock tables; +insert into t2 values (); +select * from t2; +lock table t2 write, t3 read; +# Full ALTER TABLE with RENAME +alter table t2 change c vc varchar(100) default "Four", rename to t1; +--error ER_TABLE_NOT_LOCKED +select * from t1; +--error ER_TABLE_NOT_LOCKED +select * from t2; +select * from t3; +unlock tables; +insert into t1 values (); +select * from t1; +drop tables t1, t3; + + +# +# Bug#18775 - Temporary table from alter table visible to other threads +# +# Check if special characters work and duplicates are detected. +--disable_warnings +DROP TABLE IF EXISTS `t+1`, `t+2`; +--enable_warnings +CREATE TABLE `t+1` (c1 INT); +ALTER TABLE `t+1` RENAME `t+2`; +CREATE TABLE `t+1` (c1 INT); +--error ER_TABLE_EXISTS_ERROR +ALTER TABLE `t+1` RENAME `t+2`; +DROP TABLE `t+1`, `t+2`; +# +# Same for temporary tables though these names do not become file names. +CREATE TEMPORARY TABLE `tt+1` (c1 INT); +ALTER TABLE `tt+1` RENAME `tt+2`; +CREATE TEMPORARY TABLE `tt+1` (c1 INT); +--error ER_TABLE_EXISTS_ERROR +ALTER TABLE `tt+1` RENAME `tt+2`; +SHOW CREATE TABLE `tt+1`; +SHOW CREATE TABLE `tt+2`; +DROP TABLE `tt+1`, `tt+2`; +# +# Check if special characters as in tmp_file_prefix work. +CREATE TABLE `#sql1` (c1 INT); +CREATE TABLE `@0023sql2` (c1 INT); +SHOW TABLES; +RENAME TABLE `#sql1` TO `@0023sql1`; +RENAME TABLE `@0023sql2` TO `#sql2`; +SHOW TABLES; +ALTER TABLE `@0023sql1` RENAME `#sql-1`; +ALTER TABLE `#sql2` RENAME `@0023sql-2`; +SHOW TABLES; +INSERT INTO `#sql-1` VALUES (1); +INSERT INTO `@0023sql-2` VALUES (2); +DROP TABLE `#sql-1`, `@0023sql-2`; +# +# Same for temporary tables though these names do not become file names. +CREATE TEMPORARY TABLE `#sql1` (c1 INT); +CREATE TEMPORARY TABLE `@0023sql2` (c1 INT); +SHOW TABLES; +ALTER TABLE `#sql1` RENAME `@0023sql1`; +ALTER TABLE `@0023sql2` RENAME `#sql2`; +SHOW TABLES; +INSERT INTO `#sql2` VALUES (1); +INSERT INTO `@0023sql1` VALUES (2); +SHOW CREATE TABLE `#sql2`; +SHOW CREATE TABLE `@0023sql1`; +DROP TABLE `#sql2`, `@0023sql1`; + +# +# Bug #22369: Alter table rename combined with other alterations causes lost tables +# +# This problem happens if the data change is compatible. +# Changing to the same type is compatible for example. +# +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +--enable_warnings +CREATE TABLE t1 ( + int_field INTEGER UNSIGNED NOT NULL, + char_field CHAR(10), + INDEX(`int_field`) +); + +DESCRIBE t1; + +SHOW INDEXES FROM t1; + +INSERT INTO t1 VALUES (1, "edno"), (1, "edno"), (2, "dve"), (3, "tri"), (5, "pet"); +--echo "Non-copy data change - new frm, but old data and index files" +ALTER TABLE t1 + CHANGE int_field unsigned_int_field INTEGER UNSIGNED NOT NULL, + RENAME t2; + +--error ER_NO_SUCH_TABLE +SELECT * FROM t1 ORDER BY int_field; +SELECT * FROM t2 ORDER BY unsigned_int_field; +DESCRIBE t2; +DESCRIBE t2; +ALTER TABLE t2 MODIFY unsigned_int_field BIGINT UNSIGNED NOT NULL; +DESCRIBE t2; + +DROP TABLE t2; + +# +# Bug#28427: Columns were renamed instead of moving by ALTER TABLE. +# +CREATE TABLE t1 (f1 INT, f2 INT, f3 INT); +INSERT INTO t1 VALUES (1, 2, NULL); +SELECT * FROM t1; +ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f1; +SELECT * FROM t1; +ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f2; +SELECT * FROM t1; +DROP TABLE t1; + +# +# BUG#29957 - alter_table.test fails +# +create table t1 (c char(10) default "Two"); +lock table t1 write; +insert into t1 values (); +alter table t1 modify c char(10) default "Three"; +unlock tables; +select * from t1; +check table t1; +drop table t1; + +# +# Bug#33873: Fast ALTER TABLE doesn't work with multibyte character sets +# + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings +CREATE TABLE t1 (id int, c int) character set latin1; +INSERT INTO t1 VALUES (1,1); +--enable_info +ALTER TABLE t1 CHANGE c d int; +ALTER TABLE t1 CHANGE d c int; +ALTER TABLE t1 MODIFY c VARCHAR(10); +ALTER TABLE t1 CHANGE c d varchar(10); +ALTER TABLE t1 CHANGE d c varchar(10); +--disable_info +DROP TABLE t1; + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings +CREATE TABLE t1 (id int, c int) character set utf8; +INSERT INTO t1 VALUES (1,1); +--enable_info +ALTER TABLE t1 CHANGE c d int; +ALTER TABLE t1 CHANGE d c int; +ALTER TABLE t1 MODIFY c VARCHAR(10); +ALTER TABLE t1 CHANGE c d varchar(10); +ALTER TABLE t1 CHANGE d c varchar(10); +--disable_info +DROP TABLE t1; + +# +# Bug#39372 "Smart" ALTER TABLE not so smart after all. +# +create table t1(f1 int not null, f2 int not null, key (f1), key (f2)); +let $count= 50; +--disable_query_log +begin; +while ($count) +{ + EVAL insert into t1 values (1,1),(1,1),(1,1),(1,1),(1,1); + EVAL insert into t1 values (2,2),(2,2),(2,2),(2,2),(2,2); + dec $count ; +} +commit; +--enable_query_log + +select index_length into @unpaked_keys_size from +information_schema.tables where table_name='t1'; +alter table t1 pack_keys=1; +select index_length into @paked_keys_size from +information_schema.tables where table_name='t1'; +select (@unpaked_keys_size > @paked_keys_size); + +select max_data_length into @orig_max_data_length from +information_schema.tables where table_name='t1'; +alter table t1 max_rows=100; +select max_data_length into @changed_max_data_length from +information_schema.tables where table_name='t1'; +select (@orig_max_data_length > @changed_max_data_length); + +drop table t1; + +# +# Bug #23113: Different behavior on altering ENUM fields between 5.0 and 5.1 +# +CREATE TABLE t1(a INT AUTO_INCREMENT PRIMARY KEY, + b ENUM('a', 'b', 'c') NOT NULL); +INSERT INTO t1 (b) VALUES ('a'), ('c'), ('b'), ('b'), ('a'); +ALTER TABLE t1 MODIFY b ENUM('a', 'z', 'b', 'c') NOT NULL; +SELECT * FROM t1; +DROP TABLE t1; + +# +# Test for ALTER column DROP DEFAULT +# + +SET @save_sql_mode=@@sql_mode; +SET sql_mode=strict_all_tables; + +CREATE TABLE t1 (a int NOT NULL default 42); +INSERT INTO t1 values (); +SELECT * FROM t1; +ALTER TABLE t1 ALTER COLUMN a DROP DEFAULT; +--error 1364 +INSERT INTO t1 values (); +INSERT INTO t1 (a) VALUES (11); +SELECT * FROM t1 ORDER BY a; +DROP TABLE t1; +SET @@sql_mode=@save_sql_mode; +--echo # +--echo # Bug#45567: Fast ALTER TABLE broken for enum and set +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (a ENUM('a1','a2')); +INSERT INTO t1 VALUES ('a1'),('a2'); +--enable_info +--echo # No copy: No modification +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2'); +--echo # No copy: Add new enumeration to the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a3'); +--echo # Copy: Modify and add new to the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','xx','a5'); +--echo # Copy: Remove from the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','xx'); +--echo # Copy: Add new enumeration +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a0','xx'); +--echo # No copy: Add new enumerations to the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a0','xx','a5','a6'); +--disable_info +DROP TABLE t1; + +CREATE TABLE t1 (a SET('a1','a2')); +INSERT INTO t1 VALUES ('a1'),('a2'); +--enable_info +--echo # No copy: No modification +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2'); +--echo # No copy: Add new to the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a3'); +--echo # Copy: Modify and add new to the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','xx','a5'); +--echo # Copy: Remove from the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','xx'); +--echo # Copy: Add new member +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx'); +--echo # No copy: Add new to the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx','a5','a6'); +--echo # Copy: Numerical incrase (pack lenght) +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx','a5','a6','a7','a8','a9','a10'); +--disable_info +DROP TABLE t1; + +# +# Bug#43508: Renaming timestamp or date column triggers table copy +# + +CREATE TABLE t1 (f1 TIMESTAMP NULL DEFAULT NULL, + f2 INT(11) DEFAULT NULL) ENGINE=MYISAM DEFAULT CHARSET=utf8; + +INSERT INTO t1 VALUES (NULL, NULL), ("2009-10-09 11:46:19", 2); + +--echo this should affect no rows as there is no real change +--enable_info +ALTER TABLE t1 CHANGE COLUMN f1 f1_no_real_change TIMESTAMP NULL DEFAULT NULL; +--disable_info +DROP TABLE t1; + + +--echo # +--echo # Bug #31145: ALTER TABLE DROP COLUMN, ADD COLUMN crashes (linux) +--echo # or freezes (win) the server +--echo # + +CREATE TABLE t1 (a TEXT, id INT, b INT); +ALTER TABLE t1 DROP COLUMN a, ADD COLUMN c TEXT FIRST; + +DROP TABLE t1; + + +--echo # +--echo # Test for bug #12652385 - "61493: REORDERING COLUMNS TO POSITION +--echo # FIRST CAN CAUSE DATA TO BE CORRUPTED". +--echo # +--disable_warnings +drop table if exists t1; +--enable_warnings +--echo # Use MyISAM engine as the fact that InnoDB doesn't support +--echo # in-place ALTER TABLE in cases when columns are being renamed +--echo # hides some bugs. +create table t1 (i int, j int) engine=myisam; +insert into t1 value (1, 2); +--echo # First, test for original problem described in the bug report. +select * from t1; +--echo # Change of column order by the below ALTER TABLE statement should +--echo # affect both column names and column contents. +alter table t1 modify column j int first; +select * from t1; +--echo # Now test for similar problem with the same root. +--echo # The below ALTER TABLE should change not only the name but +--echo # also the value for the last column of the table. +alter table t1 drop column i, add column k int default 0; +select * from t1; +--echo # Clean-up. +drop table t1; + + +--echo End of 5.1 tests + +# +# Bug #31031 ALTER TABLE regression in 5.0 +# +# The ALTER TABLE operation failed with +# ERROR 1089 (HY000): Incorrect sub part key; ... +# +CREATE TABLE t1(c CHAR(10), + i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY); +INSERT INTO t1 VALUES('a',2),('b',4),('c',6); +ALTER TABLE t1 + DROP i, + ADD i INT UNSIGNED NOT NULL AUTO_INCREMENT, + AUTO_INCREMENT = 1; +DROP TABLE t1; + + +# +# Bug#50542 5.5.x doesn't check length of key prefixes: +# corruption and crash results +# +# This case is related to Bug#31031 (above) +# A statement where the index key is larger/wider than +# the column type, should cause an error +# +--error ER_WRONG_SUB_KEY +CREATE TABLE t1 (a CHAR(1), PRIMARY KEY (a(255))); + +# Test other variants of creating indices +CREATE TABLE t1 (a CHAR(1)); +# ALTER TABLE +--error ER_WRONG_SUB_KEY +ALTER TABLE t1 ADD PRIMARY KEY (a(20)); +--error ER_WRONG_SUB_KEY +ALTER TABLE t1 ADD KEY (a(20)); +# CREATE INDEX +--error ER_WRONG_SUB_KEY +CREATE UNIQUE INDEX i1 ON t1 (a(20)); +--error ER_WRONG_SUB_KEY +CREATE INDEX i2 ON t1 (a(20)); +# cleanup +DROP TABLE t1; + + +# +# Bug #45052 ALTER TABLE ADD COLUMN crashes server with multiple foreign key columns +# The alter table fails if 2 or more new fields added and +# also added a key with these fields +# +CREATE TABLE t1 (id int); +INSERT INTO t1 VALUES (1), (2); +ALTER TABLE t1 ADD COLUMN (f1 INT), ADD COLUMN (f2 INT), ADD KEY f2k(f2); +DROP TABLE t1; + + +--echo # +--echo # Test for bug #53820 "ALTER a MEDIUMINT column table causes full +--echo # table copy". +--echo # +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings +CREATE TABLE t1 (a INT, b MEDIUMINT); +INSERT INTO t1 VALUES (1, 1), (2, 2); +--echo # The below ALTER should not copy table and so no rows should +--echo # be shown as affected. +--enable_info +ALTER TABLE t1 CHANGE a id INT; +--disable_info +DROP TABLE t1; + + +--echo # +--echo # Bug#11754461 CANNOT ALTER TABLE WHEN KEY PREFIX TOO LONG +--echo # + +--disable_warnings +DROP DATABASE IF EXISTS db1; +--enable_warnings + +CREATE DATABASE db1 CHARACTER SET utf8; +CREATE TABLE db1.t1 (bar TINYTEXT, KEY (bar(100))); +ALTER TABLE db1.t1 ADD baz INT; + +DROP DATABASE db1; + + +--echo # Additional coverage for refactoring which is made as part +--echo # of fix for bug #27480 "Extend CREATE TEMPORARY TABLES privilege +--echo # to allow temp table operations". +--echo # +--echo # At some point the below test case failed on assertion. + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TEMPORARY TABLE t1 (i int) ENGINE=MyISAM; + +--error ER_ILLEGAL_HA +ALTER TABLE t1 DISCARD TABLESPACE; + +DROP TABLE t1; + + +--echo # +--echo # Bug#11938039 RE-EXECUTION OF FRM-ONLY ALTER TABLE WITH RENAME +--echo # CLAUSE FAILS OR ABORTS SERVER. +--echo # +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (a int); +prepare stmt1 from 'alter table t1 alter column a set default 1, rename to t2'; +execute stmt1; +rename table t2 to t1; +--echo # The below statement should succeed and not emit error or abort server. +execute stmt1; +deallocate prepare stmt1; +drop table t2; + +--echo # +--echo # MDEV-8960 Can't refer the same column twice in one ALTER TABLE +--echo # + +CREATE TABLE t1 ( + `a` int(11) DEFAULT NULL +) DEFAULT CHARSET=utf8; + +ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL, +ALTER COLUMN `consultant_id` DROP DEFAULT; + +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + `a` int(11) DEFAULT NULL +) DEFAULT CHARSET=utf8; + +ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL, +ALTER COLUMN `consultant_id` SET DEFAULT 2; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + `a` int(11) DEFAULT NULL +) DEFAULT CHARSET=utf8; + +ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL DEFAULT 2, +ALTER COLUMN `consultant_id` DROP DEFAULT; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + `a` int(11) DEFAULT NULL +) DEFAULT CHARSET=utf8; + +ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL DEFAULT 2, +ALTER COLUMN `consultant_id` DROP DEFAULT, +MODIFY COLUMN `consultant_id` BIGINT; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +--echo # +--echo # BUG#27788685: NO WARNING WHEN TRUNCATING A STRING WITH DATA LOSS +--echo # + +SET GLOBAL max_allowed_packet=17825792; + +--connect(con1, localhost, root,,) +CREATE TABLE t1 (t1_fld1 TEXT); +CREATE TABLE t2 (t2_fld1 MEDIUMTEXT); +CREATE TABLE t3 (t3_fld1 LONGTEXT); + +INSERT INTO t1 VALUES (REPEAT('a',300)); +INSERT INTO t2 VALUES (REPEAT('b',65680)); +INSERT INTO t3 VALUES (REPEAT('c',16777300)); + +SELECT LENGTH(t1_fld1) FROM t1; +SELECT LENGTH(t2_fld1) FROM t2; +SELECT LENGTH(t3_fld1) FROM t3; + +--echo # With strict mode +SET SQL_MODE='STRICT_ALL_TABLES'; + +--error ER_DATA_TOO_LONG +ALTER TABLE t1 CHANGE `t1_fld1` `my_t1_fld1` TINYTEXT; +--error ER_DATA_TOO_LONG +ALTER TABLE t2 CHANGE `t2_fld1` `my_t2_fld1` TEXT; +--error ER_DATA_TOO_LONG +ALTER TABLE t3 CHANGE `t3_fld1` `my_t3_fld1` MEDIUMTEXT; + +--echo # With non-strict mode +SET SQL_MODE=''; + +ALTER TABLE t1 CHANGE `t1_fld1` `my_t1_fld1` TINYTEXT; +ALTER TABLE t2 CHANGE `t2_fld1` `my_t2_fld1` TEXT; +ALTER TABLE t3 CHANGE `t3_fld1` `my_t3_fld1` MEDIUMTEXT; + +SELECT LENGTH(my_t1_fld1) FROM t1; +SELECT LENGTH(my_t2_fld1) FROM t2; +SELECT LENGTH(my_t3_fld1) FROM t3; + +# Cleanup +--disconnect con1 +--source include/wait_until_disconnected.inc + +--connection default +DROP TABLE t1, t2, t3; + +SET SQL_MODE=default; +SET GLOBAL max_allowed_packet=default; + +# +# Test of ALTER TABLE IF [NOT] EXISTS +# + +CREATE TABLE t1 ( + id INT(11) NOT NULL, + x_param INT(11) DEFAULT NULL, + PRIMARY KEY (id) +) ENGINE=MYISAM; + +ALTER TABLE t1 ADD COLUMN IF NOT EXISTS id INT, + ADD COLUMN IF NOT EXISTS lol INT AFTER id; +ALTER TABLE t1 ADD COLUMN IF NOT EXISTS lol INT AFTER id; +ALTER TABLE t1 DROP COLUMN IF EXISTS lol; +ALTER TABLE t1 DROP COLUMN IF EXISTS lol; + +ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param); +ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param); +ALTER TABLE t1 MODIFY IF EXISTS lol INT; + +DROP INDEX IF EXISTS x_param ON t1; +DROP INDEX IF EXISTS x_param ON t1; +CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param); +CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + id INT(11) NOT NULL, + x_param INT(11) DEFAULT NULL, + PRIMARY KEY (id) +) ENGINE=INNODB; + +CREATE TABLE t2 ( + id INT(11) NOT NULL) ENGINE=INNODB; + +ALTER TABLE t1 ADD COLUMN IF NOT EXISTS id INT, + ADD COLUMN IF NOT EXISTS lol INT AFTER id; +ALTER TABLE t1 ADD COLUMN IF NOT EXISTS lol INT AFTER id; +ALTER TABLE t1 DROP COLUMN IF EXISTS lol; +ALTER TABLE t1 DROP COLUMN IF EXISTS lol; + +ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param); +ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param); +ALTER TABLE t1 MODIFY IF EXISTS lol INT; + +DROP INDEX IF EXISTS x_param ON t1; +DROP INDEX IF EXISTS x_param ON t1; +CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param); +CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param); +SHOW CREATE TABLE t1; + +ALTER TABLE t2 ADD FOREIGN KEY IF NOT EXISTS fk(id) REFERENCES t1(id); +ALTER TABLE t2 ADD FOREIGN KEY IF NOT EXISTS fk(id) REFERENCES t1(id); +ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS fk; +ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS fk; +SHOW CREATE TABLE t2; +ALTER TABLE t2 ADD FOREIGN KEY (id) REFERENCES t1(id); +ALTER TABLE t2 ADD FOREIGN KEY IF NOT EXISTS t2_ibfk_1(id) REFERENCES t1(id); +ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS t2_ibfk_1; +ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS t2_ibfk_1; +SHOW CREATE TABLE t2; + +DROP TABLE t2; +CREATE TABLE t2 ( + id INT(11) NOT NULL); +ALTER TABLE t2 ADD COLUMN a INT, ADD COLUMN IF NOT EXISTS a INT; +ALTER TABLE t2 ADD KEY k_id(id), ADD KEY IF NOT EXISTS k_id(id); +SHOW CREATE TABLE t2; +ALTER TABLE t2 DROP KEY k_id, DROP KEY IF EXISTS k_id; +ALTER TABLE t2 DROP COLUMN a, DROP COLUMN IF EXISTS a; +SHOW CREATE TABLE t2; + +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 ( + `transaction_id` int(11) NOT NULL DEFAULT '0', + KEY `transaction_id` (`transaction_id`)); +ALTER TABLE t1 DROP KEY IF EXISTS transaction_id, ADD PRIMARY KEY IF NOT EXISTS (transaction_id); +SHOW CREATE TABLE t1; + +DROP TABLE t1; + +--echo # Bug#11748057 (formerly known as 34972): ALTER TABLE statement doesn't +--echo # identify correct column name. +--echo # + +CREATE TABLE t1 (c1 int unsigned , c2 char(100) not null default ''); +ALTER TABLE t1 ADD c3 char(16) NOT NULL DEFAULT '' AFTER c2, + MODIFY c2 char(100) NOT NULL DEFAULT '' AFTER c1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +--echo # +--echo # WL#5534 Online ALTER, Phase 1 +--echo # + +--echo # Single thread tests. +--echo # See innodb_mysql_sync.test for multi thread tests. + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1(a INT PRIMARY KEY, b INT) engine=InnoDB; +CREATE TABLE m1(a INT PRIMARY KEY, b INT) engine=MyISAM; +INSERT INTO t1 VALUES (1,1), (2,2); +INSERT INTO m1 VALUES (1,1), (2,2); + +--echo # +--echo # 1: Test ALGORITHM keyword +--echo # + +--echo # --enable_info allows us to see how many rows were updated +--echo # by ALTER TABLE. in-place will show 0 rows, while copy > 0. + +--enable_info +ALTER TABLE t1 ADD INDEX i1(b); +ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= DEFAULT; +ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= COPY; +ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= INPLACE; +--error ER_UNKNOWN_ALTER_ALGORITHM +ALTER TABLE t1 ADD INDEX i5(b), ALGORITHM= INVALID; + +ALTER TABLE m1 ENABLE KEYS; +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= DEFAULT; +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY; +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE; +--disable_info + +ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4; + +--echo # +--echo # 2: Test ALGORITHM + old_alter_table +--echo # + +--enable_info +SET SESSION old_alter_table= 1; +ALTER TABLE t1 ADD INDEX i1(b); +ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= DEFAULT; +ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= COPY; +ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= INPLACE; +SET SESSION old_alter_table= 0; +--disable_info + +ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4; + +--echo # +--echo # 3: Test unsupported in-place operation +--echo # + +ALTER TABLE t1 ADD COLUMN (c1 INT); +ALTER TABLE t1 ADD COLUMN (c2 INT), ALGORITHM= DEFAULT; +ALTER TABLE t1 ADD COLUMN (c3 INT), ALGORITHM= COPY; +ALTER TABLE t1 ADD COLUMN (c4 INT), ALGORITHM= INPLACE; + +ALTER TABLE t1 DROP COLUMN c1, DROP COLUMN c2, DROP COLUMN c3, DROP COLUMN c4; + +--echo # +--echo # 4: Test LOCK keyword +--echo # + +--enable_info +ALTER TABLE t1 ADD INDEX i1(b), LOCK= DEFAULT; +ALTER TABLE t1 ADD INDEX i2(b), LOCK= NONE; +ALTER TABLE t1 ADD INDEX i3(b), LOCK= SHARED; +ALTER TABLE t1 ADD INDEX i4(b), LOCK= EXCLUSIVE; +--error ER_UNKNOWN_ALTER_LOCK +ALTER TABLE t1 ADD INDEX i5(b), LOCK= INVALID; +--disable_info + +ALTER TABLE m1 ENABLE KEYS, LOCK= DEFAULT; +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE m1 ENABLE KEYS, LOCK= NONE; +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE m1 ENABLE KEYS, LOCK= SHARED; +ALTER TABLE m1 ENABLE KEYS, LOCK= EXCLUSIVE; + +ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4; + +--echo # +--echo # 5: Test ALGORITHM + LOCK +--echo # + +--enable_info +ALTER TABLE t1 ADD INDEX i1(b), ALGORITHM= INPLACE, LOCK= NONE; +ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= INPLACE, LOCK= SHARED; +ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= INPLACE, LOCK= EXCLUSIVE; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= COPY, LOCK= NONE; +ALTER TABLE t1 ADD INDEX i5(b), ALGORITHM= COPY, LOCK= SHARED; +ALTER TABLE t1 ADD INDEX i6(b), ALGORITHM= COPY, LOCK= EXCLUSIVE; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= NONE; +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= SHARED; +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= EXCLUSIVE; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= NONE; +# This works because the lock will be SNW for the copy phase. +# It will still require exclusive lock for actually enabling keys. +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= SHARED; +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= EXCLUSIVE; +--disable_info + +DROP TABLE t1, m1; + +--echo # +--echo # 6: Possible deadlock involving thr_lock.c +--echo # + +CREATE TABLE t1(a INT PRIMARY KEY, b INT); +INSERT INTO t1 VALUES (1,1), (2,2); + +START TRANSACTION; +INSERT INTO t1 VALUES (3,3); + +--echo # Connection con1 +connect (con1, localhost, root); +--echo # Sending: +--send ALTER TABLE t1 DISABLE KEYS + +--echo # Connection default +connection default; +--echo # Waiting until ALTER TABLE is blocked. +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table metadata lock" AND + info = "ALTER TABLE t1 DISABLE KEYS"; +--source include/wait_condition.inc +UPDATE t1 SET b = 4; +COMMIT; + +--echo # Connection con1 +connection con1; +--echo # Reaping: ALTER TABLE t1 DISABLE KEYS +--reap +disconnect con1; +--source include/wait_until_disconnected.inc + +--echo # Connection default +connection default; +DROP TABLE t1; + +--echo # +--echo # 7: Which operations require copy and which can be done in-place? +--echo # +--echo # Test which ALTER TABLE operations are done in-place and +--echo # which operations are done using temporary table copy. +--echo # +--echo # --enable_info allows us to see how many rows were updated +--echo # by ALTER TABLE. in-place will show 0 rows, while copy > 0. +--echo # + +--disable_warnings +DROP TABLE IF EXISTS ti1, ti2, ti3, tm1, tm2, tm3; +--enable_warnings + +--echo # Single operation tests + +CREATE TABLE ti1(a INT NOT NULL, b INT, c INT) engine=InnoDB; +CREATE TABLE tm1(a INT NOT NULL, b INT, c INT) engine=MyISAM; +CREATE TABLE ti2(a INT PRIMARY KEY AUTO_INCREMENT, b INT, c INT) engine=InnoDB; +CREATE TABLE tm2(a INT PRIMARY KEY AUTO_INCREMENT, b INT, c INT) engine=MyISAM; +INSERT INTO ti1 VALUES (1,1,1), (2,2,2); +INSERT INTO ti2 VALUES (1,1,1), (2,2,2); +INSERT INTO tm1 VALUES (1,1,1), (2,2,2); +INSERT INTO tm2 VALUES (1,1,1), (2,2,2); + +--enable_info +ALTER TABLE ti1; +ALTER TABLE tm1; + +ALTER TABLE ti1 ADD COLUMN d VARCHAR(200); +ALTER TABLE tm1 ADD COLUMN d VARCHAR(200); +ALTER TABLE ti1 ADD COLUMN d2 VARCHAR(200); +ALTER TABLE tm1 ADD COLUMN d2 VARCHAR(200); +ALTER TABLE ti1 ADD COLUMN e ENUM('a', 'b') FIRST; +ALTER TABLE tm1 ADD COLUMN e ENUM('a', 'b') FIRST; +ALTER TABLE ti1 ADD COLUMN f INT AFTER a; +ALTER TABLE tm1 ADD COLUMN f INT AFTER a; + +ALTER TABLE ti1 ADD INDEX ii1(b); +ALTER TABLE tm1 ADD INDEX im1(b); +ALTER TABLE ti1 ADD UNIQUE INDEX ii2 (c); +ALTER TABLE tm1 ADD UNIQUE INDEX im2 (c); +ALTER TABLE ti1 ADD FULLTEXT INDEX ii3 (d); +ALTER TABLE tm1 ADD FULLTEXT INDEX im3 (d); +ALTER TABLE ti1 ADD FULLTEXT INDEX ii4 (d2); +ALTER TABLE tm1 ADD FULLTEXT INDEX im4 (d2); + +# Bug#14140038 INCONSISTENT HANDLING OF FULLTEXT INDEXES IN ALTER TABLE +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE ti1 ADD PRIMARY KEY(a), ALGORITHM=INPLACE; +ALTER TABLE ti1 ADD PRIMARY KEY(a); +ALTER TABLE tm1 ADD PRIMARY KEY(a); + +ALTER TABLE ti1 DROP INDEX ii3; +ALTER TABLE tm1 DROP INDEX im3; + +ALTER TABLE ti1 DROP COLUMN d2; +ALTER TABLE tm1 DROP COLUMN d2; + +ALTER TABLE ti1 ADD CONSTRAINT fi1 FOREIGN KEY (b) REFERENCES ti2(a); +ALTER TABLE tm1 ADD CONSTRAINT fm1 FOREIGN KEY (b) REFERENCES tm2(a); + +ALTER TABLE ti1 ALTER COLUMN b SET DEFAULT 1; +ALTER TABLE tm1 ALTER COLUMN b SET DEFAULT 1; +ALTER TABLE ti1 ALTER COLUMN b DROP DEFAULT; +ALTER TABLE tm1 ALTER COLUMN b DROP DEFAULT; + +# This will set both ALTER_COLUMN_NAME and COLUMN_DEFAULT_VALUE +ALTER TABLE ti1 CHANGE COLUMN f g INT; +ALTER TABLE tm1 CHANGE COLUMN f g INT; +ALTER TABLE ti1 CHANGE COLUMN g h VARCHAR(20); +ALTER TABLE tm1 CHANGE COLUMN g h VARCHAR(20); +ALTER TABLE ti1 MODIFY COLUMN e ENUM('a', 'b', 'c'); +ALTER TABLE tm1 MODIFY COLUMN e ENUM('a', 'b', 'c'); +ALTER TABLE ti1 MODIFY COLUMN e INT; +ALTER TABLE tm1 MODIFY COLUMN e INT; +# This will set both ALTER_COLUMN_ORDER and COLUMN_DEFAULT_VALUE +ALTER TABLE ti1 MODIFY COLUMN e INT AFTER h; +ALTER TABLE tm1 MODIFY COLUMN e INT AFTER h; +ALTER TABLE ti1 MODIFY COLUMN e INT FIRST; +ALTER TABLE tm1 MODIFY COLUMN e INT FIRST; +# This will set both ALTER_COLUMN_NOT_NULLABLE and COLUMN_DEFAULT_VALUE +--disable_info +# NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on. +SET @orig_sql_mode = @@sql_mode; +SET @@sql_mode = 'STRICT_TRANS_TABLES'; +--enable_info +ALTER TABLE ti1 MODIFY COLUMN c INT NOT NULL; +--disable_info +SET @@sql_mode = @orig_sql_mode; +--enable_info +ALTER TABLE tm1 MODIFY COLUMN c INT NOT NULL; +# This will set both ALTER_COLUMN_NULLABLE and COLUMN_DEFAULT_VALUE +ALTER TABLE ti1 MODIFY COLUMN c INT NULL; +ALTER TABLE tm1 MODIFY COLUMN c INT NULL; +# This will set both ALTER_COLUMN_EQUAL_PACK_LENGTH and COLUMN_DEFAULT_VALUE +ALTER TABLE ti1 MODIFY COLUMN h VARCHAR(30); +ALTER TABLE tm1 MODIFY COLUMN h VARCHAR(30); +ALTER TABLE ti1 MODIFY COLUMN h VARCHAR(30) AFTER d; +ALTER TABLE tm1 MODIFY COLUMN h VARCHAR(30) AFTER d; + +ALTER TABLE ti1 DROP COLUMN h; +ALTER TABLE tm1 DROP COLUMN h; + +ALTER TABLE ti1 DROP INDEX ii2; +ALTER TABLE tm1 DROP INDEX im2; +ALTER TABLE ti1 DROP PRIMARY KEY; +ALTER TABLE tm1 DROP PRIMARY KEY; + +ALTER TABLE ti1 DROP FOREIGN KEY fi1; +ALTER TABLE tm1 DROP FOREIGN KEY fm1; + +ALTER TABLE ti1 RENAME TO ti3; +ALTER TABLE tm1 RENAME TO tm3; +ALTER TABLE ti3 RENAME TO ti1; +ALTER TABLE tm3 RENAME TO tm1; + +ALTER TABLE ti1 ORDER BY b; +ALTER TABLE tm1 ORDER BY b; + +ALTER TABLE ti1 CONVERT TO CHARACTER SET utf16; +ALTER TABLE tm1 CONVERT TO CHARACTER SET utf16; +ALTER TABLE ti1 DEFAULT CHARACTER SET utf8; +ALTER TABLE tm1 DEFAULT CHARACTER SET utf8; + +ALTER TABLE ti1 FORCE; +ALTER TABLE tm1 FORCE; + +ALTER TABLE ti1 AUTO_INCREMENT 3; +ALTER TABLE tm1 AUTO_INCREMENT 3; +ALTER TABLE ti1 AVG_ROW_LENGTH 10; +ALTER TABLE tm1 AVG_ROW_LENGTH 10; +ALTER TABLE ti1 CHECKSUM 1; +ALTER TABLE tm1 CHECKSUM 1; +ALTER TABLE ti1 COMMENT 'test'; +ALTER TABLE tm1 COMMENT 'test'; +ALTER TABLE ti1 MAX_ROWS 100; +ALTER TABLE tm1 MAX_ROWS 100; +ALTER TABLE ti1 MIN_ROWS 1; +ALTER TABLE tm1 MIN_ROWS 1; +ALTER TABLE ti1 PACK_KEYS 1; +ALTER TABLE tm1 PACK_KEYS 1; + +--disable_info +DROP TABLE ti1, ti2, tm1, tm2; + +--echo # Tests of >1 operation (InnoDB) + +CREATE TABLE ti1(a INT PRIMARY KEY AUTO_INCREMENT, b INT) engine=InnoDB; +INSERT INTO ti1(b) VALUES (1), (2); + +--enable_info +ALTER TABLE ti1 RENAME TO ti3, ADD INDEX ii1(b); + +ALTER TABLE ti3 DROP INDEX ii1, AUTO_INCREMENT 5; +--disable_info +INSERT INTO ti3(b) VALUES (5); +--enable_info +ALTER TABLE ti3 ADD INDEX ii1(b), AUTO_INCREMENT 7; +--disable_info +INSERT INTO ti3(b) VALUES (7); +SELECT * FROM ti3; + +DROP TABLE ti3; + +--echo # +--echo # 8: Scenario in which ALTER TABLE was returning an unwarranted +--echo # ER_ILLEGAL_HA error at some point during work on this WL. +--echo # + +CREATE TABLE tm1(i INT DEFAULT 1) engine=MyISAM; +ALTER TABLE tm1 ADD INDEX ii1(i), ALTER COLUMN i DROP DEFAULT; +DROP TABLE tm1; + +# +# MDEV-4435 Server crashes in my_strcasecmp_utf8 on ADD KEY IF NOT EXISTS with implicit name when the key exists. +# +create table if not exists t1 (i int); +alter table t1 add key (i); +alter table t1 add key if not exists (i); +DROP TABLE t1; + +# +# MDEV-4436 CHANGE COLUMN IF EXISTS does not work and throws wrong warning. +# +create table t1 (a int); +alter table t1 change column if exists a b bigint; +show create table t1; +DROP TABLE t1; + +# +# MDEV-4437 ALTER TABLE .. ADD UNIQUE INDEX IF NOT EXISTS causes syntax error. +# + +create table t1 (i int); +alter table t1 add unique index if not exists idx(i); +alter table t1 add unique index if not exists idx(i); +show create table t1; +DROP TABLE t1; + +# +# MDEV-8358 ADD PRIMARY KEY IF NOT EXISTS -> ERROR 1068 (42000): Multiple primary key +# + +CREATE TABLE t1 ( + `event_id` bigint(20) unsigned NOT NULL DEFAULT '0', + `market_id` bigint(20) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (`event_id`,`market_id`) + ); +ALTER TABLE t1 ADD PRIMARY KEY IF NOT EXISTS event_id (event_id,market_id); +DROP TABLE t1; + +--echo # +--echo # MDEV-11126 Crash while altering persistent virtual column +--echo # + +CREATE TABLE `tab1` ( + `id` bigint(20) NOT NULL AUTO_INCREMENT, + `field2` set('option1','option2','option3','option4') NOT NULL, + `field3` set('option1','option2','option3','option4','option5') NOT NULL, + `field4` set('option1','option2','option3','option4') NOT NULL, + `field5` varchar(32) NOT NULL, + `field6` varchar(32) NOT NULL, + `field7` varchar(32) NOT NULL, + `field8` varchar(32) NOT NULL, + `field9` int(11) NOT NULL DEFAULT '1', + `field10` varchar(16) NOT NULL, + `field11` enum('option1','option2','option3') NOT NULL DEFAULT 'option1', + `v_col` varchar(128) AS (IF(field11='option1',CONCAT_WS(":","field1",field2,field3,field4,field5,field6,field7,field8,field9,field10), CONCAT_WS(":","field1",field11,field2,field3,field4,field5,field6,field7,field8,field9,field10))) PERSISTENT, + PRIMARY KEY (`id`) +) DEFAULT CHARSET=latin1; + +ALTER TABLE `tab1` CHANGE COLUMN v_col `v_col` varchar(128); +SHOW CREATE TABLE `tab1`; +ALTER TABLE `tab1` CHANGE COLUMN v_col `v_col` varchar(128) AS (IF(field11='option1',CONCAT_WS(":","field1",field2,field3,field4,field5,field6,field7,field8,field9,field10), CONCAT_WS(":","field1",field11,field2,field3,field4,field5,field6,field7,field8,field9,field10))) PERSISTENT; +SHOW CREATE TABLE `tab1`; +DROP TABLE `tab1`; + +--echo # +--echo # MDEV-11548 Reproducible server crash after the 2nd ALTER TABLE ADD FOREIGN KEY IF NOT EXISTS +--echo # + +CREATE TABLE t1 (id INT UNSIGNED NOT NULL PRIMARY KEY); +CREATE TABLE t2 (id1 INT UNSIGNED NOT NULL); + +ALTER TABLE t2 +ADD FOREIGN KEY IF NOT EXISTS (id1) + REFERENCES t1 (id); + +ALTER TABLE t2 +ADD FOREIGN KEY IF NOT EXISTS (id1) +REFERENCES t1 (id); + +DROP TABLE t2; +DROP TABLE t1; + + +--echo # +--echo # MDEV-6390 CONVERT TO CHARACTER SET utf8 doesn't change DEFAULT CHARSET. +--echo # + +CREATE TABLE t1 (id int(11) NOT NULL, a int(11) NOT NULL, b int(11)) + ENGINE=InnoDB DEFAULT CHARSET=latin1; +SHOW CREATE TABLE t1; +ALTER TABLE t1 CONVERT TO CHARACTER SET utf8; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +--echo # +--echo # +--echo # MDEV-15308 +--echo # Assertion `ha_alter_info->alter_info->drop_list.elements > 0' failed +--echo # in ha_innodb::prepare_inplace_alter_table +--echo # + +CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB; +ALTER TABLE t1 DROP FOREIGN KEY IF EXISTS fk, DROP COLUMN b; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB; +ALTER TABLE t1 DROP INDEX IF EXISTS fk, DROP COLUMN b; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INT, b INT, c INT, KEY(c)) ENGINE=InnoDB; +ALTER TABLE t1 DROP FOREIGN KEY IF EXISTS fk, DROP COLUMN c; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INT, b INT, c INT, KEY c1(c)) ENGINE=InnoDB; +ALTER TABLE t1 DROP FOREIGN KEY IF EXISTS fk, DROP INDEX c1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB; +ALTER TABLE t1 DROP INDEX IF EXISTS fk, DROP COLUMN IF EXISTS c; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-14668 ADD PRIMARY KEY IF NOT EXISTS on composite key +--echo # +CREATE TABLE t1 ( + `ID` BIGINT(20) NOT NULL, + `RANK` MEDIUMINT(4) NOT NULL, + `CHECK_POINT` BIGINT(20) NOT NULL, + UNIQUE INDEX `HORIZON_UIDX01` (`ID`, `RANK`) + ) ENGINE=InnoDB; + +ALTER TABLE t1 ADD PRIMARY KEY IF NOT EXISTS (`ID`, `CHECK_POINT`); +SHOW CREATE TABLE t1; +ALTER TABLE t1 ADD PRIMARY KEY IF NOT EXISTS (`ID`, `CHECK_POINT`); +DROP TABLE t1; diff --git a/mysql-test/r/derived_opt.result b/mysql-test/r/derived_opt.result index 04a76c2cbc8..63d2c432768 100644 --- a/mysql-test/r/derived_opt.result +++ b/mysql-test/r/derived_opt.result @@ -499,9 +499,42 @@ where D1.a= t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where -1 PRIMARY <derived2> hash_ALL key0 #hash#key0 5 test.t1.a 100 Using join buffer (flat, BNLH join) +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 10 2 DERIVED t2 ALL NULL NULL NULL NULL 100 Using filesort set join_cache_level=@tmp_jcl; set optimizer_switch=@tmp_os; drop table t1, t2; +# +# Bug mdev-17382: equi-join of derived table with join_cache_level=4 +# +CREATE TABLE t1 ( +id int NOT NULL, +amount decimal DEFAULT NULL, +PRIMARY KEY (id) +); +CREATE TABLE t2 ( +id int NOT NULL, +name varchar(50) DEFAULT NULL, +PRIMARY KEY (id) +); +INSERT INTO t1 VALUES +(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000), +(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000); +INSERT INTO t2 VALUES +(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL), +(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL); +set join_cache_level=4; +EXPLAIN +SELECT t2.id,t2.name,t.total_amt +FROM t2 +LEFT JOIN +(SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t +ON t2.id=t.id +WHERE t2.id < 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 3 Using index condition +1 PRIMARY <derived2> ref key0 key0 5 test.t2.id 2 +2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort +set join_cache_level=default; +DROP TABLE t1,t2; set optimizer_switch=@exit_optimizer_switch; diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 8346f7afb40..b978cef8cbc 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -1111,7 +1111,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) +Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(/*always not null*/ 1))) and trigcond(((<cache>(5) = 5) or isnull(/*always not null*/ 1))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) SELECT t.a,t.b FROM t3 RIGHT JOIN ((SELECT * FROM t1) AS t, t2) ON t2.b != 0 WHERE (t.a,t.b) NOT IN (SELECT 7, 5); a b @@ -1125,7 +1125,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) +Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(/*always not null*/ 1))) and trigcond(((<cache>(5) = 5) or isnull(/*always not null*/ 1))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) SELECT t.a,t.b FROM t3 RIGHT JOIN (v1 AS t, t2) ON t2.b != 0 WHERE (t.a,t.b) NOT IN (SELECT 7, 5); a b @@ -1139,7 +1139,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) +Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(/*always not null*/ 1))) and trigcond(((<cache>(5) = 5) or isnull(/*always not null*/ 1))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) DROP VIEW v1; DROP TABLE t1,t2,t3; # diff --git a/mysql-test/r/func_isnull.result b/mysql-test/r/func_isnull.result index 88c5bfd5468..a97d4a67939 100644 --- a/mysql-test/r/func_isnull.result +++ b/mysql-test/r/func_isnull.result @@ -106,5 +106,25 @@ Note 1003 select `test`.`t2`.`d1` AS `d1`,`test`.`t1`.`d1` AS `d1` from `test`.` DROP VIEW v1; DROP TABLE t1,t2; # +# MDEV-15475: Assertion `!table || (!table->read_set || +# bitmap_is_set(table->read_set, field_index))' +# failed on EXPLAIN EXTENDED with constant table and view +# +CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=MyISAM; +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1); +EXPLAIN EXTENDED SELECT ISNULL(pk) FROM v1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 +Warnings: +Note 1003 select isnull(/*always not null*/ 1) AS `ISNULL(pk)` from dual +EXPLAIN EXTENDED SELECT IFNULL(pk,0) FROM v1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 +Warnings: +Note 1003 select ifnull(1,0) AS `IFNULL(pk,0)` from dual +DROP VIEW v1; +DROP TABLE t1; +# # End of 5.5 tests # diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 7316724a16f..d4b289f1d80 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -2770,6 +2770,167 @@ SEC_TO_TIME(MAKEDATE(0,RAND(~0))) 838:59:59 Warnings: Warning 1292 Truncated incorrect time value: '20000101' +SELECT PERIOD_DIFF(2018, AES_ENCRYPT('Rae Bareli', 'Rae Bareli')); +PERIOD_DIFF(2018, AES_ENCRYPT('Rae Bareli', 'Rae Bareli')) +24257 +# +# MDEV-17249 MAKETIME(-1e50,0,0) returns a wrong result +# +SELECT +MAKETIME(1e10,0,0), +MAKETIME(-1e10,0,0), +MAKETIME(1e50,0,0), +MAKETIME(-1e50,0,0), +MAKETIME(COALESCE(1e50),0,0), +MAKETIME(COALESCE(-1e50),0,0); +MAKETIME(1e10,0,0) 838:59:59 +MAKETIME(-1e10,0,0) -838:59:59 +MAKETIME(1e50,0,0) 838:59:59 +MAKETIME(-1e50,0,0) -838:59:59 +MAKETIME(COALESCE(1e50),0,0) 838:59:59 +MAKETIME(COALESCE(-1e50),0,0) -838:59:59 +Warnings: +Level Warning +Code 1292 +Message Truncated incorrect time value: '10000000000:00:00' +Level Warning +Code 1292 +Message Truncated incorrect time value: '-10000000000:00:00' +Level Warning +Code 1292 +Message Truncated incorrect time value: '9223372036854775807:00:00' +Level Warning +Code 1292 +Message Truncated incorrect time value: '-9223372036854775808:00:00' +Level Warning +Code 1292 +Message Truncated incorrect time value: '9223372036854775807:00:00' +Level Warning +Code 1292 +Message Truncated incorrect time value: '-9223372036854775808:00:00' +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES (1e30),(-1e30); +SELECT MAKETIME(a,0,0) FROM t1; +MAKETIME(a,0,0) +838:59:59 +-838:59:59 +Warnings: +Warning 1292 Truncated incorrect time value: '9223372036854775807:00:00' +Warning 1292 Truncated incorrect time value: '-9223372036854775808:00:00' +DROP TABLE t1; +# +# MDEV-17244 MAKETIME(900,0,0.111) returns a wrong result +# +SELECT MAKETIME(900,0,0); +MAKETIME(900,0,0) +838:59:59 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.1); +MAKETIME(900,0,0.1) +838:59:59.9 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.11); +MAKETIME(900,0,0.11) +838:59:59.99 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.111); +MAKETIME(900,0,0.111) +838:59:59.999 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.1111); +MAKETIME(900,0,0.1111) +838:59:59.9999 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.11111); +MAKETIME(900,0,0.11111) +838:59:59.99999 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.111111); +MAKETIME(900,0,0.111111) +838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.1111111); +MAKETIME(900,0,0.1111111) +838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.11111111); +MAKETIME(900,0,0.11111111) +838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.111111111); +MAKETIME(900,0,0.111111111) +838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,EXP(1)); +MAKETIME(900,0,EXP(1)) +838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:02' +SELECT MAKETIME(-900,0,0); +MAKETIME(-900,0,0) +-838:59:59 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.1); +MAKETIME(-900,0,0.1) +-838:59:59.9 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.11); +MAKETIME(-900,0,0.11) +-838:59:59.99 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.111); +MAKETIME(-900,0,0.111) +-838:59:59.999 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.1111); +MAKETIME(-900,0,0.1111) +-838:59:59.9999 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.11111); +MAKETIME(-900,0,0.11111) +-838:59:59.99999 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.111111); +MAKETIME(-900,0,0.111111) +-838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.1111111); +MAKETIME(-900,0,0.1111111) +-838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.11111111); +MAKETIME(-900,0,0.11111111) +-838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.111111111); +MAKETIME(-900,0,0.111111111) +-838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,EXP(1)); +MAKETIME(-900,0,EXP(1)) +-838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:02' # # End of 5.5 tests # diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index c3a1db86c37..cfd442d58c8 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -2569,6 +2569,35 @@ foo@localhost foo@127.0.0.1 # Clean-up DROP USER foo@'127.0.0.1'; # End of Bug#12766319 +create user foo@localhost; +create database foodb; +grant create routine on foodb.* to foo@localhost; +create procedure fooproc() select 'i am fooproc'; +show grants; +Grants for foo@localhost +GRANT USAGE ON *.* TO 'foo'@'localhost' +GRANT CREATE ROUTINE ON `foodb`.* TO 'foo'@'localhost' +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`fooproc` TO 'foo'@'localhost' +rename table mysql.procs_priv to mysql.procs_priv1; +flush privileges; +show grants for foo@localhost; +Grants for foo@localhost +GRANT USAGE ON *.* TO 'foo'@'localhost' +GRANT CREATE ROUTINE ON `foodb`.* TO 'foo'@'localhost' +rename table mysql.procs_priv1 to mysql.procs_priv; +show grants for foo@localhost; +Grants for foo@localhost +GRANT USAGE ON *.* TO 'foo'@'localhost' +GRANT CREATE ROUTINE ON `foodb`.* TO 'foo'@'localhost' +flush privileges; +show grants for foo@localhost; +Grants for foo@localhost +GRANT USAGE ON *.* TO 'foo'@'localhost' +GRANT CREATE ROUTINE ON `foodb`.* TO 'foo'@'localhost' +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`fooproc` TO 'foo'@'localhost' +drop user foo@localhost; +drop procedure fooproc; +drop database foodb; # # Bug#11756966 - 48958: STORED PROCEDURES CAN BE LEVERAGED TO BYPASS # DATABASE SECURITY diff --git a/mysql-test/r/grant.test b/mysql-test/r/grant.test new file mode 100644 index 00000000000..f2dfb01cc39 --- /dev/null +++ b/mysql-test/r/grant.test @@ -0,0 +1,2291 @@ +# Test of GRANT commands + +# Grant tests not performed with embedded server +-- source include/not_embedded.inc + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + +SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators; +SET GLOBAL log_bin_trust_function_creators = 1; + +# Cleanup +--disable_warnings +drop table if exists t1; +drop database if exists mysqltest; +--enable_warnings + +connect (master,localhost,root,,); +connection master; +SET NAMES binary; + +# +# Test that SSL options works properly +# + +delete from mysql.user where user='mysqltest_1'; +delete from mysql.db where user='mysqltest_1'; +flush privileges; +grant select on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA"; +show grants for mysqltest_1@localhost; +grant delete on mysqltest.* to mysqltest_1@localhost; +query_vertical select * from mysql.user where user="mysqltest_1"; +show grants for mysqltest_1@localhost; +revoke delete on mysqltest.* from mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +grant select on mysqltest.* to mysqltest_1@localhost require NONE; +show grants for mysqltest_1@localhost; +grant USAGE on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA" AND SUBJECT "testsubject" ISSUER "Monty Program Ab"; +show grants for mysqltest_1@localhost; +revoke all privileges on mysqltest.* from mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +delete from mysql.user where user='mysqltest_1'; +flush privileges; + +# +# Test of GRANTS specifying user limits +# +delete from mysql.user where user='mysqltest_1'; +flush privileges; +grant usage on *.* to mysqltest_1@localhost with max_queries_per_hour 10; +query_vertical select * from mysql.user where user="mysqltest_1"; +show grants for mysqltest_1@localhost; +grant usage on *.* to mysqltest_1@localhost with max_updates_per_hour 20 max_connections_per_hour 30; +query_vertical select * from mysql.user where user="mysqltest_1"; +show grants for mysqltest_1@localhost; +# This is just to double check that one won't ignore results of selects +flush privileges; +show grants for mysqltest_1@localhost; +delete from mysql.user where user='mysqltest_1'; +flush privileges; + +# +# Test that the new db privileges are stored/retrieved correctly +# + +grant CREATE TEMPORARY TABLES, LOCK TABLES on mysqltest.* to mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +flush privileges; +show grants for mysqltest_1@localhost; +revoke CREATE TEMPORARY TABLES on mysqltest.* from mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +grant ALL PRIVILEGES on mysqltest.* to mysqltest_1@localhost with GRANT OPTION; +flush privileges; +show grants for mysqltest_1@localhost; +revoke LOCK TABLES, ALTER on mysqltest.* from mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +revoke all privileges on mysqltest.* from mysqltest_1@localhost; +delete from mysql.user where user='mysqltest_1'; +flush privileges; +grant usage on test.* to mysqltest_1@localhost with grant option; +show grants for mysqltest_1@localhost; +delete from mysql.user where user='mysqltest_1'; +delete from mysql.db where user='mysqltest_1'; +delete from mysql.tables_priv where user='mysqltest_1'; +delete from mysql.columns_priv where user='mysqltest_1'; +flush privileges; +--error ER_NONEXISTING_GRANT +show grants for mysqltest_1@localhost; + +# +# Test what happens when you have same table and colum level grants +# + +create table t1 (a int); +GRANT select,update,insert on t1 to mysqltest_1@localhost; +GRANT select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1"; +REVOKE select (a), update on t1 from mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +REVOKE select,update,insert,insert (a) on t1 from mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +GRANT select,references on t1 to mysqltest_1@localhost; +select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1"; +grant all on test.* to mysqltest_3@localhost with grant option; +revoke all on test.* from mysqltest_3@localhost; +show grants for mysqltest_3@localhost; +revoke grant option on test.* from mysqltest_3@localhost; +show grants for mysqltest_3@localhost; +grant all on test.t1 to mysqltest_2@localhost with grant option; +revoke all on test.t1 from mysqltest_2@localhost; +show grants for mysqltest_2@localhost; +revoke grant option on test.t1 from mysqltest_2@localhost; +show grants for mysqltest_2@localhost; +delete from mysql.user where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3"; +delete from mysql.db where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3"; +delete from mysql.tables_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3"; +delete from mysql.columns_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3"; +flush privileges; +drop table t1; + +# +# Test some error conditions +# +--error ER_WRONG_USAGE +GRANT FILE on mysqltest.* to mysqltest_1@localhost; +select 1; # To test that the previous command didn't cause problems + +# +# Bug#4898 User privileges depending on ORDER BY Settings of table db +# +insert into mysql.user (host, user) values ('localhost', 'test11'); +insert into mysql.db (host, db, user, select_priv) values +('localhost', 'a%', 'test11', 'Y'), ('localhost', 'ab%', 'test11', 'Y'); +alter table mysql.db order by db asc; +flush privileges; +show grants for test11@localhost; +alter table mysql.db order by db desc; +flush privileges; +show grants for test11@localhost; +delete from mysql.user where user='test11'; +delete from mysql.db where user='test11'; + +# +# Bug#6123 GRANT USAGE inserts useless Db row +# +create database mysqltest1; +grant usage on mysqltest1.* to test6123 identified by 'magic123'; +select host,db,user,select_priv,insert_priv from mysql.db where db="mysqltest1"; +delete from mysql.user where user='test6123'; +drop database mysqltest1; + +# +# Test for 'drop user', 'revoke privileges, grant' +# + +create table t1 (a int); +grant ALL PRIVILEGES on *.* to drop_user2@localhost with GRANT OPTION; +show grants for drop_user2@localhost; +revoke all privileges, grant option from drop_user2@localhost; +drop user drop_user2@localhost; + +grant ALL PRIVILEGES on *.* to drop_user@localhost with GRANT OPTION; +grant ALL PRIVILEGES on test.* to drop_user@localhost with GRANT OPTION; +grant select(a) on test.t1 to drop_user@localhost; +show grants for drop_user@localhost; + +# +# Bug#3086 SHOW GRANTS doesn't follow ANSI_QUOTES +# +set sql_mode=ansi_quotes; +show grants for drop_user@localhost; +set sql_mode=default; + +set sql_quote_show_create=0; +show grants for drop_user@localhost; +set sql_mode="ansi_quotes"; +show grants for drop_user@localhost; +set sql_quote_show_create=1; +show grants for drop_user@localhost; +set sql_mode=""; +show grants for drop_user@localhost; + +revoke all privileges, grant option from drop_user@localhost; +show grants for drop_user@localhost; +drop user drop_user@localhost; +--error ER_REVOKE_GRANTS +revoke all privileges, grant option from drop_user@localhost; + +grant select(a) on test.t1 to drop_user1@localhost; +grant select on test.t1 to drop_user2@localhost; +grant select on test.* to drop_user3@localhost; +grant select on *.* to drop_user4@localhost; +# Drop user now implicitly revokes all privileges. +drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost, +drop_user4@localhost; +--error ER_REVOKE_GRANTS +revoke all privileges, grant option from drop_user1@localhost, drop_user2@localhost, +drop_user3@localhost, drop_user4@localhost; +--error ER_CANNOT_USER +drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost, +drop_user4@localhost; +drop table t1; +grant usage on *.* to mysqltest_1@localhost identified by "password"; +grant select, update, insert on test.* to mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +drop user mysqltest_1@localhost; + +# +# Bug#3403 Wrong encoding in SHOW GRANTS output +# +SET NAMES koi8r; +CREATE DATABASE ÂÄ; +USE ÂÄ; +CREATE TABLE ÔÁ (ËÏÌ INT); + +GRANT SELECT ON ÂÄ.* TO ÀÚÅÒ@localhost; +SHOW GRANTS FOR ÀÚÅÒ@localhost; +REVOKE SELECT ON ÂÄ.* FROM ÀÚÅÒ@localhost; + +GRANT SELECT ON ÂÄ.ÔÁ TO ÀÚÅÒ@localhost; +SHOW GRANTS FOR ÀÚÅÒ@localhost; +REVOKE SELECT ON ÂÄ.ÔÁ FROM ÀÚÅÒ@localhost; + +GRANT SELECT (ËÏÌ) ON ÂÄ.ÔÁ TO ÀÚÅÒ@localhost; +SHOW GRANTS FOR ÀÚÅÒ@localhost; +REVOKE SELECT (ËÏÌ) ON ÂÄ.ÔÁ FROM ÀÚÅÒ@localhost; + +# Revoke does not drop user. Leave a clean user table for the next tests. +DROP USER ÀÚÅÒ@localhost; + +DROP DATABASE ÂÄ; +SET NAMES latin1; + +# +# Bug#5831 REVOKE ALL PRIVILEGES, GRANT OPTION does not revoke everything +# +USE test; +CREATE TABLE t1 (a int ); +CREATE TABLE t2 LIKE t1; +CREATE TABLE t3 LIKE t1; +CREATE TABLE t4 LIKE t1; +CREATE TABLE t5 LIKE t1; +CREATE TABLE t6 LIKE t1; +CREATE TABLE t7 LIKE t1; +CREATE TABLE t8 LIKE t1; +CREATE TABLE t9 LIKE t1; +CREATE TABLE t10 LIKE t1; +CREATE DATABASE testdb1; +CREATE DATABASE testdb2; +CREATE DATABASE testdb3; +CREATE DATABASE testdb4; +CREATE DATABASE testdb5; +CREATE DATABASE testdb6; +CREATE DATABASE testdb7; +CREATE DATABASE testdb8; +CREATE DATABASE testdb9; +CREATE DATABASE testdb10; +GRANT ALL ON testdb1.* TO testuser@localhost; +GRANT ALL ON testdb2.* TO testuser@localhost; +GRANT ALL ON testdb3.* TO testuser@localhost; +GRANT ALL ON testdb4.* TO testuser@localhost; +GRANT ALL ON testdb5.* TO testuser@localhost; +GRANT ALL ON testdb6.* TO testuser@localhost; +GRANT ALL ON testdb7.* TO testuser@localhost; +GRANT ALL ON testdb8.* TO testuser@localhost; +GRANT ALL ON testdb9.* TO testuser@localhost; +GRANT ALL ON testdb10.* TO testuser@localhost; +GRANT SELECT ON test.t1 TO testuser@localhost; +GRANT SELECT ON test.t2 TO testuser@localhost; +GRANT SELECT ON test.t3 TO testuser@localhost; +GRANT SELECT ON test.t4 TO testuser@localhost; +GRANT SELECT ON test.t5 TO testuser@localhost; +GRANT SELECT ON test.t6 TO testuser@localhost; +GRANT SELECT ON test.t7 TO testuser@localhost; +GRANT SELECT ON test.t8 TO testuser@localhost; +GRANT SELECT ON test.t9 TO testuser@localhost; +GRANT SELECT ON test.t10 TO testuser@localhost; +GRANT SELECT (a) ON test.t1 TO testuser@localhost; +GRANT SELECT (a) ON test.t2 TO testuser@localhost; +GRANT SELECT (a) ON test.t3 TO testuser@localhost; +GRANT SELECT (a) ON test.t4 TO testuser@localhost; +GRANT SELECT (a) ON test.t5 TO testuser@localhost; +GRANT SELECT (a) ON test.t6 TO testuser@localhost; +GRANT SELECT (a) ON test.t7 TO testuser@localhost; +GRANT SELECT (a) ON test.t8 TO testuser@localhost; +GRANT SELECT (a) ON test.t9 TO testuser@localhost; +GRANT SELECT (a) ON test.t10 TO testuser@localhost; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM testuser@localhost; +SHOW GRANTS FOR testuser@localhost; +DROP USER testuser@localhost; +DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10; +DROP DATABASE testdb1; +DROP DATABASE testdb2; +DROP DATABASE testdb3; +DROP DATABASE testdb4; +DROP DATABASE testdb5; +DROP DATABASE testdb6; +DROP DATABASE testdb7; +DROP DATABASE testdb8; +DROP DATABASE testdb9; +DROP DATABASE testdb10; + +# +# Bug#6932 a problem with 'revoke ALL PRIVILEGES' +# + +create table t1(a int, b int, c int, d int); +grant insert(b), insert(c), insert(d), insert(a) on t1 to grant_user@localhost; +show grants for grant_user@localhost; +select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv order by Column_name; +revoke ALL PRIVILEGES on t1 from grant_user@localhost; +show grants for grant_user@localhost; +select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv; +drop user grant_user@localhost; +drop table t1; + +# +# Bug#7391 Cross-database multi-table UPDATE security problem +# +create database mysqltest_1; +create database mysqltest_2; +create table mysqltest_1.t1 select 1 a, 2 q; +create table mysqltest_1.t2 select 1 b, 2 r; +create table mysqltest_2.t1 select 1 c, 2 s; +create table mysqltest_2.t2 select 1 d, 2 t; + +# test the column privileges +grant update (a) on mysqltest_1.t1 to mysqltest_3@localhost; +grant select (b) on mysqltest_1.t2 to mysqltest_3@localhost; +grant select (c) on mysqltest_2.t1 to mysqltest_3@localhost; +grant update (d) on mysqltest_2.t2 to mysqltest_3@localhost; +connect (conn1,localhost,mysqltest_3,,); +connection conn1; +SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES + WHERE GRANTEE = '''mysqltest_3''@''localhost''' + ORDER BY TABLE_NAME,COLUMN_NAME,PRIVILEGE_TYPE; +SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES + WHERE GRANTEE = '''mysqltest_3''@''localhost''' + ORDER BY TABLE_NAME,PRIVILEGE_TYPE; +SELECT * from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES + WHERE GRANTEE = '''mysqltest_3''@''localhost''' + ORDER BY TABLE_SCHEMA,PRIVILEGE_TYPE; +SELECT * from INFORMATION_SCHEMA.USER_PRIVILEGES + WHERE GRANTEE = '''mysqltest_3''@''localhost''' + ORDER BY TABLE_CATALOG,PRIVILEGE_TYPE; +--error ER_COLUMNACCESS_DENIED_ERROR +update mysqltest_1.t1, mysqltest_1.t2 set q=10 where b=1; +--error ER_COLUMNACCESS_DENIED_ERROR +update mysqltest_1.t2, mysqltest_2.t2 set d=20 where d=1; +--error ER_TABLEACCESS_DENIED_ERROR +update mysqltest_1.t1, mysqltest_2.t2 set d=20 where d=1; +--error ER_TABLEACCESS_DENIED_ERROR +update mysqltest_2.t1, mysqltest_1.t2 set c=20 where b=1; +--error ER_COLUMNACCESS_DENIED_ERROR +update mysqltest_2.t1, mysqltest_2.t2 set d=10 where s=2; +# the following two should work +update mysqltest_1.t1, mysqltest_2.t2 set a=10,d=10; +update mysqltest_1.t1, mysqltest_2.t1 set a=20 where c=20; +connection master; +select t1.*,t2.* from mysqltest_1.t1,mysqltest_1.t2; +select t1.*,t2.* from mysqltest_2.t1,mysqltest_2.t2; +revoke all on mysqltest_1.t1 from mysqltest_3@localhost; +revoke all on mysqltest_1.t2 from mysqltest_3@localhost; +revoke all on mysqltest_2.t1 from mysqltest_3@localhost; +revoke all on mysqltest_2.t2 from mysqltest_3@localhost; + +# test the db/table level privileges +grant all on mysqltest_2.* to mysqltest_3@localhost; +grant select on *.* to mysqltest_3@localhost; +# Next grant is needed to trigger bug#7391. Do not optimize! +grant select on mysqltest_2.t1 to mysqltest_3@localhost; +flush privileges; +disconnect conn1; +connect (conn2,localhost,mysqltest_3,,); +connection conn2; +use mysqltest_1; +update mysqltest_2.t1, mysqltest_2.t2 set c=500,d=600; +# the following failed before, should fail now. +--error ER_TABLEACCESS_DENIED_ERROR +update mysqltest_1.t1, mysqltest_1.t2 set a=100,b=200; +use mysqltest_2; +# the following used to succeed, it must fail now. +--error ER_TABLEACCESS_DENIED_ERROR +update mysqltest_1.t1, mysqltest_1.t2 set a=100,b=200; +--error ER_TABLEACCESS_DENIED_ERROR +update mysqltest_2.t1, mysqltest_1.t2 set c=100,b=200; +--error ER_TABLEACCESS_DENIED_ERROR +update mysqltest_1.t1, mysqltest_2.t2 set a=100,d=200; +# lets see the result +connection master; +select t1.*,t2.* from mysqltest_1.t1,mysqltest_1.t2; +select t1.*,t2.* from mysqltest_2.t1,mysqltest_2.t2; + +delete from mysql.user where user='mysqltest_3'; +delete from mysql.db where user="mysqltest_3"; +delete from mysql.tables_priv where user="mysqltest_3"; +delete from mysql.columns_priv where user="mysqltest_3"; +flush privileges; +drop database mysqltest_1; +drop database mysqltest_2; +disconnect conn2; + +# +# just SHOW PRIVILEGES test +# +SHOW PRIVILEGES; + +# +# Rights for renaming test (Bug#3270) +# +connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); +connection root; +--disable_warnings +create database mysqltest; +--enable_warnings +create table mysqltest.t1 (a int,b int,c int); +grant all on mysqltest.t1 to mysqltest_1@localhost; +connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK); +connection user1; +-- error ER_TABLEACCESS_DENIED_ERROR +alter table t1 rename t2; +disconnect user1; +connection root; +revoke all privileges on mysqltest.t1 from mysqltest_1@localhost; +delete from mysql.user where user=_binary'mysqltest_1'; +drop database mysqltest; +connection default; +disconnect root; + +# +# check all new table privileges +# +CREATE USER dummy@localhost; +CREATE DATABASE mysqltest; +CREATE TABLE mysqltest.dummytable (dummyfield INT); +CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable; +GRANT ALL PRIVILEGES ON mysqltest.dummytable TO dummy@localhost; +GRANT ALL PRIVILEGES ON mysqltest.dummyview TO dummy@localhost; +SHOW GRANTS FOR dummy@localhost; +use INFORMATION_SCHEMA; +SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY +PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE += '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; +FLUSH PRIVILEGES; +SHOW GRANTS FOR dummy@localhost; +SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY +PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE += '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; +SHOW FIELDS FROM mysql.tables_priv; +use test; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost; +DROP USER dummy@localhost; +DROP DATABASE mysqltest; +# check view only privileges +CREATE USER dummy@localhost; +CREATE DATABASE mysqltest; +CREATE TABLE mysqltest.dummytable (dummyfield INT); +CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable; +GRANT CREATE VIEW ON mysqltest.dummytable TO dummy@localhost; +GRANT CREATE VIEW ON mysqltest.dummyview TO dummy@localhost; +SHOW GRANTS FOR dummy@localhost; +use INFORMATION_SCHEMA; +SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY +PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE += '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; +FLUSH PRIVILEGES; +SHOW GRANTS FOR dummy@localhost; +SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY +PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE += '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; +use test; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost; +DROP USER dummy@localhost; +DROP DATABASE mysqltest; +CREATE USER dummy@localhost; +CREATE DATABASE mysqltest; +CREATE TABLE mysqltest.dummytable (dummyfield INT); +CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable; +GRANT SHOW VIEW ON mysqltest.dummytable TO dummy@localhost; +GRANT SHOW VIEW ON mysqltest.dummyview TO dummy@localhost; +SHOW GRANTS FOR dummy@localhost; +use INFORMATION_SCHEMA; +SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY +PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE += '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; +FLUSH PRIVILEGES; +SHOW GRANTS FOR dummy@localhost; +SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY +PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE += '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; +use test; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost; +DROP USER dummy@localhost; +DROP DATABASE mysqltest; +# +# Bug#11330 Entry in tables_priv with host = '' causes crash +# +connection default; +use mysql; +insert into tables_priv values ('','test_db','mysqltest_1','test_table','test_grantor',CURRENT_TIMESTAMP,'Select','Select'); +flush privileges; +delete from tables_priv where host = '' and user = 'mysqltest_1'; +flush privileges; +use test; + +# +# Bug#10892 user variables not auto cast for comparisons +# Check that we don't get illegal mix of collations +# +set @user123="non-existent"; +select * from mysql.db where user=@user123; + +set names koi8r; +create database ÂÄ; +grant select on ÂÄ.* to root@localhost; +select hex(Db) from mysql.db where Db='ÂÄ'; +show grants for root@localhost; +flush privileges; +show grants for root@localhost; +drop database ÂÄ; +revoke all privileges on ÂÄ.* from root@localhost; +show grants for root@localhost; +set names latin1; + +# +# Bug#15598 Server crashes in specific case during setting new password +# - Caused by a user with host '' +# +create user mysqltest_7@; +set password for mysqltest_7@ = password('systpass'); +show grants for mysqltest_7@; +drop user mysqltest_7@; +--error ER_NONEXISTING_GRANT +show grants for mysqltest_7@; + +# +# Bug#14385 GRANT and mapping to correct user account problems +# +create database mysqltest; +use mysqltest; +create table t1(f1 int); +GRANT DELETE ON mysqltest.t1 TO mysqltest1@'%'; +GRANT SELECT ON mysqltest.t1 TO mysqltest1@'192.%'; +show grants for mysqltest1@'192.%'; +show grants for mysqltest1@'%'; +delete from mysql.user where user='mysqltest1'; +delete from mysql.db where user='mysqltest1'; +delete from mysql.tables_priv where user='mysqltest1'; +flush privileges; +drop database mysqltest; + +# +# Bug#27515 DROP previlege is not required for RENAME TABLE +# +connection master; +create database db27515; +use db27515; +create table t1 (a int); +grant alter on db27515.t1 to user27515@localhost; +grant insert, create on db27515.t2 to user27515@localhost; + +connect (conn27515, localhost, user27515, , db27515); +connection conn27515; +--error ER_TABLEACCESS_DENIED_ERROR +rename table t1 to t2; +disconnect conn27515; + +connection master; +revoke all privileges, grant option from user27515@localhost; +drop user user27515@localhost; +drop database db27515; + +--echo End of 4.1 tests + +# +# Bug#16297 In memory grant tables not flushed when users's hostname is "" +# +use test; +create table t1 (a int); + +# Backup anonymous users and remove them. (They get in the way of +# the one we test with here otherwise.) +create table t2 as select * from mysql.user where user=''; +delete from mysql.user where user=''; +flush privileges; + +# Create some users with different hostnames +create user mysqltest_8@''; +create user mysqltest_8@host8; + +# Try to create them again +--error ER_CANNOT_USER +create user mysqltest_8@''; +--error ER_CANNOT_USER +create user mysqltest_8; +--error ER_CANNOT_USER +create user mysqltest_8@host8; + +select user, QUOTE(host) from mysql.user where user="mysqltest_8"; + +--echo Schema privileges +grant select on mysqltest.* to mysqltest_8@''; +show grants for mysqltest_8@''; +grant select on mysqltest.* to mysqltest_8@; +show grants for mysqltest_8@; +grant select on mysqltest.* to mysqltest_8; +show grants for mysqltest_8; +select * from information_schema.schema_privileges +where grantee like "'mysqltest_8'%"; +connect (conn3,localhost,mysqltest_8,,); +select * from t1; +disconnect conn3; +connection master; +revoke select on mysqltest.* from mysqltest_8@''; +show grants for mysqltest_8@''; +show grants for mysqltest_8; +select * from information_schema.schema_privileges +where grantee like "'mysqltest_8'%"; +flush privileges; +show grants for mysqltest_8@''; +show grants for mysqltest_8@; +grant select on mysqltest.* to mysqltest_8@''; +flush privileges; +show grants for mysqltest_8@; +revoke select on mysqltest.* from mysqltest_8@''; +flush privileges; + +--echo Column privileges +grant update (a) on t1 to mysqltest_8@''; +grant update (a) on t1 to mysqltest_8; +show grants for mysqltest_8@''; +show grants for mysqltest_8; +flush privileges; +show grants for mysqltest_8@''; +show grants for mysqltest_8; +select * from information_schema.column_privileges; +connect (conn4,localhost,mysqltest_8,,); +select * from t1; +disconnect conn4; +connection master; +revoke update (a) on t1 from mysqltest_8@''; +show grants for mysqltest_8@''; +show grants for mysqltest_8; +select * from information_schema.column_privileges; +flush privileges; +show grants for mysqltest_8@''; +show grants for mysqltest_8; + +--echo Table privileges +grant update on t1 to mysqltest_8@''; +grant update on t1 to mysqltest_8; +show grants for mysqltest_8@''; +show grants for mysqltest_8; +flush privileges; +show grants for mysqltest_8@''; +show grants for mysqltest_8; +select * from information_schema.table_privileges; +connect (conn5,localhost,mysqltest_8,,); +select * from t1; +disconnect conn5; +connection master; +revoke update on t1 from mysqltest_8@''; +show grants for mysqltest_8@''; +show grants for mysqltest_8; +select * from information_schema.table_privileges; +flush privileges; +show grants for mysqltest_8@''; +show grants for mysqltest_8; + +--echo "DROP USER" should clear privileges +grant all privileges on mysqltest.* to mysqltest_8@''; +grant select on mysqltest.* to mysqltest_8@''; +grant update on t1 to mysqltest_8@''; +grant update (a) on t1 to mysqltest_8@''; +grant all privileges on mysqltest.* to mysqltest_8; +show grants for mysqltest_8@''; +show grants for mysqltest_8; +select * from information_schema.user_privileges +where grantee like "'mysqltest_8'%"; +connect (conn5,localhost,mysqltest_8,,); +select * from t1; +disconnect conn5; +connection master; +flush privileges; +show grants for mysqltest_8@''; +show grants for mysqltest_8; +drop user mysqltest_8@''; +--error ER_NONEXISTING_GRANT +show grants for mysqltest_8@''; +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +--error ER_ACCESS_DENIED_ERROR +connect (conn6,localhost,mysqltest_8,,); +connection master; +--error ER_NONEXISTING_GRANT +show grants for mysqltest_8; +drop user mysqltest_8@host8; +--error ER_NONEXISTING_GRANT +show grants for mysqltest_8@host8; + +# Restore the anonymous users. +insert into mysql.user select * from t2; +flush privileges; +drop table t2; +drop table t1; + +# +# Bug#20214 Incorrect error when user calls SHOW CREATE VIEW on non +# privileged view +# + +connection master; + +CREATE DATABASE mysqltest3; +USE mysqltest3; + +CREATE TABLE t_nn (c1 INT); +CREATE VIEW v_nn AS SELECT * FROM t_nn; + +CREATE DATABASE mysqltest2; +USE mysqltest2; + +CREATE TABLE t_nn (c1 INT); +CREATE VIEW v_nn AS SELECT * FROM t_nn; +CREATE VIEW v_yn AS SELECT * FROM t_nn; +CREATE VIEW v_gy AS SELECT * FROM t_nn; +CREATE VIEW v_ny AS SELECT * FROM t_nn; +CREATE VIEW v_yy AS SELECT * FROM t_nn WHERE c1=55; + +GRANT SHOW VIEW ON mysqltest2.v_ny TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1'; +GRANT SELECT ON mysqltest2.v_yn TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1'; +GRANT SELECT ON mysqltest2.* TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1'; +GRANT SHOW VIEW,SELECT ON mysqltest2.v_yy TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1'; + +connect (mysqltest_1, localhost, mysqltest_1, mysqltest_1,); + +# fail because of missing SHOW VIEW (have generic SELECT) +--error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE VIEW mysqltest2.v_nn; +--error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE TABLE mysqltest2.v_nn; + +# fail because of missing SHOW VIEW +--error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE VIEW mysqltest2.v_yn; +--error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE TABLE mysqltest2.v_yn; + +# succeed (despite of missing SELECT, having SHOW VIEW bails us out) +SHOW CREATE TABLE mysqltest2.v_ny; + +# succeed (despite of missing SELECT, having SHOW VIEW bails us out) +SHOW CREATE VIEW mysqltest2.v_ny; + +# fail because of missing (specific or generic) SELECT +--error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE TABLE mysqltest3.t_nn; + +# fail because of missing (specific or generic) SELECT (not because it's not a view!) +--error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE VIEW mysqltest3.t_nn; + +# fail because of missing missing (specific or generic) SELECT (and SHOW VIEW) +--error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE VIEW mysqltest3.v_nn; +--error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE TABLE mysqltest3.v_nn; + +# succeed thanks to generic SELECT +SHOW CREATE TABLE mysqltest2.t_nn; + +# fail because it's not a view! (have generic SELECT though) +--error ER_WRONG_OBJECT +SHOW CREATE VIEW mysqltest2.t_nn; + +# succeed, have SELECT and SHOW VIEW +SHOW CREATE VIEW mysqltest2.v_yy; + +# succeed, have SELECT and SHOW VIEW +SHOW CREATE TABLE mysqltest2.v_yy; + +# clean-up +connection master; + +# succeed, we're root +SHOW CREATE TABLE mysqltest2.v_nn; +SHOW CREATE VIEW mysqltest2.v_nn; + +SHOW CREATE TABLE mysqltest2.t_nn; + +# fail because it's not a view! +--error ER_WRONG_OBJECT +SHOW CREATE VIEW mysqltest2.t_nn; + +DROP VIEW mysqltest2.v_nn; +DROP VIEW mysqltest2.v_yn; +DROP VIEW mysqltest2.v_ny; +DROP VIEW mysqltest2.v_yy; +DROP TABLE mysqltest2.t_nn; +DROP DATABASE mysqltest2; +DROP VIEW mysqltest3.v_nn; +DROP TABLE mysqltest3.t_nn; +DROP DATABASE mysqltest3; +disconnect mysqltest_1; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'mysqltest_1'@'localhost'; +DROP USER 'mysqltest_1'@'localhost'; + +# restore the original database +USE test; +connection default; +disconnect master; + + +# +# Bug#10668 CREATE USER does not enforce username length limit +# +--error ER_WRONG_STRING_LENGTH +create user longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789; + +# +# Test for Bug#16899 Possible buffer overflow in handling of DEFINER-clause. +# +# These checks are intended to ensure that appropriate errors are risen when +# illegal user name or hostname is specified in user-clause of GRANT/REVOKE +# statements. +# + +# +# Bug#22369 Alter table rename combined with other alterations causes lost tables +# +CREATE DATABASE mysqltest1; +CREATE TABLE mysqltest1.t1 ( + int_field INTEGER UNSIGNED NOT NULL, + char_field CHAR(10), + INDEX(`int_field`) +); +CREATE TABLE mysqltest1.t2 (int_field INT); + +--echo "Now check that we require equivalent grants for " +--echo "RENAME TABLE and ALTER TABLE" +CREATE USER mysqltest_1@localhost; +GRANT SELECT ON mysqltest1.t1 TO mysqltest_1@localhost; + +connect (conn42,localhost,mysqltest_1,,mysqltest1); +SELECT USER(); +SHOW GRANTS; +--error ER_TABLEACCESS_DENIED_ERROR +RENAME TABLE t1 TO t2; +--error ER_TABLEACCESS_DENIED_ERROR +ALTER TABLE t1 RENAME TO t2; +--disconnect conn42 +--connection default +GRANT DROP ON mysqltest1.t1 TO mysqltest_1@localhost; + +connect (conn42,localhost,mysqltest_1,,mysqltest1); +--error ER_TABLEACCESS_DENIED_ERROR +RENAME TABLE t1 TO t2; +--error ER_TABLEACCESS_DENIED_ERROR +ALTER TABLE t1 RENAME TO t2; +--disconnect conn42 +--connection default +GRANT ALTER ON mysqltest1.t1 TO mysqltest_1@localhost; + +connect (conn42,localhost,mysqltest_1,,mysqltest1); +SHOW GRANTS; +--error ER_TABLEACCESS_DENIED_ERROR +RENAME TABLE t1 TO t2; +--error ER_TABLEACCESS_DENIED_ERROR +ALTER TABLE t1 RENAME TO t2; +--disconnect conn42 +--connection default +GRANT INSERT, CREATE ON mysqltest1.t1 TO mysqltest_1@localhost; +connect (conn42,localhost,mysqltest_1,,mysqltest1); +SHOW GRANTS; +--error ER_TABLEACCESS_DENIED_ERROR +--disconnect conn42 +--connection default +GRANT INSERT, SELECT, CREATE, ALTER, DROP ON mysqltest1.t2 TO mysqltest_1@localhost; +DROP TABLE mysqltest1.t2; + +connect (conn42,localhost,mysqltest_1,,mysqltest1); +SHOW GRANTS; +RENAME TABLE t1 TO t2; +RENAME TABLE t2 TO t1; +ALTER TABLE t1 RENAME TO t2; +ALTER TABLE t2 RENAME TO t1; +--disconnect conn42 +--connection default +REVOKE DROP, INSERT ON mysqltest1.t1 FROM mysqltest_1@localhost; +REVOKE DROP, INSERT ON mysqltest1.t2 FROM mysqltest_1@localhost; + +connect (conn42,localhost,mysqltest_1,,mysqltest1); +SHOW GRANTS; +--error ER_TABLEACCESS_DENIED_ERROR +RENAME TABLE t1 TO t2; +--error ER_TABLEACCESS_DENIED_ERROR +ALTER TABLE t1 RENAME TO t2; +--disconnect conn42 +--connection default + +DROP USER mysqltest_1@localhost; +DROP DATABASE mysqltest1; +USE test; + +# Working with database-level privileges. + +--error ER_WRONG_STRING_LENGTH +GRANT CREATE ON mysqltest.* TO longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; + +--error ER_WRONG_STRING_LENGTH +GRANT CREATE ON mysqltest.* TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; + +--error ER_WRONG_STRING_LENGTH +REVOKE CREATE ON mysqltest.* FROM longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; + +--error ER_WRONG_STRING_LENGTH +REVOKE CREATE ON mysqltest.* FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; + +# Working with table-level privileges. + +--error ER_WRONG_STRING_LENGTH +GRANT CREATE ON t1 TO longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; + +--error ER_WRONG_STRING_LENGTH +GRANT CREATE ON t1 TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; + +--error ER_WRONG_STRING_LENGTH +REVOKE CREATE ON t1 FROM longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; + +--error ER_WRONG_STRING_LENGTH +REVOKE CREATE ON t1 FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; + +# Working with routine-level privileges. + +--error ER_WRONG_STRING_LENGTH +GRANT EXECUTE ON PROCEDURE p1 TO longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; + +--error ER_WRONG_STRING_LENGTH +GRANT EXECUTE ON PROCEDURE p1 TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; + +--error ER_WRONG_STRING_LENGTH +REVOKE EXECUTE ON PROCEDURE p1 FROM longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; + +--error ER_WRONG_STRING_LENGTH +REVOKE EXECUTE ON PROCEDURE t1 FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; + + +# +# Bug#23556 TRUNCATE TABLE still maps to DELETE +# +CREATE USER bug23556@localhost; +CREATE DATABASE bug23556; +GRANT SELECT ON bug23556.* TO bug23556@localhost; +connect (bug23556,localhost,bug23556,,bug23556); + +connection default; +USE bug23556; +CREATE TABLE t1 (a INT PRIMARY KEY); INSERT INTO t1 VALUES (1),(2),(3),(4),(5); +GRANT DELETE ON t1 TO bug23556@localhost; + +connection bug23556; +USE bug23556; +--error ER_TABLEACCESS_DENIED_ERROR +TRUNCATE t1; + +connection default; +USE bug23556; +REVOKE DELETE ON t1 FROM bug23556@localhost; +GRANT DROP ON t1 TO bug23556@localhost; + +connection bug23556; +USE bug23556; +TRUNCATE t1; + +connection default; +USE bug23556; +DROP TABLE t1; +USE test; +DROP DATABASE bug23556; +DROP USER bug23556@localhost; +connection default; +disconnect bug23556; + + +# +# Bug#6774 Replication fails with Wrong usage of DB GRANT and GLOBAL PRIVILEGES +# +# Check if GRANT ... ON * ... fails when no database is selected +connect (con1, localhost, root,,*NO-ONE*); +connection con1; +--error ER_NO_DB_ERROR +GRANT PROCESS ON * TO user@localhost; +disconnect con1; +connection default; + + +# +# Bug#9504 Stored procedures: execute privilege doesn't make 'use database' +# okay. +# + +# Prepare. + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest1; +DROP DATABASE IF EXISTS mysqltest2; +DROP DATABASE IF EXISTS mysqltest3; +DROP DATABASE IF EXISTS mysqltest4; +--enable_warnings + +CREATE DATABASE mysqltest1; +CREATE DATABASE mysqltest2; +CREATE DATABASE mysqltest3; +CREATE DATABASE mysqltest4; + +CREATE PROCEDURE mysqltest1.p_def() SQL SECURITY DEFINER + SELECT 1; + +CREATE PROCEDURE mysqltest2.p_inv() SQL SECURITY INVOKER + SELECT 1; + +CREATE FUNCTION mysqltest3.f_def() RETURNS INT SQL SECURITY DEFINER + RETURN 1; + +CREATE FUNCTION mysqltest4.f_inv() RETURNS INT SQL SECURITY INVOKER + RETURN 1; + +GRANT EXECUTE ON PROCEDURE mysqltest1.p_def TO mysqltest_1@localhost; +GRANT EXECUTE ON PROCEDURE mysqltest2.p_inv TO mysqltest_1@localhost; +GRANT EXECUTE ON FUNCTION mysqltest3.f_def TO mysqltest_1@localhost; +GRANT EXECUTE ON FUNCTION mysqltest4.f_inv TO mysqltest_1@localhost; + +GRANT ALL PRIVILEGES ON test.* TO mysqltest_1@localhost; + +# Test. + +--connect (bug9504_con1,localhost,mysqltest_1,,) +--echo +--echo ---> connection: bug9504_con1 + +# - Check that we can switch to the db; + +use mysqltest1; + +use mysqltest2; + +use mysqltest3; + +use mysqltest4; + +# - Check that we can call stored routines; + +use test; + +CALL mysqltest1.p_def(); + +CALL mysqltest2.p_inv(); + +SELECT mysqltest3.f_def(); + +SELECT mysqltest4.f_inv(); + +# Cleanup. + +--connection default +--echo +--echo ---> connection: default + +--disconnect bug9504_con1 + +DROP DATABASE mysqltest1; +DROP DATABASE mysqltest2; +DROP DATABASE mysqltest3; +DROP DATABASE mysqltest4; + +DROP USER mysqltest_1@localhost; + + +# +# Bug#27337 Privileges are not restored properly. +# +# Actually, the patch for this bugs fixes two problems. So, here are two test +# cases. + +# Test case 1: privileges are not restored properly after calling a stored +# routine defined with SQL SECURITY INVOKER clause. + +# Prepare. + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest1; +DROP DATABASE IF EXISTS mysqltest2; +--enable_warnings + +CREATE DATABASE mysqltest1; +CREATE DATABASE mysqltest2; + +GRANT ALL PRIVILEGES ON mysqltest1.* TO mysqltest_1@localhost; +GRANT SELECT ON mysqltest2.* TO mysqltest_1@localhost; + +CREATE PROCEDURE mysqltest1.p1() SQL SECURITY INVOKER + SELECT 1; + +# Test. + +--connect (bug27337_con1,localhost,mysqltest_1,,mysqltest2) +--echo +--echo ---> connection: bug27337_con1 + +--error ER_TABLEACCESS_DENIED_ERROR +CREATE TABLE t1(c INT); + +CALL mysqltest1.p1(); + +--error ER_TABLEACCESS_DENIED_ERROR +CREATE TABLE t1(c INT); + +--disconnect bug27337_con1 + +--connect (bug27337_con2,localhost,mysqltest_1,,mysqltest2) +--echo +--echo ---> connection: bug27337_con2 + +--error ER_TABLEACCESS_DENIED_ERROR +CREATE TABLE t1(c INT); + +SHOW TABLES; + +# Cleanup. + +--connection default +--echo +--echo ---> connection: default + +--disconnect bug27337_con2 + +DROP DATABASE mysqltest1; +DROP DATABASE mysqltest2; + +DROP USER mysqltest_1@localhost; + +# Test case 2: privileges are not checked properly for prepared statements. + +# Prepare. + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest1; +DROP DATABASE IF EXISTS mysqltest2; +--enable_warnings + +CREATE DATABASE mysqltest1; +CREATE DATABASE mysqltest2; + +CREATE TABLE mysqltest1.t1(c INT); +CREATE TABLE mysqltest2.t2(c INT); + +GRANT SELECT ON mysqltest1.t1 TO mysqltest_1@localhost; +GRANT SELECT ON mysqltest2.t2 TO mysqltest_2@localhost; + +# Test. + +--connect (bug27337_con1,localhost,mysqltest_1,,mysqltest1) +--echo +--echo ---> connection: bug27337_con1 + +SHOW TABLES FROM mysqltest1; + +PREPARE stmt1 FROM 'SHOW TABLES FROM mysqltest1'; + +EXECUTE stmt1; + +--connect (bug27337_con2,localhost,mysqltest_2,,mysqltest2) +--echo +--echo ---> connection: bug27337_con2 + +SHOW COLUMNS FROM mysqltest2.t2; + +PREPARE stmt2 FROM 'SHOW COLUMNS FROM mysqltest2.t2'; + +EXECUTE stmt2; + +--connection default +--echo +--echo ---> connection: default + +REVOKE SELECT ON mysqltest1.t1 FROM mysqltest_1@localhost; +REVOKE SELECT ON mysqltest2.t2 FROM mysqltest_2@localhost; + +--connection bug27337_con1 +--echo +--echo ---> connection: bug27337_con1 + +--error ER_DBACCESS_DENIED_ERROR +SHOW TABLES FROM mysqltest1; + +--error ER_DBACCESS_DENIED_ERROR +EXECUTE stmt1; + +--connection bug27337_con2 +--echo +--echo ---> connection: bug27337_con2 + +--error ER_TABLEACCESS_DENIED_ERROR +SHOW COLUMNS FROM mysqltest2.t2; + +--error ER_TABLEACCESS_DENIED_ERROR +EXECUTE stmt2; + +# Cleanup. + +--connection default +--echo +--echo ---> connection: default + +--disconnect bug27337_con1 +--disconnect bug27337_con2 + +DROP DATABASE mysqltest1; +DROP DATABASE mysqltest2; + +DROP USER mysqltest_1@localhost; +DROP USER mysqltest_2@localhost; + +# +# Bug#27878 Unchecked privileges on a view referring to a table from another +# database. +# +USE test; +CREATE TABLE t1 (f1 int, f2 int); +INSERT INTO t1 VALUES(1,1), (2,2); +CREATE DATABASE db27878; +GRANT UPDATE(f1) ON t1 TO 'mysqltest_1'@'localhost'; +GRANT SELECT ON `test`.* TO 'mysqltest_1'@'localhost'; +GRANT ALL ON db27878.* TO 'mysqltest_1'@'localhost'; +USE db27878; +CREATE SQL SECURITY INVOKER VIEW db27878.v1 AS SELECT * FROM test.t1; +connect (user1,localhost,mysqltest_1,,test); +connection user1; +USE db27878; +--error 1356 +UPDATE v1 SET f2 = 4; +SELECT * FROM test.t1; +disconnect user1; +connection default; +REVOKE UPDATE (f1) ON `test`.`t1` FROM 'mysqltest_1'@'localhost'; +REVOKE SELECT ON `test`.* FROM 'mysqltest_1'@'localhost'; +REVOKE ALL ON db27878.* FROM 'mysqltest_1'@'localhost'; +DROP USER mysqltest_1@localhost; +DROP DATABASE db27878; +USE test; +DROP TABLE t1; + +--echo # +--echo # Bug#33275 Server crash when creating temporary table mysql.user +--echo # +CREATE TEMPORARY TABLE mysql.user (id INT); +FLUSH PRIVILEGES; +DROP TABLE mysql.user; + + +# +# Bug#33201 Crash occurs when granting update privilege on one column of a view +# +drop table if exists test; +drop function if exists test_function; +drop view if exists v1; +create table test (col1 varchar(30)); +delimiter |; +create function test_function() returns varchar(30) +begin + declare tmp varchar(30); + select col1 from test limit 1 into tmp; + return '1'; +end| +delimiter ;| +create view v1 as select test.* from test where test.col1=test_function(); +grant update (col1) on v1 to 'greg'@'localhost'; +drop user 'greg'@'localhost'; +drop view v1; +drop table test; +drop function test_function; + +# +# Bug#41456 SET PASSWORD hates CURRENT_USER() +# +SELECT CURRENT_USER(); +SET PASSWORD FOR CURRENT_USER() = PASSWORD("admin"); +SET PASSWORD FOR CURRENT_USER() = PASSWORD(""); + +# +# Bug#57952: privilege change is not taken into account by EXECUTE. +# + +--echo +--echo # Bug#57952 +--echo + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest1; +DROP DATABASE IF EXISTS mysqltest2; +--enable_warnings + +CREATE DATABASE mysqltest1; +CREATE DATABASE mysqltest2; + +use mysqltest1; +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 1); + +CREATE TABLE t2(a INT); +INSERT INTO t2 VALUES (2); + +CREATE TABLE mysqltest2.t3(a INT); +INSERT INTO mysqltest2.t3 VALUES (4); + +CREATE USER testuser@localhost; +GRANT CREATE ROUTINE, EXECUTE ON mysqltest1.* TO testuser@localhost; +GRANT SELECT(b) ON t1 TO testuser@localhost; +GRANT SELECT ON t2 TO testuser@localhost; +GRANT SELECT ON mysqltest2.* TO testuser@localhost; + +--echo +--echo # Connection: bug57952_con1 (testuser@localhost, db: mysqltest1) +--connect (bug57952_con1,localhost,testuser,,mysqltest1) +PREPARE s1 FROM 'SELECT b FROM t1'; +PREPARE s2 FROM 'SELECT a FROM t2'; +PREPARE s3 FROM 'SHOW TABLES FROM mysqltest2'; + +CREATE PROCEDURE p1() SELECT b FROM t1; +CREATE PROCEDURE p2() SELECT a FROM t2; +CREATE PROCEDURE p3() SHOW TABLES FROM mysqltest2; + +CALL p1; +CALL p2; +CALL p3; + +--echo +--echo # Connection: default +--connection default +REVOKE SELECT ON t1 FROM testuser@localhost; +GRANT SELECT(a) ON t1 TO testuser@localhost; +REVOKE SELECT ON t2 FROM testuser@localhost; +REVOKE SELECT ON mysqltest2.* FROM testuser@localhost; + +--echo +--echo # Connection: bug57952_con1 (testuser@localhost, db: mysqltest1) +--connection bug57952_con1 +--echo # - Check column-level privileges... +--error ER_COLUMNACCESS_DENIED_ERROR +EXECUTE s1; + +--error ER_COLUMNACCESS_DENIED_ERROR +SELECT b FROM t1; + +--error ER_COLUMNACCESS_DENIED_ERROR +EXECUTE s1; + +--error ER_COLUMNACCESS_DENIED_ERROR +CALL p1; + +--echo # - Check table-level privileges... +--error ER_TABLEACCESS_DENIED_ERROR +SELECT a FROM t2; + +--error ER_TABLEACCESS_DENIED_ERROR +EXECUTE s2; + +--error ER_TABLEACCESS_DENIED_ERROR +CALL p2; + +--echo # - Check database-level privileges... +--error ER_DBACCESS_DENIED_ERROR +SHOW TABLES FROM mysqltest2; + +--error ER_DBACCESS_DENIED_ERROR +EXECUTE s3; + +--error ER_DBACCESS_DENIED_ERROR +CALL p3; + +--echo +--echo # Connection: default +--connection default +--disconnect bug57952_con1 +DROP DATABASE mysqltest1; +DROP DATABASE mysqltest2; +DROP USER testuser@localhost; +use test; +--echo + + +--echo # +--echo # Test for bug #36544 "DROP USER does not remove stored function +--echo # privileges". +--echo # +create database mysqltest1; +create function mysqltest1.f1() returns int return 0; +create procedure mysqltest1.p1() begin end; +--echo # +--echo # 1) Check that DROP USER properly removes privileges on both +--echo # stored procedures and functions. +--echo # +create user mysqluser1@localhost; +grant execute on function mysqltest1.f1 to mysqluser1@localhost; +grant execute on procedure mysqltest1.p1 to mysqluser1@localhost; + +--echo # Quick test that granted privileges are properly reflected +--echo # in privilege tables and in in-memory structures. +show grants for mysqluser1@localhost; +select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost'; +--echo # +--echo # Create connection 'bug_36544_con1' as 'mysqluser1@localhost'. +--connect (bug36544_con1,localhost,mysqluser1,,) +call mysqltest1.p1(); +select mysqltest1.f1(); + +--echo # +--echo # Switch to connection 'default'. +--connection default +drop user mysqluser1@localhost; + +--echo # +--echo # Test that dropping of user is properly reflected in +--echo # both privilege tables and in in-memory structures. +--echo # +--echo # Switch to connection 'bug36544_con1'. +--connection bug36544_con1 +--echo # The connection cold be alive but should not be able to +--echo # access to any of the stored routines. +--error ER_PROCACCESS_DENIED_ERROR +call mysqltest1.p1(); +--error ER_PROCACCESS_DENIED_ERROR +select mysqltest1.f1(); +--disconnect bug36544_con1 + +--echo # +--echo # Switch to connection 'default'. +--connection default +--echo # +--echo # Now create user with the same name and check that he +--echo # has not inherited privileges. +create user mysqluser1@localhost; +show grants for mysqluser1@localhost; +select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost'; +--echo # +--echo # Create connection 'bug_36544_con2' as 'mysqluser1@localhost'. +--connect (bug36544_con2,localhost,mysqluser1,,) +--echo # Newly created user should not be able to access any of the routines. +--error ER_PROCACCESS_DENIED_ERROR +call mysqltest1.p1(); +--error ER_PROCACCESS_DENIED_ERROR +select mysqltest1.f1(); +--echo # +--echo # Switch to connection 'default'. +--connection default + +--echo # +--echo # 2) Check that RENAME USER properly updates privileges on both +--echo # stored procedures and functions. +--echo # +grant execute on function mysqltest1.f1 to mysqluser1@localhost; +grant execute on procedure mysqltest1.p1 to mysqluser1@localhost; +--echo # +--echo # Create one more user to make in-memory hashes non-trivial. +--echo # User names 'mysqluser11' and 'mysqluser10' were selected +--echo # to trigger bug discovered during code inspection. +create user mysqluser11@localhost; +grant execute on function mysqltest1.f1 to mysqluser11@localhost; +grant execute on procedure mysqltest1.p1 to mysqluser11@localhost; +--echo # Also create a couple of tables to test for another bug +--echo # discovered during code inspection (again table names were +--echo # chosen especially to trigger the bug). +create table mysqltest1.t11 (i int); +create table mysqltest1.t22 (i int); +grant select on mysqltest1.t22 to mysqluser1@localhost; +grant select on mysqltest1.t11 to mysqluser1@localhost; + +--echo # Quick test that granted privileges are properly reflected +--echo # in privilege tables and in in-memory structures. +show grants for mysqluser1@localhost; +select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost'; +select db, table_name, table_priv from mysql.tables_priv where user='mysqluser1' and host='localhost'; +--echo # +--echo # Switch to connection 'bug36544_con2'. +--connection bug36544_con2 +call mysqltest1.p1(); +select mysqltest1.f1(); +select * from mysqltest1.t11; +select * from mysqltest1.t22; + +--echo # +--echo # Switch to connection 'default'. +--connection default +rename user mysqluser1@localhost to mysqluser10@localhost; + +--echo # +--echo # Test that there are no privileges left for mysqluser1. +--echo # +--echo # Switch to connection 'bug36544_con2'. +--connection bug36544_con2 +--echo # The connection cold be alive but should not be able to +--echo # access to any of the stored routines or tables. +--error ER_PROCACCESS_DENIED_ERROR +call mysqltest1.p1(); +--error ER_PROCACCESS_DENIED_ERROR +select mysqltest1.f1(); +--error ER_TABLEACCESS_DENIED_ERROR +select * from mysqltest1.t11; +--error ER_TABLEACCESS_DENIED_ERROR +select * from mysqltest1.t22; +--disconnect bug36544_con2 + +--echo # +--echo # Switch to connection 'default'. +--connection default +--echo # +--echo # Now create user with the old name and check that he +--echo # has not inherited privileges. +create user mysqluser1@localhost; +show grants for mysqluser1@localhost; +select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost'; +select db, table_name, table_priv from mysql.tables_priv where user='mysqluser1' and host='localhost'; +--echo # +--echo # Create connection 'bug_36544_con3' as 'mysqluser1@localhost'. +--connect (bug36544_con3,localhost,mysqluser1,,) +--echo # Newly created user should not be able to access to any of the +--echo # stored routines or tables. +--error ER_PROCACCESS_DENIED_ERROR +call mysqltest1.p1(); +--error ER_PROCACCESS_DENIED_ERROR +select mysqltest1.f1(); +--error ER_TABLEACCESS_DENIED_ERROR +select * from mysqltest1.t11; +--error ER_TABLEACCESS_DENIED_ERROR +select * from mysqltest1.t22; +--disconnect bug36544_con3 + +--echo # +--echo # Switch to connection 'default'. +--connection default +--echo # +--echo # Now check that privileges became associated with a new user +--echo # name - mysqluser10. +--echo # +show grants for mysqluser10@localhost; +select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser10' and host='localhost'; +select db, table_name, table_priv from mysql.tables_priv where user='mysqluser10' and host='localhost'; +--echo # +--echo # Create connection 'bug_36544_con4' as 'mysqluser10@localhost'. +--connect (bug36544_con4,localhost,mysqluser10,,) +call mysqltest1.p1(); +select mysqltest1.f1(); +select * from mysqltest1.t11; +select * from mysqltest1.t22; +--disconnect bug36544_con4 + +--echo # +--echo # Switch to connection 'default'. +--connection default +--echo # +--echo # Clean-up. +drop user mysqluser1@localhost; +drop user mysqluser10@localhost; +drop user mysqluser11@localhost; +drop database mysqltest1; + + +--echo End of 5.0 tests +set names utf8; +--error ER_WRONG_STRING_LENGTH +grant select on test.* to очень_длинный_юзер890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890@localhost; +set names default; + +# +# Bug#20901 CREATE privilege is enough to insert into a table +# + +create database mysqltest; +use mysqltest; + +grant create on mysqltest.* to mysqltest@localhost; +create table t1 (i INT); + +connect (user1,localhost,mysqltest,,mysqltest); +connection user1; +# show we don't have INSERT +--error ER_TABLEACCESS_DENIED_ERROR +insert into t1 values (1); +# show we have CREATE +create table t2 (i INT); +create table t4 (i INT); + +connection default; +grant select, insert on mysqltest.t2 to mysqltest@localhost; +grant insert on mysqltest.t4 to mysqltest@localhost; +# to specify ACLs for non-existent objects, must explictly |CREATE +grant create, insert on mysqltest.t5 to mysqltest@localhost; +grant create, insert on mysqltest.t6 to mysqltest@localhost; +flush privileges; + +connection user1; +insert into t2 values (1); + + +# CREATE IF NOT EXISTS...SELECT, t1 exists, no INSERT, must fail +--error ER_TABLEACCESS_DENIED_ERROR +create table if not exists t1 select * from t2; + +# CREATE IF NOT EXISTS...SELECT, no t3 yet, no INSERT, must fail +--error ER_TABLEACCESS_DENIED_ERROR +create table if not exists t3 select * from t2; + +# CREATE IF NOT EXISTS...SELECT, t4 exists, have INSERT, must succeed +create table if not exists t4 select * from t2; + +# CREATE IF NOT EXISTS...SELECT, no t5 yet, have INSERT, must succeed +create table if not exists t5 select * from t2; + + +# CREATE...SELECT, no t6 yet, have INSERT, must succeed +create table t6 select * from t2; + +# CREATE...SELECT, no t7 yet, no INSERT, must fail +--error ER_TABLEACCESS_DENIED_ERROR +create table t7 select * from t2; + +# CREATE...SELECT, t4 exists, have INSERT, must still fail (exists) +--error 1050 +create table t4 select * from t2; + +# CREATE...SELECT, t1 exists, no INSERT, must fail +--error ER_TABLEACCESS_DENIED_ERROR +create table t1 select * from t2; + + +connection default; +drop table t1,t2,t4,t5,t6; + +revoke create on mysqltest.* from mysqltest@localhost; +revoke select, insert on mysqltest.t2 from mysqltest@localhost; +revoke insert on mysqltest.t4 from mysqltest@localhost; +revoke create, insert on mysqltest.t5 from mysqltest@localhost; +revoke create, insert on mysqltest.t6 from mysqltest@localhost; +drop user mysqltest@localhost; + +disconnect user1; +drop database mysqltest; +use test; + + +# +# Bug#16470 crash on grant if old grant tables +# + +call mtr.add_suppression("Can't open and lock privilege tables"); + +--echo FLUSH PRIVILEGES without procs_priv table. +RENAME TABLE mysql.procs_priv TO mysql.procs_gone; +FLUSH PRIVILEGES; +--echo Assigning privileges without procs_priv table. +CREATE DATABASE mysqltest1; +CREATE PROCEDURE mysqltest1.test() SQL SECURITY DEFINER + SELECT 1; +CREATE FUNCTION mysqltest1.test() RETURNS INT RETURN 1; +--error ER_NO_SUCH_TABLE +GRANT EXECUTE ON FUNCTION mysqltest1.test TO mysqltest_1@localhost; +GRANT ALL PRIVILEGES ON test.* TO mysqltest_1@localhost; +CALL mysqltest1.test(); +DROP DATABASE mysqltest1; +RENAME TABLE mysql.procs_gone TO mysql.procs_priv; +DROP USER mysqltest_1@localhost; +FLUSH PRIVILEGES; + + +# +# Bug#33464 DROP FUNCTION caused a crash. +# +CREATE DATABASE dbbug33464; +CREATE USER 'userbug33464'@'localhost'; + +GRANT CREATE ROUTINE ON dbbug33464.* TO 'userbug33464'@'localhost'; + +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (connbug33464, localhost, userbug33464, , dbbug33464); +--source suite/funcs_1/include/show_connection.inc + +delimiter //; +CREATE PROCEDURE sp3(v1 char(20)) +BEGIN + SELECT * from dbbug33464.t6 where t6.f2= 'xyz'; +END// +delimiter ;// + +delimiter //; +CREATE FUNCTION fn1() returns char(50) SQL SECURITY INVOKER +BEGIN + return 1; +END// +delimiter ;// + +delimiter //; +CREATE FUNCTION fn2() returns char(50) SQL SECURITY DEFINER +BEGIN + return 2; +END// +delimiter ;// + +disconnect connbug33464; + +# cleanup +connection default; +USE dbbug33464; +--source suite/funcs_1/include/show_connection.inc + +SELECT fn1(); +SELECT fn2(); + +--error 0, ER_CANNOT_USER +DROP USER 'userbug33464'@'localhost'; + +DROP FUNCTION fn1; +DROP FUNCTION fn2; +DROP PROCEDURE sp3; + +--error 0, ER_CANNOT_USER +DROP USER 'userbug33464'@'localhost'; + +USE test; +DROP DATABASE dbbug33464; + + +SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators; + +# +# Bug#44658 Create procedure makes server crash when user does not have ALL privilege +# +CREATE USER user1; +CREATE USER user2; +GRANT CREATE ON db1.* TO 'user1'@'localhost'; +GRANT CREATE ROUTINE ON db1.* TO 'user1'@'localhost'; +GRANT CREATE ON db1.* TO 'user2'@'%'; +GRANT CREATE ROUTINE ON db1.* TO 'user2'@'%'; +FLUSH PRIVILEGES; +SHOW GRANTS FOR 'user1'@'localhost'; +connect (con1,localhost,user1,,); +--echo ** Connect as user1 and create a procedure. +--echo ** The creation will imply implicitly assigned +--echo ** EXECUTE and ALTER ROUTINE privileges to +--echo ** the current user user1@localhost. +SELECT @@GLOBAL.sql_mode; +SELECT @@SESSION.sql_mode; +CREATE DATABASE db1; +DELIMITER ||; +CREATE PROCEDURE db1.proc1(p1 INT) + BEGIN + SET @x = 0; + REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; + END ;|| +DELIMITER ;|| + +connect (con2,localhost,user2,,); +--echo ** Connect as user2 and create a procedure. +--echo ** Implicitly assignment of privileges will +--echo ** fail because the user2@localhost is an +--echo ** unknown user. +DELIMITER ||; +CREATE PROCEDURE db1.proc2(p1 INT) + BEGIN + SET @x = 0; + REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; + END ;|| +DELIMITER ;|| + +connection default; +SHOW GRANTS FOR 'user1'@'localhost'; +SHOW GRANTS FOR 'user2'; +disconnect con1; +disconnect con2; +DROP PROCEDURE db1.proc1; +DROP PROCEDURE db1.proc2; +REVOKE ALL ON db1.* FROM 'user1'@'localhost'; +REVOKE ALL ON db1.* FROM 'user2'@'%'; +DROP USER 'user1'; +DROP USER 'user1'@'localhost'; +DROP USER 'user2'; +DROP DATABASE db1; + + +--echo # +--echo # Bug #25863 No database selected error, but documentation +--echo # says * for global allowed +--echo # + +connect(conn1,localhost,root,,*NO-ONE*); + +--error ER_NO_DB_ERROR +GRANT ALL ON * TO mysqltest_1; + +GRANT ALL ON *.* TO mysqltest_1; +SHOW GRANTS FOR mysqltest_1; +DROP USER mysqltest_1; + +USE test; + +GRANT ALL ON * TO mysqltest_1; +SHOW GRANTS FOR mysqltest_1; +DROP USER mysqltest_1; + +GRANT ALL ON *.* TO mysqltest_1; +SHOW GRANTS FOR mysqltest_1; +DROP USER mysqltest_1; + +connection default; +disconnect conn1; + + +# +# Bug #53371: COM_FIELD_LIST can be abused to bypass table level grants. +# + +CREATE DATABASE db1; +CREATE DATABASE db2; +GRANT SELECT ON db1.* to 'testbug'@localhost; +USE db2; +CREATE TABLE t1 (a INT); +USE test; +connect (con1,localhost,testbug,,db1); +--error ER_NO_SUCH_TABLE +SELECT * FROM `../db2/tb2`; +--error ER_TABLEACCESS_DENIED_ERROR +SELECT * FROM `../db2`.tb2; +--error ER_WRONG_TABLE_NAME +SELECT * FROM `#mysql50#/../db2/tb2`; +connection default; +disconnect con1; +DROP USER 'testbug'@localhost; +DROP TABLE db2.t1; +DROP DATABASE db1; +DROP DATABASE db2; + +--echo # +--echo # Bug #36742 +--echo # +grant usage on Foo.* to myuser@Localhost identified by 'foo'; +grant select on Foo.* to myuser@localhost; +select host,user from mysql.user where User='myuser'; +revoke select on Foo.* from myuser@localhost; +delete from mysql.user where User='myuser'; +flush privileges; + +--echo ######################################################################### +--echo # +--echo # Bug#38347: ALTER ROUTINE privilege allows SHOW CREATE TABLE. +--echo # +--echo ######################################################################### + +--echo +--echo # -- +--echo # -- Prepare the environment. +--echo # -- + +DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; +FLUSH PRIVILEGES; + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest_db1; +--enable_warnings + +CREATE DATABASE mysqltest_db1; + +CREATE TABLE mysqltest_db1.t1(a INT); + +--echo +--echo # -- +--echo # -- Check that global privileges don't allow SHOW CREATE TABLE. +--echo # -- + +GRANT EVENT ON mysqltest_db1.* TO mysqltest_u1@localhost; +GRANT CREATE TEMPORARY TABLES ON mysqltest_db1.* TO mysqltest_u1@localhost; +GRANT LOCK TABLES ON mysqltest_db1.* TO mysqltest_u1@localhost; +GRANT ALTER ROUTINE ON mysqltest_db1.* TO mysqltest_u1@localhost; +GRANT CREATE ROUTINE ON mysqltest_db1.* TO mysqltest_u1@localhost; +GRANT EXECUTE ON mysqltest_db1.* TO mysqltest_u1@localhost; + +GRANT FILE ON *.* TO mysqltest_u1@localhost; +GRANT CREATE USER ON *.* TO mysqltest_u1@localhost; +GRANT PROCESS ON *.* TO mysqltest_u1@localhost; +GRANT RELOAD ON *.* TO mysqltest_u1@localhost; +GRANT REPLICATION CLIENT ON *.* TO mysqltest_u1@localhost; +GRANT REPLICATION SLAVE ON *.* TO mysqltest_u1@localhost; +GRANT SHOW DATABASES ON *.* TO mysqltest_u1@localhost; +GRANT SHUTDOWN ON *.* TO mysqltest_u1@localhost; +GRANT USAGE ON *.* TO mysqltest_u1@localhost; + +--echo +SHOW GRANTS FOR mysqltest_u1@localhost; + +--echo +--echo # connection: con1 (mysqltest_u1@mysqltest_db1) +--connect (con1,localhost,mysqltest_u1,,mysqltest_db1) +--connection con1 + +--echo +--error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE TABLE t1; + +--echo +--echo # connection: default +--connection default + +--disconnect con1 + +--echo +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; +SHOW GRANTS FOR mysqltest_u1@localhost; + +--echo +--echo # -- +--echo # -- Check that global SELECT allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT SELECT ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global INSERT allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT INSERT ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global UPDATE allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT UPDATE ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global DELETE allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global CREATE allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT CREATE ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global DROP allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT DROP ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global ALTER allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT ALTER ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global INDEX allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT INDEX ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global REFERENCES allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT REFERENCES ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global GRANT OPTION allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT GRANT OPTION ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global CREATE VIEW allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT CREATE VIEW ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global SHOW VIEW allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT SHOW VIEW ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level SELECT allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level INSERT allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT INSERT ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level UPDATE allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level DELETE allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT DELETE ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level CREATE allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT CREATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level DROP allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT DROP ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level ALTER allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT ALTER ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level INDEX allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT INDEX ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level REFERENCES allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT REFERENCES ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level GRANT OPTION allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT GRANT OPTION ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level CREATE VIEW allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT CREATE VIEW ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level SHOW VIEW allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT SHOW VIEW ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Cleanup. +--echo # -- + +--echo +DROP DATABASE mysqltest_db1; + +DROP USER mysqltest_u1@localhost; + +--echo +--echo # End of Bug#38347. +--echo + + +--echo # +--echo # BUG#11759114 - '51401: GRANT TREATS NONEXISTENT FUNCTIONS/PRIVILEGES +--echo # DIFFERENTLY'. +--echo # +--disable_warnings +drop database if exists mysqltest_db1; +--enable_warnings +create database mysqltest_db1; +create user mysqltest_u1; +--echo # Both GRANT statements below should fail with the same error. +--error ER_SP_DOES_NOT_EXIST +grant execute on function mysqltest_db1.f1 to mysqltest_u1; +--error ER_SP_DOES_NOT_EXIST +grant execute on procedure mysqltest_db1.p1 to mysqltest_u1; +--echo # Let us show that GRANT behaviour for routines is consistent +--echo # with GRANT behaviour for tables. Attempt to grant privilege +--echo # on non-existent table also results in an error. +--error ER_NO_SUCH_TABLE +grant select on mysqltest_db1.t1 to mysqltest_u1; +show grants for mysqltest_u1; +drop database mysqltest_db1; +drop user mysqltest_u1; + + +--echo # +--echo # Bug#12766319 - 61865: RENAME USER DOES NOT WORK CORRECTLY - +--echo # REQUIRES FLUSH PRIVILEGES +--echo # + +CREATE USER foo@'127.0.0.1'; +GRANT ALL ON *.* TO foo@'127.0.0.1'; + +--echo # First attempt, should connect successfully +connect (conn1, '127.0.0.1', foo,,test); +SELECT user(), current_user(); + +--echo # Rename the user +RENAME USER foo@'127.0.0.1' to foo@'127.0.0.0/255.0.0.0'; + +--echo # Second attempt, should connect successfully as its valid mask +--echo # This was failing without fix +connect (conn2, '127.0.0.1', foo,,test); +SELECT user(), current_user(); + +--echo # Rename the user back to original +RENAME USER foo@'127.0.0.0/255.0.0.0' to foo@'127.0.0.1'; + +--echo # Third attempt, should connect successfully +connect (conn3, '127.0.0.1', foo,,test); +SELECT user(), current_user(); + +--echo # Clean-up +connection default; +disconnect conn1; +disconnect conn2; +disconnect conn3; +DROP USER foo@'127.0.0.1'; + +--echo # End of Bug#12766319 + +# +# Bug#27230925: HANDLE_FATAL_SIGNAL (SIG=11) IN SHOW_ROUTINE_GRANTS +# +create user foo@localhost; +create database foodb; +grant create routine on foodb.* to foo@localhost; +connect con1,localhost,foo; +create procedure fooproc() select 'i am fooproc'; +show grants; +disconnect con1; +connection default; +rename table mysql.procs_priv to mysql.procs_priv1; +flush privileges; +show grants for foo@localhost; +rename table mysql.procs_priv1 to mysql.procs_priv; +show grants for foo@localhost; +flush privileges; +show grants for foo@localhost; +drop user foo@localhost; +drop procedure fooproc; +drop database foodb; + + +--echo # +--echo # Bug#11756966 - 48958: STORED PROCEDURES CAN BE LEVERAGED TO BYPASS +--echo # DATABASE SECURITY +--echo # + +--disable_warnings +DROP DATABASE IF EXISTS secret; +DROP DATABASE IF EXISTS no_such_db; +--enable_warnings + +CREATE DATABASE secret; +GRANT USAGE ON *.* TO untrusted@localhost; + +--echo # Connection con1 +connect (con1, localhost, untrusted); +SHOW GRANTS; +SHOW DATABASES; + +--echo # Both statements below should fail with the same error. +--echo # They used to give different errors, thereby +--echo # hinting that the secret database exists. +--error ER_DBACCESS_DENIED_ERROR +CREATE PROCEDURE no_such_db.foo() BEGIN END; +--error ER_DBACCESS_DENIED_ERROR +CREATE PROCEDURE secret.peek_at_secret() BEGIN END; + +--echo # Connection default +--connection default +disconnect con1; +DROP USER untrusted@localhost; +DROP DATABASE secret; + +# Wait till we reached the initial number of concurrent sessions +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/r/innodb_mrr_cpk.result b/mysql-test/r/innodb_mrr_cpk.result index 99ed73a3e83..355eae70edb 100644 --- a/mysql-test/r/innodb_mrr_cpk.result +++ b/mysql-test/r/innodb_mrr_cpk.result @@ -226,7 +226,7 @@ set join_cache_level=3; explain SELECT 1 FROM (SELECT url, id FROM t2 LIMIT 1 OFFSET 20) derived RIGHT JOIN t1 ON t1.id = derived.id; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # -1 PRIMARY <derived2> hash_ALL key0 #hash#key0 25 test.t1.id # Using join buffer (flat, BNLH join) +1 PRIMARY <derived2> ref key0 key0 25 test.t1.id # 2 DERIVED t2 ALL NULL NULL NULL NULL # set join_cache_level= @tmp_mdev5037; drop table t0,t1,t2; diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index d065403371d..35713443ee7 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -1516,11 +1516,13 @@ ERROR 42S22: Unknown column 'f' in 'from clause' DROP TABLE t; CREATE TABLE t (f INT); CALL p; -ERROR 42S22: Unknown column 'f' in 'from clause' +f DROP TABLE t; CREATE TABLE t (i INT); CALL p; -ERROR 42S22: Unknown column 'f' in 'from clause' +ERROR 42S22: Unknown column 't1.f' in 'field list' +CALL p; +ERROR 42S22: Unknown column 't1.f' in 'field list' DROP PROCEDURE p; DROP TABLE t; CREATE TABLE t1 (a INT, b INT); diff --git a/mysql-test/r/lowercase_fs_off.result b/mysql-test/r/lowercase_fs_off.result index d7e1b8c9e5b..785596c7d2f 100644 --- a/mysql-test/r/lowercase_fs_off.result +++ b/mysql-test/r/lowercase_fs_off.result @@ -63,6 +63,68 @@ CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a= 1; RENAME TABLE t1 TO T1; ALTER TABLE T1 RENAME t1; DROP TABLE t1; +create database TEST; +create procedure TEST.pr() begin end; +create procedure test.pr() begin end; +Phase 1/6: Checking and upgrading mysql database +Processing databases +mysql +mysql.column_stats OK +mysql.columns_priv OK +mysql.db OK +mysql.event OK +mysql.func OK +mysql.gtid_slave_pos OK +mysql.help_category OK +mysql.help_keyword OK +mysql.help_relation OK +mysql.help_topic OK +mysql.host OK +mysql.index_stats OK +mysql.innodb_index_stats +Error : Unknown storage engine 'InnoDB' +error : Corrupt +mysql.innodb_table_stats +Error : Unknown storage engine 'InnoDB' +error : Corrupt +mysql.plugin OK +mysql.proc OK +mysql.procs_priv OK +mysql.proxies_priv OK +mysql.roles_mapping OK +mysql.servers OK +mysql.table_stats OK +mysql.tables_priv OK +mysql.time_zone OK +mysql.time_zone_leap_second OK +mysql.time_zone_name OK +mysql.time_zone_transition OK +mysql.time_zone_transition_type OK +mysql.user OK + +Repairing tables +mysql.innodb_index_stats +Error : Unknown storage engine 'InnoDB' +error : Corrupt +mysql.innodb_table_stats +Error : Unknown storage engine 'InnoDB' +error : Corrupt +Phase 2/6: Fixing views +Phase 3/6: Running 'mysql_fix_privilege_tables' +Phase 4/6: Fixing table and database names +Phase 5/6: Checking and upgrading tables +Processing databases +TEST +information_schema +mtr +mtr.global_suppressions OK +mtr.test_suppressions OK +performance_schema +test +Phase 6/6: Running 'FLUSH PRIVILEGES' +OK +drop procedure test.pr; +drop database TEST; create table t1 (a int); create trigger t1_bi before insert on t1 for each row set new.a= 1; show triggers like '%T1%'; diff --git a/mysql-test/r/lowercase_fs_off.test b/mysql-test/r/lowercase_fs_off.test new file mode 100644 index 00000000000..f4df5e8188d --- /dev/null +++ b/mysql-test/r/lowercase_fs_off.test @@ -0,0 +1,124 @@ +# +# Specific tests for case sensitive file systems +# i.e. lower_case_filesystem=OFF +# +-- source include/have_case_sensitive_file_system.inc +-- source include/not_embedded.inc + +connect (master,localhost,root,,); +connection master; +create database d1; +grant all on d1.* to 'sample'@'localhost' identified by 'password'; +flush privileges; + +connect (sample,localhost,sample,password,d1); +connection sample; +select database(); +--error ER_DBACCESS_DENIED_ERROR +create database d2; +--error ER_DBACCESS_DENIED_ERROR +create database D1; +disconnect sample; +--source include/wait_until_disconnected.inc + +connection master; +drop user 'sample'@'localhost'; +drop database if exists d1; +disconnect master; +--source include/wait_until_disconnected.inc +connection default; + +# End of 4.1 tests + +# +# Bug#41049 does syntax "grant" case insensitive? +# +CREATE DATABASE d1; +USE d1; +CREATE TABLE T1(f1 INT); +CREATE TABLE t1(f1 INT); +GRANT SELECT ON T1 to user_1@localhost; + +connect (con1,localhost,user_1,,d1); +--error ER_TABLEACCESS_DENIED_ERROR +select * from t1; +select * from T1; +connection default; +GRANT SELECT ON t1 to user_1@localhost; +connection con1; +select * from information_schema.table_privileges; +connection default; +disconnect con1; + +REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_1@localhost; +DROP USER user_1@localhost; +DROP DATABASE d1; +USE test; + +CREATE DATABASE db1; +USE db1; +CREATE PROCEDURE p1() BEGIN END; +CREATE FUNCTION f1(i INT) RETURNS INT RETURN i+1; + +GRANT USAGE ON db1.* to user_1@localhost; +GRANT EXECUTE ON PROCEDURE db1.P1 to user_1@localhost; +GRANT EXECUTE ON FUNCTION db1.f1 to user_1@localhost; +GRANT UPDATE ON db1.* to USER_1@localhost; + +connect (con1,localhost,user_1,,db1); +call p1(); +call P1(); +select f1(1); +connect (con2,localhost,USER_1,,db1); +--error ER_PROCACCESS_DENIED_ERROR +call p1(); +--error ER_PROCACCESS_DENIED_ERROR +call P1(); +--error ER_PROCACCESS_DENIED_ERROR +select f1(1); + +connection default; +disconnect con1; +disconnect con2; + +REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_1@localhost; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM USER_1@localhost; +DROP FUNCTION f1; +DROP PROCEDURE p1; +DROP USER user_1@localhost; +DROP USER USER_1@localhost; +DROP DATABASE db1; +use test; + +# End of 5.0 tests + + +--echo # +--echo # Extra test coverage for Bug#56595 RENAME TABLE causes assert on OS X +--echo # + +CREATE TABLE t1(a INT); +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a= 1; +RENAME TABLE t1 TO T1; +ALTER TABLE T1 RENAME t1; +DROP TABLE t1; + +# +# MDEV-13912 mysql_upgrade: case (in)sensitivity for stored procedures +# +create database TEST; +create procedure TEST.pr() begin end; +create procedure test.pr() begin end; +--exec $MYSQL_UPGRADE --force 2>&1 +drop procedure test.pr; +drop database TEST; + +# End of 5.5 tests + +# +# MDEV-9014 SHOW TRIGGERS not case sensitive +# +create table t1 (a int); +create trigger t1_bi before insert on t1 for each row set new.a= 1; +show triggers like '%T1%'; +drop table t1; diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index 3c1e1287c0c..a1cc93e9c9f 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -1067,6 +1067,12 @@ The following specify which files/extra groups are read (specified before remain --wsrep-causal-reads (DEPRECATED) Setting this variable is equivalent to setting wsrep_sync_wait READ flag + --wsrep-certification-rules=name + Certification rules to use in the cluster. Possible + values are: "strict": stricter rules that could result in + more certification failures. "optimized": relaxed rules + that allow more concurrency and cause less certification + failures. --wsrep-certify-nonPK Certify tables with no primary key (Defaults to on; use --skip-wsrep-certify-nonPK to disable.) @@ -1451,6 +1457,7 @@ wait-timeout 28800 wsrep-OSU-method TOI wsrep-auto-increment-control TRUE wsrep-causal-reads FALSE +wsrep-certification-rules strict wsrep-certify-nonPK TRUE wsrep-cluster-address wsrep-cluster-name my_wsrep_cluster diff --git a/mysql-test/r/sp-security.result b/mysql-test/r/sp-security.result index e8c3fbff0e3..4125762e622 100644 --- a/mysql-test/r/sp-security.result +++ b/mysql-test/r/sp-security.result @@ -617,6 +617,29 @@ SELECT 1 latin1 latin1_swedish_ci latin1_swedish_ci # Connection default DROP USER user2@localhost; DROP DATABASE db1; +create user foo@local_ost; +create user foo@`local\_ost`; +update mysql.user set plugin='foobar' where host='local\\_ost'; +flush privileges; +create database foodb; +grant create routine on foodb.* to foo@local_ost; +select user(), current_user(); +user() current_user() +foo@localhost foo@local_ost +show grants; +Grants for foo@local_ost +GRANT USAGE ON *.* TO 'foo'@'local_ost' +GRANT CREATE ROUTINE ON `foodb`.* TO 'foo'@'local_ost' +create procedure fooproc() select 'i am fooproc'; +show grants; +Grants for foo@local_ost +GRANT USAGE ON *.* TO 'foo'@'local_ost' +GRANT CREATE ROUTINE ON `foodb`.* TO 'foo'@'local_ost' +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`fooproc` TO 'foo'@'local_ost' +drop user foo@local_ost; +drop user foo@`local\_ost`; +drop procedure fooproc; +drop database foodb; # # Test for bug#12602983 - User without privilege on routine can discover # its existence by executing "select non_existing_func();" or by diff --git a/mysql-test/r/sp-security.test b/mysql-test/r/sp-security.test new file mode 100644 index 00000000000..53dc4f8c7ac --- /dev/null +++ b/mysql-test/r/sp-security.test @@ -0,0 +1,1069 @@ +# +# Testing SQL SECURITY of stored procedures +# + +# Can't test with embedded server that doesn't support grants +-- source include/not_embedded.inc + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + +connect (con1root,localhost,root,,); + +connection con1root; +use test; + +# Create user user1 with no particular access rights +grant usage on *.* to user1@localhost; +flush privileges; + +--disable_warnings +drop table if exists t1; +drop database if exists db1_secret; +--enable_warnings +# Create our secret database +create database db1_secret; + +# Can create a procedure in other db +create procedure db1_secret.dummy() begin end; +drop procedure db1_secret.dummy; + +use db1_secret; + +create table t1 ( u varchar(64), i int ); +insert into t1 values('test', 0); + +# A test procedure and function +create procedure stamp(i int) + insert into db1_secret.t1 values (user(), i); +--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' +show procedure status like 'stamp'; + +delimiter |; +create function db() returns varchar(64) +begin + declare v varchar(64); + + select u into v from t1 limit 1; + + return v; +end| +delimiter ;| +--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' +show function status like 'db'; + +# root can, of course +call stamp(1); +select * from t1; +select db(); + +grant execute on procedure db1_secret.stamp to user1@'%'; +grant execute on function db1_secret.db to user1@'%'; +grant execute on procedure db1_secret.stamp to ''@'%'; +grant execute on function db1_secret.db to ''@'%'; + +connect (con2user1,localhost,user1,,); +connect (con3anon,localhost,anon,,); + + +# +# User1 can +# +connection con2user1; + +# This should work... +call db1_secret.stamp(2); +select db1_secret.db(); + +# ...but not this +--error ER_TABLEACCESS_DENIED_ERROR +select * from db1_secret.t1; + +# ...and not this +--error ER_DBACCESS_DENIED_ERROR +create procedure db1_secret.dummy() begin end; +--error ER_PROCACCESS_DENIED_ERROR +drop procedure db1_secret.dummy; +--error ER_PROCACCESS_DENIED_ERROR +drop procedure db1_secret.stamp; +--error ER_PROCACCESS_DENIED_ERROR +drop function db1_secret.db; + + +# +# Anonymous can +# +connection con3anon; + +# This should work... +call db1_secret.stamp(3); +select db1_secret.db(); + +# ...but not this +--error ER_TABLEACCESS_DENIED_ERROR +select * from db1_secret.t1; + +# ...and not this +--error ER_DBACCESS_DENIED_ERROR +create procedure db1_secret.dummy() begin end; +--error ER_PROCACCESS_DENIED_ERROR +drop procedure db1_secret.dummy; +--error ER_PROCACCESS_DENIED_ERROR +drop procedure db1_secret.stamp; +--error ER_PROCACCESS_DENIED_ERROR +drop function db1_secret.db; + + +# +# Check it out +# +connection con1root; +select * from t1; + +# +# Change to invoker's rights +# +alter procedure stamp sql security invoker; +--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' +show procedure status like 'stamp'; + +alter function db sql security invoker; +--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' +show function status like 'db'; + +# root still can +call stamp(4); +select * from t1; +select db(); + +# +# User1 cannot +# +connection con2user1; + +# This should not work +--error ER_TABLEACCESS_DENIED_ERROR +call db1_secret.stamp(5); +--error ER_TABLEACCESS_DENIED_ERROR +select db1_secret.db(); + +# +# Anonymous cannot +# +connection con3anon; + +# This should not work +--error ER_TABLEACCESS_DENIED_ERROR +call db1_secret.stamp(6); +--error ER_TABLEACCESS_DENIED_ERROR +select db1_secret.db(); + +# +# Bug#2777 Stored procedure doesn't observe definer's rights +# + +connection con1root; +--disable_warnings +drop database if exists db2; +--enable_warnings +create database db2; + +use db2; + +create table t2 (s1 int); +insert into t2 values (0); + +grant usage on db2.* to user1@localhost; +grant select on db2.* to user1@localhost; +grant usage on db2.* to user2@localhost; +grant select,insert,update,delete,create routine on db2.* to user2@localhost; +grant create routine on db2.* to user1@localhost; +flush privileges; + +connection con2user1; +use db2; + +create procedure p () insert into t2 values (1); + +# Check that this doesn't work. +--error ER_TABLEACCESS_DENIED_ERROR +call p(); + +connect (con4user2,localhost,user2,,); + +connection con4user2; +use db2; + +# This should not work, since p is executed with definer's (user1's) rights. +--error ER_PROCACCESS_DENIED_ERROR +call p(); +select * from t2; + +create procedure q () insert into t2 values (2); + +call q(); +select * from t2; + +connection con1root; +grant usage on procedure db2.q to user2@localhost with grant option; + +connection con4user2; +grant execute on procedure db2.q to user1@localhost; + +connection con2user1; +use db2; + +# This should work +call q(); +select * from t2; + +# +# Bug#6030 Stored procedure has no appropriate DROP privilege +# (or ALTER for that matter) + +# still connection con2user1 in db2 + +# This should work: +alter procedure p modifies sql data; +drop procedure p; + +# This should NOT work +--error ER_PROCACCESS_DENIED_ERROR +alter procedure q modifies sql data; +--error ER_PROCACCESS_DENIED_ERROR +drop procedure q; + +connection con1root; +use db2; +# But root always can +alter procedure q modifies sql data; +drop procedure q; + + +# Clean up +#Still connection con1root; +disconnect con2user1; +disconnect con3anon; +disconnect con4user2; +use test; +select type,db,name from mysql.proc where db like 'db%'; +drop database db1_secret; +drop database db2; +# Make sure the routines are gone +select type,db,name from mysql.proc where db like 'db%'; +# Get rid of the users +delete from mysql.user where user='user1' or user='user2'; +delete from mysql.user where user='' and host='%'; +# And any routine privileges +delete from mysql.procs_priv where user='user1' or user='user2'; +# Delete the grants to user ''@'%' that was created above +delete from mysql.procs_priv where user='' and host='%'; +delete from mysql.db where user='user2'; +flush privileges; +# +# Test the new security acls +# +grant usage on *.* to usera@localhost; +grant usage on *.* to userb@localhost; +grant usage on *.* to userc@localhost; +create database sptest; +create table t1 ( u varchar(64), i int ); +create procedure sptest.p1(i int) insert into test.t1 values (user(), i); +grant insert on t1 to usera@localhost; +grant execute on procedure sptest.p1 to usera@localhost; +show grants for usera@localhost; +grant execute on procedure sptest.p1 to userc@localhost with grant option; +show grants for userc@localhost; + +connect (con2usera,localhost,usera,,); +connect (con3userb,localhost,userb,,); +connect (con4userc,localhost,userc,,); + +connection con2usera; +call sptest.p1(1); +--error ER_PROCACCESS_DENIED_ERROR +grant execute on procedure sptest.p1 to userb@localhost; +--error ER_PROCACCESS_DENIED_ERROR +drop procedure sptest.p1; + +connection con3userb; +--error ER_PROCACCESS_DENIED_ERROR +call sptest.p1(2); +--error ER_PROCACCESS_DENIED_ERROR +grant execute on procedure sptest.p1 to userb@localhost; +--error ER_PROCACCESS_DENIED_ERROR +drop procedure sptest.p1; + +connection con4userc; +call sptest.p1(3); +grant execute on procedure sptest.p1 to userb@localhost; +--error ER_PROCACCESS_DENIED_ERROR +drop procedure sptest.p1; + +connection con3userb; +call sptest.p1(4); +--error ER_PROCACCESS_DENIED_ERROR +grant execute on procedure sptest.p1 to userb@localhost; +--error ER_PROCACCESS_DENIED_ERROR +drop procedure sptest.p1; + +connection con1root; +select * from t1; + +grant all privileges on procedure sptest.p1 to userc@localhost; +show grants for userc@localhost; +show grants for userb@localhost; + +connection con4userc; +revoke all privileges on procedure sptest.p1 from userb@localhost; + +connection con1root; +show grants for userb@localhost; + +#cleanup +disconnect con4userc; +disconnect con3userb; +disconnect con2usera; +use test; +drop database sptest; +delete from mysql.user where user='usera' or user='userb' or user='userc'; +delete from mysql.procs_priv where user='usera' or user='userb' or user='userc'; +delete from mysql.tables_priv where user='usera'; +flush privileges; +drop table t1; + +# +# Bug#9503 reseting correct parameters of thread after error in SP function +# +connect (root,localhost,root,,test); +connection root; + +--disable_warnings +drop function if exists bug_9503; +--enable_warnings +delimiter //; +create database mysqltest// +use mysqltest// +create table t1 (s1 int)// +grant select on t1 to user1@localhost// +create function bug_9503 () returns int sql security invoker begin declare v int; +select min(s1) into v from t1; return v; end// +delimiter ;// + +connect (user1,localhost,user1,,test); +connection user1; +use mysqltest; +-- error ER_PROCACCESS_DENIED_ERROR +select bug_9503(); + +connection root; +grant execute on function bug_9503 to user1@localhost; + +connection user1; +do 1; +use test; + +disconnect user1; +connection root; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost; +drop function bug_9503; +use test; +drop database mysqltest; +connection default; +disconnect root; + +# +# correct value from current_user() in function run from "security definer" +# (Bug#7291 Stored procedures: wrong CURRENT_USER value) +# +connection con1root; +use test; + +select current_user(); +select user(); +create procedure bug7291_0 () sql security invoker select current_user(), user(); +create procedure bug7291_1 () sql security definer call bug7291_0(); +create procedure bug7291_2 () sql security invoker call bug7291_0(); +grant execute on procedure bug7291_0 to user1@localhost; +grant execute on procedure bug7291_1 to user1@localhost; +grant execute on procedure bug7291_2 to user1@localhost; + +connect (user1,localhost,user1,,); +connection user1; + +call bug7291_2(); +call bug7291_1(); + +connection con1root; +drop procedure bug7291_1; +drop procedure bug7291_2; +drop procedure bug7291_0; +disconnect user1; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost; +drop user user1@localhost; + +# +# Bug#12318 Wrong error message when accessing an inaccessible stored +# procedure in another database when the current database is +# information_schema. +# + +--disable_warnings +drop database if exists mysqltest_1; +--enable_warnings + +create database mysqltest_1; +delimiter //; +create procedure mysqltest_1.p1() +begin + select 1 from dual; +end// +delimiter ;// + +grant usage on *.* to mysqltest_1@localhost; + +connect (n1,localhost,mysqltest_1,,information_schema,$MASTER_MYPORT,$MASTER_MYSOCK); +connection n1; +--error ER_PROCACCESS_DENIED_ERROR +call mysqltest_1.p1(); +disconnect n1; +# Test also without a current database +connect (n2,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,$MASTER_MYSOCK); +connection n2; +--error ER_PROCACCESS_DENIED_ERROR +call mysqltest_1.p1(); +disconnect n2; + +connection default; + +drop procedure mysqltest_1.p1; +drop database mysqltest_1; + +revoke usage on *.* from mysqltest_1@localhost; +drop user mysqltest_1@localhost; + +# +# Bug#12812 create view calling a function works without execute right +# on function +delimiter |; +--disable_warnings +drop function if exists bug12812| +--enable_warnings +create function bug12812() returns char(2) +begin + return 'ok'; +end; +create user user_bug12812@localhost IDENTIFIED BY 'ABC'| +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (test_user_12812,localhost,user_bug12812,ABC,test)| +--error ER_PROCACCESS_DENIED_ERROR +SELECT test.bug12812()| +--error ER_PROCACCESS_DENIED_ERROR +CREATE VIEW v1 AS SELECT test.bug12812()| +# Cleanup +connection default| +disconnect test_user_12812| +DROP USER user_bug12812@localhost| +drop function bug12812| +delimiter ;| + + +# +# Bug#14834 Server denies to execute Stored Procedure +# +# The problem here was with '_' in the database name. +# +create database db_bug14834; + +create user user1_bug14834@localhost identified by ''; +# The exact name of the database (no wildcard) +grant all on `db\_bug14834`.* to user1_bug14834@localhost; + +create user user2_bug14834@localhost identified by ''; +# The exact name of the database (no wildcard) +grant all on `db\_bug14834`.* to user2_bug14834@localhost; + +create user user3_bug14834@localhost identified by ''; +# Wildcards in the database name +grant all on `db__ug14834`.* to user3_bug14834@localhost; + +connect (user1_bug14834,localhost,user1_bug14834,,db_bug14834); +# Create the procedure and check that we can call it +create procedure p_bug14834() select user(), current_user(); +call p_bug14834(); + +connect (user2_bug14834,localhost,user2_bug14834,,db_bug14834); +# This didn't work before +call p_bug14834(); + +connect (user3_bug14834,localhost,user3_bug14834,,db_bug14834); +# Should also work +call p_bug14834(); + +# Cleanup +connection default; +disconnect user1_bug14834; +disconnect user2_bug14834; +disconnect user3_bug14834; +drop user user1_bug14834@localhost; +drop user user2_bug14834@localhost; +drop user user3_bug14834@localhost; +drop database db_bug14834; + + +# +# Bug#14533 'desc tbl' in stored procedure causes error +# ER_TABLEACCESS_DENIED_ERROR +# +create database db_bug14533; +use db_bug14533; +create table t1 (id int); +create user user_bug14533@localhost identified by ''; + +create procedure bug14533_1() + sql security definer + desc db_bug14533.t1; + +create procedure bug14533_2() + sql security definer + select * from db_bug14533.t1; + +grant execute on procedure db_bug14533.bug14533_1 to user_bug14533@localhost; +grant execute on procedure db_bug14533.bug14533_2 to user_bug14533@localhost; + +connect (user_bug14533,localhost,user_bug14533,,test); + +# These should work +call db_bug14533.bug14533_1(); +call db_bug14533.bug14533_2(); + +# For reference, these should not work +--error ER_TABLEACCESS_DENIED_ERROR +desc db_bug14533.t1; +--error ER_TABLEACCESS_DENIED_ERROR +select * from db_bug14533.t1; + +# Cleanup +connection default; +disconnect user_bug14533; +drop user user_bug14533@localhost; +drop database db_bug14533; + + +# +# WL#2897 Complete definer support in the stored routines. +# +# The following cases are tested: +# 1. check that if DEFINER-clause is not explicitly specified, stored routines +# are created with CURRENT_USER privileges; +# 2. check that if DEFINER-clause specifies non-current user, SUPER privilege +# is required to create a stored routine; +# 3. check that if DEFINER-clause specifies non-existent user, a warning is +# emitted. +# 4. check that SHOW CREATE PROCEDURE | FUNCTION works correctly; +# +# The following cases are tested in other test suites: +# - check that mysqldump dumps new attribute correctly; +# - check that slave replicates CREATE-statements with explicitly specified +# DEFINER correctly. +# + +# Setup the environment. + +--echo +--echo ---> connection: root +--connection con1root + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest; +--enable_warnings + +CREATE DATABASE mysqltest; + +CREATE USER mysqltest_1@localhost; +GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_1@localhost; + +CREATE USER mysqltest_2@localhost; +GRANT SUPER ON *.* TO mysqltest_2@localhost; +GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_2@localhost; + +--connect (mysqltest_2_con,localhost,mysqltest_2,,mysqltest) +--connect (mysqltest_1_con,localhost,mysqltest_1,,mysqltest) + +# test case (1). + +--echo +--echo ---> connection: mysqltest_2_con +--connection mysqltest_2_con + +USE mysqltest; + +CREATE PROCEDURE wl2897_p1() SELECT 1; + +CREATE FUNCTION wl2897_f1() RETURNS INT RETURN 1; + +# test case (2). + +--echo +--echo ---> connection: mysqltest_1_con +--connection mysqltest_1_con + +USE mysqltest; + +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +CREATE DEFINER=root@localhost PROCEDURE wl2897_p2() SELECT 2; + +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +CREATE DEFINER=root@localhost FUNCTION wl2897_f2() RETURNS INT RETURN 2; + +# test case (3). + +--echo +--echo ---> connection: mysqltest_2_con +--connection mysqltest_2_con + +use mysqltest; + +CREATE DEFINER='a @ b @ c'@localhost PROCEDURE wl2897_p3() SELECT 3; + +CREATE DEFINER='a @ b @ c'@localhost FUNCTION wl2897_f3() RETURNS INT RETURN 3; + +# test case (4). + +--echo +--echo ---> connection: con1root +--connection con1root + +USE mysqltest; + +SHOW CREATE PROCEDURE wl2897_p1; +SHOW CREATE PROCEDURE wl2897_p3; + +SHOW CREATE FUNCTION wl2897_f1; +SHOW CREATE FUNCTION wl2897_f3; + +# Cleanup. + +DROP USER mysqltest_1@localhost; +DROP USER mysqltest_2@localhost; + +DROP DATABASE mysqltest; + +--disconnect mysqltest_1_con +--disconnect mysqltest_2_con + + +# +# Bug#13198 SP executes if definer does not exist +# + +# Prepare environment. + +--echo +--echo ---> connection: root +--connection con1root + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest; +--enable_warnings + +CREATE DATABASE mysqltest; + +CREATE USER mysqltest_1@localhost; +GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_1@localhost; + +CREATE USER mysqltest_2@localhost; +GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_2@localhost; + +--connect (mysqltest_1_con,localhost,mysqltest_1,,mysqltest) +--connect (mysqltest_2_con,localhost,mysqltest_2,,mysqltest) + +# Create a procedure/function under u1. + +--echo +--echo ---> connection: mysqltest_1_con +--connection mysqltest_1_con + +USE mysqltest; + +CREATE PROCEDURE bug13198_p1() + SELECT 1; + +CREATE FUNCTION bug13198_f1() RETURNS INT + RETURN 1; + +CALL bug13198_p1(); + +SELECT bug13198_f1(); + +# Check that u2 can call the procedure/function. + +--echo +--echo ---> connection: mysqltest_2_con +--connection mysqltest_2_con + +USE mysqltest; + +CALL bug13198_p1(); + +SELECT bug13198_f1(); + +# Drop user u1 (definer of the object); + +--echo +--echo ---> connection: root +--connection con1root + +--disconnect mysqltest_1_con + +DROP USER mysqltest_1@localhost; + +# Check that u2 can not call the procedure/function. + +--echo +--echo ---> connection: mysqltest_2_con +--connection mysqltest_2_con + +USE mysqltest; + +--error ER_NO_SUCH_USER +CALL bug13198_p1(); + +--error ER_NO_SUCH_USER +SELECT bug13198_f1(); + +# Cleanup. + +--echo +--echo ---> connection: root +--connection con1root + +--disconnect mysqltest_2_con + +DROP USER mysqltest_2@localhost; + +DROP DATABASE mysqltest; + +# +# Bug#19857 When a user with CREATE ROUTINE priv creates a routine, +# it results in NULL p/w +# + +# Can't test with embedded server that doesn't support grants + +GRANT USAGE ON *.* TO user19857@localhost IDENTIFIED BY 'meow'; +GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ROUTINE, ALTER ROUTINE ON test.* TO +user19857@localhost; +SELECT Host,User,Password FROM mysql.user WHERE User='user19857'; + +--connect (mysqltest_2_con,localhost,user19857,meow,test) +--echo +--echo ---> connection: mysqltest_2_con +--connection mysqltest_2_con + +USE test; + +DELIMITER //; + CREATE PROCEDURE sp19857() DETERMINISTIC + BEGIN + DECLARE a INT; + SET a=1; + SELECT a; + END // +DELIMITER ;// + +SHOW CREATE PROCEDURE test.sp19857; + +--disconnect mysqltest_2_con +--connect (mysqltest_2_con,localhost,user19857,meow,test) +--connection mysqltest_2_con + +DROP PROCEDURE IF EXISTS test.sp19857; + +--echo +--echo ---> connection: root +--connection con1root + +--disconnect mysqltest_2_con + +SELECT Host,User,Password FROM mysql.user WHERE User='user19857'; + +DROP USER user19857@localhost; + +--disconnect con1root +--connection default +use test; + +# +# Bug#18630 Arguments of suid routine calculated in wrong security context +# +# Arguments of suid routines were calculated in definer's security +# context instead of caller's context thus creating security hole. +# +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP VIEW IF EXISTS v1; +DROP FUNCTION IF EXISTS f_suid; +DROP PROCEDURE IF EXISTS p_suid; +DROP FUNCTION IF EXISTS f_evil; +--enable_warnings +DELETE FROM mysql.user WHERE user LIKE 'mysqltest\_%'; +DELETE FROM mysql.db WHERE user LIKE 'mysqltest\_%'; +DELETE FROM mysql.tables_priv WHERE user LIKE 'mysqltest\_%'; +DELETE FROM mysql.columns_priv WHERE user LIKE 'mysqltest\_%'; +FLUSH PRIVILEGES; + +CREATE TABLE t1 (i INT); +CREATE FUNCTION f_suid(i INT) RETURNS INT SQL SECURITY DEFINER RETURN 0; +CREATE PROCEDURE p_suid(IN i INT) SQL SECURITY DEFINER SET @c:= 0; + +CREATE USER mysqltest_u1@localhost; +# Thanks to this grant statement privileges of anonymous users on +# 'test' database are not applicable for mysqltest_u1@localhost. +GRANT EXECUTE ON test.* TO mysqltest_u1@localhost; + +delimiter |; +CREATE DEFINER=mysqltest_u1@localhost FUNCTION f_evil () RETURNS INT + SQL SECURITY INVOKER +BEGIN + SET @a:= CURRENT_USER(); + SET @b:= (SELECT COUNT(*) FROM t1); + RETURN @b; +END| +delimiter ;| + +CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT f_evil(); + +connect (conn1, localhost, mysqltest_u1,,); + +--error ER_TABLEACCESS_DENIED_ERROR +SELECT COUNT(*) FROM t1; + +--error ER_TABLEACCESS_DENIED_ERROR +SELECT f_evil(); +SELECT @a, @b; + +--error ER_TABLEACCESS_DENIED_ERROR +SELECT f_suid(f_evil()); +SELECT @a, @b; + +--error ER_TABLEACCESS_DENIED_ERROR +CALL p_suid(f_evil()); +SELECT @a, @b; + +--error ER_TABLEACCESS_DENIED_ERROR +SELECT * FROM v1; +SELECT @a, @b; + +disconnect conn1; +connection default; + +DROP VIEW v1; +DROP FUNCTION f_evil; +DROP USER mysqltest_u1@localhost; +DROP PROCEDURE p_suid; +DROP FUNCTION f_suid; +DROP TABLE t1; + +--echo # +--echo # Bug #48872 : Privileges for stored functions ignored if function name +--echo # is mixed case +--echo # + +CREATE DATABASE B48872; +USE B48872; +CREATE TABLE `TestTab` (id INT); +INSERT INTO `TestTab` VALUES (1),(2); +CREATE FUNCTION `f_Test`() RETURNS INT RETURN 123; +CREATE FUNCTION `f_Test_denied`() RETURNS INT RETURN 123; +CREATE USER 'tester'; +CREATE USER 'Tester'; +GRANT SELECT ON TABLE `TestTab` TO 'tester'; +GRANT EXECUTE ON FUNCTION `f_Test` TO 'tester'; +GRANT EXECUTE ON FUNCTION `f_Test_denied` TO 'Tester'; + +SELECT f_Test(); +SELECT * FROM TestTab; + +CONNECT (con_tester,localhost,tester,,B48872); +CONNECT (con_tester_denied,localhost,Tester,,B48872); +CONNECTION con_tester; + +SELECT * FROM TestTab; +SELECT `f_Test`(); +SELECT `F_TEST`(); +SELECT f_Test(); +SELECT F_TEST(); + +CONNECTION con_tester_denied; + +--disable_result_log +--error ER_TABLEACCESS_DENIED_ERROR +SELECT * FROM TestTab; +--error ER_PROCACCESS_DENIED_ERROR +SELECT `f_Test`(); +--error ER_PROCACCESS_DENIED_ERROR +SELECT `F_TEST`(); +--error ER_PROCACCESS_DENIED_ERROR +SELECT f_Test(); +--error ER_PROCACCESS_DENIED_ERROR +SELECT F_TEST(); +--enable_result_log +SELECT `f_Test_denied`(); +SELECT `F_TEST_DENIED`(); + +CONNECTION default; +DISCONNECT con_tester; +DISCONNECT con_tester_denied; +DROP TABLE `TestTab`; +DROP FUNCTION `f_Test`; +DROP FUNCTION `f_Test_denied`; + +USE test; +DROP USER 'tester'; +DROP USER 'Tester'; +DROP DATABASE B48872; + +--echo End of 5.0 tests. + + +--echo # +--echo # Test for bug#57061 "User without privilege on routine can discover +--echo # its existence." +--echo # +--disable_warnings +drop database if exists mysqltest_db; +--enable_warnings +create database mysqltest_db; +--echo # Create user with no privileges on mysqltest_db database. +create user bug57061_user@localhost; +create function mysqltest_db.f1() returns int return 0; +create procedure mysqltest_db.p1() begin end; +--echo # Connect as user 'bug57061_user@localhost' +connect (conn1, localhost, bug57061_user,,); +--echo # Attempt to drop routine on which user doesn't have privileges +--echo # should result in the same 'access denied' type of error whether +--echo # routine exists or not. +--error ER_PROCACCESS_DENIED_ERROR +drop function if exists mysqltest_db.f_does_not_exist; +--error ER_PROCACCESS_DENIED_ERROR +drop procedure if exists mysqltest_db.p_does_not_exist; +--error ER_PROCACCESS_DENIED_ERROR +drop function if exists mysqltest_db.f1; +--error ER_PROCACCESS_DENIED_ERROR +drop procedure if exists mysqltest_db.p1; +--echo # Connection 'default'. +connection default; +disconnect conn1; +drop user bug57061_user@localhost; +drop database mysqltest_db; + + +--echo # +--echo # Bug#11882603 SELECT_ACL ON ANY COLUMN IN MYSQL.PROC ALLOWS TO SEE +--echo # DEFINITION OF ANY ROUTINE. +--echo # + +--disable_warnings +DROP DATABASE IF EXISTS db1; +--enable_warnings + +CREATE DATABASE db1; +CREATE PROCEDURE db1.p1() SELECT 1; +CREATE USER user2@localhost IDENTIFIED BY ''; +GRANT SELECT(db) ON mysql.proc TO user2@localhost; + +--echo # Connection con2 as user2 +connect (con2, localhost, user2); +--echo # The statement below before disclosed info from body_utf8 column. +--error ER_SP_DOES_NOT_EXIST +SHOW CREATE PROCEDURE db1.p1; + +--echo # Check that SHOW works with SELECT grant on whole table +--echo # Connection default +connection default; +GRANT SELECT ON mysql.proc TO user2@localhost; + +--echo # Connection con2 +connection con2; +--echo # This should work +SHOW CREATE PROCEDURE db1.p1; + +--echo # Connection default +connection default; +disconnect con2; +DROP USER user2@localhost; +DROP DATABASE db1; + +# +# Bug#27407480: AUTOMATIC_SP_PRIVILEGES REQUIRES NEED THE INSERT PRIVILEGES FOR MYSQL.USER TABLE +# +create user foo@local_ost; +# +# Create a user with an authentification plugin 'foobar'. +# Instead of using a normal "CREATE USER <user> IDENTIFIED VIA <plugin>" +# we do CREATE (without VIA) followed by UPDATE and FLUSH. +# This is to avoid installing a real plugin and thus avoid the test dependency. +# We won't login under this user in the below test, so this is fine. +# +create user foo@`local\_ost`; +update mysql.user set plugin='foobar' where host='local\\_ost'; +flush privileges; +create database foodb; +grant create routine on foodb.* to foo@local_ost; +connect con1,localhost,foo; +select user(), current_user(); +show grants; +create procedure fooproc() select 'i am fooproc'; +show grants; +disconnect con1; +connection default; +drop user foo@local_ost; +drop user foo@`local\_ost`; +drop procedure fooproc; +drop database foodb; + +--echo # +--echo # Test for bug#12602983 - User without privilege on routine can discover +--echo # its existence by executing "select non_existing_func();" or by +--echo # "call non_existing_proc()"; +--echo # +--disable_warnings +drop database if exists mysqltest_db; +--enable_warnings +create database mysqltest_db; +create function mysqltest_db.f1() returns int return 0; +create procedure mysqltest_db.p1() begin end; + +--echo # Create user with no privileges on mysqltest_db database. +create user bug12602983_user@localhost; + +--echo # Connect as user 'bug12602983_user@localhost' +connect (conn1, localhost, bug12602983_user,,); + +--echo # Attempt to execute routine on which user doesn't have privileges +--echo # should result in the same 'access denied' error whether +--echo # routine exists or not. +--error ER_PROCACCESS_DENIED_ERROR +select mysqltest_db.f_does_not_exist(); +--error ER_PROCACCESS_DENIED_ERROR +call mysqltest_db.p_does_not_exist(); + +--error ER_PROCACCESS_DENIED_ERROR +select mysqltest_db.f1(); +--error ER_PROCACCESS_DENIED_ERROR +call mysqltest_db.p1(); + +--error ER_PROCACCESS_DENIED_ERROR +create view bug12602983_v1 as select mysqltest_db.f_does_not_exist(); +--error ER_PROCACCESS_DENIED_ERROR +create view bug12602983_v1 as select mysqltest_db.f1(); + +--echo # Connection 'default'. +connection default; +disconnect conn1; +drop user bug12602983_user@localhost; +drop database mysqltest_db; + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 2cb1b701e2d..058ef69c548 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -7898,6 +7898,23 @@ SET S.CLOSE_YN = '' where 1=1; drop function if exists f1; drop table t1,t2; +# +# MDEV-16957: Server crashes in Field_iterator_natural_join::next +# upon 2nd execution of SP +# +CREATE TABLE t1 (a INT, b VARCHAR(32)); +CREATE PROCEDURE sp() SELECT * FROM t1 AS t1x JOIN t1 AS t1y USING (c); +CALL sp; +ERROR 42S22: Unknown column 'c' in 'from clause' +CALL sp; +ERROR 42S22: Unknown column 'c' in 'from clause' +CALL sp; +ERROR 42S22: Unknown column 'c' in 'from clause' +alter table t1 add column c int; +CALL sp; +c a b a b +DROP PROCEDURE sp; +DROP TABLE t1; # End of 5.5 test # # MDEV-7040: Crash in field_conv, memcpy_field_possible, part#2 diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index 05c4efff920..aa0ac73abd2 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -499,7 +499,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull(/*always not null*/ 1)) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull(/*always not null*/ 1)) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); a1 a2 1 - 01 2 - 01 @@ -509,7 +509,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull(/*always not null*/ 1)) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull(/*always not null*/ 1)) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual); a1 a2 1 - 01 2 - 01 @@ -1896,7 +1896,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (<cache>(isnull(`<subquery2>`.`MAX(c)`)) or (`<subquery2>`.`MAX(c)` = 7))) +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (<cache>(isnull(/*always not null*/ 1)) or (`<subquery2>`.`MAX(c)` = 7))) SELECT * FROM t1 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); a b diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index 05d54c7086f..b48be32441a 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -520,7 +520,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull(/*always not null*/ 1)) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull(/*always not null*/ 1)) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); a1 a2 1 - 01 2 - 01 @@ -530,7 +530,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull(/*always not null*/ 1)) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull(/*always not null*/ 1)) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual); a1 a2 1 - 01 2 - 01 @@ -1934,7 +1934,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (<cache>(isnull(`<subquery2>`.`MAX(c)`)) or (`<subquery2>`.`MAX(c)` = 7))) +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (<cache>(isnull(/*always not null*/ 1)) or (`<subquery2>`.`MAX(c)` = 7))) SELECT * FROM t1 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); a b diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result index 57e2660750b..f71d4c13a0d 100644 --- a/mysql-test/r/type_float.result +++ b/mysql-test/r/type_float.result @@ -448,6 +448,42 @@ select format(truncate('1.7976931348623157E+308',-12),1,'fr_BE') as foo; foo 0 # +# MDEV-17249 MAKETIME(-1e50,0,0) returns a wrong result +# +SELECT LEFT('a',EXP(50)); +LEFT('a',EXP(50)) +a +SELECT LEFT('a', COALESCE(1e30)); +LEFT('a', COALESCE(1e30)) +a +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES (1e30); +SELECT LEFT('a',a), LEFT('a',1e30) FROM t1; +LEFT('a',a) LEFT('a',1e30) +a a +DROP TABLE t1; +PREPARE stmt FROM 'SELECT LEFT(111,?)'; +SET @a=1e30; +EXECUTE stmt USING @a; +LEFT(111,?) +111 +DEALLOCATE PREPARE stmt; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT LEFT('a',(SELECT 1e30 FROM t1 LIMIT 1)); +LEFT('a',(SELECT 1e30 FROM t1 LIMIT 1)) +a +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +INSERT INTO t1 VALUES (1e30),(0); +SELECT LEFT('a', SUM(a)) FROM t1; +LEFT('a', SUM(a)) +a +SELECT LEFT('a', AVG(a)) FROM t1; +LEFT('a', AVG(a)) +a +DROP TABLE t1; +# # Bug #13500371 63704: CONVERSION OF '1.' TO A NUMBER GIVES ERROR 1265 # (WARN_DATA_TRUNCATED) # diff --git a/mysql-test/r/type_float.test b/mysql-test/r/type_float.test new file mode 100644 index 00000000000..5dfb4a75bb3 --- /dev/null +++ b/mysql-test/r/type_float.test @@ -0,0 +1,417 @@ +# Description +# ----------- +# Numeric floating point. + +--disable_warnings +drop table if exists t1,t2; +--enable_warnings + +SELECT 10,10.0,10.,.1e+2,100.0e-1; +SELECT 6e-16, -6e-16, --6e-16, -6e-16+1.000000; +SELECT 1e1,1.e1,1.0e1,1e+1,1.e+1,1.0e+1,1e-1,1.e-1,1.0e-1; +SELECT 0.001e+1,0.001e-1, -0.001e+01,-0.001e-01; +SELECT 123.23E+02,-123.23E-02,"123.23E+02"+0.0,"-123.23E-02"+0.0; +SELECT 2147483647E+02,21474836.47E+06; + +create table t1 (f1 float(24),f2 float(52)); +# We mask out Privileges column because it differs for embedded server +--replace_column 8 # +show full columns from t1; +insert into t1 values(10,10),(1e+5,1e+5),(1234567890,1234567890),(1e+10,1e+10),(1e+15,1e+15),(1e+20,1e+20),(1e+50,1e+50),(1e+150,1e+150); +insert into t1 values(-10,-10),(1e-5,1e-5),(1e-10,1e-10),(1e-15,1e-15),(1e-20,1e-20),(1e-50,1e-50),(1e-150,1e-150); +select * from t1; +drop table t1; + +create table t1 (datum double); +insert into t1 values (0.5),(1.0),(1.5),(2.0),(2.5); +select * from t1; +select * from t1 where datum < 1.5; +select * from t1 where datum > 1.5; +select * from t1 where datum = 1.5; +drop table t1; + +create table t1 (a decimal(7,3) not null, key (a)); +insert into t1 values ("0"),("-0.00"),("-0.01"),("-0.002"),("1"); +select a from t1 order by a; +select min(a) from t1; +drop table t1; + +# +# BUG#3612, BUG#4393, BUG#4356, BUG#4394 +# + +create table t1 (c1 double, c2 varchar(20)); +insert t1 values (121,"16"); +select c1 + c1 * (c2 / 100) as col from t1; +create table t2 select c1 + c1 * (c2 / 100) as col1, round(c1, 5) as col2, round(c1, 35) as col3, sqrt(c1*1e-15) col4 from t1; +# Floats are a bit different in PS +--disable_ps_protocol +select * from t2; +--enable_ps_protocol +show create table t2; +drop table t1,t2; + +# Bug #1022: When a table contains a 'float' field, +# and one of the functions MAX, MIN, or AVG is used on that field, +# the system crashes. + +create table t1 (a float); +insert into t1 values (1); +select max(a),min(a),avg(a) from t1; +drop table t1; + +# +# FLOAT/DOUBLE/DECIMAL handling +# + +create table t1 (f float, f2 float(24), f3 float(6,2), d double, d2 float(53), d3 double(10,3), de decimal, de2 decimal(6), de3 decimal(5,2), n numeric, n2 numeric(8), n3 numeric(7,6)); +# We mask out Privileges column because it differs for embedded server +--replace_column 8 # +show full columns from t1; +drop table t1; + +create table t1 (a decimal(7,3) not null, key (a)); +insert into t1 values ("0"),("-0.00"),("-0.01"),("-0.002"),("1"); +select a from t1 order by a; +select min(a) from t1; +drop table t1; + +--error 1425 +create table t1 (a float(200,100), b double(200,100)); + +# +# float in a char(1) field +# +create table t1 (c20 char); +insert into t1 values (5000.0); +insert into t1 values (0.5e4); +drop table t1; + +# Errors + +--error 1063 +create table t1 (f float(54)); # Should give an error +--disable_warnings +drop table if exists t1; +--enable_warnings + +# Don't allow 'double unsigned' to be set to a negative value (Bug #7700) +create table t1 (d1 double, d2 double unsigned); +insert into t1 set d1 = -1.0; +update t1 set d2 = d1; +select * from t1; +drop table t1; + +# Ensure that maximum values as the result of number of decimals +# being specified in table schema are enforced (Bug #7361) +create table t1 (f float(4,3)); +insert into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11"); +select * from t1; +drop table if exists t1; +create table t1 (f double(4,3)); +insert into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11"); +select * from t1; +drop table if exists t1; + +# Check conversion of floats to character field (Bug #7774) +create table t1 (c char(20)); +insert into t1 values (5e-28); +select * from t1; +drop table t1; +create table t1 (c char(6)); +insert into t1 values (2e5),(2e6),(2e-4),(2e-5); +select * from t1; +drop table t1; + +# +# Test of comparison of integer with float-in-range (Bug #7840) +# This is needed because some ODBC applications (like Foxpro) uses +# floats for everything. +# + +CREATE TABLE t1 ( + reckey int unsigned NOT NULL, + recdesc varchar(50) NOT NULL, + PRIMARY KEY (reckey) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +INSERT INTO t1 VALUES (108, 'Has 108 as key'); +INSERT INTO t1 VALUES (109, 'Has 109 as key'); +select * from t1 where reckey=108; +select * from t1 where reckey=1.08E2; +select * from t1 where reckey=109; +select * from t1 where reckey=1.09E2; +drop table t1; + +# +# Bug #13372 (decimal union) +# +create table t1 (d double(10,1)); +create table t2 (d double(10,9)); +insert into t1 values ("100000000.0"); +insert into t2 values ("1.23456780"); +create table t3 select * from t2 union select * from t1; +select * from t3; +show create table t3; +drop table t1, t2, t3; + + +# +# Bug #9855 (inconsistent column type for create select +# +create table t1 select 105213674794682365.00 + 0.0 x; +show warnings; +desc t1; +drop table t1; + +create table t1 select 0.0 x; +desc t1; +create table t2 select 105213674794682365.00 y; +desc t2; +create table t3 select x+y a from t1,t2; +show warnings; +desc t3; +drop table t1,t2,t3; + +# +# Bug #22129: A small double precision number becomes zero +# +# check if underflows are detected correctly +select 1e-308, 1.00000001e-300, 100000000e-300; + +# check if overflows are detected correctly +select 10e307; + +# +# Bug #19690: ORDER BY eliminates rows from the result +# +create table t1(a int, b double(8, 2)); +insert into t1 values +(1, 28.50), (1, 121.85), (1, 157.23), (1, 1351.00), (1, -1965.35), (1, 81.75), +(1, 217.08), (1, 7.94), (4, 96.07), (4, 6404.65), (4, -6500.72), (2, 100.00), +(5, 5.00), (5, -2104.80), (5, 2033.80), (5, 0.07), (5, 65.93), +(3, -4986.24), (3, 5.00), (3, 4857.34), (3, 123.74), (3, 0.16), +(6, -1695.31), (6, 1003.77), (6, 499.72), (6, 191.82); +explain select sum(b) s from t1 group by a; +select sum(b) s from t1 group by a; +select sum(b) s from t1 group by a having s <> 0; +select sum(b) s from t1 group by a having s <> 0 order by s; +select sum(b) s from t1 group by a having s <=> 0; +select sum(b) s from t1 group by a having s <=> 0 order by s; +alter table t1 add key (a, b); +explain select sum(b) s from t1 group by a; +select sum(b) s from t1 group by a; +select sum(b) s from t1 group by a having s <> 0; +select sum(b) s from t1 group by a having s <> 0 order by s; +select sum(b) s from t1 group by a having s <=> 0; +select sum(b) s from t1 group by a having s <=> 0 order by s; +drop table t1; + +--echo End of 4.1 tests + +# +# bug #12694 (float(m,d) specifications) +# + +--error 1427 +create table t1 (s1 float(0,2)); +--error 1427 +create table t1 (s1 float(1,2)); + +# +# MySQL Bugs: #11589: mysqltest --ps-protocol, strange output, float/double/real with zerofill +# + +CREATE TABLE t1 ( + f1 real zerofill, + f2 double zerofill, + f3 float zerofill); +INSERT INTO t1 VALUES ( 0.314152e+1, 0.314152e+1, 0.314152e+1); + +let $my_stmt= select f1, f2, f3 FROM t1; +eval PREPARE stmt1 FROM '$my_stmt'; +select f1, f2, f3 FROM t1; +eval $my_stmt; +EXECUTE stmt1; + +DROP TABLE t1; +# Bug #28121 "INSERT or UPDATE into DOUBLE(200,0) field being truncated to 31 digits" +# + +create table t1 (f1 double(200, 0)); +insert into t1 values (1e199), (-1e199); +insert into t1 values (1e200), (-1e200); +insert into t1 values (2e200), (-2e200); +select f1 + 0e0 from t1; +drop table t1; + +create table t1 (f1 float(30, 0)); +insert into t1 values (1e29), (-1e29); +insert into t1 values (1e30), (-1e30); +insert into t1 values (2e30), (-2e30); +select f1 + 0e0 from t1; +drop table t1; + +# +# Bug #12860 "Difference in zero padding of exponent between Unix and Windows" +# + +create table t1 (c char(6)); +insert into t1 values (2e6),(2e-5); +select * from t1; +drop table t1; + +# +# Bug #21497 "DOUBLE truncated to unusable value" +# + +CREATE TABLE d1 (d DOUBLE); +INSERT INTO d1 VALUES (1.7976931348623157E+308); +SELECT * FROM d1; +--error ER_ILLEGAL_VALUE_FOR_TYPE +INSERT INTO d1 VALUES (1.79769313486232e+308); +SELECT * FROM d1; +DROP TABLE d1; + +# +# Bug #26788 "mysqld (debug) aborts when inserting specific numbers into char +# fields" +# + +create table t1 (a char(20)); +insert into t1 values (1.225e-05); +select a+0 from t1; +drop table t1; + +# +# Bug #27483: Casting 'scientific notation type' to 'unsigned bigint' fails on +# windows. +# + +create table t1(d double, u bigint unsigned); + +insert into t1(d) values (9.22337203685479e18), + (1.84e19); + +update t1 set u = d; +select u from t1; + +drop table t1; + +# +# Bug #21205: Different number of digits for float/doble/real in --ps-protocol +# + +CREATE TABLE t1 (f1 DOUBLE); +INSERT INTO t1 VALUES(-1.79769313486231e+308); +SELECT f1 FROM t1; +DROP TABLE t1; + +--echo # +--echo # Bug#12406055 BUFFER OVERFLOW OF VARIABLE 'BUFF' IN STRING::SET_REAL +--echo # + +--echo # Ignoring output from misc. float operations +--disable_result_log + +let $nine_65= +99999999999999999999999999999999999999999999999999999999999999999; + +select format(-1.7976931348623157E+307,256) as foo; +select least(-1.1111111111111111111111111, + - group_concat(1.7976931348623157E+308)) as foo; +eval select concat((truncate((-1.7976931348623157E+307),(0x1e))), + ($nine_65)) into @a; +--enable_result_log + +--echo End of 5.0 tests + +--echo # +--echo # Bug#12368853 FORMAT() CRASHES WITH LARGE NUMBERS AFTER TRUNCATE... +--echo # + +select format(truncate('1.7976931348623157E+308',-12),1,'fr_BE') as foo; + + +--echo # +--echo # MDEV-17249 MAKETIME(-1e50,0,0) returns a wrong result +--echo # + +SELECT LEFT('a',EXP(50)); +SELECT LEFT('a', COALESCE(1e30)); + +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES (1e30); +SELECT LEFT('a',a), LEFT('a',1e30) FROM t1; +DROP TABLE t1; + +PREPARE stmt FROM 'SELECT LEFT(111,?)'; +SET @a=1e30; +EXECUTE stmt USING @a; +DEALLOCATE PREPARE stmt; + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT LEFT('a',(SELECT 1e30 FROM t1 LIMIT 1)); +DROP TABLE t1; + +CREATE TABLE t1 (a DOUBLE); +INSERT INTO t1 VALUES (1e30),(0); +SELECT LEFT('a', SUM(a)) FROM t1; +SELECT LEFT('a', AVG(a)) FROM t1; +DROP TABLE t1; + + +--echo # +--echo # Bug #13500371 63704: CONVERSION OF '1.' TO A NUMBER GIVES ERROR 1265 +--echo # (WARN_DATA_TRUNCATED) +--echo # + +CREATE TABLE t1 (f FLOAT); +INSERT INTO t1 VALUES ('1.'); +INSERT INTO t1 VALUES ('2.0.'); +INSERT INTO t1 VALUES ('.'); +SELECT * FROM t1 ORDER BY f; +DROP TABLE t1; + + +--echo # +--echo # Start of 10.0 tests +--echo # + +--echo # +--echo # MDEV-6950 Bad results with joins comparing DATE/DATETIME and INT/DECIMAL/DOUBLE/ENUM/VARCHAR columns +--echo # +CREATE TABLE t1 (a DATETIME PRIMARY KEY); +INSERT INTO t1 VALUES ('1999-01-01 00:00:00'); +CREATE TABLE t2 (a DOUBLE); +INSERT INTO t2 VALUES (19990101000000); +INSERT INTO t2 VALUES (990101000000); +SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a; +SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; +ALTER TABLE t2 ADD PRIMARY KEY(a); +SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a; +SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; +--echo # t2 should NOT be eliminated +EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; +DROP TABLE t1,t2; + +--echo # +--echo # MDEV-6971 Bad results with joins comparing TIME and DOUBLE/DECIMAL columns +--echo # +CREATE TABLE t1 (a TIME(6) PRIMARY KEY); +INSERT INTO t1 VALUES ('10:20:30'); +CREATE TABLE t2 (a DOUBLE); +INSERT INTO t2 VALUES (102030),(102030.000000001); +SELECT t1.* FROM t1 JOIN t2 USING(a); +SELECT t1.* FROM t1 LEFT JOIN t2 USING(a); +ALTER TABLE t2 ADD PRIMARY KEY(a); +SELECT t1.* FROM t1 JOIN t2 USING(a); +SELECT t1.* FROM t1 LEFT JOIN t2 USING(a); +--echo # t2 should NOT be elimitated +EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(a); +DROP TABLE t1,t2; + +--echo # +--echo # End of 10.0 tests +--echo # diff --git a/mysql-test/suite/heap/heap_btree.result b/mysql-test/suite/heap/heap_btree.result index 12a011778c6..83d1bcb6c92 100644 --- a/mysql-test/suite/heap/heap_btree.result +++ b/mysql-test/suite/heap/heap_btree.result @@ -379,3 +379,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range uniq_id uniq_id 8 NULL 4 Using where drop table t1; End of 5.3 tests +create table t1 (id int, a varchar(300) not null, key using btree(a)) engine=heap; +insert t1 values (1, repeat('a', 300)); +drop table t1; +End of 5.5 tests diff --git a/mysql-test/suite/heap/heap_btree.test b/mysql-test/suite/heap/heap_btree.test index 02c09f52263..aca41c430b3 100644 --- a/mysql-test/suite/heap/heap_btree.test +++ b/mysql-test/suite/heap/heap_btree.test @@ -279,3 +279,12 @@ explain select 0+a from t1 where a in (869751,736494,226312,802616); drop table t1; --echo End of 5.3 tests + +# +# Bug#27799513: POTENTIAL DOUBLE FREE OR CORRUPTION OF HEAP INFO (HP_INFO) +# +create table t1 (id int, a varchar(300) not null, key using btree(a)) engine=heap; +insert t1 values (1, repeat('a', 300)); +drop table t1; + +--echo End of 5.5 tests diff --git a/mysql-test/suite/maria/fulltext2.result b/mysql-test/suite/maria/fulltext2.result new file mode 100644 index 00000000000..1e4e6636ef6 --- /dev/null +++ b/mysql-test/suite/maria/fulltext2.result @@ -0,0 +1,86 @@ +CREATE TABLE t1 ( +i int(10) unsigned not null auto_increment primary key, +a varchar(255) not null, +FULLTEXT KEY (a) +) ENGINE=Aria ROW_FORMAT=DYNAMIC MAX_ROWS=2000000000000; +repair table t1 quick; +Table Op Msg_type Msg_text +test.t1 repair status OK +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +repair table t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +repair table t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +select count(*) from t1 where match a against ('aaaxxx'); +count(*) +0 +select count(*) from t1 where match a against ('aaayyy'); +count(*) +150 +select count(*) from t1 where match a against ('aaaxxx' in boolean mode); +count(*) +1024 +select count(*) from t1 where match a against ('aaayyy' in boolean mode); +count(*) +150 +select count(*) from t1 where match a against ('aaax*' in boolean mode); +count(*) +1024 +select count(*) from t1 where match a against ('aaay*' in boolean mode); +count(*) +150 +select count(*) from t1 where match a against ('aaa*' in boolean mode); +count(*) +1174 +insert t1 (a) values ('aaaxxx'),('aaayyy'); +select count(*) from t1 where match a against ('aaaxxx' in boolean mode); +count(*) +1025 +select count(*) from t1 where match a against ('aaayyy'); +count(*) +151 +insert t1 (a) values ('aaaxxx 000000'); +select count(*) from t1 where match a against ('000000'); +count(*) +1 +delete from t1 where match a against ('000000'); +select count(*) from t1 where match a against ('000000'); +count(*) +0 +select count(*) from t1 where match a against ('aaaxxx'); +count(*) +0 +select count(*) from t1 where match a against ('aaaxxx' in boolean mode); +count(*) +1025 +select count(*) from t1 where match a against ('aaayyy' in boolean mode); +count(*) +151 +select count(*) from t1 where a = 'aaaxxx'; +count(*) +1025 +select count(*) from t1 where a = 'aaayyy'; +count(*) +151 +insert t1 (a) values ('aaaxxx 000000'); +select count(*) from t1 where match a against ('000000'); +count(*) +1 +select count(*) from t1 where match a against ('aaaxxx' in boolean mode); +count(*) +1026 +update t1 set a='aaaxxx' where a = 'aaayyy'; +select count(*) from t1 where match a against ('aaaxxx' in boolean mode); +count(*) +1177 +select count(*) from t1 where match a against ('aaayyy' in boolean mode); +count(*) +0 +drop table t1; diff --git a/mysql-test/suite/maria/fulltext2.test b/mysql-test/suite/maria/fulltext2.test new file mode 100644 index 00000000000..060b748eb4f --- /dev/null +++ b/mysql-test/suite/maria/fulltext2.test @@ -0,0 +1,77 @@ +# +# test of new fulltext search features +# + +let collation=utf8_unicode_ci; +source include/have_collation.inc; + +# +# two-level tree +# + +CREATE TABLE t1 ( + i int(10) unsigned not null auto_increment primary key, + a varchar(255) not null, + FULLTEXT KEY (a) +) ENGINE=Aria ROW_FORMAT=DYNAMIC MAX_ROWS=2000000000000; + +# two-level entry, second-level tree with depth 2 +disable_query_log; +let $1=1024; +while ($1) +{ + eval insert t1 (a) values ('aaaxxx'); + dec $1; +} + +# one-level entry (entries) +let $1=150; +while ($1) +{ + eval insert t1 (a) values ('aaayyy'); + dec $1; +} +enable_query_log; + +repair table t1 quick; +check table t1; +repair table t1; +check table t1; +repair table t1; + +select count(*) from t1 where match a against ('aaaxxx'); +select count(*) from t1 where match a against ('aaayyy'); +select count(*) from t1 where match a against ('aaaxxx' in boolean mode); +select count(*) from t1 where match a against ('aaayyy' in boolean mode); + +select count(*) from t1 where match a against ('aaax*' in boolean mode); +select count(*) from t1 where match a against ('aaay*' in boolean mode); +select count(*) from t1 where match a against ('aaa*' in boolean mode); + +# mi_write: +insert t1 (a) values ('aaaxxx'),('aaayyy'); +# call to enlarge_root() below +select count(*) from t1 where match a against ('aaaxxx' in boolean mode); +select count(*) from t1 where match a against ('aaayyy'); + +# mi_delete +insert t1 (a) values ('aaaxxx 000000'); +select count(*) from t1 where match a against ('000000'); +delete from t1 where match a against ('000000'); +select count(*) from t1 where match a against ('000000'); +select count(*) from t1 where match a against ('aaaxxx'); +select count(*) from t1 where match a against ('aaaxxx' in boolean mode); +select count(*) from t1 where match a against ('aaayyy' in boolean mode); +# double-check without index +select count(*) from t1 where a = 'aaaxxx'; +select count(*) from t1 where a = 'aaayyy'; + +# update +insert t1 (a) values ('aaaxxx 000000'); +select count(*) from t1 where match a against ('000000'); +select count(*) from t1 where match a against ('aaaxxx' in boolean mode); +update t1 set a='aaaxxx' where a = 'aaayyy'; +select count(*) from t1 where match a against ('aaaxxx' in boolean mode); +select count(*) from t1 where match a against ('aaayyy' in boolean mode); + +drop table t1; diff --git a/mysql-test/suite/maria/maria.result b/mysql-test/suite/maria/maria.result index 2618327c716..9c8565d0f03 100644 --- a/mysql-test/suite/maria/maria.result +++ b/mysql-test/suite/maria/maria.result @@ -2732,6 +2732,10 @@ id name -1 dog 2 cat DROP TABLE t1; +CREATE TABLE t1 (pk int, i2 int) ENGINE=Aria; +INSERT INTO t1 VALUES (1,2), (2,3),(3,4); +DELETE FROM tt.*, t1.* USING t1 AS tt LEFT JOIN t1 ON (tt.i2 = t1.pk); +DROP TABLE t1; # # End of 5.5 tests # diff --git a/mysql-test/suite/maria/maria.test b/mysql-test/suite/maria/maria.test index 6c080e484ce..9616933e363 100644 --- a/mysql-test/suite/maria/maria.test +++ b/mysql-test/suite/maria/maria.test @@ -2002,6 +2002,16 @@ INSERT INTO t1 (name) VALUES ('cat'); SELECT * FROM t1; DROP TABLE t1; +# +# MDEV-16682 +# Assertion `(buff[7] & 7) == HEAD_PAGE' failed. +# + +CREATE TABLE t1 (pk int, i2 int) ENGINE=Aria; +INSERT INTO t1 VALUES (1,2), (2,3),(3,4); +DELETE FROM tt.*, t1.* USING t1 AS tt LEFT JOIN t1 ON (tt.i2 = t1.pk); +DROP TABLE t1; + --echo # --echo # End of 5.5 tests --echo # diff --git a/mysql-test/suite/parts/r/update_and_cache.result b/mysql-test/suite/parts/r/update_and_cache.result new file mode 100644 index 00000000000..52f13e66702 --- /dev/null +++ b/mysql-test/suite/parts/r/update_and_cache.result @@ -0,0 +1,7 @@ +CREATE TABLE t1 (pk INT PRIMARY KEY, a INT); +INSERT INTO t1 VALUES (1,10),(2,20); +CREATE TABLE t2 (b INT) PARTITION BY KEY (b) PARTITIONS 2; +INSERT INTO t2 VALUES (1),(2); +DELETE t2 FROM t2 WHERE b BETWEEN 5 AND 9; +UPDATE t2 JOIN t1 SET b = 5; +DROP TABLE t1, t2; diff --git a/mysql-test/suite/parts/t/update_and_cache.test b/mysql-test/suite/parts/t/update_and_cache.test new file mode 100644 index 00000000000..08ade807422 --- /dev/null +++ b/mysql-test/suite/parts/t/update_and_cache.test @@ -0,0 +1,12 @@ +--source include/have_partition.inc + +CREATE TABLE t1 (pk INT PRIMARY KEY, a INT); +INSERT INTO t1 VALUES (1,10),(2,20); + +CREATE TABLE t2 (b INT) PARTITION BY KEY (b) PARTITIONS 2; +INSERT INTO t2 VALUES (1),(2); + +DELETE t2 FROM t2 WHERE b BETWEEN 5 AND 9; +UPDATE t2 JOIN t1 SET b = 5; + +DROP TABLE t1, t2; diff --git a/mysql-test/suite/rpl/r/rpl_15919.result b/mysql-test/suite/rpl/r/rpl_15919.result new file mode 100644 index 00000000000..0c176624cf7 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_15919.result @@ -0,0 +1,16 @@ +include/master-slave.inc +[connection master] +create table RPL(a int); +insert into RPL values(1); +select * from rpl; +a +1 +insert into RPL values(3); +insert into rpl values(4); +select * from rpl; +a +1 +3 +4 +drop table RPL; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_lcase_tblnames_rewrite_db.result b/mysql-test/suite/rpl/r/rpl_lcase_tblnames_rewrite_db.result new file mode 100644 index 00000000000..3feb01d92fc --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_lcase_tblnames_rewrite_db.result @@ -0,0 +1,33 @@ +include/master-slave.inc +[connection master] +SET SQL_LOG_BIN=0; +CREATE DATABASE B37656; +SET SQL_LOG_BIN=1; +CREATE DATABASE BUG37656; +### action: show that database on slave is created in lowercase +SHOW DATABASES LIKE '%37656'; +Database (%37656) +bug37656 +USE B37656; +CREATE TABLE T1 (a int); +INSERT INTO T1 VALUES (1); +### assertion: master contains capitalized case table +SHOW TABLES; +Tables_in_B37656 +T1 +use bug37656; +### assertion: slave contains lowered case table +SHOW TABLES; +Tables_in_bug37656 +t1 +### assertion: master and slave tables do not differ +include/diff_tables.inc [master:B37656.T1, slave:bug37656.t1] +SET SQL_LOG_BIN=0; +DROP DATABASE B37656; +SET SQL_LOG_BIN=1; +SHOW DATABASES LIKE '%37656'; +Database (%37656) +DROP DATABASE BUG37656; +SHOW DATABASES LIKE '%37656'; +Database (%37656) +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_row_lcase_tblnames.result b/mysql-test/suite/rpl/r/rpl_row_lcase_tblnames.result new file mode 100644 index 00000000000..d8d459f4dcc --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_row_lcase_tblnames.result @@ -0,0 +1,47 @@ +include/master-slave.inc +[connection master] +******** [ MASTER ] ******** +CREATE DATABASE BUG_37656; +use BUG_37656; +show databases like 'BUG_37656'; +Database (BUG_37656) +BUG_37656 +******** [ SLAVE ] ******** +show databases like 'bug_37656'; +Database (bug_37656) +bug_37656 +******** [ MASTER ] ******** +CREATE TABLE T1 (a int); +CREATE TABLE T2 (b int) ENGINE=InnoDB; +CREATE TABLE T3 (txt TEXT); +show tables; +Tables_in_BUG_37656 +T1 +T2 +T3 +******** [ SLAVE ] ******** +use bug_37656; +show tables; +Tables_in_bug_37656 +t2 +t3 +CREATE TABLE t1 (a INT); +******** [ MASTER ] ******** +use BUG_37656; +INSERT INTO T1 VALUES (1); +INSERT INTO T2 VALUES (1); +use test; +INSERT INTO BUG_37656.T1 VALUES (2); +INSERT INTO BUG_37656.T2 VALUES (2); +LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE BUG_37656.T3; +******** [ SLAVE ] ******** +include/diff_tables.inc [master:BUG_37656.T2, slave:bug_37656.t2] +include/diff_tables.inc [master:BUG_37656.T3, slave:bug_37656.t3] +******** [ MASTER ] ******** +DROP DATABASE BUG_37656; +CREATE DATABASE B50653; +USE B50653; +CREATE PROCEDURE b50653_proc() BEGIN SELECT 1; END; +DROP PROCEDURE b50653_proc; +DROP DATABASE B50653; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_row_spatial.result b/mysql-test/suite/rpl/r/rpl_row_spatial.result new file mode 100644 index 00000000000..8f546fc479e --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_row_spatial.result @@ -0,0 +1,14 @@ +include/master-slave.inc +[connection master] +CREATE TABLE t1 (g POINT NOT NULL, SPATIAL INDEX(g)); +INSERT INTO t1 VALUES (ST_GEOMFROMTEXT('Point(1 1)')); +INSERT INTO t1 VALUES (ST_GEOMFROMTEXT('Point(2 1)')); +INSERT INTO t1 VALUES (ST_GEOMFROMTEXT('Point(1 2)')); +INSERT INTO t1 VALUES (ST_GEOMFROMTEXT('Point(2 2)')); +DELETE FROM t1 where MBREqual(g, ST_GEOMFROMTEXT('Point(1 2)')); +select count(*) from t1; +count(*) +3 +DELETE FROM t1; +drop table t1; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_stm_lcase_tblnames.result b/mysql-test/suite/rpl/r/rpl_stm_lcase_tblnames.result new file mode 100644 index 00000000000..0d60f52a983 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_stm_lcase_tblnames.result @@ -0,0 +1,44 @@ +include/master-slave.inc +[connection master] +******** [ MASTER ] ******** +CREATE DATABASE BUG_37656; +use BUG_37656; +show databases like 'BUG_37656'; +Database (BUG_37656) +BUG_37656 +******** [ SLAVE ] ******** +show databases like 'bug_37656'; +Database (bug_37656) +bug_37656 +******** [ MASTER ] ******** +CREATE TABLE T1 (a int); +CREATE TABLE T2 (b int) ENGINE=InnoDB; +CREATE TABLE T3 (txt TEXT); +show tables; +Tables_in_BUG_37656 +T1 +T2 +T3 +******** [ SLAVE ] ******** +use bug_37656; +show tables; +Tables_in_bug_37656 +t2 +t3 +CREATE TABLE t1 (a INT); +******** [ MASTER ] ******** +use BUG_37656; +INSERT INTO T1 VALUES (1); +INSERT INTO T2 VALUES (1); +LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE BUG_37656.T3; +******** [ SLAVE ] ******** +include/diff_tables.inc [master:BUG_37656.T2, slave:bug_37656.t2] +include/diff_tables.inc [master:BUG_37656.T3, slave:bug_37656.t3] +******** [ MASTER ] ******** +DROP DATABASE BUG_37656; +CREATE DATABASE B50653; +USE B50653; +CREATE PROCEDURE b50653_proc() BEGIN SELECT 1; END; +DROP PROCEDURE b50653_proc; +DROP DATABASE B50653; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_15919-master.opt b/mysql-test/suite/rpl/t/rpl_15919-master.opt new file mode 100644 index 00000000000..9b27aef9bf8 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_15919-master.opt @@ -0,0 +1 @@ +--lower_case_table_names=0 diff --git a/mysql-test/suite/rpl/t/rpl_15919-slave.opt b/mysql-test/suite/rpl/t/rpl_15919-slave.opt new file mode 100644 index 00000000000..62ab6dad1e0 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_15919-slave.opt @@ -0,0 +1 @@ +--lower_case_table_names=1 diff --git a/mysql-test/suite/rpl/t/rpl_15919.test b/mysql-test/suite/rpl/t/rpl_15919.test new file mode 100644 index 00000000000..0462f7fd067 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_15919.test @@ -0,0 +1,16 @@ +--source include/master-slave.inc +--source include/have_innodb.inc +--connection master +create table RPL(a int); +insert into RPL values(1); + +--sync_slave_with_master +select * from rpl; +insert into RPL values(3); +insert into rpl values(4); +select * from rpl; + +--connection master +drop table RPL; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_lcase_tblnames_rewrite_db-slave.opt b/mysql-test/suite/rpl/t/rpl_lcase_tblnames_rewrite_db-slave.opt new file mode 100644 index 00000000000..0031a57a693 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_lcase_tblnames_rewrite_db-slave.opt @@ -0,0 +1 @@ +--lower-case-table-names=1 "--replicate-rewrite-db=b37656->bug37656" diff --git a/mysql-test/suite/rpl/t/rpl_lcase_tblnames_rewrite_db.test b/mysql-test/suite/rpl/t/rpl_lcase_tblnames_rewrite_db.test new file mode 100644 index 00000000000..9c804d8206a --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_lcase_tblnames_rewrite_db.test @@ -0,0 +1,60 @@ +# BUG#37656 +# +# DESCRIPTION +# +# +# This test case is tests whether replication works properly when +# slave is configured with --lower-case-table-names=1 and replication +# rewrite rules are in effect. +# +# It checks four issues: +# +# (i) master contains capitalized table name +# +# (ii) slave contains lowered case table name +# +# (iii) master and slave tables do not differ +# +-- source include/master-slave.inc +-- source include/not_windows.inc + +SET SQL_LOG_BIN=0; +CREATE DATABASE B37656; +SET SQL_LOG_BIN=1; + +-- connection slave +CREATE DATABASE BUG37656; + +-- echo ### action: show that database on slave is created in lowercase +SHOW DATABASES LIKE '%37656'; + +-- connection master +USE B37656; +CREATE TABLE T1 (a int); +INSERT INTO T1 VALUES (1); + +-- echo ### assertion: master contains capitalized case table +SHOW TABLES; + +-- sync_slave_with_master + +use bug37656; + +-- echo ### assertion: slave contains lowered case table +SHOW TABLES; + +-- echo ### assertion: master and slave tables do not differ +let $diff_tables= master:B37656.T1, slave:bug37656.t1; + +-- source include/diff_tables.inc + +-- connection master +SET SQL_LOG_BIN=0; +DROP DATABASE B37656; +SET SQL_LOG_BIN=1; +SHOW DATABASES LIKE '%37656'; + +-- connection slave +DROP DATABASE BUG37656; +SHOW DATABASES LIKE '%37656'; +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_row_lcase_tblnames-slave.opt b/mysql-test/suite/rpl/t/rpl_row_lcase_tblnames-slave.opt new file mode 100644 index 00000000000..7624b013dcd --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_row_lcase_tblnames-slave.opt @@ -0,0 +1 @@ +--replicate-do-db=bug_37656 --replicate-ignore-table=buG_37656.T1 --replicate-do-table=bUg_37656.T2 --replicate-do-table=bUg_37656.T3 --lower-case-table-names=1 diff --git a/mysql-test/suite/rpl/t/rpl_row_lcase_tblnames.test b/mysql-test/suite/rpl/t/rpl_row_lcase_tblnames.test new file mode 100644 index 00000000000..78a66a7df32 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_row_lcase_tblnames.test @@ -0,0 +1,12 @@ +# BUG#37656 +# +# For details look into extra/rpl_tests/rpl_lower_case_table_names.test +# + +-- source include/master-slave.inc +-- source include/have_innodb.inc +-- source include/not_windows.inc +-- source include/have_binlog_format_row.inc + +-- let $engine=InnoDB +-- source extra/rpl_tests/rpl_lower_case_table_names.test diff --git a/mysql-test/suite/rpl/t/rpl_row_spatial.test b/mysql-test/suite/rpl/t/rpl_row_spatial.test new file mode 100644 index 00000000000..00c3dd7c54d --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_row_spatial.test @@ -0,0 +1,17 @@ +--source include/have_binlog_format_row.inc +--source include/master-slave.inc + +CREATE TABLE t1 (g POINT NOT NULL, SPATIAL INDEX(g)); +INSERT INTO t1 VALUES (ST_GEOMFROMTEXT('Point(1 1)')); +INSERT INTO t1 VALUES (ST_GEOMFROMTEXT('Point(2 1)')); +INSERT INTO t1 VALUES (ST_GEOMFROMTEXT('Point(1 2)')); +INSERT INTO t1 VALUES (ST_GEOMFROMTEXT('Point(2 2)')); +DELETE FROM t1 where MBREqual(g, ST_GEOMFROMTEXT('Point(1 2)')); + +--sync_slave_with_master +select count(*) from t1; + +--connection master +DELETE FROM t1; +drop table t1; +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_stm_lcase_tblnames-slave.opt b/mysql-test/suite/rpl/t/rpl_stm_lcase_tblnames-slave.opt new file mode 100644 index 00000000000..8be29bbe976 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_stm_lcase_tblnames-slave.opt @@ -0,0 +1 @@ +--replicate-do-db=bug_37656 --replicate-ignore-table=bug_37656.t1 --replicate-do-table=bug_37656.t2 --replicate-do-table=bug_37656.t3 --lower-case-table-names=1 diff --git a/mysql-test/suite/rpl/t/rpl_stm_lcase_tblnames.test b/mysql-test/suite/rpl/t/rpl_stm_lcase_tblnames.test new file mode 100644 index 00000000000..00df8e9d385 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_stm_lcase_tblnames.test @@ -0,0 +1,12 @@ +# BUG#37656 +# +# For details look into extra/rpl_tests/rpl_lower_case_table_names.test +# + +-- source include/master-slave.inc +-- source include/have_innodb.inc +-- source include/not_windows.inc +-- source include/have_binlog_format_mixed_or_statement.inc + +-- let $engine=InnoDB +-- source extra/rpl_tests/rpl_lower_case_table_names.test diff --git a/mysql-test/suite/sys_vars/r/all_vars.result b/mysql-test/suite/sys_vars/r/all_vars.result index 1bd4e394f6a..b7beb7b5347 100644 --- a/mysql-test/suite/sys_vars/r/all_vars.result +++ b/mysql-test/suite/sys_vars/r/all_vars.result @@ -10,5 +10,6 @@ there should be *no* long test name listed below: select distinct variable_name as `there should be *no* variables listed below:` from t2 left join t1 on variable_name=test_name where test_name is null; there should be *no* variables listed below: +wsrep_certification_rules drop table t1; drop table t2; diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index 28d8c5bf5e9..9703b9a1455 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -287,7 +287,7 @@ drop table t1; # set names koi8r; create table t1 (a char(10) character set koi8r); -insert into t1 values ('ÔÅÓÔ'); +insert into t1 values ('����'); select a,hex(a) from t1; alter table t1 change a a char(10) character set cp1251; select a,hex(a) from t1; @@ -367,7 +367,7 @@ DROP TABLE T12207; # modified. In other words, the values were reinterpreted # as UTF8 instead of being converted. create table t1 (a text) character set koi8r; -insert into t1 values (_koi8r'ÔÅÓÔ'); +insert into t1 values (_koi8r'����'); select hex(a) from t1; alter table t1 convert to character set cp1251; select hex(a) from t1; @@ -1296,55 +1296,55 @@ MODIFY COLUMN `consultant_id` BIGINT; SHOW CREATE TABLE t1; DROP TABLE t1; -# -# Test of ALTER TABLE IF [NOT] EXISTS -# +--echo # +--echo # BUG#27788685: NO WARNING WHEN TRUNCATING A STRING WITH DATA LOSS +--echo # -CREATE TABLE t1 ( - id INT(11) NOT NULL, - x_param INT(11) DEFAULT NULL, - PRIMARY KEY (id) -) ENGINE=MYISAM; +SET GLOBAL max_allowed_packet=17825792; -ALTER TABLE t1 ADD COLUMN IF NOT EXISTS id INT, - ADD COLUMN IF NOT EXISTS lol INT AFTER id; -ALTER TABLE t1 ADD COLUMN IF NOT EXISTS lol INT AFTER id; -ALTER TABLE t1 DROP COLUMN IF EXISTS lol; -ALTER TABLE t1 DROP COLUMN IF EXISTS lol; +--connect(con1, localhost, root,,) +CREATE TABLE t1 (t1_fld1 TEXT); +CREATE TABLE t2 (t2_fld1 MEDIUMTEXT); +CREATE TABLE t3 (t3_fld1 LONGTEXT); -ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param); -ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param); -ALTER TABLE t1 MODIFY IF EXISTS lol INT; +INSERT INTO t1 VALUES (REPEAT('a',300)); +INSERT INTO t2 VALUES (REPEAT('b',65680)); +INSERT INTO t3 VALUES (REPEAT('c',16777300)); -DROP INDEX IF EXISTS x_param ON t1; -DROP INDEX IF EXISTS x_param ON t1; -CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param); -CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param); -SHOW CREATE TABLE t1; -DROP TABLE t1; +SELECT LENGTH(t1_fld1) FROM t1; +SELECT LENGTH(t2_fld1) FROM t2; +SELECT LENGTH(t3_fld1) FROM t3; -CREATE TABLE t1 ( - id INT(11) NOT NULL, - x_param INT(11) DEFAULT NULL, - PRIMARY KEY (id) -) ENGINE=INNODB; +--echo # With strict mode +SET SQL_MODE='STRICT_ALL_TABLES'; -CREATE TABLE t2 ( - id INT(11) NOT NULL) ENGINE=INNODB; +--error ER_DATA_TOO_LONG +ALTER TABLE t1 CHANGE `t1_fld1` `my_t1_fld1` TINYTEXT; +--error ER_DATA_TOO_LONG +ALTER TABLE t2 CHANGE `t2_fld1` `my_t2_fld1` TEXT; +--error ER_DATA_TOO_LONG +ALTER TABLE t3 CHANGE `t3_fld1` `my_t3_fld1` MEDIUMTEXT; -ALTER TABLE t1 ADD COLUMN IF NOT EXISTS id INT, - ADD COLUMN IF NOT EXISTS lol INT AFTER id; -ALTER TABLE t1 ADD COLUMN IF NOT EXISTS lol INT AFTER id; -ALTER TABLE t1 DROP COLUMN IF EXISTS lol; -ALTER TABLE t1 DROP COLUMN IF EXISTS lol; +--echo # With non-strict mode +SET SQL_MODE=''; -ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param); -ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param); -ALTER TABLE t1 MODIFY IF EXISTS lol INT; +ALTER TABLE t1 CHANGE `t1_fld1` `my_t1_fld1` TINYTEXT; +ALTER TABLE t2 CHANGE `t2_fld1` `my_t2_fld1` TEXT; +ALTER TABLE t3 CHANGE `t3_fld1` `my_t3_fld1` MEDIUMTEXT; -DROP INDEX IF EXISTS x_param ON t1; -DROP INDEX IF EXISTS x_param ON t1; -CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param); +SELECT LENGTH(my_t1_fld1) FROM t1; +SELECT LENGTH(my_t2_fld1) FROM t2; +SELECT LENGTH(my_t3_fld1) FROM t3; + +# Cleanup +--disconnect con1 +--source include/wait_until_disconnected.inc + +--connection default +DROP TABLE t1, t2, t3; + +SET SQL_MODE=default; +SET GLOBAL max_allowed_packet=default; CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param); SHOW CREATE TABLE t1; diff --git a/mysql-test/t/derived_opt.test b/mysql-test/t/derived_opt.test index 7f19553e4e5..aab95f69f26 100644 --- a/mysql-test/t/derived_opt.test +++ b/mysql-test/t/derived_opt.test @@ -363,5 +363,43 @@ set join_cache_level=@tmp_jcl; set optimizer_switch=@tmp_os; drop table t1, t2; +--echo # +--echo # Bug mdev-17382: equi-join of derived table with join_cache_level=4 +--echo # + +CREATE TABLE t1 ( + id int NOT NULL, + amount decimal DEFAULT NULL, +PRIMARY KEY (id) +); + +CREATE TABLE t2 ( + id int NOT NULL, + name varchar(50) DEFAULT NULL, +PRIMARY KEY (id) +); + +INSERT INTO t1 VALUES +(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000), +(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000); + +INSERT INTO t2 VALUES +(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL), +(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL); + +set join_cache_level=4; + +EXPLAIN +SELECT t2.id,t2.name,t.total_amt + FROM t2 + LEFT JOIN + (SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t + ON t2.id=t.id + WHERE t2.id < 3; + +set join_cache_level=default; + +DROP TABLE t1,t2; + # The following command must be the last one the file set optimizer_switch=@exit_optimizer_switch; diff --git a/mysql-test/t/func_isnull.test b/mysql-test/t/func_isnull.test index 4c59fa3cbe8..7d1a7e83a1a 100644 --- a/mysql-test/t/func_isnull.test +++ b/mysql-test/t/func_isnull.test @@ -83,6 +83,22 @@ SELECT * FROM t2 LEFT JOIN v1 ON t2.d1=v1.d1 WHERE v1.d1 IS NULL; DROP VIEW v1; DROP TABLE t1,t2; + +--echo # +--echo # MDEV-15475: Assertion `!table || (!table->read_set || +--echo # bitmap_is_set(table->read_set, field_index))' +--echo # failed on EXPLAIN EXTENDED with constant table and view +--echo # + +CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=MyISAM; +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1); +EXPLAIN EXTENDED SELECT ISNULL(pk) FROM v1; +EXPLAIN EXTENDED SELECT IFNULL(pk,0) FROM v1; +# Cleanup +DROP VIEW v1; +DROP TABLE t1; + --echo # --echo # End of 5.5 tests --echo # diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 1baded9fef7..cb8f35c3627 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -1697,6 +1697,59 @@ DO TO_DAYS(SEC_TO_TIME(TIME(CEILING(UUID())))); DO TO_DAYS(SEC_TO_TIME(MAKEDATE('',RAND(~(''))))); SELECT SEC_TO_TIME(MAKEDATE(0,RAND(~0))); +# +# MDEV-16810 AddressSanitizer: stack-buffer-overflow in int10_to_str +# +SELECT PERIOD_DIFF(2018, AES_ENCRYPT('Rae Bareli', 'Rae Bareli')); + + +--echo # +--echo # MDEV-17249 MAKETIME(-1e50,0,0) returns a wrong result +--echo # + +--vertical_results +SELECT + MAKETIME(1e10,0,0), + MAKETIME(-1e10,0,0), + MAKETIME(1e50,0,0), + MAKETIME(-1e50,0,0), + MAKETIME(COALESCE(1e50),0,0), + MAKETIME(COALESCE(-1e50),0,0); +--horizontal_results + +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES (1e30),(-1e30); +SELECT MAKETIME(a,0,0) FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-17244 MAKETIME(900,0,0.111) returns a wrong result +--echo # + +SELECT MAKETIME(900,0,0); +SELECT MAKETIME(900,0,0.1); +SELECT MAKETIME(900,0,0.11); +SELECT MAKETIME(900,0,0.111); +SELECT MAKETIME(900,0,0.1111); +SELECT MAKETIME(900,0,0.11111); +SELECT MAKETIME(900,0,0.111111); +SELECT MAKETIME(900,0,0.1111111); +SELECT MAKETIME(900,0,0.11111111); +SELECT MAKETIME(900,0,0.111111111); +SELECT MAKETIME(900,0,EXP(1)); + +SELECT MAKETIME(-900,0,0); +SELECT MAKETIME(-900,0,0.1); +SELECT MAKETIME(-900,0,0.11); +SELECT MAKETIME(-900,0,0.111); +SELECT MAKETIME(-900,0,0.1111); +SELECT MAKETIME(-900,0,0.11111); +SELECT MAKETIME(-900,0,0.111111); +SELECT MAKETIME(-900,0,0.1111111); +SELECT MAKETIME(-900,0,0.11111111); +SELECT MAKETIME(-900,0,0.111111111); +SELECT MAKETIME(-900,0,EXP(1)); + --echo # --echo # End of 5.5 tests diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test index 156a55e0466..feca35086f0 100644 --- a/mysql-test/t/grant.test +++ b/mysql-test/t/grant.test @@ -210,26 +210,26 @@ drop user mysqltest_1@localhost; # Bug#3403 Wrong encoding in SHOW GRANTS output # SET NAMES koi8r; -CREATE DATABASE ÂÄ; -USE ÂÄ; -CREATE TABLE ÔÁ (ËÏÌ INT); +CREATE DATABASE ��; +USE ��; +CREATE TABLE ��� (��� INT); -GRANT SELECT ON ÂÄ.* TO ÀÚÅÒ@localhost; -SHOW GRANTS FOR ÀÚÅÒ@localhost; -REVOKE SELECT ON ÂÄ.* FROM ÀÚÅÒ@localhost; +GRANT SELECT ON ��.* TO ����@localhost; +SHOW GRANTS FOR ����@localhost; +REVOKE SELECT ON ��.* FROM ����@localhost; -GRANT SELECT ON ÂÄ.ÔÁ TO ÀÚÅÒ@localhost; -SHOW GRANTS FOR ÀÚÅÒ@localhost; -REVOKE SELECT ON ÂÄ.ÔÁ FROM ÀÚÅÒ@localhost; +GRANT SELECT ON ��.��� TO ����@localhost; +SHOW GRANTS FOR ����@localhost; +REVOKE SELECT ON ��.��� FROM ����@localhost; -GRANT SELECT (ËÏÌ) ON ÂÄ.ÔÁ TO ÀÚÅÒ@localhost; -SHOW GRANTS FOR ÀÚÅÒ@localhost; -REVOKE SELECT (ËÏÌ) ON ÂÄ.ÔÁ FROM ÀÚÅÒ@localhost; +GRANT SELECT (���) ON ��.��� TO ����@localhost; +SHOW GRANTS FOR ����@localhost; +REVOKE SELECT (���) ON ��.��� FROM ����@localhost; # Revoke does not drop user. Leave a clean user table for the next tests. -DROP USER ÀÚÅÒ@localhost; +DROP USER ����@localhost; -DROP DATABASE ÂÄ; +DROP DATABASE ��; SET NAMES latin1; # @@ -512,14 +512,14 @@ set @user123="non-existent"; select * from mysql.db where user=@user123; set names koi8r; -create database ÂÄ; -grant select on ÂÄ.* to root@localhost; -select hex(Db) from mysql.db where Db='ÂÄ'; +create database ��; +grant select on ��.* to root@localhost; +select hex(Db) from mysql.db where Db='��'; show grants for root@localhost; flush privileges; show grants for root@localhost; -drop database ÂÄ; -revoke all privileges on ÂÄ.* from root@localhost; +drop database ��; +revoke all privileges on ��.* from root@localhost; show grants for root@localhost; set names latin1; @@ -2265,5 +2265,26 @@ disconnect con1; DROP USER untrusted@localhost; DROP DATABASE secret; +# +# Bug#27230925: HANDLE_FATAL_SIGNAL (SIG=11) IN SHOW_ROUTINE_GRANTS +# +create user foo@localhost; +create database foodb; +grant create routine on foodb.* to foo@localhost; +connect con1,localhost,foo; +create procedure fooproc() select 'i am fooproc'; +show grants; +disconnect con1; +connection default; +rename table mysql.procs_priv to mysql.procs_priv1; +error ER_NO_SUCH_TABLE; +flush privileges; +show grants for foo@localhost; +rename table mysql.procs_priv1 to mysql.procs_priv; +drop user foo@localhost; +drop procedure fooproc; +drop database foodb; + + # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index 8a088de91cc..3d2a02e2346 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -1185,12 +1185,13 @@ CREATE TABLE t (f INT); # # The following shouldn't fail as the table is now matching the using # ---error ER_BAD_FIELD_ERROR CALL p; DROP TABLE t; CREATE TABLE t (i INT); --error ER_BAD_FIELD_ERROR CALL p; +--error ER_BAD_FIELD_ERROR +CALL p; DROP PROCEDURE p; DROP TABLE t; diff --git a/mysql-test/t/lowercase_fs_off.test b/mysql-test/t/lowercase_fs_off.test index 01989ef5367..8729efde02c 100644 --- a/mysql-test/t/lowercase_fs_off.test +++ b/mysql-test/t/lowercase_fs_off.test @@ -104,9 +104,11 @@ ALTER TABLE T1 RENAME t1; DROP TABLE t1; # -# MDEV-9014 SHOW TRIGGERS not case sensitive +# MDEV-13912 mysql_upgrade: case (in)sensitivity for stored procedures # -create table t1 (a int); -create trigger t1_bi before insert on t1 for each row set new.a= 1; -show triggers like '%T1%'; -drop table t1; +create database TEST; +create procedure TEST.pr() begin end; +create procedure test.pr() begin end; +--exec $MYSQL_UPGRADE --force 2>&1 +drop procedure test.pr; +drop database TEST; diff --git a/mysql-test/t/sp-security.test b/mysql-test/t/sp-security.test index a2079e91440..6a596b4febb 100644 --- a/mysql-test/t/sp-security.test +++ b/mysql-test/t/sp-security.test @@ -995,6 +995,24 @@ disconnect con2; DROP USER user2@localhost; DROP DATABASE db1; +# +# Bug#27407480: AUTOMATIC_SP_PRIVILEGES REQUIRES NEED THE INSERT PRIVILEGES FOR MYSQL.USER TABLE +# +create user foo@local_ost; +create user foo@`local\_ost` identified by 'nevermore'; +create database foodb; +grant create routine on foodb.* to foo@local_ost; +connect con1,localhost,foo; +select user(), current_user(); +show grants; +create procedure fooproc() select 'i am fooproc'; +show grants; +disconnect con1; +connection default; +drop user foo@local_ost; +drop user foo@`local\_ost`; +drop procedure fooproc; +drop database foodb; --echo # --echo # Test for bug#12602983 - User without privilege on routine can discover --echo # its existence by executing "select non_existing_func();" or by @@ -1039,4 +1057,3 @@ drop database mysqltest_db; # Wait till all disconnects are completed --source include/wait_until_count_sessions.inc - diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index c5d37d1017d..1b765f0eb2d 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -9353,6 +9353,27 @@ where 1=1; drop function if exists f1; drop table t1,t2; +--echo # +--echo # MDEV-16957: Server crashes in Field_iterator_natural_join::next +--echo # upon 2nd execution of SP +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(32)); +CREATE PROCEDURE sp() SELECT * FROM t1 AS t1x JOIN t1 AS t1y USING (c); +--error ER_BAD_FIELD_ERROR +CALL sp; +--error ER_BAD_FIELD_ERROR +CALL sp; +--error ER_BAD_FIELD_ERROR +CALL sp; +alter table t1 add column c int; +CALL sp; + +# Cleanup +DROP PROCEDURE sp; +DROP TABLE t1; + + --echo # End of 5.5 test --echo # diff --git a/mysql-test/t/type_float.test b/mysql-test/t/type_float.test index bb7a784553e..297860f1e05 100644 --- a/mysql-test/t/type_float.test +++ b/mysql-test/t/type_float.test @@ -332,34 +332,34 @@ eval select concat((truncate((-1.7976931348623157E+307),(0x1e))), select format(truncate('1.7976931348623157E+308',-12),1,'fr_BE') as foo; + --echo # ---echo # Bug #13500371 63704: CONVERSION OF '1.' TO A NUMBER GIVES ERROR 1265 ---echo # (WARN_DATA_TRUNCATED) +--echo # MDEV-17249 MAKETIME(-1e50,0,0) returns a wrong result --echo # -CREATE TABLE t1 (f FLOAT); -INSERT INTO t1 VALUES ('1.'); -INSERT INTO t1 VALUES ('2.0.'); -INSERT INTO t1 VALUES ('.'); -SELECT * FROM t1 ORDER BY f; +SELECT LEFT('a',EXP(50)); +SELECT LEFT('a', COALESCE(1e30)); + +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES (1e30); +SELECT LEFT('a',a), LEFT('a',1e30) FROM t1; DROP TABLE t1; +PREPARE stmt FROM 'SELECT LEFT(111,?)'; +SET @a=1e30; +EXECUTE stmt USING @a; +DEALLOCATE PREPARE stmt; ---echo # ---echo # Start of 10.0 tests ---echo # +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT LEFT('a',(SELECT 1e30 FROM t1 LIMIT 1)); +DROP TABLE t1; ---echo # ---echo # MDEV-6950 Bad results with joins comparing DATE/DATETIME and INT/DECIMAL/DOUBLE/ENUM/VARCHAR columns ---echo # -CREATE TABLE t1 (a DATETIME PRIMARY KEY); -INSERT INTO t1 VALUES ('1999-01-01 00:00:00'); -CREATE TABLE t2 (a DOUBLE); -INSERT INTO t2 VALUES (19990101000000); -INSERT INTO t2 VALUES (990101000000); -SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a; -SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; -ALTER TABLE t2 ADD PRIMARY KEY(a); +CREATE TABLE t1 (a DOUBLE); +INSERT INTO t1 VALUES (1e30),(0); +SELECT LEFT('a', SUM(a)) FROM t1; +SELECT LEFT('a', AVG(a)) FROM t1; +DROP TABLE t1; SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a; SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; --echo # t2 should NOT be eliminated |