propel diff issue with CURRENT_TIMESTAMP on maria 10.2

[wollanup]

Expected Behavior

propel diff should not generate diffs when nothing has changed

Current Behavior

propel diff find diff with TIMESTAMP columns with defaultExpr="CURRENT_TIMESTAMP" From what I can remember, issue started with mariadb 10.2, and was not present in 10.1

Steps to Reproduce

1. change/create schema
2. run propel diff
3. run propel migrate
4. run propel diff (should not not find diff)

Detailed Description

Given this XML schema :

<?xml version="1.0" encoding="utf-8"?>
<database name="test_date_db">
	<table name="test_date">
		<column name="col_date" type="TIMESTAMP" defaultExpr="CURRENT_TIMESTAMP"/>
	</table>
</database>

I run propel diff which generate CREATE code :

CREATE TABLE `test_date`
(
    `col_date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

propel migration creates the table.

mysqldump give me this result :

CREATE TABLE `test_date` (
  `col_date` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

If a run propel diff again, I get this :

ALTER TABLE `test_date`

  CHANGE `col_date` `col_date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP;

Environment

Application runs in a Debian docker container with :

• php 7.0
• mariadb 10.2.8
• propel 2 (6c3d6364fd237231519298911055292107a8b3f1)

[wollanup]

After some investigations on Diff code, in ColumnComparator.php we have :

$fromDefaultValue

NULL

$toDefaultValue

class Propel\Generator\Model\ColumnDefaultValue#716 (2) {
  private $value =>
  string(17) "CURRENT_TIMESTAMP"
  private $type =>
  string(4) "expr"
}

I wil try to understand how Domain object is populated as we use it in Column::getDefaultValue() ...

[marcj]

You can place here

https://github.com/propelorm/Propel2/blob/master/src/Propel/Generator/Command/MigrationDiffCommand.php#L185

something like this

echo (string)$databaseDiff;

to see, what the comparator objects have detected. I always wanted to implement that as a feature for the diff command, maybe through --show option, so one can see beforehand what Propel detected before it creates the actual sql files.

[wollanup]

In my real word case, with 'alarm' table I got this :

alarm:
    modifiedColumns:
      alarme.ALARM_DATE:
        modifiedProperties:
          defaultValueType: [null,"expr"]
          defaultValueValue: [null,"CURRENT_TIMESTAMP"]

I try to find how diff build column info on actual (from) database, It should be comparable to reverse command.

Also, I use HeidiSQL to explore database, and it's interesting to see with maria 10.2, the CREATE code looks strange :

CREATE TABLE `alarm` (
	`alarm_date` DATETIME NOT NULL DEFAULT ''
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

Note actual '' instead of expected'CURRENT_TIMESTAMP' only mysqldump seems to perform well.

[wollanup]

If I run

SHOW COLUMNS FROM alarm

(Found in MysqlSchemaParser.php)

I get :

• Field : alarm_date
• Type : datetime
• Null : NO
• Default : current_timestamp()

[wollanup]

OK, found, will submit PR and update issue soon

[wollanup]

@marcj Do you think we should add some tests with latest version of MariaDB in Travis ? If yes I can try to add phpunit test case.

[marcj]

@wollanup sure, every additional test is a good test :)

[dereuromark]

Is this still relevant or can it be closed?

[wollanup]

@dereuromark Didn't write the test 3 years ago, can't remember why... Maybe harder/longer than I thought ? Sorry!