Problems migrating to new version

zwighi · September 30, 2022, 7:22am

Hello,

I have a problem in my new server Passbolt.
I created a new vm with new version of Passbolt CE and imported old db that i have on a passbolt version 2.7.
I had to delete the table email_queue in the new db cause it was very large and during the passbolt migrate i received an error for table too large.

Now I’m having problem when i launch command

sudo -H -u nginx /bin/bash -c “/usr/share/php/passbolt/bin/cake migrations migrate --no-lock”

It gives an error and i don’t know how to solve.

using migration paths

/etc/passbolt/Migrations
using seed paths
/etc/passbolt/Seeds
using environment default
using adapter mysql
using database passbolt
ordering by creation time

== 20211215180000 V350RemovePermissionsTypeIndex: migrating
InvalidArgumentException: The specified index on columns ‘type’ does not exist in /usr/share/php/passbolt/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/MysqlAdapter.php:717
Stack trace:
#0 /usr/share/php/passbolt/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php(919): Phinx\Db\Adapter\MysqlAdapter->getDropIndexByColumnsInstructions()
#1 /usr/share/php/passbolt/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/AdapterWrapper.php(484): Phinx\Db\Adapter\PdoAdapter->executeActions()
#2 /usr/share/php/passbolt/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/TimedOutputAdapter.php(420): Phinx\Db\Adapter\AdapterWrapper->executeActions()
#3 /usr/share/php/passbolt/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/AdapterWrapper.php(484): Phinx\Db\Adapter\TimedOutputAdapter->executeActions()
#4 /usr/share/php/passbolt/vendor/robmorgan/phinx/src/Phinx/Db/Plan/Plan.php(151): Phinx\Db\Adapter\AdapterWrapper->executeActions()
#5 /usr/share/php/passbolt/vendor/robmorgan/phinx/src/Phinx/Db/Table.php(715): Phinx\Db\Plan\Plan->execute()
#6 /usr/share/php/passbolt/vendor/robmorgan/phinx/src/Phinx/Db/Table.php(623): Phinx\Db\Table->executeActions()
#7 /usr/share/php/passbolt/vendor/cakephp/migrations/src/Table.php(152): Phinx\Db\Table->update()
#8 /usr/share/php/passbolt/vendor/robmorgan/phinx/src/Phinx/Db/Table.php(682): Migrations\Table->update()
#9 /etc/passbolt/Migrations/20211215180000_V350RemovePermissionsTypeIndex.php(21): Phinx\Db\Table->save()
#10 /usr/share/php/passbolt/vendor/robmorgan/phinx/src/Phinx/Migration/Manager/Environment.php(111): V350RemovePermissionsTypeIndex->up()
#11 /usr/share/php/passbolt/vendor/robmorgan/phinx/src/Phinx/Migration/Manager.php(385): Phinx\Migration\Manager\Environment->executeMigration()
#12 /usr/share/php/passbolt/vendor/robmorgan/phinx/src/Phinx/Migration/Manager.php(359): Phinx\Migration\Manager->executeMigration()
#13 /usr/share/php/passbolt/vendor/robmorgan/phinx/src/Phinx/Console/Command/Migrate.php(122): Phinx\Migration\Manager->migrate()
#14 /usr/share/php/passbolt/vendor/cakephp/migrations/src/Command/Phinx/CommandTrait.php(37): Phinx\Console\Command\Migrate->execute()
#15 /usr/share/php/passbolt/vendor/cakephp/migrations/src/Command/Phinx/Migrate.php(85): Migrations\Command\Phinx\Migrate->parentExecute()
#16 /usr/share/php/passbolt/vendor/symfony/console/Command/Command.php(298): Migrations\Command\Phinx\Migrate->execute()
#17 /usr/share/php/passbolt/vendor/symfony/console/Application.php(1028): Symfony\Component\Console\Command\Command->run()
#18 /usr/share/php/passbolt/vendor/symfony/console/Application.php(299): Symfony\Component\Console\Application->doRunCommand()
#19 /usr/share/php/passbolt/vendor/symfony/console/Application.php(171): Symfony\Component\Console\Application->doRun()
#20 /usr/share/php/passbolt/vendor/cakephp/migrations/src/Command/MigrationsCommand.php(126): Symfony\Component\Console\Application->run()
#21 /usr/share/php/passbolt/vendor/cakephp/cakephp/src/Console/BaseCommand.php(179): Migrations\Command\MigrationsCommand->execute()
#22 /usr/share/php/passbolt/vendor/cakephp/migrations/src/Command/MigrationsCommand.php(198): Cake\Console\BaseCommand->run()
#23 /usr/share/php/passbolt/vendor/cakephp/cakephp/src/Console/CommandRunner.php(334): Migrations\Command\MigrationsCommand->run()
#24 /usr/share/php/passbolt/vendor/cakephp/cakephp/src/Console/CommandRunner.php(172): Cake\Console\CommandRunner->runCommand()
#25 /usr/share/php/passbolt/bin/cake.php(13): Cake\Console\CommandRunner->run()
#26 {main}

I saw that there is another topic like mine so i tried to launch some commands to know if the y should help me to solve. Here are the results:

sudo su -s /bin/bash -c “/usr/share/php/passbolt/bin/cake migrations status” nginx
using migration paths

/etc/passbolt/Migrations
using seed paths
/etc/passbolt/Seeds
using environment default

Status Migration ID Migration Name

 up  20170830064410  V162InitialMigration
 up  20170830065037  V200ActiveMustBeBoolean
 up  20170830065038  V200DropUnusedProfileFields
 up  20170830065039  V200IncreaseEmailSize
 up  20170830065040  V200DropUnusedCreatedBy
 up  20170830065041  V200MigrateUUID
 up  20170830065042  V200MigrateKeyField
 up  20171002061834  V200DropUnusedResourceFields
 up  20171006141922  V200AddFavoriteModifiedField
 up  20171009093000  V200DropUnusedPermissionTypesTable
 up  20171009093001  V200MigrateEmailsTable
 up  20171009093002  V200MigrateFileStorageTable
 up  20171025154754  V200AddCommentsUserIdField
 up  20180102065042  V200MigrateForeignIdField
 up  20180102180000  V200DropUnusedTables
 up  20180102221500  V200AddMissingTablesIndexes
 up  20180413171600  V202ForceColumnsCharset
 up  20180503135810  V210InstallAccountSettingsPlugin
 up  20180930151500  V240AddAuthenticationTokenType
 up  20181002171600  V240ExtendAccountSettingsPlugin
 up  20181210170000  V270AddMissingIndexes
 up  20190106170300  V280AdditionalEmailMigration
 up  20190106170301  V280AdditionalFileStorageMigration
 up  20190106170302  V280FileDirectoryPathsMigrations
 up  20190112124290  V270AddActionsTable
 up  20190112124300  V270AddActionLogsTable
 up  20190121111100  V270AddEntitiesHistoryTable
 up  20190121121100  V270AddPermissionsHistoryTable
 up  20190211124300  V270AddSecretsHistoryTable
 up  20190221124300  V270AddSecretAccessesTable
 up  20190512115400  V2100AddOrganizationSettingsTable
 up  20190623143400  V2110ExtendKeyIdSizeField
 up  20190923103000  V2120UpdateEmailQueue
 up  20191119160000  V2120DropUnusedTables
 up  20200108135000  V2130DropLegacyAnonymousUser
 up  20200319135000  V2130SoftDeleteGpgKeysForSoftDeletedUsers
 up  20200501182000  V2130ReconcileLoginHistory
 up  20200806110200  V300ExtendSecretsDataField
 up  20200806110201  V300AddResourceTypeIdField
 up  20200806110202  V300AddResourceTypesTable
 up  20200806110203  V300AddResourceTypesDefaultData
 up  20200806110204  V300AddResourceTypesToResources
 up  20200824191900  V2136CleanupUnusedActionLogs
 up  20200824191901  V2136AddActionLogsRelatedIndexes
 up  20201221093528  V300DeleteMetadataOfSoftDeletedResources
 up  20210111163200  V300AddActionLogsExtraIndex
 up  20210121141742  V320AddAvatarsTable
 up  20210125212543  V320TransferFileStorageToAvatars
 up  20210206521254  V320DropFileStorage
 up  20210329110000  V320FixResourceTypesDefaultData
 up  20210427124200  V330AddMobileTransferTable
 up  20211027202137  V331ConvertEmailVariablesToJson
 up  20211121231300  V340MigrateASCIIFieldsEncoding
 up  20211122732400  V350ConvertIdFieldsToUuidFields

down 20211215180000 V350RemovePermissionsTypeIndex
down 20211215180001 V350AddPermissionsCombinedIndex
down 20220103180000 V350IncreaseResourcesNameUsernameColumnsSize
down 20220103180001 V350IncreaseResourcesNameUsernameLengthInResourceTypes
down 20220405232411 V360RemoveAuthLoginLoginGetActionFromLogs
down 20220405234003 V360RemoveAuthCheckSessionCheckSessionGetFromLogs
down 20220405234359 V360RemoveAuthIsAuthenticatedIsAuthenticatedFromLogs
down 20220802151030 V380AlterNameAndSlugOnResourceTypes
down 20220802151740 V380TrimSpacesOnResourceTypesNameAndSlug

sudo su -s /bin/bash -c “/usr/share/php/passbolt/bin/cake passbolt cleanup” nginx

 ____                  __          ____
/ __ \____  _____ ____/ /_  ____  / / /_

/ // / __ `/ / / __ / __ / / _/
/ / // ( | ) // / // / / /
// _,///./_//__/

Open source password manager for teams

Cleanup shell (fix mode)

No issue found, data looks squeaky clean!

Could you help me?Thanks

garrett · September 30, 2022, 11:23am

Hi @zwighi Instead of deleting the table you could truncate it so the records get cleared.

truncate email_queue TRUNCATE TABLE - MariaDB Knowledge Base

zwighi · September 30, 2022, 12:36pm

Thanks Garret, i’ve just repeated the procedure dropping the db, recreating a new db using command
CREATE DATABASE passbolt CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Then i uploaded the old db. I used command “truncate email_queue” instead “delete from email_queue” and V2120UpdateEmailQueue is ok but…V350RemovePermissionsTypeIndex still continue hanging the migration procedue

garrett · September 30, 2022, 12:54pm

The migration in question removes an index from the permissions table. What do you get when you run:

SHOW INDEX FROM permissions FROM passbolt;

zwighi · September 30, 2022, 1:08pm

MariaDB [passbolt]> SHOW INDEX FROM permissions FROM passbolt;
±------------±-----------±----------------±-------------±----------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
±------------±-----------±----------------±-------------±----------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
| permissions | 0 | PRIMARY | 1 | id | A | 9448 | NULL | NULL | | BTREE | | |
| permissions | 1 | aco_foreign_key | 1 | aco_foreign_key | A | 9448 | NULL | NULL | | BTREE | | |
| permissions | 1 | aco | 1 | aco | A | 2 | NULL | NULL | | BTREE | | |
| permissions | 1 | aco | 2 | aro | A | 4 | NULL | NULL | | BTREE | | |
| permissions | 1 | aro_foreign_key | 1 | aro_foreign_key | A | 224 | NULL | NULL | YES | BTREE | | |
| permissions | 1 | aro_foreign_key | 2 | type | A | 236 | NULL | NULL | | BTREE | | |
±------------±-----------±----------------±-------------±----------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
6 rows in set (0.000 sec)

garrett · September 30, 2022, 1:18pm

I will need someone else to explain what to do to in this scenario. If a migration is “up-only” and not reversible, and the up appears to be blocking…

Q1: is blocking what is actually happening?
Q2: does it get fixed with adding in an index in order to pass the migration? (EDIT: actually, it looks the the index is there)

It seems like there was a mixup of version records or migration history at some point.

garrett · September 30, 2022, 1:22pm

@zwighi can you also provide the healthcheck and OS, thanks.

zwighi · September 30, 2022, 1:40pm

OS
cat /etc/redhat-release
Red Hat Enterprise Linux release 8.6 (Ootpa)

Healthcheck
sudo -H -u nginx /bin/bash -c “/usr/share/php/passbolt/bin/cake passbolt healthcheck -v”

 ____                  __          ____
/ __ \____  _____ ____/ /_  ____  / / /_

/ // / __ `/ / / __ / __ / / _/
/ / // ( | ) // / // / / /
// _,///./_//__/

Open source password manager for teams

Healthcheck shell

Environment

[PASS] PHP version 7.4.32.
[PASS] PCRE compiled with unicode support.
[PASS] The temporary directory and its content are writable and not executable.
[PASS] The logs directory and its content are writable.
[PASS] GD or Imagick extension is installed.
[PASS] Intl extension is installed.
[PASS] Mbstring extension is installed.

Config files

[PASS] The application config file is present
[PASS] The passbolt config file is present

Core config

[PASS] Debug mode is off.
[PASS] Cache is working.
[PASS] Unique value set for security.salt
[PASS] Full base url is set to https://passbolt...com
[PASS] App.fullBaseUrl validation OK.
[PASS] /healthcheck/status is reachable.

SSL Certificate

[PASS] SSL peer certificate validates
[PASS] Hostname is matching in SSL certificate.
[PASS] Not using a self-signed certificate

Database

[PASS] The application is able to connect to the database
[PASS] 26 tables found
[PASS] Some default content is present
[FAIL] The database schema is not up to date.
[HELP] Run the migration scripts:
[HELP] sudo su -s /bin/bash -c “/usr/share/php/passbolt/bin/cake migrations migrate --no-lock” nginx
[HELP] See. Passbolt Help | Update

GPG Configuration

[PASS] PHP GPG Module is installed and loaded.
[PASS] The environment variable GNUPGHOME is set to /var/lib/passbolt/.gnupg.
[PASS] The directory /var/lib/passbolt/.gnupg containing the keyring is writable by the webserver user.
[PASS] The server OpenPGP key is not the default one
[PASS] The public key file is defined in config/passbolt.php and readable.
[PASS] The private key file is defined in config/passbolt.php and readable.
[PASS] The server key fingerprint matches the one defined in config/passbolt.php.
[PASS] The server public key defined in the config/passbolt.php (or environment variables) is in the keyring.
[PASS] There is a valid email id defined for the server key.
[PASS] The public key can be used to encrypt a message.
[PASS] The private key can be used to sign a message.
[PASS] The public and private keys can be used to encrypt and sign a message.
[PASS] The private key can be used to decrypt a message.
[PASS] The private key can be used to decrypt and verify a message.
[PASS] The public key can be used to verify a signature.
[PASS] The server public key format is Gopengpg compatible.
[PASS] The server private key format is Gopengpg compatible.

Application configuration

[PASS] Using latest passbolt version (3.7.3).
[PASS] Passbolt is configured to force SSL use.
[PASS] App.fullBaseUrl is set to HTTPS.
[PASS] Selenium API endpoints are disabled.
[PASS] Search engine robots are told not to index content.
[PASS] Registration is closed, only administrators can add users.
[PASS] Serving the compiled version of the javascript app
[PASS] All email notifications will be sent.

JWT Authentication

[PASS] The JWT Authentication plugin is enabled
[PASS] The /etc/passbolt/jwt/ directory is not writable.
[PASS] A valid JWT key pair was found

[FAIL] 1 error(s) found. Hang in there!

garrett · September 30, 2022, 1:55pm

@zwighi Can you also provide php -m

Just a guess, but maybe sqlite3 module is missing, as the migration is attempting to perform a column modification.

zwighi · September 30, 2022, 2:15pm

php -m
[PHP Modules]
bz2
calendar
Core
ctype
curl
date
dom
exif
fileinfo
filter
ftp
gd
gettext
gnupg
hash
iconv
intl
json
libxml
mbstring
mysqli
mysqlnd
openssl
pcntl
pcre
PDO
pdo_mysql
pdo_sqlite
Phar
posix
readline
Reflection
session
shmop
SimpleXML
sockets
sodium
SPL
sqlite3
standard
sysvmsg
sysvsem
sysvshm
tokenizer
xml
xmlreader
xmlwriter
xsl
Zend OPcache
zlib

[Zend Modules]
Zend OPcache

garrett · September 30, 2022, 2:35pm

@zwighi Well, I’m not sure why it’s not working, but you could try manually dropping the index (since you have a backup we have no worries, right?).

ALTER TABLE permissions DROP INDEX type

zwighi · October 3, 2022, 6:50am

Hi, i solved launching this command
create index type_desc on permissions(type);

My db was modified years ago for speeding up Passbolt because of problems loading password/groups by creating new indexes.

Thanks for your precious help!