Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  YEAR(2) Limitations and Migrating to YEAR(4)

11.3.4 YEAR(2) Limitations and Migrating to YEAR(4)

This section describes problems that can occur when using YEAR(2) and provides information about converting existing YEAR(2) columns to YEAR(4).

Although the internal range of values for YEAR(4) and the deprecated YEAR(2) type is the same (1901 to 2155, and 0000), the display width for YEAR(2) makes that type inherently ambiguous because displayed values indicate only the last two digits of the internal values and omit the century digits. The result can be a loss of information under certain circumstances. For this reason, before MySQL 5.7.5, avoid using YEAR(2) in your applications and use YEAR(4) wherever you need a YEAR data type. As of MySQL 5.7.5, support for YEAR(2) is removed and existing YEAR(2) columns must be converted to YEAR(4) to become usable again.

YEAR(2) Limitations

Issues with the YEAR(2) data type include ambiguity of displayed values, and possible loss of information when values are dumped and reloaded or converted to strings.

  • Displayed YEAR(2) values can be ambiguous. It is possible for up to three YEAR(2) values that have different internal values to have the same displayed value, as the following example demonstrates:

    mysql> CREATE TABLE t (y2 YEAR(2), y4 YEAR(4));
    Query OK, 0 rows affected (0.01 sec)
    mysql> INSERT INTO t (y2) VALUES(1912),(2012),(2112);
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    mysql> UPDATE t SET y4 = y2;
    Query OK, 3 rows affected (0.00 sec)
    Rows matched: 3  Changed: 3  Warnings: 0
    mysql> SELECT * FROM t;
    | y2   | y4   |
    |   12 | 1912 |
    |   12 | 2012 |
    |   12 | 2112 |
    3 rows in set (0.00 sec)
  • If you use mysqldump to dump the table created in the preceding item, the dump file represents all y2 values using the same 2-digit representation (12). If you reload the table from the dump file, all resulting rows have internal value 2012 and display value 12, thus losing the distinctions among them.

  • Conversion of a YEAR(2) or YEAR(4) data value to string form uses the display width of the YEAR type. Suppose that YEAR(2) and YEAR(4) columns both contain the value 1970. Assigning each column to a string results in a value of '70' or '1970', respectively. That is, loss of information occurs for conversion from YEAR(2) to string.

  • Values outside the range from 1970 to 2069 are stored incorrectly when inserted into a YEAR(2) column in a CSV table. For example, inserting 2111 results in a display value of 11 but an internal value of 2011.

To avoid these problems, use YEAR(4) rather than YEAR(2). Suggestions regarding migration strategies appear later in this section.

Reduced/Removed YEAR(2) Support in MySQL 5.7

Before MySQL 5.7.5, support for YEAR(2) is diminished. As of MySQL 5.7.5, support for YEAR(2) is removed.

  • YEAR(2) column definitions for new tables produce warnings or errors:

    • Before MySQL 5.7.5, YEAR(2) column definitions for new tables are converted (with an ER_INVALID_YEAR_COLUMN_LENGTH warning) to YEAR(4):

      mysql> CREATE TABLE t1 (y YEAR(2));
      Query OK, 0 rows affected, 1 warning (0.04 sec)
      mysql> SHOW WARNINGS\G
      *************************** 1. row ***************************
        Level: Warning
         Code: 1818
      Message: YEAR(2) column type is deprecated. Creating YEAR(4) column instead.
      1 row in set (0.00 sec)
      mysql> SHOW CREATE TABLE t1\G
      *************************** 1. row ***************************
             Table: t1
      Create Table: CREATE TABLE `t1` (
        `y` year(4) DEFAULT NULL
      1 row in set (0.00 sec)
    • As of MySQL 5.7.5, 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):

    • Before MySQL 5.7.5, YEAR(2) is processed in queries as in older versions of MySQL.

    • As of MySQL 5.7.5, 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:


Modify the column using ALTER TABLE as follows:


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.

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