SET NAMES '...' COLLATE '...' fails

[krzysztof-ksiazek]

Apparently ProxySQL does not support:
SET NAMES '...' COLLATE '...'
For example:

root@vagrant:~# mysql -h127.0.0.1 -usbtest -ppass -P6033 -e "SET NAMES utf8 COLLATE 'utf8_general_ci'"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1115 (42000) at line 1: Unknown character set 'utf8' or collation ''utf8_general_ci'

On the other hand SET NAMES only succeeds:

root@vagrant:~# mysql -h127.0.0.1 -usbtest -ppass -P6033 -e "SET NAMES 'utf8'"
mysql: [Warning] Using a password on the command line interface can be insecure.

When used without quotes it works too:

root@vagrant:~# mysql -h127.0.0.1 -usbtest -ppass -P6033 -e "SET NAMES utf8 COLLATE utf8_general_ci"
mysql: [Warning] Using a password on the command line interface can be insecure.

[kpettit]

This seems to be an issue for any ProxySQL 1.4.4 to 1.4.6 (current). Versions prior to 1.4.4 work fine. This new behaviour also breaks any python scripts that use mysql.connector.

I discovered it as I use a python script to configure proxysql and since the 1.4.4 release, ProxySQL returns the error: Error: 1115 (42000): Unknown character set 'utf8'' or collation ''utf8_general_ci' when connecting via Python mysql.connector:

Using this script:

#!/usr/bin/python

import sys
import mysql.connector


mysql_hostname=sys.argv[1]
mysql_port=sys.argv[2]
username=sys.argv[3]
passwd=sys.argv[4]

config = {
  'user': username,
  'password': passwd,
  'host': mysql_hostname,
  'port': mysql_port,
  'database': 'information_schema',
  'raise_on_warnings': True
}

# Connect with ProxySQL/MySQL Server
cnx = mysql.connector.connect(**config)

# Get cursor
cur = cnx.cursor(buffered=True)

query = ("select 'Connected!'")
cur.execute(query)

for (data) in cur:
  print str(data[0])
cnx.close()

You can see that the code in connection.py module does a "SET NAMES '%s' COLLATE '%s'" % (charset_name, collation_name) call in the _post_connection step...

[root@ip-10-xx-xx-xxx proxysql]# python test.py 10.xx.xx.xxx ...
Traceback (most recent call last):
File "test.py", line 22, in
cnx = mysql.connector.connect(**config)
File "/usr/lib/python2.7/site-packages/mysql/connector/init.py", line 98, in connect
return MySQLConnection(*args, **kwargs)
File "/usr/lib/python2.7/site-packages/mysql/connector/connection.py", line 118, in init
self.connect(**kwargs)
File "/usr/lib/python2.7/site-packages/mysql/connector/connection.py", line 383, in connect
self._post_connection()
File "/usr/lib/python2.7/site-packages/mysql/connector/connection.py", line 362, in _post_connection
self.set_charset_collation(charset=self._charset_id)
File "/usr/lib/python2.7/site-packages/mysql/connector/connection.py", line 973, in set_charset_collation
self._execute_query("SET NAMES '%s' COLLATE '%s'" % (charset_name, collation_name))
File "/usr/lib/python2.7/site-packages/mysql/connector/connection.py", line 1332, in _execute_query
self.cmd_query(query)
File "/usr/lib/python2.7/site-packages/mysql/connector/connection.py", line 635, in cmd_query
statement))
File "/usr/lib/python2.7/site-packages/mysql/connector/connection.py", line 553, in _handle_result
raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1115 (42000): Unknown character set 'utf8'' or collation ''utf8_general_ci'

[renecannao]

Fixed. Thank you for the report

[cwhsu1984]

I think there might be another parsing issue on time_zone, see the following commands:

user@host:~$ mysql -u proxysql_user -p -h 127.0.0.1 -P 6033 -e "set names utf8, time_zone='+08:00'"
Enter password: 
ERROR 1115 (42000) at line 1: Unknown character set: 'utf8, time_zone='+08:00'
user@host:~$ mysql -u proxysql_user -p -h 127.0.0.1 -P 6033 -e "set names utf8"
Enter password: 

When I set names with timezone, it shows error. However, if setting names alone, it works well.

[bargoud]

Hi,
I've got the error too (MySQL 5.7 with Group Replication enabled):

MySQL_Session.cpp:3815:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [ERROR] Unable to parse query. If correct, report it as a bug: SET TIME_ZONE = "+02:00", sql_mode=(SELECT REPLACE(@@sql_mode,"ONLY_FULL_GROUP_BY","")), NAMES utf8mb4 COLLATE utf8mb4_unicode_520_ci

[renecannao]

This specific issue is fixed