Page 1 of 2
Default / Empty Values - SQL Error. Changed behavior ?
Posted: Wed May 13, 2020 3:45 pm
by hagie
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
Re: Default / Empty Values - SQL Error. Changed behavior ?
Posted: Wed May 13, 2020 3:54 pm
by kev1n
Do you get the same error when executing the same SQL directly in phpMyAdmin ?
Re: Default / Empty Values - SQL Error. Changed behavior ?
Posted: Wed May 13, 2020 4:22 pm
by hagie
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
Re: Default / Empty Values - SQL Error. Changed behavior ?
Posted: Wed May 13, 2020 4:44 pm
by kev1n
Null should be = Yes
Default = NULL
(For all columns but the primary key)
db_table.png
Re: Default / Empty Values - SQL Error. Changed behavior ?
Posted: Wed May 13, 2020 5:01 pm
by hagie
kev1n wrote:Null should be = Yes
Default = NULL
(For all columns but the primary key)
db_table.png
My Table looks different - it's NO / None:
table.png
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
Re: Default / Empty Values - SQL Error. Changed behavior ?
Posted: Wed May 13, 2020 5:04 pm
by kev1n
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.
Re: Default / Empty Values - SQL Error. Changed behavior ?
Posted: Wed May 13, 2020 5:11 pm
by kev1n
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.
Re: Default / Empty Values - SQL Error. Changed behavior ?
Posted: Wed May 13, 2020 5:14 pm
by hagie
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
Re: Default / Empty Values - SQL Error. Changed behavior ?
Posted: Wed May 13, 2020 5:17 pm
by kev1n
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.
Re: Default / Empty Values - SQL Error. Changed behavior ?
Posted: Wed May 13, 2020 5:38 pm
by hagie
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 :
https://forums.nubuilder.cloud/viewtopic. ... et+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.