Mysql "SOURCE <file>" importing of UTF8 dump gives question marks

Question

I have searched online for a solution and none of the solutions that I found online seems to work here.

My problem is that I have a MySQL dump (SQL file) containing hebrew text values that is saved in UTF8 encoding, and when I import it using "source [file]" it saves the hebrew characters as question marks (???).

Now, when I look at the SQL file (cat [file]) I can see the hebrew characters properly.

Even when I try to copy & paste the SQL commands from the output that "cat" gave directly into the MySQL command line, it works as well.

It only fails when I use "SOURCE [file]" (which I need, because it is a HUGE file).

I have also tried the following:

1. mysql -uroot -p[pass] --default-character-set=utf8 , and then "SET NAMES utf8" and then "SOURCE [file]" - Gives question marks.
2. Login to mysql client, then do "SET NAMES utf8", "SET COLLATE utf8_bin" (this is the settings for all the tables in the DB) - Gives question marks.
3. CREATE DATABASE [db_name] DEFAULT CHARACTER SET UTF8 with the previous setting (section 2 above) - Gives question marks.
4. mysql -uroot -p[pass] --default-character-set=utf8 [db_name] < [file.sql] - Gives question marks.
5. set character_set_filesystem utf8 and then running source [file] - Gives question marks.

None of these works properly, the ONLY thing that works is if I do copy+paste directly from cat's output to mysql command line, which is not an option because of the length of the file (several hundreds of MB).

Please help, thanks!

Answer 1

Will be problem reading the file as < file.sql.

As documented about using MySQL in batch mode If you are running mysql under Windows and have some special characters in the file < file.sql might cause problems, use this instead:

mysql -e "source file.sql" dbname ... --default-character-set=UTF8

Answer 2

Ok, I managed to solve this problem by creating a PHP "import" script.

First, I took the entire SQL file and split it into two files: structure (commands that create the tables and structure) and data.

Then, I just ran the entire structure.sql file using mysql_query, and took the data file, explode it by "\n" to get the seperate lines of all the INSERTs, and then ran them using a loop and mysql_query.

I didnt even need to include "SET NAMES utf8", figures out once I removed this line everything worked perfectly.

I know this isn't an ideal solution, but it is one that worked for me.