Upgrade from version 3.0 to version 3.1 fails #1435

sync-tester · 2019-08-23T19:51:19Z

Hi,

I tried to upgrade my current installation of syspass 3.0 to version 3.1 and got the error message "error while updating the database" Image attached

I had the same issue upgrading to 3.1.0-rc1 and 3.1.0-rc5 but expected it would work with the final stable release.

How can i upgrade my installation?

Thanks in advance for your help

nuxsmin · 2019-08-23T19:55:56Z

Hi, could you check out 'syspass.log' file within '.../app/config' directory?. There should be some error messages.

Regards

sync-tester · 2019-08-23T20:07:22Z

Hi Nuxmin,

here is the log:

#16 /home/vault/public_html/index.php(28): require(String)","caller":"N/A"}
[2019-08-23 15:09:38] syspass.ERROR: logger {"message":"Could not set locale","caller":"SP\Core\Language::setLocales"}
[2019-08-23 15:09:38] syspass.INFO: logger {"message":"Loaded icons cache","caller":"SP\Core\UI\Theme::initIcons"}
[2019-08-23 15:09:38] syspass.INFO: logger {"message":"Loaded actions cache","caller":"SP\Core\Acl\Actions::loadCache"}
[2019-08-23 15:09:38] syspass.DEBUG: upgrade.db.start {"message":"Update DB","user":"N/A","address":"181.52.65.114","caller":"SP\Services\Upgrade\UpgradeDatabaseService::upgrade"} []
[2019-08-23 15:09:38] syspass.DEBUG: upgrade.db.process {"message":"Version: 310.19012201","user":"N/A","address":"181.52.65.114","caller":"SP\Services\Upgrade\UpgradeDatabaseService::applyUpgrade"} []
[2019-08-23 15:09:38] syspass.EXCEPTION: logger {"message":"SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'versionLevel'
#0 /home/vault/public_html/lib/SP/Services/Upgrade/UpgradeDatabaseService.php(173): PDO->exec(String)
#1 /home/vault/public_html/lib/SP/Services/Upgrade/UpgradeDatabaseService.php(108): SP\Services\Upgrade\UpgradeDatabaseService->applyUpgrade(String)
#2 /home/vault/public_html/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 /home/vault/public_html/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 /home/vault/public_html/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 /home/vault/public_html/vendor/klein/klein/src/Klein/Klein.php(588): Klein\Klein->handleRouteCallback(Object(Klein\Route),Object(Klein\DataCollection\RouteCollection),Array)
#8 /home/vault/public_html/lib/SP/Bootstrap.php(464): Klein\Klein->dispatch(Object(Klein\Request))
#9 /home/vault/public_html/lib/Base.php(75): SP\Bootstrap->run(Object(DI\Container))
#10 /home/vault/public_html/index.php(28): require(String)","caller":"N/A"}
[2019-08-23 15:09:38] syspass.EXCEPTION: logger {"message":"Error while updating the database

Many thanks

nuxsmin · 2019-08-23T20:29:12Z

It seems you're trying to try to upgrade over a failed upgrade?: Duplicate column name 'versionLevel

If so please restore a previous backup and then perform the upgrade again.

http://doc.syspass.org/en/3.1/updating/v3.1.html

Regards

sync-tester · 2019-08-23T22:21:59Z

Hello Nuxmin,

as far as i know, this installation was not upgraded before. Anyway, i manually removed the column versionLevel from the Plugin table and now i get this log:

[2019-08-23 18:13:29] syspass.DEBUG: upgrade.db.start {"message":"Update DB","user":"N/A","address":"181.52.65.114","caller":"SP\Services\Upgrade\UpgradeDatabaseService::upgrade"} []
[2019-08-23 18:13:29] syspass.DEBUG: upgrade.db.process {"message":"Version: 310.19012201","user":"N/A","address":"181.52.65.114","caller":"SP\Services\Upgrade\UpgradeDatabaseService::applyUpgrade"} []
[2019-08-23 18:13:30] syspass.DEBUG: upgrade.db.process {"message":"Version: 310.19012201","user":"N/A","address":"181.52.65.114","caller":"SP\Services\Upgrade\UpgradeDatabaseService::applyUpgrade"} []
[2019-08-23 18:13:30] syspass.EXCEPTION: logger {"message":"SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

any idea?

is it possible to make a clean installation of 3.1 and migrate the credentials already saved in the database of the 3.0 installation?

Many thanks for your time and help

dstyles-jg · 2019-08-28T12:31:15Z

@nuxsmin

Hi. We are using 3.1 rc5 - I noted that 3.1 was released.. so.. I cloned the repo into a new directory: /var/www/html/sysPass

I installed php composer and dependencies, chown'd the sysPass dir to the www-data user and chmod 750 on app and config directories.. I also copied the OLD config.xml and the 2 .pem files from OLD syspass to new directory... my old was in /var/www/html/ssap123/ - once this was done I renamed the ssap123 directory to ssap123.OLD and then renamed the new directory (sysPass) to ssap123 and then pointed my browser at the location expecting the upgrade page - I didn't get one. I got a login page - also there is no upgrade key in my config.xml AND from the new login page my existing admin password does not work anymore. For the time being I have reverted to 3.1 rc5 - but, I would like to know is there an upgrade path to 3.1 or do I have to install from scratch again? Can I import the sql backup I made into a fresh install of 3.1? Will I have issues with admin passwords doing this? Please advise on the best way forward.

Regards
DS

And thanks for the software. It is very good.

nuxsmin · 2019-08-28T13:46:06Z

@dstyles-jg Hello, could you check out 'app/config/syspass.log' file for any error messages?

Updating from RC5 to 3.1 doesn't require an upgrade key, since no database structure changes need to be performed.

Regards

dstyles-jg · 2019-08-28T14:08:11Z

It seems that syspass reports "Wrong login" every time I attempt to login. I have checked the password and it is the same one I use for the "old" installation. Basically, when I revert to the 3.1 rc5 directory - the admin pass works. When I use the 3.1 release directory sysPass reports "wrong login". Also in the syspass.log I see this:
[2019-08-28 12:17:48] syspass.EXCEPTION: logger {"message":"Unable to read from file (/var/www/html/mySiteName/app/config/key.pem) - followed a short time later by: [2019-08-28 12:17:48] syspass.EXCEPTION: logger {"message":"Wrong login

it is worth noting that I set the same permissions on the "new" installation - as I followed on the "old" installation and copied the config, key and pubkey files across... they SHOULD be readable so I don't see why the new syspass cannot read them.

dstyles-jg · 2019-08-28T14:17:22Z

meh... file owner on those files was NOT www-data. I missed that... when I copied them across i forgot to chown them back to apache2 user. MY APOLOGIES @nuxsmin. the upgrade process would have been seamless if i didn't forget to change the permissions on those.

alebeta90 · 2019-08-28T16:36:55Z

Hi @nuxsmin

I rolled back to the backup and trying again, without any upgrade failure or mistake, and I get the error of the database update, but in this case, the error is not longer about the duplicate column. In this case it says Foreign key constraint is incorrectly formed

Here is the full log

[2019-08-28 16:33:16] syspass.INFO: logger {"message":"Loaded icons cache","caller":"SP\Core\UI\Theme::initIcons"}
[2019-08-28 16:33:16] syspass.INFO: logger {"message":"Loaded actions cache","caller":"SP\Core\Acl\Actions::loadCache"}
[2019-08-28 16:33:16] syspass.DEBUG: upgrade.db.start {"message":"Update DB","user":"N/A","address":"10.255.0.3","caller":"SP\\Services\\Upgrade\\UpgradeDatabaseService::upgrade"} []
[2019-08-28 16:33:16] 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-28 16:33:16] 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-28 16:33:16] syspass.EXCEPTION: logger {"message":"SQLSTATE[HY000]: General error: 1005 Can't create table `syspass`.`PluginData` (errno: 150 "Foreign key constraint is incorrectly formed")
#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-28 16:33:16] 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"}```

In this case I was using a healthy database. 

I will be researching about it

alebeta90 · 2019-08-28T16:43:24Z

it may be related with this migration https://github.com/nuxsmin/sysPass/blob/master/schemas/31019012201.sql

nuxsmin · 2019-08-28T16:46:40Z

@dstyles-jg no worries. I was wondering about the permission of certificate files, since there should be an error when encrypting the password field...

alebeta90 · 2019-08-28T16:46:52Z

Also reading about the DB schema, Plugin has not defined name as Primary Key then it may be creating a problem with PluginData Foreign Key constraint

look here https://github.com/nuxsmin/sysPass/blob/master/schemas/dbstructure.sql#L443

nuxsmin · 2019-08-28T16:56:31Z

@alebeta90 I'm not sure about that, since a clean install works fine. I think it would be so because the "itemId" definition, that should be "int(11)" under some engine versions. There is a closed issue about that (#1428)

Thanks for the feedback!

alebeta90 · 2019-08-28T17:19:21Z

Hi @nuxsmin I read #1428 but it seems its case was solve.

In my case there is still this failure with the Foreign key, I will keep checking.

But just for information, I run the create table for PluginData and it works with no problem from my database manager.

alebeta90 · 2019-08-28T17:28:52Z

I got this log output from MariaDB

Using command show engine innodb status

```2019-08-28 17:16:22 0x7fa0983c7700 Error in foreign key constraint of table syspass.`PluginData`:

foreign key (name) references Plugin (name)
on update cascade on delete cascade
):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to https://mariadb.com/kb/en/library/foreign-keys/ for correct foreign key definition.
Create table syspass.PluginData with foreign key constraint failed. Field type or character set for column 'name' does not mach referenced column 'name' near '
foreign key (name) references Plugin (name)
on update cascade on delete cascade
)'.```

nuxsmin · 2019-08-28T17:59:15Z

@alebeta90 hmm, it seems an index issue, I'll fix it shortly.

Thanks for the feedback!

alebeta90 · 2019-08-28T18:07:11Z

@nuxsmin thanks to you, let me know if I could provide more useful information.

all the best

devops-tc · 2019-08-30T07:59:24Z

Hi !
We had the same problem and we fixed it by adding COLLATE utf8_unicode_ci at the end of PluginData create table sql command in https://github.com/nuxsmin/sysPass/blob/master/schemas/31019012201.sql :

DELIMITER $$

alter table Plugin
  add versionLevel varchar(15) null $$

create table PluginData
(
  name   varchar(100)    not null,
  itemId int             not null,
  `data` blob            not null,
  `key`  varbinary(2000) not null,
  constraint `PRIMARY`
    primary key (name, itemId),
  constraint fk_PluginData_name
    foreign key (name) references Plugin (name)
      on update cascade on delete cascade
) COLLATE utf8_unicode_ci $$

Hope this helps !

nuxsmin · 2019-08-30T14:39:55Z

@devops-tc thanks for your contribution!!

Yotouille · 2019-09-17T08:12:31Z

Hola,

Same issue here with PluginData table creation, trying to update from 3.0.4 to 3.1:

General error: 1005 Can't create table `syspass`.`PluginData` (errno: 150 "Foreign key constraint is incorrectly formed")

Bye !

pierrehenrymuller · 2019-09-19T08:22:30Z

Hi !
We had the same problem and we fixed it by adding COLLATE utf8_unicode_ci at the end of PluginData create table sql command in https://github.com/nuxsmin/sysPass/blob/master/schemas/31019012201.sql :
DELIMITER $$

alter table Plugin
  add versionLevel varchar(15) null $$

create table PluginData
(
  name   varchar(100)    not null,
  itemId int             not null,
  `data` blob            not null,
  `key`  varbinary(2000) not null,
  constraint `PRIMARY`
    primary key (name, itemId),
  constraint fk_PluginData_name
    foreign key (name) references Plugin (name)
      on update cascade on delete cascade
) COLLATE utf8_unicode_ci $$
Hope this helps !

I have the same problem but this solution don't work for me.
I have an error to : alter table Plugin add versionLevel varchar(15) null $$
I have test to remove this two lines becouse database structure for Plugin already have this structure in 3.0.5.19020701 database version but with no effect.

nuxsmin · 2019-12-21T11:59:59Z

Solved in latest release

fago · 2020-07-22T12:53:55Z

I ran into the same issue while updating from 3.0 to the latest release - so it does not seem to be fixed.
https://github.com/nuxsmin/sysPass/blob/master/schemas/dbstructure.sql does not show a foreign key though, so I guess it just needs to be removed from the update as well?

nuxsmin added kind/question v3 3.0 version labels Aug 23, 2019

anthillinc mentioned this issue Oct 18, 2019

Is it possible to export/import into different versions of SysPass? #1486

Closed

nuxsmin closed this as completed Dec 21, 2019

nuxsmin added kind/bug triage/fixed and removed kind/question labels Dec 21, 2019

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Upgrade from version 3.0 to version 3.1 fails #1435

Upgrade from version 3.0 to version 3.1 fails #1435

sync-tester commented Aug 23, 2019

nuxsmin commented Aug 23, 2019

sync-tester commented Aug 23, 2019

nuxsmin commented Aug 23, 2019

sync-tester commented Aug 23, 2019

dstyles-jg commented Aug 28, 2019

nuxsmin commented Aug 28, 2019

dstyles-jg commented Aug 28, 2019 •

edited

Loading

dstyles-jg commented Aug 28, 2019

alebeta90 commented Aug 28, 2019

alebeta90 commented Aug 28, 2019

nuxsmin commented Aug 28, 2019

alebeta90 commented Aug 28, 2019

nuxsmin commented Aug 28, 2019 •

edited

Loading

alebeta90 commented Aug 28, 2019

alebeta90 commented Aug 28, 2019

nuxsmin commented Aug 28, 2019

alebeta90 commented Aug 28, 2019

devops-tc commented Aug 30, 2019 •

edited

Loading

nuxsmin commented Aug 30, 2019

Yotouille commented Sep 17, 2019 •

edited

Loading

pierrehenrymuller commented Sep 19, 2019

nuxsmin commented Dec 21, 2019

fago commented Jul 22, 2020

Upgrade from version 3.0 to version 3.1 fails #1435

Upgrade from version 3.0 to version 3.1 fails #1435

Comments

sync-tester commented Aug 23, 2019

nuxsmin commented Aug 23, 2019

sync-tester commented Aug 23, 2019

nuxsmin commented Aug 23, 2019

sync-tester commented Aug 23, 2019

dstyles-jg commented Aug 28, 2019

nuxsmin commented Aug 28, 2019

dstyles-jg commented Aug 28, 2019 • edited Loading

dstyles-jg commented Aug 28, 2019

alebeta90 commented Aug 28, 2019

alebeta90 commented Aug 28, 2019

nuxsmin commented Aug 28, 2019

alebeta90 commented Aug 28, 2019

nuxsmin commented Aug 28, 2019 • edited Loading

alebeta90 commented Aug 28, 2019

alebeta90 commented Aug 28, 2019

nuxsmin commented Aug 28, 2019

alebeta90 commented Aug 28, 2019

devops-tc commented Aug 30, 2019 • edited Loading

nuxsmin commented Aug 30, 2019

Yotouille commented Sep 17, 2019 • edited Loading

pierrehenrymuller commented Sep 19, 2019

nuxsmin commented Dec 21, 2019

fago commented Jul 22, 2020

dstyles-jg commented Aug 28, 2019 •

edited

Loading

nuxsmin commented Aug 28, 2019 •

edited

Loading

devops-tc commented Aug 30, 2019 •

edited

Loading

Yotouille commented Sep 17, 2019 •

edited

Loading