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.

score 1 · Accepted Answer · edited May 23 '17 at 12:20

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.

score 0 · Answer 2 · answered Jul 01 '14 at 14:18

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.

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. — jakobht, Jul 01 '14 at 16:24
Do you get that error with defaults of '1970-01-01 00:00:01'? Maybe a bug report to MySQLDB is called for. — Mike Sherrill 'Cat Recall', Jul 01 '14 at 16:27

mysql and Python: does not allow two timestamp columns

2 Answers2