Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
10.4(EOL), 10.5(EOL)
-
None
-
Related to install and upgrade
Description
10.4+ has mysql.user view for the backward compatibility, but this compatibility is flawed.
Simple queries from like
|
10.5 deadec4e |
select * from mysql.user where is_role = 'Y'; |
can fail with ER_CANT_AGGREGATE_2COLLATIONS (illegal mix of collations) errors when it wouldn't happen before. For example,
MariaDB [test]> select collation_connection from information_schema.views where table_schema = 'mysql' and table_name = 'user'; |
+----------------------+ |
| collation_connection |
|
+----------------------+ |
| latin1_swedish_ci |
|
+----------------------+ |
1 row in set (0.001 sec) |
|
|
MariaDB [test]> set names latin2; |
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [test]> select count(*) from mysql.user where is_role = 'Y'; |
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation '=' |
Before 10.4 the query would work fine:
|
10.3 7d04ce6a |
MariaDB [test]> select table_collation from information_schema.tables where table_schema = 'mysql' and table_name = 'user'; |
+-------------------+ |
| table_collation |
|
+-------------------+ |
| latin1_swedish_ci |
|
+-------------------+ |
1 row in set (0.001 sec) |
|
|
MariaDB [test]> set names latin2; |
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [test]> select count(*) from mysql.user where is_role = 'Y'; |
+----------+ |
| count(*) | |
+----------+ |
| 0 |
|
+----------+ |
1 row in set (0.001 sec) |
Apparently it happens because coercibility of is_role column in mysql.user table is 2, while in mysql.user view it's 4, same as the literal's.
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-
- relates to
-
-
- Open
-
-
-
- Closed
-
-
-
- Closed
-