default=func.now() issue in MySQL

[Former user]

(original reporter: sparticvs) Hello, first let me start by apologizing should this be a duplicate. I did some hunting through titles to see if anything seemed like it would be the same, but I didn't see anything specific, so here we go.

Using the following example:

from sqlalchemy import Column, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.mysql import TIMESTAMP, BIGINT, INTEGER, BOOLEAN
from sqlalchemy.sql import func
Base = declarative_base()
class Alpha(Base):
     __tablename__ = 'alpha'
     __table_args__ = {'mysql_engine':'MyISAM', 'mysql_charset': 'utf8', 'mysql_collate': 'utf8_unicode_ci'}
     alpha_id = Column(BIGINT(unsigned=True), autoincrement=True, primary_key=True, unique=True, nullable=False)
     beta_id = Column(BIGINT(unsigned=True), nullable=False)
     charlie_id = Column(INTEGER(unsigned=True), nullable=False)
     inserted_time = Column(TIMESTAMP, nullable=False, default=func.current_timestamp())
     alpha_state = Column(BOOLEAN, nullable=False, default=False)

engine = create_engine('mysql://root@localhost/sa_test_db', echo=True)
Base.metadata.create_all(engine)

And according to the echo that executes:

CREATE TABLE alpha (
        alpha_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        beta_id BIGINT UNSIGNED NOT NULL,
        charlie_id INTEGER UNSIGNED NOT NULL,
        inserted_time TIMESTAMP,
        alpha_state BOOL NOT NULL,
        PRIMARY KEY (alpha_id),
        UNIQUE (alpha_id),
        CHECK (alpha_state IN (0, 1))
)ENGINE=MyISAM CHARSET=utf8 COLLATE utf8_unicode_ci

However this is not same as what I stated above as a show create table shows the following:

mysql> show create table alpha;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                        |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| alpha | CREATE TABLE `alpha` (
  `alpha_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `beta_id` bigint(20) unsigned NOT NULL,
  `charlie_id` int(10) unsigned NOT NULL,
  `inserted_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `alpha_state` tinyint(1) NOT NULL,
  PRIMARY KEY (`alpha_id`),
  UNIQUE KEY `alpha_id` (`alpha_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

However if you set {{{ inserted_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP }}} (which is representative of the Column declaration above) this doesn't create the ON UPDATE.

Therefore, that said, I propose that when a Column with the type of TIMESTAMP has a default value of func.now() or func.current_timestamp() that it is explicitly created as such in a MySQL table.

Thank you.

[Mike Bayer]

I see that MySQL has an implicit behavior whereby the "TIMESTAMP" type implies a series of ON INSERT and ON UPDATE defaults, but this has nothing to do with the "default" or "onupdate" Column arguments.

"default" and "onpudate" are intended to represent Python values, functions, or SQL expressions that are automatically embedded into INSERT and UPDATE statements by the Core. Their intent is to represent defaults that either don't work as SQL expressions, aren't supported as DDL-level defaults, or for existing tables that aren't being created. So in no case is it appropriate for either of these directives to have any impact or reliance on the format of a CREATE TABLE statement; the expectation is that they take effect for any existing table regardless of that table's structure in the database.

Server-side DEFAULT directives is controlled by the server_default argument:

http://docs.sqlalchemy.org/en/rel_0_7/core/schema.html#server-side-defaults

SQLAlchemy has no direct support for rendering MySQL's ON UPDATE directive. 0.8 will have a feature that will make it easier to add this in, and there's no reason SQLAlchemy itself couldn't support this directive directly, but it's not there right now. It would need to be a MySQL-specific argument like Column(..., mysql_onupdate=XYZ).

If the only thing you're looking for here is an implicit jump from client-side defaults to server side defaults, then this ticket should be closed.

[Former user]

(original author: sparticvs) Thanks for the update zzzeek. I too read about the TIMESTAMP type, which lead me to posting this. I did not know about the server_default argument (not sure how I missed that one). It was a misunderstanding, so feel free to resolve this ticket. My apologies.

[Mike Bayer]

OK, sorry about that doofy TIMESTAMP on MySQL's side ...