Hi Folks,
We’ve faced a curious issue when upgrading some of our systems that use PXC 5.7.36 (on el7) through to PXC 8.0.32 (on el8)
We export the DB from the PXC 5.7.36 system using XtraBackup 2.4.24 and import it into the PXC 8.0.32 DB and then modify a table to add an extra column:
ALTER TABLE my_table ADD COLUMN created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP after col_10;
…with the intention of it ending up like:
mysql> describe my_table;
+----------------------------+--------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+--------------+------+-----+-------------------+-----------------------------------------------+
| id | int | NO | PRI | NULL | auto_increment |
| col_1 | varchar(128) | NO | MUL | NULL | |
| col_2 | int | NO | | NULL | |
| col_3 | varchar(50) | NO | MUL | NULL | |
| col_4 | tinyint(1) | NO | | 0 | |
| col_5 | varchar(300) | YES | | NULL | |
| col_6 | json | YES | | NULL | |
| col_7 | json | YES | | NULL | |
| col_8 | int | YES | | NULL | |
| col_9 | tinyint(1) | YES | | 0 | |
| col_10 | json | YES | | NULL | |
| created_at | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| last_updated | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+----------------------------+--------------+------+-----+-------------------+-----------------------------------------------+
We then find that table is corrupted:
mysql> select * from my_table;
ERROR 3142 (HY000): The JSON binary value contains invalid data.
…where we think the columns have been incorrectly shifted or something.
It sounds pretty similar to that being discussed over here: Data corruption after Percona Server 5.7.32-35 to 8.0.32-24 upgrade
…but curiously we only seem to experience the issue when doing the upgrade with tarball versions of PXC5.7.36 and PXC8.0.32. The same steps carried out on a RPM install system seem to work fine.
Any idea what we could do to pin it down? Is it feasible that the tarball PXC 8.0.32 could contain a bug that the RPM version doesn’t?
Specific versions in play here are:
Tarball:
- Percona-XtraDB-Cluster-5.7.36-rel39-55.1.Linux.x86_64.glibc2.12.tar.gz
- percona-xtrabackup-2.4.24-Linux-x86_64.glibc2.12.tar.gz
- Percona-XtraDB-Cluster_8.0.32-24.2_Linux.x86_64.glibc2.17.tar.gz
- percona-xtrabackup-8.0.32-26-Linux-x86_64.glibc2.17.tar.gz
RPM:
- Percona-XtraDB-Cluster-server-57-5.7.36-31.55.1.el7.x86_64
- percona-xtrabackup-24-2.4.24-1.el7.x86_64
- percona-xtrabackup-80-8.0.32-26.1.el8.x86_64
- percona-xtradb-cluster-server-8.0.32-24.2.el8.x86_64
Appreciate any responses.
many thanks,
Neil