Tuesday, May 08, 2012

MySQL LOAD DATA LOCAL INFILE UTF8 WTF

It appears that using LOAD DATA LOCAL INFILE to populate a remote server using the --local-infile ignores the server's character set, and ONLY recognizes the database or sessions. Unfortunately, rebuilding the schema and specifying a default character set, quickly, was out of the question. The solution (that I found, there could always be others) -- was setting the MySQL session settings, "collation_database" and "character_set_database."

Example of LOAD DATA LOCAL INFILE (supporting different MySQL database charsets):

mysql --default-character-set=utf8 -h database_host -uuser -p --local-infile=1 -e "set session collation_database=utf8_general_ci; set session character_set_database=utf8; LOAD DATA LOCAL INFILE '/tmp/source.txt' INTO TABLE table_name (column, column, ....)" database_name