update from 1.2.0.21 to 2.1.6.17041301 fails

[bitking]

I fail the update from 1.2.0.21 to 2.1.6.17041301, could use some help.

I also got this message prior to upgrading and didnt do anything with it because not sure what i need to do exactly.

"warning Some orphaned items have been found. Please, modify those items or enter the default IDs for them.
If the items IDs are not set, they will be created.
accounts_user : 116
accounts_useredit : 194
accountshistory_user : 148
accountshistory_useredit : 986
accountshistory_customer : 5"

Log after upgrade fail:
`2017-04-13 14:56:44 - It will be running shortly
2017-04-13 14:56:44 - initError
2017-04-13 14:56:44 - Application in maintenance
2017-04-13 14:56:44 - It will be running shortly
2017-04-13 14:56:50 - Waiting for task ...
2017-04-13 14:57:00 - Waiting for task ...
2017-04-13 14:57:02 - initError
2017-04-13 14:57:02 - The application needs to be updated
2017-04-13 14:57:02 - If you are an administrator, click on the link: Update
2017-04-13 14:57:10 - Waiting for task ...
2017-04-13 14:57:20 - Waiting for task ...
2017-04-13 14:57:30 - Waiting for task ...
2017-04-13 14:57:40 - Waiting for task ...
2017-04-13 14:57:50 - Waiting for task ...
2017-04-13 14:58:00 - Waiting for task ...
2017-04-13 14:58:10 - Aborting ...
2017-04-13 15:01:04 - Register Task: upgrade
2017-04-13 15:01:04 - auxPreDbUpgrade: 130.16011001
2017-04-13 15:01:04 - upgradeDB: 130.00000000
2017-04-13 15:01:05 - Update DB
Database update not needed.
Version : 130.16011001
2017-04-13 15:01:05 - auxPreDbUpgrade: 130.16100601
2017-04-13 15:01:05 - upgradeDB: 130.16100601
2017-04-13 15:01:05 - Exception: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'acchistory_dateEdit' at row 1
2017-04-13 15:01:05 - #0 SP\Storage\DB->prepareQueryData() called at [/var/www/html/syspass2/inc/SP/Storage/DB.class.php:150]
#1 SP\Storage\DB->doQuery() called at [/var/www/html/syspass2/inc/SP/Storage/DB.class.php:341]
#2 SP\Storage\DB::getQuery() called at [/var/www/html/syspass2/inc/SP/Core/Upgrade/Upgrade.class.php:230]
#3 SP\Core\Upgrade\Upgrade::upgradeDB() called at [/var/www/html/syspass2/inc/SP/Core/Upgrade/Upgrade.class.php:87]
#4 SP\Core\Upgrade\Upgrade::doUpgrade() called at [/var/www/html/syspass2/inc/SP/Controller/MainActionController.class.php:101]
#5 SP\Controller\MainActionController->upgrade() called at [/var/www/html/syspass2/inc/SP/Controller/MainActionController.class.php:72]
#6 SP\Controller\MainActionController->doAction() called at [/var/www/html/syspass2/ajax/ajax_upgrade.php:36]

2017-04-13 15:01:05 - 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-04-13 15:01:05 - 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-04-13 15:01:05 - Deregister Task: upgrade
2017-04-13 15:01:05 - Waiting for task ...`

[bitking]

And all rows in accHistory table with acchistory_dateEdit are 0000-00-00 00:00:00 so not sure how to solve this if this is even the main problem of my failed upgrade.

[bitking]

I set Mysql in no strict mode and put NO_ZERO_DATE off to make it work since i could not make the query's work to change the value of the table.

Still waiting for it to finish apparently it takes a long time to upgrade, i have about 2200 entry's.

Reference:
https://askubuntu.com/questions/811831/whats-the-correct-way-to-revert-mysql-5-7-strict-mode-back-to-how-it-was-in-5-6

[bitking]

For the Category and Customer ID for accounts i didnt fill in anything and in the database it choose one that was free so i guess thats fine. Everything seems to be in working order.

[nuxsmin]

Great, that is most likely the cause of the issue.

The newly created items are there because they are needed to be compliant with database constraints but you can safely delete them after removing the dependants objects.