Inconsistent MySQL COLLATE errors across databases

Posted by Teflon Ted on Stack Overflow See other posts from Stack Overflow or by Teflon Ted
Published on 2010-03-16T13:47:29Z Indexed on 2010/03/16 14:06 UTC
Read the original article Hit count: 284

Filed under:

mysql

|

collate

|

collation

I have two physically-separate MySQL databases on which I have to run a single query.

The query has a section of SQL that looks like this:

and foo_table.bar_column like concat('%', rules.pattern, '%') COLLATE utf8_general_ci

It runs fine on database A but on database B I get this error:

ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

If I remove the collation it runs fine on database B but on database A I get this error:

ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation 'like'

Is there a version of the query that will run on both databases?

Or, is there a configuration I can change on either database to make the query happy in both places?

Update:

Database A is version 5.1.38, Database B is version 5.1.34

© Stack Overflow or respective owner

Related posts about mysql

How to remove MySQL completely with config and library files on ubuntu 12.04 gnome 3.0

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
I tried everything till now: sudo apt-get remove mysql-server mysql-client mysql-common sudo apt-get purge mysql-server mysql-client mysql-common sudo apt-get autoremove and even more commands... But whenever I am trying to locate mysql. I get a no. of files related to mysql command: shell>>… >>> More
mysql: Cannot load from mysql.proc. The table is probably corrupted

as seen on Server Fault - Search for 'Server Fault'
Mysql was started: /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 but when I'm trying to do something: ERROR 1548 (HY000) at line 1: Cannot… >>> More
Why is there a /etc/init.d/mysql file on this Slackware machine? How could it have gotten there?

as seen on Server Fault - Search for 'Server Fault'
A client of my IT-consulting service owns a web-development shop. He's been having problems with a Slackware 12.0 server running MySQL 5.0.67. The machine was set up by the client's sysadmin, who left on bad terms. My client no longer employs a sysadmin. As far as I can tell, the only copy of MySQL… >>> More
mysql: Bind on unix socket: Permission denied

as seen on Server Fault - Search for 'Server Fault'
Can't start mysql with: sudo /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 120222 13:40:48 mysqld_safe Starting mysqld daemon with databases… >>> More
MySQL – Learning MySQL Online in 6 Hours – MySQL Fundamentals in 320 Minutes

as seen on SQL Authority - Search for 'SQL Authority'
MySQL is one of the most popular database language and I have been recently working with it a lot. Data have no barrier and every database have their own place. I have been working with MySQL for quite a while and just like SQL Server, I often find lots of people asking me if I have a tutorial which… >>> More

Related posts about collate

Problem with ColdFusion communicating with MySQL database

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi, I have been working to migrate a non-profit website from a local server (running Windows XP) to a GoDaddy hosting account (running Linux). Most of the pages are written in ColdFusion. Things have gone smoothly, up until this point. There is a flash form within the site (see this page: http://www… >>> More
Inconsistent MySQL COLLATE errors across databases

as seen on Stack Overflow - Search for 'Stack Overflow'
I have two physically-separate MySQL databases on which I have to run a single query. The query has a section of SQL that looks like this: and foo_table.bar_column like concat('%', rules.pattern, '%') COLLATE utf8_general_ci It runs fine on database A but on database B I get this error: ERROR… >>> More
Why is is giving me an SQL syntax error?

as seen on Stack Overflow - Search for 'Stack Overflow'
Do you have any idea why i get this: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '``, `title` varchar(255) collate latin1_general_ci NOT NULL default ``,' at line 3 The code is like this (the part im having… >>> More
what is called KEY

as seen on Stack Overflow - Search for 'Stack Overflow'
CREATE TABLE `ost_staff` ( `staff_id` int(11) unsigned NOT NULL auto_increment, `group_id` int(10) unsigned NOT NULL default '0', `dept_id` int(10) unsigned NOT NULL default '0', `username` varchar(32) collate latin1_german2_ci NOT NULL default '', `firstname` varchar(32) collate latin1_german2_ci… >>> More
Calendar Table - Week number of month

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a calendar table with data from year 2000 to 2012 (2012 wasn't intentional!). I just realize that I don't have the week number of month (e.g In January 1,2,3,4 February 1,2,3,4) How do I go about calculating the week numbers in a month to fill this table? Here is the table schema CREATE… >>> More