WL#6263: Remove limited YEAR(2) support
Status: Complete
We earlier deprecated the YEAR(2) type, but kept some limited support for old YEAR(2) columns (just to allow upgrade procedures using MySQL 5.6). The code to be able to support this will be hard to maintain and be limited tested, it contains many open bugs, so this WL removes the rest of YEAR(2) code in 5.7. User Documentation ================== http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-5.html http://dev.mysql.com/doc/refman/5.7/en/date-and-time-type-overview.html http://dev.mysql.com/doc/refman/5.7/en/year.html http://dev.mysql.com/doc/refman/5.7/en/migrating-to-year4.html http://dev.mysql.com/doc/refman/5.7/en/check-table.html
This WL supports:
F1: Remove some old codes about YEAR(2).
F2: Must not create YEAR(x!=4) column.
Only support YEAR/YEAR(4) column.
F3: Must not ALTER TABLE a column to YEAR(x!=4) column.
F4: Run DML statements on existent legacy YEAR(X!=4) column will be given a
error.
F5: Run SHOW CREATE TABLE statement on existent legacy YEAR(X!=4) column will
be given a warning.
F6: Must not replicate CREATE YEAR(x!=4) column from 5.5 or 5.1 to this WL.
1. For F1:
There is no change for usage and output.
2. For F2:
When a CREATE TABLE statement includes a YEAR(x!=4) column, a error will be
given. The error is ER_INVALID_YEAR_COLUMN_LENGTH, it output the following
similar info:
ERROR 1818 (HY000): Supports only YEAR or YEAR(4) column.
3. For F3:
When a ALTER TABLE statement alter a column to YEAR(x!=4) column, a error will
be given. The error is ER_INVALID_YEAR_COLUMN_LENGTH, it output the following
similar info:
ERROR 1818 (HY000): Supports only YEAR or YEAR(4) column.
4. For F4:
If we copy a MyISAM table(must include .frm, .MYD, .MYI) that includes
YEAR(x!=4) from 5.5 or 5.1 to this WL version, we must call ALTER/REPAIR
statement or mysql_upgrade program to upgrade this table before we call DML or
SHOW CREATE TABLE statement.
When a DML statement(SELECT, INSERT, UPDATE, DELETE) includes a YEAR(x!=4)
column, then a error will be given. The error is ER_NOT_FORM_FILE, it output the
following similar info:
ERROR HY000: Found incompatible YEAR(x) field in t1; Please do "ALTER TABLE
`t1` FORCE" or "REPAIR TABLE `t1`" to fix it!
A user must use copy command of OS to copy MyISAM table(must include .frm, .MYD,
.MYI) from 5.5 or 5.1 to this WL.
5. For F5:
When a SHOW CREATE TABLE statement includes a YEAR(x!=4) column, then a warning
will be given. The warning is similar with the following info:
Found incompatible YEAR(x) field 'y2' in t1; Please do "ALTER TABLE `t1`
FORCE" to fix it!
6. For F6:
Do not add/delete/modify relevant codes and parameters.
For example:
Example 1: Create YEAR/YEAR(x) column, then call SQL command below.
----------------------------------------------------------------
Function | Old version 5.6 | New version(this WL)
-------------------+-----------------+---------------------
CREATE TABLE with | OK | OK
YEAR(4)/YEAR column| |
-------------------+-----------------+---------------------
CREATE TABLE with | OK, with warning| error
YEAR(x!=4) column | x -> 4 | ER_INVALID_YEAR_COLUMN_LENGTH
-------------------+-----------------+---------------------
DML (SELECT, INSERT| OK | OK
, UPDATE, DELETE) | |
-------------------+-----------------+---------------------
SHOW CREATE TABLE | OK | OK
-------------------+-----------------+---------------------
ALTER TABLE | OK | OK
-------------------+-----------------+---------------------
CHECK TABLE | OK | OK
-------------------+-----------------+---------------------
REPAIR TABLE | OK | OK
-------------------+-----------------+---------------------
Example 2: Copy a MyISAM table with YEAR(x!=4) from 5.5 or 5.1, then call
SQL command below.
------------------------------------------------------------------------------
Function | Old version 5.5 | Old version 5.6 | New version(this WL)
--------------------+-----------------+-----------------+---------------------
DML (SELECT,INSERT, | OK | OK | error
UPDATE, DELETE)| | | ER_NOT_FORM_FILE
--------------------+-----------------+-----------------+---------------------
SHOW CREATE TABLE | OK | OK | warning
| | | Found incompatible
YEAR...
--------------------+-----------------+-----------------+---------------------
ALTER TABLE | OK, x!=4 -> x=4 | OK, x!=4 -> x=4 | OK, x!=4 -> x=4
--------------------+-----------------+-----------------+---------------------
ALTER TABLE | warning[1], | warning[2], | error,x=4 -> x!=4
| x=4 -> x=2 | x=4 -> x=2 |
ER_INVALID_YEAR_COLUMN_LENGTH
--------------------+-----------------+-----------------+---------------------
CHECK TABLE | OK | OK | OK
--------------------+-----------------+-----------------+---------------------
REPAIR TABLE | OK, x!=4 -> x=4 | OK, x!=4 -> x=4 | OK, x!=4 -> x=4
--------------------+-----------------+-----------------+---------------------
warning[1]:
'YEAR(2)' is deprecated and will be removed in a future release. Please use
YEAR(4) instead
warning[2]:
YEAR(2) column type is deprecated. Creating YEAR(4) column instead.
NB:
The user must run REPAIR/ALTER statements when he want to read the old version's
data(5.5 or 5.1) in this WL version.
Example 3: There is no YEAR(x!=4) column in master
| master(5.5 or 5.1) | slave (5.6) | slave(this WL)
--|----------------------------+-----------------------+---------------
A)| create table r (YEAR(x!=4))| OK. x->4 with warning | error
--|----------------------------+-----------------------+---------------
B)| create table r (YEAR(4)) | OK | OK
C)| insert into r values (55) | OK | OK
D)| update, delete | OK | OK
--|----------------------------+-----------------------+---------------
Example 4: There is YEAR(x!=4) column in master, we have created table
successfully in the master and the slave
master> CREATE TABLE t1 (c1 YEAR(2));
slave> CREATE TABLE t1 (c1 YEAR(4));
| master(5.5 or 5.1) | slave (5.6) | slave(this WL)
--|----------------------------+-----------------------+---------------
A)| insert into r values (55) | OK | OK
B)| update r SET c1=20 | OK | OK
C)| delete from r; | OK | OK
--|----------------------------+-----------------------+---------------
Example 5: There is YEAR(x!=4) column in master, we have created table
successfully in the master
master> CREATE TABLE t1 (c1 YEAR(2));
| master(5.5 or 5.1) | slave (5.6) | slave(this WL)
--|----------------------------+-----------------------+---------------
A)| insert into r values (55) | error | error
B)| update r SET c1=20 | error | error
C)| delete from r; | error | error
--|----------------------------+-----------------------+---------------
Note:
If the slave(include 5.6 and this WL) file a error, it is because table r does
not exist.
Example 6: There is no YEAR(x!=4) column in master
| master(5.6) | slave(this WL)
--|----------------------------+---------------
A)| create table r (YEAR(x!=4))| error
--|----------------------------+---------------
B)| create table r (YEAR(4)) | OK
C)| insert into r values (55) | OK
D)| update, delete | OK
--|----------------------------+---------------
CODE CHANGES
============
1. About syntax:
---------------
1) sql_yacc.yy:
* Support to use YEAR/YEAR(4). It will file a error when x!=4.
* Remove the syntax of YEAR(2) code in 5.7.
2. About query execution:
---------------
1) Create_field::init(), sql/field.cc:
* Set the YEAR(x)'s default value is 4.
* Only support YEAR(x)'s default value is 4.
2) Create_field::Create_field(), sql/field.cc:
* Set the YEAR(x)'s default value is 4.
* Only support YEAR(x)'s default value is 4.
3. Table opening
3.1 sql/table.cc
Modify the open_binary_frm() function:
* Output warnings on existent legacy YEAR(2) columns to enforce DB
administrator for mysql_upgrade/REPAIR/ALTER TABLE ... FORCE,
* Mark table share as "crashed".
3.2 sql/sql_base.cc
Modify the open_table() function: if the open_table_from_share()
returns "success", but the table share is marked as "crashes",
then we have a table to upgrade, so:
* Output a warning on "ALTER TABLE", "REPAIR TABLE", "CHECK TABLE"
and "SHOW CREATE TABLES",
* Abort other statements with the ER_CRASHED_ON_USAGE error.
4. Table check
client/mysqlcheck.c
* Let the mysql_upgrade/mysqlcheck tool output a hint when alter
table success.
Note:
Should test InnoDB migration manually.
5. Code simplification
1) get_year_value(), sql/item_cmpfunc.cc
* Remove some dead code about a special case for YEAR(2) type
6. Error infomation
sql/share/errmsg-utf8.txt
* Modify ER_INVALID_YEAR_COLUMN_LENGTH info.
NOTICE
======
1 Q: Why does not this WL support YEAR(x) column where x!=4?
A: This is because YEAR(x!=4) is not some commonly used data type,
it should have very limited impact on users.
It was decided years ago at serverPT.
2 Some migration test, mysql_upgrade test, replication test will be necessary
because YEAR(2) column is removed.
3 Other info, please see WL#6219.
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.