ProxySQL not working with our CMS (Jahia)

[nielsalkema]

Our setup:

RHEL 8 : ProxySQL 2.0.6 RHEL 7 : MySQL 5.7.27 Master RHEL 7 : MySQL 5.7.27 Slave

The CMS solution we use (https://github.com/Jahia) is not working with ProxySQL 2.0.6.

During writes for his internal journalisation we get the following errors in the ProySQL log

2019-08-28 15:04:32 MySQL_Session.cpp:3739:handler(): [WARNING] Error during query on (10,tstmysql0.domain.com,3306): 1062, Duplicate entry '22980955' for key 'JR_J_JOURNAL_IDX' 2019-08-28 15:04:33 MySQL_Session.cpp:3739:handler(): [WARNING] Error during query on (10,tstmysql0.domain.com,3306): 1062, Duplicate entry '22980971' for key 'JR_J_JOURNAL_IDX'

I have tried the following settings

1. mysql-autocommit_false_not_reusable=true
2. mysql-forward_autocommit=true
3. mysql-server_version=5.7.27
4. Set the user to : transaction_persistent: 1

But none of them solved this problem.

[nielsalkema]

It looks like the option : fast_forward: 1 solved my problem.

[renecannao]

Can you confirm this behaviour with older version, for example 2.0.4 ? We are aware of a bug related to autocommit affecting 2.0.5 and 2.0.6 , fixed in 2.0.7 .

[nielsalkema]

Can you confirm this behaviour with older version, for example 2.0.4 ? We are aware of a bug related to autocommit affecting 2.0.5 and 2.0.6 , fixed in 2.0.7 .

I have tested it with 2.0.7 and we have the same error

2019-10-07 11:54:22 MySQL_Session.cpp:4153:handler(): [WARNING] Error during query on (10,tstmysql0.domain.com,3306): 1062, Duplicate entry '23321832' for key 'JR_J_JOURNAL_IDX'

[umagmrit]

@renecannao Facing same issue with 2.0.10 also. Any work around to fix this ?

[umagmrit]

@NielsA If you set fast_forward: 1, it will disable query routing. It will not be a good solution.

[jamalawd]

@renecannao Facing a similar issue of duplicate entry with 2.0.10 when multiplexing is enabled. We have different applications using several ORMs, one of them is Sequelize a node.js library where it sends the insert queries as follows:

INSERT INTO `table1` (`id`) VALUES (DEFAULT);

It inserts a row in the database with id = 0 and then it always throws 1062, Duplicate entry '0' for key. Even if you try to run the above query in any DBMS, you will get the same error. We have several ProxySQL instances and this behavior wasn't happening on all of them but on the one receiving the highest number of similar queries. The issue doesn't occur with other ORMs (mysql client for Rails) since it relies on mysql to set the AUTO_INCREMENT id.

We have temporarily fixed this with ProxySQL query rewrites where we catch all insert queries similar to the above pattern and we set NULL instead of DEFAULT; this way we are utilizing multiplexing to the maximum and no need to turn it off for such queries, and that helped us to drop the connections from a couple of thousands to a handful (to the extent where sometimes we feel that there is something wrong with the stats we pull from the system 👍)

Do you have any other solutions? Is this a bug in ProxySQL? What is DEFAULT doing in this context and how ProxySQL is reacting to it?

Many thanks for the great work. Those are the stats from one of our least loaded ProxySQL nodes.

[renecannao]

@jaywad: if an INSERT statements fails, proxysql temporarily disables multiplexing. The reason is that proxysql relies on StatusFlags to determine if a transaction is running or not. OK packet and EOF packet include StatusFlags . But ERR packet doesn't include StatusFlags, therefore ProxySQL doesn't know if a transaction is running or not. To be on the safe side, it assumes that possibly a transaction is running, until a query is executed and an OK or EOF packet is returned.

[jamalawd]

@renecannao Many thanks for the prompt reply and the great tool.

I am still unable to link your explanation with the above-mentioned case we are facing and curious to know how ProxySQL is treating the DEFAULT value of the auto-increment field which is being sent by an ORM.

[renecannao]

@jaywad : if I am understanding your comment correctly, you are saying that multiplexing is being disabled when a query uses DEFAULT , and I am explained why. Maybe I misunderstood your comment?

[jamalawd]

@renecannao I think there is a misunderstanding.

When multiplexing is set to true (by admin) and a query uses DEFAULT, one record in the table is inserted with id = 0 and the error 1062, Duplicate entry '0' for key starts happening on the successive inserts which uses DEFAULT as well. When we replaced DEFAULT with NULL we never faced the issue.

One thing we noticed after running a query that uses DEFAULT, the SELECT LAST_INSERT_ID() returned zero.

[jamalawd]

@renecannao We found it 😄

Rails mysql2 adapter is setting the below session variables when establishing every connection where it adds NO_AUTO_VALUE_ON_ZERO to sql_mode and when the same connection is being multiplexed and used by the node.js library, on insert queries that use DEFAULT, the error 1062, Duplicate entry '0' for key starts coming and that was the reason why the error wasn't consistent since some connections were established by the node.js apps and used among itself without issues however all the connections established by Rails and afterward used by node.js, the error starts happening.

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

Do you have a better solution to the issue? Many thanks in advance!