Inconsistent sql_mode handling with new connections to ProxySQL

[leeparayno]

I have ProxySQL sitting in front of a Master with Two Slaves (all servers are MySQL 5.7.12-5-log).

I have two hostgroups defined with hostgroup1 (write) and hostgroup2 (read).

Currently I have the hostgroup2 defined with both slaves with a higher priority and the master as a lower priority read server. As of now, both slaves have broken replication, leaving only the master in the read pool (full disclosure of existing condition).

This is what I'm seeing regarding the sql_mode, when connecting through ProxySQL to the backends:

mysql> select @@hostname;
+----------------------------+
| @@hostname |
+----------------------------+
| dev1 |
+----------------------------+
1 row in set (0.02 sec)

mysql> show variables like '%sql_mode%';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

This is the sql_mode that is the default coming from the database instances (backends). When I manually attempt to set the sql_mode on the connection, it appears to stick.

mysql> set sql_mode = 'STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> show variables like '%sql_mode%';
+---------------+---------------------+
| Variable_name | Value |
+---------------+---------------------+
| sql_mode | STRICT_TRANS_TABLES |
+---------------+---------------------+
1 row in set (0.00 sec)

mysql> exit

Next connection to ProxySQL (presumably reuses the existing connection from the pool which had it's sql_mode changed):

mysql> select @@hostname;
+----------------------------+
| @@hostname |
+----------------------------+
| dev1 |
+----------------------------+
1 row in set (0.03 sec)

mysql> show variables like 'sql_mode';
+---------------+---------------------+
| Variable_name | Value |
+---------------+---------------------+
| sql_mode | STRICT_TRANS_TABLES |
+---------------+---------------------+
1 row in set (0.00 sec)

I would assume a new connection to ProxySQL to assume to have the original default settings from the database instances.

New SSH window, create another session while the second session is still open, thus creating a new connection from ProxySQL to the backends:

mysql> select @@hostname;
+----------------------------+
| @@hostname |
+----------------------------+
| dev1 |
+----------------------------+
1 row in set (0.02 sec)

mysql> show variables like '%sql_mode%';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

Since this is a new connection, it appears to pull the settings from the database instances.

I haven't found any information in the docs regarding what is expected to be the default behavior.

However, IMO, I would think the expectation of the application would be to have the settings defaulted from the database defaults upon creating/reusing a connection from the pool.

When a user makes changes to the sql_mode for a connection, I would expect that ProxySQL would only have those setting be transient, in that they would not remain on the connection when it is returned to ProxySQL's database connection pool.

Let me know what you think, as it appears we're seeing issues with this current inconsistency of sql_mode handling with our application.

[renecannao]

Hi Lee,
From a first read, this behavior seems incorrect.
Which version of ProxySQL are you using?

Please also attach the output of SELECT * FROM mysql_query_rules and let me know if you are using transaction_persistent or not.

I will look into this tomorrow.
Thanks

[leeparayno]

v1.3.3

*************************** 3. row ***************************
rule_id: 4
active: 1
username: dbuser
schemaname: master
flagIN: 0
client_addr: NULL
proxy_addr: NULL
proxy_port: NULL
digest: NULL
match_digest: NULL
match_pattern: ^SELECT.*FOR UPDATE$
negate_match_pattern: 0
flagOUT: NULL
replace_pattern: NULL
destination_hostgroup: 1
cache_ttl: NULL
reconnect: NULL
timeout: NULL
retries: NULL
delay: NULL
mirror_flagOUT: NULL
mirror_hostgroup: NULL
error_msg: NULL
log: NULL
apply: 1
comment: NULL
*************************** 4. row ***************************
rule_id: 5
active: 1
username: dbuser
schemaname: master
flagIN: 0
client_addr: NULL
proxy_addr: NULL
proxy_port: NULL
digest: NULL
match_digest: NULL
match_pattern: ^SELECT
negate_match_pattern: 0
flagOUT: NULL
replace_pattern: NULL
destination_hostgroup: 2
cache_ttl: NULL
reconnect: NULL
timeout: NULL
retries: NULL
delay: NULL
mirror_flagOUT: NULL
mirror_hostgroup: NULL
error_msg: NULL
log: NULL
apply: 1
comment: NULL

We are using transaction_persistent for the mysql user.

[renecannao]

Hi Lee,
Thank you for the output.

1.3.x doesn't track sql_mode , so what you describe is expected in this version.
Version 1.4 tracks sql_mode, and a similar issue shouldn't happen.

Many applications do not use multiple sql_mode, therefore it is often possible to just set the desired sql_mode in MySQL server, and this missing feature in 1.3 won't be a problem.
Another option is to disable multiplexing globally. In the Admin interface:

SET mysql-multiplexing='false';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Note that disabling multiplexing doesn't disable routing, and sql_mode will only be set to one of the connections.

Also note that, according to the output of mysql_query_rules, SELECT @@hostname is sent to HG2 , while show variable is sent to HG1 : two different connections

[leeparayno]

A little context might be required here. Our application is a Ruby on Rails app, which in newer versions of Rails sets appears to append or replace specific variables in sql_mode to adjust the handling of data truncating/handling.

https://github.com/rails/rails/blob/2809f38ab8d75dabb5b053ac14f5e78a2ad802fa/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb#L865-L916

We use an older version of Rails however, and it appears that the same code is just replacing the sql_mode on the connection it gets to the database.

https://github.com/rails/rails/blob/v4.2.7.1/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb#L858-L907

We ran a load test with the application running through ProxySQL to get to the backends, it appears that sql_mode is only set for the first connection, as all the subsequent connections are seeing the sql_mode from the database backends. Unfortunately for about 15% of the transactions we are sending we are getting errors regarding sql_mode. Although setting the sql_mode explicitly on the MySQL backends is possible, we want to try to move in that direction as much as possible.

It appears that the additional connections requested by our version of Rails isn't resetting the sql_mode over again.

@renecannao Is it possible to set a sql_mode to be default on the connections coming from the backend?

[renecannao]

Hi Lee,
If I understood your question correctly, the reply is the same as in #509 (comment) :
You can configure mysql-init_connect this way:

UPDATE global_variables SET variable_value='SET sql_mode="STRICT_TRANS_TABLES"'
WHERE variable_name='mysql-init_connect';
SAVE MYSQL VARIABLES TO DISK;
LOAD MYSQL VARIABLES TO RUNTIME;

If I misunderstood the question please let me know.
Thanks

[leeparayno]

Do you have any more information on multi-plexing? I thought that was what referred to as the query routing between hostgroups and/or redirecting connections to stay on specific host group for transaction persistent operations. Lee Parayno

…

On Feb 17, 2017, at 2:29 PM, René Cannaò ***@***.***> wrote: Hi Lee, If I understood your question correctly, the reply is the same as in #509 (comment) : You can configure mysql-init_connect this way: UPDATE global_variables SET variable_value='SET sql_mode="STRICT_TRANS_TABLES"' WHERE variable_name='mysql-init_connect'; SAVE MYSQL VARIABLES TO DISK; LOAD MYSQL VARIABLES TO RUNTIME; If I misunderstood the question please let me know. Thanks — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or mute the thread.

[arthurchui]

We have a similar problem running the Rails5 app. At the boot time of the Rails5 app, it runs:

SET @@SESSION.sql_mode = '', @@SESSION.auto_increment_increment = 2, @@SESSION.auto_increment_offset = 2, @@SESSION.max_statement_time = 300000, @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483;

As we observe the general mysql log, the command does not reach the backend. The backend receives the command when we inspect the value of sql_mode.

190129 19:52:22	 4999 Query	SET SQL_MODE=''
		 4999 Query	show variables like 'sql_mode'

We are not sure why there is a delay. Yet the rest of the SET command is dropped silently. We also attempted different values of mysql-default_sql_mode but no luck.

In Rails4, it runs a similar command at boot time but the value of sql_mode happens at the end of the statement:

SET @@SESSION.auto_increment_increment = 2, @@SESSION.auto_increment_offset = 2, @@SESSION.max_statement_time = 300000, @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483, @@SESSION.sql_mode = '';

Guess what? ProxySQL relays the statement to the backend. All the values are update properly.

At the moment, we added a monkey patch to tweak the sql statement. Yet, if we can possibly fix the bug, we don't need the workaround.

[arthurchui]

Note that the behaviour is the same in version 1.4.9 and 2.0.1.

[tomwganem]

We are running into the bug too. We are using resque and our workers keep failing with the following error.

Mysql2::Error: Max connect timeout reached while reaching hostgroup 10 after 10000ms: SET NAMES utf8mb4, @@SESSION.sql_mode = 'TRADITIONAL', @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483

I've modified the global sql_mode on the backends to be "TRADITIONAL", but no luck.

[krunde-nextpoint]

We are hitting the same problem too now.

[hanikesn]

Let me add that I was also quite surprised to see a modified sql_mode persist across connections and only reset after explicitly unsetting it with proxysql 2.7.1.

IMHO it's probably worth to add it to the exception list for multiplexing so it's safe by default.

[renecannao]

Hi @hanikesn .
You are replying to a ticket that is 4 years old and should have been closed since.
2.0.9 fixes the issue described in this issue, and I will close this now.

What you describe shouldn't happen, unless proxysql is not able to parse the SET statement that changed the sql_mode .
For example, if you are using prepared statements to execute SET statement.
If this is the case, please stop using prepared statements for SET statement: it is very inefficient (no matter proxysql).

If you are able to have a reproducible test case, please feel free to open a new issue.

[hanikesn]

If you are able to have a reproducible test case, please feel free to open a new issue.

Sorry for the noise. Turns out both mysql servers had a different sql_mode configured

[renecannao]

Thank you, that is reassuring!