Query using new schema but prepared statement using old schema
Posted by:
No No
Date: October 26, 2020 04:20AM
I'm not sure if this is a MySQL problem or a library problem. Due to time pressure, I'll try posting on both :)
To summarize, it seems like execute is not recognizing that I've changed schemas. This is causing data to be operated on in the incorrect schema.
I have schemas a, b, and c. I create a connection pool defaulting to schema a. On request, I determine the correct target schema (let's say b) and switch to it using .query('use ??'. [schema]);. I do later operations using prepared statements. Logging throughout the process shows I am on schema b after switching, and data goes to the right place.
On a later request, I need to switch to schema c. The logic is the same as the previous request. However, things are strange. After switching schemas, if I check it using .query('select database()'), I get the new schema. If I check it using a prepared statement to execute 'select database()', I get the old schema. Since all of our data operations are using prepared statements, data ends up in the wrong schema.
Is this something about the library using a prepared statement in the wrong schema? Is this a MySQL problem? Help! I don't want to switch to .query(...) for everything due to performance, but I'm not sure of any other solution.