strapi cms service can not start when using proxysql

[cataglyphis]

• ProxySQL: ProxySQL version 2.5.2-217-g7f727b3
• OS: Ubuntu 22.04.2 LTS ARM
• strapi: 4.10.5
• AWS Aurora MySQL: 8.0.23

Strapi cms service can not start when using proxysql, but when we change the MySQL endpoint to pre-us-instance-1.cp22vumu2eah.us-west-1.rds.amazonaws.com, everything worked well. It looks that strapi service want to set session variable, but failed.

2023-06-08 07:14:03 MySQL_Session.cpp:2602:handler_again___status_SETTING_GENERIC_VARIABLE(): [WARNING] Error while setting sql_require_primary_key to "OFF" on pre-us-instance-1.cp22vumu2eah.us-west-1.rds.amazonaws.com:3306 hg 1 :  1235, This version of MySQL doesn't yet support 'Modifying this variable at the session level'
2023-06-08 07:14:03 MySQL_Session.cpp:240:kill_query_thread(): [WARNING] KILL CONNECTION 80989 on pre-us-instance-1.cp22vumu2eah.us-west-1.rds.amazonaws.com:3306
2023-06-08 07:14:04 MySQL_Session.cpp:2602:handler_again___status_SETTING_GENERIC_VARIABLE(): [WARNING] Error while setting sql_require_primary_key to "OFF" on pre-us-instance-1.cp22vumu2eah.us-west-1.rds.amazonaws.com:3306 hg 1 :  1235, This version of MySQL doesn't yet support 'Modifying this variable at the session level'
2023-06-08 07:14:04 MySQL_Session.cpp:240:kill_query_thread(): [WARNING] KILL CONNECTION 80991 on pre-us-instance-1.cp22vumu2eah.us-west-1.rds.amazonaws.com:3306
2023-06-08 07:14:04 MySQL_Session.cpp:2602:handler_again___status_SETTING_GENERIC_VARIABLE(): [WARNING] Error while setting sql_require_primary_key to "OFF" on pre-us-instance-1.cp22vumu2eah.us-west-1.rds.amazonaws.com:3306 hg 1 :  1235, This version of MySQL doesn't yet support 'Modifying this variable at the session level'
2023-06-08 07:14:04 MySQL_Session.cpp:240:kill_query_thread(): [WARNING] KILL CONNECTION 80993 on pre-us-instance-1.cp22vumu2eah.us-west-1.rds.amazonaws.com:3306

MySQL Servers:

INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections, comment) VALUES (1, 'pre-us-instance-1.cp22vumu2eah.us-west-1.rds.amazonaws.com', 3306, 1000, 'pre-us-instance-1');
INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections, comment) VALUES (1, 'pre-us-instance-1-us-west-1b.cp22vumu2eah.us-west-1.rds.amazonaws.com', 3306, 1000, 'pre-us-instance-1-us-west-1b');

Aurora Config:

INSERT INTO mysql_aws_aurora_hostgroups (writer_hostgroup, reader_hostgroup, active, aurora_port, domain_name, max_lag_ms, writer_is_also_reader, lag_num_checks, comment) VALUES (1, 2, 1, 3306, '.cp22vumu2eah.us-west-1.rds.amazonaws.com', 10000, 0, 3, 'us-west-1 pre instance');

MySQL User:

INSERT INTO mysql_users (username, password, default_hostgroup, default_schema, transaction_persistent, max_connections) VALUES ('cms', 'xxx', 1, 'cms', 1, 200);

Server Status:

MySQL proxyadmin@localhost:(none)> select * from runtime_mysql_servers
+--------------+-----------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------------------+
| hostgroup_id | hostname                                                              | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment                      |
+--------------+-----------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------------------+
| 1            | pre-us-instance-1.cp22vumu2eah.us-west-1.rds.amazonaws.com            | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | pre-us-instance-1            |
| 2            | pre-us-instance-1-us-west-1b.cp22vumu2eah.us-west-1.rds.amazonaws.com | 3306 | 0         | ONLINE | 1000   | 0           | 1000            | 0                   | 0       | 0              | pre-us-instance-1-us-west-1b |
+--------------+-----------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------------------+

pre-us-instance-1 is the writer instance, and pre-us-instance-1-us-west-1b is the reader instance.

Proxy Rules:

# writer instance
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (1, 1, '\/\*FORCE_MASTER\*\/', 1, 1);

# reader instance
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (2, 1, '\/\*FORCE_SLAVE\*\/', 2, 1);

# COUNT(*), reader instance
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES (3, 1, '^SELECT COUNT\(\*\)', 2, 1); 

# writer instance
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES (10, 1, '^SELECT .* FOR UPDATE$', 1, 1);

# reader instance
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES (11, 1, '^SELECT .*', 2, 1);

[renecannao]

Hi @cataglyphis . It seems that the client (strapi cms) is trying to set sql_require_primary_key=OFF while Aurora doesn't support modifying it (note that sql_require_primary_key=OFF is the default anyway). Why are you creating an issue for ProxySQL other than because "can not start when using proxysql" ? Is this a proxysql issue? a strapi cms issue? A misconfiguration from your end?

[cataglyphis]

Hi @cataglyphis . It seems that the client (strapi cms) is trying to set sql_require_primary_key=OFF while Aurora doesn't support modifying it (note that sql_require_primary_key=OFF is the default anyway). Why are you creating an issue for ProxySQL other than because "can not start when using proxysql" ? Is this a proxysql issue? a strapi cms issue? A misconfiguration from your end?

Yes, I understand your questions. But when i change the host address to aurora instance (bypass ProxySQL), then strapi cms can work again. This confused me.

[renecannao]

I understand your confusion, but I think you first need to answer this question: why strapi cms tries to set sql_require_primary_key when connecting to proxysql?

[cataglyphis]

I understand your confusion, but I think you first need to answer this question: why strapi cms tries to set sql_require_primary_key when connecting to proxysql?

I will post this issue in strapi repo and hope this can be resolved. Thanks 🙏

[cataglyphis]

I understand your confusion, but I think you first need to answer this question: why strapi cms tries to set sql_require_primary_key when connecting to proxysql?

I compare the result of sql command. If set sql_require_primary_key=0 failed, all other sql commands will failed too when using ProxySQL. But with Aurora, the other sql commands can still run successfully even though set sql_require_primary_key=0 failed.

ProxySQL:

MySQL cms@13.57.xx.xx:cms> select version() as version;
1 row in set
Time: 0.179s
MySQL cms@13.57.xx.xx:cms> select version()
1 row in set
Time: 0.179s
MySQL cms@13.57.xx.xx:cms> set session sql_require_primary_key=0;
Query OK, 0 rows affected
Time: 0.172s
MySQL cms@13.57.xx.xx:cms> select version()
(1235, "This version of MySQL doesn't yet support 'Modifying this variable at the session level'")
MySQL cms@13.57.xx.xx:cms> select version()
(1235, "This version of MySQL doesn't yet support 'Modifying this variable at the session level'")
MySQL cms@13.57.xx.xx:cms> select version()
(1235, "This version of MySQL doesn't yet support 'Modifying this variable at the session level'")
MySQL cms@13.57.xx.xx:cms> select version()
(1235, "This version of MySQL doesn't yet support 'Modifying this variable at the session level'")
MySQL cms@13.57.xx.xx:cms>

Aurora:

MySQL cms@13.57.xx.xx:cms> select version()
1 row in set
Time: 0.167s
MySQL cms@13.57.xx.xx:cms> set session sql_require_primary_key=0;
(1235, "This version of MySQL doesn't yet support 'Modifying this variable at the session level'")
MySQL cms@13.57.xx.xx:cms> select version()
1 row in set
Time: 0.160s
MySQL cms@13.57.xx.xx:cms> select version()
1 row in set
Time: 0.169s
MySQL cms@13.57.xx.xx:cms> select version()
1 row in set
Time: 0.163s

[renecannao]

Hi @cataglyphis .

Ok, from the above output it seems that strapi cms executes that command no matter the backend, but if executed through proxysql it then fails to make progress.

You can create a query rule to filter this query. Something like the following (adjust rule_id and other columns if needed):

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, OK_msg) VALUES
(1,1,'^SET.*sql_require_primary_key', '');
LOAD MYSQL QUERY RULES TO RUNTIME;

We need a better way to handle this issue transparently without the need for a query rules. Please do not close this issue yet. Thanks

[dbadmin-irl]

Just an FYI in case anyone stumbles across this. AFAIK, from Aurora version 3.03(8.0.26 compat) you should be able to set sql_require_primary_key on writers and readers(kinda redundant on readers since they are always innodb_read_only in Aurora), provided the user running set has the session_variables_admin dynamic privilege.

Its nice insurance, but prob not best for a client to be automatically trying to set a "restricted" session variable by default on connect.