Metricbeat sql module - MySql connection leak when query uses prepared statements

Discuss:
https://discuss.elastic.co/t/metricbeat-8-sql-module-leaks-connections-when-using-prepared-statements/377684

Metricbeat Version 8.14.3
Amazon Linux 2 w/ MySql 8.0.28, Also AWS Aurora Mysql 8.0.36

We recently went from Metricbeat 7.14 to 8.14
We have found idle metricbeat connections piling up in mysql.

Problem: We use the sql module to run queries on mysql. The queries use prepared statements. Since going to metricbeat 8, the connections leak, maybe a difference in the GOlang driver. The queries still work but the connections stay until we hit the mysql connection limit.

Steps to duplicate: spin up a mysql 8.0 instance and use this sql.yml to query it.

Set up /etc/metricbeat/modules.d/sql.yaml to query mysql with a prepared statement:

```
# Module: sql
# Docs: https://www.elastic.co/guide/en/beats/metricbeat/8.14/metricbeat-module-sql.html

- module: sql
metricsets:
- query
period: 10s
hosts: ["metricbeat:password@tcp(127.0.0.1:3306)/?multiStatements=true"]
driver: "mysql"
sql_query: |
SET @schema_name = 'mysql';
SET @sql = CONCAT(
'SELECT TABLE_NAME, TABLE_TYPE ',
'FROM information_schema.tables ',
'WHERE TABLE_SCHEMA = \'', @schema_name, '\''
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
sql_response_format: table
```

Check connections to mysql


These queries work fine, but I have set up a separate job to kill the connections because they increase until we hit the connection limit. Seems like a drastic step.
I have tried to convert my queries into stored procedures, and then used metricbeat to call the proc. Unfortunately same result.

Start by reproducing the leak with the provided sql.yml against MySQL 8.0, using the prepared-statement query and checking idle connections. Then trace the Metricbeat SQL module's connection lifecycle and verify that repeated queries, stored procedures, and prepared statements release connections without reaching the MySQL limit.

Written by the indexing model from the issue text.