Default / Empty Values - SQL Error. Changed behavior ?

[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

[kev1n]

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

[hagie]

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

[kev1n]

Null should be = Yes
Default = NULL

(For all columns but the primary key)

db_table.png

[hagie]

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

[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.

[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.

[hagie]

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

[kev1n]

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.

[hagie]

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.