1.8.9, 1.12.2: Excessive Database Connections with Hikari enabled with MariaDB.

[null321-jared]

"/version ChatControlRed" - plugin version

10.21.12

Are you using MySQL?

Yes

Are you using BungeeCord?

Yes

Error log (if applicable)

No response

"/chc debug" output (strongly recommended)

debug.zip

Information about the issue/bug

I have 8 servers running with ChatControl Red, and after upgrading from ChatControl Pro to ChatControl red, my MariaDB server has run out of connections. Looking at my MySQL process list, most are ChatControl. This is likely a sign that the connections aren't getting closed correctly, since 8 servers shouldn't have over 200 open connections total.

Here is a snippet of the process list on MariaDB:

MariaDB [(none)]> show processlist;
+--------+------------+-----------------+--------------------+---------+-------+----------+------------------+----------+
| Id     | User       | Host            | db                 | Command | Time  | State    | Info             | Progress |
+--------+------------+-----------------+--------------------+---------+-------+----------+------------------+----------+
| 353306 | global     | localhost:38498 | global_chatcontrol | Sleep   |   514 |          | NULL             |    0.000 |
| 353389 | global     | localhost:40394 | global_chatcontrol | Sleep   | 19031 |          | NULL             |    0.000 |
| 371911 | global     | localhost:33804 | global_chatcontrol | Sleep   |  1022 |          | NULL             |    0.000 |
| 373851 | global     | localhost:54582 | global_chatcontrol | Sleep   |  1786 |          | NULL             |    0.000 |
| 373854 | global     | localhost:41386 | global_chatcontrol | Sleep   |  1783 |          | NULL             |    0.000 |
| 373872 | global     | localhost:41484 | global_chatcontrol | Sleep   |  1775 |          | NULL             |    0.000 |
| 373875 | global     | localhost:60020 | global_chatcontrol | Sleep   |  1774 |          | NULL             |    0.000 |
| 373888 | global     | localhost:60044 | global_chatcontrol | Sleep   |  1767 |          | NULL             |    0.000 |
| 373890 | global     | localhost:33710 | global_chatcontrol | Sleep   |  1764 |          | NULL             |    0.000 |
| 373892 | global     | localhost:33718 | global_chatcontrol | Sleep   |  1762 |          | NULL             |    0.000 |
| 373894 | global     | localhost:33734 | global_chatcontrol | Sleep   |  1757 |          | NULL             |    0.000 |
| 373895 | global     | localhost:33748 | global_chatcontrol | Sleep   |  1756 |          | NULL             |    0.000 |
| 373923 | global     | localhost:33666 | global_chatcontrol | Sleep   |  1739 |          | NULL             |    0.000 |
| 373927 | global     | localhost:33676 | global_chatcontrol | Sleep   |  1735 |          | NULL             |    0.000 |
| 373928 | global     | localhost:33680 | global_chatcontrol | Sleep   |  1735 |          | NULL             |    0.000 |
| 373929 | global     | localhost:41550 | global_chatcontrol | Sleep   |  1734 |          | NULL             |    0.000 |
| 373930 | global     | localhost:41560 | global_chatcontrol | Sleep   |  1733 |          | NULL             |    0.000 |
| 373950 | global     | localhost:40220 | global_chatcontrol | Sleep   |  1711 |          | NULL             |    0.000 |
| 373953 | global     | localhost:40224 | global_chatcontrol | Sleep   |  1706 |          | NULL             |    0.000 |
| 373965 | global     | localhost:45068 | global_chatcontrol | Sleep   |  1691 |          | NULL             |    0.000 |
| 373977 | global     | localhost:45134 | global_chatcontrol | Sleep   |  1686 |          | NULL             |    0.000 |
| 374001 | global     | localhost:51758 | global_chatcontrol | Sleep   |  1662 |          | NULL             |    0.000 |
| 374009 | global     | localhost:51816 | global_chatcontrol | Sleep   |  1660 |          | NULL             |    0.000 |
| 374024 | global     | localhost:51086 | global_chatcontrol | Sleep   |  1642 |          | NULL             |    0.000 |
| 374029 | global     | localhost:43814 | global_chatcontrol | Sleep   |  1633 |          | NULL             |    0.000 |
| 374030 | global     | localhost:43816 | global_chatcontrol | Sleep   |  1633 |          | NULL             |    0.000 |
| 374045 | global     | localhost:37428 | global_chatcontrol | Sleep   |  1623 |          | NULL             |    0.000 |
| 374065 | global     | localhost:54562 | global_chatcontrol | Sleep   |  1613 |          | NULL             |    0.000 |
| 374111 | global     | localhost:41968 | global_chatcontrol | Sleep   |  1582 |          | NULL             |    0.000 |
| 374118 | global     | localhost:46032 | global_chatcontrol | Sleep   |  1572 |          | NULL             |    0.000 |
| 374152 | global     | localhost:52202 | global_chatcontrol | Sleep   |  1542 |          | NULL             |    0.000 |
| 374168 | global     | localhost:47870 | global_chatcontrol | Sleep   |  1529 |          | NULL             |    0.000 |
| 374204 | global     | localhost:47254 | global_chatcontrol | Sleep   |  1499 |          | NULL             |    0.000 |
| 374209 | global     | localhost:56330 | global_chatcontrol | Sleep   |  1494 |          | NULL             |    0.000 |

As you can see, there are a bunch of sleeping connections. This is a really bad issue because it can break all services that depend on MySQL. To prevent that, I switched ChatControl to its own user, and limited it to 50 connections.

[kangarko]

Hey and thanks for reporting!

Can you try the two versions newer ChatControl? You seem a bit out of date.

Anyways, I run the same SQL and after trying /chc reload and hitting refresh in phpmyadmin, it still only shown two processes. I am trying to reproduce this right? Tested with MySQL.

You can try toggling Hikari key in mysql.yml, disable if you had it on or vice versa and see if that has any effect, although I am not sure if hikari would be the culprit. We did fix this very issue some time ago as I can recall.

[null321-jared]

Disabling Hikari worked around the issue. So something to look into in the code for that.

Here is my current config:

# Use an external database to store logs, mails and user data (channels, nicks, etc.)?
# If false, then we store these date into your data.db file.
Enabled: true

# Enable the more modern HikariCP connection pool? 
# If you have issues, try adding &allowPublicKeyRetrieval=true (or false) at the end of "Line" key,
# as well as changing autoReconnect from true to false. Test various combinations or eventually
# set this key to false as last-resort solution.
Hikari: false

# The host and the port to connect to
Host: localhost:3306

# ADVANCED: The full connector line, do not modify unless you know what you are doing.
# If facing issues try appending &useUnicode=yes&characterEncoding=UTF-8 at the end.
#
# Use 'jdbc:mysql://' if you are running a mysql database.
# Use 'jdbc:mariadb://' if you are running a mariadb database.
Line: jdbc:mariadb://{host}/{database}?autoReconnect=false&useSSL=false

[kangarko]

Thanks. It must be hikari then because we do close it. To my best developer knowledge we already do what's recommended here and on other sources. It's a low priority issue then, I'll have a look when I have more time into an alternative fix.