Upgrade process fails when trying to migrate database


#1

Hi all,

I did an update of Dreamfactory following

http://wiki.dreamfactory.com/DreamFactory/Upgrades#Perform_a_Backup

When I execute the step

php artisan migrate --seed

I get the following errors:

[Illuminate\Database\QueryException]
SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘last_modified_date’ at row 1 (SQL: alter table service add config text null)

and

[Doctrine\DBAL\Driver\PDOException]
SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘last_modified_date’ at row 1

and

[PDOException]
SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘last_modified_date’ at row 1

I’m a bit helpless about what to do next. Does anybody know advice?

Addendum:
The service table contains normal date values like ‘2017-09-01 13:40:00’


#2

Solved. I took a close look at the system_config table. The column “last_modified_date” is defined there with default “0000-00-00 00:00:00”. This is an invalid default value because:

It can hold values starting at ‘1970-01-01 00:00:01’ (UTC) to ‘2038-01-19 05:14:07’ (UTC)

(MariaDB documentation)

So basically this seems to be a bug in DF. I will file an according bug report.


#3

Same is for table event_script btw


#4

This was due to an update in the underlying framework that runs DreamFactory.
This not a bug in a strict sense (there’s nothing to change in the code.) You just need a workaround to get past it.
You’ll see this issue if you go from pre-2.5 to 2.6 or later (I think those are the right versions.)

In your database, set the default value for those last_modified_date fields to CURRENT_TIMESTAMP and then run the migrations again. You may see this error for multiple tables. Repeat the process as needed.


#5

Hi drewpearce,

thank you for your help! It’s greatly appreciated!

Best
MrC0de