Database Upgrade issue to 2.1

[b-twis]

I have just tried an upgrade to the 2.1 tagged release and got the anerror during the database upgrade.

Current version 1.2.0.21

I followed the recommendation of

• Dump mysql db and restore to syspass21 (with permissions etc)
• Extract v 2.1 to new directory and copy config folder
• Update config.xml to point to new database

The site loads to the "Insert upgrade Key" and after entering the data the following error is written to syspass.log

Version : 21117030601
2017-03-07 10:36:00 - Action: Update -- Description: Version updating done. -- Details: Version : 21117030601
Type : config
2017-03-07 10:36:00 - initError
2017-03-07 10:36:00 - The "/config" directory permissions are wrong
2017-03-07 10:36:00 - Current: 755 - Needed: 750
2017-03-07 10:36:35 - initError
2017-03-07 10:36:35 - The "/config" directory permissions are wrong
2017-03-07 10:36:35 - Current: 755 - Needed: 750
2017-03-07 10:36:43 - initError
2017-03-07 10:36:43 - The application needs to be updated
2017-03-07 10:36:43 - If you are an administrator, click on the link: Update
2017-03-07 10:38:32 - Register Task: upgrade
2017-03-07 10:38:32 - auxPreDbUpgrade: 130.16011001
2017-03-07 10:38:32 - upgradeDB: 130.00000000
2017-03-07 10:38:35 - Update DB
Database update not needed.
Version : 130.16011001
2017-03-07 10:38:35 - auxPreDbUpgrade: 130.16100601
2017-03-07 10:38:35 - upgradeDB: 130.16100601
2017-03-07 10:38:36 - Exception: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'acchistory_dateEdit' at row 1
2017-03-07 10:38:36 - #0 SP\Storage\DB->prepareQueryData() called at [/var/www/html/syspass21/inc/SP/Storage/DB.class.php:150]
#1 SP\Storage\DB->doQuery() called at [/var/www/html/syspass21/inc/SP/Storage/DB.class.php:341]
#2 SP\Storage\DB::getQuery() called at [/var/www/html/syspass21/inc/SP/Core/Upgrade/Upgrade.class.php:230]
#3 SP\Core\Upgrade\Upgrade::upgradeDB() called at [/var/www/html/syspass21/inc/SP/Core/Upgrade/Upgrade.class.php:87]
#4 SP\Core\Upgrade\Upgrade::doUpgrade() called at [/var/www/html/syspass21/inc/SP/Controller/MainActionController.class.php:101]
#5 SP\Controller\MainActionController->upgrade() called at [/var/www/html/syspass21/inc/SP/Controller/MainActionController.class.php:72]
#6 SP\Controller\MainActionController->doAction() called at [/var/www/html/syspass21/ajax/ajax_upgrade.php:36]

2017-03-07 10:38:36 - Error en la consulta
SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'acchistory_dateEdit' at row 1 (0)
Caller 1: SP\Storage\DB\logDBException
Caller 2: SP\Storage\DB\getQuery
Caller 3: SP\Core\Upgrade\Upgrade\upgradeDB
Caller 4: SP\Core\Upgrade\Upgrade\doUpgrade
Caller 5: SP\Controller\MainActionController\upgrade
Caller 6: SP\Controller\MainActionController\doAction
2017-03-07 10:38:36 - SQL : 'ALTER TABLE accHistory CHANGE COLUMN acchistory_userId acchistory_userId SMALLINT(5) UNSIGNED NOT NULL, CHANGE COLUMN acchistory_userEditId acchistory_userEditId SMALLINT(5) UNSIGNED NOT NULL, CHANGE COLUMN acchistory_customerId acchistory_customerId INT(10) UNSIGNED NOT NULL, CHANGE COLUMN acchistory_categoryId acchistory_categoryId SMALLINT(5) UNSIGNED NOT NULL, CHANGE COLUMN acchistory_dateEdit acchistory_dateEdit DATETIME NULL DEFAULT NULL, CHANGE COLUMN acchistory_userGroupId acchistory_userGroupId SMALLINT(5) UNSIGNED NOT NULL, ADD INDEX fk_accHistory_users_id_idx (acchistory_userId ASC), ADD INDEX fk_accHistory_users_edit_id_idx (acchistory_userEditId ASC), ADD INDEX fk_accHistory_categories_id_idx (acchistory_categoryId ASC), ADD INDEX fk_accHistory_customers_id_idx (acchistory_customerId ASC)'
2017-03-07 10:38:36 - Deregister Task: upgrade

I will try again with the not tagged latest master branch commit.

Thanks,
Basil

[nuxsmin]

Hello,
the issue could be related to NO_ZERO_DATESmode: http://stackoverflow.com/questions/35565128/mysql-incorrect-datetime-value-0000-00-00-000000#37780259

[b-twis]

Worked perfectly. I updated the global sql_mode to remove the NO_ZERO_DATES.

Thanks for the quick help as always :)

Thanks,
Basil

[nuxsmin]

You're welcome ;)

Thanks for the feedback!