Syspass: Upgrading to 2.1

[nuxsmin]

Hello,

because some major improvements that have been done on sysPass, there is a new minor revision. Please post the upgrading issues in this thread.

It's very advisable to read the doc on: https://doc.syspass.org

You can also join to a chat room to discuss the upgrading issues

---

How to test sysPass 2.1 avoiding upgrade the current installation

• Perform a full backup. You can use either the sysPass built-in backup or MySQL workbench tool or mysqldump tool
• Create a new database (eg. syspass21)
• Create a new directory and unpack the new version in it.
• Copy all the files located in the config directory to the new config directory and check for the proper permissions.
• Import the old database into the new one. You could use the tools above mentioned.
• Create the sysPass user (usually sp_admin) in MySQL and grant permissions over the syspass21 database.
• Modify config.xml file to change the database name to syspass21 within the tag <dbName></dbName>. Also check that the dbHostis correctly set.
• Point your browser to the new URL and follow the upgrading steps

If you have more than 500 accounts a progress bar would be displayed.

[madcoda9000]

Hello,

nice to hear that :-)

I'll try this but currebntly a bit frightened because i only have this installation. How can i ensure that no dtat will be lost?

Regards
Sascha

[nuxsmin]

@madcoda9000 You could perform a full backup and then bring up a new sysPass instance (i.e new directory and database), that would be populated with the current data. Are you working with virtualization?

[madcoda9000]

what does that mean exactly. should i create a new empty database. make a clean install and then import the backup (is that possible?? if so, how?)

[nuxsmin]

Brief description of the steps to follow up:

• Perform a full backup. You can use either the sysPass built-in backup or MySQL workbench tool or mysqldump tool
• Create a new database (eg. syspass21)
• Create a new directory and unpack the new version in it.
• Copy all the files located in the config directory to the new config directory
• Import the old database into the new one. You could use the tools above mentioned.
• Point your browser to the new URL and follow the upgrading steps

[testor321]

Hi

Upgrade 2.0 -> 2.1 works OK
thank you

[nuxsmin]

Thanks @testor321 for the feedback!!.

I'm wondering what do you think about the new upgrading process?. Did you get the accounts encryption progress status? (displayed only for more than 500 accounts).

[testor321]

Hi

About progress - nothing. I have only 225 accounts.
About upgrading process - yes, it looks OK. No issues/questions.

I have one issues after upgrade - export to XML doesn't work. But I will investigate more
Error is :
Error while exporting accounts
Invalid header. : appendNode

[nuxsmin]

@testor321 I've just tested the XML export for ~2000 accounts and it worked fine. Which versions are you running on (Information tab)?. Is there any error in the webserver log?

[testor321]

sysPass version 2.1 (2.1.0.17022601)

event log says : XML Export / Error while exporting accounts Invalid header. : appendNode

in apache logs - nothing
ssl_access_log
"POST /sysPass/ajax/ajax_configSave.php HTTP/1.1" 200 156

ssl_request_log
TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 "POST /sysPass/ajax/ajax_configSave.php HTTP/1.1" 156

[nuxsmin]

@testor321 And what about the PHP version?

[testor321]

php -v
PHP 7.0.15 (cli) (built: Jan 17 2017 16:07:55) ( NTS )
Copyright (c) 1997-2017 The PHP Group
Zend Engine v3.0.0, Copyright (c) 1998-2017 Zend Technologies

[madcoda9000]

@nuxsmin I did the following:

• exported all Data from syspass 2.0 installation
• unzipped the new release
• copy all files under config folder from 2.0 installation to the new config folder
• set the folder permissions and owner
• created an empty database syspass21
• imported all Data from previous export
• called the page in the browser. The i see the installation wizard. Not the upgrade wizard

Regards
Sascha

[nuxsmin]

Ummm, sorry, I forgot to mention that you need to create the same sysPass user in the database and grant permissions over the sysPass database. Finally, change the database name in the config.xml file to the new one.

[madcoda9000]

ok, i did so. But it shows the install wizard too :-(

[nuxsmin]

It could be because the installed tag in the config file is set to 1. Please set it to 0

[nuxsmin]

Recap:

How to test sysPass 2.1 avoiding upgrade the current installation

• Perform a full backup. You can use either the sysPass built-in backup or MySQL workbench tool or mysqldump tool
• Create a new database (eg. syspass21)
• Create a new directory and unpack the new version in it.
• Copy all the files located in the config directory to the new config directory and check for the proper permissions.
• Import the old database into the new one. You could use the tools above mentioned.
• Create the sysPass user (usually so_admin) in MySQL and grant permissions over the syspass21 database.
• Modify config.xml file to change the database name to syspass21 within the tag <dbName></dbName>. Also check that the dbHostis correctly set.
• Point your browser to the new URL and follow the upgrading steps

[madcoda9000]

@nuxsmin sorry for bothering :-)

I did all steps as mentioned and it always displays the install wizard. wether the installed tag is set to 1 or 0

[madcoda9000]

uhhh, my fault :-). The owner permission where not correct on the files in the config folder...

[madcoda9000]

so, the update went well and no errors occured. I've seen no progessbar as thre where only 222 accounts in the database.

[nuxsmin]

Great!!, So you can check it out and then upgrade the current installation.

[nuxsmin]

One question, do you remember how many time did it take?

[madcoda9000]

1. i've upgraded the current installation (because i've used a copy of the production adatabase). So there is no need to make this update on the old database again.

2. i assume so round about a minute

[nuxsmin]

i've upgraded the current installation (because i've used a copy of the production adatabase). So there is no need to make this update on the old database again.

Sure, but now you have a testing environment for future releases :)

i assume so round about a minute

I expected so

Thanks!

[madcoda9000]

Sure, but now you have a testing environment for future releases :)

Yes, i'll do it the way i did now.

• new database
• import data
• new webfolder'
• try the upgrade

if it fails, i am not worry as my original install still works.

[nuxsmin]

That's the developer day to day :)

[Yotouille]

¡Hola!

I've tried to upgrade this morning ! But failed again... 😢
Still using 1.2, and upgrading now to 2.1. The upgrade process ask me about the upgradekey, a valid user ID, master password, and a valid username. The process failed and bring me back to the upgrade page. Happy to find a log file in the config ! :)

Here it is (I've cut the part with dozens lines of Parámetro):

2017-03-02 10:24:19 - Action: Actualizar Configuración -- Description:  -- Details: Parámetro : account_count
(...Cropped Part, 44 lines of "Parámetros" /yotouille...)
Versión : 21017022601
2017-03-02 10:24:19 - Action: Actualización -- Description: Actualización de versión realizada. -- Details: Versión : 21017022601
Tipo : config
2017-03-02 10:24:19 - initError
2017-03-02 10:24:19 - L'application a besoin d'être mise à jour
2017-03-02 10:24:19 - Si vous êtes un administrateur, cliquez sur ce lien: <a href="index.php?a=upgrade&type=db">Mise à jour</a>
2017-03-02 10:26:14 - initError
2017-03-02 10:26:14 - Application en maintenance
2017-03-02 10:26:14 - Sera opérationnel sous peu
2017-03-02 10:26:26 - Register Task: upgrade
2017-03-02 10:26:26 - auxPreDbUpgrade: 1316011001
2017-03-02 10:26:26 - upgradeDB: 1300000000
2017-03-02 10:26:27 - auxPreDbUpgrade: 1316100601
2017-03-02 10:26:27 - upgradeDB: 1316100601
2017-03-02 10:26:29 - Exception: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`db_syspass`.`#sql-5b21_202cd72`, CONSTRAINT `fk_acc$
2017-03-02 10:26:29 - #0  SP\Storage\DB->prepareQueryData() called at [/var/www/sysPass/inc/SP/Storage/DB.class.php:150]
#1  SP\Storage\DB->doQuery() called at [/var/www/sysPass/inc/SP/Storage/DB.class.php:341]
#2  SP\Storage\DB::getQuery() called at [/var/www/sysPass/inc/SP/Core/Upgrade/Upgrade.class.php:169]
#3  SP\Core\Upgrade\Upgrade::upgradeDB() called at [/var/www/sysPass/inc/SP/Core/Upgrade/Upgrade.class.php:78]
#4  SP\Core\Upgrade\Upgrade::doUpgrade() called at [/var/www/sysPass/inc/SP/Controller/MainActionController.class.php:102]
#5  SP\Controller\MainActionController->upgrade() called at [/var/www/sysPass/inc/SP/Controller/MainActionController.class.php:72]
#6  SP\Controller\MainActionController->doAction() called at [/var/www/sysPass/ajax/ajax_upgrade.php:36]

2017-03-02 10:26:29 - Error en la consulta
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`db_syspass`.`#sql-5b21_202cd72`, CONSTRAINT `fk_accHistory_userGroup_id` FOREIGN KEY$
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-02 10:26:29 - SQL : 'ALTER TABLE `accHistory`  ADD CONSTRAINT `fk_accHistory_userGroup_id`FOREIGN KEY (`acchistory_userGroupId`)REFERENCES `usrGroups` (`usergroup_id`)  ON DELETE NO ACTION  ON UPDATE NO$
2017-03-02 10:26:29 - Deregister Task: upgrade

I think sysPass don't like my DB, at all... 😞

[c1xx]

Hi Rubén,

I'm trying to update my sysPass 2.0 to 2.1 and getting this error:

SyntaxError: JSON.parse: unexpected character at line 1 column 1 of the JSON dataparsererror

Whats wrong?

Thank you,
Lukas

[nuxsmin]

Hello @Yotouille and @lukasp2

I've just pushed a new commit that would solve these issues.

@Yotouille I missed to include the groups check on the upgrade process. Merci!
@lukasp2 Yesterday, while upgrading the demo site, I noticed that some browser headers were removed, so the Ajax requests weren't properly managed.

[Yotouille]

¡No hay de qué!

I'll retry to upgrade asap today !

Thanks to you !

[c1xx]

@nuxsmin same error :-(

SyntaxError: JSON.parse: unexpected character at line 1 column 1 of the JSON dataparsererror

[nuxsmin]

@lukasp2 did you download from the master branch?. Is there any error in the webserver log?. There is also a syspass.log file in the config directory, so it could be any stuff there...

[c1xx]

yes, I downloaded last master branch.

output from syspass.log:

2017-03-02 13:23:14 - initError
2017-03-02 13:23:14 - Application in maintenance mode
2017-03-02 13:23:14 - It will start shortly

[nuxsmin]

And what about the webserver log?

[c1xx]

xxx.xxx.technology [Thu Mar 02 12:39:06.644112 2017] [proxy_http:error] [pid 74192:tid 140635121960704] (20014)Internal error: [client 185.111.192.4:25908] AH01102: error reading status line from remote server 127.0.0.1:81, referer https://xxx.xxx.technology/

xxx.xxx.technology [Thu Mar 02 12:39:06.644143 2017] [proxy:error] [pid 74192:tid 140635121960704] [client 185.111.192.4:25908] AH00898: Error reading from remote server returned by /css/css.php, referer https://xxx.xxx.technology/

xxx.xxx.technology [Thu Mar 02 12:40:25.022184 2017] [proxy_http:error] [pid 74192:tid 140635373741824] (20014)Internal error: [client 185.111.192.4:45682] AH01102: error reading status line from remote server 127.0.0.1:81, referer https://xxx.xxx.technology/

xxx.xxx.technology [Thu Mar 02 12:40:25.022216 2017] [proxy:error] [pid 74192:tid 140635373741824] [client 185.111.192.4:45682] AH00898: Error reading from remote server returned by /css/css.php, referer https://xxx.xxx.technology/

[nuxsmin]

@lukasp2 sorry, I missed to modify a file to get it working...

[c1xx]

@nuxsmin great, that worked for me! Thank you very much :)

[nuxsmin]

It was an issue related with some proxy servers (they "eat" some HTTP headers)...

[Yotouille]

Heyyyy ! It worked for me also ! 😃

Great !!

BTW, during the upgrade process it shown a message "do not close window/tab" (with the rolling wheel), but it ended on a Gateway Time-out error. I did panic a bit I confess, but accessing on my sysPass login page, all seems to work fine ! No errors logged this time !

note: we are using Nginx to publish the website.
--edit-- and I ran the upgrade from Chrome ----

Thanks again for this great work ! 🎉

[nuxsmin]

@Yotouille that error could be because the Nginx proxy? timeout. The encryption process could take some time, so the PHP script holds the webserver connection for that time and some servers break it after some time.

Please check the sysPass event log and the accounts passwords are visible...

Thanks so much for the feedback an tests!

[c1xx]

@nuxsmin Oh, I see that my custom created pasword fields are filled just with "*" when I use the show password function :-(

[nuxsmin]

And what about the normal passwords?

[nuxsmin]

The "eye" icon is designed to view the password, but it will be shown as a tooltip, and not revealing the password instead.

[c1xx]

normales passwords are fine.
Yes I know but when I copy the password via clipboard icon -> the password is: *

[Yotouille]

Here all the password seems okay, same for customs fields. 👍

About the Timeout I can confirm it come from Nginx:
upstream timed out (110: Operation timed out) while reading response header from upstream
This timeout is set to 60 seconds by default, it does not seems to have impaired the upgrade process, here are the last logs in syspass (before my login):

After some Migrate events, It made a config_backup (Configuration events) and finished with:

4764    2017-03-02 13:39:22 INFO    Actualisation   MyUser  192.168.100.3   Mise à jour de version effectuée.
Version : 12018 => 21017030201
Type : db

All contents seems to have follow the upgrade without issue !

[nuxsmin]

Ok @Yotouille it seems that went fine 👌

[c1xx]

How I can fix my problem ?

[nuxsmin]

Let me take a look to the code ;)

[nuxsmin]

@lukasp2 does the eye icon display the password?. I've just tested the demo site and the copy to clipboard works fine.

[c1xx]

@nuxsmin maybe something went during convert progress wrong :-/

[c1xx]

No the eye icon doesn't display the password

[nuxsmin]

Umm, could you try it out with a new user?

[wseredynski]

@nuxsmin Is there any chance for docker releases?

[nuxsmin]

Sure @wseredynski , luckily it's already on docker...

https://doc.syspass.org/en/installing/docker.html

[wseredynski]

@nuxsmin It seems last docker build is 2.0.

[nuxsmin]

You're right @wseredynski, I forgot to push the changes so the docker trigger didn't take place.

I've just pushed :)

[wseredynski]

@nuxsmin Thanks!

[nuxsmin]

@wseredynski You're welcome

[derStephan]

Upgrade from 1.2 to 2.1 worked this time. Thanks for that. But there were quite a lot of spanish messages during the process. My language is set to German.

[nuxsmin]

@derStephan ummm, it shouldn't be so, because the untranslated/missing strings should be set in english. BTW, I'm happy that it worked fine this time...

[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\UpgradeupgradeDB
Caller 4: SP\Core\Upgrade\Upgrade\doUpgrade
Caller 5: SP\Controller\MainActionControllerupgrade
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 ...`

[nuxsmin]

@bitking sorry for the delay, I missed your question among the issues...

Regarding the orphaned items, you can proceed without any concern, because some items will be automatically create to keep the database constraints.

The error thrown is caused by the database configuration, because it doesn't allow blank (0'es) dates, so you could tweak its configuration to avoid the issue: https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_sql_mode

[berniepeers]

Hi @nuxsmin, I've just update my sysPass 1.2.0.21 to the new newest 2.1.9.
I found a couple of troubles along the update process, but after restoring the db dump and applied the following fixes it terminated successfully:

2017-05-05 12:14:58 - SQL : 'ALTER TABLEaccTagsADD CONSTRAINTfk_accTags_accounts_idFOREIGN KEY (acctag_accountId)REFERENCESaccounts(account_id) ON DELETE CASCADE ON UPDATE CASCADE'

2017-05-05 12:23:07 - SQL : 'ALTER TABLEaccTagsADD CONSTRAINTfk_accTags_tags_idFOREIGN KEY (acctag_tagId)REFERENCEStags(tag_id) ON DELETE CASCADE ON UPDATE CASCADE'

2017-05-05 12:28:07 - SQL : 'CREATE TABLEtrack(track_idINT UNSIGNED NOT NULL AUTO_INCREMENT,track_userIdSMALLINT(5) UNSIGNED NULL,track_sourceVARCHAR(100) NOT NULL,track_timeINT UNSIGNED NOT NULL,track_ipv4BINARY(4) NOT NULL,track_ipv6BINARY(16) NULL, PRIMARY KEY (track_id), INDEXIDX_userId(track_userIdASC), INDEXIDX_time-ip-source(track_timeASC,track_ipv4ASC,track_ipv6ASC,track_sourceASC)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8'

alter table accTags drop foreign key fk_accTags_accounts_id;
alter table accTags drop foreign key fk_accTags_tags_id;
drop table track;

Greetings and thank you for your work!
Bernabè

[nuxsmin]

Hi @atahualpaii !

It's nice to hear that it went fine, but did you execute the last queries?:

alter table accTags drop foreign key fk_accTags_accounts_id; alter table accTags drop foreign key fk_accTags_tags_id; drop table track;

Kind regards

[berniepeers]

Yes, I did. Each query line was related to an error message in the syspass.log file.

[nuxsmin]

They will be needed by the application and some features will be broken.

The tags table constraints are needed to delete/update the tags when they are set on the account (they are updated every time the account is saved using tags).

The track table will be used on every failed login, because it will lock the client login attempts when they are exceeded.

[berniepeers]

Finally they have been created though. I don't know why but I already had them in my db.
Here is my log: https://pastebin.com/EhXnChCt

Thanks again.

[nuxsmin]

@atahualpaii that sounds better!. I'll take a look to your log to look for any issue.

Thanks for the feedback!

[akzov]

2.1 upgrade is failing for me, no progress bar just error after 5 minutes. From error log:

[Mon May 08 12:11:26.400802 2017] [fcgid:warn] [pid 39431] [client 172.16.x:58916] mod_fcgid: stderr: PHP Warning: Invalid argument supplied for foreach() in /public_html/inc/SP/Core/SessionUtil.class.php on line 113
[Mon May 08 12:21:04.209517 2017] [fcgid:warn] [pid 39432] (104)Connection reset by peer: [client 172.16.x:58933] mod_fcgid: error reading data from FastCGI server, referer: https://website/index.php?a=upgrade&type=db
[Mon May 08 12:21:04.209559 2017] [fcgid:warn] [pid 39432] (104)Connection reset by peer: [client 172.16.x:58933] mod_fcgid: ap_pass_brigade failed in handle_request_ipc function, referer: https://website/index.php?a=upgrade&type=db
[Mon May 08 12:21:04.210166 2017] [fcgid:warn] [pid 39429] (104)Connection reset by peer: [client 172.16.x:58934] mod_fcgid: error reading data from FastCGI server, referer: https://website/index.php?a=upgrade&type=db
[Mon May 08 12:21:04.210201 2017] [core:error] [pid 39429] [client 172.16.x:58934] End of script output before headers: ajax_upgrade.php, referer: https://website/index.php?a=upgrade&type=db

[nuxsmin]

@akzov it seems that you're having a timeout issue on your upstream webserver. There is an old issue related to nginx timeout, please take a look to it.

Kind regards

[nuxsmin]

Please read the doc on: https://doc.syspass.org