[solved] Foreign key error after 1.6.0 upgrade

Pydio Cells
1

Hello,

I’ve an error in my logs since I upgrade to 1.6.0 (docker) :

{"level":"error","ts":"2019-07-05T22:06:49Z","logger":"pydio.grpc.data-key","msg":"Failed to init DB provider","error":"Error 1005: Can't create table `cells`.`enc_node_blocks` (errno: 150 \"Foreign key constraint is incorrectly formed\") handling data_key_data-key.0.3.sql"}
{"level":"error","ts":"2019-07-05T22:06:49Z","logger":"pydio.grpc.data-key","msg":"Could not run ","error":"Error 1005: Can't create table `cells`.`enc_node_blocks` (errno: 150 \"Foreign key constraint is incorrectly formed\") handling data_key_data-key.0.3.sql"}

Nothing special to add … except it doesn’t seems to affect the usability of the app.
Thanks in advance for your help.

2

Hello @dam09fr,

could you tell me on which Cells version you were before upgrading?

3

I was on 1.5.3 (still Docker)
I followed all upgrades since 1.2.0 - 1.2.5 (I don’t remember exactly).

4

Hey @dam09fr ,

Could you tell me the exact steps that you did for the upgrade (did you stop the old container, and then restarted a new one with the newest image?),
also I would like to know if you altered the database? (such as deleting, updating something).

And what is your database version?

5

My database server is MariaDB : 10.3.16
I never altered the database since the first installation (data or schema).

To upgrade, I stop the old container, and restarted a new one with the newest version with mapping the same data volume.

With v1.5.0, I followed the instructions on the blog to migrate indexes:

./cells-ctl data sync --service pydio.grpc.log
./cells-ctl data sync --service pydio.grpc.search
./cells-ctl data sync --service pydio.grpc.docstore

Source : Pydio Cells 1.5.0 - Performances, features, security | Pydio

6

Hello @dam09fr,

Could you put the logs in debug mod and show me the logs of this error.

To change the logs type you can either change the env variable to PYDIO_LOGS_LEVEL=debug,
you can run a new container with this env variable.

7

Hello @zayn,

I just upgrade to 1.6.1, here’s the part of my logs I think you want (I didn’t see anything else that can help you):

2019-07-15T14:11:20.048Z        DEBUG   pydio.grpc.data-key     BeforeStart - Valid dependencies
2019-07-15T14:11:20.054Z        DEBUG   pydio.grpc.workspace    BeforeStart - Valid dependencies
2019-07-15T14:11:20.055Z        ERROR   pydio.grpc.data-key     Failed to init DB provider      {"error": "Error 1054: Unknown column 'nonce' in 'field list' handling data_key_data-key.0.3.sql"}
github.com/pydio/cells/common/service.newDBProvider.func1
        /opt/teamcity/agent/work/fd94d35d44c59350/src/github.com/pydio/cells/common/service/db.go:59
github.com/pydio/cells/vendor/github.com/micro/go-micro.(*service).Start
        /opt/teamcity/agent/work/fd94d35d44c59350/src/github.com/pydio/cells/vendor/github.com/micro/go-micro/service.go:98
github.com/pydio/cells/vendor/github.com/micro/go-micro.(*service).Run
        /opt/teamcity/agent/work/fd94d35d44c59350/src/github.com/pydio/cells/vendor/github.com/micro/go-micro/service.go:147
github.com/pydio/cells/common/service.(*service).Start.func1
        /opt/teamcity/agent/work/fd94d35d44c59350/src/github.com/pydio/cells/common/service/service.go:375
2019-07-15T14:11:20.055Z        ERROR   pydio.grpc.data-key     Could not run   {"error": "Error 1054: Unknown column 'nonce' in 'field list' handling data_key_data-key.0.3.sql"}
github.com/pydio/cells/common/service.(*service).Start.func1
        /opt/teamcity/agent/work/fd94d35d44c59350/src/github.com/pydio/cells/common/service/service.go:376
2019-07-15T14:11:20.055Z        INFO    pydio.grpc.data-key     stopping
8

@zayn, I think the problem is somewhere in this file:
https://github.com/pydio/cells/blob/master/data/key/migrations/mysql/data-key.0.3.sql

Here’s my DB schema:

-- Structure de la table `enc_legacy_nodes`
CREATE TABLE `enc_legacy_nodes` (
  `node_id` varchar(255) NOT NULL,
  `nonce` longblob DEFAULT NULL,
  `block_size` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Structure de la table `enc_nodes`
CREATE TABLE `enc_nodes` (
  `node_id` varchar(255) NOT NULL,
  `legacy` int(11) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Structure de la table `enc_node_blocks`
CREATE TABLE `enc_node_blocks` (
  `node_id` varchar(255) NOT NULL,
  `part_id` int(11) DEFAULT NULL,
  `seq_start` int(11) DEFAULT NULL,
  `seq_end` int(11) DEFAULT NULL,
  `block_data_size` int(11) DEFAULT NULL,
  `block_header_size` int(11) DEFAULT NULL,
  `owner` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Structure de la table `enc_node_keys`
CREATE TABLE `enc_node_keys` (
  `node_id` varchar(255) NOT NULL,
  `owner_id` varchar(255) NOT NULL,
  `user_id` varchar(255) NOT NULL,
  `key_data` blob DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Index pour les tables déchargées

-- Index pour la table `enc_legacy_nodes`
ALTER TABLE `enc_legacy_nodes`
  ADD PRIMARY KEY (`node_id`);

-- Index pour la table `enc_nodes`
ALTER TABLE `enc_nodes`
  ADD PRIMARY KEY (`node_id`);

-- Index pour la table `enc_node_keys`
ALTER TABLE `enc_node_keys`
  ADD KEY `node_id` (`node_id`);

-- Contraintes pour les tables déchargées

-- Contraintes pour la table `enc_node_keys`
ALTER TABLE `enc_node_keys`
  ADD CONSTRAINT `enc_node_keys_ibfk_1` FOREIGN KEY (`node_id`) REFERENCES `enc_nodes` (`node_id`) ON DELETE CASCADE;
COMMIT;
9

Could you please show me the content of the table gorp_migrations.

10

@zayn Here’s what you asked for:

id applied_at
1 2019-02-18 19:44:04
2 2019-02-18 19:44:11
3 2019-02-18 19:44:16
data_changes_0.1.sql 2019-02-18 19:44:03
data_changes_0.2.sql 2019-02-18 19:44:04
data_index_cellsdata_idx_0.1.sql 2019-02-18 19:44:10
data_index_cellsdata_idx_0.2.sql 2019-04-16 21:51:16
data_index_personal_idx_0.1.sql 2019-02-18 19:44:09
data_index_personal_idx_0.2.sql 2019-04-16 21:51:31
data_index_pydiods1_idx_0.1.sql 2019-02-18 19:44:10
data_index_pydiods1_idx_0.2.sql 2019-04-16 21:51:15
data_key_data-key.0.1.sql 2019-02-18 19:44:05
data_key_data-key.0.2.sql 2019-02-18 19:44:10
data_meta_0.1.sql 2019-02-18 19:44:01
data_meta_0.2.sql 2019-02-18 19:44:01
idm_acl_0.1.sql 2019-02-18 19:44:05
idm_acl_0.2.sql 2019-05-07 18:06:24
idm_key_0.1.sql 2019-02-18 19:44:01
idm_policy_0.1.sql 2019-02-18 19:44:16
idm_role_0.1.sql 2019-02-18 19:44:10
idm_role_0.2.sql 2019-02-20 15:48:07
idm_role_0.3.sql 2019-05-07 18:06:26
idm_role_policies_0.1.sql 2019-02-18 19:44:08
idm_user_0.1.sql 2019-02-18 19:44:14
idm_user_0.2.sql 2019-04-16 21:51:18
idm_user_0.3.sql 2019-05-07 18:06:28
idm_user_idx_0.1.sql 2019-02-18 19:44:13
idm_user_idx_0.2.sql 2019-04-16 21:51:14
idm_user_policies_0.1.sql 2019-02-18 19:44:11
idm_usr_meta_0.1.sql 2019-02-18 19:44:04
idm_usr_meta_ns_0.1.sql 2019-02-18 19:44:06
idm_usr_meta_policies_0.1.sql 2019-02-18 19:44:02
idm_workspace_0.1.sql 2019-02-18 19:44:10
idm_workspace_policies_0.1.sql 2019-02-18 19:44:07
11

Hello @dam09fr ,

Could you use the following command on your database,

INSERT INTO `cells`.`gorp_migrations` (`id`, `applied_at`) VALUES ('data_key_data-key.0.3.sql', '2019-02-18 19:44:15');

It seems that you are missing this entry and therefore Cells is trying to run the migration process even though it seems that you actually have completed the process a long time ago, I have compared your database scheme against mine and they are the same.

12

Hello @zayn,
Thank you for your help.
No more error after I use your SQL query.
I’ll investigate next week on my test server to see if something was wrong on my side or if Cells missed the query during the upgrade process for some reason.
Thanks again.