Truncating a BINARY column in MySQL using ALTER TABLE

Question

I have a MySQL table t with over 150 million rows. One of the columns (c) was a VARCHAR(64) containing a 64-digit hexadecimal number. To save space and make things faster, I wanted to decode the hex and turn it into a BINARY(32) column.

My plan was to use three queries:

1. ALTER TABLE t CHANGE c c BINARY(64) NOT NULL;

2. UPDATE t SET c=UNHEX(c);

3. ALTER TABLE t CHANGE c c BINARY(32) NOT NULL;

The first 2 worked perfectly, but on the 3rd query I'm getting the error:

#1265 - Data truncated for column 'c' at row 1

I understand that I am truncating data, that's exactly what I want. I want to get rid of the 32 0x00 bytes at the end of the BINARY(64) to turn it into a BINARY(32).

Things I've tried:

• UPDATE t SET c=LEFT(c, 32); did not seem to do anything at all.

• Using ALTER IGNORE TABLE gives me a syntax error.

Answer 1

To get around the #1265 - Data truncated for column ... error you must remove the STRICT_TRANS_TABLES flag from the global sql_mode variable.

The query SHOW VARIABLES LIKE 'sql_mode'; gave me:

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

So I ran this query:

SET GLOBAL sql_mode = 'NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

For safety, I will re-enable strict mode after I'm done truncating columns.