MySQL collate error occurs when sent by PHP app but does not occur in phpMyAdmin

collationMySQLPHP

I have a query that uses:

t3.`value` LIKE "%someval%" COLLATE utf8_general_ci

I load the page in my browser and get this error printed to the screen:

COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

The query is also echoed to the page. And if I copy and paste the outputted query into a phpMyAdmin SQL window, it runs fine, producing the expected result.

I had a previous problem where PHP could not use LOAD DATA INFILE. I wonder if this is a similar type of problem. But I can't see how it would be.

This is running on an Ubuntu Server 10.04 MySQL Apache PHP stack.

Best Answer

Thank you google.

Apparently, I have been using PHP with MySQL without ever needing to worry about client collation.

I don't know if this will lead to other problems, but for the moment I've solved this problem by adding the following query to the main controller (so that the query is run for every script on the site).

SET NAMES "utf8" COLLATE "utf8_general_ci";

Now my query works through my PHP script. I suppose that phpMyAdmin might take the collation of whatever tables you are querying and set the client collation accordingly. I'm just guessing on that one.

Here's where I found the answer: http://dev.mysql.com/doc/refman/4.1/en/charset-collation-charset.html

Related Solutions

SQL Server Collation – Using COLLATE: When to Use and When Not to Use

If you are going to use custom collations for specific databases then yes, you'll need to make the collations match whenever you are joining or unioning data from the two databases.

In fact you will need to do this with many metadata queries anyway. Just look at catalog views like sys.tables:

SELECT c.name, c.collation_name
FROM sys.all_columns AS c
INNER JOIN sys.all_views AS v
ON c.[object_id] = v.[object_id]
INNER JOIN sys.schemas AS s
ON v.[schema_id] = s.[schema_id]
WHERE s.name = N'sys' AND v.name = N'tables'
AND c.collation_name IS NOT NULL;

Results:

name                    SQL_Latin1_General_CP1_CI_AS
type                    Latin1_General_CI_AS_KS_WS
type_desc               Latin1_General_CI_AS_KS_WS
lock_escalation_desc    Latin1_General_CI_AS_KS_WS
durability_desc         Latin1_General_CI_AS_KS_WS

So here we have columns with two different collations inside a single system object. No database can have a DATABASE_DEFAULT that matches both...

You don't have any control over the collation of your customer's columns or databases, or the server collation. So really the only ways to resolve the conflict are to:

use a method that doesn't hard-code a specific collation (like DATABASE_DEFAULT)
hard-code some specific, compatible collation on both sides

Since the latter is more work, makes for more complex queries, and introduces more opportunities to turn seeks into scans, I think the former is really your best option.

MySQL – Troubleshooting High CPU Usage for Single Query

I would recommend adding indexes on the columns participating in the JOIN conditions and the WHERE clauses.

Indexes are designed to help the query optimizer make better choices. Unless you enforce uniqueness on a column that isn't meant to be unique, your indexes will only help.

That said, you may want to enforce uniqueness in the index for columns that should be unique, which helps the optimizer even more.

Keep in mind that indexes (and the statistics using these indexes) need regular maintenance, and they will add additional writes when the table is updated (a small overhead). This is generally far less overhead than the time taken to run queries.