| Server IP : 172.67.216.182 / Your IP : 162.158.88.97 Web Server : Apache System : Linux krdc-ubuntu-s-2vcpu-4gb-amd-blr1-01.localdomain 5.15.0-142-generic #152-Ubuntu SMP Mon May 19 10:54:31 UTC 2025 x86_64 User : www ( 1000) PHP Version : 7.4.33 Disable Function : passthru,exec,system,putenv,chroot,chgrp,chown,shell_exec,popen,proc_open,pcntl_exec,ini_alter,ini_restore,dl,openlog,syslog,readlink,symlink,popepassthru,pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,imap_open,apache_setenv MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : OFF | Sudo : ON | Pkexec : ON Directory : /www/server/mysql/mysql-test/r/ |
Upload File : |
# # 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 Warnings: Warning 1194 Found incompatible YEAR(x) field 'y2' in t1; Please do "ALTER TABLE `t1` FORCE" to fix it! # 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 warning Table is marked as crashed test.t1 check error Table upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it! SELECT * FROM t1; ERROR HY000: Table 't1' is marked as crashed and should be repaired INSERT INTO t1 () VALUES (); ERROR HY000: Table 't1' is marked as crashed and should be repaired INSERT INTO t1 (i) VALUES (10); ERROR HY000: Table 't1' is marked as crashed and should be repaired INSERT INTO t1 (y2) VALUES (10); ERROR HY000: Table 't1' is marked as crashed and should be repaired UPDATE t1 SET y2=10; ERROR HY000: Table 't1' is marked as crashed and should be repaired DELETE FROM t1 LIMIT 1; ERROR HY000: Table 't1' is marked as crashed and should be repaired CHECK TABLE t1 FOR UPGRADE; Table Op Msg_type Msg_text test.t1 check warning Table is marked as crashed 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 Found incompatible YEAR(x) field 'y2' in t1; Please do "ALTER TABLE `t1` FORCE" to fix it! test.t1 repair Warning Found incompatible YEAR(x) field 'y2' in t1; Please do "ALTER TABLE `t1` FORCE" to fix it! 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 INSERT INTO t1 () VALUES (); UPDATE t1 SET y2=10; DELETE FROM t1 LIMIT 1; DROP TABLE t1; # ALTER TABLE ... FORCE should convert YEAR(2) to YEAR(4) ALTER TABLE t1 FORCE; Warnings: Warning 1194 Found incompatible YEAR(x) field 'y2' in t1; Please do "ALTER TABLE `t1` FORCE" to fix it! 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 Warnings: Warning 1194 Found incompatible YEAR(x) field 'y2' in t1; Please do "ALTER TABLE `t1` FORCE" to fix it! # 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 warning Table is marked as crashed test.t1 check error Table upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it! SELECT * FROM t1; ERROR HY000: Table 't1' is marked as crashed and should be repaired INSERT INTO t1 () VALUES (); ERROR HY000: Table 't1' is marked as crashed and should be repaired INSERT INTO t1 (i) VALUES (10); ERROR HY000: Table 't1' is marked as crashed and should be repaired INSERT INTO t1 (y2) VALUES (10); ERROR HY000: Table 't1' is marked as crashed and should be repaired UPDATE t1 SET y2=10; ERROR HY000: Table 't1' is marked as crashed and should be repaired DELETE FROM t1 LIMIT 1; ERROR HY000: Table 't1' is marked as crashed and should be repaired # 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 Found incompatible YEAR(x) field 'y2' in t1; Please do "ALTER TABLE `t1` FORCE" to fix it! 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) SELECT * FROM t1; ERROR HY000: Table 't1' is marked as crashed and should be repaired INSERT INTO t1 () VALUES (); ERROR HY000: Table 't1' is marked as crashed and should be repaired INSERT INTO t1 (i) VALUES (10); ERROR HY000: Table 't1' is marked as crashed and should be repaired INSERT INTO t1 (y2) VALUES (10); ERROR HY000: Table 't1' is marked as crashed and should be repaired UPDATE t1 SET y2=10; ERROR HY000: Table 't1' is marked as crashed and should be repaired DELETE FROM t1 LIMIT 1; ERROR HY000: Table 't1' is marked as crashed and should be repaired ALTER TABLE t1 FORCE; 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 SELECT * FROM t1; ERROR HY000: Table 't1' is marked as crashed and should be repaired INSERT INTO t1 () VALUES (); ERROR HY000: Table 't1' is marked as crashed and should be repaired INSERT INTO t1 (i) VALUES (10); ERROR HY000: Table 't1' is marked as crashed and should be repaired INSERT INTO t1 (y2) VALUES (10); ERROR HY000: Table 't1' is marked as crashed and should be repaired UPDATE t1 SET y2=10; ERROR HY000: Table 't1' is marked as crashed and should be repaired DELETE FROM t1 LIMIT 1; ERROR HY000: Table 't1' is marked as crashed and should be repaired SHOW WARNINGS; Level Code Message Error 1194 Table 't1' is marked as crashed and should be repaired ALTER TABLE t1 ENGINE=InnoDB; 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 Warnings: Warning 1194 Found incompatible YEAR(x) field 'y2' in t1; Please do "ALTER TABLE `t1` FORCE" to fix it! # 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 warning Table is marked as crashed 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 1194 Found incompatible YEAR(x) field 'y2' in t1; Please do "ALTER TABLE `t1` FORCE" to fix it! 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 # mysql.columns_priv OK mysql.db OK mysql.engine_cost OK mysql.event OK mysql.func OK mysql.general_log OK mysql.gtid_executed 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.server_cost 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 mtr.global_suppressions OK mtr.test_suppressions OK sys.sys_config OK test.t1 warning : Table is marked as crashed Warning : Found incompatible YEAR(x) field 'y2' in t1; Please do "ALTER TABLE `t1` FORCE" to fix it! error : Table upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it! Repairing tables test.t1 Warning : Found incompatible YEAR(x) field 'y2' in t1; Please do "ALTER TABLE `t1` FORCE" to fix it! Warning : Found incompatible YEAR(x) field 'y2' in t1; Please do "ALTER TABLE `t1` FORCE" to fix it! 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; #