Description:
Hi,
Bug#98501 (Exchanging partition does not update SDI) was fixed in 8.0.24, but it looks like nothing was implemented in the upgrade process to clear the wrong SDI introduced by exchanging partition. See How to repeat for details.
As Bug#98501 was considered a important enough bug to be fixed, I would expect wrong SDI introduced by exchanging partition to be cleaned-up on upgrade.
I am not aware of situations where these wrong SDI are causing major problems. However, if we let these "latent SDI corruptions" exist in ibd files, we might eventually see a critical problem because of this.
How to repeat is for 8.0.40, but I was also able to reproduce with 8.4.3, and then upgrading from 8.4.3 to 9.1.0 does not clear the corruption either.
Two ways of cleaning this corruption is given in How to repeat: ALTER TABLE FORCE and double EXCHANGE PARTITION.
Also in How to repeat: MySQL Shell checkForServerUpgrade does not detect this corruption.
Setting this as S2 (Serious) as Bug#98501 was also S2.
Many thanks for looking into this,
J-F Gagné
How to repeat:
# The reproduction first introduces SDI corruption in 8.0.23.
# Then we upgrade to 8.0.40 and show the corruption is still there.
############################################################
# A few bash variables to make scripts below easier to read.
# ---=== Begin variables ===---
sql_db="drop database if exists test_jfg; create database test_jfg; use test_jfg;"
sql_table_part='
CREATE TABLE e (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30)
) PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (50),
PARTITION p1 VALUES LESS THAN (100),
PARTITION p2 VALUES LESS THAN (150),
PARTITION p3 VALUES LESS THAN (MAXVALUE));
INSERT INTO e VALUES
(1669, "Jim", "Smith"),
(337, "Mary", "Jones"),
(16, "Frank", "White"),
(2005, "Linda", "Black");'
sql_table_simple="CREATE TABLE e2 LIKE e; ALTER TABLE e2 REMOVE PARTITIONING;"
sql_persist="flush table e, e2 for export;"
sql_exchange="ALTER TABLE e EXCHANGE PARTITION p1 WITH TABLE e2;"
# ---=== End variables ===---
####################################################
# Some back functions to make the test-case compact.
# ---=== Begin functions ===---
function show_partial_sdi() {
. sb_include
local jq='.[] | if type == "object" then . else {} end | [ .object.dd_object_type, .object.dd_object.name]'
{ echo "$1:"
$CLIENT_BASEDIR/bin/ibd2sdi data/test_jfg/$1 | jq -c "$jq" | grep -v null
} | paste -s -d " " -
}
function show_partial_sdis() {
show_partial_sdi e#p#p0.ibd
show_partial_sdi e#p#p1.ibd
show_partial_sdi e2.ibd
}
function exchange_test() {
./use <<< "
$sql_db
$sql_table_part
$sql_table_simple
$sql_persist"
show_partial_sdis
# We need $sql_persist because if not,
#. show_partial_sdis could show old values.
./use test_jfg <<< "$sql_exchange $sql_persist"
echo
show_partial_sdis
}
function upgrade_test () {
./stop; rm -rf data/
( cd ../$1/; ./stop > /dev/null; tar -c data; ) | tar -x; rm data/msandbox.err
./start
show_partial_sdis
}
# ---=== End functions ===---
dbdeployer deploy single mysql_8.0.23
############################################################
# In 8.0.23 directory, run the exchange test.
# The 2nd invocation of ibd2sdi on e#p#p1.ibd shows old data
# for table e2, which is Bug#98501.
$ exchange_test
e#p#p0.ibd: ["Table","e"] ["Tablespace","test_jfg/e#p#p0"]
e#p#p1.ibd: ["Tablespace","test_jfg/e#p#p1"]
e2.ibd: ["Table","e2"] ["Tablespace","test_jfg/e2"]
e#p#p0.ibd: ["Table","e"] ["Tablespace","test_jfg/e#p#p0"]
e#p#p1.ibd: ["Table","e2"] ["Tablespace","test_jfg/e#p#p1"] <<<===--- ["Table","e2"] should not be here !!!
e2.ibd: ["Table","e2"] ["Tablespace","test_jfg/e2"]
dbdeployer deploy single mysql_8.0.40
#####################################################################
# In 8.0.40 directory and out of curiosity, run the exchange test.
# The 2nd invocation of ibd2sdi on e#p#p1.ibd does not show table e2.
$ exchange_test
e#p#p0.ibd: ["Table","e"] ["Tablespace","test_jfg/e#p#p0"]
e#p#p1.ibd: ["Tablespace","test_jfg/e#p#p1"]
e2.ibd: ["Table","e2"] ["Tablespace","test_jfg/e2"]
e#p#p0.ibd: ["Table","e"] ["Tablespace","test_jfg/e#p#p0"]
e#p#p1.ibd: ["Tablespace","test_jfg/e#p#p1"] <<<<<<======------ No table e2 !!!
e2.ibd: ["Table","e2"] ["Tablespace","test_jfg/e2"]
######################################################
# Let's do an upgrade from 8.0.23 in 8.0.40.
# The corruption in 8.0.23 is still present in 8.0.40.
$ upgrade_test msb_mysql_8_0_23
stop /home/jgagne/sandboxes/msb_mysql_8_0_40
........................................ sandbox server started
e#p#p0.ibd: ["Table","e"] ["Tablespace","test_jfg/e#p#p0"]
e#p#p1.ibd: ["Table","e2"] ["Tablespace","test_jfg/e#p#p1"] <<<===--- ["Table","e2"] should not be here !!!
e2.ibd: ["Table","e2"] ["Tablespace","test_jfg/e2"]
###############
# More tests...
# You can also test with 8.4.3 from 8.0.23 (result not shown).
dbdeployer deploy single mysql_8.4.3
upgrade_test msb_mysql_8_0_23
# Or from 8.0.40 (result not shown).
upgrade_test msb_mysql_8_0_40
# And after doing 8.4.3, you can do 9.1.0 (result not shown).
dbdeployer deploy single mysql_9.1.0
upgrade_test msb_mysql_8_4_3
# An ALTER TABLE FORCE clears the corruption, but would be very long on a large table.
$ ./use test_jfg <<< "ALTER TABLE e FORCE; $sql_persist"
$ show_partial_sdis
e#p#p0.ibd: ["Table","e"] ["Tablespace","test_jfg/e#p#p0"]
e#p#p1.ibd: ["Tablespace","test_jfg/e#p#p1"] <<<<<<======------ No table e2 after ALTER FORCE !!!
e2.ibd: ["Table","e2"] ["Tablespace","test_jfg/e2"]
# And a double EXCHANGE PARTITION also clears the corruption, and this is probably quicker.
$ ./use test_jfg <<< "$sql_exchange $sql_exchange $sql_persist"
$ show_partial_sdis
e#p#p0.ibd: ["Table","e"] ["Tablespace","test_jfg/e#p#p0"]
e#p#p1.ibd: ["Tablespace","test_jfg/e#p#p1"] <<<===--- No table e2 after double EXCHANGE PARTITION !!!
e2.ibd: ["Table","e2"] ["Tablespace","test_jfg/e2"]
# And MySQL Shell checkForServerUpgrade not finding any problems.
$ ./mysqlsh --defaults-file=~/sandboxes/msb_mysql_8_0_23/my.sandbox.cnf -- util checkForServerUpgrade
Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory
The MySQL server at /tmp%2Fmysql_sandbox8023.sock, version 8.0.23 - MySQL
Community Server - GPL, will now be checked for compatibility issues for
upgrade to MySQL 8.0.40...
1) Usage of db objects with names conflicting with new reserved keywords
No issues found
2) Issues reported by 'check table x for upgrade' command
No issues found
3) Check for deprecated usage of single dollar signs in object names
No issues found
Errors: 0
Warnings: 0
Notices: 0
No known compatibility errors or issues were found.
Suggested fix:
On upgrade, detect corrupted SDI and fix them. This would need to be done in 8.0, 8.4 and 9.
Also, I think the MySQL Shell should detect such corruption.
