What do you see when you’re executing the mysql command show tables now?
Could you also run, the following so that we can see if there are some issues with specific fields:
SELECT table_schema, table_name, column_name, character_set_name, collation_name FROM information_schema.columns where table_schema='passbolt' ORDER BY table_schema, table_name,ordinal_position;
Replace ‘passbolt’ by the name of your database.
@remy the output of the command you say:
mysql> SELECT table_schema, table_name, column_name, character_set_name, collation_name FROM information_schema.columns where table_schema=‘passbolt’ ORDER BY table_schema, table_name,ordinal_position;
±-------------±-----------------------------±----------------±-------------------±------------------+
| table_schema | table_name | column_name | character_set_name | collation_name |
±-------------±-----------------------------±----------------±-------------------±------------------+
| passbolt | authentication_tokens | id | utf8 | utf8_unicode_ci |
| passbolt | authentication_tokens | token | utf8 | utf8_unicode_ci |
| passbolt | authentication_tokens | user_id | utf8 | utf8_unicode_ci |
| passbolt | authentication_tokens | active | NULL | NULL |
| passbolt | authentication_tokens | created | NULL | NULL |
| passbolt | authentication_tokens | modified | NULL | NULL |
| passbolt | authentication_tokens | created_by | utf8 | utf8_unicode_ci |
| passbolt | authentication_tokens | modified_by | utf8 | utf8_unicode_ci |
| passbolt | burzum_file_storage_phinxlog | version | NULL | NULL |
| passbolt | burzum_file_storage_phinxlog | migration_name | utf8 | utf8_general_ci |
| passbolt | burzum_file_storage_phinxlog | start_time | NULL | NULL |
| passbolt | burzum_file_storage_phinxlog | end_time | NULL | NULL |
| passbolt | burzum_file_storage_phinxlog | breakpoint | NULL | NULL |
| passbolt | cake_sessions | id | latin1 | latin1_swedish_ci |
| passbolt | cake_sessions | data | latin1 | latin1_swedish_ci |
| passbolt | cake_sessions | expires | NULL | NULL |
| passbolt | comments | id | utf8 | utf8_unicode_ci |
| passbolt | comments | parent_id | utf8 | utf8_unicode_ci |
| passbolt | comments | foreign_id | utf8 | utf8_unicode_ci |
| passbolt | comments | foreign_model | utf8 | utf8_unicode_ci |
| passbolt | comments | content | utf8 | utf8_unicode_ci |
| passbolt | comments | created | NULL | NULL |
| passbolt | comments | modified | NULL | NULL |
| passbolt | comments | created_by | utf8 | utf8_unicode_ci |
| passbolt | comments | modified_by | utf8 | utf8_unicode_ci |
| passbolt | controller_logs | id | utf8 | utf8_unicode_ci |
| passbolt | controller_logs | user_id | utf8 | utf8_unicode_ci |
| passbolt | controller_logs | role_id | utf8 | utf8_unicode_ci |
| passbolt | controller_logs | level | utf8 | utf8_unicode_ci |
| passbolt | controller_logs | method | utf8 | utf8_unicode_ci |
| passbolt | controller_logs | controller | utf8 | utf8_unicode_ci |
| passbolt | controller_logs | action | utf8 | utf8_unicode_ci |
| passbolt | controller_logs | user_agent_id | utf8 | utf8_unicode_ci |
| passbolt | controller_logs | ip | utf8 | utf8_unicode_ci |
| passbolt | controller_logs | request_data | utf8 | utf8_unicode_ci |
| passbolt | controller_logs | message | utf8 | utf8_unicode_ci |
| passbolt | controller_logs | scope | utf8 | utf8_unicode_ci |
| passbolt | controller_logs | created | NULL | NULL |
| passbolt | email_queue | id | ascii | ascii_general_ci |
| passbolt | email_queue | to | utf8 | utf8_general_ci |
| passbolt | email_queue | from_name | utf8 | utf8_general_ci |
| passbolt | email_queue | from_email | utf8 | utf8_general_ci |
| passbolt | email_queue | subject | utf8 | utf8_general_ci |
| passbolt | email_queue | config | utf8 | utf8_general_ci |
| passbolt | email_queue | template | utf8 | utf8_general_ci |
| passbolt | email_queue | layout | utf8 | utf8_general_ci |
| passbolt | email_queue | format | utf8 | utf8_general_ci |
| passbolt | email_queue | template_vars | utf8 | utf8_general_ci |
| passbolt | email_queue | headers | utf8 | utf8_general_ci |
| passbolt | email_queue | sent | NULL | NULL |
| passbolt | email_queue | locked | NULL | NULL |
| passbolt | email_queue | send_tries | NULL | NULL |
| passbolt | email_queue | send_at | NULL | NULL |
| passbolt | email_queue | created | NULL | NULL |
| passbolt | email_queue | modified | NULL | NULL |
| passbolt | email_queue_phinxlog | version | NULL | NULL |
| passbolt | email_queue_phinxlog | migration_name | utf8 | utf8_general_ci |
| passbolt | email_queue_phinxlog | start_time | NULL | NULL |
| passbolt | email_queue_phinxlog | end_time | NULL | NULL |
| passbolt | email_queue_phinxlog | breakpoint | NULL | NULL |
| passbolt | favorites | id | utf8 | utf8_unicode_ci |
| passbolt | favorites | user_id | utf8 | utf8_unicode_ci |
| passbolt | favorites | foreign_id | utf8 | utf8_unicode_ci |
| passbolt | favorites | foreign_model | utf8 | utf8_unicode_ci |
| passbolt | favorites | created | NULL | NULL |
| passbolt | file_storage | id | latin1 | latin1_swedish_ci |
| passbolt | file_storage | user_id | latin1 | latin1_swedish_ci |
| passbolt | file_storage | foreign_key | latin1 | latin1_swedish_ci |
| passbolt | file_storage | model | latin1 | latin1_swedish_ci |
| passbolt | file_storage | filename | latin1 | latin1_swedish_ci |
| passbolt | file_storage | filesize | NULL | NULL |
| passbolt | file_storage | mime_type | latin1 | latin1_swedish_ci |
| passbolt | file_storage | extension | latin1 | latin1_swedish_ci |
| passbolt | file_storage | hash | latin1 | latin1_swedish_ci |
| passbolt | file_storage | path | latin1 | latin1_swedish_ci |
| passbolt | file_storage | adapter | latin1 | latin1_swedish_ci |
| passbolt | file_storage | created | NULL | NULL |
| passbolt | file_storage | modified | NULL | NULL |
| passbolt | gpgkeys | id | utf8 | utf8_unicode_ci |
| passbolt | gpgkeys | user_id | utf8 | utf8_unicode_ci |
| passbolt | gpgkeys | key | utf8 | utf8_unicode_ci |
| passbolt | gpgkeys | bits | NULL | NULL |
| passbolt | gpgkeys | uid | utf8 | utf8_unicode_ci |
| passbolt | gpgkeys | key_id | utf8 | utf8_unicode_ci |
| passbolt | gpgkeys | fingerprint | utf8 | utf8_unicode_ci |
| passbolt | gpgkeys | type | utf8 | utf8_unicode_ci |
| passbolt | gpgkeys | expires | NULL | NULL |
| passbolt | gpgkeys | key_created | NULL | NULL |
| passbolt | gpgkeys | deleted | NULL | NULL |
| passbolt | gpgkeys | created | NULL | NULL |
| passbolt | gpgkeys | modified | NULL | NULL |
| passbolt | gpgkeys | created_by | utf8 | utf8_unicode_ci |
| passbolt | gpgkeys | modified_by | utf8 | utf8_unicode_ci |
| passbolt | groups | id | utf8 | utf8_unicode_ci |
| passbolt | groups | name | utf8 | utf8_unicode_ci |
| passbolt | groups | deleted | NULL | NULL |
| passbolt | groups | created | NULL | NULL |
| passbolt | groups | modified | NULL | NULL |
| passbolt | groups | created_by | utf8 | utf8_unicode_ci |
| passbolt | groups | modified_by | utf8 | utf8_unicode_ci |
| passbolt | groups_users | id | utf8 | utf8_unicode_ci |
| passbolt | groups_users | group_id | utf8 | utf8_unicode_ci |
| passbolt | groups_users | user_id | utf8 | utf8_unicode_ci |
| passbolt | groups_users | is_admin | NULL | NULL |
| passbolt | groups_users | created | NULL | NULL |
| passbolt | groups_users | created_by | utf8 | utf8_unicode_ci |
| passbolt | permissions | id | utf8 | utf8_unicode_ci |
| passbolt | permissions | aco | utf8 | utf8_unicode_ci |
| passbolt | permissions | aco_foreign_key | utf8 | utf8_unicode_ci |
| passbolt | permissions | aro | utf8 | utf8_unicode_ci |
| passbolt | permissions | aro_foreign_key | utf8 | utf8_unicode_ci |
| passbolt | permissions | type | NULL | NULL |
| passbolt | permissions | created | NULL | NULL |
| passbolt | permissions | modified | NULL | NULL |
| passbolt | permissions | created_by | utf8 | utf8_unicode_ci |
| passbolt | permissions | modified_by | utf8 | utf8_unicode_ci |
| passbolt | permissions_types | id | utf8 | utf8_unicode_ci |
| passbolt | permissions_types | serial | utf8 | utf8_unicode_ci |
| passbolt | permissions_types | name | utf8 | utf8_unicode_ci |
| passbolt | permissions_types | description | utf8 | utf8_unicode_ci |
| passbolt | permissions_types | active | NULL | NULL |
| passbolt | phinxlog | version | NULL | NULL |
| passbolt | phinxlog | migration_name | utf8 | utf8_general_ci |
| passbolt | phinxlog | start_time | NULL | NULL |
| passbolt | phinxlog | end_time | NULL | NULL |
| passbolt | phinxlog | breakpoint | NULL | NULL |
| passbolt | profiles | id | utf8 | utf8_unicode_ci |
| passbolt | profiles | user_id | utf8 | utf8_unicode_ci |
| passbolt | profiles | gender | utf8 | utf8_unicode_ci |
| passbolt | profiles | date_of_birth | NULL | NULL |
| passbolt | profiles | title | utf8 | utf8_unicode_ci |
| passbolt | profiles | first_name | utf8 | utf8_unicode_ci |
| passbolt | profiles | last_name | utf8 | utf8_unicode_ci |
| passbolt | profiles | timezone | utf8 | utf8_unicode_ci |
| passbolt | profiles | locale | utf8 | utf8_unicode_ci |
| passbolt | profiles | created | NULL | NULL |
| passbolt | profiles | modified | NULL | NULL |
| passbolt | resources | id | utf8 | utf8_unicode_ci |
| passbolt | resources | name | utf8 | utf8_unicode_ci |
| passbolt | resources | username | utf8 | utf8_unicode_ci |
| passbolt | resources | expiry_date | NULL | NULL |
| passbolt | resources | uri | utf8 | utf8_unicode_ci |
| passbolt | resources | description | utf8 | utf8_unicode_ci |
| passbolt | resources | deleted | NULL | NULL |
| passbolt | resources | created | NULL | NULL |
| passbolt | resources | modified | NULL | NULL |
| passbolt | resources | created_by | utf8 | utf8_unicode_ci |
| passbolt | resources | modified_by | utf8 | utf8_unicode_ci |
| passbolt | roles | id | utf8 | utf8_unicode_ci |
| passbolt | roles | name | utf8 | utf8_unicode_ci |
| passbolt | roles | description | utf8 | utf8_unicode_ci |
| passbolt | roles | created | NULL | NULL |
| passbolt | roles | modified | NULL | NULL |
| passbolt | schema_migrations | id | NULL | NULL |
| passbolt | schema_migrations | class | utf8 | utf8_unicode_ci |
| passbolt | schema_migrations | type | utf8 | utf8_unicode_ci |
| passbolt | schema_migrations | created | NULL | NULL |
| passbolt | secrets | id | utf8 | utf8_unicode_ci |
| passbolt | secrets | user_id | utf8 | utf8_unicode_ci |
| passbolt | secrets | resource_id | utf8 | utf8_unicode_ci |
| passbolt | secrets | data | utf8 | utf8_unicode_ci |
| passbolt | secrets | created | NULL | NULL |
| passbolt | secrets | modified | NULL | NULL |
| passbolt | secrets | created_by | utf8 | utf8_unicode_ci |
| passbolt | secrets | modified_by | utf8 | utf8_unicode_ci |
| passbolt | users | id | utf8 | utf8_unicode_ci |
| passbolt | users | role_id | utf8 | utf8_unicode_ci |
| passbolt | users | username | utf8 | utf8_unicode_ci |
| passbolt | users | active | NULL | NULL |
| passbolt | users | deleted | NULL | NULL |
| passbolt | users | created | NULL | NULL |
| passbolt | users | modified | NULL | NULL |
| passbolt | users | created_by | utf8 | utf8_unicode_ci |
| passbolt | users | modified_by | utf8 | utf8_unicode_ci |
| passbolt | users_resources_permissions | user_id | utf8 | utf8_unicode_ci |
| passbolt | users_resources_permissions | resource_id | utf8 | utf8_unicode_ci |
| passbolt | users_resources_permissions | permission_id | utf8 | utf8_unicode_ci |
| passbolt | users_resources_permissions | permission_type | NULL | NULL |
| passbolt | user_agents | id | utf8 | utf8_unicode_ci |
| passbolt | user_agents | name | utf8 | utf8_unicode_ci |
±-------------±-----------------------------±----------------±-------------------±------------------+
180 rows in set (0.01 sec)
@remy the output of show tables:
mysql> show tables;
±-----------------------------+
| Tables_in_passbolt |
±-----------------------------+
| authentication_tokens |
| burzum_file_storage_phinxlog |
| cake_sessions |
| comments |
| controller_logs |
| email_queue |
| email_queue_phinxlog |
| favorites |
| file_storage |
| gpgkeys |
| groups |
| groups_users |
| permissions |
| permissions_types |
| phinxlog |
| profiles |
| resources |
| roles |
| schema_migrations |
| secrets |
| user_agents |
| users |
| users_resources_permissions |
±-----------------------------+
23 rows in set (0.00 sec)
@jpmdwx I can see several issues:
- You still have all the fields / tables that were dropped during v1 to v2 migration
- You have mixed collations (like latin1_swedish_ci, utf8_unicode_ci, utf8_general_ci) this is known to cause issues when joining tables.
- You do not have anywhere the charset of the v2 ( utf8mb4_unicode_ci)
It seems that your database schema is not affected by the migration script, which i’m not sure why.
Just in case, can you remove the files under tmp/cache/persistent/ and tmp/cache/model your user doesn’t have right on.
@cedric I’ve removed the files you says, now migration’s script output is all right.
@remy What can I do then? May be I can force collation/charset on my schema?
Thanks!
Can you execute this mysql command describe favorites; so we know if the migrations performed at least the expected columns changes.
Here is the output:
mysql> describe favorites;
±--------------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±--------------±------------±-----±----±--------±------+
| id | varchar(36) | NO | PRI | NULL | |
| user_id | varchar(36) | YES | | NULL | |
| foreign_id | varchar(36) | NO | | NULL | |
| foreign_model | varchar(36) | NO | | NULL | |
| created | datetime | NO | | NULL | |
±--------------±------------±-----±----±--------±------+
5 rows in set (0.00 sec)
Here foreign_id should be renamed in foreign_key.
The db migrations scripts are not run against your passbolt db, I don’t know where else they can be run on
When you’re loading your backup from the v1, is the database empty ?
I’ve dropped my schema and imported it again from backup. Then, I ran the migration script and now is working fine!!
Thanks a lot for all your help!!!
Best regards
This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.