MySQL 8.0 does not support the
YEAR(2) data type permitted in
older versions of MySQL. Existing
YEAR(2) columns must be converted
YEAR(4) to become usable
again. This section provides information about performing that
MySQL 8.0 handles
YEAR(2) columns as follows:
mysql> CREATE TABLE t1 (y YEAR(2)); ERROR 1818 (HY000): Supports only YEAR or YEAR(4) column.
ALTER TABLEstatements that result in a table rebuild.
Dumping with mysqldump and reloading the dump file. Unlike the conversions performed by the preceding three items, a dump and reload has the potential to change values.
A MySQL upgrade usually involves at least one of the last two items. However, with respect to
YEAR(2), mysql_upgrade is preferable. You should avoid using mysqldump because, as noted, that can change values.
YEAR(2) columns to
YEAR(4), you can do so manually
at any time without upgrading. Alternatively, you can upgrade
to a version of MySQL with reduced or removed support for
YEAR(2) (MySQL 5.6.6 or later),
then have MySQL convert
columns automatically. In the latter case, avoid upgrading by
dumping and reloading your data because that can change data
values. In addition, if you use replication, there are upgrade
considerations you must take into account.
CREATE TABLE t1 (ycol YEAR(2) NOT NULL DEFAULT '70');
Modify the column using
ALTER TABLE as
ALTER TABLE t1 FORCE;
ALTER TABLE statement
converts the table without changing
YEAR(2) values. If the server
is a replication master, the
TABLE statement replicates to slaves and makes the
corresponding table change on each one.
Another migration method is to perform a binary upgrade:
Install MySQL without dumping and reloading your data. Then
run mysql_upgrade, which uses
REPAIR TABLE to convert
YEAR(2) columns to
YEAR(4) without changing data
values. If the server is a replication master, the
REPAIR TABLE statements
replicate to slaves and make the corresponding table changes
on each one, unless you invoke
mysql_upgrade with the
Upgrades to replication servers usually involve upgrading
slaves to a newer version of MySQL, then upgrading the master.
For example, if a master and slave both run MySQL 5.5, a
typical upgrade sequence involves upgrading the slave to 5.6,
then upgrading the master to 5.6. With regard to the different
YEAR(2) as of
MySQL 5.6.6, that upgrade sequence results in a problem:
Suppose that the slave has been upgraded but not yet the
master. Then creating a table containing a
YEAR(2) column on the master
results in a table containing a
YEAR(4) column on the slave.
Consequently, these operations will have a different result on
the master and slave, if you use statement-based replication:
To avoid such problems, modify all
YEAR(2) columns on the master
YEAR(4) before upgrading.
ALTER TABLE, as described
previously.) Then you can upgrade normally (slave first, then
master) without introducing any
YEAR(4) differences between the
master and slave.