Broken SQL CONCAT function behaviour in 5.7.30-0ubuntu0.18.04.1

Bug #1877442 reported by Grégoire Payen de La Garanderie
20
This bug affects 2 people
Affects Status Importance Assigned to Milestone
mysql-5.7 (Ubuntu)
High
Unassigned
Xenial
High
Unassigned
Bionic
High
Unassigned
Eoan
Undecided
Unassigned
Focal
Undecided
Unassigned
mysql-8.0 (Ubuntu)
High
Unassigned
Xenial
Undecided
Unassigned
Bionic
Undecided
Unassigned
Eoan
High
Unassigned
Focal
High
Unassigned

Bug Description

[Impact]
This is a regression in MySQL 5.7.30 (does not affect 8.0 which is in focal and development release, nor 5.7.29). The CONCAT function has inconsistent behavior depending on whether or not the result is assigned to a variable, which is leading to a crash in slurmdbd.

Since it's limited to 5.7.30, the bug only affects stable releases Bionic and Xenial.

[Test Case]
- Start up MySQL Server 5.7.30
- Connect to it with the mysql cli (with default install of the server, "sudo mysql" will connect to the MySQL root user)
- Run the following two queries:
  * SELECT CONCAT('');
  * SET @var = ""; SELECT @var := CONCAT('');
They should give the same output, but the second one returns NULL instead of an empty string when using 5.7.30.

[Regression Potential]
The patch changes fairly low-level code, which can always carry some risk.

[Other info]
This bug is fixed in the next upstream release of MySQL (5.7.31), so the patch would only be needed until that is picked.

--- ORIGINAL DESCRIPTION ---
Hi,

Yesterday we upgraded from 5.7.29-0ubuntu0.18.04.1 to 5.7.30-0ubuntu0.18.04.1 (via unattended-upgrade) on Ubuntu 18.04 and slurmdbd (https://packages.ubuntu.com/bionic/admin/slurmdbd) started segfaulting.

Upon inspection, the slurmdbd crash is caused by a change of behaviour of the SQL CONCAT function. The new behaviour seems wrong.

Calling
SELECT CONCAT('');
will result in an empty string as expected however if the result is assigned to a variable with:
SET @var = ""; SELECT @var := CONCAT('');
the variable will be NULL instead of an empty string.

It seems that when the output of CONCAT is assigned to a variable; if CONCAT should have returned an empty string, it will set the variable to NULL instead.

SLURM itself crashes because it uses a stored procedure which relies on the output of CONCAT to be an empty string rather than a NULL variable and tries to dereference the pointer.

I wasn’t able to get hold of 5.7.29-0ubuntu0.18.04.1 via APT so I tested on 5.7.21-1ubuntu1. On 5.7.21, the variable was correctly set to an empty string. I have also tried on 20.04 and the behaviour is correct as well. So the problem seems to be present only in the version 5.7.30-0ubuntu0.18.04.1.

I think that the behaviour was introduced in the 5.7 branch by this commit:
https://github.com/mysql/mysql-server/commit/addb2aab601d98e685eccae545f79d120561ad9b
but I am not entirely sure.

This bug is a bit annoying because it makes SLURM with accounting enabled completely unusable. However downgrading MySQL to 5.7.29-0ubuntu0.18.04.1 temporarily fixed the problem.

I have attached a log file with examples to reproduce the bug.

Thank you very much.

Revision history for this message
Grégoire Payen de La Garanderie (gdlg) wrote :
Revision history for this message
Grégoire Payen de La Garanderie (gdlg) wrote :

I am also attaching the SLURM stored procedure which triggers the bug. It’s obviously a bit more complicated than my minimal example :-)

Revision history for this message
Lars Tangvald (lars-tangvald) wrote :

Thanks for the report and the testcase! I'll pass it on upstream.

Revision history for this message
Lars Tangvald (lars-tangvald) wrote :

Verified as upstream bug: https://bugs.mysql.com/bug.php?id=99485

Revision history for this message
Launchpad Janitor (janitor) wrote :

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

Changed in mysql-5.7 (Ubuntu):
status: New → Confirmed
Robie Basak (racb)
tags: added: regression-update
Changed in mysql-5.7 (Ubuntu):
status: Confirmed → Triaged
importance: Undecided → High
Changed in mysql-8.0 (Ubuntu):
status: New → Triaged
Revision history for this message
Robie Basak (racb) wrote :

Thank you for taking the time to report this bug and helping to make Ubuntu better. This is noted in the upstream bug as "fixed in 5.7.31, 8.0.21". We will need to wait for publicly available patches to fix Ubuntu.

Revision history for this message
Robie Basak (racb) wrote :

(5.7.31 and 8.0.21 are not yet released)

Changed in mysql-5.7 (Ubuntu Eoan):
status: New → Invalid
Changed in mysql-5.7 (Ubuntu Focal):
status: New → Invalid
Changed in mysql-8.0 (Ubuntu Xenial):
status: New → Invalid
Changed in mysql-8.0 (Ubuntu Bionic):
status: New → Invalid
Changed in mysql-8.0 (Ubuntu Eoan):
status: New → Triaged
Changed in mysql-8.0 (Ubuntu Focal):
status: New → Triaged
Changed in mysql-5.7 (Ubuntu Xenial):
importance: Undecided → High
status: New → Triaged
Changed in mysql-5.7 (Ubuntu Bionic):
importance: Undecided → High
status: New → Triaged
Changed in mysql-8.0 (Ubuntu):
importance: Undecided → High
Changed in mysql-8.0 (Ubuntu Eoan):
importance: Undecided → High
Changed in mysql-8.0 (Ubuntu Focal):
importance: Undecided → High
Revision history for this message
Lars Tangvald (lars-tangvald) wrote :

This bug is actually only in 5.7.30, not 8.0.20 (not 100% sure if we made a mistake marking it as fixed in 8.0.21 unless there was some underlying issue that only triggered the NULL result in 5.7)

Revision history for this message
Lars Tangvald (lars-tangvald) wrote :

I've tested a patch that fixes this issue now. Will try to get a fix in (though it will also be fixed in the next upstream release).

description: updated
description: updated
Revision history for this message
Brian Murray (brian-murray) wrote :

The Eoan Ermine has reached end of life, so this bug will not be fixed for that release

Changed in mysql-8.0 (Ubuntu Eoan):
status: Triaged → Won't Fix
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.