Lower-case default value of TIMESTAMP causes error

[t2hog]

We have discovered a bug that occurs when using Phinx with MariaDB, and trying to update a TIMESTAMP column with current_timestamp() as default value.

The error: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'createdAt'

We have found the reason the error occurs. MariaDB is defaulting the TIMESTAMP-value to current_timestamp() in lower-case. There is Phinx code to NOT quote CURRENT_TIMESTAMP in src/Phinx/Db/Adapter/PdoAdapter.php:getDefaultValueDefinition(), but because it only handles upper-case, this lower-case default value is quoted anyway.

elseif (is_string($default) && strpos($default, 'CURRENT_TIMESTAMP') !== 0) {
            // Ensure a defaults of CURRENT_TIMESTAMP(3) is not quoted.
            $default = $this->getConnection()->quote($default);
}

The suggested fix: Adding strtolower around $default and 'CURRENT_TIMESTAMP' in src/Phinx/Db/Adapter/PdoAdapter.php:getDefaultValueDefinition().

} elseif (is_string($default) && strpos(strtolower($default), strtolower('CURRENT_TIMESTAMP')) !== 0) {
            // Ensure a defaults of CURRENT_TIMESTAMP(3) is not quoted.
            $default = $this->getConnection()->quote($default);
}

Is it possible to get this small fix done? :-) Or is there another workaround?

Apparently, MariaDB has changed the default from CURRENT_TIMESTAMP to current_timestamp() from version 10.2.3. (https://mariadb.com/kb/en/now/#description )

Our migration causing the error:

private const TABLE_NAME = 'DocumentRef';
private const NEW_COLUMN_NAME = 'incomingAt';
private const OLD_COLUMN_NAME = 'createdAt';

public function change()
{
    $this->table(self::TABLE_NAME)
          ->renameColumn(self::OLD_COLUMN_NAME, self::NEW_COLUMN_NAME)
          ->update();
 }

The generated SQL causing the error:

"ALTER TABLE `DocumentRef` CHANGE COLUMN `incomingAt` `createdAt` timestamp NOT NULL  DEFAULT           
 'current_timestamp()' COMMENT '';"

[garas]

You can open pull request with suggested fix.

You can use stripos instead of strpos, the strtolower is unnecessary.

[t2hog]

Thanks for your reply. I'll look into doing a pull request. In the meantime, we managed to solve it by creating a custom adapter and doing the necessary change of the default value, before calling the original getDefaultValueDefinition() function.

Aha, yeah, stripos is a better idea. :-)

[pe1uca]

Maybe the solution in here would be for phinx to not quote functions.
In this case someone might want to use the functionality of precision described in here https://mariadb.com/kb/en/current_timestamp/

In a similar case we want to include a default value for a json in mysql which would be better using the functions included https://dev.mysql.com/doc/refman/8.0/en/json-creation-functions.html