Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation 'find_in_set'

[GoogleCodeExporter]

Which version of common_schema are you using?
1.1

Which component is failing?
none that I know of

What is the expected output? What do you see instead?
ERROR 1267 (HY000) at line 7629 in file: 'common_schema-1.1.sql': Illegal mix 
of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for 
operation 'find_in_set'
Query OK, 0 rows affected (0.00 sec)

ERROR 1267 (HY000) at line 7664 in file: 'common_schema-1.1.sql': Illegal mix 
of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for 
operation 'find_in_set'
Query OK, 0 rows affected (0.00 sec)

ERROR 1267 (HY000) at line 7706 in file: 'common_schema-1.1.sql': Illegal mix 
of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for 
operation 'find_in_set'
Query OK, 0 rows affected (0.00 sec)

ERROR 1267 (HY000) at line 7738 in file: 'common_schema-1.1.sql': Illegal mix 
of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for 
operation 'find_in_set'
Query OK, 0 rows affected (0.00 sec)

ERROR 1267 (HY000) at line 7768 in file: 'common_schema-1.1.sql': Illegal mix 
of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for 
operation 'find_in_set'
Query OK, 0 rows affected (0.00 sec)

ERROR 1267 (HY000) at line 7800 in file: 'common_schema-1.1.sql': Illegal mix 
of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for 
operation 'find_in_set'

Please provide any additional information below.
MariaDB [common_schema]>show variables like 'version%';
+-------------------------+--------------------+
| Variable_name           | Value              |
+-------------------------+--------------------+
| version                 | 5.5.27-MariaDB-log |
| version_comment         | MariaDB Server     |
| version_compile_machine | x86_64             |
| version_compile_os      | Linux              |
+-------------------------+--------------------+
4 rows in set (0.02 sec)

Original issue reported on code.google.com by gabriel....@brainient.com on 24 Sep 2012 at 8:41

[GoogleCodeExporter]

Maybe it has something to do with:
MariaDB [(none)]>show variables like 'collation_%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

But even if I do
>set @@collation_connection ='utf8_unicode_ci';
And then reissue common_schema import... same thing happens.

Original comment by gabriel....@brainient.com on 24 Sep 2012 at 8:44

[GoogleCodeExporter]

Can you please attach your my.cnf file? In particular character-set and 
collation settings.
Do you have an init-connect setting?

Original comment by shlomi.n...@gmail.com on 25 Sep 2012 at 4:42

[GoogleCodeExporter]

Have just tried to repeat with 5.5.27-MariaDB, encountered no problem. Will 
need your config.

Original comment by shlomi.n...@gmail.com on 25 Sep 2012 at 5:47

[GoogleCodeExporter]

[client]
default-character-set = utf8

[mysqld]

character-set-server      = utf8
collation-server          = utf8_unicode_ci

lower-case-table-names    = 1

event-scheduler           = ON

[mysqld-5.5]

[mariadb]

[mariadb-5.5]
performance_schema
userstat
enable-feedback

Original comment by gabr...@e-radical.ro on 25 Sep 2012 at 11:08

[GoogleCodeExporter]

Most probably the line "collation-server = utf8_unicode_ci" is the cause.

Original comment by gabr...@e-radical.ro on 25 Sep 2012 at 11:09

[GoogleCodeExporter]

Most probably -- but I still can't repeat. Using same config as yours, both for 
[mysqld] and for [client] (was suspecting mismatch between client & server), 
and 5.5.27-MariaDB (64bit) on Ubuntu Linux.

What is the command you use to import common_schema?
Via connector? GUI editor? command line? SOURCE?

I am guessing there will be no problem using SOURCE. Can you verify?

Original comment by shlomi.n...@gmail.com on 25 Sep 2012 at 2:17

[GoogleCodeExporter]

My scenario:

[root@process ~]# cat .bashrc | grep mariadb
alias mariadb='/data/mariadb/bin/mysql --socket=/data/mariadb-data/mariadb.sock 
--prompt="MariaDB-Collector [\d]>"'

Original comment by gabriel....@brainient.com on 27 Sep 2012 at 7:57

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Original comment by gabr...@e-radical.ro on 27 Sep 2012 at 8:01

Attachments:

• terminal-output.txt

[GoogleCodeExporter]

[Sorry about mixing email addresses but it seems at home the wrong GA domain is 
the default domain... I'm the same Gabriel but posting from two addresses]

What else would you need?

Original comment by gabr...@e-radical.ro on 27 Sep 2012 at 8:02

[GoogleCodeExporter]

Thanks! I think you gave me all the info I may need. I will try again to 
reproduce on my machine, though so far unlucky.

I have a good guess as for where the bug originates, but would like to actually 
SEE it...

Original comment by shlomi.n...@gmail.com on 28 Sep 2012 at 4:00

[GoogleCodeExporter]

You might want to try adding in my.cnf that is read by the client the following:

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

Original comment by gabr...@e-radical.ro on 28 Sep 2012 at 9:52

[GoogleCodeExporter]

haha! At last I am able to reproduce. Problem lies within the CREATE DATABASE 
statement and the collation_database variable. Will fix.

Original comment by shlomi.n...@gmail.com on 29 Sep 2012 at 1:19

• Changed state: Accepted

[GoogleCodeExporter]

As a quick fix for you, please add the following line:

ALTER DATABASE common_schema DEFAULT CHARACTER SET 'utf8' DEFAULT COLLATE 
'utf8_general_ci';

Right after the line:

CREATE DATABASE IF NOT EXISTS common_schema;

(line 50 in common_schema-1.1.sql)
Let me know if this solves your immediate error.

Will have this in next release, scheduled soon.

Original comment by shlomi.n...@gmail.com on 29 Sep 2012 at 1:23

• Changed state: Started

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Great.

I confirm that after adding ALTER DATABASE... all works fine.

Same goes for TokuDB for MariaDB.

Original comment by gabr...@e-radical.ro on 1 Oct 2012 at 7:01

[GoogleCodeExporter]

Fixed by r350
(not yet released)

Original comment by shlomi.n...@gmail.com on 2 Oct 2012 at 5:40

• Changed state: Verified