Column %s precise type mismatch error while importing a tablespace

General

Escalation

General

Escalation

Description

When xtrabackup takes a backup and exports a tablespace, xtrabackup gets the wrong table definition from the ibd for tables that have changed the charset-collation in MySQL before backup.

CREATE TABLE test.a (
  a datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_ci;

the collation_id is 8 (latin1_swedish_ci)

shell> ibd2sdi /var/lib/mysql/test/a.ibd | jq '.[1].object.dd_object.columns[0]' | grep collation_id
  "collation_id": 8,

When MySQL converts the charset on a table, It converts the date and time data types columns in ibd file, but the data dictionary cache. The collation in the ibd does not match that of data dictionary.

ALTER TABLE test.a CONVERT TO CHARACTER SET utf8mb4 collate utf8mb4_unicode_ci;

The collation_id becomes 224 (utf8mb4_unicode_ci)

shell> ibd2sdi /var/lib/mysql/test/a.ibd | jq '.[1].object.dd_object.columns[0]' | grep collation_id
  "collation_id": 224,

The collation_id of the copied table is 8 (latin1_swedish_ci)

create table xb.a like test.a;

shell> ibd2sdi /var/lib/mysql/xb/a.ibd | jq '.[1].object.dd_object.columns[0]' | grep collation_id
  "collation_id": 8,

When xtrabackup exports the tablespace, the collation_id is 224 in ibd. Xtrabackup will write it to cfg metadata file.

When MySQL imports a tablespace, MySQL gets an error Column %s precise type mismatch because the collation_id of MySQL does not match that of xtrabackup.

The issue caused by the https://perconadev.atlassian.net/browse/PS-9219

Reproduce

create table

create database test;
CREATE TABLE test.a (
  a datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_ci;

Converting collation

ALTER TABLE test.a CONVERT TO CHARACTER SET utf8mb4 collate utf8mb4_unicode_ci;

backup

xtrabackup --backup --target-dir=/tmp/backup
xtrabackup  --prepare --export --target-dir=/tmp/backup

create a new table for importing

create database xb;
create table xb.a like test.a;

discarding tablespace

alter table xb.a discard tablespace;

copy tablespace from backup

cp /tmp/backup/test/a.* /var/lib/mysql/xb
chown -R mysql: /var/lib/mysql/xb

import tablespace

alter table xb.a import tablespace;

MySQL shows the error below:

ERROR 1808 (HY000): Schema mismatch (Column a precise type mismatch.)

Debug

In row_import::match_table_columns , the cfg_col->prtype does not match col->prtype .

      if (cfg_col->prtype != col->prtype) {
        ib_errf(thd, IB_LOG_LEVEL_ERROR, ER_TABLE_SCHEMA_MISMATCH,
                "Column %s precise type mismatch.", col_name);
        err = DB_ERROR;
      }

The col in the metadata cfg file is

Thread 39 "mysqld" hit Breakpoint 1, row_import::match_table_columns (this=this@entry=0x7fc2e83207a0, thd=thd@entry=0x7fc284000d20)
    at /usr/src/debug/percona-server-8.0.25-15/percona-server-8.0.25-15/storage/innobase/row/row0import.cc:1251
1251          if (cfg_col->prtype != col->prtype) {
(gdb) p *cfg_col
$2 = {
  instant_default = 0x0,
  prtype = 14681100,
  mtype = 3,
  len = 5,
  mbminmaxlen = 21,
  ind = 0,
  ord_part = 0,
  max_prefix = 0,
  is_visible = false
}

The col in the MySQL is

(gdb) p *col
$3 = {
  instant_default = 0x0,
  prtype = 525324,
  mtype = 3,
  len = 5,
  mbminmaxlen = 6,
  ind = 0,
  ord_part = 0,
  max_prefix = 0,
  is_visible = true
}

The prtype in the MySQL is 525324, but the 14681100 in the metadata cfg file.

mysql> select t.TABLE_ID, t.NAME, c.prtype from information_schema.innodb_columns c join information_schema.innodb_tables t using (TABLE_ID) where c.NAME = 'a';
+----------+--------+--------+
| TABLE_ID | NAME   | prtype |
+----------+--------+--------+
|     1145 | test/a | 525324 |
|     1147 | xb/a   | 525324 |
+----------+--------+--------+

By adding the break at dtype_form_prtype in xtrabackup, we can see the prtype is 14681100

gdb --args xtrabackup --prepare --export --target-dir=/tmp/backup

(gdb) b dd_table_load_part if table_id == 1145
Breakpoint 1 at 0x13dc580: file /usr/src/debug/percona-xtrabackup-8.0.25-17/storage/innobase/dict/dict0dd.cc, line 1097.
(gdb) run
...
Thread 1 "xtrabackup" hit Breakpoint 1, dd_table_load_part (table_id=1145, dd_table=..., dd_part=dd_part@entry=0x0, table=@0x7ffe0a977ee0: 0x0,
    schema_name=schema_name@entry=0x7ffe0a977d70, implicit=implicit@entry=true, space_id=57, thd=0x52aa180)
    at /usr/src/debug/percona-xtrabackup-8.0.25-17/storage/innobase/dict/dict0dd.cc:1097
1097    static int dd_table_load_part(table_id_t table_id, const dd::Table &dd_table,
...
(gdb) b dtype_form_prtype
Breakpoint 3 at 0x13b1df0: file /usr/src/debug/percona-xtrabackup-8.0.25-17/storage/innobase/data/data0type.cc, line 132.
(gdb) c
Continuing.
Page cleaner took 34601ms to flush 0 and evict 0 pages

Thread 1 "xtrabackup" hit Breakpoint 3, dtype_form_prtype (old_prtype=1036, charset_coll=charset_coll@entry=224)
    at /usr/src/debug/percona-xtrabackup-8.0.25-17/storage/innobase/data/data0type.cc:132
132     {
(gdb) p (old_prtype + (charset_coll << 16))
$1 = 14681100

Because the xtrabackup reads the table structure from the ibd tablespace, the charset_coll is 224 (utf8mb4_unicode_ci)

shell> ibd2sdi /var/lib/mysql/test/a.ibd | jq '.[1].object.dd_object.columns[0]' | grep collation_id
  "collation_id": 224,

The dtype_form_prtype is below:

/** Forms a precise type from the < 4.1.2 format precise type plus the
 charset-collation code.
 @return precise type, including the charset-collation code */
ulint dtype_form_prtype(
    ulint old_prtype,   /*!< in: the MySQL type code and the flags
                        DATA_BINARY_TYPE etc. */
    ulint charset_coll) /*!< in: MySQL charset-collation code */
{
  ut_a(old_prtype < 256 * 256);
  ut_a(charset_coll <= MAX_CHAR_COLL_NUM);

  return (old_prtype + (charset_coll << 16));
}

(gdb) bt
#0  dtype_form_prtype (old_prtype=1036, charset_coll=charset_coll@entry=224) at /usr/src/debug/percona-xtrabackup-8.0.25-17/storage/innobase/data/data0type.cc:132
#1  0x00000000013d9d26 in dd_table_create_on_dd_obj (dd_table=dd_table@entry=0x5e661a0, dd_part=dd_part@entry=0x0, schema_name=schema_name@entry=0x7ffcd59826b0,
    is_implicit=is_implicit@entry=true, space_id=space_id@entry=51) at /usr/src/debug/percona-xtrabackup-8.0.25-17/storage/innobase/dict/dict0dd.cc:747
#2  0x00000000013dc80f in dd_table_load_part (table_id=1145, dd_table=..., dd_part=dd_part@entry=0x0, table=@0x7ffcd5982820: 0x0, schema_name=schema_name@entry=0x7ffcd59826b0,
    implicit=implicit@entry=true, space_id=<optimized out>, thd=0x5e5db10) at /usr/src/debug/percona-xtrabackup-8.0.25-17/storage/innobase/dict/dict0dd.cc:1121
#3  0x00000000013dc883 in dd_table_load_on_dd_obj (client=client@entry=0x5e61260, space_id=space_id@entry=51, dd_table=..., table=@0x7ffcd5982820: 0x0, thd=thd@entry=0x5e5db10,
    schema_name=schema_name@entry=0x7ffcd59826b0, implicit=true) at /usr/src/debug/percona-xtrabackup-8.0.25-17/storage/innobase/dict/dict0dd.cc:1189
#4  0x0000000000cefccf in dict_load_tables_from_space_id (space_id=space_id@entry=51, thd=thd@entry=0x5e5db10, trx=trx@entry=0x5434798)
    at /usr/src/debug/percona-xtrabackup-8.0.25-17/storage/innobase/xtrabackup/src/xtrabackup.cc:2245
#5  0x0000000000cf01ea in dict_load_from_spaces_sdi () at /usr/src/debug/percona-xtrabackup-8.0.25-17/storage/innobase/xtrabackup/src/xtrabackup.cc:2303
#6  innodb_init (init_dd=<optimized out>, for_apply_log=<optimized out>) at /usr/src/debug/percona-xtrabackup-8.0.25-17/storage/innobase/xtrabackup/src/xtrabackup.cc:2349
#7  0x0000000000cf1c95 in xtrabackup_prepare_func (argc=5, argv=0x53b6450) at /usr/src/debug/percona-xtrabackup-8.0.25-17/storage/innobase/xtrabackup/src/xtrabackup.cc:6593
#8  0x0000000000cb112e in main (argc=<optimized out>, argv=<optimized out>) at /usr/src/debug/percona-xtrabackup-8.0.25-17/storage/innobase/xtrabackup/src/xtrabackup.cc:7668

Environment

None

AFFECTED CS IDs

CS0045899, CS0045636

Linked issues

relates to

PS-9219

MySQL converts collation of date data type in ibd but data dictionary

Activity

Show:

Venkatesh Prasad May 17, 2024 at 1:38 PM

This is a bug in server and is being handled as part of https://perconadev.atlassian.net/browse/PS-9219.

With the draft patch for https://perconadev.atlassian.net/browse/PS-9219, import tablespace is successful and no longer errors out with Column %s precise type mismatch.

More testing is in progress.

1. Create table, backup and prepare

2. Create database xb
mysql> create database xb;
Query OK, 1 row affected (0.00 sec)

3. Issue CREATE TABLE LIKE 
mysql> create table xb.a like test.a;
Query OK, 1 row affected (0.00 sec)

mysql> alter table xb.a discard tablespace;
Query OK, 0 rows affected (0.01 sec)

# Copy ibd and .cfg files from the backup to the datadir
$ cp backup/test/a.* /home/venki/work/ps/80/build/mysql-test/var/mysqld.1/data/xb/

mysql> alter table xb.a import tablespace;
Query OK, 0 rows affected (0.02 sec)

mysql> show create table xb.a\G
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `dt` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

Done

Details
Assignee
Venkatesh Prasad
Reporter
jinyou.ma
Needs QA
Yes
Sprint
None
Fix versions
8.4.0-1 (Q2 2024)
8.0.35-31 (Q2 2024)
Affects versions
8.0.35-30
Priority
Medium

Smart Checklist

Created April 30, 2024 at 5:35 AM

Updated July 9, 2024 at 7:42 AM

Resolved May 24, 2024 at 9:26 AM

Configure

Column %s precise type mismatch error while importing a tablespace

Description

Description

Reproduce

Debug

Environment

AFFECTED CS IDs

Linked issues

relates to

Activity

Venkatesh Prasad May 17, 2024 at 1:38 PM

DetailsAssigneeVenkatesh PrasadVenkatesh PrasadReporterjinyou.majinyou.maNeeds QAYesSprintNone+1Fix versions8.4.0-1 (Q2 2024)8.0.35-31 (Q2 2024)Affects versions8.0.35-30PriorityMedium

Details

Assignee

Reporter

Needs QA

Sprint

Fix versions

Affects versions

Priority

Smart ChecklistOpen Smart Checklist

Smart Checklist

Details
Assignee
Venkatesh Prasad
Reporter
jinyou.ma
Needs QA
Yes
Sprint
None
Fix versions
8.4.0-1 (Q2 2024)
8.0.35-31 (Q2 2024)
Affects versions
8.0.35-30
Priority
Medium

Smart Checklist