Fixed MySQL session reset

[jk8]

• In Data/MySQL/src/SessionHandle.cpp changed calling of mysql_reset_connection() to query FLUSH
• previously used mysql_reset_connection() caused issues with encoding
• see comment in Data/MySQL/src/SessionHandle.cpp:181 SessionHandle::reset() for more info

Related issues

https://github.com/pocoproject/poco/issues/2546 https://github.com/pocoproject/poco/issues/2800

[obiltschnig]

FLUSH STATUS seems to affect more than the current session:

This option adds the session status from all active sessions to the global status variables, resets the status of all active sessions, and resets account, host, and user status values aggregated from disconnected sessions. See Section 26.12.14, “Performance Schema Status Variable Tables”. This information may be of use when debugging a query. See Section 1.7, “How to Report Bugs or Problems”.

Not sure if this is the right way to do it.

[frwilckens]

Using "FLUSH STATUS" instead of mysql_reset_connection() is problematic. It may solve a special problem with character encodings, but it leads to other problems. It changes the semantics. For example, the "FLUSH" statement causes an implicit commit, while mysql_reset_connection() rolls back any active transaction and resets autocommit mode. Clearly, you want to do the latter when you return a Session to a SessionPool, not the former.

[frwilckens]

As an experiment I added test void MySQLTest::testSessionPoolAndUnicode() from the pull request to the other MySQL tests in the devel branch without changing SessionHandle::reset(), and the test passes on my machine. I'm using MySQL 8.0.23. Of course, this is not a comprehensive test but it raises the question under which conditions the encoding issue comes up in the first place.