TIMESTAMP type mixed with DATETIME

[vljukap98]

Bug Report

Q	A
Version	4.2.2

Summary

When performing SchemaDiff between two schemas and then generating SQL statements that will represent the same schema diff performed, type TIMESTAMP(N) is replaced with DATETIME.

Current behavior

The current behavior is that the migrating SQL statement determines the wrong type of the column. I have a type timestamp(6) and the default value for this column current_timestamp(6). When executing the following code:

$comparator = $fromSchemaManager->createComparator();
$schemaDiff = $comparator->compareSchemas($fromSchemaManager->introspectSchema(), $toSchemamanager->introspectSchema());$alterSchemaSQL = $platform->getAlterSchemaSQL($schemaDiff);

In the alterSchemaSQL there is a query that looks like (I omitted unrelevant columns):

CREATE TABLE test (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    time DATETIME DEFAULT 'current_timestamp(6)' NOT NULL,
    PRIMARY KEY (id)
) 

The query won't even execute.

Expected behavior

It executes if I remove ' around current_timestamp(6), but then the default value turns to be just current_timestamp(). Only when I rewrite the query as:

CREATE TABLE test (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    time TIMESTAMP(6) DEFAULT current_timestamp(6) NOT NULL,
    PRIMARY KEY (id)
) 

It executes and the table has all columns with expected properties.

How to reproduce

The code I'm executing:

$fromConn = DriverManager::getConnection([
    'dbname' => 'test_db',
    'user' => 'root',
    'password' => 'root',
    'host' => 'localhost',
    'port' => 3306,
    'driver' => 'mysqli',
]);
$toConn = DriverManager::getConnection([
    'dbname' => 'test_db2',
    'user' => 'root',
    'password' => 'root',
    'host' => 'localhost',
    'port' => 3306,
    'driver' => 'mysqli',
]);

$fromSchemaManager = $fromConn->createSchemaManager();
$toSchemaManager = $toConn->createSchemaManager();
$platform = $toConn->getDatabasePlatform();
$comparator = $fromSchemaManager->createComparator();
$schemaDiff = $comparator->compareSchemas($fromSchemaManager->introspectSchema(), $toSchemamanager->introspectSchema());
$alterSchemaSQL = $platform->getAlterSchemaSQL($schemaDiff);
foreach ($alterSchemaSQL as $v) {
    echo $v . "\n";
}

[vljukap98]

I found something in documentation that might point to solving this problem:

[13] Chosen if the column definition does not contain the version option inside the platformOptions attribute array or is set to false which marks it as a non-locking information column.

But how can I manipulate said version to get the type I need?

[hrvoj3e]

It seems that the information is lost - not good at all.

• tested with MariaDB 10.11

Column has type TIMESTAMP but after introspectSchema()->getTable() + _getPortableTableColumnDefinition() that information is lost and gets converted to DATETIME

I tried to add this snippet before the return in Doctrine\DBAL\Schema\MySQLSchemaManager::_getPortableTableColumnDefinition and it preserves column type TIMESTAMP but not sure what that version is and why it is not set to TRUE by default.

• is this a bug ?

// when using this override for timestamp SQL generator preserves TIMESTAMP
        if ('timestamp' === $dbType) {
            $column->setPlatformOption('version', true);
        }

// my code
        $table = $manager->introspectSchema()->getTable($tableName);
        $createSql = $this->platform()->getCreateTableSQL($table);
        // returns
        //     time_created DATETIME DEFAULT 'current_timestamp(6)' NOT NULL,

Table in MariaDB has

time_created TIMESTAMP(6) DEFAULT current_timestamp(6) NOT NULL,

[vljukap98]

Assigning true to column's platform options version does something right, but only to extent. It's still missing precision value, in this case 6. Looks like something regarding this was done in #5961, but never got the chance to be merged...

[vljukap98]

I resolved this with creating my custom type for timestamp, and being able to set precision/scale to it. Although it would be really nice that the tool supported this whole thing under its own hood.