Error Code: 1366 – Incorrect String Value: 'xa350'

[Mike Goodman]

Hi All,

Trying to Import an MysqlDump I get; Error Code: 1366 – Incorrect string value: 'xA350' for column 'contracttext' at row 1

sqlyog.err shows;

Query:

insert into `contracts` values ('110', '1069', ' Chantel Lockwood ', 'Air Cadets', '244', 'Tupton', 'Chesterfield', '11-04-2011', '11-04-2011', '', '0', '1845', '2045', '20110411', '-1,35,34', '(O) Track Pitch, (O) Runing Track', 'no charge due to last bookings error.', ' Chantel Lockwood ', '25.00', '2.00', '50.00', '0', 'Active', 'chris houghton', 'From 11-04-2011 to 11-04-2011. (Actual dates listed below) no charge due to error 2010rn18/4/11 @ 25 per hour session charge £50', 'Yes', '-', '-', '-', '-')

Error occured at:2011-04-22 18:02:51

Line no.:75

Error Code: 1366 – Incorrect string value: 'xA350' for column 'contracttext' at row 1

I have narrowed it down by deleteing characters and find it is the '£' character causing the problem.

Googling the problem, it appears to be the Character settings not being set for my area (UK).

How do I correct this.

phpMyAdmin imports the same dump without error but I would prefer to use SQLyog.

Thanks in advance for any assistance.

Mike

[peterlaursen]

Are you using HTTP tunnel? In that case please try to enable base64 encoding for the connection (from connection manager .. HTTP tab .. advanced button).

It could be a duplicate of this http://code.google.c…es/detail?id=77 and 'base64' option should help.

[Mike Goodman]

'peterlaursen' wrote:

Are you using HTTP tunnel? In that case please try to enable base64 encoding for the connection (from connection manager .. HTTP tab .. advanced button).

It could be a duplicate of this http://code.google.c…es/detail?id=77 and 'base64' option should help.

Thanks for the reply..

I am using mySQL, not HTTP Tunnel.

What I have noticed, due to my inexperience, I have set up all my tables using latin1_swedish_ci instead of utf8_general_ci

Could this be the problem?

[peterlaursen]

It would help a lot if you

1) told the exact server version (execute “SELECT VERSION();”). It could be a charset problem on the server if it is not a recent version.

2) could attach a small dump where this is reproducible. It can be random/dummy data as long as it is reproducible and if you don't want to expose data to the public you may create a support ticket by sending a mail to [email protected].

Also please tell: With what program was the dump created (if not with SQLyog)? I think the problem could be this http://www.webyog.com/faq/content/34/148/en/do-i-need-to-set-names-to-display-characters-of-my-language.html

“However if you execute a SQL-script from a file you may include a SET NAMES statement in that file. You will need to do if character data in the file are not encoded with the server default charset. When SQLyog executes an external file, a separate connection is created for that and SET NAMES in that file will not affect the connection that the GUI uses. SQL scripts generated by SQLyog are UTF8-encoded and the file contains a “SET NAMES utf8″ statement. But if the script is generated with another program it is not necessarily the case.” This you can verify by importing using MySQL command line – syntax like “mysql source {filename};”.

[Mike Goodman]

'peterlaursen' wrote:

It would help a lot if you

1) told the exact server version (execute “SELECT VERSION();”). It could be a charset problem on the server if it is not a recent version.

2) could attach a small dump where this is reproducible. It can be random/dummy data as long as it is reproducible and if you don't want to expose data to the public you may create a support ticket by sending a mail to [email protected].

Also please tell: With what program was the dump created (if not with SQLyog)? I think the problem could be this http://www.webyog.com/faq/content/34/148/en/do-i-need-to-set-names-to-display-characters-of-my-language.html

“However if you execute a SQL-script from a file you may include a SET NAMES statement in that file. You will need to do if character data in the file are not encoded with the server default charset. When SQLyog executes an external file, a separate connection is created for that and SET NAMES in that file will not affect the connection that the GUI uses. SQL scripts generated by SQLyog are UTF8-encoded and the file contains a “SET NAMES utf8″ statement. But if the script is generated with another program it is not necessarily the case.” This you can verify by importing using MySQL command line – syntax like “mysql source {filename};”.

Hi Peter, Thanks for your assistance.

1) Result of SELECT VERSION(); is 5.0.88-community-nt

2) I have uploaded a section of the dump file for one table that produces the error. Download from Here. (I also managed to attach it eventually.)

The Dump that displays the error in SQLyog is created by phpMyBackupPro (open source scheduled backup), or phpMyAdmin.

If the Dump is created by SQLyog, No error is displayed, but the data after the offending characters for the current table is not imported. The next table is then attempted.

By changing all pound signs to a hash, it goes further but trips up when it gets to a ½ (asc 0189) character with the same error. (Not present in the attached file)

I hope this info is of help.

Regards

Mike

[peterlaursen]

We will check after the Easter holidays. But I'd not be surprised if

1) the data in the dump are latin-encoded

2) there is no SET NAMES statement in the beginning of the dump (what there should always be).

2) and your server has uft8 as default charset. What does “SHOW VARIABLES LIKE 'char%';” return on that server?

[Mike Goodman]

'peterlaursen' wrote:

We will check after the Easter holidays. But I'd not be surprised if

1) the data in the dump are latin-encoded

2) there is no SET NAMES statement in the beginning of the dump (what there should always be).

2) and your server has uft8 as default charset. What does “SHOW VARIABLES LIKE 'char%';” return on that server?

Have a Good Easter Peter.

Thanks for your help.

Attached is result of “SHOW VARIABLES LIKE 'char%';” for both Local and Web Server

Regards

Mike

[peterlaursen]

OK .. character_set_server is latin1. I wonder if “€” is available in latin1. It looks like the application uses some 'trick' to represent it. But now lets us try to import your dump on various environments.

[Mike Goodman]

'peterlaursen' wrote:

OK .. character_set_server is latin1. I wonder if “€” is available in latin1. It looks like the application uses some 'trick' to represent it. But now lets us try to import your dump on various environments.

Hi Peter,

NB

The “£” is what is creating the error, plus some others. “½” for instance (Ascii 0189)

Please let me know what you would like me to do next when you are ready.

Thanks

Mike

[peterlaursen]

We are working with it now. 1 or a few hours and we probably have the conclusion!

[peterlaursen]

Could you please try to add on top of the script this: “SET NAMES latin1;”

The problem happens with latin1 characters outside the ASCII-range. In 'strict mode' execution of the script returns errors and the script aborts. In 'empty mode' you get warnings and truncations for the affected characters. I think you have strict mode on? (execute “SHOW GLOBAL VARIABLES LIKE 'sql_mode';”)

I have created this report in our issue tracker http://code.google.com/p/sqlyog/issues/list?thanks=1630 . But I do not quite understand as you tell that latin1 is default character set for this server. We do not (or should not) explicitly define a client character set for the background thread whre the import runs. We are checking this.

[Mike Goodman]

'peterlaursen' wrote:

Could you please try to add on top of the script this: “SET NAMES latin1;”

The problem happens with latin1 characters outside the ASCII-range. In 'strict mode' execution of the script returns errors and the script aborts. In 'empty mode' you get warnings and truncations for the affected characters. I think you have strict mode on? (execute “SHOW GLOBAL VARIABLES LIKE 'sql_mode';”)

I have created this report in our issue tracker http://code.google.com/p/sqlyog/issues/list?thanks=1630 . But I do not quite understand as you tell that latin1 is default character set for this server. We do not (or should not) explicitly define a client character set for the background thread whre the import runs. We are checking this.

I think you have strict mode on? (execute “SHOW GLOBAL VARIABLES LIKE 'sql_mode';”)It appears you are correct; I have attached image of “SHOW GLOBAL VARIABLES LIKE 'sql_mode';” results.

How would I correct this?

I shall try to add on top of the script this: “SET NAMES latin1;” and reply later

[Mike Goodman]

'Mike wrote:

I think you have strict mode on? (execute “SHOW GLOBAL VARIABLES LIKE 'sql_mode';”)It appears you are correct; I have attached image of “SHOW GLOBAL VARIABLES LIKE 'sql_mode';” results.

How would I correct this?

I shall try to add on top of the script this: “SET NAMES latin1;” and reply later

I have done as you suggested

Quote:

add on top of the script this: “SET NAMES latin1;

to the small dump and the table was imported perfectly including the “£” sign.

I then added the line to the top of the Full Dump and it also imported perfect, including the “½” character.

It appears you are understanding my problem.

Mike

[peterlaursen]

I have to say that I find it a bug with the application used for backup that it does not SET NAMES according to how the file is encoded.

But still there may be an issue on our side that we should look into. If server default charset really is latin1 the dump should import.

Anyway: cant you use SQLyog for creating dumps/backups? They will import with no problem.

[Mike Goodman]

'peterlaursen' wrote:

I have to say that I find it a bug with the application used for backup that it does not SET NAMES according to how the file is encoded.

But still there may be an issue on our side that we should look into. If server default charset really is latin1 the dump should import.

Anyway: cant you use SQLyog for creating dumps/backups? They will import with no problem.

I understand what you are saying and realise that by adding the SET NAMES statement makes it work perfectly..

There are a few other things to remember though.

1. I also get this problem when I create the dump with phpAdmin and a different problem when creating it with SQLyog.

As I mentioned in Post #5 of this topic.

The Dump that displays the error in SQLyog is created by phpMyBackupPro (open source scheduled backup), or phpMyAdmin.

If the Dump is created by SQLyog, No error is displayed, but the data after the offending characters for the current table is not imported. The next table is then attempted.

I thought I would have a look at a dump produced by SQL yog and found that it has the SET NAMES statement but do not understand what the /*! code means at the beginning of the lines. I realise that /* this text will be ignored */. But do not understand what the “!” does.

This is at the top of the Dump Created by SQLyog;-

Code:

/*
SQLyog Professional v9.01
MySQL – 5.0.77-log : Database – hidden
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

/*Table structure for table `contracts` */

DROP TABLE IF EXISTS `contracts`;

I can, of course manually create an SQL dump but I need to create the dump automatically.

Also to take into consideration, the dumps will import OK using phpAdmin or phpMyBackupPro

Import from a SQLyog dump, drops data after the offending characters with no warning.

I hope this info helps.

I appreciate the work you are putting into this.

Regards

Mike

[Mike Goodman]

An Update

I have been doing quite a lot of experiments.

When I do an SQLdump using SQLyog, it Imports OK now and uses /*!40101 SET NAMES utf8 */;

If I change this to latin1, some characters apear with  before them ( £ ½ )

When I do an SQLdump from phpMyAdmin or phpMyBackupPro, it errors using utf8 but Imports perfect with latin1

I am emailing the two SQL dumps to the address as suggested earlier so you can compare the structures

The headers of each tell what created them. I have added the line SET NAMES latin1; to the Non-SQLyog file

I hope this is helpfull

[Author]

Posts