Hi everybody
we are trying to apply migrations to a small forum database but sadly get confusing error messages.
Database:
percona xtradb - 5.7.16-10-57-log on Centos 7.4
The table:
±-----------------±-------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-----------------±-------------±-----±----±--------±------+
| conversationID | int(10) | NO | MUL | NULL | |
| participantID | int(10) | YES | MUL | NULL | |
| username | varchar(255) | NO | | | |
| hideConversation | tinyint(1) | NO | | 0 | |
| isInvisible | tinyint(1) | NO | | 0 | |
| lastVisitTime | int(10) | NO | | 0 | |
| joinedAt | int(10) | NO | | 0 | |
| leftAt | int(10) | NO | | 0 | |
| lastMessageID | int(10) | YES | MUL | NULL | |
±-----------------±-------------±-----±----±--------±------+
The original query:
ALTER TABLE wcf1_conversation_to_user ADD COLUMN conversationToUserID INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT;
That should work:
PTDEBUG=1 pt-online-schema-change --execute “ADD COLUMN conversationToUserID INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT” D=board_wcf,t=wcf1_conversation_to_user
But doesnt:
<snip lots of seemingly unimportend stuff to get under the 10000 char limit>
/usr/bin/perl 5.016003
Linux exampledb01.not.real 3.10.0-693.21.1.el7.x86_64 #1 SMP Wed Mar 7 19:03:37 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
Arguments: [–execute] [–alter] [ADD COLUMN conversationToUserID INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT] [D=board_wcf,t=wcf1_conversation_to_user]
…
NibbleIterator:5971 11747 EXPLAIN SELECT * FROM board_wcf.wcf1_conversation_to_user WHERE 1=1
NibbleIterator:5973 11747 $VAR1 = {
extra => undef,
filtered => ‘100.00’,
id => ‘1’,
key => undef,
key_len => undef,
partitions => undef,
possible_keys => undef,
ref => undef,
rows => ‘6059’,
select_type => ‘SIMPLE’,
table => ‘wcf1_conversation_to_user’,
type => ‘ALL’
};
…
NibbleIterator:5521 11747 Ascend params: $VAR1 = {
boundaries => {
‘<’ => ‘((((? IS NOT NULL AND participantid IS NULL) OR (participantid < ?))) OR (((? IS NULL AND participantid IS NULL) OR (participantid = ?)) AND conversationid < ?))’,
‘<=’ => ‘((((? IS NOT NULL AND participantid IS NULL) OR (participantid < ?))) OR (((? IS NULL AND participantid IS NULL) OR (participantid = ?)) AND conversationid <= ?))’,
‘>’ => ‘((((? IS NULL AND participantid IS NOT NULL) OR (participantid > ?))) OR (((? IS NULL AND participantid IS NULL) OR (participantid = ?)) AND conversationid > ?))’,
‘>=’ => ‘((((? IS NULL AND participantid IS NOT NULL) OR (participantid > ?))) OR (((? IS NULL AND participantid IS NULL) OR (participantid = ?)) AND conversationid >= ?))’
},
cols => [
‘conversationid’,
‘participantid’,
‘username’,
‘hideconversation’,
‘isinvisible’,
‘lastvisittime’,
‘joinedat’,
‘leftat’,
‘lastmessageid’
],
index => ‘participantid’,
scols => [
‘participantid’,
‘participantid’,
‘participantid’,
‘participantid’,
‘conversationid’
],
slice => [
1,
1,
1,
1,
0
],
where => ‘((((? IS NULL AND participantid IS NOT NULL) OR (participantid > ?))) OR (((? IS NULL AND participantid IS NULL) OR (participantid = ?)) AND conversationid > ?))’
};
NibbleIterator:5538 11747 First lower boundary statement: SELECT /*!40001 SQL_NO_CACHE */ participantid, participantid, participantid, participantid, conversationid FROM board_wcf.wcf1_conversation_to_user FORCE INDEX(participantid) ORDER BY participantid, conversationid LIMIT 1 /first lower boundary/
NibbleIterator:5563 11747 Last upper boundary statement: SELECT /*!40001 SQL_NO_CACHE */ participantid, participantid, participantid, participantid, conversationid FROM board_wcf.wcf1_conversation_to_user FORCE INDEX(participantid) ORDER BY participantid DESC,conversationid DESC LIMIT 1 /last upper boundary/
NibbleIterator:5574 11747 Upper boundary statement: SELECT /*!40001 SQL_NO_CACHE */ participantid, participantid, participantid, participantid, conversationid FROM board_wcf.wcf1_conversation_to_user FORCE INDEX(participantid) WHERE ((((? IS NULL AND participantid IS NOT NULL) OR (participantid > ?))) OR (((? IS NULL AND participantid IS NULL) OR (participantid = ?)) AND conversationid >= ?)) ORDER BY participantid, conversationid LIMIT ?, 2 /next chunk boundary/
…
NibbleIterator:5603 11747 Initial chunk size (LIMIT): 999
NibbleIterator:5983 11747 Preparing statement handles
NibbleIterator:6012 11747 First lower boundary: $VAR1 = [
undef,
undef,
undef,
undef,
‘1’
];
NibbleIterator:6028 11747 Next lower boundary: $VAR1 = [
undef,
undef,
undef,
undef,
‘1’
];
2018-06-04T13:07:38 Copying approximately 6059 rows…
IndexLength:6678 11747 SELECT /*!40001 SQL_NO_CACHE */ participantid, conversationid FROM board_wcf.wcf1_conversation_to_user FORCE INDEX (participantid) WHERE participantid IS NOT NULL AND conversationid IS NOT NULL ORDER BY participantid, conversationid LIMIT 1 /key_len/
IndexLength:6645 11747 EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ * FROM board_wcf.wcf1_conversation_to_user FORCE INDEX (participantid) WHERE participantid = ? AND conversationid >= ? /key_len/ params: 1 1
IndexLength:6649 11747 Range scan: $VAR1 = {
extra => ‘Using index condition’,
filtered => ‘100.00’,
id => ‘1’,
key => ‘participantID’,
key_len => ‘9’,
partitions => undef,
possible_keys => ‘participantID’,
ref => undef,
rows => ‘8’,
select_type => ‘SIMPLE’,
table => ‘wcf1_conversation_to_user’,
type => ‘range’
};
NibbleIterator:5657 11747 init callback returned 1
pt_online_schema_change:11489 11747 EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ participantid, participantid, participantid, participantid, conversationid FROM board_wcf.wcf1_conversation_to_user FORCE INDEX(participantid) WHERE ((((? IS NULL AND participantid IS NOT NULL) OR (participantid > ?))) OR (((? IS NULL AND participantid IS NULL) OR (participantid = ?)) AND conversationid >= ?)) ORDER BY participantid, conversationid LIMIT ?, 2 /next chunk boundary/ params: undef undef undef undef 1 999
pt_online_schema_change:11498 11747 EXPLAIN plan: $VAR1 = {
extra => ‘Using where; Using index’,
filtered => ‘100.00’,
id => ‘1’,
key => ‘participantID’,
key_len => ‘9’,
partitions => undef,
possible_keys => ‘participantID’,
ref => undef,
rows => ‘3236’,
select_type => ‘SIMPLE’,
table => ‘wcf1_conversation_to_user’,
type => ‘range’
};
NibbleIterator:6083 11747 next_boundaries callback returned 1
CleanupTask:6551 11747 Calling cleanup task CODE(0x3159090)
…
2018-06-04T13:07:38 Error copying rows from board_wcf.wcf1_conversation_to_user to board_wcf._wcf1_conversation_to_user_new: Use of uninitialized value in join or string at /bin/pt-online-schema-change line 6092.
…
Cxn:4096 11747 Destroying cxn
Cxn:4105 11747 DBI::db=HASH(0x3091470) Disconnecting dbh on exampledb01.not.real
It seems the script stumbles over the ‘undefined’ results from the boundry queries:
Query:
SELECT /*!40001 SQL_NO_CACHE */ participantid, participantid, participantid, participantid, conversationid FROM board_wcf.wcf1_conversation_to_user FORCE INDEX(participantid) ORDER BY participantid, conversationid LIMIT 1
Result:
$VAR1 = [
undef,
undef,
undef,
undef,
‘1’
];
But we don’t really know what to do with this information.
We tried with Percona toolkit 2.2.10 and 3.0.10, same problem.
Does anyone has an idea whats amiss?
Thanks
Tarwin