Cannot use more than one query in on_connect.sql with MySQL

[nanawel]

Introduction

Using on_connect.sql as described in the documentation does not seem to work if more that one query is used.

To Reproduce

✔️ Working

select 1;

❌ Not working

select 1;
select 2;

Actual behavior

App logs:

[INFO  sqlpage::webserver::database::connect] Connecting to database: mysql://user:password@myhost/mydb
[INFO  sqlpage::webserver::database::connect] Creating a custom SQL database connection handler from "/var/www/sqlpage/on_connect.sql"
[ERROR sqlx_core_oldapi::pool::inner] error returned from after_connect: Database(MySqlDatabaseError { code: Some("42000"), number: 1064, message: "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 'select 2' at line 33" })

Expected behavior

One should be able to use any number of properly terminated queries in the on_connect.sql bootstrap file.

Version information

• Database: MySQL 8
• SQLPage Version: 0.15.2

[lovasoa]

Indeed, the on_connect file contents are passed as is to the database, without SQLPage parsing and splitting it.

This allows you to use the raw database syntax, but limits you to what your database accepts, and MySQL accepts a single statement by query by default.

You can work around that by using a user-defined function.

[nanawel]

Unfortunately that's a no-go for what I intended to do where I cannot write a single persistent piece of data on the remote server.
I was trying to build a dev tool to inspect rapidly a running production database where I can only create temporary tables and execute SELECT statements.
Here I was using on_connect.sql to "persist" common data into a temporary table that I could then reuse on every page.

I managed to partially work around this by turning my CREATE TEMP TABLE + INSERT INTO to a single CREATE TABLE ... SELECT ... but it stills means I can only use it once, and for a single record.

[lovasoa]

Oh, I get it, the limitation is indeed annoying. I'll look into whether there is a way to bypass MySql single statement limitation.

Why would CREATE ... SELECT be limited to a single record?

Out of curiosity, what is it you are building with SQLPage ?

[nanawel]

Why would CREATE ... SELECT be limited to a single record?

Well, that's not entirely true, but having to chain UNION does not really help readability when those data are hardcoded and not the results of a SELECT against the database.

Out of curiosity, what is it you are building with SQLPage ?

As written above, a dev tool to inspect rapidly a running production database. I hope it could help me gather interesting data quickly, and be able to look at them whenever I need through a cute webGUI.
Currently I use dBeaver with a pile of SQL queries I often reuse but it lacks the user-friendliness of a dedicated tool.

[lovasoa]

MySQL's documentation gives the following example:

CREATE TABLE tvx
    SELECT (x,z) FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v(x,y,z);

https://dev.mysql.com/doc/refman/8.0/en/create-table-select.html

[nanawel]

Right! I didn't know this syntax. Thanks for the tip.

Anyway, eventually I'll probably need several tables to meet my needs, so this does not completely solve the problem.

[lovasoa]

By querying the database differently, we should be able to support multiple statements in on_connect even in MySQL 🎉

I pushed a fix, it should be automatically published as sqlpage:main on docker hub soon. Can you give it a try, @nanawel ?