When using LOAD DATA especially when importing from one charset container to utf8. Make sure to issue SHOW WARNINGS after the statement. Why? Well a cryptic message might occur and lead you to the true cause of the problem.
For instance if you get a message:
"Data truncated for column 'column' at row 157"
Why was row 157 truncated? The data use to exist before correctly in the table structure in the old format, what's different?
Well, in the scenario of upgrading to true utf8 column character sets, MySQL will remove all invalid utf8 chars and produce this message. You might be lead to believe oh crap maybe I need to increase the column size, NO that's not it. In MySQL 4.1 varchar, text, chars size are no longer just a function of bytes. Previous to 4.1
varchar(255) means that it would take up to 255 bytes, i.e. 1 latin1 character 1 byte. In 4.1+ this means that the column can hold up to 255 characters with a max byte size of 765 bytes for 3 byte utf8 characters.
So, why such a cryptic message, why not say "This row contains an invalid character. Data truncated for column at row 157".
Because you haven't bought a MySQL support license and requested this addition :)
1 comment:
I REALLY wish i could get mysql to echo the warnings when they occur without requiring an extra step. I regularly write complex "data massaging" scripts that I execute via: 'source formatLayout.sql' When a warning happens on one of the commands, it's lost when the next executes. It's VERY frustrating. I thought I once had a setting the did this, but now I cannot find it.
Post a Comment