Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 33.6Mb
PDF (A4) - 33.6Mb
PDF (RPM) - 31.7Mb
HTML Download (TGZ) - 8.1Mb
HTML Download (Zip) - 8.1Mb
HTML Download (RPM) - 6.9Mb
Man Pages (TGZ) - 145.6Kb
Man Pages (Zip) - 206.4Kb
Info (Gzip) - 3.1Mb
Info (Zip) - 3.1Mb


MySQL 8.0 Reference Manual  /  ...  /  Migrating YEAR(2) Columns to YEAR(4)

Pre-General Availability Draft: 2017-10-20

11.3.4 Migrating YEAR(2) Columns to YEAR(4)

MySQL 8.0 does not support the YEAR(2) data type permitted in older versions of MySQL. Existing YEAR(2) columns must be converted to YEAR(4) to become usable again. This section provides information about performing that conversion.

Removed YEAR(2) Support in MySQL 8.0

MySQL 8.0 handles YEAR(2) columns as follows:

  • YEAR(2) column definitions for new tables produce an ER_INVALID_YEAR_COLUMN_LENGTH error:

    mysql> CREATE TABLE t1 (y YEAR(2));
    ERROR 1818 (HY000): Supports only YEAR or YEAR(4) column.
  • YEAR(2) column in existing tables remain as YEAR(2), but YEAR(2) columns in queries produce warnings or errors.

  • Several programs or statements convert YEAR(2) to YEAR(4) automatically:

    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.

Migrating from YEAR(2) to YEAR(4)

To convert 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 YEAR(2) 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.

To convert YEAR(2) columns to YEAR(4) manually, use ALTER TABLE or REPAIR TABLE. Suppose that a table t1 has this definition:

CREATE TABLE t1 (ycol YEAR(2) NOT NULL DEFAULT '70');

Modify the column using ALTER TABLE as follows:

ALTER TABLE t1 FORCE;

The ALTER TABLE statement converts the table without changing YEAR(2) values. If the server is a replication master, the ALTER 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 --skip-write-binlog option.

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 treatment of 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:

  • Inserting numeric 0. The resulting value has an internal value of 2000 on the master but 0000 on the slave.

  • Converting YEAR(2) to string. This operation uses the display value of YEAR(2) on the master but YEAR(4) on the slave.

To avoid such problems, modify all YEAR(2) columns on the master to YEAR(4) before upgrading. (Use ALTER TABLE, as described previously.) Then you can upgrade normally (slave first, then master) without introducing any YEAR(2) to YEAR(4) differences between the master and slave.

One migration method should be avoided: Do not dump your data with mysqldump and reload the dump file after upgrading. This has the potential to change YEAR(2) values, as described previously.

A migration from YEAR(2) to YEAR(4) should also involve examining application code for the possibility of changed behavior under conditions such as these:

  • Code that expects selecting a YEAR column to produce exactly two digits.

  • Code that does not account for different handling for inserts of numeric 0: Inserting 0 into YEAR(2) or YEAR(4) results in an internal value of 2000 or 0000, respectively.


User Comments
Sign Up Login You must be logged in to post a comment.