B4BY.588
Home
Terminal
Upload
information
Create File
Create Folder
About
Tools
:
/
usr
/
share
/
mysql-test
/
r
/
Filename :
wl6219-upgrade.result
back
Copy
# # Test with a saved MyISAM table from 4.1 # # Everything _looks_ fine SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `y2` year(2) DEFAULT NULL, `i` int(11) DEFAULT NULL, `i_chk` int(11) DEFAULT NULL, `y4` year(4) DEFAULT NULL, `y4_chk` year(4) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 # Run CHECK TABLE, it should indicate table need a REPAIR TABLE CHECK TABLE t1 FOR UPGRADE; Table Op Msg_type Msg_text test.t1 check error Table upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it! # Run REPAIR TABLE to alter the table and repair the YEAR(2) fields REPAIR TABLE t1; Table Op Msg_type Msg_text test.t1 repair Warning YEAR(2) column type is deprecated. Creating YEAR(4) column instead. test.t1 repair status OK # Now check it's back to normal SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `y2` year(4) DEFAULT NULL, `i` int(11) DEFAULT NULL, `i_chk` int(11) DEFAULT NULL, `y4` year(4) DEFAULT NULL, `y4_chk` year(4) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t1; y2 i i_chk y4 y4_chk 1901 1901 1 1901 1901 2001 1 1 2001 2001 1970 1970 70 1970 1970 1970 70 70 1970 1970 2000 2000 0 2000 2000 0000 0 0 0000 0000 2069 2069 69 2069 2069 2069 69 69 2069 2069 2155 2155 55 2155 2155 2055 55 55 2055 2055 DROP TABLE t1; # ALTER TABLE ... FORCE should convert YEAR(2) to YEAR(4) ALTER TABLE t1 FORCE; Warnings: Warning 1818 YEAR(2) column type is deprecated. Creating YEAR(4) column instead. SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `y2` year(4) DEFAULT NULL, `i` int(11) DEFAULT NULL, `i_chk` int(11) DEFAULT NULL, `y4` year(4) DEFAULT NULL, `y4_chk` year(4) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; # # Test with a saved MyISAM table from 5.5 # # Everything _looks_ fine SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `y2` year(2) DEFAULT NULL, `i` int(11) DEFAULT NULL, `i_chk` int(11) DEFAULT NULL, `y4` year(4) DEFAULT NULL, `y4_chk` year(4) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 # Run CHECK TABLE, it should indicate table need a REPAIR TABLE CHECK TABLE t1 FOR UPGRADE; Table Op Msg_type Msg_text test.t1 check error Table upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it! # Run REPAIR TABLE to alter the table and repair the YEAR(2) fields REPAIR TABLE t1; Table Op Msg_type Msg_text test.t1 repair Warning YEAR(2) column type is deprecated. Creating YEAR(4) column instead. test.t1 repair status OK # Now check it's back to normal SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `y2` year(4) DEFAULT NULL, `i` int(11) DEFAULT NULL, `i_chk` int(11) DEFAULT NULL, `y4` year(4) DEFAULT NULL, `y4_chk` year(4) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t1; y2 i i_chk y4 y4_chk 1901 1901 1 1901 1901 2001 1 1 2001 2001 1970 1970 70 1970 1970 1970 70 70 1970 1970 2000 2000 0 2000 2000 0000 0 0 0000 0000 2069 2069 69 2069 2069 2069 69 69 2069 2069 2155 2155 55 2155 2155 2055 55 55 2055 2055 DROP TABLE t1; # ALTER TABLE ... FORCE should convert YEAR(2) to YEAR(4) ALTER TABLE t1 FORCE; Warnings: Warning 1818 YEAR(2) column type is deprecated. Creating YEAR(4) column instead. SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `y2` year(4) DEFAULT NULL, `i` int(11) DEFAULT NULL, `i_chk` int(11) DEFAULT NULL, `y4` year(4) DEFAULT NULL, `y4_chk` year(4) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; # # Test with InnoDB table (with fake .frm file) # # Create InnoDB table with YEAR(4) columns ALTER TABLE t1 ENGINE=InnoDB; Warnings: Warning 1818 YEAR(2) column type is deprecated. Creating YEAR(4) column instead. SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `y2` year(4) DEFAULT NULL, `i` int(11) DEFAULT NULL, `i_chk` int(11) DEFAULT NULL, `y4` year(4) DEFAULT NULL, `y4_chk` year(4) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 FLUSH TABLE t1; # Replace YEAR(4) column metadata with YEAR(2) metadata SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `y2` year(2) DEFAULT NULL, `i` int(11) DEFAULT NULL, `i_chk` int(11) DEFAULT NULL, `y4` year(4) DEFAULT NULL, `y4_chk` year(4) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 # Run CHECK TABLE, it should indicate table need a REPAIR TABLE CHECK TABLE t1 FOR UPGRADE; Table Op Msg_type Msg_text test.t1 check error Table rebuild required. Please do "ALTER TABLE `t1` FORCE" or dump/reload to fix it! # Run ALTER TABLE ... FORCE to alter the table and repair the YEAR(2) fields ALTER TABLE t1 FORCE; Warnings: Warning 1818 YEAR(2) column type is deprecated. Creating YEAR(4) column instead. SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `y2` year(4) DEFAULT NULL, `i` int(11) DEFAULT NULL, `i_chk` int(11) DEFAULT NULL, `y4` year(4) DEFAULT NULL, `y4_chk` year(4) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DROP TABLE t1; # # Test mysql_upgrade tool # Warning: Using a password on the command line interface can be insecure. Warning: Using a password on the command line interface can be insecure. mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.general_log OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK mysql.slave_master_info OK mysql.slave_relay_log_info OK mysql.slave_worker_info OK mysql.slow_log OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK Warning: Using a password on the command line interface can be insecure. Warning: Using a password on the command line interface can be insecure. Warning: Using a password on the command line interface can be insecure. mtr.global_suppressions OK mtr.test_suppressions OK test.t1 error : Table upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it! Repairing tables test.t1 Warning : YEAR(2) column type is deprecated. Creating YEAR(4) column instead. status : OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `y2` year(4) DEFAULT NULL, `i` int(11) DEFAULT NULL, `i_chk` int(11) DEFAULT NULL, `y4` year(4) DEFAULT NULL, `y4_chk` year(4) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; #