Anonymous Login
2019-07-22 19:38 PDT

View Issue Details Jump to Notes ]
IDProjectCategoryView StatusLast Update
0000138Pending RequestsCore Infrastructurepublic2016-12-26 10:25
Reporterjacknyu 
Assigned To 
PrioritynormalSeverityminorReproducibilityalways
StatusnewResolutionopen 
Product Version 
Target VersionFixed in Version 
Summary0000138: Default datatime value '0000-00-00'
DescriptionWhen posting data to the database, the default value for datatime is '0000-00-00' instead of NULL.

This is OK with MYSQL, but not compatible with most of other application or mysql gui tool.

TagsNo tags attached.
Attached Files

-Relationships
child of 0000191new Cross Database Support 
+Relationships

-Notes

~0000215

eureka (reporter)

which kind of incompatibility?

~0000216

jacknyu (reporter)

Example, on table TASKS, the default for task_created, task_updated is '0000-00-00 00:00:00'.

While it is not a problem with php, it causes conversion exception for Java(JDBC). Also, it causes reject while importing data to other database like Oracle.

~0000217

jacknyu (reporter)

This is actually a problem with mysql, http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html, not Web2Project.

However, removing the default value on the DDL, then using NOW(), CURDATE(), CURTIME() function on the INSERT should eliminate it.

I do a quick search on the sql and found the following:

001_base_install.mysql.sql: `forum_create_date` datetime default '0000-00-00 00:00:00',
001_base_install.mysql.sql: `forum_last_date` datetime default '0000-00-00 00:00:00',
001_base_install.mysql.sql: `message_date` datetime default '0000-00-00 00:00:00',
001_base_install.mysql.sql: `history_date` datetime NOT NULL default '0000-00-00 00:00:00',
001_base_install.mysql.sql: `project_created` datetime NOT NULL default '0000-00-00 00:00:00',
001_base_install.mysql.sql: `project_updated` datetime NOT NULL default '0000-00-00 00:00:00',
001_base_install.mysql.sql: `project_end_date_adjusted` datetime NOT NULL default '0000-00-00 00:00:00',
001_base_install.mysql.sql: `session_created` datetime NOT NULL default '0000-00-00 00:00:00',
001_base_install.mysql.sql: `task_created` datetime NOT NULL default '0000-00-00 00:00:00',
001_base_install.mysql.sql: `task_updated` datetime NOT NULL default '0000-00-00 00:00:00',
001_base_install.mysql.sql: `date_time_in` datetime default '0000-00-00 00:00:00',
001_base_install.mysql.sql: `date_time_out` datetime default '0000-00-00 00:00:00',
001_base_install.mysql.sql: `date_time_last_action` datetime default '0000-00-00 00:00:00',
001_base_install.mysql.sql: `last_db_update` date NOT NULL default '0000-00-00',
001_base_install.mysql.sql: `last_code_update` date NOT NULL default '0000-00-00',

~0000218

caseydk (administrator)

Since we current don't support Oracle and don't have anything to do with JDBC and this default set for quite a while (years?) with no issue, I'm going to downgrade this to a non-critical post release issue.
+Notes

-Issue History
Date Modified Username Field Change
2009-04-18 07:08 jacknyu New Issue
2009-04-18 07:55 eureka Note Added: 0000215
2009-04-18 08:05 jacknyu Note Added: 0000216
2009-04-18 08:23 jacknyu Note Added: 0000217
2009-04-19 18:21 caseydk Note Added: 0000218
2009-04-19 18:21 caseydk Project v1.0 Release (Closed) => Pending Requests
2009-06-14 06:18 caseydk Relationship added child of 0000191
2010-01-31 22:34 caseydk Category -- none specified -- => Core Infrastructure
2014-08-16 18:28 caseydk Category General => Core Infrastructure
2014-11-15 21:32 caseydk Project Pending Requests => v4.0 Release (Planning)
2016-12-26 10:25 caseydk Project v4.0 Release (Planning) => Pending Requests
+Issue History