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.