ProxySQL and Strict Mode

We have a 3-node cluster setup running MySQL 5.7.34-37.

We are using ProxySQL 2.4.4-44 to route traffic for the cluster.

We recently turned strict mode and innodb strict mode off on all nodes, but we are still seeing strict mode-type errors when calling certain stored procedures.

An example error we have been seeing:

Incorrect integer value: '' for column '$MyVar' at row 1

Adding to the odd behavior, so far, every time the error is thrown it is when making the stored procedure calls through the proxy. We have not been able to replicate the errors when executing those procedures directly on the cluster nodes.

Furthermore, as a test, we ran these stored procedures through a different proxy connected to the same cluster. This one is running ProxySQL version 2.0.17-16. So far, no issues.

To summarize. While there is no pattern to it, the newer ProxySQL instance is returning an error when calling certain stored procedures, even though strict mode & innodb strict mode are off on all nodes in the cluster.

Are there any other variables or configuration items we should be considering to resolve these errors we are seeing?

Hello @daa,
Stored procedures will restore the state of strict mode and sql_mode to whatever it was when the stored proc was created. If you need to modify strict mode or sql_mode, you will also need to drop the proc and recreate it.