[ASTPPCOM-357] 0000-00-00 is not a valid date. Set it to something more valid. Created: 01/Apr/18  Updated: 01/Apr/21  Resolved: 01/Apr/21

Status: Done
Project: ASTPP Community
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: New Feature
Reporter: Rob Thomas (Inactive) Assignee: Samir Doshi
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified


 Description   

This will probably need someone caring about it, because if anyone
ever tries to upgrade their MySQL version, I'm not actually sure what
will happen with invalid data like this. It probably will be worth testing.

Probably a good idea to have an upgrade script that updates everything in
the database as part of 3.6, and warn people that they can't upgrade
MySQL until this is run, because it'll eat their data.

Signed-Off-By: Rob Thomas <xrobau@gmail.com>
Signed-Off-By: Rob Thomas <rthomas@sangoma.com>



 Comments   
Comment by Rob Thomas (Inactive) [ 03/Apr/18 ]

The other option is to set it to NULL, rather than 0000. I've found a bunch of other issues with CDRs when things are missing, that it's probably better to use NULL rather than a random hardcoded date.

Comment by Rob Thomas (Inactive) [ 03/Apr/18 ]

You can see my first stab at it here: https://github.com/xrobau/ASTPP/commit/811e2ad6114160d196ed2cdd7ebbd4ed0b82e195#diff-2d3963eeca4f9b003b26dd9a83d1e8efR346 - but I'm not sure how you want to do it. Personally, I would generate the entire insert as a key => val array, and then use that to generate the SQL Insert.

Similar to this code: https://github.com/FreePBX/framework/blob/release/13.0/amp_conf/htdocs/admin/libraries/BMO/Freepbx_conf.class.php#L1036

Comment by Samir Doshi [ 04/Apr/18 ]

Got it but I realize the validate date patches will create a problem for existing users as their database tables already have records with 0000-00-00 00:00:00 date.

So If we need to replace it with validate date then need to plan about it.

On other hand I am wondering what difference it will make If we will start using 1980-01-01 00:00:00 inplace of 0000-00-00 00:00:00. At this moment, system will also working fine with 0000-00-00 00:00:00 format.

Comment by Rob Thomas (Inactive) [ 05/Apr/18 ]

0000-00-00 is no longer a valid datetime.

Unable to find source-code formatter for language: `. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 351989
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> connect asterisk
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Connection id:    351990
Current database: asterisk

MariaDB [asterisk]> create table x ( y datetime null );
Query OK, 0 rows affected (0.02 sec)

MariaDB [asterisk]> insert into x values ( '0000-00-00 00:00:00' );
Query OK, 1 row affected (0.01 sec)

MariaDB [asterisk]> drop table x;
Query OK, 0 rows affected (0.01 sec)

MariaDB [asterisk]> \q
Bye

`

But on 5.7

Unable to find source-code formatter for language: `. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml
[root@db-1 ~]# mysql freeswitch
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1677352
Server version: 5.7.21-20-57-log Percona XtraDB Cluster (GPL), Release rel20, Revision 1702aea, WSREP version 29.26, wsrep_29.26

Copyright (c) 2009-2018 Percona LLC and/or its affiliates
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table x ( y datetime null );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into x values ( '0000-00-00 00:00:00' );
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'y' at row 1
mysql> drop table x;
Query OK, 0 rows affected (0.01 sec)

mysql>

`

However, as I'm working through these bugs, I think it will be better to just remove the 'not null' restriction, and use NULL instead.

Generated at Sat Feb 10 07:16:24 CET 2024 using Jira 8.13.3#813003-sha1:22ebedbb75c99b147c66f14e031dd8a2d214753a.