ProxySQL 2.4.5 crash in MySQL_Session.cpp:3303

[calh]

• [x] ProxySQL version: 2.4.5-146-ge590770, SHA1 checksum: c606f3f25d17a1211560ea6f2fee709def853448

• [x] OS version: Rocky 8.7, kernel 4.18.0-425.3.1.el8.x86_64

• [x] MySQL version 5.6.51

• [x] The full ProxySQL error log (default location: /var/lib/proxysql/proxysql.log)

  
  proxysql: MySQL_Session.cpp:3303: void MySQL_Session::handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_STMT_EXECUTE(PtrSize_t&): Assertion `0' failed.
  Error: signal 6:
  /usr/bin/proxysql(_Z13crash_handleri+0x2a)[0x61f3da]
  /lib64/libc.so.6(+0x4eb80)[0x7ff584787b80]
  /lib64/libc.so.6(gsignal+0x10f)[0x7ff584787aff]
  /lib64/libc.so.6(abort+0x127)[0x7ff58475aea5]
  /lib64/libc.so.6(+0x21d79)[0x7ff58475ad79]
  /lib64/libc.so.6(+0x47456)[0x7ff584780456]
  /usr/bin/proxysql(_ZN13MySQL_Session75handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_STMT_EXECUTEER10_PtrSize_t+0x5da)[0x693cca]
  /usr/bin/proxysql(_ZN13MySQL_Session20get_pkts_from_clientERbR10_PtrSize_t+0x685)[0x69d215]
  /usr/bin/proxysql(_ZN13MySQL_Session7handlerEv+0xc8)[0x69dcc8]
  /usr/bin/proxysql(_ZN12MySQL_Thread20process_all_sessionsEv+0x34c)[0x6806ec]
  /usr/bin/proxysql(_ZN12MySQL_Thread3runEv+0x50a)[0x680e8a]
  /usr/bin/proxysql(_Z24mysql_worker_thread_funcPv+0x74)[0x6171e4]
  /lib64/libpthread.so.0(+0x81cf)[0x7ff585c621cf]
  /lib64/libc.so.6(clone+0x43)[0x7ff584772e73]
  ---- /usr/bin/proxysql(_Z13crash_handleri+0x2a) [0x61f3da] : crash_handler(int)
  ---- /usr/bin/proxysql(_ZN13MySQL_Session20get_pkts_from_clientERbR10_PtrSize_t+0x685) [0x69d215] : MySQL_Session::get_pkts_from_client(bool&, _PtrSize_t&)
  ---- /usr/bin/proxysql(_ZN13MySQL_Session7handlerEv+0xc8) [0x69dcc8] : MySQL_Session::handler()
  ---- /usr/bin/proxysql(_ZN12MySQL_Thread20process_all_sessionsEv+0x34c) [0x6806ec] : MySQL_Thread::process_all_sessions()
  ---- /usr/bin/proxysql(_ZN12MySQL_Thread3runEv+0x50a) [0x680e8a] : MySQL_Thread::run()
  ---- /usr/bin/proxysql(_Z24mysql_worker_thread_funcPv+0x74) [0x6171e4] : mysql_worker_thread_func(void*)
  To report a crashing bug visit: https://github.com/sysown/proxysql/issues
  For support visit: https://proxysql.com/services/support/
  2023-03-03 21:08:29 main.cpp:1297:ProxySQL_daemonize_phase3(): [ERROR] ProxySQL crashed. Restarting!
  2023-03-03 21:08:29 [INFO] ProxySQL version 2.4.5-146-ge590770
  2023-03-03 21:08:29 [INFO] ProxySQL SHA1 checksum: c606f3f25d17a1211560ea6f2fee709def853448
  2023-03-03 21:08:29 [INFO] Angel process started ProxySQL process 3455345
  2023-03-03 21:08:29 [INFO] Loaded built-in SQLite3
  Standard ProxySQL MySQL Logger rev. 2.0.0714 -- MySQL_Logger.cpp -- Wed Dec 14 12:20:55 2022
  Standard ProxySQL Cluster rev. 0.4.0906 -- ProxySQL_Cluster.cpp -- Wed Dec 14 12:20:55 2022
  Standard ProxySQL Statistics rev. 1.4.1027 -- ProxySQL_Statistics.cpp -- Wed Dec 14 12:20:55 2022
  Standard ProxySQL HTTP Server Handler rev. 1.4.1031 -- ProxySQL_HTTP_Server.cpp -- Wed Dec 14 12:20:55 2022
  2023-03-03 21:08:29 [INFO] Using UUID from database: 6a024e7a-9f97-4030-bc46-30e084257f01
  2023-03-03 21:08:29 [INFO] Computed checksum for 'LOAD ADMIN VARIABLES TO RUNTIME' was '0x5A0D36921256AE13', with epoch '1677899309'
  2023-03-03 21:08:29 ProxySQL_Admin.cpp:6814:flush_mysql_variables___database_to_runtime(): [WARNING] Impossible to set variable server_capabilities with value "569899". Resetting to current "569867".
  2023-03-03 21:08:29 [INFO] Computed checksum for 'LOAD MYSQL VARIABLES TO RUNTIME' was '0x117D2E3F2F6ECCD9', with epoch '1677899309'
  Standard ProxySQL Admin rev. 2.0.6.0805 -- ProxySQL_Admin.cpp -- Wed Dec 14 12:20:55 2022
  2023-03-03 21:08:29 [INFO] ProxySQL SHA1 checksum: c606f3f25d17a1211560ea6f2fee709def853448
  Standard MySQL Threads Handler rev. 0.2.0902 -- MySQL_Thread.cpp -- Wed Dec 14 12:20:55 2022
  Standard MySQL Authentication rev. 0.2.0902 -- MySQL_Authentication.cpp -- Wed Dec 14 12:20:55 2022
  2023-03-03 21:08:29 [INFO] Computed checksum for 'LOAD MYSQL USERS TO RUNTIME' was '0xE461BD0E42ACEA20', with epoch '1677899309'
  2023-03-03 21:08:29 [INFO] Creating new server in HG 0 : REDACTED:3306 , gtid_port=0, weight=1, status=0
  2023-03-03 21:08:29 [INFO] Creating new server in HG 1 : REDACTED:3306 , gtid_port=0, weight=100, status=0
  2023-03-03 21:08:29 [INFO] Creating new server in HG 1 : REDACTED:3306 , gtid_port=0, weight=1, status=0
  2023-03-03 21:08:29 [INFO] New mysql_group_replication_hostgroups table
  2023-03-03 21:08:29 [INFO] New mysql_galera_hostgroups table
  2023-03-03 21:08:29 [INFO] New mysql_aws_aurora_hostgroups table
  2023-03-03 21:08:29 [INFO] Checksum for table mysql_servers is 3727846084437269917
  2023-03-03 21:08:29 [INFO] Checksum for table mysql_replication_hostgroups is 11160393474678938655
  2023-03-03 21:08:29 [INFO] MySQL_HostGroups_Manager::commit() locked for 2ms
  2023-03-03 21:08:29 [INFO] Computed checksum for 'LOAD PROXYSQL SERVERS TO RUNTIME' was '0x0000000000000000', with epoch '1677899309'
  Standard Query Processor rev. 2.0.6.0805 -- Query_Processor.cpp -- Wed Dec 14 12:20:55 2022
  2023-03-03 21:08:29 [INFO] Computed checksum for 'LOAD MYSQL QUERY RULES TO RUNTIME' was '0x884BDFB039529B00', with epoch '1677899309'
  In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Wed Dec 14 12:20:55 2022
  Standard MySQL Monitor (StdMyMon) rev. 2.0.1226 -- MySQL_Monitor.cpp -- Wed Dec 14 12:20:55 2022

In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Wed Dec 14 12:20:55 2022 Standard MySQL Monitor (StdMyMon) rev. 2.0.1226 -- MySQL_Monitor.cpp -- Wed Dec 14 12:20:55 2022 2023-03-03 21:08:29 [INFO] For information about products and services visit: https://proxysql.com/ 2023-03-03 21:08:29 [INFO] For online documentation visit: https://proxysql.com/documentation/ 2023-03-03 21:08:29 [INFO] For support visit: https://proxysql.com/services/support/ 2023-03-03 21:08:29 [INFO] For consultancy visit: https://proxysql.com/services/consulting/

2023-03-03 21:08:29 MySQL_Session.cpp:4312:handler_minus1_LogErrorDuringQuery(): [WARNING] Error during query on (1,REDACTED,3306,1004501): 1193, Unknown system variable 'transaction_isolation' 2023-03-03 21:08:29 MySQL_Variables.cpp:320:validate_charset(): [WARNING] Server doesn't support collation (255) utf8mb4_0900_ai_ci. Replacing it with the configured default (33) utf8_general_ci. Client REDACTED

- [x] A clear description of the issue

I was previously running mysql 5.5 with proxysql 2.3.2, and migrated our production workload to a new cluster running mysql 5.6 and proxysql 2.4.5.  The new cluster had been running fine for about 8 days before this crash happened.  Although I don't have full query logging turned on, there's nothing particularly special happening at 8:00pm on a Friday compared to any other time of day.

A crash in itself isn't too horrible as long as ProxySQL can restart itself and continue serving traffic.  However in this case, something wild started happening with the configuration database.

Above in the log file, you can see where it loads the mysql servers.  One has a weight of 100, and the others are 1.  My configuration has them all at 100 weight.  Also later on in the file, there's some odd things going on with locale settings or maybe column types that I have never seen before.

When I started investigating this issue, I noticed that my max_connections was set to 2048, but my configuration is supposed to be 8192.  I discovered that, on restart, proxysql re-read my `/etc/proxysql.cnf` file and loaded some configuration settings that I had used to bootstrap the cluster.  This is _not_ supposed to happen, and I guessed that maybe something was corrupt with the `proxysql.db` file.  

I tried this next:

1. Shut down proxysql
2. Deleted the `proxysql.db` and `proxysql.db.bak` files
3. Started proxysql back up
4. Re-loaded my config variables from an SQL file
5. Restarted proxysql

After I restarted proxysql the second time, it again re-read `/etc/proxysql.cnf` and destroyed my config variables that I loaded in step 4.  It seems the only choice I had was to port my SQL config over to the `proxysql.cnf` format just in case it crashes again.

- [x] The steps to reproduce the issue

Currently, all I have to do is change a config setting, save it to disk, restart proxysql and it's gone.  It seems to re-read `proxysql.cnf` every time and wipe out the proxysql database.  Until this crash happened it was working fine and restarts behaved normally, leaving the `proxysql.cnf` file alone.

If this is a crashing bug, please also include:
- [x] The package used to install ProxySQL:  proxysql-2.4.5-1.x86_64  from https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/centos/
- [x] The compressed core dump -- I do have a core dump if needed, but it has a lot of sensitive information in it

Thank you!

[calh]

I believe I have some more findings that make this issue a little more obvious.

Back in December when I bootstrapped this cluster, something must have failed on the first startup of proxysql:

# systemctl status proxysql-initial
● proxysql-initial.service - High Performance Advanced Proxy for MySQL, this service will reset the database and start ProxySQL
   Loaded: loaded (/etc/systemd/system/proxysql-initial.service; disabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Wed 2022-12-28 10:13:33 CST; 2 months 7 days ago
 Main PID: 15906 (code=exited, status=1/FAILURE)

systemd[1]: Starting High Performance Advanced Proxy for MySQL, this service will reset the database and start ProxySQL...
systemctl[15906]: Job for proxysql.service failed because a timeout was exceeded.
systemctl[15906]: See "systemctl status proxysql.service" and "journalctl -xe" for details.
systemd[1]: proxysql-initial.service: Main process exited, code=exited, status=1/FAILURE
systemd[1]: proxysql-initial.service: Failed with result 'exit-code'.
systemd[1]: Failed to start High Performance Advanced Proxy for MySQL, this service will reset the database and start ProxySQL.

Which shouldn't have been a big deal. I fixed things and started it up after that. However, since this one shot service failed, systemd has had this environment variable stuck in it the whole time:

# systemctl show-environment
LANG=en_US.UTF-8
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin
PROXYSQL_OPTS=--initial

It apparently has been destroying and recreating the database every time I restart proxysql. When I work on this server, I'm also frequently adjusting the SQL config and loading it anyway. It only surfaced and became a problem when a crash happened.

This one shot service to destroy the database and recreate it is maybe a little dangerous to use on a deployed package. Is there some better way you can accomplish this? Or maybe a safer implementation of the --initial flag that won't destroy an existing proxysql disk database?