Syspass: Error updating database (from 1.2 to 2.0)
I get the following error log while attempting to upgrade sysPass and its database, following the instructions exactly at the update page:
[Tue Feb 14 15:10:47.718689 2017] [:error] [pid 20861] [client 192.168.1.xxx:56052] Action: Actualizar Configuraci\xc3\xb3n -- Description: -- Details: Par\xc3\xa1metro : account_count\nPar\xc3\xa1metro : account_link\nPar\xc3\xa1metro : checkupdates\nPar\xc3\xa1metro : checknotices\nPar\xc3\xa1metro : dbhost\nPar\xc3\xa1metro : dbname\nPar\xc3\xa1metro : dbpass\nPar\xc3\xa1metro : dbuser\nPar\xc3\xa1metro : debug\nPar\xc3\xa1metro : demo_enabled\nPar\xc3\xa1metro : globalsearch\nPar\xc3\xa1metro : installed\nPar\xc3\xa1metro : maintenance\nPar\xc3\xa1metro : passwordsalt\nPar\xc3\xa1metro : session_timeout\nPar\xc3\xa1metro : sitelang\nPar\xc3\xa1metro : version\nPar\xc3\xa1metro : config_hash\nPar\xc3\xa1metro : proxy_enabled\nPar\xc3\xa1metro : proxy_pass\nPar\xc3\xa1metro : proxy_port\nPar\xc3\xa1metro : proxy_server\nPar\xc3\xa1metro : proxy_user\nPar\xc3\xa1metro : resultsascards\nPar\xc3\xa1metro : sitetheme\nPar\xc3\xa1metro : account_passtoimage\nPar\xc3\xa1metro : files_allowed_exts\nPar\xc3\xa1metro : files_allowed_size\nPar\xc3\xa1metro : files_enabled\nPar\xc3\xa1metro : ldap_base\nPar\xc3\xa1metro : ldap_enabled\nPar\xc3\xa1metro : ldap_group\nPar\xc3\xa1metro : ldap_server\nPar\xc3\xa1metro : log_enabled\nPar\xc3\xa1metro : mail_enabled\nPar\xc3\xa1metro : mail_from\nPar\xc3\xa1metro : mail_server\nPar\xc3\xa1metro : wiki_enabled\nPar\xc3\xa1metro : wiki_filter\nPar\xc3\xa1metro : wiki_searchurl\nVersi\xc3\xb3n : 20017021302, referer: https://xxxxxxxxxxxx.net/
[Tue Feb 14 15:10:47.718984 2017] [:error] [pid 20861] [client 192.168.1.xxx:56052] Action: Actualizaci\xc3\xb3n -- Description: Actualizaci\xc3\xb3n de versi\xc3\xb3n realizada. -- Details: Versi\xc3\xb3n : 20017021302\nTipo : config, referer: https://xxxxxxxxxxxx.net/
[Tue Feb 14 15:10:47.726295 2017] [:error] [pid 20861] [client 192.168.1.xxx:56052] initError, referer: https://xxxxxxxxxxxx.net/
[Tue Feb 14 15:10:47.726311 2017] [:error] [pid 20861] [client 192.168.1.xxx:56052] The application needs to be updated, referer: https://xxxxxxxxxxxx.net/
[Tue Feb 14 15:10:47.726322 2017] [:error] [pid 20861] [client 192.168.1.xxx:56052] If you are an administrator, click on the link: <a href="index.php?upgrade=1&a=upgrade">Update</a>, referer: https://xxxxxxxxxxxx.net/
[Tue Feb 14 15:10:47.804479 2017] [:error] [pid 20863] [client 192.168.1.xxx:56064] initError
[Tue Feb 14 15:10:47.804506 2017] [:error] [pid 20863] [client 192.168.1.xxx:56064] Application in maintenance
[Tue Feb 14 15:10:47.804519 2017] [:error] [pid 20863] [client 192.168.1.xxx:56064] It will be running shortly
[Tue Feb 14 15:10:50.321780 2017] [:error] [pid 20861] [client 192.168.1.xxx:56146] initError
[Tue Feb 14 15:10:50.321823 2017] [:error] [pid 20861] [client 192.168.1.xxx:56146] Application in maintenance
[Tue Feb 14 15:10:50.321832 2017] [:error] [pid 20861] [client 192.168.1.xxx:56146] It will be running shortly
[Tue Feb 14 15:11:20.778624 2017] [:error] [pid 20864] [client 192.168.1.xxx:57128] Exception: SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'publicLinks' already exists, referer: https://xxxxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 15:11:20.780168 2017] [:error] [pid 20864] [client 192.168.1.xxx:57128] #0 SP\\Storage\\DB->prepareQueryData() called at [/var/www/html/inc/SP/Storage/DB.class.php:150]\n#1 SP\\Storage\\DB->doQuery() called at [/var/www/html/inc/SP/Storage/DB.class.php:347]\n#2 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Core/Upgrade.class.php:109]\n#3 SP\\Core\\Upgrade::upgradeDB() called at [/var/www/html/inc/SP/Core/Upgrade.class.php:64]\n#4 SP\\Core\\Upgrade::doUpgrade() called at [/var/www/html/inc/SP/Core/Init.class.php:647]\n#5 SP\\Core\\Init::checkDbVersion() called at [/var/www/html/inc/SP/Core/Init.class.php:166]\n#6 SP\\Core\\Init::start() called at [/var/www/html/inc/Base.php:107]\n#7 require(/var/www/html/inc/Base.php) called at [/var/www/html/index.php:31]\n, referer: https://xxxxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 15:11:20.781507 2017] [:error] [pid 20864] [client 192.168.1.xxx:57128] Error en la consulta\nSQLSTATE[42S01]: Base table or view already exists: 1050 Table 'publicLinks' already exists (42), referer: https://xxxxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 15:11:20.781524 2017] [:error] [pid 20864] [client 192.168.1.xxx:57128] Caller 1: SP\\Storage\\DB\\logDBException, referer: https://xxxxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 15:11:20.781530 2017] [:error] [pid 20864] [client 192.168.1.xxx:57128] Caller 2: SP\\Storage\\DB\\getQuery, referer: https://xxxxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 15:11:20.781582 2017] [:error] [pid 20864] [client 192.168.1.xxx:57128] Caller 3: SP\\Core\\Upgrade\\upgradeDB, referer: https://xxxxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 15:11:20.781592 2017] [:error] [pid 20864] [client 192.168.1.xxx:57128] Caller 4: SP\\Core\\Upgrade\\doUpgrade, referer: https://xxxxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 15:11:20.781660 2017] [:error] [pid 20864] [client 192.168.1.xxx:57128] Caller 5: SP\\Core\\Init\\checkDbVersion, referer: https://xxxxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 15:11:20.781666 2017] [:error] [pid 20864] [client 192.168.1.xxx:57128] Caller 6: SP\\Core\\Init\\start, referer: https://xxxxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 15:11:20.781672 2017] [:error] [pid 20864] [client 192.168.1.xxx:57128] Caller 7: \\require, referer: https://xxxxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 15:11:20.781741 2017] [:error] [pid 20864] [client 192.168.1.xxx:57128] SQL : 'CREATE TABLE `publicLinks` ( publicLink_id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, publicLink_itemId INT UNSIGNED, publicLink_hash VARBINARY(100) NOT NULL, publicLink_linkData LONGBLOB)', referer: https://xxxxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 15:11:20.789106 2017] [:error] [pid 20864] [client 192.168.1.xxx:57128] initError, referer: https://xxxxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 15:11:20.789207 2017] [:error] [pid 20864] [client 192.168.1.xxx:57128] Error al aplicar la actualizaci\xc3\xb3n de la Base de Datos, referer: https://xxxxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 15:11:20.789215 2017] [:error] [pid 20864] [client 192.168.1.xxx:57128] Compruebe el registro de eventos para m\xc3\xa1s detalles<p class="center"><a href="index.php?nodbupgrade=1">Sign in</a></p>, referer: https://xxxxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 15:11:20.854715 2017] [:error] [pid 20863] [client 192.168.1.xxx:57164] initError
[Tue Feb 14 15:11:20.854755 2017] [:error] [pid 20863] [client 192.168.1.xxx:57164] Application in maintenance
[Tue Feb 14 15:11:20.854763 2017] [:error] [pid 20863] [client 192.168.1.xxx:57164] It will be running shortly
After telling me (in spanish) the database failed to update, and to check the event log, it asks me to sign in. After signing in, it simply logs right back out and tells me Session finished, then the application goes into maintenance mode.
Running Ubuntu 16.04.1, apache2, MySQL on a different host.
piknik
All 66 comments
Hi, which sysPass version were you running before the upgrade?
nuxsmin
on 14 Feb 2017
Could log in using the URL "index.php?nodbupgrade=1"?
nuxsmin
on 14 Feb 2017
This issue is weird, because the error says that the table "publicLinks" does exist, but it can't be, because it's created by applying the db scripts. Did you perform any upgrade before?
nuxsmin
on 14 Feb 2017
This bug, or one very much like it, is also affecting me. Upgrading from 1.2. Logs:
[Tue Feb 14 12:35:36 2017] [error] [client 10.2.101.138] #0 SP\Storage\DB->prepareQueryData() called at [/export/home/nagios/syspass.example/sysPass-2.0.0.17021302/inc/SP/Storage/DB.class.php:150]\n#1 SP\Storage\DB->doQuery() called at [/export/home/nagios/syspass.example/sysPass-2.0.0.17021302/inc/SP/Storage/DB.class.php:347]\n#2 SP\Storage\DB::getQuery() called at [/export/home/nagios/syspass.example/sysPass-2.0.0.17021302/inc/SP/Core/Upgrade.class.php:109]\n#3 SP\Core\Upgrade::upgradeDB() called at [/export/home/nagios/syspass.example/sysPass-2.0.0.17021302/inc/SP/Core/Upgrade.class.php:64]\n#4 SP\Core\Upgrade::doUpgrade() called at [/export/home/nagios/syspass.example/sysPass-2.0.0.17021302/inc/SP/Core/Init.class.php:647]\n#5 SP\Core\Init::checkDbVersion() called at [/export/home/nagios/syspass.example/sysPass-2.0.0.17021302/inc/SP/Core/Init.class.php:166]\n#6 SP\Core\Init::start() called at [/export/home/nagios/syspass.example/sysPass-2.0.0.17021302/inc/Base.php:109]\n#7 require(/export/home/nagios/syspass.example/sysPass-2.0.0.17021302/inc/Base.php) called at [/export/home/nagios/syspass.example/sysPass-2.0.0.17021302/index.php:31]\n, referer: https://syspass.example/index.php?upgrade=1&a=upgrade
[Tue Feb 14 12:35:36 2017] [error] [client 10.2.101.138] Caller 1: SP\Storage\DB\logDBException, referer: https://syspass.example/index.php?upgrade=1&a=upgrade
[Tue Feb 14 12:35:36 2017] [error] [client 10.2.101.138] Caller 2: SP\Storage\DB\getQuery, referer: https://syspass.example/index.php?upgrade=1&a=upgrade
[Tue Feb 14 12:35:36 2017] [error] [client 10.2.101.138] Caller 3: SP\Core\Upgrade\upgradeDB, referer: https://syspass.example/index.php?upgrade=1&a=upgrade
[Tue Feb 14 12:35:36 2017] [error] [client 10.2.101.138] Caller 4: SP\Core\Upgrade\doUpgrade, referer: https://syspass.example/index.php?upgrade=1&a=upgrade
[Tue Feb 14 12:35:36 2017] [error] [client 10.2.101.138] Caller 5: SP\Core\Init\checkDbVersion, referer: https://syspass.example/index.php?upgrade=1&a=upgrade
[Tue Feb 14 12:35:36 2017] [error] [client 10.2.101.138] Caller 6: SP\Core\Init\start, referer: https://syspass.example/index.php?upgrade=1&a=upgrade
[Tue Feb 14 12:35:36 2017] [error] [client 10.2.101.138] Caller 7: \require, referer: https://syspass.example/index.php?upgrade=1&a=upgrade
[Tue Feb 14 12:35:36 2017] [error] [client 10.2.101.138] SQL : 'ALTER TABLE accounts DROP INDEX IDX_searchTxt', referer: https://syspass.example/index.php?upgrade=1&a=upgrade
[Tue Feb 14 12:35:36 2017] [error] [client 10.2.101.138] initError, referer: https://syspass.example/index.php?upgrade=1&a=upgrade
[Tue Feb 14 12:35:36 2017] [error] [client 10.2.101.138] Error al aplicar la actualizaci\xc3\xb3n de la Base de Datos, referer: https://syspass.example/index.php?upgrade=1&a=upgrade
[Tue Feb 14 12:35:36 2017] [error] [client 10.2.101.138] Compruebe el registro de eventos para m\xc3\xa1s detalles
48kRAM
on 14 Feb 2017
Hi @48kRAM your issue is related to a table index that wasn't created before, so you can quickly skip that by commenting out (by adding "-- " or removing) the line 10 in the "/inc/sql/1316011001.sql" file
nuxsmin
on 14 Feb 2017
Thanks - that gets me further. Now I'm running into:
[Tue Feb 14 14:36:29 2017] [error] [client 10.2.101.138] Error en la consulta\nSQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`syspass`.`#sql-e30c_36d9cb9`, CONSTRAINT `fk_accHistory_users_edit_id` FOREIGN KEY (`acchistory_userEditId`) REFERENCES `usrData` (`user_id`)) (23000), referer: https://syspass.example/index.php?upgrade=1&a=upgrade
[Tue Feb 14 14:36:29 2017] [error] [client 10.2.101.138] SQL : 'ALTER TABLE `accHistory` ADD CONSTRAINT `fk_accHistory_users_edit_id`FOREIGN KEY (`acchistory_userEditId`)REFERENCES `usrData` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT', referer: https://syspass.example/index.php?upgrade=1&a=upgrade
Am I skipping some required intermediate version by going from 1.2.0.12 to 2.0?
48kRAM
on 14 Feb 2017
Umm, it seems that you deleted some users that were used in any accounts, so now it fails because some accounts in the history uses them....let me code some query to bypass that issue
nuxsmin
on 14 Feb 2017
Ah yes, that makes sense (staff turnover). Should I open a new issue for this? It seems I've stolen an issue that isn't actually related to mine. Apologies.
48kRAM
on 14 Feb 2017
No worries ;)
nuxsmin
on 14 Feb 2017
@nuxsmin I'm running sysPass 1.2 from the latest git version in the v1.2 branch. I've just tried again, same problem as before.
Also, I've used the nodbupgrade=1 query right after updating the git version to 2.0 master, blank page, and the error in the log is this:
[Tue Feb 14 20:17:46.998264 2017] [:error] [pid 20862] [client 192.168.1.xxx:59710] Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'log_level' in 'field list'
[Tue Feb 14 20:17:46.998334 2017] [:error] [pid 20862] [client 192.168.1.xxx:59710] #0 SP\\Storage\\DB->prepareQueryData() called at [/var/www/html/inc/SP/Storage/DB.class.php:150]\n#1 SP\\Storage\\DB->doQuery() called at [/var/www/html/inc/SP/Storage/DB.class.php:347]\n#2 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#3 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#4 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#5 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#6 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#7 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#8 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#9 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#10 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#11 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#12 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#13 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#14 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#15 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#16 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#17 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#18 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#19 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#20 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#21 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#22 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#23 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#24 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#25 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#26 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#27 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#28 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#29 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#30 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#31 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#32 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#33 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#34 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#35 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#36 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#37 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#38 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#39 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#40 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#41 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#42 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#43 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#44 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#45 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#46 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#47 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#48 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#49 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#50 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#51 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#52 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#53 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#54 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#55 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#56 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#57 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#58 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#59 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#60 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#61 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#62 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#63 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#64 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#65 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#66 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#67 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#68 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#69 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#70 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#71 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#72 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#73 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#74 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#75 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#76 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#77 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#78 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#79 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#80 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#81 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#82 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#83 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#84 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#85 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#86 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Log/Log.class.php:165]\n#87 SP\\Log\\Log->writeLog() called at [/var/www/html/inc/SP/Storage/DB.class.php:300]\n#88 SP\\Storage\\DB::logDBException() called at [/var/www/html/inc/SP/Storage/DB.class.php:351]\n#
[Tue Feb 14 20:17:46.998837 2017] [:error] [pid 20862] [client 192.168.1.xxx:59710] PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes) in /var/www/html/inc/SP/Storage/DB.class.php on line 252
... about 1000+ times, causing overflow.
piknik
on 14 Feb 2017
This time I've dropped the database completely then restored it from my backup after noticing your comment that publicLinks exists. This is the error now:
[Tue Feb 14 20:30:42.662355 2017] [:error] [pid 20861] [client 192.168.1.xxx:56710] Action: Actualizar Configuraci\xc3\xb3n -- Description: -- Details: Par\xc3\xa1metro : account_count\nPar\xc3\xa1metro : account_link\nPar\xc3\xa1metro : checkupdates\nPar\xc3\xa1metro : checknotices\nPar\xc3\xa1metro : dbhost\nPar\xc3\xa1metro : dbname\nPar\xc3\xa1metro : dbpass\nPar\xc3\xa1metro : dbuser\nPar\xc3\xa1metro : debug\nPar\xc3\xa1metro : demo_enabled\nPar\xc3\xa1metro : globalsearch\nPar\xc3\xa1metro : installed\nPar\xc3\xa1metro : maintenance\nPar\xc3\xa1metro : passwordsalt\nPar\xc3\xa1metro : session_timeout\nPar\xc3\xa1metro : sitelang\nPar\xc3\xa1metro : version\nPar\xc3\xa1metro : config_hash\nPar\xc3\xa1metro : proxy_enabled\nPar\xc3\xa1metro : proxy_pass\nPar\xc3\xa1metro : proxy_port\nPar\xc3\xa1metro : proxy_server\nPar\xc3\xa1metro : proxy_user\nPar\xc3\xa1metro : resultsascards\nPar\xc3\xa1metro : sitetheme\nPar\xc3\xa1metro : account_passtoimage\nPar\xc3\xa1metro : files_allowed_exts\nPar\xc3\xa1metro : files_allowed_size\nPar\xc3\xa1metro : files_enabled\nPar\xc3\xa1metro : ldap_base\nPar\xc3\xa1metro : ldap_enabled\nPar\xc3\xa1metro : ldap_group\nPar\xc3\xa1metro : ldap_server\nPar\xc3\xa1metro : log_enabled\nPar\xc3\xa1metro : mail_enabled\nPar\xc3\xa1metro : mail_from\nPar\xc3\xa1metro : mail_server\nPar\xc3\xa1metro : wiki_enabled\nPar\xc3\xa1metro : wiki_filter\nPar\xc3\xa1metro : wiki_searchurl\nVersi\xc3\xb3n : 20017021302
[Tue Feb 14 20:30:42.662706 2017] [:error] [pid 20861] [client 192.168.1.xxx:56710] Action: Actualizaci\xc3\xb3n -- Description: Actualizaci\xc3\xb3n de versi\xc3\xb3n realizada. -- Details: Versi\xc3\xb3n : 20017021302\nTipo : config
[Tue Feb 14 20:30:42.667974 2017] [:error] [pid 20861] [client 192.168.1.xxx:56710] initError
[Tue Feb 14 20:30:42.667988 2017] [:error] [pid 20861] [client 192.168.1.xxx:56710] The application needs to be updated
[Tue Feb 14 20:30:42.667994 2017] [:error] [pid 20861] [client 192.168.1.xxx:56710] If you are an administrator, click on the link: <a href="index.php?upgrade=1&a=upgrade">Update</a>
[Tue Feb 14 20:30:42.741223 2017] [:error] [pid 20864] [client 192.168.1.xxx:56722] initError
[Tue Feb 14 20:30:42.741258 2017] [:error] [pid 20864] [client 192.168.1.xxx:56722] Application in maintenance
[Tue Feb 14 20:30:42.741272 2017] [:error] [pid 20864] [client 192.168.1.xxx:56722] It will be running shortly
[Tue Feb 14 20:30:45.122280 2017] [:error] [pid 20862] [client 192.168.1.xxx:56798] initError
[Tue Feb 14 20:30:45.122312 2017] [:error] [pid 20862] [client 192.168.1.xxx:56798] Application in maintenance
[Tue Feb 14 20:30:45.122320 2017] [:error] [pid 20862] [client 192.168.1.xxx:56798] It will be running shortly
[Tue Feb 14 20:31:05.465348 2017] [:error] [pid 20864] [client 192.168.1.xxx:57446] Exception: SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'IDX_searchTxt'; check that column/key exists, referer: https://xxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:31:05.465412 2017] [:error] [pid 20864] [client 192.168.1.xxx:57446] #0 SP\\Storage\\DB->prepareQueryData() called at [/var/www/html/inc/SP/Storage/DB.class.php:150]\n#1 SP\\Storage\\DB->doQuery() called at [/var/www/html/inc/SP/Storage/DB.class.php:347]\n#2 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Core/Upgrade.class.php:109]\n#3 SP\\Core\\Upgrade::upgradeDB() called at [/var/www/html/inc/SP/Core/Upgrade.class.php:64]\n#4 SP\\Core\\Upgrade::doUpgrade() called at [/var/www/html/inc/SP/Core/Init.class.php:647]\n#5 SP\\Core\\Init::checkDbVersion() called at [/var/www/html/inc/SP/Core/Init.class.php:166]\n#6 SP\\Core\\Init::start() called at [/var/www/html/inc/Base.php:107]\n#7 require(/var/www/html/inc/Base.php) called at [/var/www/html/index.php:31]\n, referer: https://xxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:31:05.466475 2017] [:error] [pid 20864] [client 192.168.1.xxx:57446] Error en la consulta\nSQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'IDX_searchTxt'; check that column/key exists (42000), referer: https://xxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:31:05.466496 2017] [:error] [pid 20864] [client 192.168.1.xxx:57446] Caller 1: SP\\Storage\\DB\\logDBException, referer: https://xxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:31:05.466503 2017] [:error] [pid 20864] [client 192.168.1.xxx:57446] Caller 2: SP\\Storage\\DB\\getQuery, referer: https://xxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:31:05.466509 2017] [:error] [pid 20864] [client 192.168.1.xxx:57446] Caller 3: SP\\Core\\Upgrade\\upgradeDB, referer: https://xxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:31:05.466515 2017] [:error] [pid 20864] [client 192.168.1.xxx:57446] Caller 4: SP\\Core\\Upgrade\\doUpgrade, referer: https://xxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:31:05.466520 2017] [:error] [pid 20864] [client 192.168.1.xxx:57446] Caller 5: SP\\Core\\Init\\checkDbVersion, referer: https://xxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:31:05.466526 2017] [:error] [pid 20864] [client 192.168.1.xxx:57446] Caller 6: SP\\Core\\Init\\start, referer: https://xxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:31:05.466532 2017] [:error] [pid 20864] [client 192.168.1.xxx:57446] Caller 7: \\require, referer: https://xxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:31:05.466540 2017] [:error] [pid 20864] [client 192.168.1.xxx:57446] SQL : 'ALTER TABLE `accounts` DROP INDEX `IDX_searchTxt`', referer: https://xxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:31:05.472923 2017] [:error] [pid 20864] [client 192.168.1.xxx:57446] initError, referer: https://xxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:31:05.472953 2017] [:error] [pid 20864] [client 192.168.1.xxx:57446] Error al aplicar la actualizaci\xc3\xb3n de la Base de Datos, referer: https://xxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:31:05.472961 2017] [:error] [pid 20864] [client 192.168.1.xxx:57446] Compruebe el registro de eventos para m\xc3\xa1s detalles<p class="center"><a href="index.php?nodbupgrade=1">Sign in</a></p>, referer: https://xxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:31:05.536836 2017] [:error] [pid 20958] [client 192.168.1.xxx:57482] initError
[Tue Feb 14 20:31:05.536868 2017] [:error] [pid 20958] [client 192.168.1.xxx:57482] Application in maintenance
[Tue Feb 14 20:31:05.536875 2017] [:error] [pid 20958] [client 192.168.1.xxx:57482] It will be running shortly
Hi @piknik last error is the same from @48kRAM in a comment above
Hi @48kRAM your issue is related to a table index that wasn't created before, so you can quickly skip that by commenting out (by adding "-- " or removing) the line 10 in the "/inc/sql/1316011001.sql" file
nuxsmin
on 14 Feb 2017
@48kRAM you can run these SQL queries;
UPDATE accHistory
SET
acchistory_userId = 1
WHERE
acchistory_userId NOT IN (SELECT
user_id
FROM
usrData);
UPDATE accHistory
SET
acchistory_userEditId = 1
WHERE
acchistory_userEditId NOT IN (SELECT
user_id
FROM
usrData);
Sorry for the inconvenience, but some older versions let to drop users that were being used in history records.
nuxsmin
on 14 Feb 2017
@nuxsmin I get the same error as @48kRAM. I only have had one account registered (admin) and only recently added one other account, nothing has been deleted. This is the error:
[Tue Feb 14 20:45:07.381613 2017] [:error] [pid 20865] [client 192.168.1.xxx:56642] Action: Actualizar Configuraci\xc3\xb3n -- Description: -- Details: Par\xc3\xa1metro : account_count\nPar\xc3\xa1metro : account_link\nPar\xc3\xa1metro : checkupdates\nPar\xc3\xa1metro : checknotices\nPar\xc3\xa1metro : dbhost\nPar\xc3\xa1metro : dbname\nPar\xc3\xa1metro : dbpass\nPar\xc3\xa1metro : dbuser\nPar\xc3\xa1metro : debug\nPar\xc3\xa1metro : demo_enabled\nPar\xc3\xa1metro : globalsearch\nPar\xc3\xa1metro : installed\nPar\xc3\xa1metro : maintenance\nPar\xc3\xa1metro : passwordsalt\nPar\xc3\xa1metro : session_timeout\nPar\xc3\xa1metro : sitelang\nPar\xc3\xa1metro : version\nPar\xc3\xa1metro : config_hash\nPar\xc3\xa1metro : proxy_enabled\nPar\xc3\xa1metro : proxy_pass\nPar\xc3\xa1metro : proxy_port\nPar\xc3\xa1metro : proxy_server\nPar\xc3\xa1metro : proxy_user\nPar\xc3\xa1metro : resultsascards\nPar\xc3\xa1metro : sitetheme\nPar\xc3\xa1metro : account_passtoimage\nPar\xc3\xa1metro : files_allowed_exts\nPar\xc3\xa1metro : files_allowed_size\nPar\xc3\xa1metro : files_enabled\nPar\xc3\xa1metro : ldap_base\nPar\xc3\xa1metro : ldap_enabled\nPar\xc3\xa1metro : ldap_group\nPar\xc3\xa1metro : ldap_server\nPar\xc3\xa1metro : log_enabled\nPar\xc3\xa1metro : mail_enabled\nPar\xc3\xa1metro : mail_from\nPar\xc3\xa1metro : mail_server\nPar\xc3\xa1metro : wiki_enabled\nPar\xc3\xa1metro : wiki_filter\nPar\xc3\xa1metro : wiki_searchurl\nVersi\xc3\xb3n : 20017021302
[Tue Feb 14 20:45:07.381666 2017] [:error] [pid 20865] [client 192.168.1.xxx:56642] Action: Actualizaci\xc3\xb3n -- Description: Actualizaci\xc3\xb3n de versi\xc3\xb3n realizada. -- Details: Versi\xc3\xb3n : 20017021302\nTipo : config
[Tue Feb 14 20:45:07.386278 2017] [:error] [pid 20865] [client 192.168.1.xxx:56642] initError
[Tue Feb 14 20:45:07.386290 2017] [:error] [pid 20865] [client 192.168.1.xxx:56642] The application needs to be updated
[Tue Feb 14 20:45:07.386296 2017] [:error] [pid 20865] [client 192.168.1.xxx:56642] If you are an administrator, click on the link: <a href="index.php?upgrade=1&a=upgrade">Update</a>
[Tue Feb 14 20:45:07.455224 2017] [:error] [pid 20864] [client 192.168.1.xxx:56646] initError
[Tue Feb 14 20:45:07.455256 2017] [:error] [pid 20864] [client 192.168.1.xxx:56646] Application in maintenance
[Tue Feb 14 20:45:07.455264 2017] [:error] [pid 20864] [client 192.168.1.xxx:56646] It will be running shortly
[Tue Feb 14 20:45:08.714166 2017] [:error] [pid 20862] [client 192.168.1.xxx:56686] initError
[Tue Feb 14 20:45:08.714205 2017] [:error] [pid 20862] [client 192.168.1.xxx:56686] Application in maintenance
[Tue Feb 14 20:45:08.714213 2017] [:error] [pid 20862] [client 192.168.1.xxx:56686] It will be running shortly
[Tue Feb 14 20:45:32.076334 2017] [:error] [pid 20861] [client 192.168.1.xxx:57300] Exception: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`syspass`.`#sql-12d_da43`, CONSTRAINT `fk_accHistory_users_edit_id` FOREIGN KEY (`acchistory_userEditId`) REFERENCES `usrData` (`user_id`)), referer: https://xxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:45:32.076389 2017] [:error] [pid 20861] [client 192.168.1.xxx:57300] #0 SP\\Storage\\DB->prepareQueryData() called at [/var/www/html/inc/SP/Storage/DB.class.php:150]\n#1 SP\\Storage\\DB->doQuery() called at [/var/www/html/inc/SP/Storage/DB.class.php:347]\n#2 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Core/Upgrade.class.php:109]\n#3 SP\\Core\\Upgrade::upgradeDB() called at [/var/www/html/inc/SP/Core/Upgrade.class.php:64]\n#4 SP\\Core\\Upgrade::doUpgrade() called at [/var/www/html/inc/SP/Core/Init.class.php:647]\n#5 SP\\Core\\Init::checkDbVersion() called at [/var/www/html/inc/SP/Core/Init.class.php:166]\n#6 SP\\Core\\Init::start() called at [/var/www/html/inc/Base.php:107]\n#7 require(/var/www/html/inc/Base.php) called at [/var/www/html/index.php:31]\n, referer: https://xxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:45:32.083268 2017] [:error] [pid 20861] [client 192.168.1.xxx:57300] Error en la consulta\nSQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`syspass`.`#sql-12d_da43`, CONSTRAINT `fk_accHistory_users_edit_id` FOREIGN KEY (`acchistory_userEditId`) REFERENCES `usrData` (`user_id`)) (23000), referer: https://xxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:45:32.083301 2017] [:error] [pid 20861] [client 192.168.1.xxx:57300] Caller 1: SP\\Storage\\DB\\logDBException, referer: https://xxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:45:32.083309 2017] [:error] [pid 20861] [client 192.168.1.xxx:57300] Caller 2: SP\\Storage\\DB\\getQuery, referer: https://xxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:45:32.083348 2017] [:error] [pid 20861] [client 192.168.1.xxx:57300] Caller 3: SP\\Core\\Upgrade\\upgradeDB, referer: https://xxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:45:32.083355 2017] [:error] [pid 20861] [client 192.168.1.xxx:57300] Caller 4: SP\\Core\\Upgrade\\doUpgrade, referer: https://xxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:45:32.083361 2017] [:error] [pid 20861] [client 192.168.1.xxx:57300] Caller 5: SP\\Core\\Init\\checkDbVersion, referer: https://xxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:45:32.083367 2017] [:error] [pid 20861] [client 192.168.1.xxx:57300] Caller 6: SP\\Core\\Init\\start, referer: https://xxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:45:32.083373 2017] [:error] [pid 20861] [client 192.168.1.xxx:57300] Caller 7: \\require, referer: https://xxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:45:32.083385 2017] [:error] [pid 20861] [client 192.168.1.xxx:57300] SQL : 'ALTER TABLE `accHistory` ADD CONSTRAINT `fk_accHistory_users_edit_id`FOREIGN KEY (`acchistory_userEditId`)REFERENCES `usrData` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT', referer: https://xxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:45:32.084702 2017] [:error] [pid 20861] [client 192.168.1.xxx:57300] initError, referer: https://xxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:45:32.084717 2017] [:error] [pid 20861] [client 192.168.1.xxx:57300] Error al aplicar la actualizaci\xc3\xb3n de la Base de Datos, referer: https://xxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:45:32.084724 2017] [:error] [pid 20861] [client 192.168.1.xxx:57300] Compruebe el registro de eventos para m\xc3\xa1s detalles<p class="center"><a href="index.php?nodbupgrade=1">Sign in</a></p>, referer: https://xxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 20:45:32.143822 2017] [:error] [pid 20865] [client 192.168.1.xxx:57496] initError
[Tue Feb 14 20:45:32.143861 2017] [:error] [pid 20865] [client 192.168.1.xxx:57496] Application in maintenance
[Tue Feb 14 20:45:32.143871 2017] [:error] [pid 20865] [client 192.168.1.xxx:57496] It will be running shortly
I will try using your SQL script.
piknik
on 14 Feb 2017
@nuxsmin Just tried your SQL script, same error as above.
Edit: just wondering, you mean the accounts to store passwords for, or the accounts used to log into sysPass?
piknik
on 14 Feb 2017
@piknik please run the following query, but could you check out if user id 1 exists (within table usrData)?:
UPDATE accHistory
SET
acchistory_userEditId = 1
WHERE
acchistory_userEditId NOT IN (SELECT
user_id
FROM
usrData) OR acchistory_userEditId IS NULL;
@nuxsmin In my usrData table I have user_id 1 and 2 that exists. Running that SQL then updating does this error (I think it's the same?):
Tue Feb 14 21:40:15.969736 2017] [:error] [pid 20864] [client 192.168.1.xxx:50832] Exception: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`syspass`.`#sql-12d_db49`, CONSTRAINT `fk_accounts_user_edit_id` FOREIGN KEY (`account_userEditId`) REFERENCES `usrData` (`user_id`)), referer: https://xxxxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 21:40:15.969816 2017] [:error] [pid 20864] [client 192.168.1.xxx:50832] #0 SP\\Storage\\DB->prepareQueryData() called at [/var/www/html/inc/SP/Storage/DB.class.php:150]\n#1 SP\\Storage\\DB->doQuery() called at [/var/www/html/inc/SP/Storage/DB.class.php:347]\n#2 SP\\Storage\\DB::getQuery() called at [/var/www/html/inc/SP/Core/Upgrade.class.php:109]\n#3 SP\\Core\\Upgrade::upgradeDB() called at [/var/www/html/inc/SP/Core/Upgrade.class.php:64]\n#4 SP\\Core\\Upgrade::doUpgrade() called at [/var/www/html/inc/SP/Core/Init.class.php:647]\n#5 SP\\Core\\Init::checkDbVersion() called at [/var/www/html/inc/SP/Core/Init.class.php:166]\n#6 SP\\Core\\Init::start() called at [/var/www/html/inc/Base.php:107]\n#7 require(/var/www/html/inc/Base.php) called at [/var/www/html/index.php:31]\n, referer: https://xxxxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 21:40:15.978344 2017] [:error] [pid 20864] [client 192.168.1.xxx:50832] Error en la consulta\nSQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`syspass`.`#sql-12d_db49`, CONSTRAINT `fk_accounts_user_edit_id` FOREIGN KEY (`account_userEditId`) REFERENCES `usrData` (`user_id`)) (23000), referer: https://xxxxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 21:40:15.978391 2017] [:error] [pid 20864] [client 192.168.1.xxx:50832] Caller 1: SP\\Storage\\DB\\logDBException, referer: https://xxxxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 21:40:15.978398 2017] [:error] [pid 20864] [client 192.168.1.xxx:50832] Caller 2: SP\\Storage\\DB\\getQuery, referer: https://xxxxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 21:40:15.978404 2017] [:error] [pid 20864] [client 192.168.1.xxx:50832] Caller 3: SP\\Core\\Upgrade\\upgradeDB, referer: https://xxxxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 21:40:15.978410 2017] [:error] [pid 20864] [client 192.168.1.xxx:50832] Caller 4: SP\\Core\\Upgrade\\doUpgrade, referer: https://xxxxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 21:40:15.978415 2017] [:error] [pid 20864] [client 192.168.1.xxx:50832] Caller 5: SP\\Core\\Init\\checkDbVersion, referer: https://xxxxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 21:40:15.978421 2017] [:error] [pid 20864] [client 192.168.1.xxx:50832] Caller 6: SP\\Core\\Init\\start, referer: https://xxxxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 21:40:15.978426 2017] [:error] [pid 20864] [client 192.168.1.xxx:50832] Caller 7: \\require, referer: https://xxxxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
[Tue Feb 14 21:40:15.978435 2017] [:error] [pid 20864] [client 192.168.1.xxx:50832] SQL : 'ALTER TABLE `accounts` ADD CONSTRAINT `fk_accounts_user_edit_id`FOREIGN KEY (`account_userEditId`)REFERENCES `usrData` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT', referer: https://xxxxxxxxxxxx.net/index.php?upgrade=1&a=upgrade
Edit: Looks like it's a similar error but not the same. This time references the accounts table, and I see many ID = 0 on account_userEditId
piknik
on 14 Feb 2017
The same query but for the accounts table:
UPDATE accounts
SET
`account_userEditId` = 1
WHERE
`account_userEditId` NOT IN (SELECT
user_id
FROM
usrData) OR `account_userEditId` IS NULL;
So sorry about those inconveniences, but they happened because all the constraints logic was done by the app, now INNODB is used as default DB engine, so the constraint logic will be on the DB side instead.
nuxsmin
on 14 Feb 2017
@nuxsmin That's all fine, it's the life of a programmer ;)
Those two SQL scripts worked and it was successfully updated, thanks! The two scripts should somehow be included into 2.0 for the update process.
piknik
on 14 Feb 2017
Great!!..I will think how to include them, because the DB upgrade process does not rely with data, it only performs structure changes (DDL), so that kind of tasks would be done within the code...
nuxsmin
on 14 Feb 2017
Hi!
I just upgraded from v1.2.0.18 to v2.0 and after copy-pasting upgradeKey to update form I got the same page, tried again, refreshed the page and got:
initError
The application needs to be updated
If you are an administrator, click on the link: <a href="index.php?upgrade=1&a=upgrade">Update</a>
but clicking on this link does nothing and in logs I see:
initError, referer: https://example.com/index.php?upgrade=1&a=upgrade
The application needs to be updated, referer: https://example.com/index.php?upgrade=1&a=upgrade
If you are an administrator, click on the link: <a href="index.php?upgrade=1&a=upgrade">Update</a>, referer: https://example.com/index.php?upgrade=1&a=upgrade
with nodbupgrade=1 option I see next:
Exception: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'syspass.plugins' doesn't exist
Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'log_level' in 'field list'
#0 SP\\Storage\\DB->prepareQueryData() called at /srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:150]\n#1 SP\\Storage\\DB->doQuery() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:347]\n#2 SP\\Storage\\DB::getQuery() called at /srv/vhosts/example.com/www/inc/SP/Log/Log.class.php:165]\n#3 SP\\Log\\Log->writeLog() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:300]\n#4 SP\\Storage\\DB::logDBException() called at /srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:351]\n#5 SP\\Storage\\DB::getQuery() called at /srv/vhosts/example.com/www/inc/SP/Log/Log.class.php:165]\n#6 SP\\Log\\Log->writeLog() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:300]\n#7 SP\\Storage\\DB::logDBException() called at /srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:351]\n#8 SP\\Storage\\DB::getQuery() called at /srv/vhosts/example.com/www/inc/SP/Log/Log.class.php:165]\n#9 SP\\Log\\Log->writeLog() called at /srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:300]\n#10 SP\\Storage\\DB::logDBException() called at /srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:351]\n#11 SP\\Storage\\DB::getQuery() called at [/srv/vhosts/example.com/www/inc/SP/Log/Log.class.php:165]\n#12 SP\\Log\\Log->writeLog() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:300]\n#13 SP\\Storage\\DB::logDBException() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:351]\n#14 SP\\Storage\\DB::getQuery() called at [/srv/vhosts/example.com/www/inc/SP/Log/Log.class.php:165]\n#15 SP\\Log\\Log->writeLog() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:300]\n#16 SP\\Storage\\DB::logDBException() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:351]\n#17 SP\\Storage\\DB::getQuery() called at [/srv/vhosts/example.com/www/inc/SP/Log/Log.class.php:165]\n#18 SP\\Log\\Log->writeLog() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:300]\n#19 SP\\Storage\\DB::logDBException() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:351]\n#20 SP\\Storage\\DB::getQuery() called at [/srv/vhosts/example.com/www/inc/SP/Log/Log.class.php:165]\n#21 SP\\Log\\Log->writeLog() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:300]\n#22 SP\\Storage\\DB::logDBException() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:351]\n#23 SP\\Storage\\DB::getQuery() called at [/srv/vhosts/example.com/www/inc/SP/Log/Log.class.php:165]\n#24 SP\\Log\\Log->writeLog() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:300]\n#25 SP\\Storage\\DB::logDBException() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:351]\n#26 SP\\Storage\\DB::getQuery() called at [/srv/vhosts/example.com/www/inc/SP/Log/Log.class.php:165]\n#27 SP\\Log\\Log->writeLog() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:300]\n#28 SP\\Storage\\DB::logDBException() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:351]\n#29 SP\\Storage\\DB::getQuery() called at [/srv/vhosts/example.com/www/inc/SP/Log/Log.class.php:165]\n#30 SP\\Log\\Log->writeLog() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:300]\n#31 SP\\Storage\\DB::logDBException() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:351]\n#32 SP\\Storage\\DB::getQuery() called at [/srv/vhosts/example.com/www/inc/SP/Log/Log.class.php:165]\n#33 SP\\Log\\Log->writeLog() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:300]\n#34 SP\\Storage\\DB::logDBException() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:351]\n#35 SP\\Storage\\DB::getQuery() called at [/srv/vhosts/example.com/www/inc/SP/Log/Log.class.php:165]\n#36 SP\\Log\\Log->writeLog() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:300]\n#37 SP\\Storage\\DB::logDBException() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:351]\n#38 SP\\Storage\\DB::getQuery() called at [/srv/vhosts/example.com/www/inc/SP/Log/Log.class.php:165]\n#39 SP\\Log\\Log->writeLog() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:300]\n#40 SP\\Storage\\DB::logDBException() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:351]\n#41 SP\\Storage\\DB::getQuery() called at [/srv/vhosts/example.com/www/inc/SP/Log/Log.class.php:165]\n#42 SP\\Log\\Log->writeLog() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:300]\n#43 SP\\Storage\\DB::logDBException() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:351]\n#44 SP\\Storage\\DB::getQuery() called at [/srv/vhosts/example.com/www/inc/SP/Log/Log.class.php:165]\n#45 SP\\Log\\Log->writeLog() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:300]\n#46 SP\\Storage\\DB::logDBException() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:351]\n#47 SP\\Storage\\DB::getQuery() called at [/srv/vhosts/example.com/www/inc/SP/Log/Log.class.php:165]\n#48 SP\\Log\\Log->writeLog() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:300]\n#49 SP\\Storage\\DB::logDBException() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:351]\n#50 SP\\Storage\\DB::getQuery() called at [/srv/vhosts/example.com/www/inc/SP/Log/Log.class.php:165]\n#51 SP\\Log\\Log->writeLog() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:300]\n#52 SP\\Storage\\DB::logDBException() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:351]\n#53 SP\\Storage\\DB::getQuery() called at [/srv/vhosts/example.com/www/inc/SP/Log/Log.class.php:165]\n#54 SP\\Log\\Log->writeLog() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:300]\n#55 SP\\Storage\\DB::logDBException() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:351]\n#56 SP\\Storage\\DB::getQuery() called at [/srv/vhosts/example.com/www/inc/SP/Log/Log.class.php:165]\n#57 SP\\Log\\Log->writeLog() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:300]\n#58 SP\\Storage\\DB::logDBException() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:351]\n#59 SP\\Storage\\DB::getQuery() called at [/srv/vhosts/example.com/www/inc/SP/Log/Log.class.php:165]\n#60 SP\\Log\\Log->writeLog() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:300]\n#61 SP\\Storage\\DB::logDBException() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:351]\n#62 SP\\Storage\\DB::getQuery() called at [/srv/vhosts/example.com/www/inc/SP/Log/Log.class.php:165]\n#63 SP\\Log\\Log->writeLog() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:300]\n#64 SP\\Storage\\DB::logDBException() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:351]\n#65 SP\\Storage\\DB::getQuery() called at [/srv/vhosts/example.com/www/inc/SP/Log/Log.class.php:165]\n#66 SP\\Log\\Log->writeLog() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:300]\n#67 SP\\Storage\\DB::logDBException() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:351]\n#68 SP\\Storage\\DB::getQuery() called at [/srv/vhosts/example.com/www/i
nc/SP/Log/Log.class.php:165]\n#69 SP\\Log\\Log->writeLog() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:300]\n#70 SP\\Storage\\DB::logDBException() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:351]\n#71 SP\\Storage\\DB::getQuery() called at [/srv/vhosts/example.com/www/inc/SP/Log/Log.class.php:165]\n#72 SP\\Log\\Log->writeLog() called at [/
PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes) in /srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php on line 252
line with #0 SP\\Storage\\DB->prepareQueryData() called at repeated many times (1000 or so)
TheMengzor
on 15 Feb 2017
Hi @TheMengzor you would need to point your browser to "https://example.com/index.php?upgrade=1&a=upgrade" to perform the upgrade. The "nodbupgrade" option is for those upgrades that didn't go fine and any error is thrown.
What do you get (in the error log) after the first time you press the upgrade button?
nuxsmin
on 15 Feb 2017
I have the very same problem as @TheMengzor. My old version was last stable of 1.2, my new version was fetched from master.
Using https://example.com/index.php?upgrade=1&a=upgrade just shows me "If you are an administrator, click on the link" again.
I rolled back to 1.2 using my backups. So I dont have any logs. Sorry.
derStephan
on 15 Feb 2017
@derStephan could you provide any logs?. I know that you rolled back, but it would be nice to get some feedback about the error to fix it.
nuxsmin
on 15 Feb 2017
@nuxsmin Sorry, everything is gone. But I can try to do the upgrade again today evening.
derStephan
on 15 Feb 2017
Nuxmin, I understand the issues are a result of moving constraints etc from code to database ?
Might be an idea to add some code to the upgrade that checks constraints in code before executing the DDL changes ? If issues arrise fix them in code or if impossible log them to the client and don't run the db update before they're fixed.
By the way, I had issues as well, the db upgrade wasn't executed... I found the sql files and applied them manually, strangely they executed without any error. Still my installation mentioned it wasn't updated. Regretfully I didn't collect the logs and decided to roll back to 1.2, export all accounts and do a fresh install + import.
wernerreuser
on 15 Feb 2017
Ol脿 !
Same problem here, upgrading from 1.2 to 2.0:
- IDX_searchTxt (skipped by deleting line 10 in
1316011001.sql) - Integrity constraint violation on
1316100601.sql(no resolving query tested at this point, rolling back to 1.2 and waiting)
The constraint error seems to be triggered by this query:
ALTER TABLE `accHistory`
ADD CONSTRAINT `fk_accHistory_user_id`
FOREIGN KEY (`acchistory_userId`)
REFERENCES `usrData` (`user_id`)
ON DELETE RESTRICT
ON UPDATE RESTRICT;
Thanks for all your great work, BTW !
Yotouille
on 15 Feb 2017
UPDATE
Hello, I have done some tweaks on the master branch that would solve the upgrading issues. Could you try it out?
nuxsmin
on 15 Feb 2017
@nuxsmin after clicking on that link I got:
initError, referer: https://example.com/index.php?upgrade=1&a=upgrade
The application needs to be updated, referer: https://example.com/index.php?upgrade=1&a=upgrade
If you are an administrator, click on the link: <a href="index.php?upgrade=1&a=upgrade">Update</a>, referer: https://example.com/index.php?upgrade=1&a=upgrade
I'll also rollback 1.2 on my server and try to upgrade from master branch right now.
TheMengzor
on 15 Feb 2017
@TheMengzor but did you perform the upgrade?. This process is needed to bump to 2.0 version
nuxsmin
on 15 Feb 2017
@nuxsmin IDK, since I just clicked on <a href="index.php?upgrade=1&a=upgrade">Update</a> link few times (to be sure!) and got the same page, in logs I saw the same as in my previous message.
TheMengzor
on 15 Feb 2017
Could you reload the page? Sometimes the JavaScript cache isn't refreshed when page loads.
nuxsmin
on 15 Feb 2017
I just started an upgrade from 1.2 to the actual master, I pressed "update" link and now I see a form that asks me for security code (that's okay) and also tells me:
Some orphan items have been found. Please, modify those items or enter the default IDs for them.
accounts_useredit : 43
accountshistory_useredit : 36
Where can I get proper ID for them?
TheMengzor
on 15 Feb 2017
You could pick a valid ID from the usrData table in the DB. If you never deleted the admin user, its ID is set to 1.
nuxsmin
on 15 Feb 2017
Aha, I put "1" in ID input, copy-pasted upgrade key and started upgrade, I got in browser:
Error while updating database
Compruebe el registro de eventos para m谩s detalles
in logs:
Exception: SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'IDX_searchTxt'; check that column/key exists, referer: https://example.com/index.php?upgrade=1&a=upgrade
[Wed Feb 15 22:00:29.664144 2017] [:error] [pid 11869] [client 109.86.218.135:52570] #0 SP\\Storage\\DB->prepareQueryData() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:150]\n#1 SP\\Storage\\DB->doQuery() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:347]\n#2 SP\\Storage\\DB::getQuery() called at [/srv/vhosts/example.com/www/inc/SP/Core/Upgrade.class.php:154]\n#3 SP\\Core\\Upgrade::upgradeDB() called at [/srv/vhosts/example.com/www/inc/SP/Core/Upgrade.class.php:81]\n#4 SP\\Core\\Upgrade::doUpgrade() called at [/srv/vhosts/example.com/www/inc/SP/Core/Init.class.php:650]\n#5 SP\\Core\\Init::checkDbVersion() called at [/srv/vhosts/example.com/www/inc/SP/Core/Init.class.php:169]\n#6 SP\\Core\\Init::start() called at [/srv/vhosts/example.com/www/inc/Base.php:107]\n#7 require(/srv/vhosts/example.com/www/inc/Base.php) called at [/srv/vhosts/example.com/www/index.php:31]\n, referer: https://example.com/index.php?upgrade=1&a=upgrade
[Wed Feb 15 22:00:29.665191 2017] [:error] [pid 11869] [client 109.86.218.135:52570] Error en la consulta\nSQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'IDX_searchTxt'; check that column/key exists (42000), referer: https://example.com/index.php?upgrade=1&a=upgrade
[Wed Feb 15 22:00:29.665212 2017] [:error] [pid 11869] [client 109.86.218.135:52570] Caller 1: SP\\Storage\\DB\\logDBException, referer: https://example.com/index.php?upgrade=1&a=upgrade
[Wed Feb 15 22:00:29.665218 2017] [:error] [pid 11869] [client 109.86.218.135:52570] Caller 2: SP\\Storage\\DB\\getQuery, referer: https://example.com/index.php?upgrade=1&a=upgrade
[Wed Feb 15 22:00:29.665221 2017] [:error] [pid 11869] [client 109.86.218.135:52570] Caller 3: SP\\Core\\Upgrade\\upgradeDB, referer: https://example.com/index.php?upgrade=1&a=upgrade
[Wed Feb 15 22:00:29.665225 2017] [:error] [pid 11869] [client 109.86.218.135:52570] Caller 4: SP\\Core\\Upgrade\\doUpgrade, referer: https://example.com/index.php?upgrade=1&a=upgrade
[Wed Feb 15 22:00:29.665228 2017] [:error] [pid 11869] [client 109.86.218.135:52570] Caller 5: SP\\Core\\Init\\checkDbVersion, referer: https://example.com/index.php?upgrade=1&a=upgrade
[Wed Feb 15 22:00:29.665232 2017] [:error] [pid 11869] [client 109.86.218.135:52570] Caller 6: SP\\Core\\Init\\start, referer: https://example.com/index.php?upgrade=1&a=upgrade
[Wed Feb 15 22:00:29.665235 2017] [:error] [pid 11869] [client 109.86.218.135:52570] Caller 7: \\require, referer: https://example.com/index.php?upgrade=1&a=upgrade
[Wed Feb 15 22:00:29.665243 2017] [:error] [pid 11869] [client 109.86.218.135:52570] SQL : 'ALTER TABLE `accounts` DROP INDEX `IDX_searchTxt`', referer: https://example.com/index.php?upgrade=1&a=upgrade
[Wed Feb 15 22:00:31.305435 2017] [:error] [pid 11869] [client 109.86.218.135:52570] initError, referer: https://example.com/index.php?upgrade=1&a=upgrade
[Wed Feb 15 22:00:31.305464 2017] [:error] [pid 11869] [client 109.86.218.135:52570] Error al aplicar la actualizaci\xc3\xb3n de la Base de Datos, referer: https://example.com/index.php?upgrade=1&a=upgrade
[Wed Feb 15 22:00:31.305470 2017] [:error] [pid 11869] [client 109.86.218.135:52570] Compruebe el registro de eventos para m\xc3\xa1s detalles<p class="center"><a href="index.php?nodbupgrade=1">Sign in</a></p>, referer: https://example.com/index.php?upgrade=1&a=upgrade
Please take a look to some messages above. That is because some previous update wasn't applied and a table index is missing. Don't restart the upgrade process, just reload the page.
nuxsmin
on 15 Feb 2017
Also, I clicked on "sign in", it's redirected me to the auth page with nodbupgrade=1 parameter. Logs was clear, but when I'm trying tog in, I see:
Exception: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'syspass.plugins' doesn't exist, referer: https://example.com/index.php?nodbupgrade=1
#0 SP\\Storage\\DB->prepareQueryData() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:150]\n#1 SP\\Storage\\DB->doQuery() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:104]\n#2 SP\\Storage\\DB::getResults() called at [/srv/vhosts/example.com/www/inc/SP/Mgmt/Plugins/Plugin.class.php:203]\n#3 SP\\Mgmt\\Plugins\\Plugin->getByName() called at [/srv/vhosts/example.com/www/inc/SP/Core/Plugin/PluginDataStore.class.php:65]\n#4 SP\\Core\\Plugin\\PluginDataStore::load() called at [/srv/vhosts/example.com/www/inc/SP/Core/Plugin/PluginUtil.class.php:98]\n#5 SP\\Core\\Plugin\\PluginUtil::loadPlugin() called at [/srv/vhosts/example.com/www/inc/SP/Core/Init.class.php:742]\n#6 SP\\Core\\Init::loadPlugins() called at [/srv/vhosts/example.com/www/inc/SP/Core/Init.class.php:175]\n#7 SP\\Core\\Init::start() called at [/srv/vhosts/example.com/www/inc/Base.php:107]\n#8 require_once(/srv/vhosts/example.com/www/inc/Base.php) called at [/srv/vhosts/example.com/www/ajax/ajax_doLogin.php:30]\n, referer: https://example.com/index.php?nodbupgrade=1
Error en la consulta\nSQLSTATE[42S02]: Base table or view not found: 1146 Table 'syspass.plugins' doesn't exist (42), referer: https://example.com/index.php?nodbupgrade=1
Caller 1: SP\\Storage\\DB\\logDBException, referer: https://example.com/index.php?nodbupgrade=1
Caller 2: SP\\Storage\\DB\\getResults, referer: https://example.com/index.php?nodbupgrade=1
Caller 3: SP\\Mgmt\\Plugins\\Plugin\\getByName, referer: https://example.com/index.php?nodbupgrade=1
Caller 4: SP\\Core\\Plugin\\PluginDataStore\\load, referer: https://example.com/index.php?nodbupgrade=1
Caller 5: SP\\Core\\Plugin\\PluginUtil\\loadPlugin, referer: https://example.com/index.php?nodbupgrade=1
Caller 6: SP\\Core\\Init\\loadPlugins, referer: https://example.com/index.php?nodbupgrade=1 Caller 7: SP\\Core\\Init\\start, referer: https://example.com/index.php?nodbupgrade=1 Caller 8: \\require_once, referer: https://example.com/index.php?nodbupgrade=1 SQL : 'SELECT plugin_id, plugin_name, plugin_data, BIN(plugin_enabled) AS plugin_enabled FROM plugins WHERE plugin_name = ? LIMIT 1', referer: https://example.com/index.php?nodbupgrade=1
Exception: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'syspass.plugins' doesn't exist, referer: https://example.com/index.php?nodbupgrade=1
#0 SP\\Storage\\DB->prepareQueryData() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:150]\n#1 SP\\Storage\\DB->doQuery() called at [/srv/vhosts/example.com/www/inc/SP/Storage/DB.class.php:347]\n#2 SP\\Storage\\DB::getQuery() called at [/srv/vhosts/example.com/www/inc/SP/Mgmt/Plugins/Plugin.class.php:61]\n#3 SP\\Mgmt\\Plugins\\Plugin->add() called at [/srv/vhosts/example.com/www/inc/SP/Core/Plugin/PluginDataStore.class.php:72]\n#4 SP\\Core\\Plugin\\PluginDataStore::load() called at [/srv/vhosts/example.com/www/inc/SP/Core/Plugin/PluginUtil.class.php:98]\n#5 SP\\Core\\Plugin\\PluginUtil::loadPlugin() called at [/srv/vhosts/example.com/www/inc/SP/Core/Init.class.php:742]\n#6 SP\\Core\\Init::loadPlugins() called at [/srv/vhosts/example.com/www/inc/SP/Core/Init.class.php:175]\n#7 SP\\Core\\Init::start() called at [/srv/vhosts/example.com/www/inc/Base.php:107]\n#8 require_once(/srv/vhosts/example.com/www/inc/Base.php) called at [/srv/vhosts/example.com/www/ajax/ajax_doLogin.php:30]\n, referer: https://example.com/index.php?nodbupgrade=1
Error en la consulta\nSQLSTATE[42S02]: Base table or view not found: 1146 Table 'syspass.plugins' doesn't exist (42), referer: https://example.com/index.php?nodbupgrade=1
Caller 1: SP\\Storage\\DB\\logDBException, referer: https://example.com/index.php?nodbupgrade=1
Caller 2: SP\\Storage\\DB\\getQuery, referer: https://example.com/index.php?nodbupgrade=1
Caller 3: SP\\Mgmt\\Plugins\\Plugin\\add, referer: https://example.com/index.php?nodbupgrade=1
Caller 4: SP\\Core\\Plugin\\PluginDataStore\\load, referer: https://example.com/index.php?nodbupgrade=1
Caller 5: SP\\Core\\Plugin\\PluginUtil\\loadPlugin, referer: https://example.com/index.php?nodbupgrade=1
Caller 6: SP\\Core\\Init\\loadPlugins, referer: https://example.com/index.php?nodbupgrade=1 Caller 7: SP\\Core\\Init\\start, referer: https://example.com/index.php?nodbupgrade=1 Caller 8: \\require_once, referer: https://example.com/index.php?nodbupgrade=1 SQL : 'INSERT INTO plugins SET plugin_name = ?, plugin_data = ?, plugin_enabled = ?', referer: https://example.com/index.php?nodbupgrade=1
Decryption error, referer: https://example.com/index.php?nodbupgrade=1
PHP Fatal error: Uncaught Error: Call to undefined function SP\\Core\\mcrypt_module_open() in /srv/vhosts/example.com/www/inc/SP/Core/Crypt.class.php:153\nStack trace:\n#0 /srv/vhosts/example.com/www/inc/SP/Core/Crypt.class.php(129): SP\\Core\\Crypt::getMcryptResource()\n#1 /srv/vhosts/example.com/www/inc/SP/Core/Crypt.class.php(111): SP\\Core\\Crypt::getIV()\n#2 /srv/vhosts/example.com/www/inc/SP/Mgmt/Users/UserPass.class.php(168): SP\\Core\\Crypt::makeHashSalt()\n#3 /srv/vhosts/example.com/www/inc/SP/Mgmt/Users/UserLdap.class.php(180): SP\\Mgmt\\Users\\UserPass::makeUserPassHash('Again5:)Anoda')\n#4 /srv/vhosts/example.com/www/inc/SP/Controller/LoginController.class.php(376): SP\\Mgmt\\Users\\UserLdap->update()\n#5 /srv/vhosts/example.com/www/inc/SP/Controller/LoginController.class.php(127): SP\\Controller\\LoginController->authLdap(Object(SP\\Auth\\Ldap\\LdapAuthData))\n#6 /srv/vhosts/example.com/www/ajax/ajax_doLogin.php(39): SP\\Controller\\LoginController->doLogin()\n#7 {main}\n thrown in /srv/vhosts/example.com/www/inc/SP/Core/Crypt.class.php on line 153, referer: https://example.com/index.php?nodbupgrade=1
Did you comment out the line in the SQL file?
The upgrade would need to be restarted because some tables are missing. The nodbupgrade option could be used in some special times, but it won't function always.
nuxsmin
on 15 Feb 2017
Ok, I just commented line -- ALTER TABLE `accounts` DROP INDEX `IDX_searchTxt`; and finally got Application successfully updated!
Thanks a lot for your work!
Will continue testing and providing a feedback.
TheMengzor
on 15 Feb 2017
That sounds great!!. Sorry for the inconvenience, but this version is major update that improves sysPass management.
Thanks for the feedback!
nuxsmin
on 15 Feb 2017
I tried it with current master but it still does not work.
Here is what I did:
- backup
- create new folder
- copy everything from current master to that folder
- set permissions of /config to 750
- copy config.php and those key-files from old version to new version
- access it via browser
- it tells me, that an upgrade needs to be done, i click on "start"
- it asks me for the upgrade key and tells me about 2 orphans in acchistory_customerId and acchistory_categoryId (I really do not know what to enter there, so I inserted 1, may be wrong or right, I will never know)
- I do the upgrade, There is an error saying that the upgrade has failed
- when I reload the page, it tells me that it is in maintenance-mode
- setting maintenance to 0 in config.xml
- now I am in a loop of "upgrade needed, click here". Whenever I click it, I keep seeing that page again.
Here is my log in CSV-format, directly out of the data base: log.txt
Rolling back now to get it working again.
derStephan
on 16 Feb 2017
Hello @derStephan
taking a look to the log file, it seems that the upgrade process didn't finished well, so though you accessed through the link it won't work because some tables aren't found. The customerId and categoryId should be the items ids that already exists. You can get it by looking in the customers and categories tables in the sysPass database.
This line below tells that you performed an upgrade twice:
SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'log_level' (42)
Kind regards
nuxsmin
on 16 Feb 2017
taking a look to the log file, it seems that the upgrade process didn't finished well,
I assumed that as well. But what can I do to get things right?
The customerId and categoryId should be the items ids that already exists. You can get it by looking in the customers and categories tables in the sysPass database.
May I suggest that you create a new entry like "orphan" in these cases and use the ID of that entry automatically? Looking at the database during upgrade is not very convenient and even worse error prone.
This line below tells that you performed an upgrade twice:
I did exactly what I explained, I repeated Step 12 a couple of times. But the upgrade already failed in the first run.
derStephan
on 16 Feb 2017
This is a great idea. I didn't realize about that yesterday...
May I suggest that you create a new entry like "orphan" in these cases and use the ID of that entry automatically? Looking at the database during upgrade is not very convenient and even worse error prone.
nuxsmin
on 16 Feb 2017
@derStephan
Agrrrr, some items couldn't be added first, because some tables structure need to be altered to make use of the new features, so it will fail when adding...
nuxsmin
on 16 Feb 2017
Agrrrr, some items couldn't be added first, because some tables structure need to be altered to make use of the new features, so it will fail when adding...
I don't get it. At the moment, there is a time when I have to enter the missing IDs. So this seems to be the right moment for the insertion of this dummy entries.
derStephan
on 16 Feb 2017
Right, but at that moment DB structure hasn't been altered yet and if it's altered before setting the IDs it will fail...
nuxsmin
on 16 Feb 2017
Why don't you insert those items in the old structure as it was done in previous versions? After that the alteration should work.
derStephan
on 16 Feb 2017
Because when you are upgrading, the current code (2.0 version) would need some data structures that haven't been already modified so the related functions for inserting the data will fail indeed.
Those changes would need to be done from 1.2 version, and then a new release would be needed before upgrading, so it would be annoying for some people...
Lastly, I can code some functions to make use of the older structures, but it will add some garbage code that won't be needed anymore after upgrading.
It's really difficult to take the right step.
nuxsmin
on 16 Feb 2017
I see your point. This is really a tough decision.
No offence, pal. I really like syspass and I really appreciate your work and your quick responses and your obvious dedication to the project. But right now the upgrade is broken, at least for me and for all other users which filed bug reports in the last 48h. :-(
Edit: removed and created a new comment right below. Sorry for the noise.
derStephan
on 16 Feb 2017
Maybe we should think differently, if an upgrade with a filled data base is not trivial. Maybe it is best to handle table structures and actual data in consecutive steps. What about the following automated upgrade process:
- backup all data from the data base automatically as CSV-files
- remove all tables from database
- create new (empty) tables with current structure in database
- open CSV-files one by one in PHP and insert missing columns or missing items
- reimport corrected CSV-data to data base
- delete CSV-files
So you won't get the dead lock that you get when you rely only on SQL.
Or, you could do this in SQL without using PHP.
Do this for each changed table:
- rename old table
- create new (empty) table
- Insert old data into new table and add default data to missing columns
- add "orphan" items
- delete old table
This SQL-process should be done in the correct table order to prevent foreign-key failures. What do you think?
derStephan
on 16 Feb 2017
Your suggestions are nice, but I want to solve this seamless, because I don't want to hassle users with heavy procedures.
Thanks for your suggestions!
nuxsmin
on 16 Feb 2017
because I don't want to hassle users with heavy procedures.
My point exactly. That's why I suggested to replace the current automated upgrade process with the procedures I explained in my last comment.
Of course, these steps should be done automatically and without any user interaction.
Sorry if that was not clear.
derStephan
on 16 Feb 2017
I second @derStephan on this. As a programmer and IT, experience consistently demonstrates that it's much more simple to copy data and re-import when there are some structural changes. Different databases and versions always cause problems and are much made difficult to maintain.
piknik
on 16 Feb 2017
Nice, but I notice some issues:
- A CSV file won't store binary data (ie passwords) an blob fields.
- Renaming the DB would be disallowed on some hosting environments.
Your suggestions are really worth and I working hard to get a nice solution.
nuxsmin
on 16 Feb 2017
A CSV file won't store binary data (ie passwords) an blob fields.
Are these real binary data? From what I saw, these are only Strings in UTF-8.
Renaming the DB would be disallowed on some hosting environments.
My suggestion was not to rename the database but single tables. And this should be allowed whenever you have alter rights.
Your suggestions are really worth and I working hard to get a nice solution.
I believe in you and thank you for it.
derStephan
on 16 Feb 2017
@nuxsmin base64 works to encode/decode binary. Doesn't have to be CSV either. For example, in Java there's serialization which is almost too easy to convert data, there must be something like that in PHP (I don't code PHP)
piknik
on 16 Feb 2017
Sure, password encrypted data is stored in binary mode (VARBNARY).
Are these real binary data? From what I saw, these are only Strings in UTF-8.
I will take a look to this approach...
My suggestion was not to rename the database but single tables. And this should be allowed whenever you have alter rights.
nuxsmin
on 16 Feb 2017
@piknik Some data is serialized (ie. user preferences and custom fields definition). I know that binary data could be converted to hexadecimal (performance impact would be minimal).
I think that the current procedure could be successful with some tweaks, but I'm analyzing other ways.
Thanks!
nuxsmin
on 16 Feb 2017
Hi all,
Luckily I have found the upgrading issue...The process was done right but there is a missed behaviour, a DB transaction is started at the beginning of the process, so the updating queries weren't performed until the whole process has been finished, so the DDL operations were performed when the IDs weren't changed, so they failed...I was so blind...
A new release will be pushed shortly.
Kind regards
nuxsmin
on 18 Feb 2017
Hello,
I've released a new update that should fix the upgrading issues. The orphaned items ID are still needed because the app should set those IDs for proper constraints integrity.
You can download it from the master branch.
Hope it works ;)
nuxsmin
on 20 Feb 2017
Hey Rub茅n,
what about the missing items during upgrade? Any chance of automating that process as discussed before?
https://github.com/nuxsmin/sysPass/issues/430#issuecomment-280258804
The customerId and categoryId should be the items ids that already exists. You can get it by looking in the customers and categories tables in the sysPass database.
May I suggest that you create a new entry like "orphan" in these cases and use the ID of that entry automatically? Looking at the database during upgrade is not very convenient and even worse error prone.
derStephan
on 22 Feb 2017
Hi @derStephan
currently I'm working hard on the new security improvement, so I haven't got time to work on it...
Hopefully in the next days I'll announce the new release that would solve those upgrading issues and implements the new encryption schema.
FYI: the new "upgrader" uses Ajax so it won't rely on page reloads...(that involved many checks/tasks during the page initialization -see SP\Core\Init class-)
nuxsmin
on 22 Feb 2017
This thread will be discussed on #466
nuxsmin
on 1 Mar 2017
I managed to update from 1.2.0.18 (or something similar with 18 at the end) using these scripts.
But the following code is a MUST:
UPDATE accHistory
SET
acchistory_userEditId = 1
WHERE
acchistory_userEditId NOT IN (SELECT
user_id
FROM
usrData) OR acchistory_userEditId IS NULL;
UPDATE accHistory
SET
acchistory_userEditId = 1
WHERE
acchistory_userEditId NOT IN (SELECT
user_id
FROM
usrData) OR acchistory_userEditId IS NULL;
UPDATE accounts
SET
account_userEditId = 1
WHERE
account_userEditId NOT IN (SELECT
user_id
FROM
usrData) OR account_userEditId IS NULL;
so you might want to create another sql step to fix the upgrade process.
ghost
on 23 Feb 2018
Related issues
vmario89
路
5Comments
ju29ro14
路
5Comments
NOTORIOUSVR
路
3Comments
MagicFab
路
5Comments
DCWNZ
路
4Comments
Most helpful comment
UPDATE
Hello, I have done some tweaks on the master branch that would solve the upgrading issues. Could you try it out?