Unable to store datetime.datetime.max using SQLAlchemy==0.8.1 with the mysql-python==1.2.4 driver

[peralmq]

I've noticed a change in behavior for storing datetime.datetime.max via SQLAlchemy==SQLAlchemy==0.8.1 and going from mysql-python==1.2.3 to mysql-python==1.2.4. By only changing the driver from 1.2.3 to 1.2.4 I go from being able to store to being unable to store it.

Is this expected behaviour or a bug or do I have a bad setup? I fear that a change like this will break a lot of systems out there.

This is my SQLAlchemy setup:

from sqlalchemy import create_engine, Integer, DateTime, Column
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime

engine = create_engine('mysql://root@localhost/test_database', echo=True)
Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    age = Column(DateTime, default=datetime.max)

Base.metadata.create_all(engine)
session = sessionmaker(bind=engine)()
u = User()
session.add(u)
session.commit()

I also have a virtualenv called test. This is what happens when I run the code above.

(test)➜  ~  pip install MySQL-python==1.2.3
(test)➜  ~  python test.py
2013-06-26 10:29:18,885 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2013-06-26 10:29:18,885 INFO sqlalchemy.engine.base.Engine ()
2013-06-26 10:29:18,887 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'character_set%%'
2013-06-26 10:29:18,887 INFO sqlalchemy.engine.base.Engine ()
2013-06-26 10:29:18,891 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2013-06-26 10:29:18,891 INFO sqlalchemy.engine.base.Engine ()
2013-06-26 10:29:18,896 INFO sqlalchemy.engine.base.Engine DESCRIBE `users`
2013-06-26 10:29:18,896 INFO sqlalchemy.engine.base.Engine ()
2013-06-26 10:29:18,904 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-06-26 10:29:18,905 INFO sqlalchemy.engine.base.Engine INSERT INTO users (age) VALUES (%s)
2013-06-26 10:29:18,905 INFO sqlalchemy.engine.base.Engine (datetime.datetime(9999, 12, 31, 23, 59, 59, 999999),)
2013-06-26 10:29:18,908 INFO sqlalchemy.engine.base.Engine COMMIT

And the database (test_database) looks like this:

mysql> select * from users;
+----+---------------------+
| id | age                 |
+----+---------------------+
|  1 | 9999-12-31 23:59:59 |
+----+---------------------+
1 row in set (0.00 sec)

This is my expected result so nothing strange here.

However, by simply switching the driver to mysql-python==1.2.4 I get this result.

(test)➜  ~  pip install MySQL-python==1.2.4
(test)➜  ~  python test.py
2013-06-26 10:33:39,544 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2013-06-26 10:33:39,544 INFO sqlalchemy.engine.base.Engine ()
2013-06-26 10:33:39,546 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'character_set%%'
2013-06-26 10:33:39,546 INFO sqlalchemy.engine.base.Engine ()
2013-06-26 10:33:39,546 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2013-06-26 10:33:39,546 INFO sqlalchemy.engine.base.Engine ()
2013-06-26 10:33:39,547 INFO sqlalchemy.engine.base.Engine DESCRIBE `users`
2013-06-26 10:33:39,547 INFO sqlalchemy.engine.base.Engine ()
2013-06-26 10:33:39,551 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-06-26 10:33:39,552 INFO sqlalchemy.engine.base.Engine INSERT INTO users (age) VALUES (%s)
2013-06-26 10:33:39,552 INFO sqlalchemy.engine.base.Engine (datetime.datetime(9999, 12, 31, 23, 59, 59, 999999),)
/Users/pelle/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/engine/default.py:324: Warning: Datetime function: datetime field overflow
  cursor.execute(statement, parameters)
/Users/pelle/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/engine/default.py:324: Warning: Out of range value for column 'age' at row 1
  cursor.execute(statement, parameters)
2013-06-26 10:33:39,553 INFO sqlalchemy.engine.base.Engine COMMIT

And the database looks like this.

mysql> select * from users;
+----+---------------------+
| id | age                 |
+----+---------------------+
|  1 | 0000-00-00 00:00:00 |
+----+---------------------+
1 row in set (0.00 sec)

So now all of the sudden I receive a warning Warning: Datetime function: datetime field overflow and I end up with a nullable value in my database.

[psilva261]

Hey,

It's a MySQL issue that came up when they bumped from 5.5 to 5.6: http://bugs.mysql.com/bug.php?id=68760

Cheers, Philip