pt-online-schema-change 2.1.1 doesn't choose the PRIMARY KEY
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Toolkit moved to https://jira.percona.com/projects/PT |
Fix Released
|
Medium
|
Daniel Nichter | ||
2.1 |
Fix Released
|
Medium
|
Daniel Nichter |
Bug Description
Hi
I am using pt-online-
Create Table: CREATE TABLE `CityPopularity` (
`ufi` int(11) NOT NULL,
`guest_language` char(2) NOT NULL,
`guest_country` char(2) NOT NULL,
`score` int(10) unsigned NOT NULL,
PRIMARY KEY (`ufi`,
KEY `guest_language` (`guest_
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Here is the complete output:
[root@server-03 app]# PTDEBUG=1 pt-online-
# /usr/bin/perl 5.008008
# Linux server-03 2.6.18-238.el5 #1 SMP Thu Jan 13 15:51:15 EST 2011 x86_64 x86_64 x86_64 GNU/Linux
# Arguments: _[--alter]_ _[add guy char(2)]_ _[--execute]_ _[D=app,
# OptionParser:190 14514 Option rule: --dry-run and --execute are mutually exclusive.
# OptionParser:190 14514 Option rule: This tool accepts additional command-line arguments. Refer to the SYNOPSIS and usage information for details.
# OptionParser:199 14514 =item --alter
# OptionParser:226 14514 Short help: The schema modification, without the ALTER TABLE keywords
# OptionParser:199 14514 =item --alter-
# OptionParser:226 14514 Short help: How to modify foreign keys so they reference the new table
# OptionParser:199 14514 =item --ask-pass
# OptionParser:218 14514 Option has no attributes
# OptionParser:226 14514 Short help: Prompt for a password when connecting to MySQL
# OptionParser:199 14514 =item --charset
# OptionParser:226 14514 Short help: Default character set
# OptionParser:199 14514 =item --check-interval
# OptionParser:226 14514 Short help: Sleep time between checks for --max-lag
# OptionParser:199 14514 =item --[no]check-
# OptionParser:226 14514 Short help: Abort if any replication filter is set on any server
# OptionParser:199 14514 =item --check-slave-lag
# OptionParser:226 14514 Short help: Pause the data copy until this replica's lag is less than --max-lag
# OptionParser:199 14514 =item --chunk-index
# OptionParser:226 14514 Short help: Prefer this index for chunking tables
# OptionParser:199 14514 =item --chunk-size
# OptionParser:226 14514 Short help: Number of rows to select for each chunk copied
# OptionParser:199 14514 =item --chunk-size-limit
# OptionParser:226 14514 Short help: Do not copy chunks this much larger than the desired chunk size
# OptionParser:199 14514 =item --chunk-time
# OptionParser:226 14514 Short help: Adjust the chunk size dynamically so each data-copy query takes this long to execute
# OptionParser:199 14514 =item --config
# OptionParser:226 14514 Short help: Read this comma-separated list of config files; if specified, this must be the first option on the command line
# OptionParser:199 14514 =item --critical-load
# OptionParser:226 14514 Short help: Examine SHOW GLOBAL STATUS after every chunk, and abort if the load is too high
# OptionParser:199 14514 =item --defaults-file
# OptionParser:226 14514 Short help: Only read mysql options from the given file
# OptionParser:199 14514 =item --[no]drop-
# OptionParser:226 14514 Short help: Drop the original table after renaming it
# OptionParser:199 14514 =item --dry-run
# OptionParser:218 14514 Option has no attributes
# OptionParser:226 14514 Short help: Create and alter the new table, but do not create triggers, copy data, or replace the original table
# OptionParser:199 14514 =item --execute
# OptionParser:218 14514 Option has no attributes
# OptionParser:226 14514 Short help: Indicate that you have read the documentation and want to alter the table
# OptionParser:199 14514 =item --help
# OptionParser:218 14514 Option has no attributes
# OptionParser:226 14514 Short help: Show help and exit
# OptionParser:199 14514 =item --host
# OptionParser:226 14514 Short help: Connect to host
# OptionParser:199 14514 =item --lock-wait-timeout
# OptionParser:226 14514 Short help: Set the session value of innodb_
# OptionParser:199 14514 =item --max-lag
# OptionParser:226 14514 Short help: Pause the data copy until all replicas' lag is less than this value
# OptionParser:199 14514 =item --max-load
# OptionParser:226 14514 Short help: Examine SHOW GLOBAL STATUS after every chunk, and pause if any status variables are higher than their thresholds
# OptionParser:199 14514 =item --password
# OptionParser:226 14514 Short help: Password to use when connecting
# OptionParser:199 14514 =item --pid
# OptionParser:226 14514 Short help: Create the given PID file
# OptionParser:199 14514 =item --port
# OptionParser:226 14514 Short help: Port number to use for connection
# OptionParser:199 14514 =item --print
# OptionParser:218 14514 Option has no attributes
# OptionParser:226 14514 Short help: Print SQL statements to STDOUT
# OptionParser:199 14514 =item --progress
# OptionParser:226 14514 Short help: Print progress reports to STDERR while copying rows
# OptionParser:199 14514 =item --quiet
# OptionParser:226 14514 Short help: Do not print messages to STDOUT
# OptionParser:199 14514 =item --recurse
# OptionParser:226 14514 Short help: Number of levels to recurse in the hierarchy when discovering replicas
# OptionParser:199 14514 =item --recursion-method
# OptionParser:226 14514 Short help: Preferred recursion method for discovering replicas
# OptionParser:199 14514 =item --retries
# OptionParser:226 14514 Short help: Retry a chunk this many times when there is a nonfatal error
# OptionParser:199 14514 =item --set-vars
# OptionParser:226 14514 Short help: Set these MySQL variables
# OptionParser:199 14514 =item --socket
# OptionParser:226 14514 Short help: Socket file to use for connection
# OptionParser:199 14514 =item --[no]swap-tables
# OptionParser:226 14514 Short help: Swap the original table and the new, altered table
# OptionParser:199 14514 =item --user
# OptionParser:226 14514 Short help: User for login if not current user
# OptionParser:199 14514 =item --version
# OptionParser:218 14514 Option has no attributes
# OptionParser:226 14514 Short help: Show version and exit
# OptionParser:265 14514 Parsing opt spec: group => default spec => alter=s desc => The schema modification, without the ALTER TABLE keywords
# OptionParser:303 14514 alter type: s
# OptionParser:265 14514 Parsing opt spec: group => default spec => alter-foreign-
# OptionParser:303 14514 alter-foreign-
# OptionParser:265 14514 Parsing opt spec: group => default spec => ask-pass desc => Prompt for a password when connecting to MySQL
# OptionParser:303 14514 ask-pass type: undef
# OptionParser:265 14514 Parsing opt spec: group => default spec => charset|A=s desc => Default character set
# OptionParser:303 14514 charset type: s
# OptionParser:265 14514 Parsing opt spec: group => default spec => check-interval=m desc => Sleep time between checks for --max-lag (default 1)
# OptionParser:303 14514 check-interval type: m
# OptionParser:310 14514 check-interval default: 1
# OptionParser:265 14514 Parsing opt spec: group => default spec => check-replicati
# OptionParser:303 14514 check-replicati
# OptionParser:310 14514 check-replicati
# OptionParser:265 14514 Parsing opt spec: group => default spec => check-slave-lag=s desc => Pause the data copy until this replica's lag is less than --max-lag
# OptionParser:303 14514 check-slave-lag type: s
# OptionParser:265 14514 Parsing opt spec: group => default spec => chunk-index=s desc => Prefer this index for chunking tables
# OptionParser:303 14514 chunk-index type: s
# OptionParser:265 14514 Parsing opt spec: group => default spec => chunk-size=z desc => Number of rows to select for each chunk copied (default 1000)
# OptionParser:303 14514 chunk-size type: z
# OptionParser:310 14514 chunk-size default: 1000
# OptionParser:265 14514 Parsing opt spec: group => default spec => chunk-size-limit=f desc => Do not copy chunks this much larger than the desired chunk size (default 4.0)
# OptionParser:303 14514 chunk-size-limit type: f
# OptionParser:310 14514 chunk-size-limit default: 4.0
# OptionParser:265 14514 Parsing opt spec: group => default spec => chunk-time=f desc => Adjust the chunk size dynamically so each data-copy query takes this long to execute (default 0.5)
# OptionParser:303 14514 chunk-time type: f
# OptionParser:310 14514 chunk-time default: 0.5
# OptionParser:265 14514 Parsing opt spec: group => default spec => config=A desc => Read this comma-separated list of config files; if specified, this must be the first option on the command line
# OptionParser:303 14514 config type: A
# OptionParser:265 14514 Parsing opt spec: group => default spec => critical-load=A desc => Examine SHOW GLOBAL STATUS after every chunk, and abort if the load is too high (default Threads_running=50)
# OptionParser:303 14514 critical-load type: A
# OptionParser:310 14514 critical-load default: Threads_running=50
# OptionParser:265 14514 Parsing opt spec: group => default spec => defaults-file|F=s desc => Only read mysql options from the given file
# OptionParser:303 14514 defaults-file type: s
# OptionParser:265 14514 Parsing opt spec: group => default spec => drop-old-table! desc => Drop the original table after renaming it (default yes)
# OptionParser:303 14514 drop-old-table type: undef
# OptionParser:310 14514 drop-old-table default: yes
# OptionParser:265 14514 Parsing opt spec: group => default spec => dry-run desc => Create and alter the new table, but do not create triggers, copy data, or replace the original table
# OptionParser:303 14514 dry-run type: undef
# OptionParser:265 14514 Parsing opt spec: group => default spec => execute desc => Indicate that you have read the documentation and want to alter the table
# OptionParser:303 14514 execute type: undef
# OptionParser:265 14514 Parsing opt spec: group => default spec => help desc => Show help and exit
# OptionParser:303 14514 help type: undef
# OptionParser:265 14514 Parsing opt spec: group => default spec => host|h=s desc => Connect to host
# OptionParser:303 14514 host type: s
# OptionParser:265 14514 Parsing opt spec: group => default spec => lock-wait-timeout=i desc => Set the session value of innodb_
# OptionParser:303 14514 lock-wait-timeout type: i
# OptionParser:310 14514 lock-wait-timeout default: 1
# OptionParser:265 14514 Parsing opt spec: group => default spec => max-lag=m desc => Pause the data copy until all replicas' lag is less than this value (default 1s)
# OptionParser:303 14514 max-lag type: m
# OptionParser:310 14514 max-lag default: 1s
# OptionParser:265 14514 Parsing opt spec: group => default spec => max-load=A desc => Examine SHOW GLOBAL STATUS after every chunk, and pause if any status variables are higher than their thresholds (default Threads_running=25)
# OptionParser:303 14514 max-load type: A
# OptionParser:310 14514 max-load default: Threads_running=25
# OptionParser:265 14514 Parsing opt spec: group => default spec => password|p=s desc => Password to use when connecting
# OptionParser:303 14514 password type: s
# OptionParser:265 14514 Parsing opt spec: group => default spec => pid=s desc => Create the given PID file
# OptionParser:303 14514 pid type: s
# OptionParser:265 14514 Parsing opt spec: group => default spec => port|P=i desc => Port number to use for connection
# OptionParser:303 14514 port type: i
# OptionParser:265 14514 Parsing opt spec: group => default spec => print desc => Print SQL statements to STDOUT
# OptionParser:303 14514 print type: undef
# OptionParser:265 14514 Parsing opt spec: group => default spec => progress=a desc => Print progress reports to STDERR while copying rows (default time,30)
# OptionParser:303 14514 progress type: a
# OptionParser:310 14514 progress default: time,30
# OptionParser:265 14514 Parsing opt spec: group => default spec => quiet|q desc => Do not print messages to STDOUT
# OptionParser:303 14514 quiet type: undef
# OptionParser:265 14514 Parsing opt spec: group => default spec => recurse=i desc => Number of levels to recurse in the hierarchy when discovering replicas
# OptionParser:303 14514 recurse type: i
# OptionParser:265 14514 Parsing opt spec: group => default spec => recursion-method=s desc => Preferred recursion method for discovering replicas
# OptionParser:303 14514 recursion-method type: s
# OptionParser:265 14514 Parsing opt spec: group => default spec => retries=i desc => Retry a chunk this many times when there is a nonfatal error (default 3)
# OptionParser:303 14514 retries type: i
# OptionParser:310 14514 retries default: 3
# OptionParser:265 14514 Parsing opt spec: group => default spec => set-vars=s desc => Set these MySQL variables (default wait_timeout=10000)
# OptionParser:303 14514 set-vars type: s
# OptionParser:310 14514 set-vars default: wait_timeout=10000
# OptionParser:265 14514 Parsing opt spec: group => default spec => socket|S=s desc => Socket file to use for connection
# OptionParser:303 14514 socket type: s
# OptionParser:265 14514 Parsing opt spec: group => default spec => swap-tables! desc => Swap the original table and the new, altered table (default yes)
# OptionParser:303 14514 swap-tables type: undef
# OptionParser:310 14514 swap-tables default: yes
# OptionParser:265 14514 Parsing opt spec: group => default spec => user|u=s desc => User for login if not current user
# OptionParser:303 14514 user type: s
# OptionParser:265 14514 Parsing opt spec: group => default spec => version desc => Show version and exit
# OptionParser:303 14514 version type: undef
# OptionParser:325 14514 Parsing rule: --dry-run and --execute are mutually exclusive.
# OptionParser:381 14514 Participants for --dry-run and --execute are mutually exclusive. : dry-run execute
# OptionParser:333 14514 dry-run execute are mutually exclusive
# OptionParser:325 14514 Parsing rule: This tool accepts additional command-line arguments. Refer to the SYNOPSIS and usage information for details.
# OptionParser:381 14514 Participants for This tool accepts additional command-line arguments. Refer to the SYNOPSIS and usage information for details. :
# OptionParser:357 14514 Strict mode disabled by rule
# OptionParser:109 14514 Parsing DSN OPTIONS
# OptionParser:199 14514 =item * A
# OptionParser:226 14514 Short help: Default character set
# OptionParser:199 14514 =item * D
# OptionParser:226 14514 Short help: Database for the old and new table
# OptionParser:199 14514 =item * F
# OptionParser:226 14514 Short help: Only read default options from the given file
# OptionParser:199 14514 =item * h
# OptionParser:226 14514 Short help: Connect to host
# OptionParser:199 14514 =item * p
# OptionParser:226 14514 Short help: Password to use when connecting
# OptionParser:199 14514 =item * P
# OptionParser:226 14514 Short help: Port number to use for connection
# OptionParser:199 14514 =item * S
# OptionParser:226 14514 Short help: Socket file to use for connection
# OptionParser:199 14514 =item * t
# OptionParser:226 14514 Short help: Table to alter
# OptionParser:199 14514 =item * u
# OptionParser:226 14514 Short help: User for login if not current user
# DSNParser:1158 14514 DSN option: copy=1, dsn=charset, desc=Default character set, key=A
# DSNParser:1158 14514 DSN option: copy=1, dsn=database, desc=Database for the old and new table, key=D
# DSNParser:1158 14514 DSN option: copy=1, dsn=mysql_
# DSNParser:1158 14514 DSN option: copy=1, dsn=host, desc=Connect to host, key=h
# DSNParser:1158 14514 DSN option: copy=1, dsn=password, desc=Password to use when connecting, key=p
# DSNParser:1158 14514 DSN option: copy=1, dsn=port, desc=Port number to use for connection, key=P
# DSNParser:1158 14514 DSN option: copy=1, dsn=mysql_socket, desc=Socket file to use for connection, key=S
# DSNParser:1158 14514 DSN option: copy=, dsn=table, desc=Table to alter, key=t
# DSNParser:1158 14514 DSN option: copy=1, dsn=user, desc=User for login if not current user, key=u
# OptionParser:153 14514 pt-online-
# OptionParser:465 14514 Cannot open /etc/percona-
#
# OptionParser:465 14514 Cannot open /etc/percona-
#
# OptionParser:465 14514 Cannot open /root/.
#
# OptionParser:465 14514 Cannot open /root/.
#
# OptionParser:433 14514 Got option alter = add guy char(2)
# OptionParser:433 14514 Got option execute = 1
# OptionParser:643 14514 Parsing option chunk-size as a size value
# OptionParser:653 14514 Nothing to validate for option recursion-method type s value undef
# OptionParser:653 14514 Nothing to validate for option check-slave-lag type s value undef
# OptionParser:653 14514 Nothing to validate for option chunk-index type s value undef
# OptionParser:653 14514 Nothing to validate for option password type s value undef
# OptionParser:653 14514 Nothing to validate for option pid type s value undef
# OptionParser:653 14514 Nothing to validate for option user type s value undef
# OptionParser:653 14514 Nothing to validate for option alter type s value add guy char(2)
# OptionParser:653 14514 Nothing to validate for option chunk-time type f value 0.5
# OptionParser:653 14514 Nothing to validate for option alter-foreign-
# OptionParser:653 14514 Nothing to validate for option charset type s value undef
# OptionParser:653 14514 Nothing to validate for option set-vars type s value wait_timeout=10000
# OptionParser:653 14514 Nothing to validate for option port type i value undef
# OptionParser:653 14514 Nothing to validate for option socket type s value undef
# OptionParser:653 14514 Nothing to validate for option lock-wait-timeout type i value 1
# OptionParser:604 14514 Parsing option check-interval as a time value
# OptionParser:609 14514 No suffix given; using s for check-interval (value: 1 )
# OptionParser:618 14514 Setting option check-interval to 1
# OptionParser:653 14514 Nothing to validate for option recurse type i value undef
# OptionParser:604 14514 Parsing option max-lag as a time value
# OptionParser:618 14514 Setting option max-lag to 1
# OptionParser:653 14514 Nothing to validate for option retries type i value 3
# OptionParser:653 14514 Nothing to validate for option chunk-size-limit type f value 4.0
# OptionParser:653 14514 Nothing to validate for option defaults-file type s value undef
# OptionParser:653 14514 Nothing to validate for option host type s value undef
# DSNParser:1174 14514 Setting set-vars property
# DSNParser:1245 14514 DSN string made from options:
# DSNParser:1183 14514 No DSN to parse
# DSNParser:1186 14514 Parsing D=app,t=
# DSNParser:1204 14514 Finding value for S
# DSNParser:1214 14514 Copying value for S from defaults
# DSNParser:1204 14514 Finding value for F
# DSNParser:1214 14514 Copying value for F from defaults
# DSNParser:1204 14514 Finding value for A
# DSNParser:1214 14514 Copying value for A from defaults
# DSNParser:1204 14514 Finding value for P
# DSNParser:1214 14514 Copying value for P from defaults
# DSNParser:1204 14514 Finding value for p
# DSNParser:1214 14514 Copying value for p from defaults
# DSNParser:1204 14514 Finding value for u
# DSNParser:1214 14514 Copying value for u from defaults
# DSNParser:1204 14514 Finding value for h
# DSNParser:1214 14514 Copying value for h from defaults
# DSNParser:1204 14514 Finding value for D
# DSNParser:1204 14514 Finding value for t
# OptionParser:727 14514 Getting description and usage from SYNOPSIS in /usr/bin/
# OptionParser:980 14514 Parsing SYNOPSIS in /usr/bin/
# OptionParser:993 14514 Raw SYNOPSIS text: Usage: pt-online-
#
# pt-online-
# writes. Specify the database and table in the DSN. Do not use this tool before
# reading its documentation and checking your backups carefully.
#
#
# OptionParser:731 14514 Description: pt-online-
# Usage: pt-online-
# DSNParser:1245 14514 DSN string made from options:
# DSNParser:1183 14514 No DSN to parse
# DSNParser:1297 14514 DBI:mysql:
# DSNParser:1343 14514 DBI:mysql:
# DSNParser:1352 14514 DBI::db=
# DSNParser:1359 14514 DBI::db=
# DSNParser:1378 14514 DBI::db=
# DSNParser:1404 14514 DBH info: DBI::db=
# '@@hostname' => 'server',
# 'CONNECTION_ID()' => '24807',
# 'DATABASE()' => 'app',
# 'VERSION()' => '5.5.16-log'
# };
# Connection info: Localhost via UNIX socket Character set info: $VAR1 = [
# {
# Value => 'latin1',
# Variable_name => 'character_
# },
# {
# Value => 'latin1',
# Variable_name => 'character_
# },
# {
# Value => 'utf8',
# Variable_name => 'character_
# },
# {
# Value => 'binary',
# Variable_name => 'character_
# },
# {
# Value => 'latin1',
# Variable_name => 'character_
# },
# {
# Value => 'utf8',
# Variable_name => 'character_
# },
# {
# Value => 'utf8',
# Variable_name => 'character_
# },
# {
# Value => '/usr/share/
# Variable_name => 'character_
# }
# ];
# $DBD::mysql:
# Cxn:2766 14514 DBI::db=
# Cxn:2779 14514 DBI::db=
# Cxn:2784 14514 DBI::db=
# Cxn:2786 14514 DBI::db=
# pt_online_
# VersionParser:1060 14514 5.5.16-log parses to 005005016
# VersionParser:1060 14514 5.0.10 parses to 005000010
# VersionParser:1071 14514 005005016 ge 5.0.10 : 1
# MasterSlave:2874 14514 Slave recursion method: undef
# MasterSlave:2922 14514 Connected to D=app,t=
# MasterSlave:2931 14514 SELECT @@SERVER_ID
# MasterSlave:2933 14514 Working on server ID 149207190
# MasterSlave:2977 14514 Looking for slaves on D=app,t=
# MasterSlave:2984 14514 Finding slaves with _find_slaves_
# MasterSlave:3051 14514 DBI::db=
# MasterSlave:3081 14514 DBI::db=
# MasterSlave:2984 14514 Finding slaves with _find_slaves_
# MasterSlave:3018 14514 DBI::db=
# MasterSlave:2989 14514 Found 0 slaves
# pt_online_
# pt_online_
# pt_online_
# MySQLStatusWait
# MySQLStatusWait
# MySQLStatusWait
# MySQLStatusWait
# TableParser:2261 14514 Checking `app`.`
# TableParser:2265 14514 SHOW TABLES FROM `app` LIKE 'CityPopularity'
# TableParser:2279 14514 Table exists; no privs to check
# pt_online_
# TableParser:2090 14514 /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := REPLACE(
# TableParser:2095 14514 DBI::db=
# TableParser:2099 14514 SHOW CREATE TABLE `app`.`
# TableParser:2111 14514 /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_
# TableParser:2320 14514 Storage engine: InnoDB
# TableParser:2141 14514 Table cols: `ufi`, `guest_language`, `guest_country`, `score`
# TableParser:2320 14514 Storage engine: InnoDB
# TableParser:2336 14514 Parsed key: PRIMARY KEY (`ufi`,
# TableParser:2362 14514 PRIMARY key cols: `ufi`, `guest_language`, `guest_country`
# TableParser:2384 14514 This key is the clustered key
# TableParser:2336 14514 Parsed key: KEY `guest_language` (`guest_
# TableParser:2362 14514 guest_language key cols: `guest_language`, `guest_country`, `score`
# NibbleIterator:4392 14514 EXPLAIN SELECT * FROM `app`.`
# NibbleIterator:4394 14514 $VAR1 = {
# extra => 'Using index',
# id => '1',
# key => 'guest_language',
# key_len => '8',
# possible_keys => undef,
# ref => undef,
# rows => '24947869',
# select_type => 'SIMPLE',
# table => 'CityPopularity',
# type => 'index'
# };
#
# NibbleIterator:4269 14514 One nibble: no
# NibbleIterator:4307 14514 MySQL wants to use index guest_language
# NibbleIterator:4319 14514 Wanted index is a possible index
# NibbleIterator:4337 14514 No PRIMARY or unique indexes; will use index with highest cardinality
# NibbleIterator:4368 14514 SHOW INDEXES FROM `app`.`
# NibbleIterator:4378 14514 Index guest_language cardinality: 125366
# NibbleIterator:4357 14514 Best index: guest_language
# TableParser:2202 14514 Indexes sorted best-first: PRIMARY, guest_language
# pt_online_
#
# pt_online_
# pt_online_
# pt_online_
Altering `app`.`
# CleanupTask:4856 14514 Created cleanup task CODE(0x1b68ebe0)
# TableParser:2090 14514 /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := REPLACE(
# TableParser:2095 14514 DBI::db=
# TableParser:2099 14514 SHOW CREATE TABLE `app`.`
# TableParser:2111 14514 /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_
Creating new table...
# pt_online_
# `ufi` int(11) NOT NULL,
# `guest_language` char(2) NOT NULL,
# `guest_country` char(2) NOT NULL,
# `score` int(10) unsigned NOT NULL,
# PRIMARY KEY (`ufi`,
# KEY `guest_language` (`guest_
# ) ENGINE=InnoDB DEFAULT CHARSET=latin1
# pt_online_
# `ufi` int(11) NOT NULL,
# `guest_language` char(2) NOT NULL,
# `guest_country` char(2) NOT NULL,
# `score` int(10) unsigned NOT NULL,
# PRIMARY KEY (`ufi`,
# KEY `guest_language` (`guest_
# ) ENGINE=InnoDB DEFAULT CHARSET=latin1
# pt_online_
# `ufi` int(11) NOT NULL,
# `guest_language` char(2) NOT NULL,
# `guest_country` char(2) NOT NULL,
# `score` int(10) unsigned NOT NULL,
# PRIMARY KEY (`ufi`,
# KEY `guest_language` (`guest_
# ) ENGINE=InnoDB DEFAULT CHARSET=latin1
Created new table app.___
Altering new table...
# pt_online_
Altered `app`.`
# TableParser:2090 14514 /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := REPLACE(
# TableParser:2095 14514 DBI::db=
# TableParser:2099 14514 SHOW CREATE TABLE `app`.`
# TableParser:2111 14514 /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_
# TableParser:2320 14514 Storage engine: InnoDB
# TableParser:2141 14514 Table cols: `ufi`, `guest_language`, `guest_country`, `score`, `guy`
# TableParser:2320 14514 Storage engine: InnoDB
# TableParser:2336 14514 Parsed key: PRIMARY KEY (`ufi`,
# TableParser:2362 14514 PRIMARY key cols: `ufi`, `guest_language`, `guest_country`
# TableParser:2384 14514 This key is the clustered key
# TableParser:2336 14514 Parsed key: KEY `guest_language` (`guest_
# TableParser:2362 14514 guest_language key cols: `guest_language`, `guest_country`, `score`
# pt_online_
Creating triggers...
# pt_online_
# pt_online_
# pt_online_
Created triggers OK.
# NibbleIterator:4392 14514 EXPLAIN SELECT * FROM `app`.`
# NibbleIterator:4394 14514 $VAR1 = {
# extra => 'Using index',
# id => '1',
# key => 'guest_language',
# key_len => '8',
# possible_keys => undef,
# ref => undef,
# rows => '24947869',
# select_type => 'SIMPLE',
# table => 'CityPopularity',
# type => 'index'
# };
#
# NibbleIterator:4269 14514 One nibble: no
# NibbleIterator:4307 14514 MySQL wants to use index guest_language
# NibbleIterator:4319 14514 Wanted index is a possible index
# NibbleIterator:4337 14514 No PRIMARY or unique indexes; will use index with highest cardinality
# NibbleIterator:4368 14514 SHOW INDEXES FROM `app`.`
# NibbleIterator:4378 14514 Index guest_language cardinality: 125366
# NibbleIterator:4357 14514 Best index: guest_language
# TableNibbler:1824 14514 Will ascend index guest_language
# TableNibbler:1831 14514 Will ascend columns guest_language, guest_country, score
# TableNibbler:1842 14514 Will ascend, in ordinal position: 1, 2, 3
# NibbleIterator:3990 14514 Ascend params: $VAR1 = {
# boundaries => {
# '<' => '((`guest_language` < ?) OR (`guest_language` = ? AND `guest_country` < ?) OR (`guest_language` = ? AND `guest_country` = ? AND `score` < ?))',
# '<=' => '((`guest_language` < ?) OR (`guest_language` = ? AND `guest_country` < ?) OR (`guest_language` = ? AND `guest_country` = ? AND `score` <= ?))',
# '>' => '((`guest_language` > ?) OR (`guest_language` = ? AND `guest_country` > ?) OR (`guest_language` = ? AND `guest_country` = ? AND `score` > ?))',
# '>=' => '((`guest_language` > ?) OR (`guest_language` = ? AND `guest_country` > ?) OR (`guest_language` = ? AND `guest_country` = ? AND `score` >= ?))'
# },
# cols => [
# 'ufi',
# 'guest_language',
# 'guest_country',
# 'score'
# ],
# index => 'guest_language',
# scols => [
# 'guest_language',
# 'guest_language',
# 'guest_country',
# 'guest_language',
# 'guest_country',
# 'score'
# ],
# slice => [
# 1,
# 1,
# 2,
# 1,
# 2,
# 3
# ],
# where => '((`guest_language` > ?) OR (`guest_language` = ? AND `guest_country` > ?) OR (`guest_language` = ? AND `guest_country` = ? AND `score` > ?))'
# };
#
# NibbleIterator:4003 14514 First lower boundary statement: SELECT /*!40001 SQL_NO_CACHE */ `guest_language`, `guest_language`, `guest_country`, `guest_language`, `guest_country`, `score` FROM `app`.`
# NibbleIterator:4028 14514 Last upper boundary statement: SELECT /*!40001 SQL_NO_CACHE */ `guest_language`, `guest_language`, `guest_country`, `guest_language`, `guest_country`, `score` FROM `app`.`
# NibbleIterator:4039 14514 Upper boundary statement: SELECT /*!40001 SQL_NO_CACHE */ `guest_language`, `guest_language`, `guest_country`, `guest_language`, `guest_country`, `score` FROM `app`.`
# NibbleIterator:4051 14514 Nibble statement: INSERT LOW_PRIORITY IGNORE INTO `app`.`
# NibbleIterator:4063 14514 Explain nibble statement: EXPLAIN SELECT `ufi`, `guest_language`, `guest_country`, `score` FROM `app`.`
# NibbleIterator:4066 14514 Initial chunk size (LIMIT): 999
# NibbleIterator:4400 14514 Preparing statement handles
# NibbleIterator:4428 14514 First lower boundary: $VAR1 = [
# 'ar',
# 'ar',
# 'ae',
# 'ar',
# 'ae',
# '7'
# ];
#
# NibbleIterator:4444 14514 Next lower boundary: $VAR1 = [
# 'ar',
# 'ar',
# 'ae',
# 'ar',
# 'ae',
# '7'
# ];
#
Copying approximately 24947869 rows...
# NibbleIterator:4117 14514 init callback returned 1
# pt_online_
# pt_online_
# extra => 'Using where; Using index',
# id => '1',
# key => 'guest_language',
# key_len => '8',
# possible_keys => 'guest_language',
# ref => undef,
# rows => '13715894',
# select_type => 'SIMPLE',
# table => 'CityPopularity',
# type => 'range'
# };
#
# NibbleIterator:4498 14514 next_boundaries callback returned 1
# NibbleIterator:4506 14514 SELECT /*!40001 SQL_NO_CACHE */ `guest_language`, `guest_language`, `guest_country`, `guest_language`, `guest_country`, `score` FROM `app`.`
# NibbleIterator:4509 14514 Next boundary: $VAR1 = [
# [
# 'ar',
# 'ar',
# 'ae',
# 'ar',
# 'ae',
# '7'
# ],
# [
# 'ar',
# 'ar',
# 'ae',
# 'ar',
# 'ae',
# '7'
# ]
# ];
#
# NibbleIterator:4130 14514 INSERT LOW_PRIORITY IGNORE INTO `app`.`
# pt_online_
# pt_online_
# extra => 'Using where; Using index',
# id => '1',
# key => 'guest_language',
# key_len => '8',
# possible_keys => 'guest_language',
# ref => undef,
# rows => '2853',
# select_type => 'SIMPLE',
# table => 'CityPopularity',
# type => 'range'
# };
#
# Retry:2659 14514 Try 1 of 3
# pt_online_
# pt_online_
# Retry:2676 14514 Try code succeeded
# pt_online_
# NibbleIterator:4138 14514 0 rows in nibble 1
# NibbleIterator:4150 14514 No rows in nibble or nibble skipped
# pt_online_
# WeightedAvgRate
# WeightedAvgRate
# WeightedAvgRate
# NibbleIterator:4233 14514 Set new chunk size (LIMIT): 33345
# ReplicaLagWaite
# MySQLStatusWait
# pt_online_
# MySQLStatusWait
# MySQLStatusWait
# NibbleIterator:4474 14514 Infinite loop detected
Error copying rows from `app`.`
# NibbleIterator:4556 14514 Finish nibble_sth
# NibbleIterator:4556 14514 Finish ub_sth
# NibbleIterator:4556 14514 Finish explain_nibble_sth
# NibbleIterator:4556 14514 Finish explain_ub_sth
# CleanupTask:4863 14514 Calling cleanup task CODE(0x1b68ebe0)
# pt_online_
Dropping triggers...
# pt_online_
# pt_online_
# pt_online_
Dropped triggers OK.
Error cleaning up: Undefined subroutine &main::_ called at /usr/bin/
# pt_online_
`app`.`
# Cxn:2819 14514 Disconnecting dbh DBI::db=
Related branches
- Daniel Nichter: Approve
-
Diff: 226 lines (+131/-11)3 files modifiedbin/pt-online-schema-change (+29/-11)
t/pt-online-schema-change/bugs.t (+73/-0)
t/pt-online-schema-change/samples/pk-bug-994002.sql (+29/-0)
tags: | added: chunking infinite-loop |
summary: |
- pt-online-schema-change 2.1.1-cannot alter table with mutli field PK - where 2nd field is char + pt-online-schema-change 2.1.1 doesn't choose the PRIMARY KEY |
Looks to me like the relevant part of the output is this:
Error copying rows from `app`.` CityPopularity` to `app`.` ___CityPopulari ty_new` : Possible infinite loop detected! The lower boundary for chunk 1 is <ar, ar, ae, ar, ae, 7> and the lower boundary for chunk 2 is also <ar, ar, ae, ar, ae, 7>. This usually happens when using a non-unique single column index. The current chunk index for table app.CityPopularity is guest_language which is not unique and covers 3 columns.
So the tool is not using the primary key for some reason. This might duplicate an existing report. To be investigated.