📋

Description

This warning occurs when attempting to define a `YEAR` column with a specified display width, such as `YEAR(2)` or `YEAR(4)`. MariaDB automatically converts the column to `YEAR(4)` for compatibility, but issues this warning to highlight the use of deprecated syntax. It signals that explicitly specifying a length for the `YEAR` data type is no longer recommended and will be ignored.

💬

Error Message

YEAR(%lu) column type is deprecated. Creating YEAR(4) column instead.

🔍

Known Causes

4 known causes

⚠️

Using `YEAR(2)` Syntax

Legacy SQL schemas or applications might still define `YEAR` columns with a display width of 2, which is now deprecated.

⚠️

Explicit `YEAR(4)` Definition

Even though `YEAR(4)` is the default behavior, explicitly specifying the length is considered deprecated syntax in newer MariaDB versions.

⚠️

Outdated Application Code/ORM

Applications or Object-Relational Mappers (ORMs) built for older database versions might generate `CREATE TABLE` or `ALTER TABLE` statements with `YEAR(length)`.

⚠️

Migration from Legacy Systems

When migrating databases or schemas from very old MySQL or MariaDB versions, the original `YEAR(length)` definitions can trigger this warning.

🛠️

Solutions

3 solutions available

1. Update Table Schema to YEAR(4) medium

Modify existing table definitions to use YEAR(4) instead of the deprecated YEAR() syntax.

1

Identify tables and columns using the deprecated YEAR() type. You can use the following query to find them.

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_TYPE LIKE 'year()%';

2

For each identified column, alter the table to use YEAR(4). Replace `your_table_name` and `your_column_name` with your actual table and column names.

ALTER TABLE your_table_name MODIFY COLUMN your_column_name YEAR(4);

3

If you are creating new tables, explicitly define YEAR(4) for year columns.

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    event_year YEAR(4)
);

2. Modify Application Code During Table Creation easy

Update your application's SQL statements to use YEAR(4) when creating tables.

1

Locate the SQL `CREATE TABLE` statements in your application code or migration scripts that define year columns.

/* Before */
CREATE TABLE my_data (
    id INT PRIMARY KEY,
    birth_year YEAR
);

2

Change the `YEAR` definition to `YEAR(4)`.

/* After */
CREATE TABLE my_data (
    id INT PRIMARY KEY,
    birth_year YEAR(4)
);

3

Re-run your application's database migration scripts or update your application code to reflect this change.

N/A

3. Use Data Migration Tools for Schema Updates medium

Leverage database schema migration tools to systematically update year column definitions across your database.

1

If you use a schema migration tool (e.g., Flyway, Liquibase, Django migrations, Alembic), create a new migration script.

N/A

2

In the migration script, use the tool's syntax to generate an `ALTER TABLE` statement that modifies the year columns to `YEAR(4)`. The exact syntax will depend on your tool.

Example (conceptual, actual syntax varies by tool):
-- For Flyway:
ALTER TABLE your_table_name MODIFY COLUMN your_column_name YEAR(4);

3

Apply the migration script to your database.

N/A

🔗

Related Errors

5 related errors

MariaDB Error 1818: Deprecated YEAR Column Length