[go: up one dir, main page]
Menu ▾ ▴

#427 Value '0000-00-00' is not valid for MySQL data type DATE

Development_Release
open
nobody
None
5
2016-06-17
2016-05-02
Wahyudi Arifandi
No

Following sql are not valid:
openemr/sql/2_8_2-to-2_8_3_upgrade.sql (1 hit)
Line 116: MODIFY date date NOT NULL DEFAULT '0000-00-00',
openemr/sq/database.sql (5 hits)
Line 950: last_notify date NOT NULL default '0000-00-00',
Line 1015: last_notify date NOT NULL default '0000-00-00',
Line 2506: date date NOT NULL default '0000-00-00',
Line 4785: pc_eventDate date NOT NULL default '0000-00-00',
Line 4786: pc_endDate date NOT NULL default '0000-00-00',

Date value '0000-00-00' does not work in MySQL 5.5.49-win32.
It could be fixed by modifying them into '1000-01-01' as min date for MySQL.
If keep using '0000-00-00', then change the data type become TIMESTAMP insteda of DATE

Discussion

  • Rod Roark

    Rod Roark - 2016-05-02

    I believe that whether 0000-00-00 is allowed is a MySQL option. See the documentation for your MySQL version regarding NO_ZERO_DATE and perhaps also "strict mode". It's likely a matter of updating my.ini (Windows) or my.cnf (*nix).

     

  • Oleg Sverdlov

    Oleg Sverdlov - 2016-05-16
    • Group: Release_4.2.1 --> Development_Release
     

  • Kevin Yeh

    Kevin Yeh - 2016-05-16

    The NO_ZERO_DATE was deprecated in MySQL 5.6 and does nothing in 5.7.

    If true support for transactions is a requirement (compatibility with STRICT_TRANS_MODE=on)
    , then references to 0000-00-00 would need to be addressed. Either by switching to TIMESTAMP or by using a different default value. How the calendar would treat 1000-01-01 is not something I'm sure about, and there are probably other instances where dates are used with 0000-00-00 that aren't just for the calendar.

    There are many other instances throughout the codebase that are incompatible with strict transaction that would need to be addressed which would require thorough analysis and testing. TEXT NOT NULL columns is an issue I vaguely remember encountering in the past when experimenting with strict transactions.

     

  • Oleg Sverdlov

    Oleg Sverdlov - 2016-05-16

    I believe that for the following tables: amendments, amendments_history, batchcom , we can replace 0000-00-00 with CURRENT_TIMESTAMP without touching the code.

    insurance_data will require a small fix in the code.

    drugs and drug_inventory will require a fix.

    Replacing zeros in openemr_postcalendar_events requires a fix.

    There's an INSERT INTO automatic_notification clause that needs to be changed as well.

    example_patient_data needs to be changed.

     

    Last edit: Oleg Sverdlov 2016-05-16

  • Daniel Pflieger

    Daniel Pflieger - 2016-06-14

    I am thinking that Oleg has the best idea. If there is no disagreement, we can go ahead and make these changes and have them submitted to the community. Are there any objections or unforseen problems that haven't been mentioned?

     

    Last edit: Daniel Pflieger 2016-06-14

Log in to post a comment.