Mysql server refresh failed to create a database role required by newer version of the router

[gustavosr98]

Steps to reproduce

Deploy mysql (8.0/stable | rev 255) and mysql-router (8.0/stable | rev 748)
Refresh mysql (8.0/stable | rev 346) and mysql-router (8.0/stable | rev 871)

WARN: Do not directly run juju refresh <app> but rather follow pre-checks first juju run mysql-k8s/leader pre-upgrade-check . https://canonical-charmed-mysql.readthedocs-hosted.com/how-to/refresh/single-cluster/refresh-single-cluster/

You will hit this bug canonical/mysql-router-operators#100

Then to find a workaround for it you will hit this bug (current report)

Remove mysql-router

juju remove-application mysql-router

Deploy mysql-router latest version (8.0/stable | rev 871)
Relate mysql-router to mysql

Expected behavior

To be in active state properly related to mysql

Actual behavior

Mysql-router stuck waiting for "Waiting for mysql app on backend-database endpoint"
The k8s endpoint actually exists and the juju application is trusted

# juju status --relations | grep placement-mysql-router
placement-mysql-router      8.0.44                   waiting      3  mysql-router-k8s          8.0/stable     871  10.152.183.54   no       Waiting for mysql app on backend-database endpoint
placement-mysql-router/0       waiting   idle   10.1.1.25
placement-mysql-router/1       waiting   idle   10.1.2.60
placement-mysql-router/2*      waiting   idle   10.1.0.216
mysql:database                                        placement-mysql-router:backend-database        mysql_client             regular
placement-mysql-router:cos                            placement-mysql-router:cos                     cos                      peer
placement-mysql-router:database                       placement:database                             mysql_client             regular
placement-mysql-router:mysql-router-peers             placement-mysql-router:mysql-router-peers      mysql_router_peers       peer
placement-mysql-router:refresh-v-three                placement-mysql-router:refresh-v-three         refresh                  peer
placement-mysql-router:tls                            placement-mysql-router:tls                     tls                      peer

Versions

Juju agent: 3.6.12
Cloud type: Kubernetes

Log output

2026-03-09T22:16:43.244Z [container-agent] 2026-03-09 22:16:43 ERROR juju-log database:257: Failed to execute mysql-shell command
2026-03-09T22:16:43.244Z [container-agent] Traceback (most recent call last):
2026-03-09T22:16:43.244Z [container-agent]   File "/var/lib/juju/agents/unit-mysql-1/charm/src/mysql_k8s_helpers.py", line 719, in _run_mysqlsh_script
2026-03-09T22:16:43.244Z [container-agent]     stdout, _ = process.wait_output()
2026-03-09T22:16:43.244Z [container-agent]   File "/var/lib/juju/agents/unit-mysql-1/charm/venv/lib/python3.10/site-packages/ops/pebble.py", line 1771, in wait_output
2026-03-09T22:16:43.244Z [container-agent]     raise ExecError[AnyStr](self._command, exit_code, out_value, err_value)
2026-03-09T22:16:43.244Z [container-agent] ops.pebble.ExecError: non-zero exit code 1 executing ['/usr/bin/mysqlsh', '--passwords-from-stdin', '--uri=serverconfig@mysql-1.mysql-endpoints.openstack.svc.cluster.local.:33062', '--python', '--verbose=0', '-c', 'shell.options.set(\'useWizards\', False)\nprint(\'###\')\nshell.connect_to_primary()\nsession.run_sql("CREATE USER `relation-257_9265fb6a20e24`@`%` IDENTIFIED BY \'*****\' ATTRIBUTE \'{}\';")\nsession.run_sql("GRANT mysqlrouter TO `relation-257_9265fb6a20e24`@`%`;")'], stdout="\x1b[1mPlease provide the password for 'serverconfig@mysql-1.mysql-endpoints.openstack.svc.cluster.local.:33062': \x1b[0m###\nReconnecting to the PRIMARY instance of an InnoDB cluster (mysql-2.mysql-endpoints.openstack.svc.cluster.local.:3306)...\n", stderr='Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory\nTraceback (most recent call last):\n  File "<string>", line 5, in <module>\nmysqlsh.DBError: MySQL Error (3523): ClassicSession.run_sql: Unknown authorization ID `mysqlrouter`@`%`\n'
2026-03-09T22:16:43.258Z [container-agent] 2026-03-09 22:16:43 ERROR juju-log database:257: Failed to create application scoped user relation-257_9265fb6a20e24@%
2026-03-09T22:16:43.271Z [container-agent] 2026-03-09 22:16:43 ERROR juju-log database:257: Failed to set up database relation
2026-03-09T22:16:43.271Z [container-agent] Traceback (most recent call last):
2026-03-09T22:16:43.271Z [container-agent]   File "/var/lib/juju/agents/unit-mysql-1/charm/lib/charms/mysql/v0/mysql.py", line 1670, in create_scoped_user
2026-03-09T22:16:43.271Z [container-agent]     self._run_mysqlsh_script(
2026-03-09T22:16:43.271Z [container-agent]   File "/var/lib/juju/agents/unit-mysql-1/charm/lib/charms/tempo_coordinator_k8s/v0/charm_tracing.py", line 1065, in wrapped_function
2026-03-09T22:16:43.271Z [container-agent]     return callable(*args, **kwargs)  # type: ignore
2026-03-09T22:16:43.271Z [container-agent]   File "/var/lib/juju/agents/unit-mysql-1/charm/src/mysql_k8s_helpers.py", line 727, in _run_mysqlsh_script
2026-03-09T22:16:43.271Z [container-agent]     raise MySQLClientError from None
2026-03-09T22:16:43.271Z [container-agent] charms.mysql.v0.mysql.MySQLClientError
2026-03-09T22:16:43.271Z [container-agent]
2026-03-09T22:16:43.271Z [container-agent] The above exception was the direct cause of the following exception:
2026-03-09T22:16:43.271Z [container-agent]
2026-03-09T22:16:43.271Z [container-agent] Traceback (most recent call last):
2026-03-09T22:16:43.271Z [container-agent]   File "/var/lib/juju/agents/unit-mysql-1/charm/src/relations/mysql_provider.py", line 144, in _on_database_requested
2026-03-09T22:16:43.271Z [container-agent]     self.charm._mysql.create_scoped_user(
2026-03-09T22:16:43.271Z [container-agent]   File "/var/lib/juju/agents/unit-mysql-1/charm/lib/charms/tempo_coordinator_k8s/v0/charm_tracing.py", line 1065, in wrapped_function
2026-03-09T22:16:43.271Z [container-agent]     return callable(*args, **kwargs)  # type: ignore
2026-03-09T22:16:43.271Z [container-agent]   File "/var/lib/juju/agents/unit-mysql-1/charm/lib/charms/mysql/v0/mysql.py", line 1680, in create_scoped_user
2026-03-09T22:16:43.271Z [container-agent]     raise MySQLCreateApplicationScopedUserError(e.message) from e
2026-03-09T22:16:43.271Z [container-agent] charms.mysql.v0.mysql.MySQLCreateApplicationScopedUserError
2026-03-09T22:16:44.810Z [container-agent] 2026-03-09 22:16:44 DEBUG juju-log database:257: ops 2.15.0 up and running.

Additional context

Related to sunbeam cluster refresh
https://bugs.launchpad.net/snap-openstack/+bug/2143746

On recent sunbeam stable release
https://discourse.charmhub.io/t/sunbeam-release-2024-1-stable-rev-922/19941

[gustavosr98]

To workaround this

(1) Make a backup of your mysql data.

https://canonical-charmed-mysql.readthedocs-hosted.com/how-to/back-up-and-restore/create-a-backup/

If you do not have s3-integrator configured, you can do the following

juju run mysql/leader get-password

juju ssh --container mysql mysql/leader
bash
mysqldump --all-databases --triggers --routines --events --single-transaction -p > 2026_03_09__20_33.sql
exit

juju scp --container mysql mysql/1:2026_03_09__20_33.sql 2026_03_09__20_33.sql

(2) Make a copy of mysql-router application config and relations, and then remove

# juju exec -u placement-mysql-router/leader 'config-get --all' | tee placement-mysql-router.config
expose-external: "false"
loadbalancer-extra-annotations: '{}'

NOTE: cinder-volume-mysql-router has a Juju cross model relation to openstack-machines model with cinder-volume. You will not see the relation from the openstack model side but from the openstack-machines model side

juju switch openstack-machines
juju remove-relation cinder-volume-mysql-router:database cinder-volume-noha:database
juju remove-relation cinder-volume-mysql-router:database cinder-volume:database
juju remove-saas cinder-volume-mysql-router

juju switch openstack
juju remove-offer cinder-volume-mysql-router

juju remove-application cinder-volume-mysql-router

juju status --relations | grep placement-mysql-router | grep regular | tee placement-mysql-router.relations
placement-mysql-router      8.0.44                   waiting          3  mysql-router-k8s          8.0/stable     871  10.152.183.121  no       installing agent
placement-mysql-router/0*      error        idle   10.1.0.5           hook failed: "update-status"
placement-mysql-router/1       error        idle   10.1.2.252         hook failed: "update-status"
placement-mysql-router/2       error        idle   10.1.1.173         hook failed: "refresh-v-three-relation-created"
mysql:database                                        placement-mysql-router:backend-database        mysql_client             regular
placement-mysql-router:database                       placement:database                             mysql_client             regular

(3) Db surgery

Get mysql creds

juju run mysql/0 get-password username=serverconfig

Check which is the primary mysql (at the mysql level, not juju)

juju ssh --container mysql mysql/0 mysqlsh --quiet-start=2 --py serverconfig:<PASSWORD>@localhost

dba.get_cluster().status()

Update date from primary (eg. in case it is mysql/2)

juju ssh --container mysql mysql/2 mysqlsh --quiet-start=2 --py serverconfig:<PASSWORD>@localhost
\sql

SELECT user AS role_name FROM mysql.user WHERE account_locked='Y' AND password_expired='Y' AND authentication_string='';
# If output is empty you have hit this issue
# let's fix it

CREATE ROLE mysqlrouter;GRANT CREATE ON *.* TO mysqlrouter;GRANT CREATE USER ON *.* TO mysqlrouter;GRANT ALL ON *.* TO mysqlrouter WITH GRANT OPTION;

(4) Deploy mysql-router application (8.0/stable channel) again, trust and re-add relations

juju deploy mysql-router-k8s placement-mysql-router --channel 8.0/stable --num-units 3
juju trust placement-mysql-router --scope=cluster
juju relate [..]

For cinder-volume-mysql-router

juju switch openstack
juju offer cinder-volume-mysql-router:database

juju switch openstack-machines
juju consume sunbeam/openstack.cinder-volume-mysql-router
juju relate cinder-volume cinder-volume-mysql-router

juju relate rabbitmq cinder-volume
juju relate keystone-credentials cinder-volume

Now it should all be active and properly connected

[gustavosr98]

Thanks @paulomach ++

[syncronize-issues-to-jira]

Thank you for reporting your feedback to us!

The internal ticket has been created: https://warthogs.atlassian.net/browse/DPE-9609.

This message was autogenerated

[astrojuanlu]

Thank you @gustavosr98 for opening this issue. However, I'm not able to reproduce it.

Here's what I tried:

From a broken state

3x router + 1x server, refresh the router to reach canonical/mysql-router-operators#100, then try juju remove-application mysql-router-k8s to continue the other steps.

💥 The router was in error state before, with the stop hook failing in a loop, and the app couldn't actually be removed.

Result: could not reproduce.

From a clean state

3x router + 1x server, bring the system to the state described in canonical/mysql-router-operators#100 right before doing the refresh, then

juju remove-application mysql-router-k8s
juju deploy mysql-router-k8s --revision 871 ...
juju integrate mysql-k8s mysql-router-k8s

✅ The system ends in a good state again.

Result: could not reproduce.

---

It might be the case that, after canonical/mysql-router-operators#100 is solved (as canonical/mysql-router-operators#106 seems to do), this issue would no longer hold.

It's also entirely possible that I'm missing something. We would need more context or a more concrete reproducer to be able to help further.

[astrojuanlu]

After talking with @paulomach , we came up with a simpler reproducer of the same root cause:

juju deploy mysql-k8s --trust --model=testing --revision 255 --channel 8.0/stable -n 1
[observe no predefined roles]
juju run mysql-k8s/0 pre-upgrade-check
juju refresh mysql-k8s
[observe still no predefined roles]
juju deploy mysql-router-k8s --trust --model=testing
juju deploy mysql-test-app --config database_name=test
juju integrate mysql-router-k8s mysql-test-app:database
juju integrate mysql-k8s mysql-router-k8s
[see error]

Full traceback:

unit-mysql-k8s-0: 18:58:47 INFO unit.mysql-k8s/0.juju-log database:15: Creating application scoped user relation-15_9d35c4cdcbe941@%
unit-mysql-k8s-0: 18:58:47 ERROR unit.mysql-k8s/0.juju-log database:15: Failed to execute mysql-shell command
Traceback (most recent call last):                                                         
  File "/var/lib/juju/agents/unit-mysql-k8s-0/charm/src/mysql_k8s_helpers.py", line 719, in _run_mysqlsh_script
    stdout, _ = process.wait_output()                                                      
  File "/var/lib/juju/agents/unit-mysql-k8s-0/charm/venv/lib/python3.10/site-packages/ops/pebble.py", line 1771, in wait_output
    raise ExecError[AnyStr](self._command, exit_code, out_value, err_value)
ops.pebble.ExecError: non-zero exit code 1 executing ['/usr/bin/mysqlsh', '--passwords-from-stdin', '--uri=serverconfig@mysql-k8s-0.mysql-k8s-endpoints.testing.svc.cluster.local.:33062', '--python', '--verbose=0', '-c', 'shell.options.set(\'useWizards\', False)\nprint(\'###\')\nshell.connect_to_primary()\nsession.run_sql("CREATE USER `relation-15_9d35c4cdcbe941`@`%` IDENTIFIED BY \'*****\' ATTRIBUTE \'{}\';")\nsession.run_sql("GRANT mysqlrouter TO `relation-15_9d35c4cdcbe941`@`%`;")'], stdout="\x1b[1mPlease provide the password for 'serverconfig@mysql-k8s-0.mysql-k8s-endpoints.testing.svc.cluster.local.:33062': \x1b[0m###\n", stderr='Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory\n\x1b[36mNOTE: \x1b[0mAlready connected to a PRIMARY.\nTraceback (most recent call last):\n  File "<string>", line 5, in <module>\nmysqlsh.DBError: MySQL Error (3523): ClassicSession.run_sql: Unknown authorization ID `mysqlrouter`@`%`\n'
unit-mysql-k8s-0: 18:58:47 ERROR unit.mysql-k8s/0.juju-log database:15: Failed to create application scoped user relation-15_9d35c4cdcbe941@%
unit-mysql-k8s-0: 18:58:47 ERROR unit.mysql-k8s/0.juju-log database:15: Failed to set up database relation
Traceback (most recent call last):                                                         
  File "/var/lib/juju/agents/unit-mysql-k8s-0/charm/lib/charms/mysql/v0/mysql.py", line 1670, in create_scoped_user
    self._run_mysqlsh_script(                                                              
  File "/var/lib/juju/agents/unit-mysql-k8s-0/charm/lib/charms/tempo_coordinator_k8s/v0/charm_tracing.py", line 1065, in wrapped_function
    return callable(*args, **kwargs)  # type: ignore
  File "/var/lib/juju/agents/unit-mysql-k8s-0/charm/src/mysql_k8s_helpers.py", line 727, in _run_mysqlsh_script
    raise MySQLClientError from None                                                       
charms.mysql.v0.mysql.MySQLClientError                                                     

The above exception was the direct cause of the following exception:

Traceback (most recent call last):                                                         
  File "/var/lib/juju/agents/unit-mysql-k8s-0/charm/src/relations/mysql_provider.py", line 144, in _on_database_requested
    self.charm._mysql.create_scoped_user(                                                  
  File "/var/lib/juju/agents/unit-mysql-k8s-0/charm/lib/charms/tempo_coordinator_k8s/v0/charm_tracing.py", line 1065, in wrapped_function
    return callable(*args, **kwargs)  # type: ignore
  File "/var/lib/juju/agents/unit-mysql-k8s-0/charm/lib/charms/mysql/v0/mysql.py", line 1680, in create_scoped_user
    raise MySQLCreateApplicationScopedUserError(e.message) from e
charms.mysql.v0.mysql.MySQLCreateApplicationScopedUserError

[astrojuanlu]

This will be addressed by #163. Still, the investigation uncovered more potential issues with router + refresh, we will tackle those in due time.

[astrojuanlu]

A new version was released to 8.0/edge addressing this issue @gustavosr98 , please give it a try.