Error while updating the database

[VexedSyd]

sysPass Version:
Upgarding from syspass:3.0.5 to syspass:3.1.0-rc5 (CentOS 7.6, MariaDB 5.5.60, PHP 7.2.20)

Describe the bug:
Similar to #1382

An error "Error while updating the database" appears while performing the upgrade.
Error while updating the database | ERROR: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'versionLevel'

To Reproduce:
Steps to reproduce the behavior:
Filled in Security Code
Checked-out "I have done a full backup"
Pressed "Upgrade" button
An error "Error while updating the database" occurs on the browser

sysPass Log:

#0 /var/www/html/syspass/lib/SP/Services/Upgrade/UpgradeDatabaseService.php(173): PDO->exec(String)
#1 /var/www/html/syspass/lib/SP/Services/Upgrade/UpgradeDatabaseService.php(108): SP\Services\Upgrade\UpgradeDatabaseService->applyUpgrade(String)
#2 /var/www/html/syspass/app/modules/web/Controllers/UpgradeController.php(89): SP\Services\Upgrade\UpgradeDatabaseService->upgrade(String,Object(SP\Config\ConfigData))
#3 [internal function]: SP\Modules\Web\Controllers\UpgradeController->upgradeAction()
#4 /var/www/html/syspass/lib/SP/Bootstrap.php(240): call_user_func_array(Array,Array)
#5 [internal function]: SP\Bootstrap->SP\{closure}(Object(Klein\Request),Object(Klein\Response),Object(Klein\ServiceProvider),Object(Klein\App),Object(Klein\Klein),Object(Klein\DataCollection\RouteCollection),Array)
#6 /var/www/html/syspass/vendor/klein/klein/src/Klein/Klein.php(886): call_user_func(Object(Closure),Object(Klein\Request),Object(Klein\Response),Object(Klein\ServiceProvider),Object(Klein\App),Object(Klein\Klein),Object(Klein\DataCollection\RouteCollection),Array)
#7 /var/www/html/syspass/vendor/klein/klein/src/Klein/Klein.php(588): Klein\Klein->handleRouteCallback(Object(Klein\Route),Object(Klein\DataCollection\RouteCollection),Array)
#8 /var/www/html/syspass/lib/SP/Bootstrap.php(464): Klein\Klein->dispatch(Object(Klein\Request))
#9 /var/www/html/syspass/lib/Base.php(75): SP\Bootstrap->run(Object(DI\Container))
#10 /var/www/html/syspass/index.php(28): require(String)","caller":"N/A"}
[2019-07-31 00:40:04] syspass.DEBUG: exception {"message":"Error while updating the database | ERROR: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'versionLevel' (42S21)","user":"N/A","address":"x.x.x.x","caller":"SP\\Services\\Upgrade\\UpgradeDatabaseService::applyUpgrade"} []
[2019-07-31 00:40:04] syspass.EXCEPTION: logger {"message":"Error while updating the database
#0 /var/www/html/syspass/lib/SP/Services/Upgrade/UpgradeDatabaseService.php(108): SP\Services\Upgrade\UpgradeDatabaseService->applyUpgrade(String)
#1 /var/www/html/syspass/app/modules/web/Controllers/UpgradeController.php(89): SP\Services\Upgrade\UpgradeDatabaseService->upgrade(String,Object(SP\Config\ConfigData))
#2 [internal function]: SP\Modules\Web\Controllers\UpgradeController->upgradeAction()
#3 /var/www/html/syspass/lib/SP/Bootstrap.php(240): call_user_func_array(Array,Array)
#4 [internal function]: SP\Bootstrap->SP\{closure}(Object(Klein\Request),Object(Klein\Response),Object(Klein\ServiceProvider),Object(Klein\App),Object(Klein\Klein),Object(Klein\DataCollection\RouteCollection),Array)
#5 /var/www/html/syspass/vendor/klein/klein/src/Klein/Klein.php(886): call_user_func(Object(Closure),Object(Klein\Request),Object(Klein\Response),Object(Klein\ServiceProvider),Object(Klein\App),Object(Klein\Klein),Object(Klein\DataCollection\RouteCollection),Array)
#6 /var/www/html/syspass/vendor/klein/klein/src/Klein/Klein.php(588): Klein\Klein->handleRouteCallback(Object(Klein\Route),Object(Klein\DataCollection\RouteCollection),Array)
#7 /var/www/html/syspass/lib/SP/Bootstrap.php(464): Klein\Klein->dispatch(Object(Klein\Request))
#8 /var/www/html/syspass/lib/Base.php(75): SP\Bootstrap->run(Object(DI\Container))
#9 /var/www/html/syspass/index.php(28): require(String)","caller":"N/A"}

[nuxsmin]

Hello, did you try to run the upgrade process twice?. That field wasn't created before 3.1, so you would need to import a previous backup and the run the upgrade again.

Regards

[alebeta90]

Hi

I try the upgrade from 3.0.5 to 3.1.0 in docker 3.1.0-php7.3. And I get the same error. Why it is happening? there is way to solve it?

[nuxsmin]

@alebeta90 Hi, could you provide some more information about the error?. Some logs messages would be useful...

Regards

[alebeta90]

Hi @nuxsmin

sorry for the lack of information in the first message.

The environment is Docker Swarm, it had been running syspass 3.0.5 with no problem and now I just did the switch to 3.1.0 and I get this error when trying to upgrade.

This is what I get at syspass.log

[2019-08-26 12:45:41] syspass.INFO: logger {"message":"Extensions checked","caller":"SP\Core\PhpExtensionChecker::checkMandatory"}
[2019-08-26 12:45:42] syspass.INFO: logger {"message":"Loaded icons cache","caller":"SP\Core\UI\Theme::initIcons"}
[2019-08-26 12:45:42] syspass.INFO: logger {"message":"Loaded actions cache","caller":"SP\Core\Acl\Actions::loadCache"}
[2019-08-26 12:45:42] syspass.DEBUG: upgrade.db.start {"message":"Update DB","user":"N/A","address":"10.255.0.3","caller":"SP\Services\Upgrade\UpgradeDatabaseService::upgrade"} []
[2019-08-26 12:45:42] syspass.DEBUG: upgrade.db.process {"message":"Version: 310.19012201","user":"N/A","address":"10.255.0.3","caller":"SP\Services\Upgrade\UpgradeDatabaseService::applyUpgrade"} []
[2019-08-26 12:45:42] syspass.EXCEPTION: logger {"message":"SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'versionLevel'
#0 /var/www/html/sysPass/lib/SP/Services/Upgrade/UpgradeDatabaseService.php(173): PDO->exec(String)
#1 /var/www/html/sysPass/lib/SP/Services/Upgrade/UpgradeDatabaseService.php(108): SP\Services\Upgrade\UpgradeDatabaseService->applyUpgrade(String)
#2 /var/www/html/sysPass/app/modules/web/Controllers/UpgradeController.php(89): SP\Services\Upgrade\UpgradeDatabaseService->upgrade(String,Object(SP\Config\ConfigData))
#3 [internal function]: SP\Modules\Web\Controllers\UpgradeController->upgradeAction()
#4 /var/www/html/sysPass/lib/SP/Bootstrap.php(240): call_user_func_array(Array,Array)
#5 [internal function]: SP\Bootstrap->SP{closure}(Object(Klein\Request),Object(Klein\Response),Object(Klein\ServiceProvider),Object(Klein\App),Object(Klein\Klein),Object(Klein\DataCollection\RouteCollection),Array)
#6 /var/www/html/sysPass/vendor/klein/klein/src/Klein/Klein.php(879): call_user_func(Object(Closure),Object(Klein\Request),Object(Klein\Response),Object(Klein\ServiceProvider),Object(Klein\App),Object(Klein\Klein),Object(Klein\DataCollection\RouteCollection),Array)
#7 /var/www/html/sysPass/vendor/klein/klein/src/Klein/Klein.php(588): Klein\Klein->handleRouteCallback(Object(Klein\Route),Object(Klein\DataCollection\RouteCollection),Array)
#8 /var/www/html/sysPass/lib/SP/Bootstrap.php(464): Klein\Klein->dispatch(Object(Klein\Request))
#9 /var/www/html/sysPass/lib/Base.php(75): SP\Bootstrap->run(Object(DI\Container))
#10 /var/www/html/sysPass/index.php(28): require(String)","caller":"N/A"}
[2019-08-26 12:45:42] syspass.EXCEPTION: logger {"message":"Error while updating the database
#0 /var/www/html/sysPass/lib/SP/Services/Upgrade/UpgradeDatabaseService.php(108): SP\Services\Upgrade\UpgradeDatabaseService->applyUpgrade(String)
#1 /var/www/html/sysPass/app/modules/web/Controllers/UpgradeController.php(89): SP\Services\Upgrade\UpgradeDatabaseService->upgrade(String,Object(SP\Config\ConfigData))
#2 [internal function]: SP\Modules\Web\Controllers\UpgradeController->upgradeAction()
#3 /var/www/html/sysPass/lib/SP/Bootstrap.php(240): call_user_func_array(Array,Array)
#4 [internal function]: SP\Bootstrap->SP{closure}(Object(Klein\Request),Object(Klein\Response),Object(Klein\ServiceProvider),Object(Klein\App),Object(Klein\Klein),Object(Klein\DataCollection\RouteCollection),Array)
#5 /var/www/html/sysPass/vendor/klein/klein/src/Klein/Klein.php(879): call_user_func(Object(Closure),Object(Klein\Request),Object(Klein\Response),Object(Klein\ServiceProvider),Object(Klein\App),Object(Klein\Klein),Object(Klein\DataCollection\RouteCollection),Array)
#6 /var/www/html/sysPass/vendor/klein/klein/src/Klein/Klein.php(588): Klein\Klein->handleRouteCallback(Object(Klein\Route),Object(Klein\DataCollection\RouteCollection),Array)
#7 /var/www/html/sysPass/lib/SP/Bootstrap.php(464): Klein\Klein->dispatch(Object(Klein\Request))
#8 /var/www/html/sysPass/lib/Base.php(75): SP\Bootstrap->run(Object(DI\Container))
#9 /var/www/html/sysPass/index.php(28): require(String)","caller":"N/A"}

[nuxsmin]

@alebeta90 no worries. How did you do the switch?. It complains about an already existing table.

Regards

[alebeta90]

@nuxsmin what I did was:

1. Backup database.
2. exchange the docker image tag for the new one.
3. Enter Security Code and click on "UPDATE"

then the error appear

thanks

[alebeta90]

@nuxsmin now I can not use the version 3.0.5. It is always showing upgrade page. Even after restoring to old database.

[nuxsmin]

@alebeta90 it's weird since the new version is not written into configuration file unless it was successful.

I'm trying to find out what can be causing the duplicated column error. Did you try to upgrade multiple times?

Regards

[alebeta90]

@nuxsmin thanks for the reply.

I did try to upgrade before and I was entering the wrong security code. Then when I enter the right security code, it gave me that error.

Thanks

[VexedSyd]

@nuxsmin sorry for the late reply.
I think I had a similar experience, I entered the wrong security key, then entered it correctly, after that I received the error message.

[nuxsmin]

@VexedSyd no worries

Could you restore the database to the previous state and then upgrade again?

[alebeta90]

@nuxsmin I did that, and the same issue... even with the database from before the upgrade attempt

I had been checking the database to make work, but not luck so far...

[VexedSyd]

@nuxsmin
I took server snapshot before the upgrade, then restored the application but not the database :(

[nuxsmin]

Hello, sorry for the late reply...

Could you remove the VersionLevel field from the Plugin table and then perform the upgrade again?

Regards

[VexedSyd]

Good day,
Thanks for following up. I have tried to upgrade again after dropping VersionLevel field from the Plugin table.
The upgrade went through, but unfortunately the custom fields names were empty in the web application. The Custom Fields tables contain the data though.
I updated the empty custom fields names, and now they show in the accounts page, but with no data.

Best regards.

[nuxsmin]

@VexedSyd good day!

Which version are you running? The custom fields issue was solved some releases ago.