Unable to insert into ... select with change from mysql 8.0.28 to 8.0.31

[Alan Hicks]

When performing an insert into … select there is an error:

SQLException: Cursor statement must be a SELECT

The statement causing the exception is:

INSERT INTO dbmail_messages (mailbox_idnr,physmessage_id,seen_flag,answered_flag,deleted_flag,flagged_flag,recent_flag,draft_flag,unique_id,status) SELECT 1,physmessage_id,seen_flag,answered_flag,deleted_flag,flagged_flag,1,draft_flag,'57243c0de2605866c301a90e7e2c6ebd',status FROM dbmail_messages WHERE message_idnr = 8;

The C code is approximately:

Connection_beginTransaction(c);

r = Connection_executeQuery(c, "%s", (const char *)query);

Connection_lastRowId(c)

You can see the code and changes to avoiding this bug on GitHub:
https://github.com/dbmail/dbmail/pull/174/commits/3c93216afaa43096a9651658a1b6649a1d6f1def#diff-aa22d9b5fbaccc9fc2d35745d029b332ab3c64dd6254a91d9debd16b1b12e049

And the issue with details and logs
https://github.com/dbmail/dbmail/issues/175

This is also with a build of libzdb from latest source.

[Tildeslash]

Something must have changed then in MySQL if it worked before. Hard to say without investigating this version of MySQL. Anyway, there are a few caveats in your example, Connection_executeQuery is meant to return a result-set, i.e. from a select statement. The example above is an insert-into-select statement which does not return a result-set and hence using Connection_executeQuery is not the correct choice, instead Connection_execute should be used instead. Maybe this simple change will fix the problem? Libzdb uses a different cursor type for these two calls.

[Tildeslash]

From the Github repo it seems you solved this by breaking up the statement into a prepared statement which is fine. However, if you look at the original code

if (db_params.db_driver == DM_DRIVER_ORACLE) {
            db_exec(c, "INSERT INTO %smessages

where db_exec is a wrapper around Connection_execute, but in the else part db_query is used which is a wrapper around Connection_executeQuery and is is not correct. It should be db_exec here also.

[Alan Hicks]

I’ll refactor this code as postgresql uses Connection_executeQuery as it adds returning id to the sql so it makes more sense to keep the two separate. Joys of multi-db support, most grateful you make it as easy as you do.

[Tildeslash]

Ah yes, Postgres, sigh. I recall Paul had to do some hops like this to get the last inserted id etc. Probably explains why and also that it “accidental” worked in MySQL until it didn’t :-)