^^&&^^

Edit File: wl6219-upgrade.result

#
# 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;
#