Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to run a script to modify a database, this query

ALTER TABLE cmContentVersion CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

gives this error

ERROR 1067 (42000) at line 68: Invalid default value for 'modifiedDateTime'
Warning (Code 1264): Out of range value for column 'modifiedDateTime' at row 1
Error (Code 1067): Invalid default value for 'modifiedDateTime'

the column modifiedDateTime is defined as

`modifiedDateTime` datetime NOT NULL default '0000-00-00 00:00:00',

Question: Is there a valid "invalid" default value that can replace 0000-00-00 00:00:00 when using utf8_general_ci. Or do I have to tell the developers to stop relying on an "invalid date" in their code?

share|improve this question
add comment

1 Answer 1

up vote 7 down vote accepted

This looks like more of a SQL mode issue than the char set. Strict mode (and more specifically NO_ZERO_DATE which is part of strict mode) usually sets off that error if the table was created with an all zero default date before turning on strict.

What we use (for similar modified date columns), is the '1970-01-01 00:00:01'.

and on a slightly related note, we use timestamp for these columns (and for created time too). Takes half the storage space, and is faster access.

share|improve this answer
1  
NO_ZERO_IN_DATE,NO_ZERO_DATE was set in the new db, thanks for spotting it. –  Nifle Sep 26 '11 at 14:53
    
@TechieGurl : +1 for a Good Catch !!! –  RolandoMySQLDBA Sep 26 '11 at 14:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.