SQL error (1265): Data truncated for column

Hi folks.

I originally had a VARCHAR column set to a width of 6, but now I want to reduce it to 3.

If I make the change in the Table tab of HeidiSQL I get the following error when I click the save button:

SQL error (1265): Data truncated for column 'membertype' at row 1

The field "membertype" will only ever consist of a string of three upper case alpha characters.

What can I do about this, to make the change and have it stick?

Your column "membertype" obviously has one or more values which are longer than 3 chars. That's why you get this message from the server.

I believe you are running MySQL in strict mode. Turn it off and the above message should not appear, while that surely cuts of longer values in your membertype column.

Sorry, I don't know what strict mode is.

How do I turn it off and could there be any adverse consequences to doing so?

Please check this:

SHOW VARIABLES LIKE 'sql_mode'

and post the results here.

Hi anse.

I executed that command line in the Query tab and this is all that came up:

STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Is that what you mean?

Exactly. STRICT_TRANS_TABLES is what I meant by "strict mode". That forces you to be somehow more disciplined.

In this case you have to run an UPDATE on your data and truncate them manually. Then, change the column definition and the error should not pop up.

Alternatively, remove STRICT_TRANS_TABLES from the global(!) sql_mode variable (Host > Variables) with a doubleclick, reconnect to the server and apply your changes.

Will I possibly be setting myself up for future problems by removing the STRICT_TRANS_TABLES entry?

This is what the manual says:

n MySQL 5.0.2 and up, you can select stricter treatment of input values by using the STRICT_TRANS_TABLES or STRICT_ALL_TABLES SQL modes:

SET sql_mode = 'STRICT_TRANS_TABLES';
SET sql_mode = 'STRICT_ALL_TABLES';

STRICT_TRANS_TABLES enables strict mode for transactional storage engines, and also to some extent for nontransactional engines. It works like this:

*

For transactional storage engines, bad data values occurring anywhere in a statement cause the statement to abort and roll back.
*

For nontransactional storage engines, a statement aborts if the error occurs in the first row to be inserted or updated. (When the error occurs in the first row, the statement can be aborted to leave the table unchanged, just as for a transactional table.) Errors in rows after the first do not abort the statement, because the table has already been changed by the first row. Instead, bad data values are adjusted and result in warnings rather than errors. In other words, with STRICT_TRANS_TABLES, a wrong value causes MySQL to roll back all updates done so far, if that can be done without changing the table. But once the table has been changed, further errors result in adjustments and warnings.

For even stricter checking, enable STRICT_ALL_TABLES. This is the same as STRICT_TRANS_TABLES except that for nontransactional storage engines, errors abort the statement even for bad data in rows following the first row. This means that if an error occurs partway through a multiple-row insert or update for a nontransactional table, a partial update results. Earlier rows are inserted or updated, but those from the point of the error on are not. To avoid this for nontransactional tables, either use single-row statements or else use STRICT_TRANS_TABLES if conversion warnings rather than errors are acceptable. To avoid problems in the first place, do not use MySQL to check column content. It is safest (and often faster) to let the application ensure that it passes only legal values to the database.

With either of the strict mode options, you can cause errors to be treated as warnings by using INSERT IGNORE or UPDATE IGNORE rather than INSERT or UPDATE without IGNORE.

OK, thanks for all your help.