Thursday, August 03, 2006

MySQL 4.1 and insert or Load data warnings

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 :)


Richard said...

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.

sexy said...



A片,色情,成人,做愛,情色文學,A片下載,色情遊戲,色情影片,色情聊天室,情色電影,免費視訊,免費視訊聊天,免費視訊聊天室,一葉情貼圖片區,情色,情色視訊,免費成人影片,視訊交友,視訊聊天,視訊聊天室,言情小說,愛情小說,AIO,AV片,A漫,av dvd,聊天室,自拍,情色論壇,視訊美女,AV成人網,色情A片,SEX