query: start transaction work weird on proxysql.

[juicycool92]

Hello, We've found weird behavior on specific situation when use query start transaction on connect proxysql. I also found there is solution about it, but im not sure why this things are happened, and what's difference between solution method and my case.

I want to someone explain our situation.

spec first.

DB : ProxySql 2.3.2 that facing mariadb for AWS RDS 10.5.13 ( reason why i use proxysql, We need to keep isolate RDS into private subnet ). Node ver : 14.16.1 Mariadb module ver : 2.4.1

my test codes

const mariadb = require('mariadb');
const { createPool } = mariadb;
const dbData = {
    host: `some.proxysql.url`,
    port: `6033`,
    user: `proxysql`,
    password: `SoMe_PrOxY_PaSs`,
    database: `test`,
    leakDetectionTimeout: 2000,
    timezone: "Etc/GMT",
    connectionLimit: 3
};

class DbModule {
    createPool() {
        return createPool( dbData );
    }
}

class DB {
    pool;
    constructor() {
        this.pool = new DbModule().createPool();
    }

    createConn() {
        return new Promise( ( resolve, reject ) => {
            this.pool.getConnection()
                .then( conn => resolve( conn ) )
                .catch( e => reject( `${Tag}[createConn]${e}` ) );
        } );
    }

    createTransaction() { 
        return new Promise( ( resolve, reject ) => {
            this.createConn()
                .then( ( conn ) => {
                    conn.query('start transaction')
                        .catch( e => reject( `[createTransaction] failed to start transaction : ${e}` ) );
                    return conn;
                } )
                .then( conn => resolve( conn ) )
                .catch( e => reject( `[createTransaction] failed to create Conn : ${e}` ) )
        } );
    }
    createTransactionWithFunc() { 
        return new Promise( ( resolve, reject ) => {
            this.createConn()
                .then( async ( conn ) => {
                    await conn.beginTransaction()
                        .catch( e => reject( `[createTransactionWithFunc] failed to start transaction : ${e}` ) );
                    return conn;
                } )
                .then( conn => resolve( conn ) )
                .catch( e => reject( `[createTransactionWithFunc] failed to create Conn : ${e}` ) )
        } );
    }
}

const job1 = async ( db ) => {
    try {
        console.log('# start job 1')
        const con = await db.createTransaction();
        console.log(`## pass create transaction`);
        const query = await con.query(`select 1`);
        console.log(`## pass query`);
        console.log(`## ${JSON.stringify(query[0])}`);
        await con.query(`rollback`)
        con.release();
        console.log(`## pass job1`);
    } catch ( e ) {
        throw (`[JOB1]${e}`);
    }
}

const job2 = async ( db ) => {
    try {
        console.log('# start job 2')
        const con = await db.createTransactionWithFunc();
        console.log(`## pass create transaction`);
        const query = await con.query(`select 1`);
        console.log(`## pass query`);
        console.log(`## ${JSON.stringify(query[0])}`);
        await con.query(`rollback`)
        con.release();
        console.log(`## pass job2`);
    } catch ( e ) {
        throw (`[JOB2]${e}`);
    }
}

const db = new DB();
job1( db )
    .then( _ => console.log(`all jobs done`))

3 years ago, I did not found method name beginTransaction()(not sure am i missing or its born recently 😜 ), so I just used query instead start transaction. And it also work as I expect since recent.

Now, we need to protect database into private network, so I moved on DB, and use proxysql to access test/monitoring use only. and now I facing weird error on local development situation. develop and production server is facing RDS directly, so there is no any errors found, but local server is facing RDS through proxysql, and this one got weird error.

error log on local server

# start job 1
## pass create transaction
(node:25572) UnhandledPromiseRejectionWarning: [JOB1]Error: (conn=6550, no: 45009, SQLState: 08S01) socket has unexpectedly been closed
(Use `node --trace-warnings ...` to show where the warning was created)
(node:25572) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). To terminate the node process on unhandled promise rejection, use the CLI flag `--unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 2)
(node:25572) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

I googled SQLState, but all cases are have condition that 'when cannot connect at all'. But 'create conn without transaction' is working as we expected. I checked connection, deadlock transaction, cpu usage, etc.. but everything looks just fine.

So we check several attempts, and find out only connect on proxysql server will cause these error.

In that moment, I guessed 'its probably proxysql's fault'.

But before i ask to proxysql team, my team found weird behavior that using beginTransaction() instead use start transaction query is working!

another test codes

const db = new DB();
job2( db )
    .then( _ => job2(db) )
    .then( _ => job2(db) )
    .then( _ => job2(db) )
    .then( _ => console.log(`all jobs done`))

result of test codes are

# start job 2
## pass create transaction
## pass query
## {"1":1}
## pass job2
# start job 2
## pass create transaction
## pass query
## {"1":1}
## pass job2
# start job 2
## pass create transaction
## pass query
## {"1":1}
## pass job2
# start job 2
## pass create transaction
## pass query
## {"1":1}
## pass job2
all jobs done

Somehow, beginTransaction() methods are doing magic stuff I guess. So, we know how to avoid error on this situation, but don't have any clue why this things are occurred, and what is difference between start transaction and beginTransaction()

Is there any hint or explanations, please let us know. We're very worried at this issue because 'maybe this is not actual solution nor actual problem. there might be something more big DB issues are exist. we just don't know yet'.

Thankyou.

[rusher]

there is a difference between your 2 jobs : the failing one is pipelining result.

...
.then( ( conn ) => {
                    conn.query('start transaction')
                        .catch( e => reject( `[createTransaction] failed to start transaction : ${e}` ) );
                    return conn;
                } )

changing that to

.then( async ( conn ) => {
                    await conn.query('start transaction'));
                    return conn;
                } )

will probably solve the problem.

Pipelining is a good thing, it permit better performance, but ProxySQL doesn't seem to support it.

to explain. With your current implementation, in term of exchanges here is what is done :	driver		DB server
send 'start transaction'	--->
send 'select 1'	--->
	<--	send 'start transaction' result
	<--	send 'select1' result

this permit to avoid network latency, server immediatly executing 'select 1 after having finish to handle 'start transaction' adding 'await' keyword as you have done with job2, exchange will be different :

driver		DB server
send 'start transaction'	--->
	<--	send 'start transaction' result
send 'select 1'	--->
	<--	send 'select1' result

second command will be send to DB only when having received the first query response. Proxy probably stop working when receiving another command when first one one not completly handled.

[juicycool92]

Hello, @rusher Well that's embarrassing. I checked my createTransaction function and actually its look likes

createTransaction() { 
        return new Promise( ( resolve, reject ) => {
            this.createConn()
                .then( ( conn ) => {
                    conn.query('start transaction')
                        .then( resolve( conn ) )
                        .catch( e => reject( `[createTransaction] failed to start transaction : ${e}` ) );
                } )
                .catch( e => reject( `[createTransaction] failed to create Conn : ${e}` ) )
        } );
    }

I wrote example code incorrectly. my bad. And I realized that after I implement with beginTransaction() in real projects, problems are not gone away. But surprisingly, after read your reply and review my codes again, I figured out why kind of this error occurs. It was totally my code problems🤫.

Bottom line, I changes to all codes to beginTransaction(because much cleaner to look), get more insight from your kind explain reply.

Thankyou for your time!