pymysql.err.OperationalError: caching sha2: Unknown packet for public key: b'-'

[Trent Lloyd]

After upgrading and restarting both mysql-router (does not happen automatically) and mysql server (does happen automatically) to the new 8.0.32 version clients using the pymysql library and connecting via mysql-router will fail to connect with the following error:
pymysql.err.OperationalError: caching sha2: Unknown packet for public key: b'-'

8.0.32 was published into -security today (Jan 24th/25th). As a result both mysql-router and mysql-server get upgraded by unattended-upgrades.

This does not happen on 8.0.31

This affects charmed openstack deployments on focal+jammy, database authentication doesn't work out of the box on new installs and fails on upgraded installs.

[Workaround]

Downgrade mysql-router to 8.0.31.

Note: You only need to downgrade the mysql-router package only - it does not cross-depend with any other mysql package. You cannot (and should not) downgrade the server packages as the 8.0.31 server cannot open data files upgraded to 8.0.32.

Focal (20.04) amd64: https://launchpad.net/ubuntu/+source/mysql-8.0/8.0.31-0ubuntu0.20.04.2/+build/24823714/+files/mysql-router_8.0.31-0ubuntu0.20.04.2_amd64.deb

Jammy (22.04) amd64: https://launchpad.net/~ubuntu-security-proposed/+archive/ubuntu/ppa/+build/24557440/+files/mysql-router_8.0.31-0ubuntu0.22.04.1_amd64.deb

Download and install the package, then restart the mysql-router service

apt install ./FILENAME.deb
systemctl restart \*-mysql-router

Alternatively:

Install the MySQL CLI and then manually connect to the server with each user/password combination in the environment. This will cache the password in the server's memory which triggers a different code path that avoids the issue.

If the MySQL server fails over or is restarted the cache is lost and clients will fail to connect again.

[Traceback]
Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 3250, in _wrap_pool_connect
    return fn()
  File "/usr/lib/python3/dist-packages/sqlalchemy/pool/base.py", line 310, in connect
    return _ConnectionFairy._checkout(self)
  File "/usr/lib/python3/dist-packages/sqlalchemy/pool/base.py", line 868, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/lib/python3/dist-packages/sqlalchemy/pool/base.py", line 476, in checkout
    rec = pool._do_get()
  File "/usr/lib/python3/dist-packages/sqlalchemy/pool/impl.py", line 145, in _do_get
    with util.safe_reraise():
  File "/usr/lib/python3/dist-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/usr/lib/python3/dist-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/usr/lib/python3/dist-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
    return self._create_connection()
  File "/usr/lib/python3/dist-packages/sqlalchemy/pool/base.py", line 256, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/lib/python3/dist-packages/sqlalchemy/pool/base.py", line 371, in __init__
    self.__connect()
  File "/usr/lib/python3/dist-packages/sqlalchemy/pool/base.py", line 665, in __connect
    with util.safe_reraise():
  File "/usr/lib/python3/dist-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/usr/lib/python3/dist-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/usr/lib/python3/dist-packages/sqlalchemy/pool/base.py", line 661, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/create.py", line 590, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/default.py", line 597, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 353, in __init__
    self.connect()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 633, in connect
    self._request_authentication()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 932, in _request_authentication
    auth_packet = _auth.caching_sha2_password_auth(self, auth_packet)
  File "/usr/lib/python3/dist-packages/pymysql/_auth.py", line 257, in caching_sha2_password_auth
    raise OperationalError(
pymysql.err.OperationalError: caching sha2: Unknown packet for public key: b'-'

[Launchpad Janitor (janitor)]

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

[Trent Lloyd (lathiat)]

Some findings so far
(1) "strangely a first workaround for units such as keystone or neutron-api is to install the mysql client and through the CLI to connect at least once to the database with the logins generated by Juju. Strangely after that, the units are able to initialise their database."

(2) "on Jammy, deploying mysql-router to the original version (8.0.28-0ubuntu4) does fix the issue with pymsql. however this cannot be used as a workaround as the newer mysqlrouter.conf doesn't work with the old version. it was just a quick test that indicates maybe mysql-router is the problematic component". However I tested mysql-router 8.0.32 working fine in at least one case. so it's not the entire story.

(3) unattended-upgrades (if enabled) will restart mysql (server) automaically - different cluster members may or may not restart/upgrade at the same time.

juju run --application mysql/leader cluster-status # will show the current versions/if they are up.

(4) unattended-upgrades (if enabled) will not restart mysql-router in a charmed deployment as a custom systemd unit is created that is not restarted automatically by the postinst script, but will upgrade to the new version if restarted (e.g. systemctl restart keystone-mysql-router)

[Trent Lloyd (lathiat)]

Progress update

The upgraded environments are also broken. However the way the caching_sha2_password_auth works (as documented at https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html) is the first time you connect it does a public-private encryption exchange but then the server cache's a hash you can re-authenticate with later using the same password.

On the working upgraded environment I hadn't restarted the MySQL server since upgrading mysql-router to 8.0.32. So it was still cached. In the cached case the auth code never calls into _auth.caching_sha2_password_auth and doens't hit the problematic code. But after a server restart it also fails.

It seems connecting with pymysql2 directly to mysql-server works; it only fails when going (for the first time; with no server cache) through mysql-router.

This leads to a workaround if someone hits this for now.. if you use the mysql CLI to manually login to the server with the specific user/password for each unit.. it will temporarily work.

Still looking into the cause/possible fix for the initial auth failure.

[Trent Lloyd (lathiat)]

I have narrowed this down to.. when you connect via mysql-router only, on 127.0.0.1 (TCP)

The code at /usr/lib/python3/dist-packages/pymysql/_auth.py:254 in caching_sha2_password_auth expects to receive 0x01 (checked by pkt.is_extra_auth_data) and then the public key. Instead it's received 0x2D.. and then the same public key. This causes the code there to error out.

I have not yet figured out why it's getting 0x2D or what that means.

Likely candidate upstream commits:
https://github.com/mysql/mysql-server/commit/e31f9f26e26d428a577deb9b0b9d07f652045ccb
"Bug#34778017 authenticating over unix-socket fails"

https://github.com/mysql/mysql-server/commit/099e4529acc46657b887961ae590b8090ff2c1fc
"Bug#34556764: Contribution by Facebook: Fix sha256_password_auth_client_nonblocking"

08:47:03.952527 line 254 if not conn.server_public_key:
08:47:03.952638 line 255 pkt = _roundtrip(conn, b'\x02') # Request public key
08:47:03.953153 line 256 print(hex(pkt._data[0]))
0x2d
08:47:03.953290 line 257 print(pkt._data.decode('ascii'))
-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAkrOn8+wodDNTomkEa2jT
t6YIXXSwX9MEWnwEdXiSwCk3LW46l881lg7N1yox3KtgjrNn6X1aeHUGzrDcTGy+
Sz5ErhJZzWoHLMDt6IAfgPswgrH89NUkKadHTyk02E+fV4UL4k9LGDg6pYAdLU1w
EqGUCiugoQ6YNh8JAhfk3Y+haP1mhoN5JseoYvOc0o/D4R3nEOMl+a2JyNrwOVqq
JW5Z1Rxa9eByd/yjb+Hix5jNZf2S65J6i9zbDweSl06rS3EQXEc0k6mxpBhPMxkM
R9nbO86pD0yc1JUbwDam1nLXod2hzoHzHBUbbOQ4HDK+7nQ8VAZv2zG+j7tx8s0l
2wIDAQAB
-----END PUBLIC KEY-----

08:47:03.953448 line 258 if not pkt.is_extra_auth_data():
08:47:03.953534 line 259 raise OperationalError(
08:47:03.953607 line 260 "caching sha2: Unknown packet for public key: %s" % pkt._data[:1]
08:47:03.953683 line 259 raise OperationalError(
08:47:03.953757 exception 259 raise OperationalError(
Exception:..... pymysql.err.OperationalError: caching sha2: Unknown packet for public key: b'-'
Call ended by exception

[DUFOUR Olivier (odufourc)]

So far this has been reproduced exclusively on Mysql-router 8.0.32
It works normally on 8.0.28 (from Jammy 22.04) and 8.0.31

Meaning the regression has been introduced between 8.0.31 and 8.0.32 where authentication done through mysql-routers for plmysql will fail until the password is cached on the database.
This impact many Openstack components relying on plmysql library.

A small reproducer can be :
* to deploy 3 mysql-innodb-cluster
* to deploy 1 Keystone unit with mysql-router

And from the Keystone unit to try to run the following python3 code :
import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='127.0.0.1',
                             user='keystone',
                             password='PASSWORD_IN_/ETC/KEYSTONE/KEYSTONE.CONF',
                             database='keystone',
                             cursorclass=pymysql.cursors.DictCursor)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 353, in __init__
    self.connect()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 633, in connect
    self._request_authentication()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 932, in _request_authentication
    auth_packet = _auth.caching_sha2_password_auth(self, auth_packet)
  File "/usr/lib/python3/dist-packages/pymysql/_auth.py", line 257, in caching_sha2_password_auth
    raise OperationalError(
pymysql.err.OperationalError: caching sha2: Unknown packet for public key: b'-'

The password cache can be flushed when performing on the mysql-innodb-cluster server the command "FLUSH PRIVILEGES;"
--> this will trigger the issue mentioned for all sessions using mysql-router that were working before if the cluster was already preexisting.

A way to currently make the login cached on MySQL servers is to :
* either connect once with MySQL CLI and not plmysql to the mysql-router
* connect to the Unix socket of the mysql-router through plmysql
* connect either with MySQL CLI or plmysql directly to the mysql-innodb-cluster

[James Page (james-page)]

Confirmed and marked as Critical - this is clearly seen in all of our gate tests across the OpenStack charms for all releases including 22.04/jammy.

[Trent Lloyd (lathiat)]

[Workaround]

Downgrade mysql-router to 8.0.31

Focal (20.04) amd64: https://launchpad.net/ubuntu/+source/mysql-8.0/8.0.31-0ubuntu0.20.04.2/+build/24823714/+files/mysql-router_8.0.31-0ubuntu0.20.04.2_amd64.deb

Jammy (22.04) amd64: https://launchpad.net/~ubuntu-security-proposed/+archive/ubuntu/ppa/+build/24557440/+files/mysql-router_8.0.31-0ubuntu0.22.04.1_amd64.deb

Download and install the package, then restart the mysql-router service

systemctl restart *-mysql-router

[DUFOUR Olivier (odufourc)]

For the reproducers, I'm attaching the following bundle using the 3 mysql-innodb-cluster and the keystone unit.
As well as the python test code with pymysql library that output exactly the same error as Openstack components

[James Page (james-page)]

Downgrading mysql-router to 8.0.31-* works around this issue for the time being.

[James Page (james-page)]

A bit more information to help identify what the issue is.

When using the mysql driver (rather than pymysql) the same issue is not seen - the db_sync command completes successfully.

[Marc Deslauriers (mdeslaur)]

I am preparing 8.0.32+really8.0.31 revert packages until we find the proper fix for this issue. They are being uploaded to the security team PPA here:

https://launchpad.net/~ubuntu-security-proposed/+archive/ubuntu/ppa/+packages

Once they have built and we have tested them to make sure we can downgrade versions properly, I will publish them. I don't think there's a schema update between 8.0.31 and 8.0.32 so it should be ok.

[Lars Tangvald (lars-tangvald)]

I've gotten a patch from devs for this issue. Problem was that router wasn't correctly handling a request for a public key over an unencrypted connection.

I'm building with it to do a bit of testing, so hopefully this will be resolved shortly.

[James Page (james-page)]

reference #13 - the prefix code was not being passed in this context.

45 == '-'

Patch switches send from client -> server for passing back the public key to the client.

[Ubuntu Foundations Team Bug Bot (crichton)]

The attachment "fix_unencrypted_pubkeyrequest.patch" seems to be a patch. If it isn't, please remove the "patch" flag from the attachment, remove the "patch" tag, and if you are a member of the ~ubuntu-reviewers, unsubscribe the team.

[This is an automated message performed by a Launchpad user owned by ~brian-murray, for any issues please contact him.]

[Launchpad Janitor (janitor)]

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

[Corey Bryant (corey.bryant)]

I'm hitting an issue testing against 8.0.32-0buntu0.22.04.2~test1:

Apologies on the formatting.. for readability the tracebacks are also available here:
https://paste.ubuntu.com/p/zDzy29775q/plain/

Jan 25 19:49:14 juju-c6d44f-zaza-c448271c27a9-0 (keystone): 2023-01-25 19:49:14,574 CRITICAL Unhandled error
Traceback (most recent call last): File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 3250, in _wrap_pool_connect
return fn() File "/usr/lib/python3/dist-packages/sqlalchemy/pool/base.py", line 310, in connect
return _ConnectionFairy._checkout(self) File "/usr/lib/python3/dist-packages/sqlalchemy/pool/base.py", line 868, in _checkout
fairy = _ConnectionRecord.checkout(pool) File "/usr/lib/python3/dist-packages/sqlalchemy/pool/base.py", line 476, in checkout
rec = pool._do_get() File "/usr/lib/python3/dist-packages/sqlalchemy/pool/impl.py", line 145, in _do_get
with util.safe_reraise(): File "/usr/lib/python3/dist-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
compat.raise_( File "/usr/lib/python3/dist-packages/sqlalchemy/util/compat.py", line 207, in raise_
raise exception File "/usr/lib/python3/dist-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
return self._create_connection() File "/usr/lib/python3/dist-packages/sqlalchemy/pool/base.py", line 256, in _create_connection
return _ConnectionRecord(self) File "/usr/lib/python3/dist-packages/sqlalchemy/pool/base.py", line 371, in __init__
self.__connect() File "/usr/lib/python3/dist-packages/sqlalchemy/pool/base.py", line 665, in __connect
with util.safe_reraise(): File "/usr/lib/python3/dist-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
compat.raise_( File "/usr/lib/python3/dist-packages/sqlalchemy/util/compat.py", line 207, in raise_
raise exception File "/usr/lib/python3/dist-packages/sqlalchemy/pool/base.py", line 661, in __connect
self.dbapi_connection = connection = pool._invoke_creator(self) File "/usr/lib/python3/dist-packages/sqlalchemy/engine/create.py", line 590, in connect
return dialect.connect(*cargs, **cparams) File "/usr/lib/python3/dist-packages/sqlalchemy/engine/default.py", line 597, in connect
return self.dbapi.connect(*cargs, **cparams) File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 353, in __init__
self.connect() File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 633, in connect
self._request_authentication() File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 932, in _request_authentication
auth_packet = _auth.caching_sha2_password_auth(self, auth_packet) File "/usr/lib/python3/dist-packages/pymysql/_auth.py", line 266, in caching_sha2_password_auth
pkt = _roundtrip(conn, data) File "/usr/lib/python3/dist-packages/pymysql/_auth.py", line 120, in _roundtrip
pkt = conn._read_packet() File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 692, in _read_packet
packet_header = self._read_bytes(4) File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 748, in _read_bytes
raise err.OperationalError(pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')The above exception was the direct caus...

[James Page (james-page)]

Confirmed I see the same issue as Corey with ~test1.

In addition I noticed these log messages in the mysql-router log:

2023-01-26 09:41:02 routing ERROR [7fe756452640] classic::loop() processor failed: Bad message (generic:74)

with co-incide with the calls from the pymysql client.

[James Page (james-page)]

I dumped the packet contents for the failed authentication:

09:47:02.648759 IP (tos 0x0, ttl 64, id 35994, offset 0, flags [DF], proto TCP (6), length 508)
    localhost.mysql > localhost.37606: Flags [P.], cksum 0xfff0 (incorrect -> 0x0728), seq 101:557, ack 171, win 512, options [nop,nop,TS val 3886927917 ecr 3886927916], length 456
E.....@.@.._..............%.a.tM...........
...-...,.....-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAzJcGRo2xHlYcU4T+F7WJ
vPGQv7DcDLrCajAJ1EyV4QZcig05Sze9Ua8+42usJyp5lxlS+WNDiT0Jezkvub0p
h4TqilEChgGbhVqEutZvbN5R4oi9B7UOCZZWj9LDExz/Ns4Lk5swbXgoZxIYj1rt
17esGeJIjG0ltzX1qtlQKwuWqiHXnBuPmOULvlcWSk1WS6H4tCLoz+UFvczAY2N4
OifjEgNZJuOvlS6pER27hJAVuqLxjZRewtCa9atihlF5Vo12Po5O4bqRP4eqEkib
NEqtFknYPUu0RK959aBPdaNwbKs3zBcg6YzNbQ9Dts3E+8GJbxe2BIfArNVS4NP1
xwIDAQAB
-----END PUBLIC KEY-----

[James Page (james-page)]

Actually that's not where the failure is - the request for the public key appears to success.

This is the call where the encrypted password is sent.

[Rodrigo Barbieri (rodrigo-barbieri2010)]

the workaround for jammy is incorrect. The correct is: https://launchpad.net/~ubuntu-security-proposed/+archive/ubuntu/ppa/+build/24557440/+files/mysql-router_8.0.31-0ubuntu0.22.04.1_amd64.deb

[Marc Deslauriers (mdeslaur)]

Temporarily reverting MySQL to 8.0.31 in the archive isn't possible:

2023-01-26T14:57:26.899944Z 1 [ERROR] [MY-013171] [InnoDB] Cannot boot server ve
rsion 80031 on data directory built by version 80032. Downgrade is not supported
mysqld: Can't open file: 'mysql.ibd' (errno: 0 - )

So we need to see if upstream can provide us with a fix, or attempt to locate the problematic commits that went into 8.0.32.

[Jan Kneschke (jan-kneschke)]

Hi, the dev here.

With the -test1 packet, pymysql + mysqlrouter works for me.

Below, a working strace of pymysql. Can someone please provide me with an strace shows the sendto/recvfrom of a failing pymysql?

$ strace -e sendto,recvfrom python3 -c "import pymysql;cnx = pymysql.connect(host='127.0.0.1', port=6446, user='someuser', password='somepass'); cnx.query('DO 1')"
recvfrom(3, "M\0\0\0\n8.0.32\0\7\0\0\0DQi=\34qGI\0\337\377\377\2"..., 8192, 0, NULL, NULL) = 81
sendto(3, "\225\0\0\1\5\242:\0\377\377\377\0-\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 153, 0, NULL, 0) = 153
recvfrom(3, "\2\0\0\2\1\4", 8192, 0, NULL, NULL) = 6
sendto(3, "\1\0\0\3\2", 5, 0, NULL, 0) = 5
recvfrom(3, "\304\1\0\4\1-----BEGIN PUBLIC KEY-----\n"..., 8192, 0, NULL, NULL) = 456
sendto(3, "\0\1\0\5SS\301\252\336\366\262GMRb\237\267\227\262\r\v\6l\201\213\17Aso\366\311\33"..., 260, 0, NULL, 0) = 260
recvfrom(3, "\7\0\0\6\0\0\0\2\0\0\0", 8192, 0, NULL, NULL) = 11
sendto(3, "\23\0\0\0\3SET AUTOCOMMIT = 0", 23, 0, NULL, 0) = 23
recvfrom(3, "\7\0\0\1\0\0\0\0\0\0\0", 8192, 0, NULL, NULL) = 11
sendto(3, "\5\0\0\0\3DO 1", 9, 0, NULL, 0) = 9
recvfrom(3, "\7\0\0\1\0\0\0\0\0\0\0", 8192, 0, NULL, NULL) = 11
+++ exited with 0 +++

[Aleksey Morozov (lexxxx89)]

how to change version via juju in bundle.yaml ????

[Aleksey Morozov (lexxxx89)]

when deployed via juju it always installs the latest version 8.0.32

[Corey Bryant (corey.bryant)]

@Jan, thanks for reaching out. Here is the strace output with the test1 package:

ubuntu@juju-c93656-zaza-8e11ec22ace2-0:~$ strace -e sendto,recvfrom python3 -c "import pymysql; cnx = pymysql.connect(host='127.0.0.1', port=3306, user='<masked>', password='<masked>'); cnx.query('DO 1')"
recvfrom(3, "`\0\0\0\n8.0.32-0buntu0.22.04.2~test"..., 8192, 0, NULL, NULL) = 100
sendto(3, "\227\0\0\1\5\242:\0\377\377\377\0-\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 155, 0, NULL, 0) = 155
recvfrom(3, "\2\0\0\2\1\4", 8192, 0, NULL, NULL) = 6
sendto(3, "\1\0\0\3\2", 5, 0, NULL, 0) = 5
recvfrom(3, "\304\1\0\4\1-----BEGIN PUBLIC KEY-----\n"..., 8192, 0, NULL, NULL) = 456
sendto(3, "\0\1\0\5\0262;\2049p?\342\0046\302\255\251\"\341mE\36\251\272\365\370\250\306\340\236\242O"..., 260, 0, NULL, 0) = 260
recvfrom(3, "", 8192, 0, NULL, NULL) = 0
Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 353, in __init__
    self.connect()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 633, in connect
    self._request_authentication()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 932, in _request_authentication
    auth_packet = _auth.caching_sha2_password_auth(self, auth_packet)
  File "/usr/lib/python3/dist-packages/pymysql/_auth.py", line 266, in caching_sha2_password_auth
    pkt = _roundtrip(conn, data)
  File "/usr/lib/python3/dist-packages/pymysql/_auth.py", line 120, in _roundtrip
    pkt = conn._read_packet()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 692, in _read_packet
    packet_header = self._read_bytes(4)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 748, in _read_bytes
    raise err.OperationalError(
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')
+++ exited with 1 +++

[Corey Bryant (corey.bryant)]

Config files for recreating:
mysql-innodb-cluster:
/etc/mysql/mysql.conf.d/mysqld.cnf: https://paste.ubuntu.com/p/5r4pDnpwpr/
mysql-router:
/var/lib/mysql/keystone-mysql-router/mysqlrouter.conf: https://paste.ubuntu.com/p/32xWFTBdgZ/

Please let me know if I've missed any important ones.

[Corey Bryant (corey.bryant)]

This can be recreated doing the following. I'd recommend doing this on a Jammy machine.

1) snap install lxd
2) snap install juju
3) Follow these steps up through the 'juju bootstrap localhost overlord') command: https://juju.is/docs/olm/lxd#heading--set-up-the-localhost-cloud
4) juju deploy ./jammy-yoga.yaml (see file contents below)
5) watch juju status (as soon as you see Agent column say "executing" you can ssh into the corresponding unit and add the security ppa)
6) (optional to add security ppa) add PPA to units - ssh to units as follows and run 'sudo add-apt-repository ppa:ubuntu-security-proposed/ppa --yes'
juju ssh keystone/0
juju ssh mysql-innodb-cluster/0
juju ssh mysql-innodb-cluster/1
juju ssh mysql-innodb-cluster/2
(you'll need to do this fairly quickly as the unit will start installing software soon)
7) The keystone unit is where mysql-router will get installed. /etc/keystone/keystone.conf will have the mysql credentials. /var/log/juju/unit-keystone-0.log will eventually show a Python traceback with a failing command that ends with:
subprocess.CalledProcessError: Command '['sudo', '-u', 'keystone', 'keystone-manage', 'db_sync']' returned non-zero exit status 1."

$ cat jammy-yoga.yaml
variables:
  source: &source proposed
  openstack-origin: &openstack-origin distro-proposed

series: &series jammy
applications:
  keystone:
    charm: ch:keystone
    num_units: 1
    options:
      admin-password: openstack
      openstack-origin: *openstack-origin
    constraints: mem=1024
    channel: yoga/edge
  keystone-mysql-router:
    charm: ch:mysql-router
    channel: 8.0/edge
  mysql-innodb-cluster:
    charm: ch:mysql-innodb-cluster
    num_units: 3
    constraints: mem=4096
    channel: 8.0/edge
relations:
- - keystone-mysql-router:db-router
  - mysql-innodb-cluster:db-router
- - keystone:shared-db
  - keystone-mysql-router:shared-db

[Launchpad Janitor (janitor)]

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

[Alan Baghumian (alanbach)]

I confirm #23

mysqld: Can't open file: 'mysql.ibd' (errno: 0 - )
2023-01-26T23:23:12.211977Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2023-01-26T23:23:12.216007Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2023-01-26T23:23:12.216053Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-01-26T23:23:12.220664Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.31-0ubuntu0.20.04.2) (Ubuntu).
2023-01-26T23:23:16.720520Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.31-0ubuntu0.20.04.2) starting as process 2968
2023-01-26T23:23:16.763896Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-01-26T23:23:18.493164Z 1 [ERROR] [MY-013171] [InnoDB] Cannot boot server version 80031 on data directory built by version 80032. Downgrade is not supported

[Trent Lloyd (lathiat)]

@Aleksey Morozov (lexxxx89): It's not possible to avoid this issue with a bundle.yaml currently. There is no easy way to ask juju to deploy an older build of MySQL as it's not available from the archive normally. The only thing you could do is let it deploy then manually downgrade the mysql-router package everywhere - but the charms may or may not recover correctly from the original failing deploy depending on the charm.

[Nobuto Murata (nobuto)]

A "hack" is putting cloudinit-userdata into Juju's model-config. Obviously, it's not for production usage since it will miss all security fixes so far. Also, the hack must be removed from the filesystem and model-config once this issue settles.

[focal]
====
cloudinit-userdata: |
  write_files:
    - content: |
        Package: mysql-router
        Pin: release a=focal
        Pin-Priority: 1001
      owner: "root:root"
      path: /etc/apt/preferences.d/mysql-router-hack
      permissions: "0664"
====

[jammy]
====
cloudinit-userdata: |
  write_files:
    - content: |
        Package: mysql-router
        Pin: release a=jammy
        Pin-Priority: 1001
      owner: "root:root"
      path: /etc/apt/preferences.d/mysql-router-hack
      permissions: "0664"
====

[DUFOUR Olivier (odufourc)]

To reply to the comment 23 and 34 about the fact that Mysql package cannot be downgraded.

Downgrading just Mysql-router packages, which doesn't impact any existing database on 8.0.32 is sufficient and works.
Meaning that you can have mysql-innodb-cluster working at 8.0.32 and just mysql-router downgraded to 8.0.31.
Wouldn't it be sufficient to unblock the situation from the archive point of view ?

[Aleksey Morozov (lexxxx89)]

DUFOUR Olivier (odufourc) how to downgrade an already installed router?

[Trent Lloyd (lathiat)]

As mysql-router is built from the same mysql source package as the server, I do not believe it is trivial to revert the mysql-router binary package only to 8.0.31. We cannot revert the entire server because of the previously mentioned reasons.

To downgrade it manually, see the issue description "Workaround" section.

[Marc Deslauriers (mdeslaur)]

There are updates building in the security team PPA here based on Corey's test package which revert all the changes that went into the router component:

https://launchpad.net/~ubuntu-security-proposed/+archive/ubuntu/ppa/+packages

[Corey Bryant (corey.bryant)]

@Marc, I've tested all 3 releases from the security-proposed PPA successfully with the juju recreate from comment #29 above.

[Launchpad Janitor (janitor)]

This bug was fixed in the package mysql-8.0 - 8.0.32-0ubuntu0.20.04.2

---------------
mysql-8.0 (8.0.32-0ubuntu0.20.04.2) focal-security; urgency=medium

  * SECURITY REGRESSION: Regression with PyMySQL (LP: #2003835)
    - d/p/revert-router-to-8.0.31/*.patch: Revert every patch that modified
      the router directory in version 8.0.32, except for the following:
      605df79542d, b806d2bbb2d, faabb6e7404, 1ae29236865, d03f34cd9fe.
    - debian/mysql-router-8.0.install: Removed destination_status files.

 -- Marc Deslauriers <email address hidden> Sat, 28 Jan 2023 09:44:32 -0500

[Launchpad Janitor (janitor)]

This bug was fixed in the package mysql-8.0 - 8.0.32-0ubuntu0.22.10.2

---------------
mysql-8.0 (8.0.32-0ubuntu0.22.10.2) kinetic-security; urgency=medium

  * SECURITY REGRESSION: Regression with PyMySQL (LP: #2003835)
    - d/p/revert-router-to-8.0.31/*.patch: Revert every patch that modified
      the router directory in version 8.0.32, except for the following:
      605df79542d, b806d2bbb2d, faabb6e7404, 1ae29236865, d03f34cd9fe.
    - debian/mysql-router-8.0.install: Removed destination_status files.

 -- Marc Deslauriers <email address hidden> Sat, 28 Jan 2023 09:44:32 -0500

[Launchpad Janitor (janitor)]

This bug was fixed in the package mysql-8.0 - 8.0.32-0ubuntu0.22.04.2

---------------
mysql-8.0 (8.0.32-0ubuntu0.22.04.2) jammy-security; urgency=medium

  * SECURITY REGRESSION: Regression with PyMySQL (LP: #2003835)
    - d/p/revert-router-to-8.0.31/*.patch: Revert every patch that modified
      the router directory in version 8.0.32, except for the following:
      605df79542d, b806d2bbb2d, faabb6e7404, 1ae29236865, d03f34cd9fe.
    - debian/mysql-router-8.0.install: Removed destination_status files.

 -- Marc Deslauriers <email address hidden> Sat, 28 Jan 2023 09:44:32 -0500

[Trent Lloyd (lathiat)]

The package was now released. Confirmed working for me also.

Note that you need to restart the mysql-router services manually to effect the upgrade:
systemctl restart \*-mysql-router

[Lars Tangvald (lars-tangvald)]

Jan made a more targeted patch for the underlying issue: Router doesn't correctly handle unencrypted passwords with a length over 20.

[Lars Tangvald (lars-tangvald)]

Just to sum up, I think these are technically two separate issues (and both patches are needed):
- Over an unencrypted channel, router 8.0.32 doesn't use the correct header when passing the public key
- Over an unencrypted channel, router 8.0.32 doesn't correctly scramble long passwords

[Jan Kneschke (jan-kneschke)]

Understanding the root-cause, a workaround would be to ask pymysql for an encrypted connection by adding 'ssl={"verify_mode": ...}'

  import pymysql;

  with pymysql.connect(host='127.0.0.1', port=6446,
      user='<user>', password='<pass>',
      ssl={'verify_mode':0}) as cnx:
    cnx.query('SELECT ...')"

That would bypass all the issues handled above.

[Launchpad Janitor (janitor)]

This bug was fixed in the package mysql-8.0 - 8.0.32-0ubuntu3

---------------
mysql-8.0 (8.0.32-0ubuntu3) lunar; urgency=medium

  * SECURITY REGRESSION: Regression with PyMySQL (LP: #2003835)
    - d/p/revert-router-to-8.0.31/*.patch: Revert every patch that modified
      the router directory in version 8.0.32, except for the following:
      605df79542d, b806d2bbb2d, faabb6e7404, 1ae29236865, d03f34cd9fe.
    - debian/mysql-router-8.0.install: Removed destination_status files.

 -- Marc Deslauriers <email address hidden> Sat, 28 Jan 2023 09:44:32 -0500

[Utkarsh Gupta (utkarsh)]

Ubuntu 22.10 (Kinetic Kudu) has reached end of life, so this bug will not be fixed for that specific release.