-- source include/mysql_upgrade_preparation.inc call mtr.add_suppression("Table rebuild required"); let $MYSQLD_DATADIR= `select @@datadir`; --enable_prepare_warnings --echo # --echo # Opening a Maria-10.2.26 table with a stored VARCHAR column --echo # --echo # Copying maria100226_char_to_vchar_stored.* to MYSQLD_DATADIR --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_stored.frm $MYSQLD_DATADIR/test/t1.frm --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_stored.MYD $MYSQLD_DATADIR/test/t1.MYD --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_stored.MYI $MYSQLD_DATADIR/test/t1.MYI SHOW CREATE TABLE t1; SHOW CREATE TABLE t1; FLUSH TABLES; SHOW CREATE TABLE t1; FLUSH TABLES; SELECT * FROM t1; SELECT * FROM t1; FLUSH TABLES; SELECT * FROM t1; --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t2 LIKE t1; FLUSH TABLES; --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t2 LIKE t1; SHOW CREATE TABLE t1; --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED ALTER TABLE t1 ADD b INT DEFAULT a; SHOW CREATE TABLE t1; SELECT * FROM t1; FLUSH TABLES; --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED ALTER TABLE t1 ADD c INT DEFAULT a; SELECT * FROM t1; DROP TABLE t1; --echo # --echo # Fixing a Maria-10.2.26 table with a stored VARCHAR column --echo # --echo # Fixing by dropping the generated stored column --echo # Copying maria100226_char_to_vchar_stored.* to MYSQLD_DATADIR --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_stored.frm $MYSQLD_DATADIR/test/t1.frm --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_stored.MYD $MYSQLD_DATADIR/test/t1.MYD --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_stored.MYI $MYSQLD_DATADIR/test/t1.MYI ALTER TABLE t1 DROP v; SELECT * FROM t1; DROP TABLE t1; --echo # Fixing by altering the generation expression of the stored column --echo # Copying maria100226_char_to_vchar_stored.* to MYSQLD_DATADIR --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_stored.frm $MYSQLD_DATADIR/test/t1.frm --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_stored.MYD $MYSQLD_DATADIR/test/t1.MYD --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_stored.MYI $MYSQLD_DATADIR/test/t1.MYI SHOW CREATE TABLE t1; ALTER TABLE t1 MODIFY v VARCHAR(5) AS (RTRIM(a)) PERSISTENT; SHOW CREATE TABLE t1; DROP TABLE t1; --echo # --echo # Opening a Maria-10.2.26 table with a virtual VARCHAR column --echo # --echo # Copying maria100226_char_to_vchar_virtual.* to MYSQLD_DATADIR --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.frm $MYSQLD_DATADIR/test/t1.frm --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.MYD $MYSQLD_DATADIR/test/t1.MYD --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.MYI $MYSQLD_DATADIR/test/t1.MYI SHOW CREATE TABLE t1; SHOW CREATE TABLE t1; FLUSH TABLES; SHOW CREATE TABLE t1; FLUSH TABLES; SELECT * FROM t1; SELECT * FROM t1; FLUSH TABLES; SELECT * FROM t1; --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t2 LIKE t1; FLUSH TABLES; --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t2 LIKE t1; SHOW CREATE TABLE t1; --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED ALTER TABLE t1 ADD b INT DEFAULT a; FLUSH TABLES; --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED ALTER TABLE t1 ADD c INT DEFAULT a; SELECT * FROM t1; DROP TABLE t1; --echo # --echo # Fixing a Maria-10.2.26 table with a virtual VARCHAR column --echo # --echo # Fixing by dropping the virtual column --echo # Copying maria100226_char_to_vchar_virtual.* to MYSQLD_DATADIR --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.frm $MYSQLD_DATADIR/test/t1.frm --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.MYD $MYSQLD_DATADIR/test/t1.MYD --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.MYI $MYSQLD_DATADIR/test/t1.MYI SHOW CREATE TABLE t1; ALTER TABLE t1 DROP v; SELECT * FROM t1; DROP TABLE t1; --echo # Fixing by dropping a key on a virtual column, using ALTER TABLE --echo # Copying maria100226_char_to_vchar_virtual.* to MYSQLD_DATADIR --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.frm $MYSQLD_DATADIR/test/t1.frm --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.MYD $MYSQLD_DATADIR/test/t1.MYD --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.MYI $MYSQLD_DATADIR/test/t1.MYI SHOW CREATE TABLE t1; ALTER TABLE t1 DROP KEY v; SHOW CREATE TABLE t1; SELECT * FROM t1; DROP TABLE t1; --echo # Fixing by dropping a key on a virtual column, using DROP INDEX --echo # Copying maria100226_char_to_vchar_virtual.* to MYSQLD_DATADIR --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.frm $MYSQLD_DATADIR/test/t1.frm --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.MYD $MYSQLD_DATADIR/test/t1.MYD --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.MYI $MYSQLD_DATADIR/test/t1.MYI DROP INDEX v ON t1; SHOW CREATE TABLE t1; SELECT * FROM t1; DROP TABLE t1; --echo # Fixing by altering the generation expression of a virtual column --echo # Copying maria100226_char_to_vchar_virtual.* to MYSQLD_DATADIR --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.frm $MYSQLD_DATADIR/test/t1.frm --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.MYD $MYSQLD_DATADIR/test/t1.MYD --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.MYI $MYSQLD_DATADIR/test/t1.MYI SHOW CREATE TABLE t1; ALTER TABLE t1 MODIFY v VARCHAR(5) AS(RTRIM(a)) VIRTUAL; SHOW CREATE TABLE t1; SELECT * FROM t1; CREATE TABLE t2 LIKE t1; SHOW CREATE TABLE t2; DROP TABLE t2; DROP TABLE t1; --echo # --echo # Upgrading a Maria-10.2.26 table with a stored column --echo # --echo # Copying maria100226_char_to_varchar.* to MYSQLD_DATADIR --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_stored.frm $MYSQLD_DATADIR/test/t1.frm --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_stored.MYD $MYSQLD_DATADIR/test/t1.MYD --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_stored.MYI $MYSQLD_DATADIR/test/t1.MYI CHECK TABLE t1 FOR UPGRADE; FLUSH TABLES; CHECK TABLE t1 FOR UPGRADE; DROP TABLE t1; --echo # --echo # Upgrading a Maria-10.2.26 table with a virtual column --echo # --echo # Copying maria100226_char_to_varchar.* to MYSQLD_DATADIR --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.frm $MYSQLD_DATADIR/test/t1.frm --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.MYD $MYSQLD_DATADIR/test/t1.MYD --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.MYI $MYSQLD_DATADIR/test/t1.MYI CHECK TABLE t1 FOR UPGRADE; FLUSH TABLES; CHECK TABLE t1 FOR UPGRADE; DROP TABLE t1;