📋

Description

MariaDB Error 1265 indicates that data provided for a column was too long and has been truncated. Instead of failing the operation, MariaDB issues a warning, potentially leading to silent data loss if not addressed.

💬

Error Message

Data truncated for column '%s' at row %ld

🔍

Known Causes

3 known causes

⚠️

Column Data Type Too Small

The data being inserted or updated exceeds the defined length or range of the target column's data type (e.g., a long string into a VARCHAR(10)).

⚠️

Incorrect Character Set Encoding

When using multi-byte character sets (like UTF-8), a string might occupy more bytes than expected, causing truncation if the column length is defined for single-byte characters.

⚠️

Non-Strict SQL Mode Enabled

MariaDB's default behavior in non-strict SQL modes is to truncate data and issue a warning rather than throwing an error for oversized values.

🛠️

Solutions

3 solutions available

1. Adjust Column Data Type and Size medium

Modify the table schema to accommodate the data being inserted.

1

Identify the table and column causing the truncation. The error message should provide this information. For example, if the message is 'Data truncated for column 'product_name' at row 3', the problematic column is 'product_name' in the table where row 3 is being inserted.

2

Determine the maximum length of the data you intend to store in that column. This might involve checking your application's input fields or data sources.

3

Alter the table schema to increase the size or change the data type of the offending column. For example, if you're inserting a string longer than the defined `VARCHAR(255)` size, you might change it to `VARCHAR(500)` or even `TEXT` for larger data.

ALTER TABLE your_table_name MODIFY COLUMN your_column_name VARCHAR(500);

4

If you're dealing with numeric data and receiving truncation, ensure the data type (e.g., `INT`, `BIGINT`, `DECIMAL`) and its precision/scale are appropriate for the range of values you expect. For example, if you're inserting a number larger than `INT` can hold, switch to `BIGINT`.

ALTER TABLE your_table_name MODIFY COLUMN your_numeric_column_name BIGINT;

5

Re-attempt the data insertion operation after altering the table schema.

2. Validate and Sanitize Input Data easy

Clean and trim data before inserting it into the database.

1

Examine the data being inserted. Look for any unexpected whitespace or characters at the beginning or end of string values.

2

In your application code or data processing script, use functions to trim leading and trailing whitespace from string values before sending them to MariaDB. For example, in Python:

data_to_insert['your_column_name'] = data_to_insert['your_column_name'].strip()

3

If the truncation is due to character encoding issues (e.g., inserting multi-byte characters into a `latin1` column), ensure your data is properly encoded and the column's character set is compatible (e.g., `utf8mb4`).

4

Consider implementing validation logic in your application to prevent excessively long strings from being submitted in the first place, either by truncating them to the column's defined length or by rejecting the input.

3. Use `sql_mode` for Strictness Control easy

Temporarily disable strict mode to allow data truncation without error, or adjust it for better data integrity.

1

To quickly bypass the error for a specific session, you can temporarily disable strict SQL mode. This will allow MariaDB to perform the truncation and issue a warning instead of an error.

SET SESSION sql_mode = '';

2

Alternatively, you can remove specific strict modes that cause this error. A common culprit is `STRICT_TRANS_TABLES` or `STRICT_ALL_TABLES`. You can view your current `sql_mode` with:

SELECT @@SESSION.sql_mode;

3

Then, to disable it for the session (replace `your_current_sql_mode` with the output from the previous command, removing the problematic modes):

SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION,ALLOW_INVALID_DATES'; -- Example, adjust as needed

4

For a more permanent change, you can modify the `my.cnf` (or `my.ini`) configuration file. Find the `[mysqld]` section and add or modify the `sql-mode` line. For example, to remove strict modes:

[mysqld]
sql-mode = "NO_ENGINE_SUBSTITUTION,ALLOW_INVALID_DATES"

5

After modifying `my.cnf`, restart the MariaDB server for the changes to take effect.

sudo systemctl restart mariadb

6

Consider that disabling strict mode sacrifices data integrity. It's generally recommended to fix the underlying data or schema issue rather than relying on this as a long-term solution.

🔗

Related Errors

5 related errors

MariaDB Error 1265: Data Truncation Warning