mysql scaled unit fail to configure, error setting read only variable

[Guillaume Boutry]

MySQL's unit fail to configure. Other units are configure fine.

2024-03-28T11:06:16.508Z [container-agent] 2024-03-28 11:06:16 INFO juju.worker.uniter.operation runhook.go:186 ran "mysql-pebble-ready" hook (via hook dispatching script: dispatch)
2024-03-28T11:06:18.850Z [pebble] Check "readiness" failure 3 (threshold 3): received non-20x status code 418
2024-03-28T11:06:18.850Z [pebble] Check "readiness" failure threshold 3 hit, triggering action
2024-03-28T11:06:21.895Z [container-agent] 2024-03-28 11:06:21 INFO juju-log Configuration does not requires restart
2024-03-28T11:06:22.122Z [container-agent] 2024-03-28 11:06:22 ERROR juju-log Failed to set variable report_host to `keystone-mysql-1.keystone-mysql-endpoints.openstack.svc.cluster.local`
2024-03-28T11:06:22.122Z [container-agent] Traceback (most recent call last):
2024-03-28T11:06:22.122Z [container-agent] File "/var/lib/juju/agents/unit-keystone-mysql-1/charm/src/mysql_k8s_helpers.py", line 666, in _run_mysqlsh_script
2024-03-28T11:06:22.122Z [container-agent] stdout, _ = process.wait_output()
2024-03-28T11:06:22.122Z [container-agent] File "/var/lib/juju/agents/unit-keystone-mysql-1/charm/venv/ops/pebble.py", line 1540, in wait_output
2024-03-28T11:06:22.122Z [container-agent] raise ExecError[AnyStr](self._command, exit_code, out_value, err_value)
2024-03-28T11:06:22.122Z [container-agent] ops.pebble.ExecError: non-zero exit code 1 executing ['/usr/bin/mysqlsh', '--no-wizard', '--python', '--verbose=1', '-f', '/tmp/script.py', ';', 'rm', '/tmp/script.py'], stdout='', stderr='Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory\nverbose: 2024-03-28T11:06:21Z: Loading startup files...\nverbose: 2024-03-28T11:06:21Z: Loading plugins...\nverbose: 2024-03-28T11:06:22Z: Connecting to MySQL at: <email address hidden>\nverbose: 2024-03-28T11:06:22Z: Shell.connect: tid=13: CONNECTED: keystone-mysql-1.keystone-mysql-endpoints.openstack.svc.cluster.local\nverbose: 2024-03-28T11:06:22Z: Session.run_sql: tid=13: MySQL Error 1238: Variable \'report_host\' is a read only variable, SQL: SET GLOBAL report_host=`keystone-mysql-1.keystone-mysql-endpoints.openstack.svc.cluster.local`\nTraceback (most recent call last):\n File "<string>", line 2, in <module>\nmysqlsh.DBError: MySQL Error (1238): Session.run_sql: Variable \'report_host\' is a read only variable\n'
2024-03-28T11:06:22.122Z [container-agent]
2024-03-28T11:06:22.122Z [container-agent] During handling of the above exception, another exception occurred:
2024-03-28T11:06:22.122Z [container-agent]
2024-03-28T11:06:22.122Z [container-agent] Traceback (most recent call last):
2024-03-28T11:06:22.122Z [container-agent] File "/var/lib/juju/agents/unit-keystone-mysql-1/charm/lib/charms/mysql/v0/mysql.py", line 1096, in set_dynamic_variable
2024-03-28T11:06:22.122Z [container-agent] self._run_mysqlsh_script("\n".join(set_var_command))
2024-03-28T11:06:22.122Z [container-agent] File "/var/lib/juju/agents/unit-keystone-mysql-1/charm/src/mysql_k8s_helpers.py", line 669, in _run_mysqlsh_script
2024-03-28T11:06:22.122Z [container-agent] raise MySQLClientError(e.stderr)
2024-03-28T11:06:22.122Z [container-agent] charms.mysql.v0.mysql.MySQLClientError: Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory
2024-03-28T11:06:22.122Z [container-agent] verbose: 2024-03-28T11:06:21Z: Loading startup files...
2024-03-28T11:06:22.122Z [container-agent] verbose: 2024-03-28T11:06:21Z: Loading plugins...
2024-03-28T11:06:22.122Z [container-agent] verbose: 2024-03-28T11:06:22Z: Connecting to MySQL at: <email address hidden>
2024-03-28T11:06:22.122Z [container-agent] verbose: 2024-03-28T11:06:22Z: Shell.connect: tid=13: CONNECTED: keystone-mysql-1.keystone-mysql-endpoints.openstack.svc.cluster.local
2024-03-28T11:06:22.122Z [container-agent] verbose: 2024-03-28T11:06:22Z: Session.run_sql: tid=13: MySQL Error 1238: Variable 'report_host' is a read only variable, SQL: SET GLOBAL report_host=`keystone-mysql-1.keystone-mysql-endpoints.openstack.svc.cluster.local`
2024-03-28T11:06:22.122Z [container-agent] Traceback (most recent call last):
2024-03-28T11:06:22.122Z [container-agent] File "<string>", line 2, in <module>
2024-03-28T11:06:22.122Z [container-agent] mysqlsh.DBError: MySQL Error (1238): Session.run_sql: Variable 'report_host' is a read only variable
2024-03-28T11:06:22.122Z [container-agent]
2024-03-28T11:06:22.126Z [container-agent] 2024-03-28 11:06:22 ERROR juju-log Uncaught exception while in charm code:
2024-03-28T11:06:22.126Z [container-agent] Traceback (most recent call last):
2024-03-28T11:06:22.126Z [container-agent] File "/var/lib/juju/agents/unit-keystone-mysql-1/charm/src/mysql_k8s_helpers.py", line 666, in _run_mysqlsh_script
2024-03-28T11:06:22.126Z [container-agent] stdout, _ = process.wait_output()
2024-03-28T11:06:22.126Z [container-agent] File "/var/lib/juju/agents/unit-keystone-mysql-1/charm/venv/ops/pebble.py", line 1540, in wait_output
2024-03-28T11:06:22.126Z [container-agent] raise ExecError[AnyStr](self._command, exit_code, out_value, err_value)
2024-03-28T11:06:22.126Z [container-agent] ops.pebble.ExecError: non-zero exit code 1 executing ['/usr/bin/mysqlsh', '--no-wizard', '--python', '--verbose=1', '-f', '/tmp/script.py', ';', 'rm', '/tmp/script.py'], stdout='', stderr='Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory\nverbose: 2024-03-28T11:06:21Z: Loading startup files...\nverbose: 2024-03-28T11:06:21Z: Loading plugins...\nverbose: 2024-03-28T11:06:22Z: Connecting to MySQL at: <email address hidden>\nverbose: 2024-03-28T11:06:22Z: Shell.connect: tid=13: CONNECTED: keystone-mysql-1.keystone-mysql-endpoints.openstack.svc.cluster.local\nverbose: 2024-03-28T11:06:22Z: Session.run_sql: tid=13: MySQL Error 1238: Variable \'report_host\' is a read only variable, SQL: SET GLOBAL report_host=`keystone-mysql-1.keystone-mysql-endpoints.openstack.svc.cluster.local`\nTraceback (most recent call last):\n File "<string>", line 2, in <module>\nmysqlsh.DBError: MySQL Error (1238): Session.run_sql: Variable \'report_host\' is a read only variable\n'
2024-03-28T11:06:22.126Z [container-agent]
2024-03-28T11:06:22.126Z [container-agent] During handling of the above exception, another exception occurred:
2024-03-28T11:06:22.126Z [container-agent]
2024-03-28T11:06:22.126Z [container-agent] Traceback (most recent call last):
2024-03-28T11:06:22.126Z [container-agent] File "/var/lib/juju/agents/unit-keystone-mysql-1/charm/lib/charms/mysql/v0/mysql.py", line 1096, in set_dynamic_variable
2024-03-28T11:06:22.126Z [container-agent] self._run_mysqlsh_script("\n".join(set_var_command))
2024-03-28T11:06:22.126Z [container-agent] File "/var/lib/juju/agents/unit-keystone-mysql-1/charm/src/mysql_k8s_helpers.py", line 669, in _run_mysqlsh_script
2024-03-28T11:06:22.126Z [container-agent] raise MySQLClientError(e.stderr)
2024-03-28T11:06:22.126Z [container-agent] charms.mysql.v0.mysql.MySQLClientError: Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory

[Alex Lutay (taurus)]

For the history, Data team tracks it here: https://github.com/canonical/mysql-k8s-operator/issues/400

Q: do we have steps to reproduce to make sure fix helps. Otherwise we are shooting blindly. Tnx!

[Alex Lutay (taurus)]

Hi Team, do we have steps to reproduce or at least some ideas here? Thank you!

[Nobuto Murata (nobuto)]

I can offer the full debug log of the deployment at least. I'm following the tutorial and this sometimes happens (not always)
https://microstack.run/docs/multi-node-maas

unit-neutron-mysql-2: 12:56:03 DEBUG unit.neutron-mysql/2.juju-log Setting report_host to `neutron-mysql-2.neutron-mysql-endpoints.openstack.svc.cluster.local` on neutron-mysql-2.ne
utron-mysql-endpoints.openstack.svc.cluster.local
unit-neutron-mysql-2: 12:56:03 ERROR unit.neutron-mysql/2.juju-log Failed to set variable report_host to `neutron-mysql-2.neutron-mysql-endpoints.openstack.svc.cluster.local`
Traceback (most recent call last):
  File "/var/lib/juju/agents/unit-neutron-mysql-2/charm/src/mysql_k8s_helpers.py", line 666, in _run_mysqlsh_script
    stdout, _ = process.wait_output()
  File "/var/lib/juju/agents/unit-neutron-mysql-2/charm/venv/ops/pebble.py", line 1540, 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', '--no-wizard', '--python', '--verbose=1', '-f', '/tmp/script.py', ';', 'rm', '/tmp/script.py'], stdout='',
stderr='Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory\nverbose: 2024-05-28T12:56:03Z: Loading startup files...\nverbose: 2024-05-28T12:56:03Z: Loading plugins..
.\nverbose: 2024-05-28T12:56:03Z: Connecting to MySQL at: <email address hidden>\nverbose: 2024-05-28T12:56:03Z: Shell.conn
ect: tid=61: CONNECTED: neutron-mysql-2.neutron-mysql-endpoints.openstack.svc.cluster.local\nverbose: 2024-05-28T12:56:03Z: Session.run_sql: tid=61: MySQL Error 1238: Variable \'rep
ort_host\' is a read only variable, SQL: SET GLOBAL report_host=`neutron-mysql-2.neutron-mysql-endpoints.openstack.svc.cluster.local`\nTraceback (most recent call last):\n File "<s
tring>", line 2, in <module>\nmysqlsh.DBError: MySQL Error (1238): Session.run_sql: Variable \'report_host\' is a read only variable\n'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/var/lib/juju/agents/unit-neutron-mysql-2/charm/lib/charms/mysql/v0/mysql.py", line 1096, in set_dynamic_variable
    self._run_mysqlsh_script("\n".join(set_var_command))
  File "/var/lib/juju/agents/unit-neutron-mysql-2/charm/src/mysql_k8s_helpers.py", line 669, in _run_mysqlsh_script
    raise MySQLClientError(e.stderr)
charms.mysql.v0.mysql.MySQLClientError: Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory
verbose: 2024-05-28T12:56:03Z: Loading startup files...
verbose: 2024-05-28T12:56:03Z: Loading plugins...
verbose: 2024-05-28T12:56:03Z: Connecting to MySQL at: <email address hidden>
verbose: 2024-05-28T12:56:03Z: Shell.connect: tid=61: CONNECTED: neutron-mysql-2.neutron-mysql-endpoints.openstack.svc.cluster.local
verbose: 2024-05-28T12:56:03Z: Session.run_sql: tid=61: MySQL Error 1238: Variable 'report_host' is a read only variable, SQL: SET GLOBAL report_host=`neutron-mysql-2.neutron-mysql-
endpoints.openstack.svc.cluster.local`
Traceback (most recent call last):
  File "<string>", line 2, in <mo...

[Paulo Machado (paulomachado)]

Fix merged on mysql-k8s: https://github.com/canonical/mysql-k8s-operator/issues/400