query error

[syco]

Hi I'm trying to get this to work on mariadb 10.11.4 on debian 12, but I get the following error, any help appreciated.

  express-mysql-session:log Creating sessions database table +73ms
  express-mysql-session:error Failed to create sessions database table. +0ms
  express-mysql-session:error SqlError: (conn=1297, no: 1064, SQLState: 42000) 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 ''sessions'? (
  express-mysql-session:error   'session_id'? varchar(128) COLLATE utf8mb4_bin NOT NULL,
  express-mysql-session:error   'e...' at line 1
  express-mysql-session:error sql: CREATE TABLE IF NOT EXISTS ?? (
  express-mysql-session:error   ?? varchar(128) COLLATE utf8mb4_bin NOT NULL,
  express-mysql-session:error   ?? int(11) unsigned NOT NULL,
  express-mysql-session:error   ?? mediumtext COLLATE utf8mb4_bin,
  express-mysql-session:error   PRIMARY KEY (??)
  express-mysql-session:error ) ENGINE=InnoDB
  express-mysql-session:error  - parameters:['sessions','session_id','expires','data','session_id']
  express-mysql-session:error     at module.exports.createError (/mnt/data/projects/siprotect/v2/portal/node/node_modules/mariadb/lib/misc/errors.js:61:10)
  express-mysql-session:error     at PacketNodeEncoded.readError (/mnt/data/projects/siprotect/v2/portal/node/node_modules/mariadb/lib/io/packet.js:572:19)
  express-mysql-session:error     at Query.readResponsePacket (/mnt/data/projects/siprotect/v2/portal/node/node_modules/mariadb/lib/cmd/parser.js:55:28)
  express-mysql-session:error     at PacketInputStream.receivePacketBasic (/mnt/data/projects/siprotect/v2/portal/node/node_modules/mariadb/lib/io/packet-input-stream.js:82:9)
  express-mysql-session:error     at PacketInputStream.onData (/mnt/data/projects/siprotect/v2/portal/node/node_modules/mariadb/lib/io/packet-input-stream.js:132:20)
  express-mysql-session:error     at Socket.emit (node:events:514:28)
  express-mysql-session:error     at addChunk (node:internal/streams/readable:324:12)
  express-mysql-session:error     at readableAddChunk (node:internal/streams/readable:297:9)
  express-mysql-session:error     at Readable.push (node:internal/streams/readable:234:10)
  express-mysql-session:error     at TCP.onStreamRead (node:internal/stream_base_commons:190:23) +0ms

Thanks.

[chill117]

This module does not officially support MariaDB, but it should work as long as you are using a version of MariaDB that is compatible with MySQL 5.7

Can you please post the code where you are using this module?

[syco]

This are the relevant pieces in my app.js

const session = require('express-session');
const MySQLStore = require('express-mysql-session')(session);

const ma_sql = require(path.join(__dirname, "libs", "db.ma.js"));
const sessionStore = new MySQLStore({}, ma_sql);

  app.use(session({
    cookie: {
      sameSite: true,
      secure: false
    },
    maxAge: 7200000,
    resave: false,
    rolling: true,
    saveUninitialized: false,
    secret: 'xxx',
    store: sessionStore
  }));

I believe the problem is that table names and column names cannot be wrapped in single/double quotes in mariadb. So when you use ?? to replace the table name and the columns, it build an invalid query.

Can I ask why you use '??' instead of '?' ? I'm not familiar with this syntax. Thanks

[syco]

Ok, I guess I found it: https://github.com/mariadb-corporation/mariadb-connector-nodejs/issues/108 in here they say ?? is not supported and needs to be done manually.

[chill117]

This module does not support custom MySQL node clients. Please use the mysql2's promise interface - e.g. from the usage section in the readme:

const mysql = require('mysql2/promise');
const session = require('express-session');
const MySQLStore = require('express-mysql-session')(session);

const options = {
    host: 'localhost',
    port: 3306,
    user: 'db_user',
    password: 'password',
    database: 'db_name'
};

const connection = mysql.createConnection(options); // or mysql.createPool(options);
const sessionStore = new MySQLStore({}/* session store options */, connection);

I believe the problem is that table names and column names cannot be wrapped in single/double quotes in mariadb. So when you use ?? to replace the table name and the columns, it build an invalid query.

Yes, the column and table names can (and should) be specified with double-question marks because MariaDB is theoretically compatible with MySQL.

Can I ask why you use '??' instead of '?' ? I'm not familiar with this syntax. Thanks

Double-question marks indicate that the value inserted should be escaped as an identifier - ie. a column or table name.