Search code examples
mysqlselectmariadbcase-insensitive

Mysql / Maria DB case (in)sensitive search

I am running 2 servers - Maria DB v10 on my Mac and MySQL v5.6 on Centos. Both of them have the same databases and behaviour is the same on both, so my problem is not related to OS or server version.

A table in database 1 is defined like

CREATE TABLE `issue_head` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `nme` longblob NOT NULL,
  `row_ts` bigint(20) NOT NULL,
  `created_user_id` bigint(20) NOT NULL,
  `assigned_user_id` bigint(20) DEFAULT NULL,
  `appID` bigint(20) NOT NULL,
  `severity` varchar(50) NOT NULL,
  `status` varchar(50) NOT NULL,
  `is_test_ind` int(11) NOT NULL DEFAULT 0,
  `price_est` decimal(15,2) DEFAULT NULL,
  `required_ts` bigint(20) DEFAULT NULL,
  `required_notif_ts` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id1` (`appID`,`status`,`row_ts`),
  KEY `id2` (`appID`,`row_ts`),
  KEY `id3` (`appID`,`assigned_user_id`,`row_ts`),
  KEY `id4` (`required_ts`,`required_notif_ts`)
) ENGINE=InnoDB AUTO_INCREMENT=1204 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_czech_ci;

A table in database 2 is defined like

CREATE TABLE `aftn` (
  `id` bigint(20) NOT NULL,
  `druh_id` bigint(20) NOT NULL,
  `out_ind` bigint(20) NOT NULL,
  `row_dt` datetime NOT NULL,
  `chng_dt` datetime NOT NULL,
  `ack_dt` datetime DEFAULT NULL,
  `mess_id` char(7) DEFAULT NULL,
  `subj` varchar(500) DEFAULT NULL,
  `msg` longtext DEFAULT NULL,
  `status_id` bigint(20) NOT NULL,
  `rel_obj_id` bigint(20) DEFAULT NULL,
  KEY `aftn_id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_czech_ci;

So, both of them have same charset and same collation. Unfortunately selects from database 1 are case sensitive although the collation is *_ci. Selects from database 2 are case insesitives. Using these examples

select * from issue_head where nme like 'test%' and appID = 23;

returns nothing, but in case I write it like this:

select * from issue_head where nme like CONVERT('test%' USING utf8mb3) COLLATE utf8mb3_czech_ci and appID = 23;

returns all rows regardles of case.

Select

select * from aftn where msg like 'gg%';

run on database 2 returns all rows regardless of case.

What is wrong with the database 1 and how can I make it case insesitive?

Solution

  • I can reproduce the issue. I'm testing with MySQL 8.0.32, which is what I have installed on my laptop.

    mysql> insert into issue_head set nme='TESTING', appid=23, row_ts = 23, created_user_id=23, severity='bad', status='on';
Query OK, 1 row affected (0.00 sec)

mysql> select * from issue_head where nme like 'test%' and appID = 23;
Empty set (0.00 sec)

    And I found this workaround:

    mysql> select * from issue_head where nme like 'test%' collate utf8mb3_czech_ci and appID = 23;
+------+------------------+--------+-----------------+------------------+-------+----------+--------+-------------+-----------+-------------+-------------------+
| id   | nme              | row_ts | created_user_id | assigned_user_id | appID | severity | status | is_test_ind | price_est | required_ts | required_notif_ts |
+------+------------------+--------+-----------------+------------------+-------+----------+--------+-------------+-----------+-------------+-------------------+
| 1204 | 0x54455354494E47 |     23 |              23 |             NULL |    23 | bad      | on     |           0 |      NULL |        NULL |              NULL |
+------+------------------+--------+-----------------+------------------+-------+----------+--------+-------------+-----------+-------------+-------------------+

    I see you defined the column nme as LONGBLOB. This is binary data with no character set or collation, so if you don't specify a collation, there's no way for it to use a case-insensitive collation.