Using a unique multi-column index which have numeric and string/binary columns fails with "Truncated incorrect DOUBLE value" error

General

Escalation

General

Escalation

Description

Here's how to reproduce the issue. Create tables with multi-column unique index containing numeric and string/binary columns.

Table creation details:

DROP DATABASE test;
CREATE TABLE t2(a int null, b varchar(20), c int, unique key uk(b,a));

Generate random data:

mysql_random_data_load test t2 100000 --host=127.0.0.1 --port=5731

Run pt-table-checksum:

$ pt-table-checksum --tables=t2 h=127.0.0.1,P=5731,D=test
Checking if all tables can be checksummed ...
Starting checksum ...
Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information.
10-06T09:26:47 Error checksumming table test.t2: Error executing checksum query: DBD::mysql::st execute failed: Truncated incorrect DOUBLE value: 'Aaron Alexander' [for Statement "REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT ?, ?, ?, ?, ?, ?, COUNT(*), '0' FROM `test`.`t2` FORCE INDEX(`uk`) WHERE (((? IS NOT NULL AND `b` IS NULL) OR (`b` < ?)) OR ((? IS NOT NULL AND `a` IS NULL) OR (`a` < ?)) OR (((? IS NULL AND `b` IS NULL) OR (`b` = ?)))) ORDER BY `b`, `a` /*past lower chunk*/" with ParamValues: 0='test', 1='t2', 2=4, 3='uk', 4=undef, 5='Aaron Alexander,Aaron Alexander,Aaron Alexander,Aaron Alexander,1122259042,1122259042', 6='Aaron Alexander', 7='Aaron Alexander', 8='Aaron Alexander', 9='Aaron Alexander', 10=1122259042, 11=1122259042] at /usr/bin/pt-table-checksum line 11791.

 TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
10-06T09:26:47 1 0 100000 0 4 0 0.351 test.t2
$ pt-table-checksum --binary-index --tables=t2 h=127.0.0.1,P=5731,D=test
Checking if all tables can be checksummed ...
Starting checksum ...
Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information.
10-06T09:26:54 Error checksumming table test.t2: Error executing checksum query: DBD::mysql::st execute failed: Truncated incorrect DOUBLE value: 'Aaron Alexander' [for Statement "REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT ?, ?, ?, ?, ?, ?, COUNT(*), '0' FROM `test`.`t2` FORCE INDEX(`uk`) WHERE (((? IS NOT NULL AND `b` IS NULL) OR (`b` < ?)) OR ((? IS NOT NULL AND `a` IS NULL) OR (`a` < ?)) OR (((? IS NULL AND `b` IS NULL) OR (`b` = ?)))) ORDER BY `b`, `a` /*past lower chunk*/" with ParamValues: 0='test', 1='t2', 2=4, 3='uk', 4=undef, 5='Aaron Alexander,Aaron Alexander,Aaron Alexander,Aaron Alexander,1122259042,1122259042', 6='Aaron Alexander', 7='Aaron Alexander', 8='Aaron Alexander', 9='Aaron Alexander', 10=1122259042, 11=1122259042] at /usr/bin/pt-table-checksum line 11791.

 TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
10-06T09:26:54 1 0 100000 0 4 0 0.378 test.t2

This is the offending query:

REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'test', 't2', '4', 'uk', NULL, 'Aaron Alexander,Aaron Alexander,Aaron Alexander,Aaron Alexander,1122259042,1122259042', COUNT(*), '0' FROM `test`.`t2` FORCE INDEX(`uk`) WHERE ((('Aaron Alexander' IS NOT NULL AND `b` IS NULL) OR (`b` < 'Aaron Alexander')) OR (('Aaron Alexander' IS NOT NULL AND `a` IS NULL) OR (`a` < 'Aaron Alexander')) OR ((('1122259042' IS NULL AND `b` IS NULL) OR (`b` = '1122259042')))) ORDER BY `b`, `a` /*past lower chunk*/

To break it down further, this is the offending condition is "`a` < 'Aaron Alexander'" and it makes sense because a column is an integer:

mysql> SELECT * FROM t2 WHERE a < 'Aaron Alexander';
Empty set, 1 warning (0.08 sec)
mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'Aaron Alexander' |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

The only workaround, though inefficient is to use chunk-index-columns:

pt-table-checksum --chunk-index-columns=1 --tables=t2 h=127.0.0.1,P=5731,D=test
Checking if all tables can be checksummed ...
Starting checksum ...
Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information.
 TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
10-06T09:32:18 0 0 100004 0 5 0 0.382 test.t2

Environment

None

AFFECTED CS IDs

CS0012951

Smart Checklist

Activity

Show:

Kevin Swift December 3, 2024 at 12:50 PM

Also forgot to add, we tested 3.2.1 and 3.6.0 Percona tools and the problem occurs in both of these.

Kevin Swift December 3, 2024 at 12:31 PM

We are seeing a similar error, on this table

CREATE TABLE searchables_myisam (
acl_id int(11) DEFAULT NULL,
owner_id int(11) DEFAULT NULL,
came_from_migration int(11) DEFAULT NULL,
search_text text NOT NULL,
display_name varchar(255) DEFAULT NULL,
source_id int(11) DEFAULT NULL,
source_type varchar(255) DEFAULT NULL,
import_job_id int(11) NOT NULL DEFAULT 0,
icon varchar(255) DEFAULT NULL,
object_ref varchar(255) DEFAULT NULL,
soundex_code varchar(255) NOT NULL DEFAULT '',
simplified_name varchar(255) DEFAULT NULL,
UNIQUE KEY index_searchables_on_source_type_and_source_id (source_type,source_id),
KEY index_searchables_on_acl_id (acl_id),
KEY index_searchables_on_owner_id (owner_id),
KEY index_searchables_on_object_ref (object_ref),
KEY index_searchables_on_soundex_code (soundex_code),
KEY index_searchables_on_simplified_name (simplified_name),
KEY index_on_import_job_id (import_job_id),
FULLTEXT KEY search_text (search_text)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci

$ pt-table-checksum -uroot -pabc123 --no-check-replication-filters --no-check-binlog-format --noreplicate-check --recursion-method dsn=D=percona,t=dsns --chunk-time=1 --replicate=percona.test04_checksums --ignore-tables-regex="data_view_[0-9]+" --no-check-binlog-format --databases='private_356486_production' --tables='searchables_myisam'
Checking if all tables can be checksummed ...
Starting checksum ...
12-03T11:49:25 Error checksumming table private_356486_production.searchables_myisam: Error executing checksum query: Checksum query for table private_356486_production.searchables_myisam caused MySQL error 1292:
Level: Warning
Code: 1292
Message: Truncated incorrect DECIMAL value: 'Private::Crm::Organisation'
Query: REPLACE INTO percona.test04_checksums (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT ?, ?, ?, ?, ?, ?, COUNT(*), '0' FROM private_356486_production.searchables_myisam FORCE INDEX(index_searchables_on_source_type_and_source_id) WHERE (((? IS NOT NULL AND source_type IS NULL) OR (source_type < ?)) OR ((? IS NOT NULL AND source_id IS NULL) OR (source_id < ?)) OR (((? IS NULL AND source_type IS NULL) OR (source_type = ?)))) ORDER BY source_type, source_id /past lower chunk/

TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE

12-03T11:49:25 1 0 1330212 0 9 0 15.745 private_356486_production.searchables_myisam

The query that fails is this one, which appears to be the last one in the sequence of chunk checksums

REPLACE INTO `percona`.`test04_checksums` (
  db,
  tbl,
  chunk,
  chunk_index,
  lower_boundary,
  upper_boundary,
  this_cnt,
  this_crc
)
SELECT 'private_356486_production',
  'searchables_myisam',
  '10',
  'index_searchables_on_source_type_and_source_id',
  NULL,
  'Private::Crm::Organisation,Private::Crm::Organisation,Private::Crm::Organisation,Private::Crm::Organisation,1,1',
  COUNT(*),
  '0'
FROM `private_356486_production`.`searchables_myisam` FORCE INDEX(`index_searchables_on_source_type_and_source_id`)
WHERE (
    (
      (
        'Private::Crm::Organisation' IS NOT NULL
        AND `source_type` IS NULL
      )
      OR (`source_type` < 'Private::Crm::Organisation')
    )
    OR (w
      (
        'Private::Crm::Organisation' IS NOT NULL
        AND `source_id` IS NULL
      )
      OR (`source_id` < 'Private::Crm::Organisation')
    )
    OR (
      (
        (
          '1' IS NULL
          AND `source_type` IS NULL
        )
        OR (`source_type` = '1')
      )
    )
  )
ORDER BY `source_type`,
  `source_id`
  /*past lower chunk*/

This contains `source_id` < 'Private::Crm::Organisation' which produces the warning

+---------+------+-----------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------+ | Warning | 1292 | Truncated incorrect DECIMAL value: 'Private::Crm::Organisation' | +---------+------+-----------------------------------------------------------------+

FYI, adding this warning to the ignore list in the code does work around the problem.

This error does not appear on all tables like this so I think the data present in the table affects if the error occurs.

This is on mariadb 10.11.9 on ubuntu 24.04

PTDEBUG and server query logs available if helpful.

Details
Assignee
Unassigned
Reporter
Jaime Sicam
Priority
Medium
Components
pt-table-checksum
Affects versions
3.2.1
Fix versions
3.7.NEXT

Smart Checklist

Created October 6, 2020 at 1:35 AM

Updated December 3, 2024 at 12:50 PM

Configure

Using a unique multi-column index which have numeric and string/binary columns fails with "Truncated incorrect DOUBLE value" error

Description

Environment

AFFECTED CS IDs

Smart Checklist

Activity

Kevin Swift December 3, 2024 at 12:50 PM

Kevin Swift December 3, 2024 at 12:31 PM

DetailsAssigneeUnassignedUnassignedReporterJaime SicamJaime SicamPriorityMediumComponentspt-table-checksumAffects versions3.2.1Fix versions3.7.NEXT

Details

Assignee

Reporter

Priority

Components

Affects versions

Fix versions

Smart ChecklistOpen Smart Checklist

Smart Checklist

Details
Assignee
Unassigned
Reporter
Jaime Sicam
Priority
Medium
Components
pt-table-checksum
Affects versions
3.2.1
Fix versions
3.7.NEXT

Smart Checklist