Use of uninitialized value in join or string at /bin/pt-online-schema-change

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

Hi,

Can you share the output of a SHOW CREATE TABLE board_wcf.wcf1_conversation_to_user ?

Yes I can. =)

SHOW CREATE TABLE board_wcf.wcf1_conversation_to_user
±--------------------------±----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±--------------------------±----------------------------------------------------------------------------------------------------------------------------------------------------------+
| wcf1_conversation_to_user | CREATE TABLE wcf1_conversation_to_user ( |
| | conversationID int(10) NOT NULL, |
| | participantID int(10) DEFAULT NULL, |
| | username varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’, |
| | hideConversation tinyint(1) NOT NULL DEFAULT ‘0’, |
| | isInvisible tinyint(1) NOT NULL DEFAULT ‘0’, |
| | lastVisitTime int(10) NOT NULL DEFAULT ‘0’, |
| | joinedAt int(10) NOT NULL DEFAULT ‘0’, |
| | leftAt int(10) NOT NULL DEFAULT ‘0’, |
| | lastMessageID int(10) DEFAULT NULL, |
| | UNIQUE KEY participantID (participantID,conversationID), |
| | KEY participantID_2 (participantID,hideConversation), |
| | KEY 01e53cf51b570bae4ddcae0a765ea835_fk (conversationID), |
| | KEY befb99cae7d8abd731273eace0406910_fk (lastMessageID), |
| | CONSTRAINT 01e53cf51b570bae4ddcae0a765ea835_fk FOREIGN KEY (conversationID) REFERENCES wcf1_conversation (conversationID) ON DELETE CASCADE, |
| | CONSTRAINT befb99cae7d8abd731273eace0406910_fk FOREIGN KEY (lastMessageID) REFERENCES wcf1_conversation_message (messageID) ON DELETE SET NULL, |
| | CONSTRAINT d52ac392b78c38925600cb02426d7af4_fk FOREIGN KEY (participantID) REFERENCES wcf1_user (userID) ON DELETE SET NULL |
| | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
±--------------------------±----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
Time: 0.008s

show index from board_wcf.wcf1_conversation_to_user
±--------------------------±-----------±------------------------------------±-------------±-----------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
±--------------------------±-----------±------------------------------------±-------------±-----------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
| wcf1_conversation_to_user | 0 | participantID | 1 | participantID | A | 3242 | | | YES | BTREE | | |
| wcf1_conversation_to_user | 0 | participantID | 2 | conversationID | A | 5965 | | | | BTREE | | |
| wcf1_conversation_to_user | 1 | participantID_2 | 1 | participantID | A | 3242 | | | YES | BTREE | | |
| wcf1_conversation_to_user | 1 | participantID_2 | 2 | hideConversation | A | 3258 | | | | BTREE | | |
| wcf1_conversation_to_user | 1 | 01e53cf51b570bae4ddcae0a765ea835_fk | 1 | conversationID | A | 2737 | | | | BTREE | | |
| wcf1_conversation_to_user | 1 | befb99cae7d8abd731273eace0406910_fk | 1 | lastMessageID | A | 1 | | | YES | BTREE | | |
±--------------------------±-----------±------------------------------------±-------------±-----------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
6 rows in set
Time: 0.010s