Mysql node insert operation unable to address the table in a db

Questions
1

Describe the problem/error/question

msedge_5bddSymeQx
msedge_5bddSymeQx1920×964 229 KB

  • Issue is to insert into a table in a database in mysql/mariadb (mysql has no schema).
  • drop down the table name in the list only shows the table from the default database/schema
  • specifying the fully qualified address to the table “db.table” in the “name” method generates syntax error

To replicate, use this SQL:

CREATE OR REPLACE TABLE `dw`.`test_table` (
  `id` int(10) unsigned NOT NULL
);

-- This generates syntax error
INSERT INTO `dw.test_table`
(`id`) 
VALUES 
(1),
(2);

-- This is OK
INSERT INTO `dw`.`test_table`
(`id`) 
VALUES 
(1),
(2);

select * from dw.test_table;

The database(or schema) dw needs to be individually quoted by back quotes.

Reference:
Identifier Qualifiers - MariaDB Knowledge Base

I suggest a field is made available to specify the database name in the mysql node.

What is the error message (if any)?

ERROR: INSERT command denied to user 'herman.tan'@'999.999.999.999' for table `mysql`.`db.table`
sql: INSERT INTO `db.table` (`id`) values ....

Please share your workflow

Share the output returned by the last node

msedge_5bddSymeQx
msedge_5bddSymeQx1920×964 229 KB

Information on your n8n setup

  • n8n version: 1.6.1
  • Database (default: SQLite): SQLite
  • n8n EXECUTIONS_PROCESS setting (default: own, main): default
  • Running n8n via (Docker, npm, n8n cloud, desktop app): desktop app
  • Operating system: windows
3

Hi @Herman_Tan, I am very sorry you are having trouble.

I was able to reproduce this on my side and shall add it to our engineering backlog for a closer look and fix.

In the meantime I was able to specify a different database by using a syntax like dw`.`test_table so n8n cam add the leading and trailing backticks without breaking the query:

image
image1214×880 41.9 KB

Hope this is a suitable workaround for you until the problem is fixed!