Error 1060 during upgrade with Authenticator Plugin

[it-dermalog]

sysPass Version
3.0

Describe the question

Does the Process of upgrading change in any way if i use a Plugin (the Authenticator Plugin)?
At what point do i have to download a plugin if i Upgrade the sysPass instance?
how can i fix this error?

Screenshots
If applicable, add screenshots to help explain your problem.

Platform (please complete the following information):

• OS: Ubuntu 18.04.3 Server
• Browser Chrome

Additional context
Hi,

I tried to do an upgrade of our sysPass installation with the Authenticator Plugin.

Before I did a full snapshot of the VM including the database.
I followed the upgrade instructions to the point where I hat to enter the upgrade security key.
after that the interface just gave an error "Error while updating the database"

in the log I noticed the following line:
[2019-12-10 18:48:19] syspass.EXCEPTION: logger {"message":"SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'versionLevel'

I Searched a bit in the database of the sysPass Instance and found the entry "versionLevel" inside the "Plugin" table.

this pointed me to the the conclusion that maybe something with the Plugin causes the issues.

After a snapshot rollback the problem still exists.

More detailed log files below:

[2019-12-10 18:48:19] 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(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-12-10 18:48:19] 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"}
[2019-12-10 18:49:13] syspass.INFO: logger {"message":"Extensions checked","caller":"SP\Core\PhpExtensionChecker::checkMandatory"}
[2019-12-10 18:49:13] syspass.INFO: logger {"message":"Loaded icons cache","caller":"SP\Core\UI\Theme::initIcons"}
[2019-12-10 18:49:13] syspass.ERROR: logger {"message":"Upgrade needed","caller":"SP\Modules\Web\Init::initialize"}
[2019-12-10 18:49:13] syspass.INFO: logger {"message":"Loaded actions cache","caller":"SP\Core\Acl\Actions::loadCache"}

[it-dermalog]

Update:

After digging deeper i found that the issue was inside one of the sql scripts (sysPass/schemas/31019012201.sql).

It tries to create tables that for some reason already existed in our database so i did a quick workaround by letting it create a placeholder table so the script could run through.

The update then went through without errors and i deleted the placeholder table afterwards.
The authentication Plugin could also be correctly installed after the update.

contents of the modified sql script:

DELIMITER $$

create table PluginDataPLACEHOLDER
(
  name   varchar(100)    not null,
  itemId int             not null,
  `data` blob            not null,
  `key`  varbinary(2000) not null
)$$

This was only a quick and dirty fix so i wont be surprised if it causes issues in a future update but for know it worked.