MantisBT - Pending Requests
View Issue Details
0000138Pending RequestsCore Infrastructurepublic2009-04-18 07:082016-12-26 10:25
Reporterjacknyu 
Assigned To 
PrioritynormalSeverityminorReproducibilityalways
StatusnewResolutionopen 
PlatformOSOS Version
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.
child of 0000191new  Cross Database Support 
Attached Files

Notes
(0000215)
eureka   
2009-04-18 07:55   
which kind of incompatibility?
(0000216)
jacknyu   
2009-04-18 08:05   
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   
2009-04-18 08:23   
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   
2009-04-19 18:21   
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.

Issue History
2009-04-18 07:08jacknyuNew Issue
2009-04-18 07:55eurekaNote Added: 0000215
2009-04-18 08:05jacknyuNote Added: 0000216
2009-04-18 08:23jacknyuNote Added: 0000217
2009-04-19 18:21caseydkNote Added: 0000218
2009-04-19 18:21caseydkProjectv1.0 Release (Closed) => Pending Requests
2009-06-14 06:18caseydkRelationship addedchild of 0000191
2010-01-31 22:34caseydkCategory-- none specified -- => Core Infrastructure
2014-08-16 18:28caseydkCategoryGeneral => Core Infrastructure
2014-11-15 21:32caseydkProjectPending Requests => v4.0 Release (Planning)
2016-12-26 10:25caseydkProjectv4.0 Release (Planning) => Pending Requests