Unable to execute multiple lines of MySQL commands

[kotori2]

Describe the bug
Syntax error when I trying to execute multiple lines of MySQL commands.

libc++abi.dylib: terminating with uncaught exception of type drogon::orm::SqlError: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SHOW TABLES' at line 2

To Reproduce

auto cli = drogon::orm::DbClient::newMysqlClient("blahblah");
cli->execSqlSync(R"EOF(
SHOW TABLES;
SHOW TABLES;
)EOF");

Expected behavior
SQL command executed without error or the framework could provide another multi line SQL interface.

Screenshots
NA

Desktop (please complete the following information):
NA

Smartphone (please complete the following information):
NA

Additional context

% mysql --version
mysql  Ver 8.0.19 for osx10.15 on x86_64 (Homebrew)

Dragon v1.1.0 release

[an-tao]

This is duplicated with the issue #582. should we find a resolution for this? @rbugajewski

[rbugajewski]

I’m not sure. I think we got this issue about 2–3x in the last couple of months, so people definitely have some need for running multiple SQL commands at once. But on the other side I wonder if for all of these issues, just splitting the commands wasn’t perfectly fine.

Maybe we should update the docs to more explicitly mention this behavior?

[rbugajewski]

@kotori2 What was your specific use case? The example you attached to this issue seems to be made up for the sake of simplification. Do you do something in your code that would be easily possible by splitting up multiple commands?

[kotori2]

@rbugajewski My use case is to automatically handle database structure initialization / upgrade, I just execute a 500 lines SQL files to create all tables.
Of course I can split them into several commands, but I think it's relatively less efficient? I just need to execute commands and no response parsing required. Not sure if MySQL provide interface for multi-line SQL command tho.

[rbugajewski]

@kotori2 I see, thanks for the explanation.

I think such functionality would greatly fit into a database migration plugin for Drogon, but there will be changes necessary in the framework, too. The plugin would have to model the transitive transitions between database model versions (migration to newer version & rollback to older version).