SQLite 'table already exists' errors during PROXYSQL STOP

[renecannao]

Bug: SQLite 'table already exists' errors during PROXYSQL STOP

Description

During PROXYSQL STOP execution, multiple SQLite errors are logged indicating that tables already exist when there's an attempt to create them. This appears to be caused by internal module restarts that try to recreate in-memory tables without checking if they already exist.

Error Log Sample

2025-11-23 16:38:44 Admin_Handler.cpp:685:admin_handler_command_proxysql(): [INFO] PROXYSQL STOP: Exited shutdown wait loop after 862 iterations
2025-11-23 16:38:44 Admin_Handler.cpp:687:admin_handler_command_proxysql(): [INFO] PROXYSQL STOP: Module stop completed, all modules stopped
2025-11-23 16:38:44 sqlite3db.cpp:227:execute(): [ERROR] SQLITE error: table mysql_servers already exists --- CREATE TABLE mysql_servers ( hostgroup_id INT NOT NULL DEFAULT 0 , hostname VARCHAR NOT NULL , port INT NOT NULL DEFAULT 3306 , gtid_port INT NOT NULL DEFAULT 0 , weight INT CHECK (weight >= 0) NOT NULL DEFAULT 1 , status INT CHECK (status IN (0, 1, 2, 3, 4)) NOT NULL DEFAULT 0 , compression INT CHECK (compression >=0 AND compression <= 102400) NOT NULL DEFAULT 0 , max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000 , max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0 , use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0 , max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0 , comment VARCHAR NOT NULL DEFAULT '' , mem_pointer INT NOT NULL DEFAULT 0 , PRIMARY KEY (hostgroup_id, hostname, port) )
2025-11-23 16:38:44 sqlite3db.cpp:227:execute(): [ERROR] SQLITE error: table mysql_servers_incoming already exists --- CREATE TABLE mysql_servers_incoming ( hostgroup_id INT NOT NULL DEFAULT 0 , hostname VARCHAR NOT NULL , port INT NOT NULL DEFAULT 3306 , gtid_port INT NOT NULL DEFAULT 0 , weight INT CHECK (weight >= 0) NOT NULL DEFAULT 1 , status INT CHECK (status IN (0, 1, 2, 3, 4)) NOT NULL DEFAULT 0 , compression INT CHECK (compression >=0 AND compression <= 102400) NOT NULL DEFAULT 0 , max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000 , max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0 , use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0 , max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0 , comment VARCHAR NOT NULL DEFAULT '' , PRIMARY KEY (hostgroup_id, hostname, port))
2025-11-23 16:38:44 sqlite3db.cpp:227:execute(): [ERROR] SQLITE error: table mysql_replication_hostgroups already exists --- CREATE TABLE mysql_replication_hostgroups ( writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY , reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>=0) , check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only','read_only|innodb_read_only','read_only&innodb_read_only')) NOT NULL DEFAULT 'read_only' , comment VARCHAR NOT NULL DEFAULT '' , UNIQUE (reader_hostgroup))
2025-11-23 16:38:44 sqlite3db.cpp:227:execute(): [ERROR] SQLITE error: table mysql_group_replication_hostgroups already exists --- CREATE TABLE mysql_group_replication_hostgroups ( writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY , backup_writer_hostgroup INT CHECK (backup_writer_hostgroup>=0 AND backup_writer_hostgroup<>writer_hostgroup) NOT NULL , reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND backup_writer_hostgroup<>reader_hostgroup AND reader_hostgroup>0) , offline_hostgroup INT NOT NULL CHECK (offline_hostgroup<>writer_hostgroup AND offline_hostgroup<>reader_hostgroup AND backup_writer_hostgroup<>offline_hostgroup AND offline_hostgroup>=0) , active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1 , max_writers INT NOT NULL CHECK (max_writers >= 0) DEFAULT 1 , writer_is_also_reader INT CHECK (writer_is_also_reader IN (0,1,2)) NOT NULL DEFAULT 0 , max_transactions_behind INT CHECK (max_transactions_behind>=0) NOT NULL DEFAULT 0 , comment VARCHAR , UNIQUE (reader_hostgroup) , UNIQUE (offline_hostgroup) , UNIQUE (backup_writer_hostgroup))

Root Cause Analysis

The issue appears to be related to:

1. Internal module restarts during PROXYSQL STOP process
2. Attempts to recreate in-memory SQLite tables without checking existence
3. Missing "IF NOT EXISTS" clauses in CREATE TABLE statements
4. Possible race conditions during module shutdown sequence

Affected Tables

Based on the error log, affected tables include:

• mysql_servers
• mysql_servers_incoming
• mysql_replication_hostgroups
• mysql_group_replication_hostgroups
• (Likely other tables as well - this is just a snippet)

Expected Behavior

• During shutdown, tables should either be dropped cleanly or recreation attempts should check for existence
• No SQLite errors should be logged during normal shutdown operations
• Module restart logic should handle existing tables gracefully

Proposed Fix

The fix should be straightforward:

1. Modify the modules issuing CREATE TABLE queries to use "IF NOT EXISTS" clauses
2. Implement proper table existence checks before recreation attempts
3. Review module shutdown sequence to prevent unnecessary table recreations
4. Ensure proper cleanup order during module stops

Files Likely Needing Changes

• MySQL module initialization code (where CREATE TABLE statements are defined)
• Module initialization/shutdown code
• Admin handler code for PROXYSQL STOP

Note: sqlite3db.cpp is just a wrapper for executing queries and should not be modified. The actual modules that issue the CREATE TABLE queries need to be updated.

Related Issues

• Fix query handling after PROXYSQL STOP #5186: Fix query handling after PROXYSQL STOP
• Verify dependencies between PR 5217 (PROXYSQL STOP/START crash fixes) and PR 4960 (module enable/disable) #5218: Verify dependencies between PR 5217 (PROXYSQL STOP/START crash fixes) and PR 4960
• PROXYSQL STOP takes ~40 seconds on idle instances #5219: PROXYSQL STOP takes ~40 seconds on idle instances (may be related to inefficient shutdown)

This seems like an easy fix that would clean up error logging during normal operations and potentially improve shutdown performance.

[renecannao]

Closed by #5223