Fail to reconnect to mysql/mariadb when running with mariadb-libs

[haoqixu]

Describe the bug

When running with mariadb-libs, sysbench fails to reconnect to the mysql/mariadb after the server restarts.

To Reproduce

1. run sysbench with mariadb-libs
2. restart the server or kill the connection from server

$ sysbench --mysql-user='root' --mysql-password='123' --mysql-db='test' --mysql-host='127.0.0.1' --mysql-port='3306' --tables='16'  --table-size='10000' --range-size='5' --db-ps-mode='disable' --skip-trx='on' --mysql-ignore-errors='all' --time='1180' --report-interval='1' --histogram='on' --threads=1 oltp_read_only run
sysbench 1.0.20 (using system LuaJIT 2.0.5)

Running the test with following options:
Number of threads: 1
Report intermediate results every 1 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 1s ] thds: 1 tps: 3286.41 qps: 46018.72 (r/w/o: 46018.72/0.00/0.00) lat (ms,95%): 0.34 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 1 tps: 3221.30 qps: 45101.25 (r/w/o: 45101.25/0.00/0.00) lat (ms,95%): 0.39 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 1 tps: 3093.93 qps: 43313.98 (r/w/o: 43313.98/0.00/0.00) lat (ms,95%): 0.61 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 1 tps: 3469.06 qps: 48568.82 (r/w/o: 48568.82/0.00/0.00) lat (ms,95%): 0.31 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 1 tps: 3462.18 qps: 48468.57 (r/w/o: 48468.57/0.00/0.00) lat (ms,95%): 0.30 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 1 tps: 3582.84 qps: 50154.78 (r/w/o: 50154.78/0.00/0.00) lat (ms,95%): 0.30 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 1 tps: 3550.11 qps: 49706.60 (r/w/o: 49706.60/0.00/0.00) lat (ms,95%): 0.30 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 1 tps: 3583.00 qps: 50162.02 (r/w/o: 50162.02/0.00/0.00) lat (ms,95%): 0.29 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 1 tps: 3506.76 qps: 49095.61 (r/w/o: 49095.61/0.00/0.00) lat (ms,95%): 0.30 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 1 tps: 3558.32 qps: 49814.42 (r/w/o: 49814.42/0.00/0.00) lat (ms,95%): 0.30 err/s: 0.00 reconn/s: 0.00
[ 11s ] thds: 1 tps: 3526.88 qps: 49374.25 (r/w/o: 49374.25/0.00/0.00) lat (ms,95%): 0.30 err/s: 0.00 reconn/s: 0.00
[ 12s ] thds: 1 tps: 3168.85 qps: 44359.84 (r/w/o: 44359.84/0.00/0.00) lat (ms,95%): 0.31 err/s: 1.00 reconn/s: 0.00
[ 13s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 14s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 16s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 17s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 18s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 19s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 21s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 22s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 23s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 24s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 25s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 26s ] thds: 1 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
^C

Expected behavior

sysbench should reconnect to the server and keep running.

Environment

$ pacman -Si sysbench mariadb-libs
Repository      : extra
Name            : sysbench
Version         : 1.0.20-1
Description     : Scriptable multi-threaded benchmark tool for databases and systems
Architecture    : x86_64
URL             : https://github.com/akopytov/sysbench
Licenses        : GPL2
Groups          : None
Provides        : None
Depends On      : luajit  mariadb-libs  postgresql-libs
Optional Deps   : None
Conflicts With  : None
Replaces        : None
Download Size   : 118.28 KiB
Installed Size  : 526.33 KiB
Packager        : Filipe Laíns <lains@archlinux.org>
Build Date      : Thu 23 Jul 2020 09:16:57 PM CST
Validated By    : MD5 Sum  SHA-256 Sum  Signature

Repository      : extra
Name            : mariadb-libs
Version         : 11.3.2-1
Description     : MariaDB libraries
Architecture    : x86_64
URL             : https://mariadb.org/
Licenses        : GPL
Groups          : None
Provides        : libmariadbclient  mariadb-connector-c  libmariadb.so=3-64  libmariadbd.so=19-64
Depends On      : liburing  libxcrypt  libcrypt.so=2-64  openssl  pcre2  zlib  zstd
Optional Deps   : krb5: for gssapi authentication
Conflicts With  : libmysqlclient  libmariadbclient  mariadb-connector-c
Replaces        : libmariadbclient
Download Size   : 5.33 MiB
Installed Size  : 20.83 MiB
Packager        : Christian Hesse <eworm@archlinux.org>
Build Date      : Sat 17 Feb 2024 07:43:52 PM CST
Validated By    : MD5 Sum  SHA-256 Sum  Signature

Additional context

We can not reuse a MYSQL handle without re-initialization after mysql_close(). We should close and reinitialize the handle before reconnection.

• https://dev.mysql.com/doc/c-api/5.7/en/mysql-close.html
• https://mariadb.com/kb/en/mysql_real_connect/