pymysql.err.ProgrammingError 1064 in simple multiline SQL query for mariadb

Question

I have tried everything and keep getting this error:

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax;
check the manual that corresponds to your MariaDB server version for the right syntax to use near
'INSERT INTO tabSingles (doctype, field, value) VALUES ('Bank Reconciliation', 'a' at line 2")

Expanded query (after python format expansion):

SELECT value INTO @var FROM tabSingles WHERE doctype = 'Bank Reconciliation' AND field = 'bank_account';
INSERT INTO tabSingles (doctype, field, value) VALUES ('Bank Reconciliation', 'account', @var);
DELETE FROM tabSingles WHERE doctype = 'Bank Reconciliation' AND field = 'bank_account';

Can anyone see the problem? Is there some issue with multi-line queries? I have tried the individual lines on the mariadb command line and they appear to work as expected. I've also tried both frappe.db.sql and multisql (thought it meant multiline sql but doesn't). If I comment line 2 out, it also errors on line 3. Sorry to disturb but I've been staring at this for hours and cannot figure it out!

EDIT: The obvious answer is this, but I'd still like to know why it doesn't like the original query:

UPDATE tabSingles SET field='{new_name}' WHERE doctype='{doctype}' AND field='{old_name}';

Can you post an example of the python code that isn't working? FWIW, I would consider this 3 single-line queries, not a multi-line query. — totalhack, Jan 22 '20 at 00:41
@totalhack I could but I don't think it will help will it? The query in the post is a single string printed from python after format expansion as sent to the db. — racitup, Jan 22 '20 at 02:59

score 3 · Accepted Answer · answered Jan 22 '20 at 10:30

For security reasons (mainly SQL injection) MariaDB (and MySQL) servers don't support the execution of multiple SQL statements by default.

For supporting multiple statements execution the client needs to send COM_SET_OPTION command and MYSQL_OPTION_MULTI_STATEMENTS_ON flag to the server, which is not supported by PyMySQL.

score -1 · Answer 2 · answered Jan 26 '20 at 17:27

Do not try to run more than one statement in a call.

Do use BEGIN and COMMIT.

Do use FOR UPDATE.

You need 5 separate commands:

BEGIN;
SELECT ... FOR UPDATE;  -- to keep other connections from messing with the row(s).
UPDATE ...;
DELETE ...
COMMIT;   -- do all of the above "atomically"

pymysql.err.ProgrammingError 1064 in simple multiline SQL query for mariadb

2 Answers2