Why does wappler not like datetime in external created databases - Wappler rerwriting the MYSQL Mode?

StevenM · September 28, 2020, 1:46am

I can not save to a datetime in an a mysql database created outside Wappler with a datetime format.

I try and add a date NOW_UTC to a datetime field it formats:

Preexisting: '2020-09-30T01:36:01.000Z' not allowed! Why is it created a different value

I create a datetime on a datetime field created with Wappler:

Wappler created: '2020-09-28T01:36:01' ok

"insert into Account (Trial, confirmed, expiry, trial_time, type) values ('1', 'No', '2020-09-30T01:42:06.000Z', '2020-09-28T01:42:06', 'Admin') - ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '2020-09-30T01:42:06.000Z' for column 'expiry' at row 1"

StevenM · September 28, 2020, 5:05am

OK

This seems to be a an error created from other node applications and from a deep dive I see that the MYSQL defaults are set to:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

I can run the query to remove the strict format of the dates and times:

SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';
select @@sql_mode;

This results in:

 NO_ENGINE_SUBSTITUTION

This would then allow for the dates to be inserted without the error.

However, it appears that DEPLOY is rewriting the MYSQL MODE

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Can some one at Wappler @Teodor can someone at Wappler check this as it is driving me Nuts and there is clearly an issue here.

(MAC 10.15.7)

This seems to be a an error created from other node applications and from a deep dive I see that the MYSQL defaults are set to:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

I can run the query to remove the strict format of the dates and times:

SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';
select @@sql_mode;

This results in:

 NO_ENGINE_SUBSTITUTION

This would then allow for the dates to be inserted without the error.

However, it appears that a a server connect action to insert a date record is rewriting the SQL mode to

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Can some one at Wappler @Teodor eck this? it is driving me batty!

Once I change the SQL mode and then run the server action to insert the UTC_now date iI then get this error in SQL Shell. And a Wappler restart is required.

mysql> Error: This socket has been ended by the other party at Socket.writeAfterFIN [as write] (net.js:455:14) at p (chrome-extension://fjjllfpmlaejicjkpknbdicdakiijgjp/Shared/DMXzone/dmxAppCreator/UI/w2editor_ftp_manager.js:8:35434) at e.fire (chrome-extension://fjjllfpmlaejicjkpknbdicdakiijgjp/Shared/DMXzone/dmxAppCreator/UI/xterm/v4/xterm.js:1:1815) at chrome-extension://fjjllfpmlaejicjkpknbdicdakiijgjp/Shared/DMXzone/dmxAppCreator/UI/xterm/v4/xterm.js:1:2009 at e.fire (chrome-extension://fjjllfpmlaejicjkpknbdicdakiijgjp/Shared/DMXzone/dmxAppCreator/UI/xterm/v4/xterm.js:1:1815) at t.triggerDataEvent (chrome-extension://fjjllfpmlaejicjkpknbdicdakiijgjp/Shared/DMXzone/dmxAppCreator/UI/xterm/v4/xterm.js:1:283109) at t._keyDown (chrome-extension://fjjllfpmlaejicjkpknbdicdakiijgjp/Shared/DMXzone/dmxAppCreator/UI/xterm/v4/xterm.js:1:125160) at HTMLTextAreaElement. (chrome-extension://fjjllfpmlaejicjkpknbdicdakiijgjp/Shared/DMXzone/dmxAppCreator/UI/xterm/v4/xterm.js:1:111164)

StevenM · September 28, 2020, 5:22am

Edited: narrowed the MYSQL MODE rewrite to “Deploy”

This is why I was seeing it work some times and not others.

As soon as you deploy it rewrites back to strict mode.

patrick · September 28, 2020, 12:37pm

Not sure what you mean with Deploy, is it after applying changes from the Database Manager?

StevenM · September 28, 2020, 10:16pm

NODE project
Eternally created database
OSX 10.15.7

Change the MYSQL Mode to:

SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';
select @@sql_mode;

 NO_ENGINE_SUBSTITUTION

deploy

select @@sql_mode;

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Wappler rewrites the MYSQL mode of the database.

SQL Shell produces the error so doubt you will be able to the select statement.

video

Teodor · September 29, 2020, 7:03am

Is your database running on some other server? Or are you using the database server within docker?

StevenM · September 29, 2020, 7:39am

Hi @Teodor

Docker

Just means that database was not created in Wappler.

Used Navicat, but is connected to Wappler no database issues accessing query data / creating / deleting etc in wappler.

Teodor · September 29, 2020, 8:57am

I recreated this again. Setup the same server/database as on your screenshots:

Created a database table using Mysql Workbench (not using Wappler database manager) and added a DATETIME field there:

Then created a server action with an insert step, which inserts NOW_UTC:

The insert runs without any errors and the time is properly inserted (11:52 here, GMT +3):

And i didn’t have to alter anything related to mysql strict mode.

Can you explain how is your field defined in Navicat?
Are you sure you properly defined it when creating the database?

George · September 29, 2020, 9:09am

Yes @StevenM - you really shouldn’t hack any MySQL server settings. This will give you only a lot of troubles later on.

If there any problems with the dat fields - we just have to find out exactly - what is the difference.
In general if you just create everything with Wappler’s Visual Database Manager, you shouldn’t have any troubles.

But maybe you are using some very specific data types we aren’t aware of.

StevenM · September 29, 2020, 9:49am

I changed the server setting for MySQL mode because there was some chatter about issues with Node and MySQL date insertion. And seems it was resolved for many by removing strict mode from the database. Happy not to change anything at all but the date will not save no matter what I do unless I remove the strict mode. if you go to the top of this thread you can see the error I am getting you can also see that if I create a new datetime field in Wappler in the insert it shows as “type text” not datetime?.

I have nothing specific or unusual with the database it couldn’t get any more basic. But it will not save dates the the database. And I get the error right at the beginning.

No matter what I do it doesn’t work.

I really didn’t want to recreate a new database using Wappler because at the end of this project it will be hooking into an existing RDS MySQL database.

There is an error here and I am stumped it is just happening.

Teodor · September 29, 2020, 9:55am

As you can see from my screenshots i recreated the same as you did and the insert works perfectly fine, without fiddling with the mysql settings (which should not be required anyway).
Maybe show a screenshot of how exactly is your database set up in Navicat and what is selected for your db fields in the table.

StevenM · September 29, 2020, 10:12am

Ok I will in the morning (Australia time) thanks

StevenM · September 30, 2020, 12:12am

Hi @Teodor

So I did a few test today I ran the same server action to save the date in a php project on the same database and there was not problem saved and no errors.

I tested in node/js version again same version and the error occurred.

"insert into Account ( Trial , confirmed , expiry , trial_time , type ) values ('1', 'No', '2020-09-30T01:42:06.000Z', '2020-09-28T01:42:06', 'Admin') - ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '2020-09-30T01:42:06.000Z' for column 'expiry' at row 1"

It is clearly the database has an issue with the node.js and the way it saves date time formats to MySQL.

From what I can tell Node.js changes the date time to a string and is why in the Wappler query builder for a node project it shows a node datetime as "text" string?

This maybe concerning because if I have an existing php app with a mysql database what changes need to happen to be able to work with Wappler node.js

I have attached the .sql export file for the test I am using (nothing in here that can't be shared) so you can create the tables as I have them and so you can see the settings.

Anyway, maybe you see something I can't!

Thanksqualback_2020-1.sql.zip (1.5 KB)

StevenM · September 30, 2020, 1:36am

Run a server action to insert the following.

I get the error:

XHRPOSThttp://localhost:8100/api/Signup
[HTTP/1.1 500 Internal Server Error 94ms]

status	"500"
code	"ER_TRUNCATED_WRONG_VALUE"
message	"insert into `Account` (`confirmed`, `expiry`, `type`) values ('No', '2020-10-02T01:33:34.000Z', 'Trial') - ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '2020-10-02T01:33:34.000Z' for column 'expiry' at row 1"
stack	"Error: ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '2020-10-02T01:33:34.000Z' for column 'expiry' at row 1\n at Query.Sequence._packetToError (/opt/node_app/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)\n at Query.ErrorPacket (/opt/node_app/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)\n at Protocol._parsePacket (/opt/node_app/node_modules/mysql/lib/protocol/Protocol.js:291:23)\n at Parser._parsePacket (/opt/node_app/node_modules/mysql/lib/protocol/Parser.js:4…lects/mysql/index.js:135:18\n at new Promise (<anonymous>)\n at Client_MySQL._query (/opt/node_app/node_modules/knex/lib/dialects/mysql/index.js:129:12)\n at Client_MySQL.query (/opt/node_app/node_modules/knex/lib/client.js:169:17)\n at Runner.query (/opt/node_app/node_modules/knex/lib/runner.js:134:36)\n at /opt/node_app/node_modules/knex/lib/runner.js:40:23\n at /opt/node_app/node_modules/knex/lib/runner.js:260:24\n at processTicksAndRejections (internal/process/task_queues.js:97:5)"

StevenM · September 30, 2020, 2:47am

I also testing and blew away my database and created a new one in Wappler and created the fields in Wappler and still getting the error:

creted Wappler

Insert value for 'expiry':

NOW.dateAdd('days', 2)

ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '2020-10-02T02:37:18.000Z' for column 'expiry' at row 1

Change the insert for:

NOW only And no error.

It is inserting

.000Z

To the end of the date time and this is the issue the 2020-10-02T03:59:41.000Z should be 2020-10-02T03:59:41 but it is not being formatted before it hits the database.

.000 is the fraction of a second and Z indicates UTC timezone. but its not being accepted when you run the insert statement NOW.dateAdd('days', 2)

There is an issue here!! I have spent days on this now and don't know how clearer I can be.

Teodor · September 30, 2020, 5:57am

Steven,
It’s really hard to follow what are you doing, testing and reporting. Now you are trying to insert {{NOW.dateAdd('days', 2)}} … before that it was just {{NOW_UTC}}
You are mentioning PHP and I thought we are referring to NodeJS and MySQL on Docker. Really you should just report the issue you are having straight to the point in one single post. We can’t understand which of all your posts are just some of your experiments and which are the things you just want to build.

If I understand you correctly (which I already doubt) you just need to format your date if you are using: NOW.dateAdd('days', 2) and receive this error.

So just use the format date formatter and add:

NOW.dateAdd('days', 2).formatDate('yyyy-MM-dd HH:mm:ss')

And please when reporting an issue don’t post 10 more additional posts which have nothing to do with your actual question or problem.

StevenM · September 30, 2020, 6:16am

And please when reporting an issue don’t post 10 more additional posts which have nothing to do with your actual question or problem.

Do you know how hard it is to report a problem when the person who is reading it continues to state there isn't a problem when clearly there is. This is ridiculous do you know how many hours I spent on this over the last two days because Wappler is crashing, reporting this errors and all sorts of issue.

You told me to update you and provide database details FFS.

Wappler has issues and your vilification and condemnation of me spending days and late hours trying to help you identify it is both abusive, bullying and frankly is offensive!

Learn some manners.

Teodor · September 30, 2020, 6:22am

As you can clearly see from my screenshots from yesterday using your previously reported attempts to insert NOW_UTC we were unable to recreate your issue on the same project configuration as yours, and it works perfectly fine. And that's the reason we needed to see the configuration of your database fields.

Today you state you want to insert some other value like NOW.dateAdd('days', 2) and you also mention several different things not actually mentioned in your initial report.

Hope you understand it's hard to follow random bits of info and tests placed one after another in the topic.

Wappler has issues and your vilification and condemnation of me spending days and late hours trying to help you identify it is both abusive, bullying and frankly is offensive!

I'm sorry to hear this and that is not my idea.
For quickly finding a solution to the issues you guys report we need just quality bug/problem reports.

StevenM · September 30, 2020, 6:35am

Stand in my shoes for a moment (first forget everything you know about Wappler) you work on a single issue 56 hours and during this time you have MySQL errors, Wappler rewrites over the database MySQL mode, Console errors as shown in the video and date and time issues that can not be resolved using the tools available in Wappler. How am I to know it is not related, or not the same issue. I would have to spend hours reporting errors which might all be related to the one task but is solved with one line of code.

I would like to do something in Wappler in this project without having to report any issues really!! Because I know even though I try and I am a specific and detailed as possible and respond to instructions I am Just opening myself for ridicule.