Not the answer you need?
Register and ask your own question!

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

tarwintarwin EntrantCurrent User Role Beginner
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: __ __ _[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

Comments

  • carlos.salguerocarlos.salguero Percona Toolkit Developer Percona Staff Role
    Hi,

    Can you share the output of a SHOW CREATE TABLE board_wcf.wcf1_conversation_to_user ?
  • tarwintarwin Entrant Current User Role Beginner
    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 | <null> | <null> | YES | BTREE | | |
    | wcf1_conversation_to_user | 0 | participantID | 2 | conversationID | A | 5965 | <null> | <null> | | BTREE | | |
    | wcf1_conversation_to_user | 1 | participantID_2 | 1 | participantID | A | 3242 | <null> | <null> | YES | BTREE | | |
    | wcf1_conversation_to_user | 1 | participantID_2 | 2 | hideConversation | A | 3258 | <null> | <null> | | BTREE | | |
    | wcf1_conversation_to_user | 1 | 01e53cf51b570bae4ddcae0a765ea835_fk | 1 | conversationID | A | 2737 | <null> | <null> | | BTREE | | |
    | wcf1_conversation_to_user | 1 | befb99cae7d8abd731273eace0406910_fk | 1 | lastMessageID | A | 1 | <null> | <null> | YES | BTREE | | |
    +
    +
    +
    +
    +
    +
    +
    +
    +
    +
    +
    +
    +
    +
    6 rows in set
    Time: 0.010s
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.