Unexpected behavior when re-using Prepared Statements in different Transactions

[brandonsturgeon]

When using the same prepared statement in different transactions, it seems like the prepared statement will always use the first values given to it in every subsequent call from within any transaction.

If you run the prepared statement outside of a transaction then it'll sort of "shake it loose", fixing the issue temporarily.

Rough pseudo-code example:

local mydb = mysqloo.connect("foo", "bar", "baz", ... )
mydb:connect()

local preparedStatements = {}

function mydb:onConnection()
    preparedStatements.newUser = mydb:prepare( "SELECT * FROM users WHERE steam_id = ? FOR UPDATE" )
end

local firstRun = true

-- Assume this is never called with the same player for this example
function playerInitialSpawn( ply )
    local transaction = mydb:createTransaction()

    local userExists = preparedStatements.newUser
    userExists:setString( 1, ply:SteamID64() )

    transaction:addQuery( userExists )
    transaction:addQuery( otherStuffToo )

    transaction.onSuccess = function()
        local userExistsData = userExists:getData()

        if not firstRun then
            assert( userExistsData[1]["*"].steam_id ~= ply:SteamID64() )
        else
            firstRun = false
        end
    end

    transaction:start()
end

We've found that, after the first player spawns, all subsequent player spawns will actually use the first player's SteamID for the preparedStatements.newUser call. However, if we called preparedStatements.newUser directly, outside of a transaction, it would work just fine!

Are we using prepared statements / transactions incorrectly? Is MySQLOO misbehaving?

I noticed this in the latest beta changelog:

Fixed not being able to add prepared query to transactions more than once

Does that address our issue, too? Because of the 64bit requirement, I can't easily test if it fixes our problem right now.

Info:

OS: Ubuntu 16.04 LTS MySQL: mysql Ver 14.14 Distrib 5.7.31, for Linux (x86_64) using EditLine wrapper MySQLOO: v9.6.1 GMod: Sandbox, running on the main branch

[FredyH]

This is likely the same issue as in #31, there is already a fix for it in the 9.7 beta release.

[brandonsturgeon]

Great!

Will bug fixes be retroactively applied to 9.6 (9.6.2?), given the 32/64bit divergence?

If a 9.6.2 was released with just the bug fixes from 9.7-beta, we'd be in a great position to test it for you!

[FredyH]

I am currently considering using the MariaDB connector for 9.7, which would allow me to compile a 32 bit release as well.