I'm using an Appsmith app connected to a MySQL server, and when I perform a single query, multiple sessions are opened due to connection pooling. This quickly maxes out the maximum user capacity of my server, resulting in query errors. I've tried increasing the maximum capacity, but my server provider says they cannot do it. I need a workaround or a feature to limit the connection pool size through Appsmith. Currently, I don't have administrative access to adjust the wait_timeout and interactive_timeout values or change the pool size.
The problem is that Appsmith connected to a MySQL server using connection pooling which resulted in multiple sessions being opened and left in sleep mode, causing the server's max user capacity to be quickly maxed out. The user cannot increase the max user capacity and cannot change wait_timeout and interactive_timeout since the database is shared with other users.
To solve this issue, the Appsmith team suggested that the user execute the following query after every MySQL query to kill all sleep sessions:
SELECT concat('KILL ',id,';') as query FROM information_schema.processlist WHERE Command = 'Sleep';
The team also recommended increasing the max_connections value on the MySQL server and opened a feature request to allow users to limit the pool size through Appsmith. The feature request can be tracked on Github.
While the feature request is being implemented, the user can use the provided workaround to mitigate the issue.