Bug #5390

During DB update if default value for DATETIME is outside of TIMESTAMP acceptable date range, it leads to error

Added by tomolimo almost 7 years ago. Updated almost 7 years ago.

Status:ClosedStart date:07/03/2015
Priority:NormalDue date:07/03/2015
Assignee:tomolimo% Done:

100%

Category:-
Target version:1.1.0

Description

Another issue: updatedb.php:

ALTER TABLE `glpi_plugin_ipam_histories`
MODIFY COLUMN `begin` TIMESTAMP NULL DEFAULT '1000-01-01 00:00:00' ,
MODIFY COLUMN `end` TIMESTAMP NULL DEFAULT '9999-12-31 23:59:59' ;
--> error when applying Invalid default value for 'begin'

MariaDB [datatrieve]> ALTER TABLE `glpi_plugin_ipam_histories`
-> MODIFY COLUMN `begin` TIMESTAMP NULL DEFAULT '1000-01-01
00:00:00' ,
-> MODIFY COLUMN `end` TIMESTAMP NULL DEFAULT '9999-12-31
23:59:59' ; --> error when applying Invalid default value for 'begin'
ERROR 1067 (42000): Invalid default value for 'begin'

The answer for this is straightforward:
https://dev.mysql.com/doc/refman/5.5/en/datetime.html =====
The DATE type is used for values with a date part but no time part.
MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The
supported range is '1000-01-01' to '9999-12-31'.

The DATETIME type is used for values that contain both date and time
parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD
HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to
'9999-12-31 23:59:59'.

The TIMESTAMP data type is used for values that contain both date and
time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to
'2038-01-19 03:14:07' UTC. ====

IE: you've tried to use DATETIME min/max values instead of TIMESTAMP ones

Do you have MAXDB mode enabled? =====
Note
The MySQL server can be run with the MAXDB SQL mode enabled. In this
case, TIMESTAMP is identical with DATETIME. If this mode is enabled at
the time that a table is created, TIMESTAMP columns are created as
DATETIME columns. As a result, such columns use DATETIME display format,
have the same range of values, and there is no automatic initialization
or updating to the current date and time. See Section 5.1.7, “Server SQL
Modes”. =====

To work around this, either:

1: maxdb would need to be enabled and the tables recreated

2: MODIFY COLUMN `begin` TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00' ,
MODIFY COLUMN `end` TIMESTAMP NULL DEFAULT '0000-00-00 00:00:0' ;

(0 is a special case)

3: MODIFY COLUMN `begin` TIMESTAMP NULL DEFAULT '1970-01-01 00:00:01' ,
MODIFY COLUMN `end` TIMESTAMP NULL DEFAULT '2038-01-19 03:14:07' ;

I believe that #2 is the best solution. Feel free to correct me.

History

#1 Updated by tomolimo almost 7 years ago

I'm going to implement the 3rd solution:

if( $column['COLUMN_DEFAULT'] == '0000-00-00 00:00:00' ){
    $defaultalter = " DEFAULT '0000-00-00 00:00:00'";
} elseif( $column['COLUMN_DEFAULT'] < '1970-01-01 00:00:01' ) {
    $defaultalter = " DEFAULT '1970-01-01 00:00:01'";
} elseif( $column['COLUMN_DEFAULT'] > '2038-01-19 03:14:07' ) {
    $defaultalter = " DEFAULT '2038-01-19 03:14:07'";
} else {
    $defaultalter = " DEFAULT '".$column['COLUMN_DEFAULT']."'";
}

#2 Updated by tomolimo almost 7 years ago

  • Status changed from New to Resolved

#3 Updated by tomolimo almost 7 years ago

  • Target version set to 1.1.0

#4 Updated by tomolimo almost 7 years ago

  • % Done changed from 0 to 100

#5 Updated by tomolimo almost 7 years ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF