Merge branch '10.0-galera' into 10.1

21 files changed, 6053 insertions, 75 deletions

diff --git a/cmake/wsrep.cmake b/cmake/wsrep.cmake
index 9fa127380a4..b272df51adb 100644
--- a/cmake/wsrep.cmake
+++ b/cmake/wsrep.cmake
@@ -26,7 +26,7 @@ ENDIF()
 OPTION(WITH_WSREP "WSREP replication API (to use, e.g. Galera Replication library)" ${with_wsrep_default})
 
 # Set the patch version
-SET(WSREP_PATCH_VERSION "23")
+SET(WSREP_PATCH_VERSION "24")
 
 # Obtain wsrep API version
 FILE(STRINGS "${MySQL_SOURCE_DIR}/wsrep/wsrep_api.h" WSREP_API_VERSION
diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl
index 5fe616c67ae..da72e7e1a9a 100755
--- a/mysql-test/mysql-test-run.pl
+++ b/mysql-test/mysql-test-run.pl
@@ -139,8 +139,6 @@ my $opt_start_exit;
 my $start_only;
 my $file_wsrep_provider;
 
-our @global_suppressions;
-
 END {
   if ( defined $opt_tmpdir_pid and $opt_tmpdir_pid == $$ )
   {
@@ -195,8 +193,6 @@ my @DEFAULT_SUITES= qw(
     sys_vars-
     unit-
     vcol-
-    wsrep-
-    galera-
   );
 my $opt_suites;
 
@@ -357,7 +353,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};
@@ -1118,7 +1113,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,
@@ -1824,16 +1818,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));
 
 }
 
@@ -2994,8 +2988,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
@@ -4407,7 +4401,6 @@ sub extract_warning_lines ($$) {
   # Perl code.
   my @antipatterns =
     (
-     @global_suppressions,
      qr/error .*connecting to master/,
      qr/InnoDB: Error: in ALTER TABLE `test`.`t[12]`/,
      qr/InnoDB: Error: table `test`.`t[12]` .*does not exist in the InnoDB internal/,
@@ -6117,8 +6110,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.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/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/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/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/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/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/suite/sys_vars/r/sysvars_wsrep.result b/mysql-test/suite/sys_vars/r/sysvars_wsrep.result
index db932ae8223..e54afd2d64a 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_wsrep.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_wsrep.result
@@ -29,6 +29,20 @@ NUMERIC_BLOCK_SIZE	NULL
 ENUM_VALUE_LIST	OFF,ON
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	OPTIONAL
+VARIABLE_NAME	WSREP_CERTIFICATION_RULES
+SESSION_VALUE	NULL
+GLOBAL_VALUE	strict
+GLOBAL_VALUE_ORIGIN	COMPILE-TIME
+DEFAULT_VALUE	strict
+VARIABLE_SCOPE	GLOBAL
+VARIABLE_TYPE	ENUM
+VARIABLE_COMMENT	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.
+NUMERIC_MIN_VALUE	NULL
+NUMERIC_MAX_VALUE	NULL
+NUMERIC_BLOCK_SIZE	NULL
+ENUM_VALUE_LIST	strict,optimized
+READ_ONLY	NO
+COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	WSREP_CERTIFY_NONPK
 SESSION_VALUE	NULL
 GLOBAL_VALUE	ON
diff --git a/sql/log_event.cc b/sql/log_event.cc
index 4879e9fcecf..a4fc2dd5398 100644
--- a/sql/log_event.cc
+++ b/sql/log_event.cc
@@ -4439,6 +4439,22 @@ int Query_log_event::do_apply_event(rpl_group_info *rgi,
       else
         thd->variables.collation_database= thd->db_charset;
 
+      {
+        const CHARSET_INFO *cs= thd->charset();
+        /*
+          We cannot ask for parsing a statement using a character set
+          without state_maps (parser internal data).
+        */
+        if (!cs->state_map)
+        {
+          rli->report(ERROR_LEVEL, ER_SLAVE_FATAL_ERROR,
+                      ER_THD(thd, ER_SLAVE_FATAL_ERROR),
+                      "character_set cannot be parsed");
+          thd->is_slave_error= true;
+          goto end;
+        }
+      }
+
       /*
         Record any GTID in the same transaction, so slave state is
         transactionally consistent.
@@ -7943,6 +7959,11 @@ User_var_log_event(const char* buf, uint event_len,
       we keep the flags set to UNDEF_F.
     */
     uint bytes_read= ((val + val_len) - buf_start);
+    if (bytes_read > event_len)
+    {
+      error= true;
+      goto err;
+    }
     if ((data_written - bytes_read) > 0)
     {
       flags= (uint) *(buf + UV_VAL_IS_NULL + UV_VAL_TYPE_SIZE +
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index 16126c471c0..d1db14d7b35 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -4940,6 +4940,19 @@ static Sys_var_mybool Sys_wsrep_certify_nonPK(
        GLOBAL_VAR(wsrep_certify_nonPK), 
        CMD_LINE(OPT_ARG), DEFAULT(TRUE));
 
+static const char *wsrep_certification_rules_names[]= { "strict", "optimized", NullS };
+static Sys_var_enum Sys_wsrep_certification_rules(
+       "wsrep_certification_rules",
+       "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.",
+       GLOBAL_VAR(wsrep_certification_rules), CMD_LINE(REQUIRED_ARG),
+       wsrep_certification_rules_names, DEFAULT(WSREP_CERTIFICATION_RULES_STRICT),
+       NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0),
+       ON_UPDATE(0));
+
 static Sys_var_mybool Sys_wsrep_causal_reads(
        "wsrep_causal_reads", "Setting this variable is equivalent "
        "to setting wsrep_sync_wait READ flag",
diff --git a/sql/wsrep_mysqld.cc b/sql/wsrep_mysqld.cc
index 47aea6d3824..ee8509e3fa2 100644
--- a/sql/wsrep_mysqld.cc
+++ b/sql/wsrep_mysqld.cc
@@ -77,6 +77,7 @@ ulong   wsrep_max_ws_size              = 1073741824UL;//max ws (RBR buffer) size
 ulong   wsrep_max_ws_rows              = 65536; // max number of rows in ws
 int     wsrep_to_isolation             = 0; // # of active TO isolation threads
 my_bool wsrep_certify_nonPK            = 1; // certify, even when no primary key
+ulong   wsrep_certification_rules      = WSREP_CERTIFICATION_RULES_STRICT;
 long    wsrep_max_protocol_version     = 3; // maximum protocol version to use
 ulong   wsrep_forced_binlog_format     = BINLOG_FORMAT_UNSPEC;
 my_bool wsrep_recovery                 = 0; // recovery
diff --git a/sql/wsrep_mysqld.h b/sql/wsrep_mysqld.h
index 2814f4013d8..2b55fbe42ee 100644
--- a/sql/wsrep_mysqld.h
+++ b/sql/wsrep_mysqld.h
@@ -30,6 +30,7 @@ typedef struct st_mysql_show_var SHOW_VAR;
 #include "mdl.h"
 #include "mysqld.h"
 #include "sql_table.h"
+#include "wsrep_mysqld_c.h"
 
 #define WSREP_UNDEFINED_TRX_ID ULONGLONG_MAX
 
diff --git a/sql/wsrep_mysqld_c.h b/sql/wsrep_mysqld_c.h
new file mode 100644
index 00000000000..15ca0ae2a6d
--- /dev/null
+++ b/sql/wsrep_mysqld_c.h
@@ -0,0 +1,26 @@
+/* Copyright 2018-2018 Codership Oy <http://www.codership.com>
+
+   This program is free software; you can redistribute it and/or modify
+   it under the terms of the GNU General Public License as published by
+   the Free Software Foundation; version 2 of the License.
+
+   This program is distributed in the hope that it will be useful,
+   but WITHOUT ANY WARRANTY; without even the implied warranty of
+   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+   GNU General Public License for more details.
+
+   You should have received a copy of the GNU General Public License
+   along with this program; if not, write to the Free Software
+   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA */
+
+#ifndef WSREP_MYSQLD_C_H
+#define WSREP_MYSQLD_C_H
+
+enum enum_wsrep_certification_rules {
+    WSREP_CERTIFICATION_RULES_STRICT,
+    WSREP_CERTIFICATION_RULES_OPTIMIZED
+};
+
+extern ulong wsrep_certification_rules;
+
+#endif /* WSREP_MYSQLD_C_H */
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index 702f84a52d1..472fb86288f 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -112,6 +112,7 @@ MYSQL_PLUGIN_IMPORT extern char mysql_unpacked_real_data_home[];
 #include "fts0priv.h"
 #include "page0zip.h"
 #include "fil0pagecompress.h"
+#include "dict0priv.h"
 
 #define thd_get_trx_isolation(X) ((enum_tx_isolation)thd_tx_isolation(X))
 
@@ -134,7 +135,7 @@ MYSQL_PLUGIN_IMPORT extern char mysql_unpacked_real_data_home[];
 # endif /* MYSQL_PLUGIN_IMPORT */
 
 #ifdef WITH_WSREP
-#include "dict0priv.h"
+#include "../../../wsrep/wsrep_api.h"
 #include "../storage/innobase/include/ut0byte.h"
 #include <mysql/service_md5.h>
 
@@ -8449,7 +8450,8 @@ report_error:
 	    && wsrep_thd_exec_mode(user_thd) == LOCAL_STATE
 	    && !wsrep_consistency_check(user_thd)
 	    && !wsrep_thd_ignore_table(user_thd)) {
-		if (wsrep_append_keys(user_thd, false, record, NULL)) {
+		if (wsrep_append_keys(user_thd, WSREP_KEY_EXCLUSIVE, record,
+				      NULL)) {
 			DBUG_PRINT("wsrep", ("row key failed"));
 			error_result = HA_ERR_INTERNAL_ERROR;
 			goto wsrep_error;
@@ -8972,7 +8974,8 @@ func_exit:
         {
 		DBUG_PRINT("wsrep", ("update row key"));
 
-		if (wsrep_append_keys(user_thd, false, old_row, new_row)) {
+		if (wsrep_append_keys(user_thd, WSREP_KEY_EXCLUSIVE, old_row,
+				      new_row)) {
 			WSREP_DEBUG("WSREP: UPDATE_ROW_KEY FAILED");
 			DBUG_PRINT("wsrep", ("row key failed"));
 			err = HA_ERR_INTERNAL_ERROR;
@@ -9036,7 +9039,8 @@ ha_innobase::delete_row(
             wsrep_on(user_thd)                           &&
             !wsrep_thd_ignore_table(user_thd))
         {
-		if (wsrep_append_keys(user_thd, false, record, NULL)) {
+		if (wsrep_append_keys(user_thd, WSREP_KEY_EXCLUSIVE, record,
+			              NULL)) {
 			DBUG_PRINT("wsrep", ("delete fail"));
 			error = (dberr_t) HA_ERR_INTERNAL_ERROR;
 			goto wsrep_error;
@@ -10282,8 +10286,22 @@ wsrep_dict_foreign_find_index(
 	ibool		check_charsets,
 	ulint		check_null);
 
+inline
+const char*
+wsrep_key_type_to_str(wsrep_key_type type)
+{
+	switch (type) {
+	case WSREP_KEY_SHARED:
+		return "shared";
+	case WSREP_KEY_SEMI:
+		return "semi";
+	case WSREP_KEY_EXCLUSIVE:
+		return "exclusive";
+	};
+	return "unknown";
+}
 
-extern dberr_t
+ulint
 wsrep_append_foreign_key(
 /*===========================*/
 	trx_t*		trx,		/*!< in: trx */
@@ -10291,7 +10309,8 @@ wsrep_append_foreign_key(
 	const rec_t*	rec,		/*!<in: clustered index record */
 	dict_index_t*	index,		/*!<in: clustered index */
 	ibool		referenced,	/*!<in: is check for referenced table */
-	ibool		shared)		/*!<in: is shared access */
+	wsrep_key_type	key_type)	/*!< in: access type of this key
+					(shared, exclusive, semi...) */
 {
 	ut_a(trx);
 	THD*  thd = (THD*)trx->mysql_thd;
@@ -10389,10 +10408,11 @@ wsrep_append_foreign_key(
 	rcode = wsrep_rec_get_foreign_key(
 		&key[1], &len, rec, index, idx,
 		wsrep_protocol_version > 1);
+
 	if (rcode != DB_SUCCESS) {
 		WSREP_ERROR(
-			"FK key set failed: %lu (%lu %lu), index: %s %s, %s",
-			rcode, referenced, shared,
+			"FK key set failed: %lu (%lu %s), index: %s %s, %s",
+			rcode, referenced, wsrep_key_type_to_str(key_type),
 			(index && index->name)       ? index->name :
 				"void index",
 			(index && index->table_name) ? index->table_name :
@@ -10400,6 +10420,7 @@ wsrep_append_foreign_key(
 			wsrep_thd_query(thd));
 		return DB_ERROR;
 	}
+
 	strncpy(cache_key,
 		(wsrep_protocol_version > 1) ?
 		((referenced) ?
@@ -10444,7 +10465,7 @@ wsrep_append_foreign_key(
 		wsrep_ws_handle(thd, trx),
 		&wkey,
 		1,
-		shared ? WSREP_KEY_SHARED : WSREP_KEY_EXCLUSIVE,
+		key_type,
                 copy);
 	if (rcode) {
 		DBUG_PRINT("wsrep", ("row key failed: %zu", rcode));
@@ -10466,15 +10487,16 @@ wsrep_append_key(
 	TABLE 		*table,
 	const char*	key,
 	uint16_t        key_len,
-	bool            shared
+	wsrep_key_type	key_type	/*!< in: access type of this key
+					(shared, exclusive, semi...) */
 )
 {
 	DBUG_ENTER("wsrep_append_key");
 	bool const copy = true;
 #ifdef WSREP_DEBUG_PRINT
 	fprintf(stderr, "%s conn %ld, trx %llu, keylen %d, table %s\n Query: %s ",
-		(shared) ? "Shared" : "Exclusive",
-		thd_get_thread_id(thd), (long long)trx->id, key_len,
+		wsrep_key_type_to_str(key_type),
+		wsrep_thd_thread_id(thd), trx->id, key_len,
 		table_share->table_name.str, wsrep_thd_query(thd));
 	for (int i=0; i<key_len; i++) {
 		fprintf(stderr, "%hhX, ", key[i]);
@@ -10501,7 +10523,7 @@ wsrep_append_key(
 			       wsrep_ws_handle(thd, trx),
 			       &wkey,
 			       1,
-			       shared ? WSREP_KEY_SHARED : WSREP_KEY_EXCLUSIVE,
+			       key_type,
                                copy);
 	if (rcode) {
 		DBUG_PRINT("wsrep", ("row key failed: %d", rcode));
@@ -10538,7 +10560,8 @@ int
 ha_innobase::wsrep_append_keys(
 /*==================*/
 	THD 		*thd,
-	bool		shared,
+	wsrep_key_type	key_type,	/*!< in: access type of this key
+					(shared, exclusive, semi...) */
 	const uchar*	record0,	/* in: row in MySQL format */
 	const uchar*	record1)	/* in: row in MySQL format */
 {
@@ -10570,7 +10593,7 @@ ha_innobase::wsrep_append_keys(
 		if (!is_null) {
 			rcode = wsrep_append_key(
 				thd, trx, table_share, table, keyval, 
-				len, shared);
+				len, key_type);
 			if (rcode) DBUG_RETURN(rcode);
 		}
 		else
@@ -10623,10 +10646,11 @@ ha_innobase::wsrep_append_keys(
 				if (!is_null) {
 					rcode = wsrep_append_key(
 						thd, trx, table_share, table, 
-						keyval0, len+1, shared);
+						keyval0, len+1, key_type);
 					if (rcode) DBUG_RETURN(rcode);
 
-				if (key_info->flags & HA_NOSAME || shared)
+					if (key_info->flags & HA_NOSAME ||
+					    key_type == WSREP_KEY_SHARED)
 			  		key_appended = true;
 				}
 				else
@@ -10643,7 +10667,7 @@ ha_innobase::wsrep_append_keys(
 						rcode = wsrep_append_key(
 							thd, trx, table_share, 
 							table, 
-							keyval1, len+1, shared);
+							keyval1, len+1, key_type);
 						if (rcode) DBUG_RETURN(rcode);
 					}
 				}
@@ -10659,7 +10683,7 @@ ha_innobase::wsrep_append_keys(
 		wsrep_calc_row_hash(digest, record0, table, prebuilt, thd);
 		if ((rcode = wsrep_append_key(thd, trx, table_share, table, 
 					      (const char*) digest, 16, 
-					      shared))) {
+					      key_type))) {
 			DBUG_RETURN(rcode);
 		}
 
@@ -10669,7 +10693,7 @@ ha_innobase::wsrep_append_keys(
 			if ((rcode = wsrep_append_key(thd, trx, table_share, 
 						      table,
 						      (const char*) digest, 
-						      16, shared))) {
+						      16, key_type))) {
 				DBUG_RETURN(rcode);
 			}
 		}
diff --git a/storage/innobase/handler/ha_innodb.h b/storage/innobase/handler/ha_innodb.h
index 958a63b23a5..2429c8a3f86 100644
--- a/storage/innobase/handler/ha_innodb.h
+++ b/storage/innobase/handler/ha_innodb.h
@@ -26,6 +26,10 @@ this program; if not, write to the Free Software Foundation, Inc.,
 
 #include "dict0stats.h"
 
+#ifdef WITH_WSREP
+#include "../../../wsrep/wsrep_api.h"
+#endif /* WITH_WSREP */
+
 /* Structure defines translation table between mysql index and innodb
 index structures */
 struct innodb_idx_translate_t {
@@ -115,7 +119,7 @@ class ha_innobase: public handler
 	dict_index_t* innobase_get_index(uint keynr);
 
 #ifdef WITH_WSREP
-	int wsrep_append_keys(THD *thd, bool shared,
+	int wsrep_append_keys(THD *thd, wsrep_key_type key_type,
 			      const uchar* record0, const uchar* record1);
 #endif
 	/* Init values for the class: */
diff --git a/storage/innobase/row/row0ins.cc b/storage/innobase/row/row0ins.cc
index c50eaa2a653..c031c38163e 100644
--- a/storage/innobase/row/row0ins.cc
+++ b/storage/innobase/row/row0ins.cc
@@ -55,6 +55,11 @@ Created 4/20/1996 Heikki Tuuri
 #include "fts0types.h"
 #include "m_string.h"
 
+#ifdef WITH_WSREP
+#include "../../../wsrep/wsrep_api.h"
+#include "wsrep_mysqld_c.h"
+#endif /* WITH_WSREP */
+
 /*************************************************************************
 IMPORTANT NOTE: Any operation that generates redo MUST check that there
 is enough space in the redo log before for that operation. This is
@@ -933,11 +938,11 @@ row_ins_invalidate_query_cache(
 }
 #ifdef WITH_WSREP
 dberr_t wsrep_append_foreign_key(trx_t *trx,
-			       dict_foreign_t*	foreign,
-			       const rec_t*	clust_rec,
-			       dict_index_t*	clust_index,
-			       ibool		referenced,
-			       ibool            shared);
+				dict_foreign_t*	foreign,
+				const rec_t*	clust_rec,
+				dict_index_t*	clust_index,
+				ibool		referenced,
+				enum wsrep_key_type key_type);
 #endif /* WITH_WSREP */
 
 /*********************************************************************//**
@@ -1296,7 +1301,7 @@ row_ins_foreign_check_on_constraint(
 				       foreign,
 				       clust_rec,
 				       clust_index,
-				       FALSE, FALSE);
+				       FALSE, WSREP_KEY_EXCLUSIVE);
 	if (err != DB_SUCCESS) {
 		fprintf(stderr,
 			"WSREP: foreign key append failed: %d\n", err);
@@ -1638,16 +1643,34 @@ run_again:
 				}
 
 				if (check_ref) {
+#ifdef WITH_WSREP
+					enum wsrep_key_type key_type = WSREP_KEY_EXCLUSIVE;
+#endif WITH_WSREP
 					err = DB_SUCCESS;
+
 #ifdef WITH_WSREP
+					if (upd_node != NULL) {
+						key_type = WSREP_KEY_SHARED;
+					} else {
+						switch (wsrep_certification_rules) {
+						case WSREP_CERTIFICATION_RULES_STRICT:
+							key_type = WSREP_KEY_EXCLUSIVE;
+							break;
+						case WSREP_CERTIFICATION_RULES_OPTIMIZED:
+							key_type = WSREP_KEY_SEMI;
+							break;
+						}
+					}
+
 					err = wsrep_append_foreign_key(
 						thr_get_trx(thr),
 						foreign,
 						rec,
 						check_index,
 						check_ref,
-						(upd_node) ? TRUE : FALSE);
-#endif /* WITH_WSREP */
+						key_type);
+ #endif /* WITH_WSREP */
+
 					goto end_scan;
 				} else if (foreign->type != 0) {
 					/* There is an ON UPDATE or ON DELETE
diff --git a/storage/xtradb/handler/ha_innodb.cc b/storage/xtradb/handler/ha_innodb.cc
index d3be5155d27..c9db941b4bf 100644
--- a/storage/xtradb/handler/ha_innodb.cc
+++ b/storage/xtradb/handler/ha_innodb.cc
@@ -110,6 +110,7 @@ this program; if not, write to the Free Software Foundation, Inc.,
 #include "fts0priv.h"
 #include "page0zip.h"
 #include "fil0pagecompress.h"
+#include "dict0priv.h"
 
 #define thd_get_trx_isolation(X) ((enum_tx_isolation)thd_tx_isolation(X))
 
@@ -133,7 +134,7 @@ this program; if not, write to the Free Software Foundation, Inc.,
 # endif /* MYSQL_PLUGIN_IMPORT */
 
 #ifdef WITH_WSREP
-#include "dict0priv.h"
+#include "../../../wsrep/wsrep_api.h"
 #include "../storage/innobase/include/ut0byte.h"
 #include <mysql/service_md5.h>
 
@@ -8894,6 +8895,7 @@ no_commit:
 	error = row_insert_for_mysql((byte*) record, prebuilt);
 	DEBUG_SYNC(user_thd, "ib_after_row_insert");
 
+
 	/* Handle duplicate key errors */
 	if (auto_inc_used) {
 		ulonglong	auto_inc;
@@ -9035,7 +9037,7 @@ report_error:
  	    && wsrep_thd_exec_mode(user_thd) == LOCAL_STATE
 	    && !wsrep_consistency_check(user_thd)
 	    && !wsrep_thd_ignore_table(user_thd)) {
-		if (wsrep_append_keys(user_thd, false, record, NULL)) {
+		if (wsrep_append_keys(user_thd, WSREP_KEY_EXCLUSIVE, record, NULL)) {
 			DBUG_PRINT("wsrep", ("row key failed"));
 			error_result = HA_ERR_INTERNAL_ERROR;
 			goto wsrep_error;
@@ -9556,7 +9558,8 @@ func_exit:
         {
 		DBUG_PRINT("wsrep", ("update row key"));
 
-		if (wsrep_append_keys(user_thd, false, old_row, new_row)) {
+		if (wsrep_append_keys(user_thd, WSREP_KEY_EXCLUSIVE, old_row,
+				      new_row)) {
 			WSREP_DEBUG("WSREP: UPDATE_ROW_KEY FAILED");
 			DBUG_PRINT("wsrep", ("row key failed"));
 			err = HA_ERR_INTERNAL_ERROR;
@@ -9619,7 +9622,8 @@ ha_innobase::delete_row(
             wsrep_on(user_thd)                           &&
             !wsrep_thd_ignore_table(user_thd))
         {
-		if (wsrep_append_keys(user_thd, false, record, NULL)) {
+		if (wsrep_append_keys(user_thd, WSREP_KEY_EXCLUSIVE, record,
+				      NULL)) {
 			DBUG_PRINT("wsrep", ("delete fail"));
 			error = (dberr_t) HA_ERR_INTERNAL_ERROR;
 			goto wsrep_error;
@@ -10850,8 +10854,22 @@ wsrep_dict_foreign_find_index(
 	ibool		check_charsets,
 	ulint		check_null);
 
+inline
+const char*
+wsrep_key_type_to_str(wsrep_key_type type)
+{
+	switch (type) {
+	case WSREP_KEY_SHARED:
+		return "shared";
+	case WSREP_KEY_SEMI:
+		return "semi";
+	case WSREP_KEY_EXCLUSIVE:
+		return "exclusive";
+	};
+	return "unknown";
+}
 
-extern dberr_t
+ulint
 wsrep_append_foreign_key(
 /*===========================*/
 	trx_t*		trx,		/*!< in: trx */
@@ -10859,7 +10877,8 @@ wsrep_append_foreign_key(
 	const rec_t*	rec,		/*!<in: clustered index record */
 	dict_index_t*	index,		/*!<in: clustered index */
 	ibool		referenced,	/*!<in: is check for referenced table */
-	ibool		shared)		/*!<in: is shared access */
+	wsrep_key_type	key_type)	/*!< in: access type of this key
+					(shared, exclusive, semi...) */
 {
 	ut_a(trx);
 	THD*  thd = (THD*)trx->mysql_thd;
@@ -10957,10 +10976,11 @@ wsrep_append_foreign_key(
 	rcode = wsrep_rec_get_foreign_key(
 		&key[1], &len, rec, index, idx,
 		wsrep_protocol_version > 1);
+
 	if (rcode != DB_SUCCESS) {
 		WSREP_ERROR(
-			"FK key set failed: %lu (%lu %lu), index: %s %s, %s",
-			rcode, referenced, shared,
+			"FK key set failed: %lu (%lu %s), index: %s %s, %s",
+			rcode, referenced, wsrep_key_type_to_str(key_type),
 			(index && index->name)       ? index->name :
 				"void index",
 			(index && index->table_name) ? index->table_name :
@@ -10968,6 +10988,7 @@ wsrep_append_foreign_key(
 			wsrep_thd_query(thd));
 		return DB_ERROR;
 	}
+
 	strncpy(cache_key,
 		(wsrep_protocol_version > 1) ?
 		((referenced) ?
@@ -11012,7 +11033,7 @@ wsrep_append_foreign_key(
 		wsrep_ws_handle(thd, trx),
 		&wkey,
 		1,
-		shared ? WSREP_KEY_SHARED : WSREP_KEY_EXCLUSIVE,
+		key_type,
                 copy);
 	if (rcode) {
 		DBUG_PRINT("wsrep", ("row key failed: %zu", rcode));
@@ -11034,14 +11055,15 @@ wsrep_append_key(
 	TABLE 		*table,
 	const char*	key,
 	uint16_t        key_len,
-	bool            shared
+	wsrep_key_type	key_type	/*!< in: access type of this key
+					(shared, exclusive, semi...) */
 )
 {
 	DBUG_ENTER("wsrep_append_key");
 	bool const copy = true;
 #ifdef WSREP_DEBUG_PRINT
 	fprintf(stderr, "%s conn %ld, trx %llu, keylen %d, table %s\n Query: %s ",
-		(shared) ? "Shared" : "Exclusive",
+		wsrep_key_type_to_str(key_type),
 		thd_get_thread_id(thd), (long long)trx->id, key_len,
 		table_share->table_name.str, wsrep_thd_query(thd));
 	for (int i=0; i<key_len; i++) {
@@ -11069,7 +11091,7 @@ wsrep_append_key(
 			       wsrep_ws_handle(thd, trx),
 			       &wkey,
 			       1,
-			       shared ? WSREP_KEY_SHARED : WSREP_KEY_EXCLUSIVE,
+			       key_type,
                                copy);
 	if (rcode) {
 		DBUG_PRINT("wsrep", ("row key failed: %d", rcode));
@@ -11106,7 +11128,8 @@ int
 ha_innobase::wsrep_append_keys(
 /*==================*/
 	THD 		*thd,
-	bool		shared,
+	wsrep_key_type	key_type,	/*!< in: access type of this key
+					(shared, exclusive, semi...) */
 	const uchar*	record0,	/* in: row in MySQL format */
 	const uchar*	record1)	/* in: row in MySQL format */
 {
@@ -11138,7 +11161,7 @@ ha_innobase::wsrep_append_keys(
 		if (!is_null) {
 			rcode = wsrep_append_key(
 				thd, trx, table_share, table, keyval, 
-				len, shared);
+				len, key_type);
 			if (rcode) DBUG_RETURN(rcode);
 		}
 		else
@@ -11191,10 +11214,11 @@ ha_innobase::wsrep_append_keys(
 				if (!is_null) {
 					rcode = wsrep_append_key(
 						thd, trx, table_share, table, 
-						keyval0, len+1, shared);
+						keyval0, len+1, key_type);
 					if (rcode) DBUG_RETURN(rcode);
 
-				if (key_info->flags & HA_NOSAME || shared)
+					if (key_info->flags & HA_NOSAME ||
+					    key_type == WSREP_KEY_SHARED)
 			  		key_appended = true;
 				}
 				else
@@ -11211,7 +11235,7 @@ ha_innobase::wsrep_append_keys(
 						rcode = wsrep_append_key(
 							thd, trx, table_share, 
 							table, 
-							keyval1, len+1, shared);
+							keyval1, len+1, key_type);
 						if (rcode) DBUG_RETURN(rcode);
 					}
 				}
@@ -11227,7 +11251,7 @@ ha_innobase::wsrep_append_keys(
 		wsrep_calc_row_hash(digest, record0, table, prebuilt, thd);
 		if ((rcode = wsrep_append_key(thd, trx, table_share, table, 
 					      (const char*) digest, 16, 
-					      shared))) {
+					      key_type))) {
 			DBUG_RETURN(rcode);
 		}
 
@@ -11237,7 +11261,7 @@ ha_innobase::wsrep_append_keys(
 			if ((rcode = wsrep_append_key(thd, trx, table_share, 
 						      table,
 						      (const char*) digest, 
-						      16, shared))) {
+						      16, key_type))) {
 				DBUG_RETURN(rcode);
 			}
 		}
diff --git a/storage/xtradb/handler/ha_innodb.h b/storage/xtradb/handler/ha_innodb.h
index 97d822ac186..48ec8ba5bae 100644
--- a/storage/xtradb/handler/ha_innodb.h
+++ b/storage/xtradb/handler/ha_innodb.h
@@ -26,6 +26,10 @@ this program; if not, write to the Free Software Foundation, Inc.,
 
 #include "dict0stats.h"
 
+#ifdef WITH_WSREP
+#include "../../../wsrep/wsrep_api.h"
+#endif /* WITH_WSREP */
+
 /* Structure defines translation table between mysql index and innodb
 index structures */
 struct innodb_idx_translate_t {
@@ -115,7 +119,7 @@ class ha_innobase: public handler
 	dict_index_t* innobase_get_index(uint keynr);
 
 #ifdef WITH_WSREP
-	int wsrep_append_keys(THD *thd, bool shared,
+	int wsrep_append_keys(THD *thd, wsrep_key_type key_type,
 			      const uchar* record0, const uchar* record1);
 #endif
 	/* Init values for the class: */
diff --git a/storage/xtradb/row/row0ins.cc b/storage/xtradb/row/row0ins.cc
index 472be8ad848..f146d1f68b1 100644
--- a/storage/xtradb/row/row0ins.cc
+++ b/storage/xtradb/row/row0ins.cc
@@ -55,6 +55,11 @@ Created 4/20/1996 Heikki Tuuri
 #include "fts0types.h"
 #include "m_string.h"
 
+#ifdef WITH_WSREP
+#include "../../../wsrep/wsrep_api.h"
+#include "wsrep_mysqld_c.h"
+#endif /* WITH_WSREP */
+
 /*************************************************************************
 IMPORTANT NOTE: Any operation that generates redo MUST check that there
 is enough space in the redo log before for that operation. This is
@@ -939,11 +944,11 @@ row_ins_invalidate_query_cache(
 }
 #ifdef WITH_WSREP
 dberr_t wsrep_append_foreign_key(trx_t *trx,
-			       dict_foreign_t*	foreign,
-			       const rec_t*	clust_rec,
-			       dict_index_t*	clust_index,
-			       ibool		referenced,
-			       ibool            shared);
+				dict_foreign_t*	foreign,
+				const rec_t*	clust_rec,
+				dict_index_t*	clust_index,
+				ibool		referenced,
+				enum wsrep_key_type key_type);
 #endif /* WITH_WSREP */
 
 /*********************************************************************//**
@@ -1302,7 +1307,7 @@ row_ins_foreign_check_on_constraint(
 				       foreign,
 				       clust_rec,
 				       clust_index,
-				       FALSE, FALSE);
+				       FALSE, WSREP_KEY_EXCLUSIVE);
 	if (err != DB_SUCCESS) {
 		fprintf(stderr,
 			"WSREP: foreign key append failed: %d\n", err);
@@ -1650,16 +1655,34 @@ run_again:
 				}
 
 				if (check_ref) {
+#ifdef WITH_WSREP
+					enum wsrep_key_type key_type = WSREP_KEY_EXCLUSIVE;
+#endif /* WITH_WSREP */
 					err = DB_SUCCESS;
+
 #ifdef WITH_WSREP
+					if (upd_node != NULL) {
+						key_type = WSREP_KEY_SHARED;
+					} else {
+						switch (wsrep_certification_rules) {
+						case WSREP_CERTIFICATION_RULES_STRICT:
+							key_type = WSREP_KEY_EXCLUSIVE;
+							break;
+						case WSREP_CERTIFICATION_RULES_OPTIMIZED:
+							key_type = WSREP_KEY_SEMI;
+							break;
+						}
+					}
+
 					err = wsrep_append_foreign_key(
 						thr_get_trx(thr),
 						foreign,
 						rec,
 						check_index,
 						check_ref,
-						(upd_node) ? TRUE : FALSE);
-#endif /* WITH_WSREP */
+						key_type);
+ #endif /* WITH_WSREP */
+
 					goto end_scan;
 				} else if (foreign->type != 0) {
 					/* There is an ON UPDATE or ON DELETE
diff --git a/zlib/CMakeLists.txt b/zlib/CMakeLists.txt
index 0c224e7ce22..d09f6c13f50 100644
--- a/zlib/CMakeLists.txt
+++ b/zlib/CMakeLists.txt
@@ -116,7 +116,6 @@ if(NOT MINGW)
         win32/zlib1.rc # If present will override custom build rule below.
     )
 endif()
-
 # parse the full version number from zlib.h and include in ZLIB_FULL_VERSION
 file(READ ${CMAKE_CURRENT_SOURCE_DIR}/zlib.h _zlib_h_contents)
 string(REGEX REPLACE ".*#define[ \t]+ZLIB_VERSION[ \t]+\"([-0-9A-Za-z.]+)\".*"
@@ -145,4 +144,4 @@ elseif(UNIX)
    if(NOT APPLE)
      set_target_properties(zlib PROPERTIES LINK_FLAGS "-Wl,--version-script,\"${CMAKE_CURRENT_SOURCE_DIR}/zlib.map\"")
    endif()
-endif()
+endif()
\ No newline at end of file