DBAL does not support default parameter for datetime types

[spacetraveller]

In more recent versions of dbal, the insertion of default option for datetime is no longer being performed, with the result that you're unable to generate a DB schema with a default CURRENT_TIMESTAMP.

    $this->addSql('ALTER TABLE cs_user_inbox CHANGE time_added time_added DATETIME NOT NULL');

/**

• @var \DateTime
• @ORM\Column(name="time_added", type="datetime", nullable=false, options={"DEFAULT"="CURRENT_TIMESTAMP"}) */ private $timeAdded;

dbal developers should be aware that in some situations, PHP might not be the only language interfacing with the database. Using Doctrine & DBAL does simplify database maintenance in a development cycle and enables simple updates thought the very simple to use migrations, however the caveat that we cannot set a default value for a datetime is taking things too far and not encouraging developers to think for themselves in a very dynamic system.

I highly encourage for this feature to be brought back as it is very useful.

[spacetraveller]

Just including part of the composer.json for your reference..

"require":

{ "php": ">=7.0.0", "creof/doctrine2-spatial": "^1.2", "doctrine/common": "^2.7.0", "doctrine/dbal": "^2.5", "doctrine/doctrine-bundle": "^1.6", "doctrine/doctrine-migrations-bundle": "^1.3", "doctrine/orm": "^2.5", "incenteev/composer-parameter-handler": "^2.0", "sensio/distribution-bundle": "^5.0.19", "sensio/framework-extra-bundle": "^3.0.2", "sonata-project/admin-bundle": "^3.28", "sonata-project/doctrine-orm-admin-bundle": "^3.2", "sonata-project/formatter-bundle": "^3.4", "symfony/ldap": "^4.0", "symfony/monolog-bundle": "^3.1.0", "symfony/polyfill-apcu": "^1.0", "symfony/serializer": "^4.0", "symfony/swiftmailer-bundle": "^2.3.10", "symfony/symfony": "3.*", "twig/twig": "^1.0||^2.0" }

[Ocramius]

I highly encourage for this feature to be brought back as it is very useful.

Not going to happen for a few reasons:

1. ORM assumes read/write to be symmetrical: default values lead to non-idempotent writes, and the ORM therefore cannot operate on fields with default values (which should not be mapped - transient)
2. The CURRENT_TIMESTAMP is a MySQL-specific that doesn't use a value, but rather a pseudo-expression that is "something special". ORM has no real way other than columnDefinition to distinguish expressions from values, and therefore that won't work and won't be implemented for this edge case. Also, other databases have proper semantics for defining default values, well above what MySQL can do, so that opens a huge can of worms. errata - CURRENT_TIMESTAMP is also supported by other engines. The opposite isn't true though, making CURRENT_TIMESTAMP an edge case supported by MySQL (https://dev.mysql.com/doc/refman/8.0/en/create-table.html).
3. The schema tool should not be used in production: you generate migration scripts that should be peer-reviewed and adapted to the specific requirements of the DB. You can freely add fields with complex definitions, constraints, triggers and so on as part of your migration scripts.

Closing here as won't fix

[ostrolucky]

CURRENT_TIMESTAMP is NOT MySQL specific. We use it with PostgreSQL 9.6. But don't have this reported issue

[Ocramius]

@ostrolucky my bad! Indeed the expression exists elsewhere too, but the other engines support multiple expressions for DEFAULT <expression>.

Amending my comment above.

https://dev.mysql.com/doc/refman/8.0/en/create-table.html states:

 DEFAULT

Specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP or DATETIME column. See Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

If a column definition includes no explicit DEFAULT value, MySQL determines the default value as described in Section 11.7, “Data Type Default Values”.

BLOB, TEXT, and JSON columns cannot be assigned a default value.

If the NO_ZERO_DATE or NO_ZERO_IN_DATE SQL mode is enabled and a date-valued default is not correct according to that mode, CREATE TABLE produces a warning if strict SQL mode is not enabled and an error if strict mode is enabled. For example, with NO_ZERO_IN_DATE enabled, c1 DATE DEFAULT '2010-00-00' produces a warning. 

[github-actions[bot]]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.