# This test scripts covers upgrade of tables, views, tablespaces,
# events, stored routines and triggers from 5.7 to 8.0.
# Set different paths for --datadir
# Copy the remote tablespace & DB zip files from suite location to working location.
# Check that the file exists in the working folder.
# Unzip the zip file.
#
# BUG#25805260: MYSQL 8.0.X CRASHES WHEN OLD-STYLE TRIGGER MISSES THE
# "CREATED" LINE IN .TRG.
#
# Create a trigger without 'created' line. Without patch,
# the server exits during upgrade of trigger.
# Create a table as same as mysql.proc to repeat Bug#24805140
# We need to add an entry in mysql.proc table of the zipped
# data data directory before upgrade starts.
# Without fix, data population for dictionary tables fail and
# upgrade aborts.
SET sql_mode='';
# CREATE statement is same as mysql.proc in mysql-5.7.
CREATE TABLE test.proc (db char(64) collate utf8_bin DEFAULT '' NOT NULL,
name char(64) DEFAULT '' NOT NULL,
type enum('FUNCTION','PROCEDURE') NOT NULL,
specific_name char(64) DEFAULT '' NOT NULL,
language enum('SQL') DEFAULT 'SQL' NOT NULL,
sql_data_access enum('CONTAINS_SQL',
'NO_SQL',
'READS_SQL_DATA',
'MODIFIES_SQL_DATA')
DEFAULT 'CONTAINS_SQL' NOT NULL,
is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL,
security_type enum('INVOKER','DEFINER')
DEFAULT 'DEFINER' NOT NULL,
param_list blob NOT NULL,
returns longblob DEFAULT '' NOT NULL,
body longblob NOT NULL,
definer char(93) collate utf8_bin DEFAULT '' NOT NULL,
created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
modified timestamp NOT NULL
DEFAULT '0000-00-00 00:00:00',
sql_mode set('REAL_AS_FLOAT', 'PIPES_AS_CONCAT',
'ANSI_QUOTES', 'IGNORE_SPACE', 'NOT_USED',
'ONLY_FULL_GROUP_BY',
'NO_UNSIGNED_SUBTRACTION',
'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE',
'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS',
'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS',
'MYSQL323', 'MYSQL40', 'ANSI',
'NO_AUTO_VALUE_ON_ZERO',
'NO_BACKSLASH_ESCAPES',
'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES',
'NO_ZERO_IN_DATE', 'NO_ZERO_DATE',
'INVALID_DATES',
'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL',
'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE',
'NO_ENGINE_SUBSTITUTION',
'PAD_CHAR_TO_FULL_LENGTH') DEFAULT '' NOT NULL,
comment text collate utf8_bin NOT NULL,
character_set_client char(32) collate utf8_bin,
collation_connection char(32) collate utf8_bin,
db_collation char(32) collate utf8_bin, body_utf8 longblob,
PRIMARY KEY (db,name,type)) engine=MyISAM character set utf8
comment='Stored Procedures';
Warnings:
Warning	3778	'utf8_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
Warning	3778	'utf8_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
Warning	3778	'utf8_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
Warning	3778	'utf8_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
Warning	3778	'utf8_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
Warning	3778	'utf8_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
Warning	1101	BLOB, TEXT, GEOMETRY or JSON column 'returns' can't have a default value
# Remove data and index files manually.
# Copy data and index files from zipped data directory.
# Insert data
INSERT INTO test.proc VALUES ('sp','bug24805140','PROCEDURE','bug24805140','SQL',
'CONTAINS_SQL','NO','DEFINER','out a int','',
'begin select requesting_trx_id from '
                              'information_schema.INNODB_LOCK_WAITS limit 1 into a; end',
'root@localhost','2016-10-05 21:44:21',
'2016-10-05 21:44:21',
'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,'
                              'NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,'
                              'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION',
'','utf8','utf8_general_ci','latin1_swedish_ci',
'begin select requesting_trx_id from '
                              'information_schema.INNODB_LOCK_WAITS limit 1 into a; end'),
('sp','proc2','PROCEDURE','proc2','SQL',
'CONTAINS_SQL','NO','DEFINER','out b int','',
'begin select   @@show_compatibility_56 into b; end',
'root@localhost','2016-10-05 21:55:05',
'2016-10-05 21:55:05','ONLY_FULL_GROUP_BY,'
                              'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,'
                              'NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,'
                              'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION','',
'utf8','utf8_general_ci','latin1_swedish_ci',
'begin select   @@show_compatibility_56 into b; end');
# Data for Bug#25633041 : SHOW CREATE PROC/FUNCTION RESULT
#                         AFTER LIVEUPGR IS DIFFERENT TO DUMPUPGR
INSERT INTO `proc` VALUES ('test','downgrade_alter_proc','PROCEDURE',
'downgrade_alter_proc',
'SQL','CONTAINS_SQL','NO','INVOKER','','',
'BEGIN\n      SELECT c1, English, French FROM t1 '
                           'JOIN t2 ON t1.c3 = t2.col2;\n     END',
'root@localhost','1988-04-25 20:45:00',
'1988-04-25 20:45:00','NO_ZERO_DATE','','latin1',
'latin1_swedish_ci','latin1_swedish_ci',
'BEGIN\n      SELECT c1, English, French FROM t1 '
                           'JOIN t2 ON t1.c3 = t2.col2;\n     END'),
('test','my_test_func','FUNCTION','myfunc','SQL',
'CONTAINS_SQL','NO','DEFINER', '',
'varchar(20) CHARSET latin1',
'BEGIN\n  RETURN \'å\';\nEND',
'root@localhost','2017-03-08 09:07:36',
'2017-03-08 09:07:36', 'ONLY_FULL_GROUP_BY,'
                           'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,'
                           'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,'
                           'NO_ENGINE_SUBSTITUTION','','latin1','latin1_swedish_ci',
'latin1_swedish_ci','BEGIN\n  RETURN \'Ã¥\';\nEND');
# Remove  the original zipped data and index files.
# Copy data and index files to zipped data directory.
# Delete the table for cleanup
DROP TABLE test.proc;
# Stop DB server which was created by MTR default
# These files are added to test error scenario, delete from for upgrade testing.
# Remove myisam partitioned tables. There are used for negative testing.
# Copy table and view files as test case for Bug#24580586
# Test case for Bug #25139901 : DB COLLATION CHANGED TO SERVER DEFAULT AFTER UPGRADE
# Create a folder and db.opt file. Write a character set in db.opt file which is not default.
# 1.1 Create db.opt file.
# Files in 'test' dir
auto.cnf
aview
db_charset_koi8r
events
foreign_keys
ib_buffer_pool
ib_logfile0
ib_logfile1
ibdata1
mysql
performance_schema
sakila
sp
sys
tablespace
test
test_mismatch
test_tablespace_2.ibd
test_tablespace_3.ibd
triggers
view_with_column_names
# Start the DB server. Server will create and populate Dictionary tables.
# Test for Bug#25518436 : MYSQL 8.0.1 - MYSQLD ERRORLOG HAS UPGRADE ERRORS
#                         AT SERVER START AT LIVE UPGRADE
# Check for errors from sys schema.
# These errors should not be there.
# Look for error.
# Sys Schema routine parsing warning found : 0 times.
# Sys Schema routine parsing error found : 0 times.
# Sys Schema view parsing warning found : 0 times.
# Sys Schema view parsing error found : 0 times.
# Sanity Testing after Server start
CREATE SCHEMA test123;
CREATE TABLE test123.t1(a int);
INSERT INTO test123.t1 values(1);
SELECT * FROM test123.t1;
a
1
DROP SCHEMA test123;
CHECK TABLE test.t_myisam_compressed2, test.t_myisam_compressed3 FOR UPGRADE;
Table	Op	Msg_type	Msg_text
test.t_myisam_compressed2	check	status	Table is already up to date
test.t_myisam_compressed3	check	status	Table is already up to date
SHOW DATABASES;
Database
__recycle_bin__
aview
db_charset_koi8r
events
foreign_keys
information_schema
mysql
performance_schema
sakila
sp
sys
tablespace
test
test_mismatch
triggers
view_with_column_names
# Check table structures
SHOW CREATE TABLE mysql.user;
Table	Create Table
user	CREATE TABLE `user` (
  `Host` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'caching_sha2_password',
  `authentication_string` text COLLATE utf8_bin,
  `password_expired` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `password_last_changed` timestamp NULL DEFAULT NULL,
  `password_lifetime` smallint(5) unsigned DEFAULT NULL,
  `account_locked` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Create_role_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Drop_role_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Password_reuse_history` smallint(5) unsigned DEFAULT NULL,
  `Password_reuse_time` smallint(5) unsigned DEFAULT NULL,
  `Password_require_current` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `User_attributes` json DEFAULT NULL,
  PRIMARY KEY (`Host`,`User`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC COMMENT='Users and global privileges'
SHOW CREATE TABLE mysql.innodb_table_stats;
Table	Create Table
innodb_table_stats	CREATE TABLE `innodb_table_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(199) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `n_rows` bigint(20) unsigned NOT NULL,
  `clustered_index_size` bigint(20) unsigned NOT NULL,
  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC
SHOW CREATE TABLE mysql.innodb_index_stats;
Table	Create Table
innodb_index_stats	CREATE TABLE `innodb_index_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(199) COLLATE utf8_bin NOT NULL,
  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `stat_value` bigint(20) unsigned NOT NULL,
  `sample_size` bigint(20) unsigned DEFAULT NULL,
  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC
SELECT COLUMN_NAME,CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS col
JOIN INFORMATION_SCHEMA.TABLES tab ON col.table_name=tab.table_name
WHERE col.TABLE_NAME LIKE '%innodb_%_stats'
    AND col.COLUMN_NAME LIKE 'table_name';
COLUMN_NAME	CHARACTER_MAXIMUM_LENGTH
table_name	199
table_name	199
SHOW CREATE TABLE test.t_compressed;
Table	Create Table
t_compressed	CREATE TABLE `t_compressed` (
  `c1` int(11) NOT NULL,
  `c2` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
SHOW CREATE TABLE test.t_compressed2;
Table	Create Table
t_compressed2	CREATE TABLE `t_compressed2` (
  `c1` int(11) NOT NULL,
  `c2` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
SHOW CREATE TABLE test.t_compressed3;
Table	Create Table
t_compressed3	CREATE TABLE `t_compressed3` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
SHOW CREATE TABLE test.t_dynamic;
Table	Create Table
t_dynamic	CREATE TABLE `t_dynamic` (
  `c1` int(11) NOT NULL,
  `c2` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
SHOW CREATE TABLE test.t_index;
Table	Create Table
t_index	CREATE TABLE `t_index` (
  `c1` int(11) NOT NULL,
  `c2` int(11) NOT NULL,
  PRIMARY KEY (`c1`),
  KEY `c2` (`c2`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SHOW CREATE TABLE test.vt2;
Table	Create Table
vt2	CREATE TABLE `vt2` (
  `c1` text NOT NULL,
  `c2` text GENERATED ALWAYS AS (substr(`c1`,1,2)) VIRTUAL,
  PRIMARY KEY (`c1`(20)),
  UNIQUE KEY `c2` (`c2`(2))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SELECT * FROM test.vt2;
c1	c2
abcd	ab
SHOW CREATE TABLE test.t_gen_stored;
Table	Create Table
t_gen_stored	CREATE TABLE `t_gen_stored` (
  `a` varchar(50) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (substr(`a`,5)) STORED
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SHOW CREATE TABLE test.jemp;
Table	Create Table
jemp	CREATE TABLE `jemp` (
  `c` json DEFAULT NULL,
  `g` int(11) GENERATED ALWAYS AS (json_extract(`c`,_utf8mb4'$.id')) VIRTUAL,
  KEY `i` (`g`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SHOW CREATE TABLE test.t_gen_stored_myisam;
Table	Create Table
t_gen_stored_myisam	CREATE TABLE `t_gen_stored_myisam` (
  `a` varchar(50) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (substr(`a`,5)) STORED
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SHOW CREATE TABLE test.t_gen_stored_myisam2;
Table	Create Table
t_gen_stored_myisam2	CREATE TABLE `t_gen_stored_myisam2` (
  `a` varchar(50) DEFAULT NULL,
  `b` varchar(10) GENERATED ALWAYS AS (substr(`a`,5)) STORED,
  KEY `a` (`a`,`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SHOW CREATE TABLE test.jemp_myisam;
Table	Create Table
jemp_myisam	CREATE TABLE `jemp_myisam` (
  `c` json DEFAULT NULL,
  `g` int(11) GENERATED ALWAYS AS (json_extract(`c`,_utf8mb4'$.id')) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, VERSION, ROW_FORMAT
FROM INFORMATION_SCHEMA.tables WHERE table_schema='test';
TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	ENGINE	VERSION	ROW_FORMAT
test	child	BASE TABLE	InnoDB	10	Dynamic
test	geom	BASE TABLE	InnoDB	10	Dynamic
test	initial_tü	BASE TABLE	BLACKHOLE	10	Fixed
test	initial_vü	VIEW	NULL	NULL	NULL
test	jemp	BASE TABLE	InnoDB	10	Dynamic
test	jemp_myisam	BASE TABLE	MyISAM	10	Dynamic
test	opening_lines	BASE TABLE	InnoDB	10	Dynamic
test	parent	BASE TABLE	InnoDB	10	Dynamic
test	t_blackhole	BASE TABLE	BLACKHOLE	10	Fixed
test	t_blob	BASE TABLE	InnoDB	10	Dynamic
test	t_blob_myisam	BASE TABLE	InnoDB	10	Dynamic
test	t_compressed	BASE TABLE	InnoDB	10	Compressed
test	t_compressed2	BASE TABLE	InnoDB	10	Compressed
test	t_compressed3	BASE TABLE	InnoDB	10	Compressed
test	t_dynamic	BASE TABLE	InnoDB	10	Dynamic
test	t_gen_stored	BASE TABLE	InnoDB	10	Dynamic
test	t_gen_stored_myisam	BASE TABLE	MyISAM	10	Dynamic
test	t_gen_stored_myisam2	BASE TABLE	MyISAM	10	Dynamic
test	t_index	BASE TABLE	MyISAM	10	Fixed
test	t_json	BASE TABLE	InnoDB	10	Dynamic
test	t_myisam_compressed	BASE TABLE	MyISAM	10	Dynamic
test	t_myisam_compressed2	BASE TABLE	MyISAM	10	Compressed
test	t_myisam_compressed3	BASE TABLE	MyISAM	10	Compressed
test	t_sc~!@#$%^&*(	BASE TABLE	InnoDB	10	Dynamic
test	vt2	BASE TABLE	InnoDB	10	Dynamic
# Check for views
# Invalid view
SHOW CREATE VIEW aview.view_invalid;
View	Create View	character_set_client	collation_connection
view_invalid	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `aview`.`view_invalid` AS select `aview`.`t`.`a` AS `column1`,`aview`.`t`.`b` AS `column2` from `aview`.`t`	utf8	utf8_general_ci
Warnings:
Warning	1356	View 'aview.view_invalid' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
# Valid views
SHOW CREATE VIEW aview.view_2;
View	Create View	character_set_client	collation_connection
view_2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `aview`.`view_2` AS select `aview`.`t1`.`a` AS `column1`,`aview`.`t1`.`b` AS `column2`,`aview`.`t1`.`c` AS `column3`,`aview`.`t2`.`a` AS `column4` from (`aview`.`t1` join `aview`.`t2`)	utf8	utf8_general_ci
SHOW CREATE VIEW aview.second_view;
View	Create View	character_set_client	collation_connection
second_view	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `aview`.`second_view` AS select `view_2`.`column1` AS `column1`,`view_2`.`column2` AS `column2`,`view_2`.`column3` AS `column3`,`view_2`.`column4` AS `column4` from `aview`.`view_2`	utf8	utf8_general_ci
SELECT * FROM aview.view_2;
column1	column2	column3	column4
50	ABCD	EFGH	100
SELECT * FROM aview.second_view;
column1	column2	column3	column4
50	ABCD	EFGH	100
SHOW CREATE VIEW aview.mixed_view;
View	Create View	character_set_client	collation_connection
mixed_view	CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `aview`.`mixed_view` AS select `second_view`.`column3` AS `column3`,`second_view`.`column4` AS `column4`,`hello`('FUNC') AS `hello('FUNC')` from `aview`.`second_view` WITH CASCADED CHECK OPTION	utf8	utf8_general_ci
SHOW CREATE VIEW aview.mixed_view2;
View	Create View	character_set_client	collation_connection
mixed_view2	CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `aview`.`mixed_view2` AS select `second_view`.`column3` AS `column3`,`second_view`.`column4` AS `column4`,`hello`('FUNC') AS `hello('FUNC')` from `aview`.`second_view` WITH LOCAL CHECK OPTION	utf8	utf8_general_ci
SELECT * FROM aview.mixed_view2;
column3	column4	hello('FUNC')
EFGH	100	Hello, FUNC!
# View with different definer
SHOW CREATE VIEW aview.view_user;
View	Create View	character_set_client	collation_connection
view_user	CREATE ALGORITHM=UNDEFINED DEFINER=`jeffrey`@`localhost` SQL SECURITY DEFINER VIEW `aview`.`view_user` AS select 1 AS `1`	utf8	utf8_general_ci
SHOW CREATE VIEW aview.view_user2;
View	Create View	character_set_client	collation_connection
view_user2	CREATE ALGORITHM=UNDEFINED DEFINER=`abcd`@`localhost` SQL SECURITY INVOKER VIEW `aview`.`view_user2` AS select 1 AS `1`	utf8	utf8_general_ci
# View with different character_set and connection collation
SHOW CREATE VIEW aview.view_character_set;
View	Create View	character_set_client	collation_connection
view_character_set	CREATE ALGORITHM=UNDEFINED DEFINER=`jeffrey`@`localhost` SQL SECURITY DEFINER VIEW `aview`.`view_character_set` AS select 1 AS `1`	latin1	latin1_swedish_ci
# Data check after upgrade for Bug#25139901
SHOW CREATE DATABASE db_charset_koi8r;
Database	Create Database
db_charset_koi8r	CREATE DATABASE `db_charset_koi8r` /*!40100 DEFAULT CHARACTER SET koi8r COLLATE koi8r_bin */ /*!80016 DEFAULT ENCRYPTION='N' */
# Test case for Bug#24580586
SET names utf8;
Warnings:
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
DESC `test`.`initial_vü`;
Field	Type	Null	Key	Default	Extra
cü	char(1)	YES		NULL	
SELECT * FROM information_schema.tables WHERE table_schema = 'test' and table_type='VIEW';
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	ENGINE	VERSION	ROW_FORMAT	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	AUTO_INCREMENT	CREATE_TIME	UPDATE_TIME	CHECK_TIME	TABLE_COLLATION	CHECKSUM	CREATE_OPTIONS	TABLE_COMMENT
def	test	initial_vü	VIEW	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	CREATE_TIME	NULL	NULL	NULL	NULL	NULL	VIEW
SET names default;
#Test case for Bug#26636238
SHOW CREATE TABLE performance_schema.threads;
Table	Create Table
threads	CREATE TABLE `threads` (
  `THREAD_ID` bigint(20) unsigned NOT NULL,
  `NAME` varchar(128) NOT NULL,
  `TYPE` varchar(10) NOT NULL,
  `PROCESSLIST_ID` bigint(20) unsigned DEFAULT NULL,
  `PROCESSLIST_USER` varchar(32) DEFAULT NULL,
  `PROCESSLIST_HOST` varchar(255) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL,
  `PROCESSLIST_DB` varchar(64) DEFAULT NULL,
  `PROCESSLIST_COMMAND` varchar(16) DEFAULT NULL,
  `PROCESSLIST_TIME` bigint(20) DEFAULT NULL,
  `PROCESSLIST_STATE` varchar(64) DEFAULT NULL,
  `PROCESSLIST_INFO` longtext,
  `PARENT_THREAD_ID` bigint(20) unsigned DEFAULT NULL,
  `ROLE` varchar(64) DEFAULT NULL,
  `INSTRUMENTED` enum('YES','NO') NOT NULL,
  `HISTORY` enum('YES','NO') NOT NULL,
  `CONNECTION_TYPE` varchar(16) DEFAULT NULL,
  `THREAD_OS_ID` bigint(20) unsigned DEFAULT NULL,
  `RESOURCE_GROUP` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`THREAD_ID`),
  KEY `PROCESSLIST_ID` (`PROCESSLIST_ID`),
  KEY `THREAD_OS_ID` (`THREAD_OS_ID`),
  KEY `NAME` (`NAME`),
  KEY `PROCESSLIST_ACCOUNT` (`PROCESSLIST_USER`,`PROCESSLIST_HOST`),
  KEY `PROCESSLIST_HOST` (`PROCESSLIST_HOST`),
  KEY `RESOURCE_GROUP` (`RESOURCE_GROUP`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
# Check for SP/SF
SHOW CREATE PROCEDURE sp.simpleproc;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
simpleproc	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleproc`(OUT param1 INT)
BEGIN
  SELECT COUNT(*) INTO param1 FROM t;
END	utf8	utf8_general_ci	latin1_swedish_ci
SHOW CREATE PROCEDURE sp.proc_123;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
proc_123	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_123`(OUT param1 INT)
    READS SQL DATA
    SQL SECURITY INVOKER
    COMMENT 'Procedure to test Upgrade'
BEGIN   SELECT COUNT(*) INTO param1 FROM t; END	utf8	utf8_general_ci	latin1_swedish_ci
SHOW CREATE PROCEDURE sp.proc_456;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
proc_456	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_456`(OUT param1 INT)
    MODIFIES SQL DATA
    DETERMINISTIC
    COMMENT 'Procedure to test Upgrade 2'
BEGIN   SELECT COUNT(*) INTO param1 FROM t; END	utf8	utf8_general_ci	latin1_swedish_ci
# Without fix, server start on 5.7 data directory above will fail.
SHOW CREATE PROCEDURE sp.bug24805140;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
bug24805140	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `bug24805140`(out a int)
begin select requesting_trx_id from information_schema.INNODB_LOCK_WAITS limit 1 into a; end	utf8	utf8_general_ci	latin1_swedish_ci
call sp.bug24805140();
ERROR HY000: Failed to load routine 'sp.bug24805140'.
SHOW WARNINGS;
Level	Code	Message
Error	1109	Unknown table 'INNODB_LOCK_WAITS' in information_schema
Error	3512	Failed to load routine 'sp.bug24805140'.
SHOW CREATE FUNCTION sp.hello;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
hello	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `hello`(s CHAR(20)) RETURNS char(50) CHARSET latin1
    DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!')	utf8	utf8_general_ci	latin1_swedish_ci
# Check for events
SHOW CREATE EVENT events.e_totals;
Event	sql_mode	time_zone	Create Event	character_set_client	collation_connection	Database Collation
e_totals	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION	SYSTEM	CREATE DEFINER=`root`@`localhost` EVENT `e_totals` ON SCHEDULE AT '2017-02-10 21:29:00' ON COMPLETION NOT PRESERVE ENABLE DO INSERT INTO events.event_table_2 VALUES (NOW())	utf8	utf8_general_ci	latin1_swedish_ci
# Check for fulltext index
SHOW CREATE TABLE test.opening_lines;
Table	Create Table
opening_lines	CREATE TABLE `opening_lines` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `opening_line` text,
  `author` varchar(200) DEFAULT NULL,
  `title` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `idx` (`opening_line`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
# Check data for Bug#25633041
SHOW CREATE PROCEDURE test.downgrade_alter_proc;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
downgrade_alter_proc	NO_ZERO_DATE	CREATE DEFINER=`root`@`localhost` PROCEDURE `downgrade_alter_proc`()
    SQL SECURITY INVOKER
BEGIN
      SELECT c1, English, French FROM t1 JOIN t2 ON t1.c3 = t2.col2;
     END	latin1	latin1_swedish_ci	latin1_swedish_ci
SHOW CREATE FUNCTION test.my_test_func;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
my_test_func	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `my_test_func`() RETURNS varchar(20) CHARSET latin1
BEGIN
  RETURN 'Ã¥';
END	latin1	latin1_swedish_ci	latin1_swedish_ci
SELECT ROUTINE_NAME, CHARACTER_SET_NAME, CHARACTER_SET_CLIENT,  COLLATION_CONNECTION,
DATABASE_COLLATION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test'
  ORDER BY ROUTINE_NAME;
ROUTINE_NAME	CHARACTER_SET_NAME	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
downgrade_alter_proc	NULL	latin1	latin1_swedish_ci	latin1_swedish_ci
my_test_func	latin1	latin1	latin1_swedish_ci	latin1_swedish_ci
# Check for General tablespace
SHOW CREATE TABLE tablespace.t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL,
  `b` varchar(50) DEFAULT NULL
) /*!50100 TABLESPACE `tbsp1` */ ENGINE=InnoDB DEFAULT CHARSET=latin1
SHOW CREATE TABLE tablespace.t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `a` int(11) NOT NULL,
  `b` varchar(50) DEFAULT NULL,
  `c` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`a`)
) /*!50100 TABLESPACE `tbsp1` */ ENGINE=InnoDB DEFAULT CHARSET=latin1
SELECT * FROM tablespace.t3;
a	b	c
11	he	ra
SHOW CREATE TABLE tablespace.t4;
Table	Create Table
t4	CREATE TABLE `t4` (
  `a` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `tbsp2` */ ENGINE=InnoDB DEFAULT CHARSET=latin1
#
# Bug#26431355 : ALTER TABLE COMMENT FAILS WITH FAILED TO DROP TABLE SDI AFTER UPGRADE
#
# Check for table with blackhole engine
SHOW CREATE TABLE test.t_blackhole;
Table	Create Table
t_blackhole	CREATE TABLE `t_blackhole` (
  `a` int(11) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
# Check for table with special character in name
SHOW CREATE TABLE `test`.`t_sc~!@#$%^&*(`;
Table	Create Table
t_sc~!@#$%^&*(	CREATE TABLE `t_sc~!@#$%^&*(` (
  `a` int(11) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
# Check for JSON types
SHOW CREATE TABLE test.t_json;
Table	Create Table
t_json	CREATE TABLE `t_json` (
  `jdoc` json DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
# Check for geometry data types
SHOW CREATE TABLE test.geom;
Table	Create Table
geom	CREATE TABLE `geom` (
  `g` geometry DEFAULT NULL,
  `pt` point NOT NULL,
  `ml` multipoint DEFAULT NULL,
  `multi` multilinestring NOT NULL,
  SPATIAL KEY `pt` (`pt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
# Check for Triggers. Operations on t1 inserts data in t2.
INSERT INTO triggers.t1 VALUES(1);
UPDATE triggers.t1 SET a=2 WHERE a=1;
SELECT * FROM triggers.t2;
b
Before Insert Trigger executed for t1
After Insert 1st Trigger executed for t1
After Insert Trigger executed for t1
After Insert 3rd Trigger executed for t1
Before Update Trigger Number 3 executed for t1
Before Update Trigger executed for t1
After Update Trigger for t1 : Query 1 :
After Update Trigger for t1 : Query 3 :
After Update Trigger for t1 : Query 3 :
SELECT TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_MANIPULATION, ACTION_TIMING,
ACTION_ORDER FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='triggers';
TRIGGER_SCHEMA	TRIGGER_NAME	EVENT_MANIPULATION	ACTION_TIMING	ACTION_ORDER
triggers	trg_t1_before_insert	INSERT	BEFORE	1
triggers	t1_bi	INSERT	BEFORE	2
triggers	trg_t1_after_insert_1	INSERT	AFTER	1
triggers	trg_t1_after_insert	INSERT	AFTER	2
triggers	trg_t1_after_insert_3	INSERT	AFTER	3
triggers	trg_t1_before_update_3	UPDATE	BEFORE	1
triggers	trg_t1_before_update	UPDATE	BEFORE	2
triggers	trg_t1_after_update	UPDATE	AFTER	1
triggers	trg1	DELETE	AFTER	1
SHOW CREATE TRIGGER triggers.trg_t1_after_insert_3;
Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation	Created
trg_t1_after_insert_3	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER `trg_t1_after_insert_3` AFTER INSERT ON `t1` FOR EACH ROW insert into t2 values('After Insert 3rd Trigger executed for t1')	utf8	utf8_general_ci	latin1_swedish_ci	#
SHOW CREATE TRIGGER triggers.trg1;
Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation	Created
trg1	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER `trg1` AFTER DELETE ON `t1` FOR EACH ROW begin set@a=1; end	utf8	utf8_general_ci	latin1_swedish_ci	#
SHOW CREATE TRIGGER triggers.t1_bi;
Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation	Created
t1_bi	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER `t1_bi` BEFORE INSERT ON `t1` FOR EACH ROW SET @a:=_koi8r'ÂÌÁÂÌÁÂÌÁ'	utf8	utf8_general_ci	latin1_swedish_ci	#
SHOW CREATE TRIGGER test.tr1_bi;
Trigger	sql_mode	SQL Original Statement	character_set_client	collation_connection	Database Collation	Created
tr1_bi	NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` TRIGGER `tr1_bi` BEFORE INSERT ON `vt2` FOR EACH ROW SET @a:=1	latin1	latin1_swedish_ci	latin1_swedish_ci	#
# I_S query to check Foreign_keys
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE
CONSTRAINT_SCHEMA='foreign_keys' ORDER BY CONSTRAINT_SCHEMA,CONSTRAINT_NAME;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_CATALOG	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	TABLE_NAME	REFERENCED_TABLE_NAME
def	foreign_keys	t3_ibfk_1	def	foreign_keys	PRIMARY	NONE	RESTRICT	RESTRICT	t3	t1
def	foreign_keys	t4_ibfk_1	def	foreign_keys	PRIMARY	NONE	RESTRICT	CASCADE	t4	t1
def	foreign_keys	t5_ibfk_1	def	foreign_keys	PRIMARY	NONE	RESTRICT	SET NULL	t5	t1
def	foreign_keys	t6_ibfk_1	def	foreign_keys	PRIMARY	NONE	CASCADE	CASCADE	t6	t1
def	foreign_keys	t7_ibfk_1	def	foreign_keys	PRIMARY	NONE	RESTRICT	RESTRICT	t7	t1
def	foreign_keys	t8_ibfk_1	def	foreign_keys	PRIMARY	NONE	RESTRICT	CASCADE	t8	t2
def	foreign_keys	ta_ibfk_1	def	foreign_keys	NULL	NONE	RESTRICT	RESTRICT	ta	t9
def	foreign_keys	tb_ibfk_1	def	foreign_keys	b	NONE	RESTRICT	RESTRICT	tb	t9
def	foreign_keys	td_ibfk_1	def	foreign_keys	NULL	NONE	RESTRICT	RESTRICT	td	tc
def	foreign_keys	te_ibfk_1	def	foreign_keys	NULL	NONE	RESTRICT	RESTRICT	te	te
def	foreign_keys	tf_ibfk_1	def	foreign_keys	b	NONE	RESTRICT	RESTRICT	tf	tf
# Check that views with column names are correct
SHOW CREATE VIEW view_with_column_names.v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_with_column_names`.`v1` AS select `view_with_column_names`.`t1`.`x` AS `a`,`view_with_column_names`.`t1`.`y` AS `b` from `view_with_column_names`.`t1` where (`view_with_column_names`.`t1`.`y` = 5)	utf8	utf8_general_ci
SELECT * FROM view_with_column_names.v1;
a	b
3	5
SHOW CREATE VIEW view_with_column_names.v2;
View	Create View	character_set_client	collation_connection
v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_with_column_names`.`v2` AS select `view_with_column_names`.`t1`.`x` AS `e`,`view_with_column_names`.`t1`.`y` AS `f` from `view_with_column_names`.`t1` where (`view_with_column_names`.`t1`.`y` = 5)	utf8	utf8_general_ci
SELECT * FROM view_with_column_names.v2;
e	f
3	5
SHOW CREATE VIEW view_with_column_names.v3;
View	Create View	character_set_client	collation_connection
v3	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_with_column_names`.`v3` AS select `view_with_column_names`.`t1`.`x` AS `x`,`view_with_column_names`.`t1`.`y` AS `y` from `view_with_column_names`.`t1` where (`view_with_column_names`.`t1`.`y` = 5)	utf8	utf8_general_ci
SELECT * FROM view_with_column_names.v3;
x	y
3	5
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_SCHEMA='view_with_column_names';
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	COLUMN_DEFAULT	IS_NULLABLE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	COLUMN_TYPE	COLUMN_KEY	EXTRA	PRIVILEGES	COLUMN_COMMENT	GENERATION_EXPRESSION	SRS_ID
def	view_with_column_names	t1	x	1	NULL	YES	int	NULL	NULL	10	0	NULL	NULL	NULL	int(11)			select,insert,update,references			NULL
def	view_with_column_names	t1	y	2	NULL	YES	int	NULL	NULL	10	0	NULL	NULL	NULL	int(11)			select,insert,update,references			NULL
def	view_with_column_names	v1	a	1	NULL	YES	int	NULL	NULL	10	0	NULL	NULL	NULL	int(11)			select,insert,update,references			NULL
def	view_with_column_names	v1	b	2	NULL	YES	int	NULL	NULL	10	0	NULL	NULL	NULL	int(11)			select,insert,update,references			NULL
def	view_with_column_names	v2	e	1	NULL	YES	int	NULL	NULL	10	0	NULL	NULL	NULL	int(11)			select,insert,update,references			NULL
def	view_with_column_names	v2	f	2	NULL	YES	int	NULL	NULL	10	0	NULL	NULL	NULL	int(11)			select,insert,update,references			NULL
def	view_with_column_names	v3	x	1	NULL	YES	int	NULL	NULL	10	0	NULL	NULL	NULL	int(11)			select,insert,update,references			NULL
def	view_with_column_names	v3	y	2	NULL	YES	int	NULL	NULL	10	0	NULL	NULL	NULL	int(11)			select,insert,update,references			NULL
# Bug #28480149 "UPGRADE FAIL: FAILED TO ADD THE FOREIGN KEY CONSTRAINT.
# MISSING INDEX FOR CONSTR". Coverage for upgrade scenario. Main part
# of coverage resides in foreign_key test.
SHOW CREATE TABLE foreign_keys.t9;
Table	Create Table
t9	CREATE TABLE `t9` (
  `pk` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  UNIQUE KEY `b` (`b`),
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SHOW CREATE TABLE foreign_keys.ta;
Table	Create Table
ta	CREATE TABLE `ta` (
  `fk1` int(11) DEFAULT NULL,
  `fk2` int(11) DEFAULT NULL,
  KEY `fk1` (`fk1`,`fk2`),
  CONSTRAINT `ta_ibfk_1` FOREIGN KEY (`fk1`, `fk2`) REFERENCES `t9` (`a`, `pk`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SHOW CREATE TABLE foreign_keys.tb;
Table	Create Table
tb	CREATE TABLE `tb` (
  `fk1` int(11) DEFAULT NULL,
  `fk2` int(11) DEFAULT NULL,
  KEY `fk1` (`fk1`,`fk2`),
  CONSTRAINT `tb_ibfk_1` FOREIGN KEY (`fk1`, `fk2`) REFERENCES `t9` (`b`, `pk`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SHOW CREATE TABLE foreign_keys.tc;
Table	Create Table
tc	CREATE TABLE `tc` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  UNIQUE KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SHOW CREATE TABLE foreign_keys.td;
Table	Create Table
td	CREATE TABLE `td` (
  `fk1` int(11) DEFAULT NULL,
  `fk2` int(11) DEFAULT NULL,
  KEY `fk1` (`fk1`,`fk2`),
  CONSTRAINT `td_ibfk_1` FOREIGN KEY (`fk1`, `fk2`) REFERENCES `tc` (`b`, `a`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SHOW CREATE TABLE foreign_keys.te;
Table	Create Table
te	CREATE TABLE `te` (
  `pk` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `fk1` int(11) DEFAULT NULL,
  `fk2` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `a` (`a`),
  KEY `fk1` (`fk1`,`fk2`),
  CONSTRAINT `te_ibfk_1` FOREIGN KEY (`fk1`, `fk2`) REFERENCES `te` (`a`, `pk`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SHOW CREATE TABLE foreign_keys.tf;
Table	Create Table
tf	CREATE TABLE `tf` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `fk1` int(11) DEFAULT NULL,
  `fk2` int(11) DEFAULT NULL,
  UNIQUE KEY `a` (`a`),
  UNIQUE KEY `b` (`b`),
  KEY `fk1` (`fk1`,`fk2`),
  CONSTRAINT `tf_ibfk_1` FOREIGN KEY (`fk1`, `fk2`) REFERENCES `tf` (`b`, `a`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1
# Restart on the upgraded data dir
# restart: --loose-skip-log-bin --skip-log-slave-updates --datadir=MYSQLD_DATADIR1
# Execute FTS queries that modify FTS pages
select count(*) from sakila.film_text;
count(*)
1000
select * from sakila.film_text where film_id = 984;
film_id	title	description
984	WONDERFUL DROP	A Boring Panorama of a Woman And a Madman who must Overcome a Butler in A U-Boat
select * from sakila.film_text where match(title,description) against("SCISSORHANDS");
film_id	title	description
770	SCISSORHANDS SLUMS	A Awe-Inspiring Drama of a Girl And a Technical Writer who must Meet a Feminist in The Canadian Rockies
delete from sakila.film_text where film_id = 984;
SET GLOBAL innodb_optimize_fulltext_only=ON;
optimize table sakila.film_text;
Table	Op	Msg_type	Msg_text
sakila.film_text	optimize	status	OK
# Stop the server
# Check if .ibds have SDI after upgrade
# Check SDI from vt2.ibd
# Check SDI from t_json.ibd
# Check SDI from t_gen_stored.ibd
# Check SDI from t_dynamic.ibd
# Check SDI from t_compressed3.ibd
# Check SDI from t_compressed2.ibd
# Check SDI from t_compressed.ibd
# Check SDI from t_blob_myisam.ibd
# Check SDI from t_blob.ibd
# Check SDI from opening_lines.ibd
# Check SDI from jemp.ibd
# Check SDI from geom.ibd
# Check SDI from store.ibd
# Check SDI from staff.ibd
# Check SDI from rental.ibd
# Check SDI from payment.ibd
# Check SDI from language.ibd
# Check SDI from inventory.ibd
# Check SDI from film_text.ibd
# Check SDI from film_category.ibd
# Check SDI from film_actor.ibd
# Check SDI from film.ibd
# Check SDI from customer.ibd
# Check SDI from country.ibd
# Check SDI from city.ibd
# Check SDI from category.ibd
# Check SDI from address.ibd
# Check SDI from actor.ibd
# Check SDI from mysql.ibd
# Check SDI from ibdata1
# Check SDI from test_tablespace_3.ibd
# Check SDI from test_tablespace_2.ibd
# Remove json files
# Remove copied files
#
# Bug#26944731 : UPGRADE TO 8.0 FAILS: DUPLICATE SET VALUES IN TABLE FROM A PERMISSIVE SQL_MODE..
# Bug#26948678 : MYSQLD: INVALID DEFAULT VALUE FOR 'CACHED_TIME'
#
# Unzip the zip file.
SHOW CREATE TABLE test.t_set;
Table	Create Table
t_set	CREATE TABLE `t_set` (
  `a` set('','') DEFAULT NULL
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
# Stop the server
# Remove copied files
# Restart the server with default options.
# restart
#
# Bug #27512609 5.7->8.0 UPGRADE CRASH WITH DEFAULT-TIME-ZONE SET
#
#
# Zip file contains data directory of a clean 5.7 server.
# made by: ./mtr --mem --mysqld=--default_time_zone="+01:00" main.1st
#
# Copy zip files from suite location to working location.
# Check that the file exists in the working folder.
# Unzip the zip file.
# Set different paths for --datadir
# Check that the file exits after unzip
# Stop DB server which was created by MTR default
# Start the DB server
# Stop the server
# Remove copied files
# Restart the server with default options.
# restart
#
# Bug #28884503 UPGRADE FROM 5.7.23+ TO 8.0.11+ FAILS WHEN INNODB_PAGE_SIZE=4K
#
# Zip file contains data directory of a clean 5.7 server.
#
# Copy zip files from suite location to working location.
# Check that the file exists in the working folder.
# Unzip the zip file.
# Set different paths for --datadir
# Restart DB server which was created by MTR default
# restart: --datadir=MYSQLD_DATADIR2 --innodb_page_size=4K --log-error=MYSQLD_LOG
# Remove copied files
# Restart the server with default options.
# restart
#
# Bug#29823053: VERY SMALL TABLE_OPEN_CACHE CAUSES SEGMENTATION FAULT WHEN UPGRADING TO 8.0.16
#
# Copy zip files from suite location to working location.
# Check that the file exists in the working folder.
# Unzip the zip file.
# Set different paths for --datadir
# Stop DB server which was created by MTR default
# Start the DB server to do upgrade. Without the bugfix, the server will fail.
# Stop the server
# Remove copied files
# Restart the server with default options.
# restart
# end of test