mysql-server-5.7 postinst fails when in read-only mode

Bug #1889472 reported by Simon Déziel on 2020-07-29
16
This bug affects 1 person
Affects Status Importance Assigned to Milestone
mysql-5.7 (Ubuntu)
Xenial
Low
Unassigned
Bionic
Low
Unassigned

Bug Description

[Impact]

Updates of the mysql-server-5.7 package fail to install (error during postinst) if operating in (super) read-only mode.
A read-only replica is common in redundant/HA setups.

[Test Case]

1) Setup a container (bionic or xenial would do)
$ lxc launch images:ubuntu/bionic sql1
$ lxc shell sql1
# apt-get update && apt-get install -y --no-install-recommends mysql-server

2) Configure read-only mode
# cat << EOF >> /etc/mysql/my.cnf

[mysqld]
super_read_only = ON
read_only = ON
EOF
# service mysql restart
# mysql -e "SELECT @@global.read_only, @@global.super_read_only;"
+--------------------+--------------------------+
| @@global.read_only | @@global.super_read_only |
+--------------------+--------------------------+
| 1 | 1 |
+--------------------+--------------------------+

3) Trigger the postinst code
# apt-get install --reinstall mysql-server-5.7
Reading package lists... Done
Building dependency tree
Reading state information... Done
0 upgraded, 0 newly installed, 1 reinstalled, 0 to remove and 0 not upgraded.
Need to get 0 B/2,931 kB of archives.
After this operation, 0 B of additional disk space will be used.
Preconfiguring packages ...
(Reading database ... 16169 files and directories currently installed.)
Preparing to unpack .../mysql-server-5.7_5.7.31-0ubuntu0.18.04.1_amd64.deb ...
Unpacking mysql-server-5.7 (5.7.31-0ubuntu0.18.04.1) over (5.7.31-0ubuntu0.18.04.1) ...
Setting up mysql-server-5.7 (5.7.31-0ubuntu0.18.04.1) ...
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
mysql_upgrade: [ERROR] 1290: The MySQL server is running with the --super-read-only option so it cannot execute this statement
mysql_upgrade failed with exit status 5
dpkg: error processing package mysql-server-5.7 (--configure):
 installed mysql-server-5.7 package post-installation script subprocess returned error exit status 1
Processing triggers for systemd (237-3ubuntu10.41) ...
Errors were encountered while processing:
 mysql-server-5.7
E: Sub-process /usr/bin/dpkg returned an error code (1)

Step 3) should not cause a dpkg error.

[Regression Potential]

This patch runs a MySQL query to check if the service is in (super) read-only mode before calling mysql_upgrade. The SQL query might
fail thus preventing the proper detection of the read-only mode. The patch assumes read-write mode by default so that it would still
call mysql_upgrade. Fortunately, mysql_upgrade is known to safely error out when in read-only as this is what the bug is about.

Another possibility is to wrongly detect read-only mode which would skip running mysql_upgrade when it should have been. To mitigate
this, the patch informs the user ("mysql_upgrade skipped due to (super) read-only mode") who could then run mysql_upgrade manually.

[Other Info]

If and when the patch lands in -proposed, I will update the test instructions to indicate that -proposed needs to be enabled and mysql-server-* should be upgraded instead of --reinstall'ed.

Simon Déziel (sdeziel) wrote :

The mysql-server packages (8.0) provided in later releases (focal+) are not affected.

Paride Legovini (paride) wrote :

Hello Simon and thanks for your bug report and for the detailed steps to reproduce it. The command causing the error is mysql_upgrade, which can also be run manually, without triggering the execution of the postinst.

The command has been dropped in mysql-8, as the upgrade is now done automatically by the mysql daemon on startup.

What you are hitting seems to be a limitation of mysql/mysql_upgrade. We could avoid running mysql_upgrade if mysql is configured in (super-)read-only mode, but we'd need a reliable way to check if that's the case even if the daemon is not running, and still I'd not be really aware of the implications of skipping the mysql_upgrade runs.

While I acknowledge the situation is not ideal, I'm not sure on how we can safely improve on your setup.

Paride Legovini (paride) on 2020-07-30
Changed in mysql-5.7 (Ubuntu):
status: New → Triaged
Simon Déziel (sdeziel) wrote :

Hi Paride, thanks for having a look. Would you consider the provided patch for inclusion? It worked in my local tests.

Simon Déziel (sdeziel) on 2020-07-30
description: updated

The attachment "Skip mysql_upgrade when in (super) read-only mode" 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.]

tags: added: patch
Robie Basak (racb) wrote :

Hi Simon,

What's the full HA setup you're using here please? Would it be more appropriate for you to be using mysql-server-core-5.7 directly instead, with a manual configuration?

I don't think it's realistic for mysql-server apt/dpkg packaging to provide both automatic management of a database in /var/lib/mysql and also automatically support HA setups since apt/dpkg can only coordinate a single host at once. For that we have mysql-server-core-* packages with the expectation that users will configure what they need - either manually or with a higher level tool that can coordinate across multiple hosts.

While I have no objection to fixing specific cases, I wonder if it's worth the maintenance cost of including this case if it's to support a use case that we cannot realistically support anyway.

Your thoughts appreciated.

Simon Déziel (sdeziel) wrote :

Hi Robie,

I took a look at mysql-server-core-5.7 based on IRC discussions with you. Unfortunately, this package doesn't create the mysql user and group, doesn't provide the systemd unit and doesn't integrate well with other tooling (like the mysql puppet module maintained by puppetlabs). For those reasons, I concluded it was better to stick with the non-core package for my client's setup.

I tried to lower the maintenance cost by providing a patch, but I would understand Canonical not wanting to adopt it for a user population that's unknown and potentially rather low.

Regards,
Simon

Lars Tangvald (lars-tangvald) wrote :

Hi Simon,

Would it help for you to manually trigger "freeze mode"? This was added as a way to stop automatic management of the service in case of some unrecoverable issue with it, without triggering apt errors.

If you create the file /etc/mysql/FROZEN (if you're installing in interactive mode you should get a debconf message about this), it should skip most of the postinst script, so it won't start the service, run mysql_upgrade, etc, but you should get all the unit and config files so you can easily init and start it up yourself.

Simon Déziel (sdeziel) wrote :

Hi Lars,

The freeze mode looked promising but it bypasses a lot of good bits of the configure script like Apparmor profile activation, directories perms/owner fixes, etc. Also, the postinst script stops the MySQL service unconditionally and the configure aborts early in freeze mode, leaving the service stopped after the upgrade. Thanks for the suggestion though, much appreciated!

Regards,
Simon

Robie Basak (racb) wrote :

Thank you for the discussion.

Since 8.0 isn't affected, I'm marking this as Fix Released with tasks for Xenial and Bionic only.

I'm on the fence about the suitability for your patch - as the packaging isn't intended to support HA configurations, so maintaining the patch incurs unnecessary maintenance burden.

However, as 8.0 isn't affected, the maintenance would only be necessary for Xenial and Bionic so would be minimal in this particular case, and your patch is non-invasive and relatively trivial.

If you're prepared to take that maintenance on, I'll support you landing SRUs for this (Bionic only if that's all you want, or Bionic and Xenial if you require Xenial). I'd expect you to follow the full SRU procedure please, apart for any necessary review and sponsorship which my team will take on for you.

Changed in mysql-5.7 (Ubuntu):
status: Triaged → Invalid
Changed in mysql-5.7 (Ubuntu Xenial):
status: New → Triaged
Changed in mysql-5.7 (Ubuntu Bionic):
status: New → Triaged
Changed in mysql-5.7 (Ubuntu Xenial):
importance: Undecided → Low
Changed in mysql-5.7 (Ubuntu Bionic):
importance: Undecided → Low
Robie Basak (racb) wrote :

Invalid because on second thought, src:mysql-5.7 doesn't actually exist in Focal or Groovy.

I should add that another reason I'm supportive of an SRU in principle is that working around is quite obtuse (it would need a switch from mysql-server-5.7 to mysql-server-core-5.7 only which is not trivial).

Simon Déziel (sdeziel) on 2020-08-12
description: updated
Simon Déziel (sdeziel) wrote :

Hi Robie,

I filled the SRU template and would appreciate if you/your team could take a look at the debdiff for Bionic (I don't intent to get one for Xenial). I tested it, in various scenarios (R/W, R/O, super R/O) and it worked fine.

Regards,
Simon

description: updated
Mathew Hodson (mhodson) on 2020-10-03
no longer affects: mysql-5.7 (Ubuntu)
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers