[xerxes-portal] Populate databases fail on General error 1366 Incorrect string value

[Barnaby Alter]

Hello,

Recently our dev instance of Xerxes has been failing on populate databases with the following:

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xE5\x85\xA7\xE9\x96\xA3...' for column 'title_display' at row 1' in /lib/framework/DataMap.php:160

This happened as we are moving to a new mysql server which requires InnoDB engine. I'm having a hard time figuring out why it would be failing all the sudden on this strange error if not for an encoding problem.

Has anyone seen this? 

Your advice, as always, is greatly appreciated!

Barnaby Alter

Web Development

Division of Libraries

NYU

[Walker, David]

Is the new database set to use UTF-8?

 

-------------------------

David Walker

Interim Director, Systemwide Digital Library Services

California State University

562-355-4845

--
You received this message because you are subscribed to the Google Groups "xerxes-portal" group.
To post to this group, send email to xerxes...@googlegroups.com.
To unsubscribe from this group, send email to xerxes-porta...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/xerxes-portal?hl=en.

[Barnaby Alter]

No it appears that it is not. Our former dev wasn't either, but our current production was. Obviously some inconsistencies here.

I've done this with other databases before but I assume I can't just change that definition but have to convert the whole database. Or because Xerxes reloads most of it's data on each cron, could I just change the encoding you think? This would effect the encoding on special characters on the categories and user records tables, but probably nothing else, yes?

Thanks,

Barnaby Alter

Web Development

Division of Libraries

NYU

[Walker, David]

The main SQL script should already set the encoding to UTF-8:

 

  http://xerxes-portal.googlecode.com/svn/trunk/sql/mysql/create-kb.sql

 

So if you run that (make sure you set the database name correctly) and then re-populate from Metalib, it might help.  Not sure if it will, but this seems a possible source of problems.

 

--Dave

[Barnaby Alter]

Hi David,

That script sets up the database in utf8, but not if it's already existing. I converted the database default charset to utf8 (alter database xerxes charset=utf8) and ran the create-kb.sql again. This might have effected any language encodings for saved records, but since I had to do this on a dev box (which will become our production but with a cut of current production which is already utf8 encoded) I'm not concerned about that.

This solved the problem, but if others have this issue and the integrity of the data is more important I've had to convert full database from latin1 to utf8 before and it's imperfect at best. So glad it didn't come to that today.

Thanks for the help,

Barnaby Alter

Web Development

Division of Libraries

NYU

[helix84]

On Wed, Jun 20, 2012 at 6:12 PM, Barnaby Alter <ba...@nyu.edu> wrote:
> This solved the problem, but if others have this issue and the integrity of
> the data is more important I've had to convert full database from latin1 to
> utf8 before and it's imperfect at best. So glad it didn't come to that
> today.

How did you convert latin1 to utf8? It shouldn't be a problem because
latin1 is a subset of utf8, so I think you did something wrong. Did
you use iconv or recode?

Regards,
~~helix84

[Barnaby Alter]

Didn't know it was a subset, good to know. I used iconv in ruby. It mostly did the job but we still noticed a few off encodings. They might've not been properly encoded in the first place though.

Barnaby Alter

Web Development

Division of Libraries

NYU