sql syntax error when loading mysqldump file

Question

I am receiving a syntax error when I try to load a mysqldump file.

My question has a couple parts:

(1) Why is mysql unable to properly read the file that mysqldump output?
(2) How can I make mysql read in the relevant data from a file?

Heres some details:

mysqldump -u username -p dbname > mydumpfile.sql goes fine (apparently)

mysql -u testuser -p testdbname < mydumpfile.sql gets through only part (about 1/3) of the file, then gives a syntax error:

ERROR 1064 (42000) at line 249: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'randomimproperlydisplayingjapanesetext',''),(508715,134707' at line 1

The text shown as the syntax error is shortly after the beginning of a new insert statement.

The (big) insert statement statement on the previous line is not being entered into the database.

The data is from a database with Japanese text, and the column has utf8_general_ci collation.

MySQL Version 5.6.23 on windows xp.

Here are the other relevant variables (I think):

mysql> show variables like '%char%';
+--------------------------+------------------------------+
| Variable_name            | Value                        |
+--------------------------+------------------------------+
| character_set_client     | sjis                         |
| character_set_connection | sjis                         |
| character_set_database   | sjis                         |
| character_set_filesystem | binary                       |
| character_set_results    | sjis                         |
| character_set_server     | sjis                         |
| character_set_system     | utf8                         |
| character_sets_dir       | C:\mysql\share\charsets\     |
+--------------------------+------------------------------+

Edit Based on the answer below, I determined that there was a SET NAMES line in the mysqldump for setting it as utf8.

Here is the SHOW CREATE TABLE trouble_table RESULTS:

CREATE TABLE `trouble_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `version_id` int(11) DEFAULT NULL,
  `myutf8column` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `mysjisenumcolumn` enum('一式','＊',[a few other japanese charactes]) CHARACTER SET sjis DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `version_id` (`version_id`)
) ENGINE=InnoDB AUTO_INCREMENT=946033 DEFAULT CHARSET=UTF-8 `

So, table character set utf16 (I forgot why), one utf8 column, and one sjis column. In the msyqldump file I can read all values though so it seems that in the dump file all are encoded the same way.

SELECT HEX(mytuf8column) seems to confirm that myutf8column has utf8 encoding (starts with the codes mentioned below, i.e. E383xx,Ewxxyy), and mysjiscolumn has hex values starting with 95 so I guess its probably sjis.

Also, after reading this SOV question, I checked and set the max_allowed_packet to be 33554432, instead of the default, but this did not change the problem.

The part of the table that does load has no noticeable problems with the inserted data, but theres too much data for me to really look through either the db data or the mysqldump file and notice any 'weird' characters that may be causing to mysql to get choked up. (The mysqldump file is over 50MB, so its not enormous by db standards but big enough to be very troublesome to read, Notepad++ and emacs seem helpless with it)

One more thing, Im nervous about changing the column collation because I dont want to lose any data (if the current encoding is wrong, is it safe to change it to another encoding?). It took a long time to parse in the original data, hence I am trying to make a backup copy. Edit Based on answer below, I am no longer nervous about changing collation because it is only a rule for comparsion, rather I am nervous about changing the character sets.

By the way it is not a big deal if mysql needs to simply skip a few problematic rows.

Answer 1

In my case it was caused by a version difference between the exporting and importing mysql versions. My exporting mysql was 5.7.x (Ubuntu 16.04), but the importing was 5.5.x (Ubuntu 14.04). After upgrading the importing to 5.7.x by following this guide, it worked.