sqlalchemy does not emit server_onupdate ON UPDATE clause with MySQL

[sqlalchemy-bot]

Migrated issue, originally created by Charles-Axel Dein (@charlax)

This seems similar to #3155 and #2631.

I have this simple script to reproduce the problem:

from sqlalchemy import create_engine
from sqlalchemy import func
from sqlalchemy.dialects.mysql import DATETIME
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import Column
from sqlalchemy.types import Integer, DateTime

Base = declarative_base()

class Timestamp(Base):

    __tablename__ = 'timestamps'

    id = Column(Integer(), primary_key=True)
    created_at = Column(DateTime(), nullable=False,
                        server_default=func.current_timestamp())
    updated_at = Column(DateTime(), nullable=False,
                        server_default=func.current_timestamp(),
                        server_onupdate=func.current_timestamp())

if __name__ == '__main__':
    engine = create_engine('mysql://root:root@localhost/test', echo=True)
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)

The SQL that sqlalchemy generates does not have the ON UPDATE clause:

CREATE TABLE timestamps (
    id INTEGER NOT NULL AUTO_INCREMENT,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
)

A SHOW CREATE TABLE query confirms that fact:

CREATE TABLE `timestamps` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

I'm not 100% sure I'm not causing the problem or have fully understood the two similar tickets. I'll read about it and follow up on this thread if I find something.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

there's no general "ON UPDATE" column clause in SQL. MySQL has a very limited form of this in their TIMESTAMP type (https://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html), SQLAlchemy talks about this at http://docs.sqlalchemy.org/en/rel_1_0/dialects/mysql.html#timestamp-columns-and-null.

The server_onupdate flag only indicates to the Core and ORM that this column somehow creates a value on the server when an update occurs, typically via a trigger. http://docs.sqlalchemy.org/en/rel_1_0/core/metadata.html#sqlalchemy.schema.Column.params.server_onupdate

A FetchedValue instance representing a database-side default generation function. This indicates to SQLAlchemy that a newly generated value will be available after updates. This construct does not specify any DDL and the implementation is left to the database, such as via a trigger.

[sqlalchemy-bot]

Charles-Axel Dein (@charlax) wrote:

But MySQL's DDL does support ON UPDATE for timestamps? I guess if I wanted to do a PR adding in sqlalchemy it would be quite difficult because this clause would only apply to a specific type?

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

it's implicit. Create a MySQL table with a TIMESTAMP, then do a "SHOW CREATE TABLE".

#!

mysql> CREATE TABLE foo (data TIMESTAMP);
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW CREATE TABLE foo;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| foo   | CREATE TABLE `foo` (
  `data` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> 

magic!

[sqlalchemy-bot]

Charles-Axel Dein (@charlax) wrote:

Yes I got confused by DATETIME vs. TIMESTAMP. Gonna take some time to get used to MySQL again after a few years on Postgres...

[sqlalchemy-bot]

Charles-Axel Dein (@charlax) wrote:

Leaving this for future reference, and people who might stumble upon this: http://jasonbos.co/two-timestamp-columns-in-mysql/

So basically getting a model with database-level created_at and updated_at behavior requires some shenanigans that you recommend against in #3155. Sadly, wasn't able to find another way to achieve this.

from sqlalchemy import create_engine
from sqlalchemy import func, text
from sqlalchemy.dialects.mysql import TIMESTAMP
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import Column
from sqlalchemy.types import Integer, DateTime
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Timestamp(Base):

    __tablename__ = 'timestamps'

    id = Column(Integer(), primary_key=True)
    created_at = Column(TIMESTAMP(), nullable=False,
                        server_default=text('0'))
    updated_at = Column(TIMESTAMP(), nullable=False,
                        server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
                        )

if __name__ == '__main__':
    engine = create_engine('mysql://root:root@localhost/test', echo=True)
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)

    Session = sessionmaker(bind=engine)
    session = Session()
    print session.execute('SHOW CREATE TABLE timestamps').fetchone()[1]

Which outputs:

2015-06-09 14:04:18,459 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2015-06-09 14:04:18,459 INFO sqlalchemy.engine.base.Engine ()
2015-06-09 14:04:18,460 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2015-06-09 14:04:18,460 INFO sqlalchemy.engine.base.Engine ()
2015-06-09 14:04:18,461 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin'
2015-06-09 14:04:18,461 INFO sqlalchemy.engine.base.Engine ()
2015-06-09 14:04:18,462 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2015-06-09 14:04:18,462 INFO sqlalchemy.engine.base.Engine ()
2015-06-09 14:04:18,462 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2015-06-09 14:04:18,463 INFO sqlalchemy.engine.base.Engine ()
2015-06-09 14:04:18,463 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1
2015-06-09 14:04:18,463 INFO sqlalchemy.engine.base.Engine ()
2015-06-09 14:04:18,464 INFO sqlalchemy.engine.base.Engine DESCRIBE `timestamps`
2015-06-09 14:04:18,464 INFO sqlalchemy.engine.base.Engine ()
2015-06-09 14:04:18,467 INFO sqlalchemy.engine.base.Engine
DROP TABLE timestamps
2015-06-09 14:04:18,467 INFO sqlalchemy.engine.base.Engine ()
2015-06-09 14:04:18,468 INFO sqlalchemy.engine.base.Engine COMMIT
2015-06-09 14:04:18,469 INFO sqlalchemy.engine.base.Engine DESCRIBE `timestamps`
2015-06-09 14:04:18,469 INFO sqlalchemy.engine.base.Engine ()
2015-06-09 14:04:18,470 INFO sqlalchemy.engine.base.Engine ROLLBACK
2015-06-09 14:04:18,470 INFO sqlalchemy.engine.base.Engine
CREATE TABLE timestamps (
    id INTEGER NOT NULL AUTO_INCREMENT,
    created_at TIMESTAMP NOT NULL DEFAULT 0,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
)

2015-06-09 14:04:18,470 INFO sqlalchemy.engine.base.Engine ()
2015-06-09 14:04:18,479 INFO sqlalchemy.engine.base.Engine COMMIT
2015-06-09 14:04:18,479 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2015-06-09 14:04:18,480 INFO sqlalchemy.engine.base.Engine SHOW CREATE TABLE timestamps
2015-06-09 14:04:18,480 INFO sqlalchemy.engine.base.Engine ()
CREATE TABLE `timestamps` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

And then, you need to pass a value of NULL on create:

mysql> insert into timestamps (id) value (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from timestamps;
+----+---------------------+---------------------+
| id | created_at          | updated_at          |
+----+---------------------+---------------------+
|  1 | 0000-00-00 00:00:00 | 2015-06-09 14:10:46 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> insert into timestamps (created_at, id) value (NULL, 2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from timestamps;
+----+---------------------+---------------------+
| id | created_at          | updated_at          |
+----+---------------------+---------------------+
|  1 | 0000-00-00 00:00:00 | 2015-06-09 14:10:46 |
|  2 | 2015-06-09 14:11:14 | 2015-06-09 14:11:14 |
+----+---------------------+---------------------+
2 rows in set (0.00 sec)

[sqlalchemy-bot]

Charles-Axel Dein (@charlax) wrote:

Probably the last update here, left for posterity: was getting too complicated, so ended up defining all of this on the app side instead of database-side.

[sqlalchemy-bot]

Dumitru Gîra (@dimmg) wrote:

For the PostgreSQL the solution I've found is to create a trigger, that will be executed when the updated_at field will be updated.

CREATE FUNCTION update_updated_at_column() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
  BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
  END;
$$;

CREATE TRIGGER <table>_updated_at BEFORE UPDATE ON <table> FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column();

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed