What is wrong with this query in mysql database?

Question

I am trying to create a table in mysql database by running below query,

CREATE TABLE vms_schedule(
   schedule_id Int AUTO_INCREMENT
   ,   name varchar(255) NOT NULL
   ,   start_date timestamp NOT NULL
   ,   end_date timestamp NOT NULL
   ,   sun Numeric(10, 0) 
   ,   mon Numeric(10, 0) 
   ,   tue Numeric(10, 0) 
   ,   wed Numeric(10, 0) 
   ,   thu Numeric(10, 0) 
   ,   fri Numeric(10, 0) 
   ,   sat Numeric(10, 0) 
   ,PRIMARY KEY (schedule_id)
);

But I am getting "Invalid default value for end_date" error. Not sure what is wrong with the query as it works for start_date and not for end_date.

EDIT: enter image description here

Thanks.

What do you mean?, you can't execute that particular `CREATE TABLE`?, it works fine in [sqlfiddle](http://sqlfiddle.com/#!9/11f07) — Lamak, Mar 25 '15 at 16:04
Atleast wait for the response before you downvote or select to close. — GuruKulki, Mar 25 '15 at 16:08
If you say a column is NOT NULL, you should have to provide a default value. — Dan, Mar 25 '15 at 16:11
@dan08 Not necessarily. Also, it shouldn't be a problem when you are creating the table — Lamak, Mar 25 '15 at 16:12
@dan08, but it works for start_date. I mean if i remove end_date column then it creates the table. — GuruKulki, Mar 25 '15 at 16:12
Have you checked http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date ? — Abhik Chakraborty, Mar 25 '15 at 16:12
Just add for example start_date timestamp NOT NULL default 0 — ares777, Mar 25 '15 at 16:14
But how come this works. CREATE TABLE vms_schedule( schedule_id Int AUTO_INCREMENT , name varchar(255) NOT NULL , start_date timestamp NOT NULL , sun Numeric(10, 0) , mon Numeric(10, 0) , tue Numeric(10, 0) , wed Numeric(10, 0) , thu Numeric(10, 0) , fri Numeric(10, 0) , sat Numeric(10, 0) ,PRIMARY KEY (schedule_id) ); — GuruKulki, Mar 25 '15 at 16:15
Perhaps you have some hidden character after `end_date`, type the query in a text file and then copy paste in `mysql` — Abhik Chakraborty, Mar 25 '15 at 16:19
This is similar to http://stackoverflow.com/questions/9192027/invalid-default-value-for-create-date-timestamp-field Which is the same thing that @AbhikChakraborty is pointing out above. It seems strange, but changing the `No_Zero_Date` option in my.ini may fix the behavior. — JNevill, Mar 25 '15 at 16:21
But my doubt is, it works fine if I remove the end_date column. — GuruKulki, Mar 25 '15 at 16:23
Which is why some people are saying that there may be some hidden character. Did you try writing that line again from scratch? — Lamak, Mar 25 '15 at 16:24
What if you swap start and end date in the statement. That would point out if it is the end_date line or the second timestamp column. I'm pretty sure it is a problem with having two NOT NULL timestamp columns, but can't track down anything definitive. — Dan, Mar 25 '15 at 16:26
after swapping, it says start_date has invalid default value. — GuruKulki, Mar 25 '15 at 16:28
I even tried to create a table without end_date and then alter to add that column, then also it's the same :( — GuruKulki, Mar 25 '15 at 16:33
For funsies, can you do `start_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, end_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP` and if that works `SHOW VARIABLES LIKE 'sql_mode'` — JNevill, Mar 25 '15 at 16:35
Yeah, finally start_date timestamp NOT NULL , end_date timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL worked. But why it is not giving the same problem for start_date? — GuruKulki, Mar 25 '15 at 16:38
sql_mode STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION — GuruKulki, Mar 25 '15 at 16:39
This seems close to the "multiple timestamp column" issue: http://stackoverflow.com/questions/4851672/one-mysql-table-with-multiple-timestamp-columns — JNevill, Mar 25 '15 at 16:40
@JNevill, that was my thought too, but that was changed in 5.6.5, and the OP is using 5.7 — Dan, Mar 25 '15 at 16:43
I missed where OP said which version they were on. I was going to ask earlier but I got all wrapped up in the strangeness of the issue. — JNevill, Mar 25 '15 at 16:45

score 2 · Answer 1 · answered Mar 25 '15 at 17:25

I cannot reproduce this problem on MySQL 5.6.23. I don't have MySQL 5.7. 5.7 is a development release. You probably shouldn't be using it for production. You probably hit a bug and you should report it.

I would avoid TIMESTAMP and use DATETIME instead. TIMESTAMP has many odd and confusing "features" and is limited to dates between 1970 and 2038. Its simpler and more robust to use DATETIME and declare any magic you want to happen explicitly.

What is wrong with this query in mysql database?

1 Answers1