Create table with default values giving error

Question

Using MySql Workbench 6.3 Build version 6.3.6. I am trying to create a table with Default constraint but its giving me error.

Here is the script

Create Table `Migration_Log2` (

                                    `Id` Int NOT NULL AUTO_INCREMENT,
                                    `FilePath` varchar(1000) NOT NULL,
                                    `FileName` varchar(100) NOT NULL,
                                    `IsSent` bool NOT NULL DEFAULT '0',
                                    `CreatedDate` DateTime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                                    `ModifiedDate` DateTime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                    `SendAttemptMade` int NOT NULL DEFAULT '0',
                                    `Message` Text  DEFAULT NULL,
                                     PRIMARY KEY (`Id`),
                                      KEY `migration_log_Id_UNIQUE` (`Id`)
                                    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Error Message

Error Code: 1067. Invalid default value for 'CreatedDate' 0.000 sec

your query running fine . check this : http://sqlfiddle.com/#!9/852935 — Abhishek Ginani, Mar 11 '16 at 04:24
This question will help you to get a solution http://stackoverflow.com/questions/9192027/invalid-default-value-for-create-date-timestamp-field — xkeshav, Mar 11 '16 at 04:59

score 0 · Answer 1 · answered Mar 11 '16 at 04:25

0

This may be due to some strict constraint on the data type check on database server. I would suggest to change type of field CreatedDate from datetime to timestamp.

I had faced similar issue in a VPS for my website.

answered Mar 11 '16 at 04:25

Elixir Techne

1,848
15
20

score 0 · Answer 2 · answered Mar 11 '16 at 04:35

0

Your CURRENT_TIMESTAMP might have been appending the microseconds in the output.

Try to use: CURRENT_TIMESTAMP(0) as the default value.

SELECT CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(0), CURRENT_TIMESTAMP(1), CURRENT_TIMESTAMP(2);

The microseconds mattered, possibly. See the differences.

answered Mar 11 '16 at 04:35

Bimal Poudel

1,214
2
18
41

1. What about changing datetime to timestamp for CreatedDate column? 2. Try changing from utf8 to Latin for a test. It can be also a reason. 3. But you should see your error message too. – Bimal Poudel Mar 11 '16 at 05:59
Hey Bimal, can u try at ur end and let me know because I tired everything and infact even from UI is not allowing http://i66.tinypic.com/142rbl2.jpg – Rockyy Mar 11 '16 at 06:30
Your original script from the problem itself runs OK in my mysql 5.6 version. Just tried to guess what any other configurations are different in your server settings. – Bimal Poudel Mar 11 '16 at 06:33

Create table with default values giving error

Error Code: 1067. Invalid default value for 'CreatedDate' 0.000 sec

2 Answers2