NextJS Mysql runs fine via phpmyadmin but has issues querying via api

Question

As the title says, my SQL code works in phpmyadmin but not when requesting from the API. My API uses the mysql package:

npm i mysql

SQL code:

BEGIN;
INSERT INTO Addresses (address, postal_code, city, state, country)
    VALUES('Something', 69420, 'Something', 'Something', 'Something');
SELECT LAST_INSERT_ID() INTO @mysql_address_id;
INSERT INTO Companies (owner, org_nr, name)
    VALUES('someid', 133769420, 'Something');
SELECT LAST_INSERT_ID() INTO @mysql_company_id;
INSERT INTO Users (company, member, administrator)
    VALUES(@mysql_company_id, 'someid', 1);
INSERT INTO CompanyDetails (shippingAddress, company, shortName, ourReference)
    VALUES(@mysql_address_id, @mysql_company_id, 'Something', 'Something');
COMMIT;

This is completely normal in phpmyadmin.

我使用以下方式调用函数：

db.query(
          `BEGIN; INSERT INTO Addresses (address, postal_code, city, state, country) VALUES('Something', 69420, 'Something', 'Something', 'Something'); SELECT LAST_INSERT_ID() INTO @mysql_address_id; INSERT INTO Companies (owner, org_nr, name) VALUES('Something', 133769420, 'Something'); SELECT LAST_INSERT_ID() INTO @mysql_company_id; INSERT INTO Users (company, member, administrator) VALUES(@mysql_company_id, 'someid', 1); INSERT INTO CompanyDetails (shippingAddress, company, shortName, ourReference) VALUES(@mysql_address_id, @mysql_company_id, 'Something', 'Something'); COMMIT;`,
          (err, result) => {
            if (err) return res.status(422).json(err);
            return res.status(200).json(result);
          }
        );

显示的错误是：

{"code":"ER_PARSE_ERROR","errno":1064,"sqlMessage":"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 Addresses (address, postal_code, city, state, country) VALUES('Ve...' at line 1","sqlState":"42000","index":0,"sql":"BEGIN; INSERT INTO Addresses (address, postal_code, city, state, country) VALUES('Something', 69420, 'Something', 'Something', 'Something'); SELECT LAST_INSERT_ID() INTO @mysql_address_id; INSERT INTO Companies (owner, org_nr, name) VALUES('someid', 133769420, 'Something'); SELECT LAST_INSERT_ID() INTO @mysql_company_id; INSERT INTO Users (company, member, administrator) VALUES(@mysql_company_id, 'someid', 1); INSERT INTO CompanyDetails (shippingAddress, company, shortName, ourReference) VALUES(@mysql_address_id, @mysql_company_id, 'Something', 'Something'); COMMIT;"}

有人知道这可能是什么原因引起的吗？任何帮助将不胜感激 :)

Answer 1

I found the solution here: https://stackoverflow.com/a/23267627/13214923 It turns out that node-mysql does not support multiple statements by default.

By adding

{multipleStatements: true,}

into my connection pool configuration and it worked like magic :)