0

I got very wierd error when I query from tbl_class table.

COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4'

here is the query 

SELECT CONCAT(SUBSTRING_INDEX(ac.class_group_name, ';', 1), '/'),
 ac.class_upper_class_id INTO return_path,
 parent_id 
FROM tbl_class AS ac 
WHERE ac.class_id = category_id
and ac.class_org_group_code = org_group_code collate utf8_general_ci;

my.cnf is like

[mysqld]
character-set-server=utf8
collaction-server=utf8_general_ci

show variables like '%character%'
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

and column's collation is like 

+------------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+-----------------------+
| Field                  | Type         | Collation       | Null | Key | Default | Extra | Privileges                      | Comment               |
+------------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+-----------------------+
| class_id               | int(11)      | NULL            | NO   | PRI | 0       |       | select,insert,update,references |                       |
| class_kind             | varchar(1)   | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |                       |
| class_upper_class_id   | int(11)      | NULL            | YES  |     | NULL    |       | select,insert,update,references |                       |
| class_org_group_code   | varchar(10)  | utf8_general_ci | NO   | PRI |         |       | select,insert,update,references |                       |
| class_group_code       | varchar(50)  | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |                       |

and table's character set is like 

KEY `tbl_class_idx2` (`class_org_group_code`,`class_upper_class_id`,`class_kind`,`class_order_no`),
  KEY `tbl_class_idx3` (`class_org_group_code`,`class_group_code`),
  KEY `tbl_class_idx4` (`class_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

I restarted mysql, but same error is occured. And there is no utf8mb4 at all. why?

bucky2
  • 53
  • 1
  • 5
  • which exact mysql version? – danblack Mar 17 '20 at 07:25
  • sorry. it's a mariaDB server. Server: MariaDB Server version: 10.4.7-MariaDB MariaDB Server Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 – bucky2 Mar 18 '20 at 01:14
  • I had trouble replicating an [example](https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=00ad2edf0d8b0faa11d322307f962443) do you want to try there a bit more. If its something that isn't happening locally for you maybe an upgrade to 10.4.12 will fix it. Are you sure an explicit collate cast is actually doing anything in this query? – danblack Mar 18 '20 at 02:16
  • 1
    i've just removed ```collate utf8_general_ci``` in query, and it works! I appreciate your help :D – bucky2 Mar 19 '20 at 05:55
  • by removing that part you make string comparisons from case-insensitive to case sensitive, just for your info – John Nov 04 '20 at 01:44

0 Answers0