PDO MySQL (MariaDB) query with UTF8 column is not case insensitive though default collation is used

Question

I have a query like this with a UTF8 name:

select column from table where name = 'something'

When I try this from my mysql client (HeidiSQL) then the result is returned even if there is a case difference between name and something. This is good.

Howevever, when I run the same query from PHP with PDO then the query is case-sensitive for some reason.

The table uses the default collation utf8_general_ci and in the PDO connection I only set charset=utf8.

Shouldn't then the default collation for the PDO connection also be case insensitive?

The query. Like it returns the result for name = 'Something', but not for name = 'something'. From the sql client both query works, from PDO only the one with the proper case, though for UTF8 case insesitive collation is the default. — Tom, Jul 09 '18 at 11:44
PDO could have overwritten the default collation (or actually, it is possible that heidisql overwrote the default case sensitive option). If you want to rely on the case sensitivity (or insensevity), then you should explicitly provide the collation name when you connect to the MySQL server. — Shadow, Jul 09 '18 at 11:52
It's possible, though other answers suggest utf8_general_ci is the default for pdo too, that's why I'm perplexed: "If you specify UTF-8 you are working with the default collation of utf8_general_ci, unless your db table or field uses something different." https://stackoverflow.com/questions/25807202/how-to-specify-collation-with-pdo-without-set-names/25807320#25807320 — Tom, Jul 09 '18 at 11:56
Unless the mysqli.ini also contains settings for the connection encoding and collation. Or the query overwrites the collation. — Shadow, Jul 09 '18 at 11:59

score 0 · Answer 1 · answered Jul 10 '18 at 14:59

From PHP I printed the collation variables:

show variables where variable_name like '%coll%';

and it said:

array(3) {
  [0]=>
  array(2) {
    ["Variable_name"]=>
    string(20) "collation_connection"
    ["Value"]=>
    string(15) "utf8_general_ci"
  }
  [1]=>
  array(2) {
    ["Variable_name"]=>
    string(18) "collation_database"
    ["Value"]=>
    string(15) "utf8_general_ci"
  }
  [2]=>
  array(2) {
    ["Variable_name"]=>
    string(16) "collation_server"
    ["Value"]=>
    string(15) "latin1_swedish_ci"
  }
}

I'm not sure why the server collation latin1_swedish_ci caused a problem when the connection and database collations were utf8_general_ci, but when I changed the server collation too to utf8_general_ci:

set global collation_server = "utf8_general_ci";

Then the query started working case insensitively from PHP too.

PDO MySQL (MariaDB) query with UTF8 column is not case insensitive though default collation is used

1 Answers1