Default / Empty Values - SQL Error. Changed behavior ?

Questions related to using nuBuilder Forte.

Default / Empty Values - SQL Error. Changed behavior ?

Postby hagie » Wed May 13, 2020 11:15 pm

Hi,

I just created a test form with 3 Fields on it. If I add a entry and don't fill in all 3 fields I get a SQL ERROR. Here I only fill in field 1 and 2:

Code: Select all
===PDO MESSAGE===

SQLSTATE[HY000]: General error: 1364 Field 'field01' doesn't have a default value

===SQL===========

INSERT INTO test         (test_id, `field00`, `field02`)  VALUES ('5ebbf910018af47', '1', '2.00');


This is right because all fields in the mysql (mariadb) are marked no null and no default.

Is there a changed behavior how empty fields are treated / inserted into a database ? Do I need to initialize all fields on creation to some kind of default , even empty value ?

Thanks
Stefan
hagie
 
Posts: 20
Joined: Fri Dec 16, 2016 9:21 pm

Re: Default / Empty Values - SQL Error. Changed behavior ?

Postby kev1n » Wed May 13, 2020 11:24 pm

Do you get the same error when executing the same SQL directly in phpMyAdmin ?
kev1n
 
Posts: 784
Joined: Mon Oct 15, 2018 2:13 am

Re: Default / Empty Values - SQL Error. Changed behavior ?

Postby hagie » Wed May 13, 2020 11:52 pm

kev1n wrote:Do you get the same error when executing the same SQL directly in phpMyAdmin ?


Yes - the error is correct. The DB-Server runs in strict mode (default for mysql / mariadb) the table is created (automatically by creating a test fast form within nubuilder) with "NO DEFAULT" and "NOT NULL" no explicit default values here. If I now create a new record and just leave a filed blank, the SQL query don't set the field which must lead to an SQL Error.

Stefan
hagie
 
Posts: 20
Joined: Fri Dec 16, 2016 9:21 pm

Re: Default / Empty Values - SQL Error. Changed behavior ?

Postby kev1n » Thu May 14, 2020 12:14 am

Null should be = Yes
Default = NULL

(For all columns but the primary key)

db_table.png
db_table.png (15.53 KiB) Viewed 266 times
kev1n
 
Posts: 784
Joined: Mon Oct 15, 2018 2:13 am

Re: Default / Empty Values - SQL Error. Changed behavior ?

Postby hagie » Thu May 14, 2020 12:31 am

kev1n wrote:Null should be = Yes
Default = NULL

(For all columns but the primary key)

The attachment db_table.png is no longer available


My Table looks different - it's NO / None:

table.png
table.png (65.61 KiB) Viewed 264 times


Server version: 10.3.22-MariaDB-1ubuntu1 - Ubuntu 20.04

The Table was created just by added a FastForm - I did not touch the Tabel in any way.

Stefan
hagie
 
Posts: 20
Joined: Fri Dec 16, 2016 9:21 pm

Re: Default / Empty Values - SQL Error. Changed behavior ?

Postby kev1n » Thu May 14, 2020 12:34 am

You need to click on change and set the default to NULL for each column but the primary key.
The problem is that Fast Form doesn't set DEFAULT NULL when creating the table.
kev1n
 
Posts: 784
Joined: Mon Oct 15, 2018 2:13 am

Re: Default / Empty Values - SQL Error. Changed behavior ?

Postby kev1n » Thu May 14, 2020 12:41 am

I modified the function nuBuildNewTable() in nubuilders.php so that DEFAULT NULL is set for each field.
You can exchange the original file with the one in the zip archive.

This means when you create other froms/tables with Fast Forms, default null is set.
Attachments
nubuilders.zip
(3.02 KiB) Downloaded 11 times
kev1n
 
Posts: 784
Joined: Mon Oct 15, 2018 2:13 am

Re: Default / Empty Values - SQL Error. Changed behavior ?

Postby hagie » Thu May 14, 2020 12:44 am

kev1n wrote:You need to click on change and set the default to NULL for each column but the primary key.
The problem is that Fast Form doesn't set DEFAULT NULL when creating the table.


I know it's possible to change the settings, but wouldn't it better if the creation of the Table would take care of this. Maybe it wasn't an issue until now but later Versions of mariadb enable strict mode by default. This wasn't the case in earlier Versions. Maybe nuBuilder should set up Tables with proper default settings now.

I will try to disable strict mode - this should also help.

Stefan
hagie
 
Posts: 20
Joined: Fri Dec 16, 2016 9:21 pm

Re: Default / Empty Values - SQL Error. Changed behavior ?

Postby kev1n » Thu May 14, 2020 12:47 am

hagie wrote:I know it's possible to change the settings, but wouldn't it better if the creation of the Table would take care of this. Maybe it wasn't an issue until now but later Versions of mariadb enable strict mode by default. This wasn't the case in earlier Versions. Maybe nuBuilder should set up Tables with proper default settings now.


Yes I agree. See my patch above.
kev1n
 
Posts: 784
Joined: Mon Oct 15, 2018 2:13 am

Re: Default / Empty Values - SQL Error. Changed behavior ?

Postby hagie » Thu May 14, 2020 1:08 am

kev1n wrote:
I know it's possible to change the settings, but wouldn't it better if the creation of the Table would take care of this. Maybe it wasn't an issue until now but later Versions of mariadb enable strict mode by default. This wasn't the case in earlier Versions. Maybe nuBuilder should set up Tables with proper default settings now.


Yes I agree. See my patch above.


Yeah I just found this by narrow my search term :

viewtopic.php?f=19&t=9697&hilit=set+default

Looks like it was solved almost 2 years ago but why is it reoccurring ? And who is Admin

Sorry for cause you extra work ..

P.S: Maybe Nubuilder should check the variable on the Database Server and Display a warning for those who try to build upon old Databases / Tables , even if new one now created with working default / empty flag.
hagie
 
Posts: 20
Joined: Fri Dec 16, 2016 9:21 pm

Next

Return to General



cron