Fails on automated execution but works manually

Questions
1

Describe the issue/error/question

I have a workflow that I created that reads data from a MySQL database then inserts it into a Postgres database. When I execute the steps manually it works flawlessly, when I execute the flow as scheduled via trigger it fails.

The failure is due to a timestamp being converted somehow from a timestamp to a readable date but the input is a timestamp I even even tried forcing the input .toString()

image

image

What is the error message (if any)?

Problem in node ‘Upsert Orders‘

invalid input syntax for type timestamp: “Sun Nov 13 2022 16:15:41 GMT+0000 (Coordinated Universal Time)”

Please share the workflow

Information on your n8n setup

Running n8n via Docker Compose

2

Quick update, when I run it manually first time it fails with same error, on second run it works

3

I also checked the failed logs and NOTHING in the insert reflects the error

here is the insert preview

image
image1536×516 120 KB

and here is the error

image
image790×1092 154 KB

4

Hey @odarkthirty,

Welcome to the community :cake:

It looks like the format is just wrong but without seeing the actual data being used it is hard to say, I know that sometimes dates can do some funky things depending on if it is a date object or a string.

5

It is a date object coming in from MySQL the JSON just it just as a string but somehow it is being converted is there a way to control that?

6

Hey @odarkthirty,

Not that I can think of, we should just be sending what is there, What version of n8n are you running?

7

@Jon

Running 0.202.1

The input is clearly accurate, the output is not when I run node manually EVERYTHING works fine but when I run through workflow it gets modified, I have tried everything to get that date to switch. Also what is odd is that postgres is pretty smart it will take a lot and convert but the parser is the one kicking it back.

Here is a sample input data:

[{
  "ID": 28965,
  "post_author": 2,
  "post_date": "2022-11-15T14:15:18.000Z",
  "post_date_gmt": "2022-11-15T18:15:18.000Z",
  "post_content": "",
  "post_title": "Order – November 15, 2022 @ 02:15 PM",
  "post_excerpt": "",
  "post_status": "wc-completed",
  "comment_status": "closed",
  "ping_status": "closed",
  "post_password": "",
  "post_name": "order-november-15-2022-0217-pm",
  "to_ping": "",
  "pinged": "",
  "post_modified": "2022-11-17T16:20:01.000Z",
  "post_modified_gmt": "2022-11-17T20:20:01.000Z",
  "post_content_filtered": "",
  "post_parent": 0,
  "menu_order": 0,
  "post_type": "shop_order",
  "post_mime_type": "",
  "comment_count": 14
}]

Run it manually no problem see below

image
image1309×738 107 KB

Try to run it via automation and it fails, and notice the date change

image
image1357×806 133 KB

I have no date manipulations anywhere, I even tried to force toString or use Luxon to convert, same problem. Running manually works great, trying to execute workflow fails

If I remove that column everything works fine

8

Hey @odarkthirty,

You are not wrong there what is in the expression looks to be solid and that is what I would expect to see passed to the database as well.

What I will do is pop together a mock postgres table and see if I can reproduce the same issue using the output data you have provided, I have taken a look at the nodes code and we just fire off whatever is in that box to the database so it could be that there is something funky when we evaluate the expression.

9

@Jon

Thanks, I tried doing it all from scratch and doing just the insert, same thing even in a new workflow

I can try to roll back n8n a few versions back and see what happens, let me do that back and report

10

@Jon also noticed a new version, so I will upgrade, try if it doesn’t work, roll back 5 tags see what happens

11

@Jon also I tried to even force the expression still doesn’t work

DateTime.fromISO($json["post_modified"]).toISO()

12

@Jon

Latest version 0.203.1 - same issue

Rolled back all the way to Version 0.193.1

Same problem

image

13

@Jon I think the answer will lie in why manually executing it works but automated doesn’t, seems there is a difference in how n8n handles those two

14

Manual and production executions should behave exactly the same. What will not behave identical is partial manual executions as they will reuse the data present in the frontend which got stringified.

15

@jan I should be more specific. If I click EXECUTE from the node it works, if I click EXECUTE workflow from the workflow screen it doesn’t work and produces an error, thus production fails but executing node by node works

16

@jan and to be more specific clicking EXECUTE NODE works, EXECUTE WORKFLOW does not and produces the date error

I can’t not figure out why n8n is making the date change, clearly something somewhere is causing a change and this is similar to another users issue so it seems to span any “SQL” type service - SQL DateTime format not recognized

Even forcing toString or converting the date and converting it back doesn’t work

17

Something is very amidst here, I changed timestamp field to varchar and it is clear n8n is altering the incoming JSON which is a timestamp and converting the date to a string, this happens when clicking EXECUTE WORKFLOW

Ironically if I just execute the note this DOES NOT happen and it reverts to standard timestamp

Using EXECUTE WORKFLOW

image

Using EXECUTE NODE

image

I am kind of surprised no one else has ran into this

18

Also wrong behavior happens on trigger events same thing, wrong transformation

19

@jan @Jon

So for shittes and wiggles I decided to just put {{DateTime.now().toISO()}} and see what happens, this is supposed to output to ISO insetad what I got was this Tue Nov 08 2022 19:56:41 GMT+0000 (Coordinated Universal Time) inserted into the DB

So n8n is 100% evaluating expression on actual workflow execution and instead over ridding to a .now, I have been digging into the code to see if I can find where this is happening

20

@jan @Jon

Alright so I dug through the code and found in the generic functions file n8n/GenericFunctions.ts at 479644a499627be052e1acd8bfdcc56d261ba0ee · n8n-io/n8n · GitHub the way that n8n was sorting through dates was actually making a new Date function FROM a date as a STRING and then going toISOString, so I decided lets do the same, I changed my code to be

{{new Date($json[‘post_date_gmt’].toString()).toISOString()}}

And like magic that worked now the output is accurate i.e 2022-11-18T20:31:59.000Z

Seems like a very big work around for no reason, if n8n would just pass it instead of trying to evaluate the expression or at least ignore on .toString life would have been simpler :man_shrugging:

took a week to solve this one, glad I found at least a workable work around

1 Like