When we ran sysbench with this setting enabled, many queries got rejected by MaxScale with-

2021-07-31 00:46:46   warning: (260) Unknown prepared statement handler (67) given to MaxScale for COM_STMT_CLOSE by ''sysbench'@'::ffff:100.24.6.203''

2021-07-31 00:46:46   warning: (260) Unknown prepared statement handler (35) given to MaxScale for COM_STMT_CLOSE by ''sysbench'@'::ffff:100.24.6.203''

2021-07-31 00:46:46   warning: (260) Unknown prepared statement handler (99) given to MaxScale for COM_STMT_CLOSE by ''sysbench'@'::ffff:100.24.6.203''

2021-07-31 00:46:46   warning: (260) Unknown prepared statement handler (131) given to MaxScale for COM_STMT_CLOSE by ''sysbench'@'::ffff:100.24.6.203''

These errors do not occur when only persistpoolmax and persistmaxtime are configured- these occur only when idle_session_pool_time is configured and these errors occur at the exact time that kicks-in (in the test case, it was 10 seconds into sysbench run).

Competing ProxySQL's Multiplexing feature also does not provide benefit for prepared statements, but it does identify prepared statements and avoids attempting to perform multiplexing on them. This means ProxySQL administrators can leave Multiplexing enabled and benefit from it when compatible queries are pushed through, and then incompatible queries (like prepared statements) are ignored and execute as expected. By comparison, for MaxScale, idle_session_pool_time needs to be disabled if any prepared statements are going to be submitted to MaxScale, even if these account for only a small fraction of the overall workload.

Can you add the sysbench command you ran?

Managed to reproduce MDEV-19811 with prepared statements and idle_session_pool_time.

Adding code that disables the pooling for that session if prepared statements are used should help avoid any problems.

Managed to also reproduce the exact same message by running sysbench.

Can you add the sysbench command you ran?

# Prepare the test

sysbench \

--db-driver=mysql \

--mysql-user=${maxscale_user} \

--mysql_password=${maxscale_pw} \

--mysql-db=${db} \

--mysql-host=${maxscale_ip} \

--mysql-port=3306 \

--tables=16 \

--table-size=50000 \

/usr/share/sysbench/oltp_read_write.lua prepare ;

# Run the test

sysbench \

--db-driver=mysql \

--mysql-user=${maxscale_user} \

--mysql_password=${maxscale_pw} \

--mysql-db=${db} \

--mysql-host=${maxscale_ip} \

--mysql-port=3306 \

--tables=16 \

--table-size=50000 \

--threads=499 \

--time=180 \

--events=0 \

--report-interval=1 \

--rate=10 \

/usr/share/sysbench/oltp_read_write.lua run

If a connection uses prepared statements, the feature is disabled. Currently this doesn't re-activate the feature if the open PS count drops to zero but this can be considered an optimization.