-1

I have a 350 Mb mysql sql file I'm trying to run locally against a remote MySQL instance that I have confirmed access to. My command is this:

"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql" -h camp****.com -u cam****aigns --password=Camp****** cam****igns < "E:\mypath\Camp****19-2020.sql"

It mostly works, but every 5000-10000 lines or so, I encounter an error like the following:

ERROR 1366 (HY000) at line 9320: Incorrect string value: '\xC3\xBD350 ...' for column 'Address1' at row 1

Line 9320 (you can see the special character):

INSERT INTO `Contacts` VALUES (33413,390,'Jim','Hancock','ý350 E 100 N','','Hampton','UT','84050','2012-06-25 15:11:49');

I've seen a number of stack posts that talk about changing ini/cnf settings, etc. But what is the easiest, most straightforward way to make this 1 cmd-line script execution work? Are there command-line args I can pass in, maybe specifying UTF-8 or something?

Improve this question
3
  • Concern, go to your file that you are inserting, go to line 9320 and see what is being inserted, specifically reflecting the address1 column. Any special characters, quotes in the string? What is your insert statement. Please EDIT your post and put the insert statement in there. Don't put in a comment.\ Commented Nov 10, 2020 at 1:35
  • 1
    also add output of show create table yourtablename to your question (for the table that's getting the error) Commented Nov 10, 2020 at 2:21
  • You certainly don't need to change overall server settings to run a script. The command-line client has a --default-character-set option. Commented Nov 10, 2020 at 8:19

2 Answers 2

Reset to default
0

try the following :

  • used ";CharSet=utf8mb4;" in connection string. I missed this one earlier. I was using "utf8".

  • set database's default charset, table's default charset and all column's charset to 'utf8mb4'

  • set database's default collation, table's default collation and all column's collation to 'utf8mb4_unicode_ci'

read more here

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

How?? Can these be added into my cmd-line args? Can you give an example?
0

Adding this to the top of my sql script solved it:

SET NAMES 'utf8';
SET CHARACTER SET utf8;
Improve this answer

1 Comment

Please note that setting encoding this way implies that the client has already read the file and established the connection. It'll possibly work if default values aren't incompatible with your desired settings but it's probably not as robust as setting the value at client level.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.