missing locale within mysql-shell snap

[DUFOUR Olivier]

There is small issue currently with mysql-shell as it is right now.
It is reproducible on any Ubuntu deployment using this snap since its related to the snap environment itself.
(Tested in my case on Focal and Impish even with the locale en_US.UTF-8 generated on the system)

Whenever running mysql-shell command, it will complain right at the beginning about missing a locale
```
$ mysql-shell.mysqlsh
Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory
MySQL Shell 8.0.23
```

While this is not blocking any command. It also pollutes messages and logs on mysql-innodb-cluster's charm since some hooks and actions use mysql-shell like "rejoin-instance" or simply during a Juju debug-log where it will appear as an error level type message.

It happens on mysql-shell based on version 8.0.23 and also 8.0.27 when I tried to rebuilt the snap from upstream version with the latest matching tag set on snapcraft.yaml file.

When looking at the source code of mysql-shell, this error is triggered from main.cc in the source code, where apparently the locale to use, en_US.UTF-8, is simply hardcoded : [1]
However from the snap environment of mysql-shell, only the locales C, C.UTF-8 are available.

I'm missing some informations about the way MySQL handles really the locale, but to fix this issue it will probably requires either to :
* install the locale en_US in the core18 environement
* make mysql-shell to use the only available locale in Ubuntu Core

For the first option, it doesn't seem simple since I couldn't find a way to add a locale and specify it on Ubuntu Core 18 image.

And just as a test for the second point, I made a quick and somewhat crude patch replacing all the en_US.UTF-8 entries to C.UTF-8 with the help of sed. It makes at least the initial error disappear when using mysql-shell but I haven't tested more since I don't know what is the right approach to fix this.

On minor issues regarding the snap package building itself :
a) There is an issue regarding the repository used for library boost where I get a 403 error from the server whenever I try to pull the archive.
I was able to circumvent this by obtening the archive here : [2]

b) to build the package, I also needed to add gcc-8 and g++-8 as requirements for the build-packages section in the snapcraft.yaml .

[1] : https://github.com/mysql/mysql-shell/blob/master/src/mysqlsh/main.cc#L628

[2] : https://boostorg.jfrog.io/artifactory/main/release/1.73.0/source/boost_1_73_0.tar.gz

[DUFOUR Olivier (odufourc)]

I don't know how to link it properly to this ticket but I've made a fork of the repository with my patch implemented here and the snapcraft.yaml updated accordingly for testing (although it is probably not implemented the best way) :
https://code.launchpad.net/~odufourc/snap-mysql-shell/+git/snap-mysql-shell

[Mark Stine (mnstine)]

https://bugs.launchpad.net/snap-openstack/+bug/2030317

Having the same issues trying to install openstack.

unit-keystone-mysql-router-0: 18:36:08 ERROR juju.worker.uniter.operation hook "mysql-router-pebble-ready" (via hook dispatching script: dispatch) failed: exit status 1
unit-keystone-mysql-router-0: 18:36:08 ERROR juju.worker.uniter pebble poll failed for container "mysql-router": failed to send pebble-ready event: hook failed
unit-nova-cell-mysql-router-0: 18:36:09 ERROR unit.nova-cell-mysql-router/0.juju-log Failed to run logged_commands=["shell.connect('relation-65:***@mysql-primary.openstack.svc.cluster.local:3306')", 'session.run_sql("CREATE DATABASE IF NOT EXISTS `nova_cell0`")', 'session.run_sql("CREATE USER `relation-65-35` IDENTIFIED BY \'qpMysaXicFPARCTgsZh6J150\' ATTRIBUTE \'{\\"created_by_user\\": \\"relation-65\\"}\'")', 'session.run_sql("GRANT ALL PRIVILEGES ON `nova_cell0`.* TO `relation-65-35`")']
stderr:
Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory
Traceback (most recent call last):
  File "<string>", line 3, in <module>
mysqlsh.DBError: MySQL Error (1396): ClassicSession.run_sql: Operation CREATE USER failed for 'relation-65-35'@'%'

Traceback (most recent call last):
  File "/var/lib/juju/agents/unit-nova-cell-mysql-router-0/charm/src/rock.py", line 119, in _run_command
    output, _ = process.wait_output()
  File "/var/lib/juju/agents/unit-nova-cell-mysql-router-0/charm/venv/ops/pebble.py", line 1359, in wait_output
    raise ExecError[AnyStr](self._command, exit_code, out_value, err_value)
ops.pebble.ExecError: non-zero exit code 1 executing ['mysqlsh', '--no-wizard', '--python', '--file', '/tmp/script.py'], stdout='', 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 3, in <module>\nmysqlsh.DBError: MySQL Error (1396): ClassicSession.run_sql: Operation CREATE USER failed for \'relation-65-35\'@\'%\'\n'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/var/lib/juju/agents/unit-nova-cell-mysql-router-0/charm/src/mysql_shell.py", line 58, in _run_commands
    output = self._container.run_mysql_shell(
  File "/var/lib/juju/agents/unit-nova-cell-mysql-router-0/charm/src/container.py", line 130, in run_mysql_shell
    return self._run_command(args, timeout=timeout)
  File "/var/lib/juju/agents/unit-nova-cell-mysql-router-0/charm/src/rock.py", line 121, in _run_command
    raise container.CalledProcessError(
container.CalledProcessError: Command '['mysqlsh', '--no-wizard', '--python', '--file', '/tmp/script.py']' returned non-zero exit status 1.

[John Quinn (jp-quinn)]

Contrary to what was stated in the original description, this *is* a blocking issue. This command I am trying to run fails:

mysqlsh -- util checkForServerUpgrade root:<pwd intentionally removed>@localhost:3306 --target-version=8.0.23 --output-format=JSON

Error:
Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory

[Launchpad Janitor (janitor)]

Status changed to 'Confirmed' because the bug affects multiple users.