# # This test upgrades a 5.7 database with tables having "FTS" # in their names to 8.0 # # Stop the running server ############################################################## # Copy and unzip the datadir created with lctn=0 ############################################################## # Restart the server with the unzipped datadir # restart: --datadir=DATADIR --innodb_page_size=16k # Check the contents of INFORMATION_SCHEMA SHOW TABLES; Tables_in_test FTS_0000000000000001 FTS_table tFTS1 t_FTS_1 SELECT lower(name) FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE name LIKE '%test%' ORDER BY name; lower(name) test/fts_0000000000000001 test/fts_0000000000000427_000000000000002d_index_1 test/fts_0000000000000427_000000000000002d_index_2 test/fts_0000000000000427_000000000000002d_index_3 test/fts_0000000000000427_000000000000002d_index_4 test/fts_0000000000000427_000000000000002d_index_5 test/fts_0000000000000427_000000000000002d_index_6 test/fts_0000000000000427_being_deleted test/fts_0000000000000427_being_deleted_cache test/fts_0000000000000427_config test/fts_0000000000000427_deleted test/fts_0000000000000427_deleted_cache test/fts_0000000000000434_000000000000003c_index_1 test/fts_0000000000000434_000000000000003c_index_2 test/fts_0000000000000434_000000000000003c_index_3 test/fts_0000000000000434_000000000000003c_index_4 test/fts_0000000000000434_000000000000003c_index_5 test/fts_0000000000000434_000000000000003c_index_6 test/fts_0000000000000434_being_deleted test/fts_0000000000000434_being_deleted_cache test/fts_0000000000000434_config test/fts_0000000000000434_deleted test/fts_0000000000000434_deleted_cache test/fts_table test/tfts1 test/t_fts_1 SELECT lower(name) FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE name LIKE '%test%' ORDER BY name; lower(name) test/fts_0000000000000001 test/fts_0000000000000427_000000000000002d_index_1 test/fts_0000000000000427_000000000000002d_index_2 test/fts_0000000000000427_000000000000002d_index_3 test/fts_0000000000000427_000000000000002d_index_4 test/fts_0000000000000427_000000000000002d_index_5 test/fts_0000000000000427_000000000000002d_index_6 test/fts_0000000000000427_being_deleted test/fts_0000000000000427_being_deleted_cache test/fts_0000000000000427_config test/fts_0000000000000427_deleted test/fts_0000000000000427_deleted_cache test/fts_0000000000000434_000000000000003c_index_1 test/fts_0000000000000434_000000000000003c_index_2 test/fts_0000000000000434_000000000000003c_index_3 test/fts_0000000000000434_000000000000003c_index_4 test/fts_0000000000000434_000000000000003c_index_5 test/fts_0000000000000434_000000000000003c_index_6 test/fts_0000000000000434_being_deleted test/fts_0000000000000434_being_deleted_cache test/fts_0000000000000434_config test/fts_0000000000000434_deleted test/fts_0000000000000434_deleted_cache test/fts_table test/tfts1 test/t_fts_1 # Cleanup # Shutdown server # Remove copied files ############################################################## # Copy and unzip the datadir created with lctn=1 ############################################################## # Restart the server with the unzipped datadir # restart: --datadir=DATADIR --innodb_page_size=16k # Check the contents of INFORMATION_SCHEMA SHOW TABLES; Tables_in_test fts_0000000000000001 fts_table t_fts_1 tfts1 SELECT lower(name) FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE name LIKE '%test%' ORDER BY name; lower(name) test/fts_0000000000000001 test/fts_0000000000000427_000000000000002d_index_1 test/fts_0000000000000427_000000000000002d_index_2 test/fts_0000000000000427_000000000000002d_index_3 test/fts_0000000000000427_000000000000002d_index_4 test/fts_0000000000000427_000000000000002d_index_5 test/fts_0000000000000427_000000000000002d_index_6 test/fts_0000000000000427_being_deleted test/fts_0000000000000427_being_deleted_cache test/fts_0000000000000427_config test/fts_0000000000000427_deleted test/fts_0000000000000427_deleted_cache test/fts_0000000000000434_000000000000003c_index_1 test/fts_0000000000000434_000000000000003c_index_2 test/fts_0000000000000434_000000000000003c_index_3 test/fts_0000000000000434_000000000000003c_index_4 test/fts_0000000000000434_000000000000003c_index_5 test/fts_0000000000000434_000000000000003c_index_6 test/fts_0000000000000434_being_deleted test/fts_0000000000000434_being_deleted_cache test/fts_0000000000000434_config test/fts_0000000000000434_deleted test/fts_0000000000000434_deleted_cache test/fts_table test/tfts1 test/t_fts_1 SELECT lower(name) FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE name LIKE '%test%' ORDER BY name; lower(name) test/fts_0000000000000001 test/fts_0000000000000427_000000000000002d_index_1 test/fts_0000000000000427_000000000000002d_index_2 test/fts_0000000000000427_000000000000002d_index_3 test/fts_0000000000000427_000000000000002d_index_4 test/fts_0000000000000427_000000000000002d_index_5 test/fts_0000000000000427_000000000000002d_index_6 test/fts_0000000000000427_being_deleted test/fts_0000000000000427_being_deleted_cache test/fts_0000000000000427_config test/fts_0000000000000427_deleted test/fts_0000000000000427_deleted_cache test/fts_0000000000000434_000000000000003c_index_1 test/fts_0000000000000434_000000000000003c_index_2 test/fts_0000000000000434_000000000000003c_index_3 test/fts_0000000000000434_000000000000003c_index_4 test/fts_0000000000000434_000000000000003c_index_5 test/fts_0000000000000434_000000000000003c_index_6 test/fts_0000000000000434_being_deleted test/fts_0000000000000434_being_deleted_cache test/fts_0000000000000434_config test/fts_0000000000000434_deleted test/fts_0000000000000434_deleted_cache test/fts_table test/tfts1 test/t_fts_1 # Cleanup # Shutdown server # Remove copied files # Restart the server # restart # # Bug#30357954 UPGRADE TO 8.0 FAILS WHEN FULLTEXT INDEX HAS BEEN DROPPED # # This test upgrades a 5.7 database with table having "FTS_DOC_ID" # column & "FTS_DOC_ID_INDEX" index to 8.0. (And not having any # FULLTEXT index) # # Stop the running server # # Copy and unzip the datadir # # Restart the server with the unzipped datadir # restart: --datadir=DATADIR --innodb_page_size=16k # Check the contents of INFORMATION_SCHEMA SHOW TABLES; Tables_in_test t1 SELECT lower(name) FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE name LIKE '%test%' ORDER BY name; lower(name) test/fts_0000000000000424_being_deleted test/fts_0000000000000424_being_deleted_cache test/fts_0000000000000424_config test/fts_0000000000000424_deleted test/fts_0000000000000424_deleted_cache test/t1 SELECT lower(name) FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE name LIKE '%test%' ORDER BY name; lower(name) test/fts_0000000000000424_being_deleted test/fts_0000000000000424_being_deleted_cache test/fts_0000000000000424_config test/fts_0000000000000424_deleted test/fts_0000000000000424_deleted_cache test/t1 # Check DDL & DML opearations are working SELECT * FROM t1; id val 1 Upgrade mysql database to 8.0 version SET DEBUG='+d,skip_dd_table_access_check'; SELECT b.name table_name, a.name column_name, a.ordinal_position, a.hidden FROM mysql.columns a, mysql.tables b WHERE a.table_id = b.id AND b.name = 't1' ORDER BY a.ordinal_position; table_name column_name ordinal_position hidden t1 id 1 Visible t1 val 2 Visible t1 DB_TRX_ID 3 SE t1 DB_ROLL_PTR 4 SE t1 FTS_DOC_ID 5 SE ALTER TABLE t1 ADD FULLTEXT INDEX val_ft_idx(val); SELECT b.name table_name, a.name column_name, a.ordinal_position, a.hidden FROM mysql.columns a, mysql.tables b WHERE a.table_id = b.id AND b.name = 't1' ORDER BY a.ordinal_position; table_name column_name ordinal_position hidden t1 id 1 Visible t1 val 2 Visible t1 FTS_DOC_ID 3 SE t1 DB_TRX_ID 4 SE t1 DB_ROLL_PTR 5 SE INSERT INTO t1 (val) VALUES ('Upgraded successfully to 8.0 version'); SELECT * FROM t1; id val 1 Upgrade mysql database to 8.0 version 2 Upgraded successfully to 8.0 version SELECT b.name table_name, a.name column_name, a.ordinal_position, a.hidden FROM mysql.columns a, mysql.tables b WHERE a.table_id = b.id AND b.name = 't1' ORDER BY a.ordinal_position; table_name column_name ordinal_position hidden t1 id 1 Visible t1 val 2 Visible t1 FTS_DOC_ID 3 SE t1 DB_TRX_ID 4 SE t1 DB_ROLL_PTR 5 SE ALTER TABLE t1 DROP INDEX val_ft_idx; SELECT b.name table_name, a.name column_name, a.ordinal_position, a.hidden FROM mysql.columns a, mysql.tables b WHERE a.table_id = b.id AND b.name = 't1' ORDER BY a.ordinal_position; table_name column_name ordinal_position hidden t1 id 1 Visible t1 val 2 Visible t1 DB_TRX_ID 3 SE t1 DB_ROLL_PTR 4 SE t1 FTS_DOC_ID 5 SE SET DEBUG='-d,skip_dd_table_access_check'; DELETE FROM t1 WHERE val LIKE 'Upgrade mysql database to 8.0 version'; SELECT * FROM t1; id val 2 Upgraded successfully to 8.0 version DROP TABLE t1; # Cleanup # Shutdown server # Remove copied files # Restart the server # restart # # Bug #31749490 OLD FTS FILES AFTER UPGRADE: ASSERTION FAILURE: # PARS0PARS.CC:736:SYM_NODE->TABLE != NULLPTR # # Stop the running server # # Copy and unzip the datadir # # Restart the server with the unzipped datadir # restart: --datadir=DATADIR --innodb_page_size=16k # Check the contents of INFORMATION_SCHEMA SHOW TABLES; Tables_in_test t1 SELECT lower(name) FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE name LIKE '%test%' ORDER BY name; lower(name) test/fts_0000000000000424_being_deleted test/fts_0000000000000424_being_deleted_cache test/fts_0000000000000424_config test/fts_0000000000000424_deleted test/fts_0000000000000424_deleted_cache test/t1 SELECT lower(name) FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE name LIKE '%test%' ORDER BY name; lower(name) test/fts_0000000000000424_being_deleted test/fts_0000000000000424_being_deleted_cache test/fts_0000000000000424_config test/fts_0000000000000424_deleted test/fts_0000000000000424_deleted_cache test/t1 INSERT INTO t1 (val) VALUES ('Upgraded successfully to 8.0 version'); SELECT * FROM t1; id val 1 Upgrade mysql database to 8.0 version 2 Upgraded successfully to 8.0 version DELETE FROM t1 WHERE val LIKE 'Upgrade mysql database to 8.0 version'; SELECT * FROM t1; id val 2 Upgraded successfully to 8.0 version DROP TABLE t1; # Cleanup # Shutdown server # Remove copied files # Restart the server # restart