Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

sql mode parsing issue #2008

Closed
ktugan opened this issue Apr 9, 2019 · 3 comments
Closed

sql mode parsing issue #2008

ktugan opened this issue Apr 9, 2019 · 3 comments

Comments

@ktugan
Copy link

ktugan commented Apr 9, 2019

The parser has an issue with following query:

SET  @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'),  @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483;

With following full error:

   (1.7ms)  SET  @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'),  @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1: SELECT table_name FROM information_schema.tables WHERE table_schema = database()
from /usr/local/bundle/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in `_query'
Caused by Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

It is very similar to the problem described in and caused in a ruby on rails application: #1904

My builder:

FROM debian:stretch as builder

WORKDIR /tmp

RUN apt-get update && \
    apt-get install automake bzip2 cmake make g++ gcc git openssl libssl-dev patch zlib1g-dev -y && \
    git clone https://github.com/sysown/proxysql.git && \
    cd proxysql && \
    git checkout v2.0.4

RUN cd proxysql && NOJEMALLOC=1 make -j 6 default

Additional information:

ProxySQL version: v2.0.4
MySQL: 5.7

ruby gem: mysql2 (0.5.2)
ruby gem: rails (5.2.2)
@ktugan
Copy link
Author

ktugan commented Apr 9, 2019

I have done more investigations and the error seems to be happening not on the SET sql mode command but on every command afterwards:

mysql> SELECT table_name FROM information_schema.tables WHERE table_schema = database();
+-------------------+
| table_name        |
+-------------------+
| TOGGLZ            |
| togglz_extensions |
+-------------------+
2 rows in set (0.09 sec)

mysql> select * from TOGGLZ limit 1;
+-------------------------------+-----------------+-------------+-----------------+
| FEATURE_NAME                  | FEATURE_ENABLED | STRATEGY_ID | STRATEGY_PARAMS |
+-------------------------------+-----------------+-------------+-----------------+
|
SOL_BO_NO_OF_PASSENGER_FIELD |               1 | NULL        | rate 100        |
+-------------------------------+-----------------+-------------+-----------------+
1 row in set (0.09 sec)

mysql> SET  @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'),  @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT table_name FROM information_schema.tables WHERE table_schema = database();
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> select * from TOGGLZ limit 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql>

When doing the same queries on the db itself, it works flawlessly.

@ktugan
Copy link
Author

ktugan commented Apr 9, 2019

Sorry about the segmented responses, this will make it hopefully replicatable. I tried all SET commands separately and pinned it down to the specific commands making problems.

SET @@SESSION.sql_auto_is_null = 0;
# Or
SET @@SESSION.wait_timeout = 2147483;

Either of them might lead to proxysql crashing and the client losing the connection:

mysql> SET @@SESSION.wait_timeout = 2147483; SELECT table_name FROM information_schema.tables WHERE table_schema = database();
ERROR 2013 (HY000): Lost connection to MySQL server during query
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0
ERROR:
Can't connect to the server

mysql>

proxysql log -- I only got this message once:

proxysql_1  | 2019-04-09 19:10:27 MySQL_Session.cpp:1749:handler_again___status_SETTING_SQL_MODE(): [WARNING] Error while setting SQL_MODE: 1064, You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

otherwise: full proxysql logs:

proxysql_1  | S3_PROXYSQL_CONFIG_PATH is set to 's3://<bucket>/proxysql.cnf'
download: s3://<bucket>/proxysql.cnf to ../etc/proxysql.cnf   e(s) remaining
proxysql_1  | S3_FLUENTD_CONFIG_PATH is set to 's3://<bucket>/td-agent-s3-credfile.conf'
download: s3://<bucket>/td-agent-s3-credfile.conf to ../etc/td-agent/td-agent.conf
proxysql_1  | 2019-04-09 20:01:55 [INFO] Using config file /etc/proxysql.cnf
proxysql_1  | 2019-04-09 20:01:55 [INFO] SSL keys/certificates found in datadir (/opt/proxysql): loading them.
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg="Starting node_exporter (version=0.17.0, branch=HEAD, revision=f6f6194a436b9a63d0439abc585c76b19a206b21)" source="node_exporter.go:82"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg="Build context (go=go1.11.2, user=root@322511e06ced, date=20181130-15:51:33)" source="node_exporter.go:83"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg="Enabled collectors:" source="node_exporter.go:90"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - arp" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - bcache" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - bonding" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - conntrack" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - cpu" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - diskstats" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg="Starting proxysql_exporter 1.1.0 for admin:admin@tcp(127.0.0.1:6032)/" source="proxysql_exporter.go:65"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - edac" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - entropy" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - filefd" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - filesystem" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - hwmon" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - infiniband" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - ipvs" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - loadavg" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - mdadm" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - meminfo" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - netclass" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - netdev" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - netstat" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - nfs" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - nfsd" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - sockstat" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - stat" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - textfile" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - time" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=error msg="Error opening connection to ProxySQL: dial tcp 127.0.0.1:6032: getsockopt: connection refused" source="exporter.go:146"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg="Starting HTTP server for http://:8080/metrics ..." source="server.go:121"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - timex" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - uname" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - vmstat" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - xfs" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg=" - zfs" source="node_exporter.go:97"
proxysql_1  | time="2019-04-09T20:01:55Z" level=info msg="Listening on :8081" source="node_exporter.go:111"
proxysql_1  | 2019-04-09 20:01:55 [INFO] ProxySQL version 2.0.4-38-g3fdf4cf3
proxysql_1  | 2019-04-09 20:01:55 [INFO] Detected OS: Linux f351352b0f4a 4.9.93-linuxkit-aufs #1 SMP Wed Jun 6 16:55:56 UTC 2018 x86_64
proxysql_1  | 2019-04-09 20:01:55 [INFO] ProxySQL SHA1 checksum: 82b00ae1e8192b3855c4658081d2bf3ecd4ab07e
proxysql_1  | Standard ProxySQL Cluster rev. 0.4.0906 -- ProxySQL_Cluster.cpp -- Tue Apr  9 10:31:33 2019
proxysql_1  | Standard ProxySQL Statistics rev. 1.4.1027 -- ProxySQL_Statistics.cpp -- Tue Apr  9 10:31:33 2019
proxysql_1  | Standard ProxySQL HTTP Server Handler rev. 1.4.1031 -- ProxySQL_HTTP_Server.cpp -- Tue Apr  9 10:31:33 2019
proxysql_1  | 2019-04-09 20:01:55 ProxySQL_Admin.cpp:4168:flush_admin_variables___database_to_runtime(): [WARNING] Impossible to set variable stats_system_memory with value "60". Resetting to current "60".
proxysql_1  | 2019-04-09 20:01:55 ProxySQL_Admin.cpp:4168:flush_admin_variables___database_to_runtime(): [WARNING] Impossible to set variable stats_system_memory with value "60". Resetting to current "60".
proxysql_1  | 2019-04-09 20:01:55 ProxySQL_Admin.cpp:4248:flush_mysql_variables___database_to_runtime(): [WARNING] Impossible to set variable server_capabilities with value "569867". Resetting to current "569899".
proxysql_1  | Standard ProxySQL Admin rev. 0.2.0902 -- ProxySQL_Admin.cpp -- Tue Apr  9 10:31:33 2019
proxysql_1  | 2019-04-09 20:01:55 [INFO] ProxySQL SHA1 checksum: 82b00ae1e8192b3855c4658081d2bf3ecd4ab07e
proxysql_1  | Standard MySQL Threads Handler rev. 0.2.0902 -- MySQL_Thread.cpp -- Tue Apr  9 10:31:33 2019
proxysql_1  | Standard MySQL Authentication rev. 0.2.0902 -- MySQL_Authentication.cpp -- Tue Apr  9 10:31:33 2019
proxysql_1  | 2019-04-09 20:01:55 [INFO] Dumping mysql_servers_incoming
proxysql_1  | +--------------+-----------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
proxysql_1  | | hostgroup_id | hostname                                      | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
proxysql_1  | +--------------+-----------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
proxysql_1  | | 1            | <HOST> | 3306 | 0         | 1      | 0      | 0           | 1000            | 0                   | 1       | 0              |         |
proxysql_1  | +--------------+-----------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
proxysql_1  | 2019-04-09 20:01:55 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
proxysql_1  | +-------------+--------------+----------+------+
proxysql_1  | | mem_pointer | hostgroup_id | hostname | port |
proxysql_1  | +-------------+--------------+----------+------+
proxysql_1  | +-------------+--------------+----------+------+
proxysql_1  | 2019-04-09 20:01:55 [INFO] Dumping mysql_servers JOIN mysql_servers_incoming
proxysql_1  | +--------------+-----------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
proxysql_1  | | hostgroup_id | hostname                                      | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
proxysql_1  | +--------------+-----------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
proxysql_1  | | 1            | <HOST> | 3306 | 0         | 1      | 0      | 0           | 1000            | 0                   | 1       | 0              |         | 0           | 0         | 1      | 0      | 0           | 1000            | 0                   | 1       | 0              |         |
proxysql_1  | +--------------+-----------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
proxysql_1  | 2019-04-09 20:01:55 [INFO] Creating new server in HG 1 : <HOST>:3306 , gtid_port=0, weight=1, status=0
proxysql_1  | 2019-04-09 20:01:55 [INFO] New mysql_group_replication_hostgroups table
proxysql_1  | 2019-04-09 20:01:55 [INFO] New mysql_galera_hostgroups table
proxysql_1  | Standard Query Processor rev. 0.4.1031 -- Query_Processor.cpp -- Tue Apr  9 10:31:33 2019
proxysql_1  | 2019-04-09 20:01:55 [INFO] Starting new mysql log file /opt/proxysql/queries.log.00000006
proxysql_1  | In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Tue Apr  9 10:31:33 2019
proxysql_1  | Standard MySQL Monitor (StdMyMon) rev. 2.0.1226 -- MySQL_Monitor.cpp -- Tue Apr  9 10:31:33 2019
proxysql_1  | /entrypoint.sh: line 30:    26 Segmentation fault      proxysql -f

renecannao added a commit that referenced this issue Apr 19, 2019
@renecannao
Better parsing for SET statements #2008
07fec62
@renecannao
Copy link
Contributor

Thank you for the report.
This specific query was already marked in setparsertest.cpp as currently "not supported by SetParser".
Now it is fixed in 2.0.4 .
Closing

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@ktugan @renecannao