Foreign key error after 1.6.0 upgrade

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.

Hello @dam09fr,

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

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

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?

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 : https://pydio.com/en/community/releases/pydio-cells/pydio-cells-150-performances-features-security

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.

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

@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;

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

@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

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.