Error from mysqldb on new n8n instance

Questions
1

Hi,
I’ve been working with N8N with many of the ways offered,
i chose mysql and it seemed to work good, on my docker-compose env and on my companies dev everything was fine,
once i pushed it to another environment with different DB i get this as soon as i hit save button:

n8n_1  | ERROR RESPONSE
n8n_1  | ResponseError: cannot update entity because entity id is not set in the entity.
n8n_1  |     at throwDuplicateEntryError (/usr/local/lib/node_modules/n8n/dist/src/WorkflowHelpers.js:217:11)
n8n_1  |     at processTicksAndRejections (internal/process/task_queues.js:93:5)
n8n_1  |     at async /usr/local/lib/node_modules/n8n/dist/src/Server.js:332:36
n8n_1  |     at async /usr/local/lib/node_modules/n8n/dist/src/ResponseHelper.js:86:26 {
n8n_1  |   httpStatusCode: 400
n8n_1  | }

i tried to consult with our DB team but they seemed to not understand, i’ve tried to recreate the DB tables but the issue persists, is there any way n8n keeps metadata somewhere in the DB that is not it’s own table? i’m very confused.
is there a chance there is a need for special permissions ? is there a place to see all permissions needed in order to work with N8N ? :exploding_head:
Thanks

2

This sounds like the database isn’t quite what n8n is expecting.

I have not come across this very error but seeing it is thrown by throwDuplicateEntryError, I wonder if you might have started with a non-empty database (e.g. copied some content from your old instances to the new environment)?

When migrating to a new PostgreSQL database last year I would need to call setval to get valid IDs (this is the post: How to migrate from SQLite to PostgreSQL? - #5 by MutedJam, it’ll use Postgres functions though so isn’t 1:1 transferable to MySQL).

4

Hey @Raggingpuppies,

Are you this error when starting up n8n or when saving / importing a workflow? Is your dev and production instances of n8n using the same version as well? I don’t think this is a DB permission issue or anything like that.

I know if you copy a trigger between workflows it can sometimes cause problems so I am thinking something similar might be happening here.

1 Like
5

@MutedJam
I dont have information migrated, it simply starting a container, creating the migration and starting the service.
once service is started (after migration) i enter the web dashboard and add 1 node (no matter which) and click save, once i hit the save button i get this error.
@jon
maybe i ran a test on this DB before, but i deleted all tables, i dont need the data…

6

When you say creating the migration what do you mean? What version of n8n are you running as well?

7

Which migration did you create? Could you drop the database and start from scratch by re-creating it without running any migrations? n8n should create the required schema automatically.

1 Like
8

@Jon @MutedJam
Thanks for the reply,
version FROM n8nio/n8n:0.142.0
When you spawn an instance of n8n and it connects to Database and the tables does not exists, it is running “migration” which creates the tables in the database, nothing i made it is OOTB with most frameworks,
the above issue is after dropping table and starting the service from scratch, no actions preformed other than starting the service and saving a workflow.

adding full log:

n8n_1  | Initializing n8n process
n8n_1  | 
n8n_1  | n8n now checks for new versions and security updates. You can turn this off using the environment variable N8N_VERSION_NOTIFICATIONS_ENABLED to "false"
n8n_1  | For more information, please refer to https://docs.n8n.io/getting-started/installation/advanced/configuration.html
n8n_1  | 
n8n_1  | UserSettings were generated and saved to: /home/node/.n8n/config
n8n_1  | 
n8n_1  | 
n8n_1  | INFO: Started with migration for wait functionality.
n8n_1  |       Depending on the number of saved executions, that may take a little bit.
n8n_1  | 
n8n_1  | 
n8n_1  | n8n ready on 0.0.0.0, port 5678
n8n_1  | Version: 0.142.0
n8n_1  | 
n8n_1  | Editor is now accessible via:
n8n_1  | http://localhost:5678/
n8n_1  | ERROR RESPONSE
n8n_1  | ResponseError: cannot update entity because entity id is not set in the entity.
n8n_1  |     at throwDuplicateEntryError (/usr/local/lib/node_modules/n8n/dist/src/WorkflowHelpers.js:217:11)
n8n_1  |     at processTicksAndRejections (internal/process/task_queues.js:93:5)
n8n_1  |     at async /usr/local/lib/node_modules/n8n/dist/src/Server.js:332:36
n8n_1  |     at async /usr/local/lib/node_modules/n8n/dist/src/ResponseHelper.js:86:26 {
n8n_1  |   httpStatusCode: 400
n8n_1  | }```
9

Perfect, So between it saying the editor is available and the error occurring what are you doing?

10

Anything, changing workflow name-> clicking save or adding a node ->clicking save

Screen Shot 2021-11-02 at 16.02.28
Screen Shot 2021-11-02 at 16.02.282547×1282 117 KB

11

the above issue is after dropping table

So if this was working fine before dropping the table I wonder if MySQL might change (or not change) its sequences in some way causing this problem. Does the problem persist when dropping the entire database and connecting to a fresh one?

12

It’s just for this specific DB’s it never worked, for mysql on docker or mysql fresh untouched install it works good,
it’s just our production class servers, and i have no idea what can possibly be different between our dev and prod,
I just need a lead, because i truly don’t understand this error

13

I would expect a DB permission issue to fail to make the tables :thinking:

Are you able to add credentials? that is a another process that writes to the db. Could you pop on debug logging as well? That may show a bit more.

14

Same error on creds (debug on)

1  | 2021-11-02T14:40:34.780Z | info     | Initializing n8n process {"file":"start.js"}
n8n_1  | 2021-11-02T14:40:34.782Z | info     | 
n8n_1  | n8n now checks for new versions and security updates. You can turn this off using the environment variable N8N_VERSION_NOTIFICATIONS_ENABLED to "false"
n8n_1  | For more information, please refer to https://docs.n8n.io/getting-started/installation/advanced/configuration.html
n8n_1  |  {"file":"start.js"}
n8n_1  | UserSettings were generated and saved to: /home/node/.n8n/config
n8n_1  | 2021-11-02T14:40:36.040Z | debug    | No codex available for: N8nTrainingCustomerDatastore.node.js {"file":"LoadNodesAndCredentials.js","function":"addCodex"}
n8n_1  | 2021-11-02T14:40:36.040Z | debug    | No codex available for: N8nTrainingCustomerMessenger.node.js {"file":"LoadNodesAndCredentials.js","function":"addCodex"}
n8n_1  | 2021-11-02T14:40:37.769Z | debug    | Wait tracker querying database for waiting executions {"file":"WaitTracker.js","function":"getwaitingExecutions"}
n8n_1  | n8n ready on 0.0.0.0, port 5678
n8n_1  | Version: 0.142.0
n8n_1  | 
n8n_1  | Editor is now accessible via:
n8n_1  | http://localhost:5678/
n8n_1  | 2021-11-02T14:40:54.002Z | debug    | Add editor-UI session {"sessionId":"b9hnzxw517","file":"Push.js","function":"add"}
n8n_1  | ERROR RESPONSE
n8n_1  | ResponseError: cannot update entity because entity id is not set in the entity.
n8n_1  |     at throwDuplicateEntryError (/usr/local/lib/node_modules/n8n/dist/src/WorkflowHelpers.js:217:11)
n8n_1  |     at processTicksAndRejections (internal/process/task_queues.js:93:5)
n8n_1  |     at async /usr/local/lib/node_modules/n8n/dist/src/Server.js:332:36
n8n_1  |     at async /usr/local/lib/node_modules/n8n/dist/src/ResponseHelper.js:86:26 {
n8n_1  |   httpStatusCode: 400
n8n_1  | }
n8n_1  | ERROR RESPONSE
n8n_1  | TypeORMError: Cannot update entity because entity id is not set in the entity.
n8n_1  |     at new TypeORMError (/usr/local/lib/node_modules/n8n/node_modules/typeorm/error/TypeORMError.js:9:28)
n8n_1  |     at /usr/local/lib/node_modules/n8n/node_modules/typeorm/query-builder/ReturningResultsEntityUpdator.js:119:39
n8n_1  |     at Array.map (<anonymous>)
n8n_1  |     at ReturningResultsEntityUpdator.<anonymous> (/usr/local/lib/node_modules/n8n/node_modules/typeorm/query-builder/ReturningResultsEntityUpdator.js:113:46)
n8n_1  |     at step (/usr/local/lib/node_modules/n8n/node_modules/typeorm/node_modules/tslib/tslib.js:143:27)
n8n_1  |     at Object.next (/usr/local/lib/node_modules/n8n/node_modules/typeorm/node_modules/tslib/tslib.js:124:57)
n8n_1  |     at /usr/local/lib/node_modules/n8n/node_modules/typeorm/node_modules/tslib/tslib.js:117:75
n8n_1  |     at new Promise (<anonymous>)
n8n_1  |     at Object.__awaiter (/usr/local/lib/node_modules/n8n/node_modules/typeorm/node_modules/tslib/tslib.js:113:16)
n8n_1  |     at ReturningResultsEntityUpdator.insert (/usr/local/lib/node_modules/n8n/node_modules/typeorm/query-builder/ReturningResultsEntityUpdator.js:88:24)
n8n_1  |     at InsertQueryBuilder.<anonymous> (/usr/local/lib/node_modules/n8n/node_modules/typeorm/query-builder/InsertQueryBuilder.js:123:76)
n8n_1  |     at step (/usr/local/lib/node_modules/n8n/node_modules/typeorm/node_modules/tslib/tslib.js:143:27)
n8n_1  |     at Object.next (/usr/local/lib/node_modules/n8n/node_modules/typeorm/node_modules/tslib/tslib.js:124:57)
n8n_1  |     at fulfilled (/usr/local/lib/node_modules/n8n/node_modules/typeorm/node_modules/tslib/tslib.js:114:62)
n8n_1  |     at processTicksAndRejections (internal/process/task_queues.js:93:5)
n8n_1  | 2021-11-02T14:41:37.769Z | debug    | Wait tracker querying database for waiting executions {"file":"WaitTracker.js","function":"getwaitingExecutions"}
15

Might be worth temporarily increasing the MySQL log levels to see what the problem is: MySQL :: MySQL 8.0 Reference Manual :: 5.4 MySQL Server Logs

1 Like
16

Yeah, i guess that’s the only option, so… will take it with DB team and update back here, Thanks!

2 Likes
17

Hi all, My DB team solved the issue,
SQL Mode was enabled : NO_AUTO_VALUE_ON_ZERO
removing that solved the issue

2 Likes
18

Awesome, glad to hear you figured it out and thanks a lot for sharing! That’s a great learn.

1 Like
19

That is going into my notes for the future.

1 Like