Hello everyone and thanks in advance.
Describe your issue in detail
I had a problem with the database after pulling docker compose for an error. i was updating some dockers, and i was on the wrong folder, and then my pydio cells and mysql docker were updated without been my intention.
My docker compose:
cat docker-compose.yml
version: ‘3.9’
services:
cells:
image: pydio/cells:latest
restart: unless-stopped
hostname: pydio-cells
ports: [“8080:8080”]
environment:
- CELLS_LOG_LEVEL=production
- CELLS_EXTERNAL=https://xxxxxxxxxx.eu:443
volumes:
- ./data:/var/cells/data
- ./cellsdir:/var/cells
mysql:
image: mysql:latest
restart: unless-stopped
ports: [“3306:3306”]
environment:
MYSQL_ROOT_PASSWORD: xxxxxxxxxxxxxxxxx
MYSQL_DATABASE: cells
MYSQL_USER: pydio
MYSQL_PASSWORD: xxxxxxxxxxx
command: [mysqld, --character-set-server=utf8mb4, --collation-server=utf8mb4_unicode_ci]
volumes:
- ./mysqldir:/var/lib/mysql
This was working a year ago till today, without any problem. What i had, is backup of all this folders and volumes, the pydio one an the mysql.
once i trigger docker-compose up -d i got this error:
Blockquote ERROR pydio.grpc.oauth Could not apply migrations {“error”: “error executing migrations/20190400000009000000_oauth2.mysql.up.sql, sql: \n– First we need to delete all rows that point to a non-existing oauth2 client.\nDELETE FROM hydra_oauth2_access WHERE NOT EXISTS (SELECT 1 FROM hydra_client WHERE hydra_oauth2_access.client_id = hydra_client.id);\nDELETE FROM hydra_oauth2_refresh WHERE NOT EXISTS (SELECT 1 FROM hydra_client WHERE hydra_oauth2_refresh.client_id = hydra_client.id);\nDELETE FROM hydra_oauth2_code WHERE NOT EXISTS (SELECT 1 FROM hydra_client WHERE hydra_oauth2_code.client_id = hydra_client.id);\nDELETE FROM hydra_oauth2_oidc WHERE NOT EXISTS (SELECT 1 FROM hydra_client WHERE hydra_oauth2_oidc.client_id = hydra_client.id);\nDELETE FROM hydra_oauth2_pkce WHERE NOT EXISTS (SELECT 1 FROM hydra_client WHERE hydra_oauth2_pkce.client_id = hydra_client.id);\n\n– request_id is a 40 varchar in the referenced table which is why we are resizing\n– 1. We must remove request_ids longer than 40 chars. This should never happen as we’ve never issued them longer than this\nDELETE FROM hydra_oauth2_access WHERE LENGTH(request_id) > 40;\nDELETE FROM hydra_oauth2_refresh WHERE LENGTH(request_id) > 40;\nDELETE FROM hydra_oauth2_code WHERE LENGTH(request_id) > 40;\nDELETE FROM hydra_oauth2_oidc WHERE LENGTH(request_id) > 40;\nDELETE FROM hydra_oauth2_pkce WHERE LENGTH(request_id) > 40;\n\n– 2. Next we’re actually resizing\nALTER TABLE hydra_oauth2_access MODIFY request_id varchar(40) NOT NULL DEFAULT ‘’;\nALTER TABLE hydra_oauth2_refresh MODIFY request_id varchar(40) NOT NULL DEFAULT ‘’;\nALTER TABLE hydra_oauth2_code MODIFY request_id varchar(40) NOT NULL DEFAULT ‘’;\nALTER TABLE hydra_oauth2_oidc MODIFY request_id varchar(40) NOT NULL DEFAULT ‘’;\nALTER TABLE hydra_oauth2_pkce MODIFY request_id varchar(40) NOT NULL DEFAULT ‘’;\n\n– In preparation for creating the client_id index and foreign key, we must set it to varchar(255) which is also\n– the length of hydra_client.id\nDELETE FROM hydra_oauth2_access WHERE LENGTH(client_id) > 255;\nDELETE FROM hydra_oauth2_refresh WHERE LENGTH(client_id) > 255;\nDELETE FROM hydra_oauth2_code WHERE LENGTH(client_id) > 255;\nDELETE FROM hydra_oauth2_oidc WHERE LENGTH(client_id) > 255;\nDELETE FROM hydra_oauth2_pkce WHERE LENGTH(client_id) > 255;\nALTER TABLE hydra_oauth2_access MODIFY client_id varchar(255) NOT NULL DEFAULT ‘’;\nALTER TABLE hydra_oauth2_refresh MODIFY client_id varchar(255) NOT NULL DEFAULT ‘’;\nALTER TABLE hydra_oauth2_code MODIFY client_id varchar(255) NOT NULL DEFAULT ‘’;\nALTER TABLE hydra_oauth2_oidc MODIFY client_id varchar(255) NOT NULL DEFAULT ‘’;\nALTER TABLE hydra_oauth2_pkce MODIFY client_id varchar(255) NOT NULL DEFAULT ‘’;\n\n– Now it’s time to create the index for client_id\nCREATE INDEX hydra_oauth2_access_client_id_idx ON hydra_oauth2_access (client_id);\nCREATE INDEX hydra_oauth2_refresh_client_id_idx ON hydra_oauth2_refresh (client_id);\nCREATE INDEX hydra_oauth2_code_client_id_idx ON hydra_oauth2_code (client_id);\nCREATE INDEX hydra_oauth2_oidc_client_id_idx ON hydra_oauth2_oidc (client_id);\nCREATE INDEX hydra_oauth2_pkce_client_id_idx ON hydra_oauth2_pkce (client_id);\n\n– Foreign keys start here\n\n– This creates a foreign key that cascade delete’s if the client_id is removed.\nALTER TABLE hydra_oauth2_access ADD CONSTRAINT hydra_oauth2_access_client_id_fk FOREIGN KEY (client_id) REFERENCES hydra_client(id) ON DELETE CASCADE;\nALTER TABLE hydra_oauth2_refresh ADD CONSTRAINT hydra_oauth2_refresh_client_id_fk FOREIGN KEY (client_id) REFERENCES hydra_client(id) ON DELETE CASCADE;\nALTER TABLE hydra_oauth2_code ADD CONSTRAINT hydra_oauth2_code_client_id_fk FOREIGN KEY (client_id) REFERENCES hydra_client(id) ON DELETE CASCADE;\nALTER TABLE hydra_oauth2_oidc ADD CONSTRAINT hydra_oauth2_oidc_client_id_fk FOREIGN KEY (client_id) REFERENCES hydra_client(id) ON DELETE CASCADE;\nALTER TABLE hydra_oauth2_pkce ADD CONSTRAINT hydra_oauth2_pkce_client_id_fk FOREIGN KEY (client_id) REFERENCES hydra_client(id) ON DELETE CASCADE;\n\n– This creates a foreign key that cascade delete’s if the consent associated with this is removed.\nALTER TABLE hydra_oauth2_access ADD CONSTRAINT hydra_oauth2_access_challenge_id_fk FOREIGN KEY (challenge_id) REFERENCES hydra_oauth2_consent_request_handled(challenge) ON DELETE CASCADE;\nALTER TABLE hydra_oauth2_refresh ADD CONSTRAINT hydra_oauth2_refresh_challenge_id_fk FOREIGN KEY (challenge_id) REFERENCES hydra_oauth2_consent_request_handled(challenge) ON DELETE CASCADE;\nALTER TABLE hydra_oauth2_code ADD CONSTRAINT hydra_oauth2_code_challenge_id_fk FOREIGN KEY (challenge_id) REFERENCES hydra_oauth2_consent_request_handled(challenge) ON DELETE CASCADE;\nALTER TABLE hydra_oauth2_oidc ADD CONSTRAINT hydra_oauth2_oidc_challenge_id_fk FOREIGN KEY (challenge_id) REFERENCES hydra_oauth2_consent_request_handled(challenge) ON DELETE CASCADE;\nALTER TABLE hydra_oauth2_pkce ADD CONSTRAINT hydra_oauth2_pkce_challenge_id_fk FOREIGN KEY (challenge_id) REFERENCES hydra_oauth2_consent_request_handled(challenge) ON DELETE CASCADE;\n\n: Error 1061: Duplicate key name ‘hydra_oauth2_access_client_id_idx’”, “errorVerbose”: “Error 1061: Duplicate key name ‘hydra_oauth2_access_client_id_idx’\nerror executing migrations/20190400000009000000_oauth2.mysql.up.sql, sql: \n– First we need to delete all rows that point to a non-existing oauth2 client.\nDELETE FROM hydra_oauth2_access WHERE NOT EXISTS (SELECT 1 FROM hydra_client WHERE hydra_oauth2_access.client_id = hydra_client.id);\nDELETE FROM hydra_oauth2_refresh WHERE NOT EXISTS (SELECT 1 FROM hydra_client WHERE hydra_oauth2_refresh.client_id = hydra_client.id);\nDELETE FROM hydra_oauth2_code WHERE NOT EXISTS (SELECT 1 FROM hydra_client WHERE hydra_oauth2_code.client_id = hydra_client.id);\nDELETE FROM hydra_oauth2_oidc WHERE NOT EXISTS (SELECT 1 FROM hydra_client WHERE hydra_oauth2_oidc.client_id = hydra_client.id);\nDELETE FROM hydra_oauth2_pkce WHERE NOT EXISTS (SELECT 1 FROM hydra_client WHERE hydra_oauth2_pkce.client_id = hydra_client.id);\n\n– request_id is a 40 varchar in the referenced table which is why we are resizing\n– 1. We must remove request_ids longer than 40 chars. This should never happen as we’ve never issued them longer than this\nDELETE FROM hydra_oauth2_access WHERE LENGTH(request_id) > 40;\nDELETE FROM hydra_oauth2_refresh WHERE LENGTH(request_id) > 40;\nDELETE FROM hydra_oauth2_code WHERE LENGTH(request_id) > 40;\nDELETE FROM hydra_oauth2_oidc WHERE LENGTH(request_id) > 40;\nDELETE FROM hydra_oauth2_pkce WHERE LENGTH(request_id) > 40;\n\n– 2. Next we’re actually resizing\nALTER TABLE hydra_oauth2_access MODIFY request_id varchar(40) NOT NULL DEFAULT ‘’;\nALTER TABLE hydra_oauth2_refresh MODIFY request_id varchar(40) NOT NULL DEFAULT ‘’;\nALTER TABLE hydra_oauth2_code MODIFY request_id varchar(40) NOT NULL DEFAULT ‘’;\nALTER TABLE hydra_oauth2_oidc MODIFY request_id varchar(40) NOT NULL DEFAULT ‘’;\nALTER TABLE hydra_oauth2_pkce MODIFY request_id varchar(40) NOT NULL DEFAULT ‘’;\n\n– In preparation for creating the client_id index and foreign key, we must set it to varchar(255) which is also\n– the length of hydra_client.id\nDELETE FROM hydra_oauth2_access WHERE LENGTH(client_id) > 255;\nDELETE FROM hydra_oauth2_refresh WHERE LENGTH(client_id) > 255;\nDELETE FROM hydra_oauth2_code WHERE LENGTH(client_id) > 255;\nDELETE FROM hydra_oauth2_oidc WHERE LENGTH(client_id) > 255;\nDELETE FROM hydra_oauth2_pkce WHERE LENGTH(client_id) > 255;\nALTER TABLE hydra_oauth2_access MODIFY client_id varchar(255) NOT NULL DEFAULT ‘’;\nALTER TABLE hydra_oauth2_refresh MODIFY client_id varchar(255) NOT NULL DEFAULT ‘’;\nALTER TABLE hydra_oauth2_code MODIFY client_id varchar(255) NOT NULL DEFAULT ‘’;\nALTER TABLE hydra_oauth2_oidc MODIFY client_id varchar(255) NOT NULL DEFAULT ‘’;\nALTER TABLE hydra_oauth2_pkce MODIFY client_id varchar(255) NOT NULL DEFAULT ‘’;\n\n– Now it’s time to create the index for client_id\nCREATE INDEX hydra_oauth2_access_client_id_idx ON hydra_oauth2_access (client_id);\nCREATE INDEX hydra_oauth2_refresh_client_id_idx ON hydra_oauth2_refresh (client_id);\nCREATE INDEX hydra_oauth2_code_client_id_idx ON hydra_oauth2_code (client_id);\nCREATE INDEX hydra_oauth2_oidc_client_id_idx ON hydra_oauth2_oidc (client_id);\nCREATE INDEX hydra_oauth2_pkce_client_id_idx ON hydra_oauth2_pkce (client_id);\n\n– Foreign keys start here\n\n– This creates a foreign key that cascade delete’s if the client_id is removed.\nALTER TABLE hydra_oauth2_access ADD CONSTRAINT hydra_oauth2_access_client_id_fk FOREIGN KEY (client_id) REFERENCES hydra_client(id) ON DELETE CASCADE;\nALTER TABLE hydra_oauth2_refresh ADD CONSTRAINT hydra_oauth2_refresh_client_id_fk FOREIGN KEY (client_id) REFERENCES hydra_client(id) ON DELETE CASCADE;\nALTER TABLE hydra_oauth2_code ADD CONSTRAINT hydra_oauth2_code_client_id_fk FOREIGN KEY (client_id) REFERENCES hydra_client(id) ON DELETE CASCADE;\nALTER TABLE hydra_oauth2_oidc ADD CONSTRAINT hydra_oauth2_oidc_client_id_fk FOREIGN KEY (client_id) REFERENCES hydra_client(id) ON DELETE CASCADE;\nALTER TABLE hydra_oauth2_pkce ADD CONSTRAINT hydra_oauth2_pkce_client_id_fk FOREIGN KEY (client_id) REFERENCES hydra_client(id) ON DELETE CASCADE;\n\n– This creates a foreign key that cascade delete’s if the consent associated with this is removed.\nALTER TABLE hydra_oauth2_access ADD CONSTRAINT hydra_oauth2_access_challenge_id_fk FOREIGN KEY (challenge_id) REFERENCES hydra_oauth2_consent_request_handled(challenge) ON DELETE CASCADE;\nALTER TABLE hydra_oauth2_refresh ADD CONSTRAINT hydra_oauth2_refresh_challenge_id_fk FOREIGN KEY (challenge_id) REFERENCES hydra_oauth2_consent_request_handled(challenge) ON DELETE CASCADE;\nALTER TABLE hydra_oauth2_code ADD CONSTRAINT hydra_oauth2_code_challenge_id_fk FOREIGN KEY (challenge_id) REFERENCES hydra_oauth2_consent_request_handled(challenge) ON DELETE CASCADE;\nALTER TABLE hydra_oauth2_oidc ADD CONSTRAINT hydra_oauth2_oidc_challenge_id_fk FOREIGN KEY (challenge_id) REFERENCES hydra_oauth2_consent_request_handled(challenge) ON DELETE CASCADE;\nALTER TABLE hydra_oauth2_pkce ADD CONSTRAINT hydra_oauth2_pkce_challenge_id_fk FOREIGN KEY (challenge_id) REFERENCES hydra_oauth2_consent_request_handled(challenge) ON DELETE CASCADE;\n\n\ngithub.com/ory/x/popx.NewMigrationBox.func1.1\n\tgithub.com/ory/x@v0.0.368/popx/migration_box.go:80\ngithub.com/ory/x/popx.Migration.Run\n\tgithub.com/ory/x@v0.0.368/popx/migration_info.go:34\ngithub.com/ory/x/popx.(*Migrator).UpTo.func1.2\n\tgithub.com/ory/x@v0.0.368/popx/migrator.go:139\ngithub.com/ory/x/popx.(*Migrator).isolatedTransaction\n\tgithub.com/ory/x@v0.0.368/popx/migrator.go:314\ngithub.com/ory/x/popx.(*Migrator).UpTo.func1\n\tgithub.com/ory/x@v0.0.368/popx/migrator.go:138\ngithub.com/ory/x/popx.(*Migrator).exec\n\tgithub.com/ory/x@v0.0.368/popx/migrator.go:559\ngithub.com/ory/x/popx.(*Migrator).UpTo\n\tgithub.com/ory/x@v0.0.368/popx/migrator.go:92\ngithub.com/ory/x/popx.(*Migrator).Up\n\tgithub.com/ory/x@v0.0.368/popx/migrator.go:80\ngithub.com/ory/hydra/persistence/sql.(*Persister).MigrateUp\n\tgithub.com/ory/hydra@v1.11.10/persistence/sql/persister_migration.go:40\ngithub.com/pydio/cells/v4/common/auth.InitRegistry.func1.1\n\tgithub.com/pydio/cells/v4/common/auth/registry.go:177\ngithub.com/pydio/cells/v4/common/utils/std.Retry\n\tgithub.com/pydio/cells/v4/common/utils/std/func.go:55\ngithub.com/pydio/cells/v4/common/auth.InitRegistry.func1\n\tgithub.com/pydio/cells/v4/common/auth/registry.go:127\nsync.(*Once).doSlow\n\tsync/once.go:74\nsync.(*Once).Do\n\tsync/once.go:65\ngithub.com/pydio/cells/v4/common/auth.InitRegistry\n\tgithub.com/pydio/cells/v4/common/auth/registry.go:118\ngithub.com/pydio/cells/v4/idm/oauth/grpc.init.0.func1.1\n\tgithub.com/pydio/cells/v4/idm/oauth/grpc/plugins.go:95\ngithub.com/pydio/cells/v4/common/service.WithGRPC.func1.1\n\tgithub.com/pydio/cells/v4/common/service/grpc.go:42\ngithub.com/pydio/cells/v4/common/service.(*service).Start\n\tgithub.com/pydio/cells/v4/common/service/service.go:233\ngithub.com/pydio/cells/v4/common/server.(*server).Serve.func2.1\n\tgithub.com/pydio/cells/v4/common/server/server.go:111\ngolang.org/x/sync/errgroup.(*Group).Go.func1\n\tgolang.org/x/sync@v0.1.0/errgroup/errgroup.go:75\nruntime.goexit\n\truntime/asm_amd64.s:1594”}
Blockquote
What version of Cells are you using?
latest
What is the server OS? Database name/version? Browser name or mobile device description (if issue appears client-side)?
My server is a debian 11 based server
What steps have you taken to resolve this issue already?
i tried to deploy new container with new database, and it works, i see all working but is empty. once i dump my database information to the new one, i had the same error than above.
any help ?
BR