Track SET NAMES ? COLLATE ?

[renecannao]

Verify what happens when a client sends commands like SET NAMES ? COLLATE ?

[bserapiokitmaker]

Hi, we can't use ProxySQL with Laravel. When connect use Collate and ProxySQL return error "Command not Supported"

[renecannao]

Hi.
The issue you describe shouldn't be related to COLLATE , but the fact that ProxySQL doesn't support prepared statements (yet).
It seems that it is possible to not use prepared statements with Laverel: I would give it a try just to validate that this is the issue, but I would wait ProxySQL to fully support prepared statements.
ProxySQL should support prepared statements in the near future.

[bserapiokitmaker]

Thanks for your answer. I'll wait till Proxysql support it .

[rdbms-at-twitter]

Hi René,

I would like to know if there is any update about this issue.
ProxySQL is very useful, so we would like to use it; however, garbled characters issue with Laravel still happen with latest ProxySQL.....

Additional Note:
When describing dsn information using PDO classes instead of Laravel, the following behaviors occurred.

charset utf8 / utf8mb4
$ dsn = 'mysql: host = test_db; dbname = test; charset = utf8mb4';
$ dsn = 'mysql: host = test_db; dbname = test; charset = utf8';
-> Always Working Fine

charset ujis
$ dsn = 'mysql: host = test_db; dbname = test; charset = ujis';
-> Always decompose

Thank you for developing nice load balancing software as open source.

Best Regards
Shinya

[renecannao]

Hi Shinya,

Thank your for the feedback.
Charset ujis should work, because it is supported:

Admin> select * from mysql_collations where charset='ujis';
+----+------------------+---------+---------+
| Id | Collation        | Charset | Default |
+----+------------------+---------+---------+
| 12 | ujis_japanese_ci | ujis    | Yes     |
| 91 | ujis_bin         | ujis    |         |
+----+------------------+---------+---------+
2 rows in set (0,00 sec)

Although I am not familiar with ujis, and I don't have a reproducible test case for it not working.
Do you have a simple test case I can use to reproduce it?

Thanks

[rdbms-at-twitter]

Hi René,

Thank you for your support.
I'm have been reconfirming about ujis issue to dev members for making sure about detail procedure, so please wait for detail of the test case.

By the way, do you have any idea about following behavior?

1. Connect Through the ProxySQL
   -bash-4.2$ mysql -u proxysql_user -ppassw0rd -h 127.0.0.1 -P 6033 -e "select @@character_set_client, @@character_set_connection, @@character_set_server, @@character_set_database limit 1"
   mysql: [Warning] Using a password on the command line interface can be insecure.
   +------------------------+----------------------------+------------------------+--------------------------+
   | @@character_set_client | @@character_set_connection | @@character_set_server | @@character_set_database |
   +------------------------+----------------------------+------------------------+--------------------------+
   | utf8mb4 | utf8mb4 | utf8mb4 | utf8 |
   +------------------------+----------------------------+------------------------+--------------------------+

2. Connect Directly to the MySQL Instance
   -bash-4.2$ mysql -u proxysql_user -ppassw0rd -S/home/mysql/gr/mysql1/my.sock -e "select @@character_set_client, @@character_set_connection, @@character_set_server, @@character_set_database"
   mysql: [Warning] Using a password on the command line interface can be insecure.
   +------------------------+----------------------------+------------------------+--------------------------+
   | @@character_set_client | @@character_set_connection | @@character_set_server | @@character_set_database |
   +------------------------+----------------------------+------------------------+--------------------------+
   | utf8mb4 | utf8mb4 | utf8mb4 | utf8mb4 |
   +------------------------+----------------------------+------------------------+--------------------------+
   -bash-4.2$

-bash-4.2$ mysql -u proxysql_user -ppassw0rd -h 127.0.0.1 -P 6033 -e "show variables like '%char%'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------------+------------------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------------------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-advanced-5.7.18-linux-glibc2.5-x86_64/share/charsets/ |
+--------------------------+------------------------------------------------------------------------+

There is three servers under proxy; however, those three servers are configured same.

Best Regards
Shinya

[renecannao]

@rdbms-at-twitter : I forgot to answer here.
ProxySQL uses utf8 as default charset (mysql-default_charset).
You can change it with:

SET mysql-default_charset='utf8mb4';
SAVE MYSQL VARIABLES TO DISK;
LOAD MYSQL VARIABLES TO RUNTIME;

[rdbms-at-twitter]

Hi René,

Thank you for your support. I tried it.
It seems "set names" doesn't work with ProxySQL.
If I specify "charset=utf8mb4" in connection string, it is working fine with ProxySQL.

Admin>select * from global_variables where variable_name like 'mysql-default%';
+------------------------------+--------------------+
| variable_name | variable_value |
+------------------------------+--------------------+
| mysql-default_charset | utf8mb4 |
| mysql-default_max_latency_ms | 1000 |
| mysql-default_reconnect | true |
| mysql-default_sql_mode | |
| mysql-default_time_zone | SYSTEM |
| mysql-default_query_delay | 0 |
| mysql-default_query_timeout | 36000000 |
| mysql-default_schema | information_schema |
+------------------------------+--------------------+
8 rows in set (0.00 sec)

Confirmation 1 (With ProxySQL = can't display Japanese)

[root@replications gr]# php query_with_proxy_char.php
??? latin1 latin1
TEST latin1 latin1
??? latin1 latin1
TEST latin1 latin1
??? latin1 latin1
TEST latin1 latin1
^C

`<?php

try {
$dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1;port=6033','proxysql_user','passw0rd');
} catch (PDOException $e) {
exit('データベース接続失敗。'.$e->getMessage());
}

$sql = "set names 'utf8mb4' collate 'utf8mb4_general_ci'";
$stmt = $dbh->prepare($sql);
$stmt->execute();

for($i=0;$i<10;$i++){
$sql = "select *,@@session.character_set_client,@@session.character_set_connection from test";
$stmt = $dbh->query($sql);
while ($row = $stmt->fetch()) {
echo $row[1],"\t" ,$row[2],"\t" ,$row[3] . "\n";
}
sleep(1);
}
$dbh = null;
?>`

Confirmation 2 (Direct Connect to the MySQL. Can display Japanese.)

[root@replications gr]# php query_without_proxy_char.php
てすと utf8mb4 utf8mb4
TEST utf8mb4 utf8mb4
てすと utf8mb4 utf8mb4
TEST utf8mb4 utf8mb4
てすと utf8mb4 utf8mb4
TEST utf8mb4 utf8mb4
てすと utf8mb4 utf8mb4
TEST utf8mb4 utf8mb4
^C

`<?php

try {
$dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1;port=63301','proxysql_user','passw0rd');
} catch (PDOException $e) {
exit('データベース接続失敗。'.$e->getMessage());
}

$sql = "set names 'utf8mb4' collate 'utf8mb4_general_ci'";
$stmt = $dbh->prepare($sql);
$stmt->execute();

for($i=0;$i<10;$i++){
$sql = "select *,@@session.character_set_client,@@session.character_set_connection from test";
$stmt = $dbh->query($sql);
while ($row = $stmt->fetch()) {
echo $row[1],"\t" ,$row[2],"\t" ,$row[3] . "\n";
}
sleep(1);
}
$dbh = null;
?>`

Confirmation 3 (With ProxySQL. Can Display Japanese.)

[root@replications gr]# php query_char.php
てすと utf8mb4 utf8mb4
TEST utf8mb4 utf8mb4
てすと utf8mb4 utf8mb4
TEST utf8mb4 utf8mb4
てすと utf8mb4 utf8mb4
TEST utf8mb4 utf8mb4

`<?php

try {
$dbh = new PDO('mysql:dbname=testdb;charset=utf8mb4;host=127.0.0.1;port=6033','proxysql_user','passw0rd');
} catch (PDOException $e) {
exit('データベース接続失敗。'.$e->getMessage());
}

$sql = "set names 'utf8mb4' collate 'utf8mb4_general_ci'";
$stmt = $dbh->prepare($sql);
$stmt->execute();

for($i=0;$i<10;$i++){
$sql = "select *,@@session.character_set_client,@@session.character_set_connection from test";
$stmt = $dbh->query($sql);
while ($row = $stmt->fetch()) {
echo $row[1],"\t" ,$row[2],"\t" ,$row[3] . "\n";
}
sleep(1);
}
$dbh = null;
?>`

Best Regards
Shinya

[xenuser]

I can confirm that there is an issue. I run ProxySQL for different PHP apps, some of them use UTF8, some of them Latin1. One specific PHP app (Icinga2, based on Zend..) uses SET statements.

However, ProxySQL doesn't seem to consider them. As a consequence, the PHP app throws errors, such as:

SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'latin1_general_ci' is not valid for CHARACTER SET 'utf8', query was: SELECT so.name1 AS host_name, h.display_name COLLATE latin1_general_ci AS ......

Is there any chance that this gets resolved soon?
Please let me know if you need any more information.

[7c]

does proxysql send the 'SET NAMES..' to backend or does it send "OK" and manages this internally? In my case proxysql 1.4.5-1.1 with ubuntu16 default installation does send this to master.

interface: eth0 (172.31.16.0/255.255.240.0)
filter: (ip or ip6) and ( port 3306 and src 52.78.111.x )
#
T 52.78.111.x:60352 -> 172.31.26.66:3306 [AP]
  '....SHOW GLOBAL VARIABLES LIKE 'read_only'
##
T 52.78.111.x:60352 -> 172.31.26.66:3306 [AP]
  '....SHOW GLOBAL VARIABLES LIKE 'read_only'
##
T 52.78.111.x:60352 -> 172.31.26.66:3306 [AP]
  '....SHOW GLOBAL VARIABLES LIKE 'read_only'
##
T 52.78.111.x:60352 -> 172.31.26.66:3306 [AP]
  '....SHOW GLOBAL VARIABLES LIKE 'read_only'
##
T 52.78.111.x:43406 -> 172.31.26.66:3306 [AP]
  ..... SET NAMES utf8mb4
##
T 52.78.111.x:60352 -> 172.31.26.66:3306 [AP]
  '....SHOW GLOBAL VARIABLES LIKE 'read_only'
##
T 52.78.111.x:60352 -> 172.31.26.66:3306 [AP]
  .....
##
T 52.78.111.x:60352 -> 172.31.26.66:3306 [AP]
  '....SHOW GLOBAL VARIABLES LIKE 'read_only'
##
T 52.78.111.x:60352 -> 172.31.26.66:3306 [AP]
  '....SHOW GLOBAL VARIABLES LIKE 'read_only'
##
T 52.78.111.x:60352 -> 172.31.26.66:3306 [AP]
  '....SHOW GLOBAL VARIABLES LIKE 'read_only'
##
T 52.78.111.x:60352 -> 172.31.26.66:3306 [AP]
  '....SHOW GLOBAL VARIABLES LIKE 'read_only'
##
T 52.78.111.x:43936 -> 172.31.26.66:3306 [AP]
  ..... SET NAMES utf8mb4
##
T 52.78.111.x:60352 -> 172.31.26.66:3306 [AP]
  '....SHOW GLOBAL VARIABLES LIKE 'read_only'
##
T 52.78.111.x:43936 -> 172.31.26.66:3306 [AP]
  ..... SET NAMES utf8mb4
##
T 52.78.111.x:43936 -> 172.31.26.66:3306 [AP]
  ..... SET NAMES utf8mb4
##
T 52.78.111.x:60352 -> 172.31.26.66:3306 [AP]
  '....SHOW GLOBAL VARIABLES LIKE 'read_only'

I do have read-write splitting as

{
		rule_id=1
		active=1
		match_pattern="^SELECT .* FOR UPDATE\$"
		destination_hostgroup=1
		apply=1
	},
	{
		rule_id=2
		active=1
		match_pattern="^SELECT"
		destination_hostgroup=2
		apply=1
		cache_ttl=10000
	},

i do have 99.% select operations but the SET NAME will be sent to master, this keeps master kind of busy too.

I have tried to add

insert into mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply) values (4,1,'^SET NAMES utf8mb4',2,1);

but nothing has changed, strangewise the rules are ignored and queries are sent to the master

thanks for this great product

[renecannao]

This is implemented.
Closing