2013-10-31 08:52:19 |
Muhammad Irfan |
description |
Here's what I have so far. I have 2 table dumps, a1 and a2. With a1, you can get skipped chunks and infinite loop errors. Though sometimes, checksum for a1 completes successfully. As for a2, I was only able to narrow it down to 200000 rows.
DROP TABLE IF EXISTS `a1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `a1` (
`word` varchar(50) NOT NULL DEFAULT '',
`sid` int(10) unsigned NOT NULL DEFAULT '0',
`type` varchar(16) DEFAULT NULL,
`score` float DEFAULT NULL,
UNIQUE KEY `word_sid_type` (`word`,`sid`,`type`),
KEY `sid_type` (`sid`,`type`),
KEY `word` (`word`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `a2`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `a2` (
`word` varchar(50) NOT NULL DEFAULT '',
`sid` int(10) unsigned NOT NULL DEFAULT '0',
`type` varchar(16) DEFAULT NULL,
`score` float DEFAULT NULL,
UNIQUE KEY `word_sid_type` (`word`,`sid`,`type`),
KEY `sid_type` (`sid`,`type`),
KEY `word` (`word`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
[user@localhost ~]$ pt-table-checksum --recursion-method=dsn=h=127.0.0.1,D=percona,t=dsns,P=22987 --databases=test3 --host=127.0.0.1 --port=22987 --empty-replicate-table
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
LANGUAGE = (unset),
LC_ALL = (unset),
LC_CTYPE = "UTF-8",
LANG = "en_US.UTF-8"
are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
10-01T11:01:56 Skipping chunk 1 of test3.a1 because MySQL chose no index instead of the word_sid_typeindex.
10-01T11:01:56 Error checksumming table test3.a1: Possible infinite loop detected! The lower boundary for chunk 2 is <???, ???, 187641, ???, 187641, node, node> and the lower boundary for chunk 3 is also <???, ???, 187641, ???, 187641, node, node>. This usually happens when using a non-unique single column index. The current chunk index for table test3.a1 is word_sid_type which is unique and covers 3 columns.
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
10-01T11:01:56 1 0 0 2 2 0.751 test3.a1
Checksumming test3.a2: 30% 01:08 remain
10-01T11:02:41 Skipping chunk 49 of test3.a2 because MySQL chose no index instead of the word_sid_typeindex.
^C# Caught SIGINT.
10-01T11:05:25 0 0 8431156 301 92 208.889 test3.a2 |
I encountered this problem with version 2.2.4
Here's what I have so far. I have 2 table dumps, a1 and a2. With a1, you can get skipped chunks and infinite loop errors. Though sometimes, checksum for a1 completes successfully. As for a2, I was only able to narrow it down to 200000 rows.
DROP TABLE IF EXISTS `a1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `a1` (
`word` varchar(50) NOT NULL DEFAULT '',
`sid` int(10) unsigned NOT NULL DEFAULT '0',
`type` varchar(16) DEFAULT NULL,
`score` float DEFAULT NULL,
UNIQUE KEY `word_sid_type` (`word`,`sid`,`type`),
KEY `sid_type` (`sid`,`type`),
KEY `word` (`word`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
DROP TABLE IF EXISTS `a2`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `a2` (
`word` varchar(50) NOT NULL DEFAULT '',
`sid` int(10) unsigned NOT NULL DEFAULT '0',
`type` varchar(16) DEFAULT NULL,
`score` float DEFAULT NULL,
UNIQUE KEY `word_sid_type` (`word`,`sid`,`type`),
KEY `sid_type` (`sid`,`type`),
KEY `word` (`word`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
[user@localhost ~]$ pt-table-checksum --recursion-method=dsn=h=127.0.0.1,D=percona,t=dsns,P=22987 --databases=test3 --host=127.0.0.1 --port=22987 --empty-replicate-table
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
LANGUAGE = (unset),
LC_ALL = (unset),
LC_CTYPE = "UTF-8",
LANG = "en_US.UTF-8"
are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
10-01T11:01:56 Skipping chunk 1 of test3.a1 because MySQL chose no index instead of the word_sid_typeindex.
10-01T11:01:56 Error checksumming table test3.a1: Possible infinite loop detected! The lower boundary for chunk 2 is <???, ???, 187641, ???, 187641, node, node> and the lower boundary for chunk 3 is also <???, ???, 187641, ???, 187641, node, node>. This usually happens when using a non-unique single column index. The current chunk index for table test3.a1 is word_sid_type which is unique and covers 3 columns.
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
10-01T11:01:56 1 0 0 2 2 0.751 test3.a1
Checksumming test3.a2: 30% 01:08 remain
10-01T11:02:41 Skipping chunk 49 of test3.a2 because MySQL chose no index instead of the word_sid_typeindex.
^C# Caught SIGINT.
10-01T11:05:25 0 0 8431156 301 92 208.889 test3.a2 |
|