NOW_UTC on beta

[mebeingken]

Wappler Version : beta 13
Operating System : mac
Server Model: node
Database Type: maria
Hosting Type:

Expected behavior

NOW_UTC should work on insert.

Actual behavior

I have the following test setup on Wapper 3.9

It inserts a record just fine and is something I’ve done numerous times.

Now in beta 13 I do the same test, to the same database:

I get the dreaded ER_TRUNCATED_WRONG_VALUE error:

{
  "status": "500",
  "code": "ER_TRUNCATED_WRONG_VALUE",
  "message": "insert into `junk` (`last_login_at`) values ('2021-07-17T21:04:43Z') - ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '2021-07-17T21:04:43Z' for column `mpp`.`junk`.`last_login_at` at row 1",
  "stack": "Error: ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '2021-07-17T21:04:43Z' for column `mpp`.`junk`.`last_login_at` at row 1\n    at Query.Sequence._packetToError (/Users/pauka/WapplerProjects/tbd/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)\n    at Query.ErrorPacket (/Users/pauka/WapplerProjects/tbd/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)\n    at Protocol._parsePacket (/Users/pauka/WapplerProjects/tbd/node_modules/mysql/lib/protocol/Protocol.js:291:23)\n    at Parser._parsePacket (/Users/pauka/WapplerProjects/tbd/node_modules/mysql/lib/protocol/Parser.js:433:10)\n    at Parser.write (/Users/pauka/WapplerProjects/tbd/node_modules/mysql/lib/protocol/Parser.js:43:10)\n    at Protocol.write (/Users/pauka/WapplerProjects/tbd/node_modules/mysql/lib/protocol/Protocol.js:38:16)\n    at Socket.<anonymous> (/Users/pauka/WapplerProjects/tbd/node_modules/mysql/lib/Connection.js:88:28)\n    at Socket.<anonymous> (/Users/pauka/WapplerProjects/tbd/node_modules/mysql/lib/Connection.js:526:10)\n    at Socket.emit (node:events:378:20)\n    at addChunk (node:internal/streams/readable:313:12)\n    --------------------\n    at Protocol._enqueue (/Users/pauka/WapplerProjects/tbd/node_modules/mysql/lib/protocol/Protocol.js:144:48)\n    at Connection.query (/Users/pauka/WapplerProjects/tbd/node_modules/mysql/lib/Connection.js:198:25)\n    at /Users/pauka/WapplerProjects/tbd/node_modules/knex/lib/dialects/mysql/index.js:134:18\n    at new Promise (<anonymous>)\n    at Client_MySQL._query (/Users/pauka/WapplerProjects/tbd/node_modules/knex/lib/dialects/mysql/index.js:128:12)\n    at Client_MySQL.query (/Users/pauka/WapplerProjects/tbd/node_modules/knex/lib/client.js:168:17)\n    at Runner.query (/Users/pauka/WapplerProjects/tbd/node_modules/knex/lib/runner.js:151:36)\n    at /Users/pauka/WapplerProjects/tbd/node_modules/knex/lib/runner.js:40:23\n    at /Users/pauka/WapplerProjects/tbd/node_modules/knex/lib/runner.js:277:24\n    at processTicksAndRejections (node:internal/process/task_queues:94:5)"
}

Both tests use the local wappler node server and Node v15.10 and point to the exact same db/table, so it seems all the variables have been removed.

[Teodor]

Hi Ken,
The datetime field of MySQL/Maria DB expects the date in a specific format and the value being sent by NOW_UTC is in different format.
Your database is not accepting it because it runs in strict mode.

please check:

Saving data to database causes error

Wappler Version : 4 beta 12 Operating System : Win 10 Server Model: NODE Database Type: MySql Hosting Type: WAMP Expected behavior Date value should save …YOUR TEXT HERE … Actual behavior Error flagged I have a login server action which is audited. This has worked perfectly over a few months of development where auditable actions are logged by calling a library API action as below [image] On installing Beta 12 i now get an error [image]

[mebeingken]

Can you think of why it works in 3.9 but not the beta?

I see now that it was a recent change.

I guess I’ll have to update all projects when bringing into version 4. That’s unfortunate.

[jellederijke]

Adding the date formatter to format ‘{{NOW_UTC.formatDate(‘yyyy-MM-dd HH:mm:ss’)}}’ (and thus removing the trailing z) fixed the error for me. Thanks for that fix

However, I do feel as if this issue should not need to come up.
I get the truncated wrong value error when I do this:

• Create a new project on Wappler 4.0.1
• NodeJS on Docker engine
• Database MySQL created within Wappler
• Create user according to @mebeingken’s tutorial on uniqueideas
• get incorrect datetime value error

Would it make more sense if Wappler formatted NOW_UTC in nodejs projects without the trailing Z by default?

[patrick]

Best way would be to have your server configured on the UTC timezone and then use the normal NOW. You need to set the timezone for NodeJS and for the Database (in most cases it just uses the system/os timezone).

Problem with for example MySQL is that it doesn’t support datetime fields with timezones, when you store a datetime it is treated as local time and MySQL internally converts it to UTC. So if you use NOW_UTC to UTC, it will be treated by MySQL as local time and it will also convert it to UTC. This normally is not a problem, when you retrieve the datetime again from the database it will be converted back to the local time, so you get the same datetime back what you have put in. Problems happens when later you decide to move the server into a different timezone, when you then retrieve the same datetime field it will be converted to new timezone and also you will get a different datetime from the database.

[jellederijke]

Thanks for the swift reply, @patrick. Let me check if I understand you correctly:

Are you saying that when I use NOW, instead of NOW_UTC for things like created_date and last-updated or whatever, the trailing z will not be added and thus the error will not occur?

[patrick]

Yes, NOW doesn’t have the trailing Z and can be safely used. It just returns the current datetime of the server, so setting your server timezone correctly it is the best way to use.

[jellederijke]

Thank you Patrick! I just tested it, and it works like a charm!