The following function is being called for each POST request to an endpoint:
exports.resetall = async (req, res, next) => {
let conn;
try {
conn = await pool.getConnection({multipleStatements: true});
await conn.query(`
SET FOREIGN_KEY_CHECKS = 0;
SELECT @str := CONCAT('TRUNCATE TABLE ', table_schema, '.', table_name, ';')
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema = DATABASE();
PREPARE stmt FROM @str;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1;
`);
res.status(200).json({
status: "OK"
});
} catch(err) {
return next(err);
} finally {
if(conn) conn.end();
}
};
Its purpose is to truncate all tables in the connected database.
However, the following SQL error occurs:
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 'SELECT @str := CONCAT('TRUNCATE TABLE ', table_schema, '.', table_name)\n ...' at line 3
This happens only when running the SQL script above from within Node.js. Using any other SQL client, it works as it should.
I've tried a bunch of things like removing the semicolons, using DELETE FROM instead of TRUNCATE TABLE, removing newlines, removing the SET statements..
Nothing works.
EDIT
For those asking, here is how the connection pool is created:
const config = {
http: {
host: "127.0.0.1",
port: 9103
},
mariadb: {
host: "127.0.0.1",
port: 3306,
user: "intelliq",
password: "",
database: "intelliq"
}
};
module.exports = config;
const mariadb = require(`mariadb`);
const config = require(`../config`);
const pool = mariadb.createPool(config.mariadb);
module.exports = pool;