Error when running TPC-C MySQL with XML Connection Pool in HammerDB v4.0

[atsuizo]

Could you inform me what settings to use to avoid this error ?

And if there is no documentation on how to set that up, It would be very helpful if you could add it.

I really wanted to contribute by finding ways to cope with the situation, but I didn't have the skills to do so.

Error Messages

mysqlexec/db server: Unknown prepared statement handler (ostat_st) given to EXECUTE
mysqlexec/db server: Unknown prepared statement handler (delivery_st) given to EXECUTE

MySQL Error Code 1243.

This message is displayed for all VUs.

Settings

・dict

mysql_connect_pool     = true

For the other parameters, I used the same values as the settings that work fine in non-ConectionPool mode.
(Even if I don't set "mysql_prepared = true", it will be set to true internally in ConnectionPool mode, right?)

・mysqlcpool.xml

・Connections
c1：Master Instance Endpoint & Correct Port, User, Password
c2：Read Replica Instance Endpoint & Correct Port, User, Password

・sprocs
<neworder>
        <connections>c1</connections>
        <policy>round_robin</policy>
</neworder>
<payment>
        <connections>c1</connections>
        <policy>round_robin</policy>
</payment>
<delivery>
        <connections>c1</connections>
        <policy>round_robin</policy>
</delivery>
<stocklevel>
        <connections>c2</connections>
        <policy>round_robin</policy>
</stocklevel>
<orderstatus>
        <connections>c2</connections>
        <policy>round_robin</policy>
</orderstatus>

I installed HammerDB v4.0 on AWS EC2 - Amazon Linux2, and set up a MySQL 8.0.23 client using "yum".

I tried it with AWS RDS/MySQL 8.0.21 and AWS Aurora MySQL 5.7（mysql_aurora.2.09.2）, reproduced it.

If in ConectionPool mode also directs all "sprocs.connections" to C1(Master Instance) only, this message will not be output.

If we set C2 only for "sprocs.orderstatusconnections" and C1 for the rest, an error message of "slev_st" will be output instead of "delivery_st" .

Furthermore, when I did the equivalent setting in PostgreSQL, no error message was output.

[sm-shaw]

Many thanks for the Issue, yes you have found a bug. There is a debug line that if uncommented prints out the prepared statements.

#For MySQL cursor names are placeholders to choose the correct policy. The placeholder is then used to select the connection. The prepared statements are always called neword_st, payment_st etc for each connection
#puts "sproc_cur:$st connections:[ set $cslist ] cursors:[set $cursor_list] number of cursors:[set $len] execs:[set $cnt]"
    }

Using your configuration I can reproduce the same error and the debug line shows that ostat and delivery are in the wrong place.

Vuser 2:sproc_cur:neword_st connections:mysql0 cursors:neword_st_0 number of cursors:1 execs:0
Vuser 2:sproc_cur:payment_st connections:mysql0 cursors:payment_st_0 number of cursors:1 execs:0
Vuser 2:sproc_cur:ostat_st connections:mysql0 cursors:ostat_st_0 number of cursors:1 execs:0
Vuser 2:sproc_cur:delivery_st connections:mysql1 cursors:delivery_st_0 number of cursors:1 execs:0
Vuser 2:sproc_cur:slev_st connections:mysql1 cursors:slev_st_0 number of cursors:1 execs:0
Vuser 2:Processing 1000000 transactions with output suppressed...
Vuser 2:mysqlexec/db server: Unknown prepared statement handler (delivery_st) given to EXECUTE
Vuser 2:mysqlexec/db server: Unknown prepared statement handler (delivery_st) given to EXECUTE
Vuser 2:mysqlexec/db server: Unknown prepared statement handler (ostat_st) given to EXECUTE
Vuser 2:mysqlexec/db server: Unknown prepared statement handler (ostat_st) given to EXECUTE

The bug is that in the driver script ostat_st is indeed in the wrong place before delivery_st when building the list of prepared statements. (Yes you are right it forces the use of prepared statements to maintain a list of what to execute for each connection).
foreach st {neword_st payment_st ostat_st delivery_st slev_st} cslist {csneworder cspayment csdelivery csstocklevel csorderstatus} cursor_list { neworder_cursors payment_cursors delivery_cursors stocklevel_cursors orderstatus_cursors } len { nolen pylen dllen sllen oslen } cnt { nocnt pycnt dlcnt slcnt oscnt } {
It should of course look like this coming after slev_st
foreach st {neword_st payment_st delivery_st slev_st ostat_st} cslist {csneworder cspayment csdelivery csstocklevel csorderstatus} cursor_list { neworder_cursors payment_cursors delivery_cursors stocklevel_cursors orderstatus_cursors } len { nolen pylen dllen sllen oslen } cnt { nocnt pycnt dlcnt slcnt oscnt } {
Changing the order now shows correctly prepared statements and it does not error on execution.

Vuser 2:sproc_cur:neword_st connections:mysql0 cursors:neword_st_0 number of cursors:1 execs:0
Vuser 2:sproc_cur:payment_st connections:mysql0 cursors:payment_st_0 number of cursors:1 execs:0
Vuser 2:sproc_cur:delivery_st connections:mysql0 cursors:delivery_st_0 number of cursors:1 execs:0
Vuser 2:sproc_cur:slev_st connections:mysql1 cursors:slev_st_0 number of cursors:1 execs:0
Vuser 2:sproc_cur:ostat_st connections:mysql1 cursors:ostat_st_0 number of cursors:1 execs:0

Also if you have just 1 connection you can specify a policy of first_named (or last_named) instead. This is what I used to debug using 2 databases on the same host.

<connpool>
<connections>
    <c1>
	<mysql_host>localhost</mysql_host>
        <mysql_port>5432</mysql_port>
        <mysql_socket>/tmp/mysql.sock</mysql_socket>
	<mysql_user>root</mysql_user>
        <mysql_pass>mysql</mysql_pass>
        <mysql_dbase>tpcc1</mysql_dbase>
    </c1>
    <c2>
	<mysql_host>localhost</mysql_host>
        <mysql_port>5432</mysql_port>
        <mysql_socket>/tmp/mysql.sock</mysql_socket>
	<mysql_user>root</mysql_user>
        <mysql_pass>mysql</mysql_pass>
	<mysql_dbase>tpcc2</mysql_dbase>
    </c2>
</connections>
<sprocs>
  	<neworder>
		<connections>c1</connections>
    		<policy>first_named</policy>
	</neworder>
    	<payment>
		<connections>c1</connections>
    		<policy>first_named</policy>
	</payment>
    	<delivery>
		<connections>c1</connections>
    		<policy>first_named</policy>
	</delivery>
    	<stocklevel>
		<connections>c2</connections>
    		<policy>first_named</policy>
	</stocklevel>
    	<orderstatus>
		<connections>c2</connections>
    		<policy>first_named</policy>
	</orderstatus>
</sprocs>
</connpool>

As a workaround for v4.0 you can edit the driver script and change this line (ie move ostat_st to the end).
foreach st {neword_st payment_st ostat_st delivery_st slev_st} ...
to this
foreach st {neword_st payment_st delivery_st slev_st ostat_st} ...
A pull request will be made to fix this in the source for inclusion in v4.1. The PR will be linked to this Issue.

[sm-shaw]

Fix is in Pull Request #217