mysql and Python: does not allow two timestamp columns

Question

I'm having issues with Python, MySQL and creating a table with two timestamp columns.

Executing the following SQL through Python and using MySQLDB:

CREATE TABLE test_db.test_with_two_datetime_columns (
    `first_datetime_field` TIMESTAMP(6) NOT NULL DEFAULT 0,
    `second_datetime_field` TIMESTAMP(6) NOT NULL DEFAULT 0
) ENGINE=InnoDB

I get the error (1067, Invalid default value for 'first_datetime_field'), whereas I can easily fire of the following equal command in the MySQL CLI:

$ mysql -h localhost

> CREATE TABLE test_db.test_with_two_datetime_columns (
      `first_datetime_field` TIMESTAMP(6) NOT NULL DEFAULT 0,
      `second_datetime_field` TIMESTAMP(6) NOT NULL DEFAULT 0
  ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

Similarly through Sequel Pro the above command executes fine.

So I'm guessing the problem is with MySQLDB (akak. MySQL-Python), but that seems very weird

I'm using MySQL 5.6.17, MySQL-Python 1.2.5 and Python 2.7.

Community · Accepted Answer · 2017-05-23 12:20:45Z

I solved using an idea from https://stackoverflow.com/a/22860449/1362628, by changing the default value to null and allow the entry to be null.

CREATE TABLE test_db.test_with_two_datetime_columns (
    `first_datetime_field` TIMESTAMP(6) NULL DEFAULT NULL,
    `second_datetime_field` TIMESTAMP(6) NULL DEFAULT NULL
) ENGINE=InnoDB""")

And now it works fine. The problem though, is that none of these fields can partake in a primary key, as they can contain null.

Update

It turns out that using SELECT @@session.sql_mode I found that the wrapper code on MySQLDB was setting sql_mode to TRADITIONAL. TRADITIONAL expands to STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, TRADITIONAL, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION, where NO_ZERO_IN_DATE and NO_ZERO_DATE makes the above error appear. In the long run though, I am more for the above solution, with an absent value represented as null.

Mike Sherrill 'Cat Recall' · Accepted Answer · 2014-07-01 14:18:13Z

0

There are probably a couple of issues here.

My guess is that MySQLDB doesn't recognize the integer 0 as a valid value for a column of type DateTime or Timestamp. IMHO, MySQL shouldn't either, but it does. You can change that behavior at the server level. See NO_ZERO_DATE.

If I were in your shoes, I'd try something along these lines.

CREATE TABLE test_with_two_datetime_columns (
    `first_datetime_field` TIMESTAMP(6) not null default '0000-00-00 00:00:00',
    `second_datetime_field` TIMESTAMP(6) not null default '0000-00-00 00:00:00',
    primary key (first_datetime_field, second_datetime_field)
) ENGINE=InnoDB ;

These defaults are effectively identical to the integer 0 in this context. One alternative is to default both columns to the minimum value of a timestamp, '1970-01-01 00:00:01'. Another is to default one column to current_timestamp. (Not both columns; MySQL regards that as an error.)

Personally, I don't like timestamps that are "all balls". "All balls" is a timestamp oddity--lexically, it's not in the defined range of of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. But you have to find your own comfort level with that.

answered Jul 1, 2014 at 14:18

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

3 Comments

jakobht Over a year ago

Yup, I tried that, but with no success. I still get an (1067, "Invalid default value for 'first_datetime_field'") error. Defaulting to current_timestamp is not really an option, because if we have an error in our code, a default timestamp could hide that.

Mike Sherrill 'Cat Recall' Over a year ago

Do you get that error with defaults of '1970-01-01 00:00:01'? Maybe a bug report to MySQLDB is called for.

jakobht Over a year ago

Yup, that I get. I'll make a bug report to MySQLDB.

Collectives™ on Stack Overflow

mysql and Python: does not allow two timestamp columns

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related