11.2.5 2-Digit YEAR(2) Limitations and Migrating to 4-Digit YEAR
This section describes problems that can occur when using the 2-digit YEAR(2)
data type and provides information about converting existing YEAR(2)
columns to 4-digit year-valued columns, which can be declared as YEAR
with an implicit display width of 4 characters, or equivalently as YEAR(4)
with an explicit display width.
Although the internal range of values for YEAR
/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, avoid using YEAR(2)
in your applications and use YEAR
/YEAR(4)
wherever you need a year-valued data type. As of MySQL 5.7.5, support for YEAR(2)
is removed and existing 2-digit YEAR(2)
columns must be converted to 4-digit YEAR
columns to become usable again.
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 threeYEAR(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); Query OK, 0 rows affected, 1 warning (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 example, 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 value2012
and display value12
, thus losing the distinctions between them.Conversion of a 2-digit or 4-digit
YEAR
data value to string form uses the data type display width. Suppose that aYEAR(2)
column and aYEAR
/YEAR(4)
column both contain the value1970
. Assigning each column to a string results in a value of'70'
or'1970'
, respectively. That is, loss of information occurs for conversion fromYEAR(2)
to string.Values outside the range from
1970
to2069
are stored incorrectly when inserted into aYEAR(2)
column in aCSV
table. For example, inserting2211
results in a display value of11
but an internal value of2011
.
To avoid these problems, use the 4-digit YEAR
or YEAR(4)
data type rather than the 2-digit YEAR(2)
data type. Suggestions regarding migration strategies appear later in this section.
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 anER_INVALID_YEAR_COLUMN_LENGTH
warning) to 4-digitYEAR
columns: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 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
As of MySQL 5.7.5,
YEAR(2)
column definitions for new tables produce anER_INVALID_YEAR_COLUMN_LENGTH
error:mysql> CREATE TABLE t1 (y YEAR(2)); ERROR 1818 (HY000): Supports only YEAR or YEAR(4) column.
Several programs or statements convert
YEAR(2)
columns to 4-digitYEAR
columns automatically:ALTER TABLE
statements that result in a table rebuild.REPAIR TABLE
(whichCHECK TABLE
recommends you use, if it finds a table that containsYEAR(2)
columns).mysql_upgrade (which uses
REPAIR TABLE
).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 data values.
A MySQL upgrade usually involves at least one of the last two items. However, with respect to
YEAR(2)
, mysql_upgrade is preferable to mysqldump, which, as noted, can change data values.
To convert 2-digit YEAR(2)
columns to 4-digit YEAR
columns, 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 2-digit YEAR(2)
columns to 4-digit YEAR
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: Upgrade MySQL in place without dumping and reloading your data. Then run mysql_upgrade, which uses REPAIR TABLE
to convert 2-digit YEAR(2)
columns to 4-digit YEAR
columns 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 2-digit YEAR(2)
column on the master results in a table containing a 4-digit YEAR
column on the slave. Consequently, the following operations have a different result on the master and slave, if you use statement-based replication:
To avoid such problems, modify all 2-digit YEAR(2)
columns on the master to 4-digit YEAR
columns before upgrading. (Use ALTER TABLE
, as described previously.) That makes it possible to 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. That has the potential to change YEAR(2)
values, as described previously.
A migration from 2-digit YEAR(2)
columns to 4-digit YEAR
columns should also involve examining application code for the possibility of changed behavior under conditions such as these: