Latin1 character set driving me mad!
Posted by: Justin P
Date: April 10, 2012 01:42AM

I have a database that I am moving between webhosts. On the prior webhost, this SQL code worked perfectly:

SELECT addresses.address AS address, storedbits.bits AS bits, addresses.id AS id
FROM addresses LEFT JOIN storedbits ON storedbits.addressesid = addresses.id
WHERE address='$address' COLLATE utf8_bin AND block != -1 AND block < $lastconf ORDER BY block, addresses.id, storedbits.bits LIMIT 1

But, after dumping the table as a .sql, and importing it into the new webhost, I get the following error:

Error in query: SELECT addresses.address AS address, storedbits.bits AS bits, addresses.id AS id FROM addresses LEFT JOIN storedbits ON storedbits.addressesid = addresses.id WHERE address='blahblahblah' COLLATE utf8_bin AND block != -1 AND block < 173962 ORDER BY block, addresses.id, storedbits.bits LIMIT 1. COLLATION 'utf8_bin' is not valid for CHARACTER SET 'latin1'

Ok, fine. So I check my tables. They are set to utf8 - default collation. I check my database. It is set to utf8 - default collation. I check my rows. They are set to utf8 - utf8_bin.

If I remove the COLLATE statement, and instead, use BINARY just before the address comparison, then it works - the query runs fine. But it isn't fast enough. It takes 3-4 times as long to run the query as it does using collation on the old host. I don't know if I can attribute this speed difference entirely to query differences, as the hosts could very well have different speed of CPUs available, but I certainly want to be sure I am doing everything I can to make the queries run quickly and efficiently as possible.

So, where are the latin1 characters, if none of my database is set up with them? Why am I getting this error on the new host, but not the old one with the same query?

EDIT: Also, I tried the guide located here: http://docs.moodle.org/22/en/Converting_your_MySQL_database_to_UTF8

I did these steps:
mysqldump -uusername -ppassword -c -e --default-character-set=utf8 --single-transaction --skip-set-charset --add-drop-database -B dbname > dump.sql
cp dump.sql dump-fixed.sql
vim dump-fixed.sql
:%s/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci/
:%s/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/
:wq
mysql -uusername -ppassword < dump-fixed.sql

The two find/replace commands in VIM didn't find anything, but I re-imported the dump-fixed.sql anyway. Still comes up with the latin1 problem. I don't understand where/why these latin1 characters are coming from!



Edited 1 time(s). Last edit at 04/11/2012 12:28PM by Justin P.

Options: ReplyQuote


Subject
Views
Written By
Posted
Latin1 character set driving me mad!
12315
April 10, 2012 01:42AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.