Collation issues « myDBR Forums

myDBR Forums » General discussion

Collation issues

(13 posts) (2 voices)

Started by shem
Latest reply from shem

Tags:

shem, Member

In MySQL workbench I changed my "System Variables"
for the Collation Variables to persist as
utf8mb4_general_ci
because that is what our application databases use.
Now every time I try to run a myDBR report from the mydBR Portal I get
(1267): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='
SQL: call sp_MyDBR_ReportNameGet(89,'dba',2,'');
shem, Member

Actually now I can't even login.
As Soon as I go to
http://localhost/mydbr/index.php?a=login
I get
(1267): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='
SQL: call sp_MyDBR_GetOptions('',0);
myDBR Team, Key Master

In MySQL and MariaDB you can ser character set / collation for multiple levels; server, database, table and column. For existing tables, you need to chnage the collation for the columns.

In order to change the collation for existing tables, you need to chaneg the collation for each column. Google the queries that let you generate the needed MODIFY queries,

--
myDBR Team
shem, Member

I reverted my changes to the "System Variables" so I am not dealing with the serious problem I mentioned above.
the reason I originally made those changes was not because of specific tables and the collation defined for those tables.
Rather it was for temporary table that my stored procedures were creating and it was causing a collation inconsistency, so I would like to ask if there is a myDBR wide collation definition somewhere that is defaulting temp table to utf8mb4_0900_ai_ci and how can I change this default to utf8mb4_general_ci
myDBR Team, Key Master
myDBR does not set the collation but relies on the server/database setting. Check your server settings for the collations:
```
show variables like '%collation%'
```
--
myDBR Team
shem, Member

Hi, I know this is an issue I was dealing with a year ago.
In the meanwhile I used a workaround.
But now I was able to get the default collation on my local machine to be utf8mb4_general_ci just like it always was on our companies test server.
However, while on my companies test server I have no problem getting into the mydbr portal, on my local VM I am once again getting this error while trying to get into the mydbr portal (using http://localhost/mydbr/index.php?a=login)
(1267): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='
SQL: call sp_MyDBR_GetOptions('',0);

You said I should verify my settings by doing
show variables like '%collation%';

so I did and it shows exactly what I want it to show namely:
mysql> show variables like '%collation%';
+-------------------------------+--------------------+
| Variable_name | Value |
+-------------------------------+--------------------+
| collation_connection | utf8mb3_general_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci |
| default_collation_for_utf8mb4 | utf8mb4_general_ci |
+-------------------------------+--------------------+
4 rows in set (0.01 sec)

Everything in mysql seems to work.
So what do I need to do to be able to get into the mydbr portal ?
shem, Member

Further to my above question.
It appears that at the time the mydbr databasse was created my default settings were for utf8mb4_0900_ai_ci and now I have changed it to utf8mb4_0900_ai_ci.
Do I need to fix all the mydbr system tables?
Or can we just fix the ones related to logging on to the portal?
myDBR Team, Key Master

It is easier to use one collation for the connection and the database. Saves you a lot of extra work.

Check the show variables like '%collation%' in myDBR SQL Editor.
--
myDBR Team
shem, Member

At the moment I CANNOT get into the mydbr portal because of the error
(1267): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='
SQL: call sp_MyDBR_GetOptions('',0);

But as I wrote you above, here are my settings
show variables like '%collation%';
+-------------------------------+--------------------+
| Variable_name | Value |
+-------------------------------+--------------------+
| collation_connection | utf8mb3_general_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci |
| default_collation_for_utf8mb4 | utf8mb4_general_ci |
+-------------------------------+--------------------+

which is exactly what I want.
And it matches what is on our testb server.
There I am able to get into the portal, I guess because the settings for utf8mb4_general_ci were done before installing mydbr.
My problem is what to do on my local VM
What is your solution?
You say
"t is easier to use one collation for the connection and the database."
But I do not want to revert to utf8mb4_0900_ai_ci because our production and test servers use utf8mb4_general_ci and that is our company policy.
myDBR Team, Key Master

What are the collations for mydbr_options table character columns? Assumign all myDBR columns in your installation share the same collation.

--
myDBR Team
shem, Member

SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'mydbr'
AND TABLE_NAME = 'mydbr_options';
+---------------+----------------+--------------------+
| TABLE_NAME | COLUMN_NAME | COLLATION_NAME |
+---------------+----------------+--------------------+
| mydbr_options | authentication | NULL |
| mydbr_options | name | utf8mb4_0900_ai_ci |
| mydbr_options | user | utf8mb4_0900_ai_ci |
| mydbr_options | value | utf8mb4_0900_ai_ci |
+---------------+----------------+--------------------+
4 rows in set (0.01 sec)

So is this the only table I have to fix, and then to recreate procedure sp_MyDBR_GetOptions
or do I have to fix ALL of them, and do I have to recreate ALL the stored procedures ?
myDBR Team, Key Master

Most likely all your tables have incorrect collation. Create a script from system tables for the ALTER table commands or dump the database and change the collation definitions from the dump file and load the database back in.

--
myDBR Team
shem, Member

ok, thanks

Reply

You must log in to post.