I am trying to run a script to drop all the tables from the database before sequelize syncs via sequelize.sync({ force: true });
The script runs with no problems when I run it from the console, the problem happens when I try to run it from my node.js application; MySql returns a parse error.
var dropAllTables = [
'SET FOREIGN_KEY_CHECKS = 0;',
'SET GROUP_CONCAT_MAX_LEN = 32768;',
'SET @tables = NULL;',
"SELECT GROUP_CONCAT('`', table_name, '`') INTO @tables FROM information_schema.tables WHERE table_schema = (SELECT DATABASE());",
"SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);",
"SELECT IFNULL(@tables, 'SELECT 1') INTO @tables;",
'PREPARE stmt FROM @tables;',
'EXECUTE stmt;',
'DEALLOCATE PREPARE stmt;',
'SET FOREIGN_KEY_CHECKS = 1;',
"SET GLOBAL sql_mode = 'STRICT_ALL_TABLES';"
].join(' ');
sequelize.query(dropAllTables, {
raw: true
}).then(function() {
return sequelize.sync({ force: true });
}).then(function() {
console.log('Database recreated!');
callback();
}, function(err) {
throw err;
});
{ [Error: ER_PARSE_ERROR: 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 'SET GROUP_CONCAT_MAX_LEN = 32768; SET @tables = NULL; SELECT GROUP_CONCAT('`', t' at line 1]
code: 'ER_PARSE_ERROR',
errno: 1064,
sqlState: '42000',
index: 0,
sql: 'SET FOREIGN_KEY_CHECKS = 0; SET GROUP_CONCAT_MAX_LEN = 32768; SET @tables = NULL; SELECT GROUP_CONCAT(\'`\', table_name, \'`\') INTO @tables FROM information_schema.tables WHERE table_schema = (SELECT DATABASE()); SET @tables = CONCAT(\'DROP TABLE IF EXISTS \', @tables); SELECT IFNULL(@tables, \'SELECT 1\') INTO @tables; PREPARE stmt FROM @tables; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET FOREIGN_KEY_CHECKS = 1; SET GLOBAL sql_mode = \'STRICT_ALL_TABLES\';' }
I found nothing regarding multiple raw queries with sequelize in Google nor at sequelize docs page (I looked for a specific parameter for the query method).
I found this thread from an SO clone, where people seem to have the same problem but I can't figure out what the solution was.
You can pass the multipleStatements option using
new Sequelize(user, pass, db, {
dialectOptions: {
multipleStatements: true
}
});
Anything you put into dialectOptions will be passed on to the underlying connection lib (in this case mysql)
Depending on the underlying mysql module being used, at least mysql/mysql2 supports the multipleStatements: true connection setting. This will allow you to send multiple queries at once. By default it is disabled for security reasons.