Is there a way to run multiple statements/transaction/a sql script ?

[yw662]

I know according to the protocol mysql only accept one statement a time.

But is there a way to work around it ?
I tried

for(const q of script.split(';')) {
    await pool.query(q);
}

It usually works until I encountered sth. like

delimiter $$
create procedure foo
begin
select........;
end $$
delimiter ;

So, do I really need to find a way to handle this ?

[sidorares]

At protocol level it's "only one command at a time" and query command can contain multiple statements - see https://github.com/mysqljs/mysql#multiple-statement-queries

[yw662]

but the tricky thing is, that flag does not work when the delimiter change :-)

[sidorares]

In your case it's one single statement, and you don't need to set delimiter. See example here -

node-mysql2/test/integration/connection/test-binary-multiple-results.js

Lines 100 to 104 in 0c422dd

	'DROP PROCEDURE IF EXISTS _as_sp_call;',
	'CREATE PROCEDURE _as_sp_call()',
	'BEGIN',
	sql + ';',
	'END'

[yw662]

But what if I read it from a sql script ?
sth like

drop table if exists foo
create table foo (
......

drop procedure if exists bar;
delimiter $$
create procedure bar
(......
begin
-- a lot of statements --
end $$
delimiter ;

[sidorares]

afaik you just use part without delimiter:

conn.query('drop procedure if exists bar');
conn.query(`
create procedure bar
(......
begin
-- a lot of statements --
end
`);

[terrisgit]

I inherited code that does something like Promise.all( connection.query(), connection.query() )

The SQL statements don't do anything tricky like commit or rollback, so as long as connection.query() "locks" the connection until the result set is loaded into memory from the server, I guess this will work.

The code is written this way because the Promise collection is generated by passing a callback to Array.map() that returns a Promise. Therefore, multiple connection.query()'s run at the same time, more or less.

So far we have not seen any issues but is this supported usage? Is it advantageous any way to

await connection.query(...);
await connection.query(...); ?

[sidorares]

Is it advantageous any way to
await connection.query(...);
await connection.query(...); ?

no, they'll be executed sequentially anyway. There might be a benefit of doing

await Promise.all([pool.query(...), pool.query(...)])

• that way both queries are actually might run in parallel because they are likely sent from different connections

[terrisgit]

Thanks! For our multiuser app, we need database connections to be available for incoming requests and therefore can't allow one request to hog connections not to mention the entire mysql server with a swarm of work. Also, for one request, there can only be one database transaction that is rolled back upon failure. Therefore we use synchronous SQL commands for individual API requests. Just giving mysql2 users something to think about when they stumble across this conversation.

[drwharris]

I am at a loss. I have tried every which way to make this work but cant.

DELIMITER $$
CREATE PROCEDURE `archive_events`()
BEGIN
    DECLARE max_id INT DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        ROLLBACK;
        DROP TEMPORARY TABLE tmp_ids;
    END;

    main:LOOP
        CREATE TEMPORARY TABLE tmp_ids AS SELECT event_id FROM event WHERE event_date < DATE_ADD(NOW(), INTERVAL -151 DAY) ORDER BY event_id LIMIT 1000;
        SELECT MAX(event_id) INTO max_id FROM tmp_ids;
        IF ISNULL(max_id) THEN
            DROP TEMPORARY TABLE tmp_ids;
            LEAVE main;
        END IF;
        
        START TRANSACTION;
            DELETE e FROM event e JOIN tmp_ids t ON e.event_id = t.event_id;
            COMMIT;
        DROP TEMPORARY TABLE tmp_ids;
    END LOOP;
END$$
DELIMITER ;

If I remove the DELIMITER statements then the ";" cause the connection to think there are multiple statements. If I remove the ";" then the statement isn't valid SQL.

Any suggestions?

[terrisgit]

Creating stored procedures with mysql2 is atypical. Have you considered db-migrate? Be sure to omit DELIMITER statements in db-migrate scripts.

This might be helpful: https://stackoverflow.com/questions/47369349/how-does-one-create-a-stored-procedure-in-node-js-using-the-mysql-library

[yw662]

If you are executing a script from file and have to stick to the sql script syntax, like what I did, you may need to handle delimiter lines and process the script manually.

It is like, first split the script by ';', then search in each statement for delimiter lines, then replace the delimiter to ';' and remove delimiter line.

[sidorares]

@terrisgit what do you mean by atypical? mysql2 is a relatively low level driver and only supports syntax that mysql server understands. I guess most confusion comes from differences that mysql cli adds by itself on the client before sending to the server

[terrisgit]

@sidorares I meant that creating a stored procedure as part of a one-time migration is orders of magnitude more common than creating them during normal application activity. That said, yes, you could implement that using mysql2.

I think @drwharris needs to disable the multipleStatements option when connecting (and of course not using the DELIMITER statements). It's unclear how you are creating the connection.

[drwharris]

    let statement = -> loaded from file <-
    let dbConnection = {
        host: myHost,
        user: myUsername,
        password: myPassword,
        waitForConnections: true,
        connectionLimit: 1,
        queueLimit: 1,
        database: dbName
    };
    db = await mysql.createConnection(dbConnection);
    await db.query(statement);

I can set the statement to be as follows (removing the delimiter lines) but it then complains about the semi colons (It thinks its multi-statement?). If I remove the semi colons its complains about invalid SQL at the 2nd declare statement.

CREATE PROCEDURE `archive_events`()
BEGIN
    DECLARE max_id INT DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        ROLLBACK;
        DROP TEMPORARY TABLE tmp_ids;
    END;

    main:LOOP
        CREATE TEMPORARY TABLE tmp_ids AS SELECT event_id FROM event WHERE event_date < DATE_ADD(NOW(), INTERVAL -151 DAY) ORDER BY event_id LIMIT 1000;
        SELECT MAX(event_id) INTO max_id FROM tmp_ids;
        IF ISNULL(max_id) THEN
            DROP TEMPORARY TABLE tmp_ids;
            LEAVE main;
        END IF;
        
        START TRANSACTION;
            DELETE e FROM event e JOIN tmp_ids t ON e.event_id = t.event_id;
            COMMIT;
        DROP TEMPORARY TABLE tmp_ids;
    END LOOP;
END

[drwharris]

Would it not be possible to have a "raw" option to just pass a statement to the DB and let it to its job without client side parsing, etc?

[yw662]

Would it not be possible to have a "raw" option to just pass a statement to the DB and let it to its job without client side parsing, etc?

"without client side parsing"
Actually the delimiter is totally done by client side parsing and mysql server know nothing about it.

[drwharris]

OK I think i solved it. You need to terminate each line with a \r\n

[JaoodxD]

@drwharris could you please provide minimal example?

[drwharris]

@drwharris could you please provide minimal example?

4y on? One of my comments had exactly what I was trying to do.

[JaoodxD]

@drwharris could you please provide minimal example?

4y on? One of my comments had exactly what I was trying to do.

Yes, this is still an issue.
Could you clarify what you meant by saying 'terminate each line with a \r\n'?
I thought each line in a text file is already terminated with this sequence by default on Windows and with '\n' on unix-like systems.