jlindemann, Member
I've been able to install MyDBR to our current LimeSurvey server. LimeSurvey is working correctly, but I'm new to MyDBR.
The software is running, but if I attempt to access the LimeSurvey DB I get an error
(1267): Illegal mix of collations (utf8_tolower_ci,EXPLICIT) and (utf8_general_ci,EXPLICIT) for operation '='
SQL: call sp_MyDBR_db_tables_views('limesurvey');
I looked back in my installation notes and my LimeSurvey software is MySQL and engine type is MYISAM
myDBR Team, Key Master
Did you install myDBR directly to LimeSurvey or in a separate myDBR database?
The problem comes from mix of collations used. A database collation defines how texts are sorted and compared.
What are the collations used in your databases (mydbr and limesurvey if you have separate ones)? You can see this with:
mysql> use mydbr;
mysql> select @@character_set_database, @@collation_database;
mysql> use limesurvey; mysql> select @@character_set_database, @@collation_database;
Usually you would like to have same character set and collation between the databases.
--
myDBR Team
jlindemann, Member
Turns out I ended up installing MyDBR into its own database. So I've gone back and ran the re-install function and currently at the screen to add it to the limesurvey database.
But I'm stuck here. It appears to see that my limesurvey database is there, but I can't add mydbr to it.
I've attempted to go into the server itself, using the SQL CLI, and enter the commands it references. But after hitting enter I keep ending up with the " -> " like it's waiting for further commands.
myDBR Team, Key Master
It is recommended to install myDBR into it's own database. myDBR does use it's own naming convention so it should not conflict with your existing database objects even if you install them into same database, but there is little benefit for doing that.
Would recommend installing myDBR into it's own database. You can create the database with command:
mysql> create database mydbr character set utf8mb4 collate utf8mb4_general_ci;
The utf8mb4 is the true Unicode character set. The utf8 is an alias for utf8mb3, which is a subset of Unicode characters (3bytes).
You had myDBR running already (the connection was ok). What changed? What is the PHP version you are running?
--
myDBR Team
jlindemann, Member
OK, went back through this again and I have mydbr within the same DB as limesurvey. On the server when I open the MySQL workbench I can expand the limesurvey schema and see all the limesurvey tables and then all the mydbr tables under that.
I also added a 'mydbr' admin user into the limesurvey schema.
OK, so they are showing up together now at least there.
Going back and logging into mydbr again (dba), the environment settings page now shows the database = limesurvey. it has the same error as stated when attempting to use tables & views
(1267): Illegal mix of collations (utf8_tolower_ci,EXPLICIT) and (utf8_general_ci,EXPLICIT) for operation '='
SQL: call sp_MyDBR_db_tables_views('limesurvey');
Using the command you suggested (switched to limesurvey):
mysql> select @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4 | utf8mb4_unicode_ci |
+--------------------------+----------------------+
1 row in set (0.00 sec)
jlindemann, Member
Sorry was writing a reply and didn't see yours until now.
I can split it back to it's own DB. At this point I'm not picky about that. MyDBR is running either way, it just won't talk to the limesurvey DB
I'll wait now and see what you say next
myDBR Team, Key Master
What do you mean by "it just won't talk to the limesurvey DB"?
Just to check for a MySQL bug that was in MySQL 8's earlier versions.
What is the output of:
select table_name, if( table_type='VIEW', 'V', 'T')
from information_schema.tables
where table_schema=cast('limesurvey' AS CHAR CHARACTER SET utf8) COLLATE utf8_general_ci
order by table_name;
Does that provide you a list of tables/views of does it produce an error?
--
myDBR Team
jlindemann, Member
"Won't talk to the DB" is just my way of saying it errors out each time it attempts to pull the information.
mysql> select table_name, if( table_type='VIEW', 'V', 'T')
-> from information_schema.tables
-> where table_schema=cast('limesurvey' AS CHAR CHARACTER SET utf8) COLLATE utf8_general_ci
-> order by table_name;
ERROR 1267 (HY000): Illegal mix of collations (utf8_tolower_ci,EXPLICIT) and (utf8_general_ci,EXPLICIT) for operation '='
myDBR Team, Key Master
"Won't talk to the DB" is just my way of saying it errors out each time it attempts to pull the information.
Is the error the one that you are seeing with the query? Where exactly do you see the error?
"ERROR 1267 (HY000): Illegal mix of collations (utf8_tolower_ci,EXPLICIT) and (utf8_general_ci,EXPLICIT) for operation '='"
What you are experiencing is a bug in your MySQL installation. The system tables (views in MySQL) holding information about the tables and other info, should be stored as utf8 with collation utf8_general_ci. In your setup the views are using utf8_tolower_ci-collation which causes the "Illegal mix of collations" error.
The bug was supposed to be fixed in 8.0.13. How did you install the MySQL? Was it a fresh install or did you upgrade from earlier version?
See:
https://bugs.mysql.com/bug.php?id=90690
--
myDBR Team
jlindemann, Member
MySQL was installed as part of LimeSurvey when it was installed. Looking back over the notes of the (fresh) installation, defaults were chosen when MySQL was installed. So I'm not sure why one collation was chosen over the other.
The full install of MySQL was done first. But during the installation of LimeSurvey (a similar setup to how MyDBR is setup/installed) Limesurvey created the db for us. We just had to supply the SQL username/password.
When I load MyDBR on the server, and I attempt to use "tables & views" this is the error I get
(1267): Illegal mix of collations (utf8_tolower_ci,EXPLICIT) and (utf8_general_ci,EXPLICIT) for operation '='
SQL: call sp_MyDBR_db_tables_views('limesurvey');
when I ran the command you asked about, this was the error I got
ERROR 1267 (HY000): Illegal mix of collations (utf8_tolower_ci,EXPLICIT) and (utf8_general_ci,EXPLICIT) for operation '='
So obviously I have a problem within my MYSQL setup. Is there a way to convert the whole Limesurvey DB without creating problems? Or make MyDBR read it? What do you think?
myDBR Team, Key Master
So obviously I have a problem within my MYSQL setup. Is there a way to convert the whole Limesurvey DB without creating problems? Or make MyDBR read it? What do you think?
Your Limesurvey and myDBR databases are likely just fine. It is just that the MySQL installation for some reason is not correct. You could backup your own databases (Limesurvey & myDBR) and reinstall MySQL. Then you can recreate the databass from the backup.
Alternatively you could install MariaDB instead. MariaDB is compatible with MySQL (has same codebase). You can restore the MySQL backups into MariaDB.
--
myDBR Team
myDBR Team, Key Master
We tested with MySQL 8.0.23 and the INFORMATION_SCHEMA views have utf8_tolower_ci as collation. We'll make myDBR to work with this collation as well. Should be quick.
--
myDBR Team
myDBR Team, Key Master
myDBR 5.9.1 will now work with INFORMATION_SCHEMA utf8_tolower_ci collations. You can run the automatic updater and you should be ok.
--
myDBR Team
jlindemann, Member
that did the trick. thank you!
and amazing speed for resolution. I'm quite impressed with that.
You must log in to post.