Radicore Forum: Bug Reports » Error with date field

Home » RADICORE development » Bug Reports » Error with date field

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Error with date field [message #7796]

Tue, 28 February 2023 06:01

htManager
Messages: 433
Registered: May 2014

Senior Member

Hi Tony,

I have one more problem. If I want to delete a record with date columns, I get the error that the values aren't valid. See attached picture. The date columns are empty/null in dictionary and this is allowed. The value 0000-00-00 is automatically inserted.

Attachment: radicore date.jpg
(Size: 86.17KB, Downloaded 884 times)

Report message to a moderator

Re: Error with dictionary import [message #7797 is a reply to message #7796]

Tue, 28 February 2023 07:02

AJM
Messages: 2367
Registered: April 2006
Location: Surrey, UK

Senior Member

That is because 0000-00-00 is not a valid date. You need to either change it to a valid date or change it to NULL.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Error with dictionary import [message #7798 is a reply to message #7796]

Wed, 01 March 2023 12:30

htManager
Messages: 433
Registered: May 2014

Senior Member

I thought that I did this in the database. See attached picture.

If I change the 0000-00-00 to NULL, I can delete the record. But if I insert a new record, the value 0000-00-00 for this date field will be generated automatically.

In data dictionary the value for NULL? is NULL and the value for Default is empty.

Attachment: date null.jpg
(Size: 284.94KB, Downloaded 801 times)

Report message to a moderator

Re: Error with dictionary import [message #7799 is a reply to message #7798]

Wed, 01 March 2023 13:11

AJM
Messages: 2367
Registered: April 2006
Location: Surrey, UK

Senior Member

It was possible in earlier versions of MySQL to set the value '0000-00-00' as the default for date columns, but this is no longer possible. Does the definition of this column in the Data Dictionary still show '0000-00-00' as the default value? If it does then you must clear it.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Error with dictionary import [message #7800 is a reply to message #7799]

Wed, 01 March 2023 16:55

htManager
Messages: 433
Registered: May 2014

Senior Member

In data dictionary isn't a value 0000-00-00. I attached a picture.

Attachment: data dictionary date.jpg
(Size: 130.73KB, Downloaded 812 times)

Report message to a moderator

Re: Error with dictionary import [message #7801 is a reply to message #7800]

Thu, 02 March 2023 04:42

AJM
Messages: 2367
Registered: April 2006
Location: Surrey, UK

Senior Member

What does the <table>.dict.inc file contain? There is nothing in the framework code which would insert '0000-00-00' into a date field, so you need to step through with your debugger to see where it happens.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Error with dictionary import [message #7802 is a reply to message #7801]

Thu, 02 March 2023 12:22

htManager
Messages: 433
Registered: May 2014

Senior Member

That's what is in dict.inc:

$fieldspec['kontakt_ma_im_verein_seit'] = array('type' => 'date',
'size' => 12);

$fieldspec['kontakt_ma_vertrag_bis'] = array('type' => 'date',
'size' => 12);

I will do so, stepping through with the debugger.

Report message to a moderator

Re: Error with dictionary import [message #7803 is a reply to message #7802]

Fri, 03 March 2023 04:42

AJM
Messages: 2367
Registered: April 2006
Location: Surrey, UK

Senior Member

You might want to try searching through your code looking for where '0000-00-00' is actually mentioned.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Error with dictionary import [message #7804 is a reply to message #7803]

Tue, 07 March 2023 18:16

htManager
Messages: 433
Registered: May 2014

Senior Member

I stepped through the code with my debugger but I didn't find an expression 0000-00-00. I created a new table with ust a primary key and a field test_datum with type date. I had the same behaviour.

When debugging, I had a message in the variables that a record was inserted in the table. When I looked in the database, I saw the new record with value 0000-00-00. If I delete the value 0000-00-00, the record will be updated without problem and the value is NULL/empty.

Where else can I search?

Report message to a moderator

Re: Error with date field [message #7812 is a reply to message #7804]

Thu, 23 March 2023 10:51

htManager
Messages: 433
Registered: May 2014

Senior Member

I created a new table with the following specifications:

$fieldspec['id'] = array('type' => 'integer', 'type_native' => 'int','size' => 10, 'minvalue' => -2147483648, 'maxvalue' => 2147483647, 'pkey' => 'y', 'required' => 'y');
$fieldspec['test_datum'] = array('type' => 'date', 'size' => 12);

// primary key details
$this->primary_key = array('id');

When I insert a new record with my application, the line 1.631 in dml.mysqli.class.inc: $result = mysqli_query($this->dbconnect, $this->query); is responsible for inserting the record in my database with the right id and the value 0000-00-00 in the field 'test_datum', if I leave this empty in my screen. I examined the database table before and after executing this line. Before, the record wasn't visible/inserted, afterwards it was.

These variables shows my debugger before executing the line:

$this->query = INSERT INTO test_datum SET `id`='2', `test_datum`=''

client_info = mysqlnd 8.2.0
server_info = 10.4.27-MariaDB

Where else can I search for the problem? My debugger doesn't show any further information. I only can see the inserted record in the database table.

Report message to a moderator

Re: Error with date field [message #7813 is a reply to message #7812]

Fri, 24 March 2023 05:28

AJM
Messages: 2367
Registered: April 2006
Location: Surrey, UK

Senior Member

Nowhere in the framework does is set any date fields to the value '0000-00-00'. I have found two 'IF' statements in std.datevalidation.class.inc at line # 893 and std.validation.class.inc at line # 575, but that's all.

What is your CREATE TABLE script for thois table?

You could also turn on SQL logging for the task which adds records to this table. See Update Task for details.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Error with date field [message #7814 is a reply to message #7813]

Fri, 24 March 2023 11:40

htManager
Messages: 433
Registered: May 2014

Senior Member

What do you mean with CREATE TABLE script?

In the sql log file seems to be a correct statement:

SELECT count(*) FROM test_datum WHERE id='4' =>[S=16:04:04.68586, F=16:04:04.68612, E=0.00026]
INSERT INTO test_datum SET `id`='4', `test_datum`='' =>Count=1 [S=16:04:04.68661, F=16:04:04.68688, E=0.00026]

I changed the storage engine in phpMyAdmin from MyIsam to InnoDB without success.

Report message to a moderator

Re: Error with date field [message #7815 is a reply to message #7814]

Sat, 25 March 2023 05:27

AJM
Messages: 2367
Registered: April 2006
Location: Surrey, UK

Senior Member

The CREATE TABLE script is the script which you used to create the table in your database. Every table has one. You can use phpMyAdmin to export these details.

You need to log the SQL query which creates the record with '0000-00-00' in its date field. If the query generated by the framework does not contain this value then it can only be inserted by MySQL.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Error with date field [message #7816 is a reply to message #7815]

Sat, 25 March 2023 06:58

htManager
Messages: 433
Registered: May 2014

Senior Member

I made a dump of the table and got a sql file. I added this script (hopefully the right one).

As far as I see, the problem is obviously a SQL problem.

Executing "INSERT INTO test_datum SET `id`='4', `test_datum`='';" in phpMyAdmin results in the same behaviour. The date field has the value '0000-00-00'.

Do I have to change the sqlmode?

Attachment: test_datum.sql
(Size: 1.26KB, Downloaded 209 times)

Report message to a moderator

Re: Error with date field [message #7817 is a reply to message #7816]

Sat, 25 March 2023 08:02

htManager
Messages: 433
Registered: May 2014

Senior Member

If I change the sql_mode to " STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUB STITUTION " I get the error 1292. Value '' for field test_datum is wrong.

Report message to a moderator

Re: Error with date field [message #7818 is a reply to message #7817]

Sun, 26 March 2023 04:16

AJM
Messages: 2367
Registered: April 2006
Location: Surrey, UK

Senior Member

That is because you are trying to enter an empty string into a date field. You must either supply a valid date or NULL.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Error with date field [message #7819 is a reply to message #7818]

Sun, 26 March 2023 05:29

htManager
Messages: 433
Registered: May 2014

Senior Member

Yes, I know. I thought that if I set NULL as default in the table structure, this would be made automatically. I attached a picture of my table structure.

If I execute "INSERT INTO test_datum SET `id`='4', `test_datum`=NULL" instead of "INSERT INTO test_datum SET `id`='4', `test_datum`=''" the record will be inserted correctly without "0000-00-00".

Is there a setting to handle a blank value '' explecitly as NULL?

Attachment: Structure date.jpg
(Size: 42.42KB, Downloaded 797 times)

Report message to a moderator

Re: Error with date field [message #7820 is a reply to message #7819]

Mon, 27 March 2023 04:45

AJM
Messages: 2367
Registered: April 2006
Location: Surrey, UK

Senior Member

No, there is not. If your CREATE TABLE script states "DEFAULT NULL" then if you do not supply a value in an INSERT statement then MySQL will use that default value instead. If you want to remove the value in an update then you must explcity set it to NULL, not ''.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Error with date field [message #7821 is a reply to message #7820]

Mon, 27 March 2023 05:18

htManager
Messages: 433
Registered: May 2014

Senior Member

I tried. I tried it in the _cm_pre_insertRecord() and in the _cm_post_insertRecord() method with the following code:

if ($fieldarray['test_datum'] == '') {
$fieldarray['test_datum'] = NULL;
}

Without success. The DBMS inserts '0000-00-00'.

The only way to handle this behaviour at the moment is to insert the record and then to delete the '0000-00-00' values manually.

This happens in my local xampp installation, but in my internet server installation as well.

It isn't obviously a Radicore problem. So I think I will wait for a new version of mysql ...

Report message to a moderator

Re: Error with date field [message #7822 is a reply to message #7821]

Tue, 28 March 2023 04:40

AJM
Messages: 2367
Registered: April 2006
Location: Surrey, UK

Senior Member

I would suggest that you ask this question in a MySQL forum. You might want to check the MySQL manual in The DATE, DATETIME, and TIMESTAMP Types where it says:
Quote:

Invalid DATE, DATETIME, or TIMESTAMP values are converted to the "zero" value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00'), if the SQL mode permits this conversion. The precise behavior depends on which if any of strict SQL mode and the NO_ZERO_DATE SQL mode are enabled; see Section 5.1.11, "Server SQL Modes".

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Error with date field [message #7823 is a reply to message #7822]

Fri, 31 March 2023 12:49

htManager
Messages: 433
Registered: May 2014

Senior Member

I posted the problem in the german MySQL forum but I didn't get an answer yet.

At the moment, I found a solution: I set the $fieldarray['foobar'] to '' in the _cm_pre_updateRecord() and _cm_pre_deleteRecord() methods. This works.

Report message to a moderator

Re: Error with date field [message #7824 is a reply to message #7823]

Sat, 01 April 2023 04:50

AJM
Messages: 2367
Registered: April 2006
Location: Surrey, UK

Senior Member

This is a temporary hack, not a proper solution. What SQL mode are you using in your MySQL server? You can check this with the following query:

SELECT @@SESSION.sql_mode

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Error with date field [message #7825 is a reply to message #7824]

Sat, 01 April 2023 09:00

htManager
Messages: 433
Registered: May 2014

Senior Member

I use NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION

Report message to a moderator

Re: Error with date field [message #7826 is a reply to message #7825]

Sun, 02 April 2023 04:43

AJM
Messages: 2367
Registered: April 2006
Location: Surrey, UK

Senior Member

When you say Quote:

I set the $fieldarray['foobar'] to '' in the _cm_pre_updateRecord() and _cm_pre_deleteRecord() methods

does this mean that this date field already contains the value '0000-00-00'. If it does then you need to investigate where it is coming from. Is it coming from the database or the HTML form?

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Error with date field [message #7827 is a reply to message #7826]

Sun, 02 April 2023 05:29

htManager
Messages: 433
Registered: May 2014

Senior Member

Yes, the date field already has the value '0000-00-00'.

I think it comes from the database. In phpMyAdmin the SQL command INSERT INTO test_datum SET `id`='4', `test_datum`='' results in the value '0000-00-00' and the command INSERT INTO test_datum SET `id`='4', `test_datum`= NULL results in a blank
value.

Report message to a moderator

Re: Error with date field [message #7828 is a reply to message #7827]

Mon, 03 April 2023 04:37

AJM
Messages: 2367
Registered: April 2006
Location: Surrey, UK

Senior Member

That is because an empty string is being treated as an invalid date which is then being converted to '0000-00-00'. I have tried the same query on my PC and the query fails instead of inserting '0000-00-00'. You have two problems:

Your code should not be trying to insert an empty string into a date field.
The MySQL query should fail if you do.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Error with date field [message #7829 is a reply to message #7828]

Mon, 03 April 2023 09:09

htManager
Messages: 433
Registered: May 2014

Senior Member

In the past (php < 7 and mySQL < Cool

Cool

inserting an empty value wasn't obviously a problem. Could this be a bug in mySQL?

How do you handle a date field which will only be used when a certain event occurs?

I can set the default value to '1000-01-01' or whatever to avoid the error messages of '0000-00-00'. And what I do not understand is, that after inserting the record and then deleting the values '0000-00-00', there is no problem and no error messages. The mySQL database has no problem with date fields with empty value ''.

Report message to a moderator

Re: Error with date field [message #7830 is a reply to message #7829]

Tue, 04 April 2023 04:44

AJM
Messages: 2367
Registered: April 2006
Location: Surrey, UK

Senior Member

If a date field is only valid when certain conditions are met then it should be nullable (not defined with NOT NULL). I am using MySQL version 8.0.32 with SQL_MODE set to

ANSI_QUOTES,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION

and this prevents me from setting a date field to '' instead of NULL.

As I said in an earlier post, if you do not have NO_ZERO_IN_DATE,NO_ZERO_DATE set then inserting an invalid date (such as an empty string) MySQL will instead replace it with '0000-00-00'.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Error with date field [message #7835 is a reply to message #7830]

Wed, 05 April 2023 09:48

htManager
Messages: 433
Registered: May 2014

Senior Member

Thank you very much for fixing the problem. With the new version 2.26.0 everything works fine again.

Report message to a moderator

Previous Topic:	PHP 8 - ListViewPrintAfter()
Next Topic:	File classes/mnu_chart.class.inc is missing

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

] [

PDF

]

Current Time: Thu Nov 21 04:58:26 EST 2024

Total time taken to generate the page: 0.01880 seconds