.\" Automatically generated by Pod::Man 4.11 (Pod::Simple 3.35) .\" .\" Standard preamble: .\" ======================================================================== .de Sp \" Vertical space (when we can't use .PP) .if t .sp .5v .if n .sp .. .de Vb \" Begin verbatim text .ft CW .nf .ne \\$1 .. .de Ve \" End verbatim text .ft R .fi .. .\" Set up some character translations and predefined strings. \*(-- will .\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left .\" double quote, and \*(R" will give a right double quote. \*(C+ will .\" give a nicer C++. Capital omega is used to do unbreakable dashes and .\" therefore won't be available. \*(C` and \*(C' expand to `' in nroff, .\" nothing in troff, for use with C<>. .tr \(*W- .ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p' .ie n \{\ . ds -- \(*W- . ds PI pi . if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch . if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\" diablo 12 pitch . ds L" "" . ds R" "" . ds C` "" . ds C' "" 'br\} .el\{\ . ds -- \|\(em\| . ds PI \(*p . ds L" `` . ds R" '' . ds C` . ds C' 'br\} .\" .\" Escape single quotes in literal strings from groff's Unicode transform. .ie \n(.g .ds Aq \(aq .el .ds Aq ' .\" .\" If the F register is >0, we'll generate index entries on stderr for .\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index .\" entries marked with X<> in POD. Of course, you'll have to process the .\" output yourself in some meaningful fashion. .\" .\" Avoid warning from groff about undefined register 'F'. .de IX .. .nr rF 0 .if \n(.g .if rF .nr rF 1 .if (\n(rF:(\n(.g==0)) \{\ . if \nF \{\ . de IX . tm Index:\\$1\t\\n%\t"\\$2" .. . if !\nF==2 \{\ . nr % 0 . nr F 2 . \} . \} .\} .rr rF .\" ======================================================================== .\" .IX Title "PT-ONLINE-SCHEMA-CHANGE 1p" .TH PT-ONLINE-SCHEMA-CHANGE 1p "2020-08-30" "perl v5.30.3" "User Contributed Perl Documentation" .\" For nroff, turn off justification. Always turn off hyphenation; it makes .\" way too many mistakes in technical documents. .if n .ad l .nh .SH "NAME" pt\-online\-schema\-change \- ALTER tables without locking them. .SH "SYNOPSIS" .IX Header "SYNOPSIS" Usage: pt-online-schema-change [\s-1OPTIONS\s0] \s-1DSN\s0 .PP pt-online-schema-change alters a table's structure without blocking reads or writes. Specify the database and table in the \s-1DSN.\s0 Do not use this tool before reading its documentation and checking your backups carefully. .PP Add a column to sakila.actor: .PP .Vb 1 \& pt\-online\-schema\-change \-\-alter "ADD COLUMN c1 INT" D=sakila,t=actor .Ve .PP Change sakila.actor to InnoDB, effectively performing \s-1OPTIMIZE TABLE\s0 in a non-blocking fashion because it is already an InnoDB table: .PP .Vb 1 \& pt\-online\-schema\-change \-\-alter "ENGINE=InnoDB" D=sakila,t=actor .Ve .SH "RISKS" .IX Header "RISKS" Percona Toolkit is mature, proven in the real world, and well tested, but all database tools can pose a risk to the system and the database server. Before using this tool, please: .IP "\(bu" 4 Read the tool's documentation .IP "\(bu" 4 Review the tool's known \*(L"\s-1BUGS\*(R"\s0 .IP "\(bu" 4 Test the tool on a non-production server .IP "\(bu" 4 Backup your production server and verify the backups .SH "DESCRIPTION" .IX Header "DESCRIPTION" pt-online-schema-change emulates the way that MySQL alters tables internally, but it works on a copy of the table you wish to alter. This means that the original table is not locked, and clients may continue to read and change data in it. .PP pt-online-schema-change works by creating an empty copy of the table to alter, modifying it as desired, and then copying rows from the original table into the new table. When the copy is complete, it moves away the original table and replaces it with the new one. By default, it also drops the original table. .PP The data copy process is performed in small chunks of data, which are varied to attempt to make them execute in a specific amount of time (see \&\*(L"\-\-chunk\-time\*(R"). This process is very similar to how other tools, such as pt-table-checksum, work. Any modifications to data in the original tables during the copy will be reflected in the new table, because the tool creates triggers on the original table to update the corresponding rows in the new table. The use of triggers means that the tool will not work if any triggers are already defined on the table. .PP When the tool finishes copying data into the new table, it uses an atomic \&\f(CW\*(C`RENAME TABLE\*(C'\fR operation to simultaneously rename the original and new tables. After this is complete, the tool drops the original table. .PP Foreign keys complicate the tool's operation and introduce additional risk. The technique of atomically renaming the original and new tables does not work when foreign keys refer to the table. The tool must update foreign keys to refer to the new table after the schema change is complete. The tool supports two methods for accomplishing this. You can read more about this in the documentation for \&\*(L"\-\-alter\-foreign\-keys\-method\*(R". .PP Foreign keys also cause some side effects. The final table will have the same foreign keys and indexes as the original table (unless you specify differently in your \s-1ALTER\s0 statement), but the names of the objects may be changed slightly to avoid object name collisions in MySQL and InnoDB. .PP For safety, the tool does not modify the table unless you specify the \&\*(L"\-\-execute\*(R" option, which is not enabled by default. The tool supports a variety of other measures to prevent unwanted load or other problems, including automatically detecting replicas, connecting to them, and using the following safety checks: .IP "\(bu" 4 In most cases the tool will refuse to operate unless a \s-1PRIMARY KEY\s0 or \s-1UNIQUE INDEX\s0 is present in the table. See \*(L"\-\-alter\*(R" for details. .IP "\(bu" 4 The tool refuses to operate if it detects replication filters. See \&\*(L"\-\-[no]check\-replication\-filters\*(R" for details. .IP "\(bu" 4 The tool pauses the data copy operation if it observes any replicas that are delayed in replication. See \*(L"\-\-max\-lag\*(R" for details. .IP "\(bu" 4 The tool pauses or aborts its operation if it detects too much load on the server. See \*(L"\-\-max\-load\*(R" and \*(L"\-\-critical\-load\*(R" for details. .IP "\(bu" 4 The tool sets \f(CW\*(C`innodb_lock_wait_timeout=1\*(C'\fR and (for MySQL 5.5 and newer) \&\f(CW\*(C`lock_wait_timeout=60\*(C'\fR so that it is more likely to be the victim of any lock contention, and less likely to disrupt other transactions. These values can be changed by specifying \*(L"\-\-set\-vars\*(R". .IP "\(bu" 4 The tool refuses to alter the table if foreign key constraints reference it, unless you specify \*(L"\-\-alter\-foreign\-keys\-method\*(R". .IP "\(bu" 4 The tool cannot alter MyISAM tables on \*(L"Percona XtraDB Cluster\*(R" nodes. .SH "Percona XtraDB Cluster" .IX Header "Percona XtraDB Cluster" pt-online-schema-change works with Percona XtraDB Cluster (\s-1PXC\s0) 5.5.28\-23.7 and newer, but there are two limitations: only InnoDB tables can be altered, and \f(CW\*(C`wsrep_OSU_method\*(C'\fR must be set to \f(CW\*(C`TOI\*(C'\fR (total order isolation). The tool exits with an error if the host is a cluster node and the table is MyISAM or is being converted to MyISAM (\f(CW\*(C`ENGINE=MyISAM\*(C'\fR), or if \&\f(CW\*(C`wsrep_OSU_method\*(C'\fR is not \f(CW\*(C`TOI\*(C'\fR. There is no way to disable these checks. .SH "MySQL 5.7 + Generated columns" .IX Header "MySQL 5.7 + Generated columns" The tools ignores MySQL 5.7+ \f(CW\*(C`GENERATED\*(C'\fR columns since the value for those columns is generated according to the expression used to compute column values. .SH "OUTPUT" .IX Header "OUTPUT" The tool prints information about its activities to \s-1STDOUT\s0 so that you can see what it is doing. During the data copy phase, it prints \*(L"\-\-progress\*(R" reports to \s-1STDERR.\s0 You can get additional information by specifying \&\*(L"\-\-print\*(R". .PP If \*(L"\-\-statistics\*(R" is specified, a report of various internal event counts is printed at the end, like: .PP .Vb 3 \& # Event Count \& # ====== ===== \& # INSERT 1 .Ve .SH "OPTIONS" .IX Header "OPTIONS" \&\*(L"\-\-dry\-run\*(R" and \*(L"\-\-execute\*(R" are mutually exclusive. .PP This tool accepts additional command-line arguments. Refer to the \&\*(L"\s-1SYNOPSIS\*(R"\s0 and usage information for details. .IP "\-\-alter" 4 .IX Item "--alter" type: string .Sp The schema modification, without the \s-1ALTER TABLE\s0 keywords. You can perform multiple modifications to the table by specifying them with commas. Please refer to the MySQL manual for the syntax of \s-1ALTER TABLE.\s0 .Sp The following limitations apply which, if attempted, will cause the tool to fail in unpredictable ways: .RS 4 .IP "\(bu" 4 In almost all cases a \s-1PRIMARY KEY\s0 or \s-1UNIQUE INDEX\s0 needs to be present in the table. This is necessary because the tool creates a \s-1DELETE\s0 trigger to keep the new table updated while the process is running. .Sp A notable exception is when a \s-1PRIMARY KEY\s0 or \s-1UNIQUE INDEX\s0 is being created from \&\fBexisting columns\fR as part of the \s-1ALTER\s0 clause; in that case it will use these column(s) for the \s-1DELETE\s0 trigger. .IP "\(bu" 4 The \f(CW\*(C`RENAME\*(C'\fR clause cannot be used to rename the table. .IP "\(bu" 4 Columns cannot be renamed by dropping and re-adding with the new name. The tool will not copy the original column's data to the new column. .IP "\(bu" 4 If you add a column without a default value and make it \s-1NOT NULL,\s0 the tool will fail, as it will not try to guess a default value for you; You must specify the default. .IP "\(bu" 4 \&\f(CW\*(C`DROP FOREIGN KEY constraint_name\*(C'\fR requires specifying \f(CW\*(C`_constraint_name\*(C'\fR rather than the real \f(CW\*(C`constraint_name\*(C'\fR. Due to a limitation in MySQL, pt-online-schema-change adds a leading underscore to foreign key constraint names when creating the new table. For example, to drop this constraint: .Sp .Vb 1 \& CONSTRAINT \`fk_foo\` FOREIGN KEY (\`foo_id\`) REFERENCES \`bar\` (\`foo_id\`) .Ve .Sp You must specify \f(CW\*(C`\-\-alter "DROP FOREIGN KEY _fk_foo"\*(C'\fR. .IP "\(bu" 4 The tool does not use \f(CW\*(C`LOCK IN SHARE MODE\*(C'\fR with MySQL 5.0 because it can cause a slave error which breaks replication: .Sp .Vb 4 \& Query caused different errors on master and slave. Error on master: \& \*(AqDeadlock found when trying to get lock; try restarting transaction\*(Aq (1213), \& Error on slave: \*(Aqno error\*(Aq (0). Default database: \*(Aqpt_osc\*(Aq. \& Query: \*(AqINSERT INTO pt_osc.t (id, c) VALUES (\*(Aq730\*(Aq, \*(Aqnew row\*(Aq)\*(Aq .Ve .Sp The error happens when converting a MyISAM table to InnoDB because MyISAM is non-transactional but InnoDB is transactional. MySQL 5.1 and newer handle this case correctly, but testing reproduces the error 5% of the time with MySQL 5.0. .Sp This is a MySQL bug, similar to , but there is no fix or workaround in MySQL 5.0. Without \f(CW\*(C`LOCK IN SHARE MODE\*(C'\fR, tests pass 100% of the time, so the risk of data loss or breaking replication should be negligible. .Sp \&\fBBe sure to verify the new table if using MySQL 5.0 and converting from MyISAM to InnoDB!\fR .RE .RS 4 .RE .IP "\-\-alter\-foreign\-keys\-method" 4 .IX Item "--alter-foreign-keys-method" type: string .Sp How to modify foreign keys so they reference the new table. Foreign keys that reference the table to be altered must be treated specially to ensure that they continue to reference the correct table. When the tool renames the original table to let the new one take its place, the foreign keys \*(L"follow\*(R" the renamed table, and must be changed to reference the new table instead. .Sp The tool supports two techniques to achieve this. It automatically finds \*(L"child tables\*(R" that reference the table to be altered. .RS 4 .IP "auto" 4 .IX Item "auto" Automatically determine which method is best. The tool uses \&\f(CW\*(C`rebuild_constraints\*(C'\fR if possible (see the description of that method for details), and if not, then it uses \f(CW\*(C`drop_swap\*(C'\fR. .IP "rebuild_constraints" 4 .IX Item "rebuild_constraints" This method uses \f(CW\*(C`ALTER TABLE\*(C'\fR to drop and re-add foreign key constraints that reference the new table. This is the preferred technique, unless one or more of the \*(L"child\*(R" tables is so large that the \f(CW\*(C`ALTER\*(C'\fR would take too long. The tool determines that by comparing the number of rows in the child table to the rate at which the tool is able to copy rows from the old table to the new table. If the tool estimates that the child table can be altered in less time than the \&\*(L"\-\-chunk\-time\*(R", then it will use this technique. For purposes of estimating the time required to alter the child table, the tool multiplies the row-copying rate by \*(L"\-\-chunk\-size\-limit\*(R", because MySQL's \f(CW\*(C`ALTER TABLE\*(C'\fR is typically much faster than the external process of copying rows. .Sp Due to a limitation in MySQL, foreign keys will not have the same names after the \s-1ALTER\s0 that they did prior to it. The tool has to rename the foreign key when it redefines it, which adds a leading underscore to the name. In some cases, MySQL also automatically renames indexes required for the foreign key. .IP "drop_swap" 4 .IX Item "drop_swap" Disable foreign key checks (FOREIGN_KEY_CHECKS=0), then drop the original table before renaming the new table into its place. This is different from the normal method of swapping the old and new table, which uses an atomic \f(CW\*(C`RENAME\*(C'\fR that is undetectable to client applications. .Sp This method is faster and does not block, but it is riskier for two reasons. First, for a short time between dropping the original table and renaming the temporary table, the table to be altered simply does not exist, and queries against it will result in an error. Secondly, if there is an error and the new table cannot be renamed into the place of the old one, then it is too late to abort, because the old table is gone permanently. .Sp This method forces \f(CW\*(C`\-\-no\-swap\-tables\*(C'\fR and \f(CW\*(C`\-\-no\-drop\-old\-table\*(C'\fR. .IP "none" 4 .IX Item "none" This method is like \f(CW\*(C`drop_swap\*(C'\fR without the \*(L"swap\*(R". Any foreign keys that referenced the original table will now reference a nonexistent table. This will typically cause foreign key violations that are visible in \f(CW\*(C`SHOW ENGINE INNODB STATUS\*(C'\fR, similar to the following: .Sp .Vb 6 \& Trying to add to index \`idx_fk_staff_id\` tuple: \& DATA TUPLE: 2 fields; \& 0: len 1; hex 05; asc ;; \& 1: len 4; hex 80000001; asc ;; \& But the parent table \`sakila\`.\`staff_old\` \& or its .ibd file does not currently exist! .Ve .Sp This is because the original table (in this case, sakila.staff) was renamed to sakila.staff_old and then dropped. This method of handling foreign key constraints is provided so that the database administrator can disable the tool's built-in functionality if desired. .RE .RS 4 .RE .IP "\-\-[no]analyze\-before\-swap" 4 .IX Item "--[no]analyze-before-swap" default: yes .Sp Execute \s-1ANALYZE TABLE\s0 on the new table before swapping with the old one. By default, this happens only when running MySQL 5.6 and newer, and \&\f(CW\*(C`innodb_stats_persistent\*(C'\fR is enabled. Specify the option explicitly to enable or disable it regardless of MySQL version and \f(CW\*(C`innodb_stats_persistent\*(C'\fR. .Sp This circumvents a potentially serious issue related to InnoDB optimizer statistics. If the table being alerted is busy and the tool completes quickly, the new table will not have optimizer statistics after being swapped. This can cause fast, index-using queries to do full table scans until optimizer statistics are updated (usually after 10 seconds). If the table is large and the server very busy, this can cause an outage. .IP "\-\-ask\-pass" 4 .IX Item "--ask-pass" Prompt for a password when connecting to MySQL. .IP "\-\-channel" 4 .IX Item "--channel" type: string .Sp Channel name used when connected to a server using replication channels. Suppose you have two masters, master_a at port 12345, master_b at port 1236 and a slave connected to both masters using channels chan_master_a and chan_master_b. If you want to run pt-table-sync to synchronize the slave against master_a, pt-table-sync won't be able to determine what's the correct master since \s-1SHOW SLAVE STATUS\s0 will return 2 rows. In this case, you can use \-\-channel=chan_master_a to specify the channel name to use in the \s-1SHOW SLAVE STATUS\s0 command. .IP "\-\-charset" 4 .IX Item "--charset" short form: \-A; type: string .Sp Default character set. If the value is utf8, sets Perl's binmode on \&\s-1STDOUT\s0 to utf8, passes the mysql_enable_utf8 option to DBD::mysql, and runs \s-1SET NAMES UTF8\s0 after connecting to MySQL. Any other value sets binmode on \s-1STDOUT\s0 without the utf8 layer, and runs \s-1SET NAMES\s0 after connecting to MySQL. .IP "\-\-[no]check\-alter" 4 .IX Item "--[no]check-alter" default: yes .Sp Parses the \*(L"\-\-alter\*(R" specified and tries to warn of possible unintended behavior. Currently, it checks for: .RS 4 .IP "Column renames" 4 .IX Item "Column renames" In previous versions of the tool, renaming a column with \&\f(CW\*(C`CHANGE COLUMN name new_name\*(C'\fR would lead to that column's data being lost. The tool now parses the alter statement and tries to catch these cases, so the renamed columns should have the same data as the originals. However, the code that does this is not a full-blown \s-1SQL\s0 parser, so you should first run the tool with \*(L"\-\-dry\-run\*(R" and \*(L"\-\-print\*(R" and verify that it detects the renamed columns correctly. .IP "\s-1DROP PRIMARY KEY\s0" 4 .IX Item "DROP PRIMARY KEY" If \*(L"\-\-alter\*(R" contain \f(CW\*(C`DROP PRIMARY KEY\*(C'\fR (case\- and space-insensitive), a warning is printed and the tool exits unless \*(L"\-\-dry\-run\*(R" is specified. Altering the primary key can be dangerous, but the tool can handle it. The tool's triggers, particularly the \s-1DELETE\s0 trigger, are most affected by altering the primary key because the tool prefers to use the primary key for its triggers. You should first run the tool with \*(L"\-\-dry\-run\*(R" and \&\*(L"\-\-print\*(R" and verify that the triggers are correct. .RE .RS 4 .RE .IP "\-\-[no]check\-foreign\-keys" 4 .IX Item "--[no]check-foreign-keys" default: yes .Sp Check for self-referencing foreign keys. Currently self referencing FKs are not full supported, so, to prevent errors, this program won't run if the table has self-referencing foreign keys. Use this parameter to disable self-referencing \&\s-1FK\s0 checks. .IP "\-\-check\-interval" 4 .IX Item "--check-interval" type: time; default: 1 .Sp Sleep time between checks for \*(L"\-\-max\-lag\*(R". .IP "\-\-[no]check\-plan" 4 .IX Item "--[no]check-plan" default: yes .Sp Check query execution plans for safety. By default, this option causes the tool to run \s-1EXPLAIN\s0 before running queries that are meant to access a small amount of data, but which could access many rows if MySQL chooses a bad execution plan. These include the queries to determine chunk boundaries and the chunk queries themselves. If it appears that MySQL will use a bad query execution plan, the tool will skip the chunk of the table. .Sp The tool uses several heuristics to determine whether an execution plan is bad. The first is whether \s-1EXPLAIN\s0 reports that MySQL intends to use the desired index to access the rows. If MySQL chooses a different index, the tool considers the query unsafe. .Sp The tool also checks how much of the index MySQL reports that it will use for the query. The \s-1EXPLAIN\s0 output shows this in the key_len column. The tool remembers the largest key_len seen, and skips chunks where MySQL reports that it will use a smaller prefix of the index. This heuristic can be understood as skipping chunks that have a worse execution plan than other chunks. .Sp The tool prints a warning the first time a chunk is skipped due to a bad execution plan in each table. Subsequent chunks are skipped silently, although you can see the count of skipped chunks in the \s-1SKIPPED\s0 column in the tool's output. .Sp This option adds some setup work to each table and chunk. Although the work is not intrusive for MySQL, it results in more round-trips to the server, which consumes time. Making chunks too small will cause the overhead to become relatively larger. It is therefore recommended that you not make chunks too small, because the tool may take a very long time to complete if you do. .IP "\-\-[no]check\-replication\-filters" 4 .IX Item "--[no]check-replication-filters" default: yes .Sp Abort if any replication filter is set on any server. The tool looks for server options that filter replication, such as binlog_ignore_db and replicate_do_db. If it finds any such filters, it aborts with an error. .Sp If the replicas are configured with any filtering options, you should be careful not to modify any databases or tables that exist on the master and not the replicas, because it could cause replication to fail. For more information on replication rules, see . .IP "\-\-check\-slave\-lag" 4 .IX Item "--check-slave-lag" type: string .Sp Pause the data copy until this replica's lag is less than \*(L"\-\-max\-lag\*(R". The value is a \s-1DSN\s0 that inherits properties from the the connection options (\*(L"\-\-port\*(R", \*(L"\-\-user\*(R", etc.). This option overrides the normal behavior of finding and continually monitoring replication lag on \s-1ALL\s0 connected replicas. If you don't want to monitor \s-1ALL\s0 replicas, but you want more than just one replica to be monitored, then use the \s-1DSN\s0 option to the \*(L"\-\-recursion\-method\*(R" option instead of this option. .IP "\-\-chunk\-index" 4 .IX Item "--chunk-index" type: string .Sp Prefer this index for chunking tables. By default, the tool chooses the most appropriate index for chunking. This option lets you specify the index that you prefer. If the index doesn't exist, then the tool will fall back to its default behavior of choosing an index. The tool adds the index to the \s-1SQL\s0 statements in a \f(CW\*(C`FORCE INDEX\*(C'\fR clause. Be careful when using this option; a poor choice of index could cause bad performance. .IP "\-\-chunk\-index\-columns" 4 .IX Item "--chunk-index-columns" type: int .Sp Use only this many left-most columns of a \*(L"\-\-chunk\-index\*(R". This works only for compound indexes, and is useful in cases where a bug in the MySQL query optimizer (planner) causes it to scan a large range of rows instead of using the index to locate starting and ending points precisely. This problem sometimes occurs on indexes with many columns, such as 4 or more. If this happens, the tool might print a warning related to the \&\*(L"\-\-[no]check\-plan\*(R" option. Instructing the tool to use only the first N columns of the index is a workaround for the bug in some cases. .IP "\-\-chunk\-size" 4 .IX Item "--chunk-size" type: size; default: 1000 .Sp Number of rows to select for each chunk copied. Allowable suffixes are k, M, G. .Sp This option can override the default behavior, which is to adjust chunk size dynamically to try to make chunks run in exactly \*(L"\-\-chunk\-time\*(R" seconds. When this option isn't set explicitly, its default value is used as a starting point, but after that, the tool ignores this option's value. If you set this option explicitly, however, then it disables the dynamic adjustment behavior and tries to make all chunks exactly the specified number of rows. .Sp There is a subtlety: if the chunk index is not unique, then it's possible that chunks will be larger than desired. For example, if a table is chunked by an index that contains 10,000 of a given value, there is no way to write a \s-1WHERE\s0 clause that matches only 1,000 of the values, and that chunk will be at least 10,000 rows large. Such a chunk will probably be skipped because of \&\*(L"\-\-chunk\-size\-limit\*(R". .IP "\-\-chunk\-size\-limit" 4 .IX Item "--chunk-size-limit" type: float; default: 4.0 .Sp Do not copy chunks this much larger than the desired chunk size. .Sp When a table has no unique indexes, chunk sizes can be inaccurate. This option specifies a maximum tolerable limit to the inaccuracy. The tool uses <\s-1EXPLAIN\s0> to estimate how many rows are in the chunk. If that estimate exceeds the desired chunk size times the limit, then the tool skips the chunk. .Sp The minimum value for this option is 1, which means that no chunk can be larger than \*(L"\-\-chunk\-size\*(R". You probably don't want to specify 1, because rows reported by \s-1EXPLAIN\s0 are estimates, which can be different from the real number of rows in the chunk. You can disable oversized chunk checking by specifying a value of 0. .Sp The tool also uses this option to determine how to handle foreign keys that reference the table to be altered. See \*(L"\-\-alter\-foreign\-keys\-method\*(R" for details. .IP "\-\-chunk\-time" 4 .IX Item "--chunk-time" type: float; default: 0.5 .Sp Adjust the chunk size dynamically so each data-copy query takes this long to execute. The tool tracks the copy rate (rows per second) and adjusts the chunk size after each data-copy query, so that the next query takes this amount of time (in seconds) to execute. It keeps an exponentially decaying moving average of queries per second, so that if the server's performance changes due to changes in server load, the tool adapts quickly. .Sp If this option is set to zero, the chunk size doesn't auto-adjust, so query times will vary, but query chunk sizes will not. Another way to do the same thing is to specify a value for \*(L"\-\-chunk\-size\*(R" explicitly, instead of leaving it at the default. .IP "\-\-config" 4 .IX Item "--config" type: Array .Sp Read this comma-separated list of config files; if specified, this must be the first option on the command line. .IP "\-\-critical\-load" 4 .IX Item "--critical-load" type: Array; default: Threads_running=50 .Sp Examine \s-1SHOW GLOBAL STATUS\s0 after every chunk, and abort if the load is too high. The option accepts a comma-separated list of MySQL status variables and thresholds. An optional \f(CW\*(C`=MAX_VALUE\*(C'\fR (or \f(CW\*(C`:MAX_VALUE\*(C'\fR) can follow each variable. If not given, the tool determines a threshold by examining the current value at startup and doubling it. .Sp See \*(L"\-\-max\-load\*(R" for further details. These options work similarly, except that this option will abort the tool's operation instead of pausing it, and the default value is computed differently if you specify no threshold. The reason for this option is as a safety check in case the triggers on the original table add so much load to the server that it causes downtime. There is probably no single value of Threads_running that is wrong for every server, but a default of 50 seems likely to be unacceptably high for most servers, indicating that the operation should be canceled immediately. .IP "\-\-database" 4 .IX Item "--database" short form: \-D; type: string .Sp Connect to this database. .IP "\-\-default\-engine" 4 .IX Item "--default-engine" Remove \f(CW\*(C`ENGINE\*(C'\fR from the new table. .Sp By default the new table is created with the same table options as the original table, so if the original table uses InnoDB, then the new table will use InnoDB. In certain cases involving replication, this may cause unintended changes on replicas which use a different engine for the same table. Specifying this option causes the new table to be created with the system's default engine. .IP "\-\-data\-dir" 4 .IX Item "--data-dir" type: string .Sp Create the new table on a different partition using the \s-1DATA DIRECTORY\s0 feature. Only available on 5.6+. This parameter is ignored if it is used at the same time than remove-data-dir. .IP "\-\-remove\-data\-dir" 4 .IX Item "--remove-data-dir" default: no .Sp If the original table was created using the \s-1DATA DIRECTORY\s0 feature, remove it and create the new table in MySQL default directory without creating a new isl file. .IP "\-\-defaults\-file" 4 .IX Item "--defaults-file" short form: \-F; type: string .Sp Only read mysql options from the given file. You must give an absolute pathname. .IP "\-\-[no]drop\-new\-table" 4 .IX Item "--[no]drop-new-table" default: yes .Sp Drop the new table if copying the original table fails. .Sp Specifying \f(CW\*(C`\-\-no\-drop\-new\-table\*(C'\fR and \f(CW\*(C`\-\-no\-swap\-tables\*(C'\fR leaves the new, altered copy of the table without modifying the original table. See \&\*(L"\-\-new\-table\-name\*(R". .Sp \&\-\-no\-drop\-new\-table does not work with \&\f(CW\*(C`alter\-foreign\-keys\-method drop_swap\*(C'\fR. .IP "\-\-[no]drop\-old\-table" 4 .IX Item "--[no]drop-old-table" default: yes .Sp Drop the original table after renaming it. After the original table has been successfully renamed to let the new table take its place, and if there are no errors, the tool drops the original table by default. If there are any errors, the tool leaves the original table in place. .Sp If \f(CW\*(C`\-\-no\-swap\-tables\*(C'\fR is specified, then there is no old table to drop. .IP "\-\-[no]drop\-triggers" 4 .IX Item "--[no]drop-triggers" default: yes .Sp Drop triggers on the old table. \f(CW\*(C`\-\-no\-drop\-triggers\*(C'\fR forces \&\f(CW\*(C`\-\-no\-drop\-old\-table\*(C'\fR. .IP "\-\-dry\-run" 4 .IX Item "--dry-run" Create and alter the new table, but do not create triggers, copy data, or replace the original table. .IP "\-\-execute" 4 .IX Item "--execute" Indicate that you have read the documentation and want to alter the table. You must specify this option to alter the table. If you do not, then the tool will only perform some safety checks and exit. This helps ensure that you have read the documentation and understand how to use this tool. If you have not read the documentation, then do not specify this option. .IP "\-\-[no]check\-unique\-key\-change" 4 .IX Item "--[no]check-unique-key-change" default: yes .Sp Avoid \f(CW\*(C`pt\-online\-schema\-change\*(C'\fR to run if the specified statement for \*(L"\-\-alter\*(R" is trying to add an unique index. Since \f(CW\*(C`pt\-online\-schema\-change\*(C'\fR uses \f(CW\*(C`INSERT IGNORE\*(C'\fR to copy rows to the new table, if the row being written produces a duplicate key, it will fail silently and data will be lost. .Sp Example: .Sp .Vb 7 \& CREATE DATABASE test; \& USE test; \& CREATE TABLE \`a\` ( \& \`id\` int(11) NOT NULL, \& \`unique_id\` varchar(32) DEFAULT NULL, \& PRIMARY KEY (\`id\`) \& ) ENGINE=InnoDB DEFAULT CHARSET=latin1; \& \& insert into a values (1, "a"); \& insert into a values (2, "b"); \& insert into a values (3, ""); \& insert into a values (4, ""); \& insert into a values (5, NULL); \& insert into a values (6, NULL); .Ve .Sp Using \f(CW\*(C`pt\-online\-schema\-change\*(C'\fR to add an unique index on the \f(CW\*(C`unique_id\*(C'\fR field, will cause some rows to be lost due to the use of \f(CW\*(C`INSERT IGNORE\*(C'\fR to copy rows from the source table. For this reason, \f(CW\*(C`pt\-online\-schema\-change\*(C'\fR will fail if it detects that the \*(L"\-\-alter\*(R" parameter is trying to add an unique key and it will show an example query to run to detect if there are rows that will produce duplicated indexes. .Sp Even if you run the query and there are no rows that will produce duplicated indexes, take into consideration that after running this query, changes can be made to the table that can produce duplicate rows and this data will be lost. .IP "\-\-force" 4 .IX Item "--force" This options bypasses confirmation in case of using alter-foreign-keys-method = none , which might break foreign key constraints. .IP "\-\-help" 4 .IX Item "--help" Show help and exit. .IP "\-\-host" 4 .IX Item "--host" short form: \-h; type: string .Sp Connect to host. .IP "\-\-max\-flow\-ctl" 4 .IX Item "--max-flow-ctl" type: float .Sp Somewhat similar to \-\-max\-lag but for \s-1PXC\s0 clusters. Check average time cluster spent pausing for Flow Control and make tool pause if it goes over the percentage indicated in the option. A value of 0 would make the tool pause when *any* Flow Control activity is detected. Default is no Flow Control checking. This option is available for \s-1PXC\s0 versions 5.6 or higher. .IP "\-\-max\-lag" 4 .IX Item "--max-lag" type: time; default: 1s .Sp Pause the data copy until all replicas' lag is less than this value. After each data-copy query (each chunk), the tool looks at the replication lag of all replicas to which it connects, using Seconds_Behind_Master. If any replica is lagging more than the value of this option, then the tool will sleep for \*(L"\-\-check\-interval\*(R" seconds, then check all replicas again. If you specify \*(L"\-\-check\-slave\-lag\*(R", then the tool only examines that server for lag, not all servers. If you want to control exactly which servers the tool monitors, use the \s-1DSN\s0 value to \*(L"\-\-recursion\-method\*(R". .Sp The tool waits forever for replicas to stop lagging. If any replica is stopped, the tool waits forever until the replica is started. The data copy continues when all replicas are running and not lagging too much. .Sp The tool prints progress reports while waiting. If a replica is stopped, it prints a progress report immediately, then again at every progress report interval. .IP "\-\-max\-load" 4 .IX Item "--max-load" type: Array; default: Threads_running=25 .Sp Examine \s-1SHOW GLOBAL STATUS\s0 after every chunk, and pause if any status variables are higher than their thresholds. The option accepts a comma-separated list of MySQL status variables. An optional \f(CW\*(C`=MAX_VALUE\*(C'\fR (or \f(CW\*(C`:MAX_VALUE\*(C'\fR) can follow each variable. If not given, the tool determines a threshold by examining the current value and increasing it by 20%. .Sp For example, if you want the tool to pause when Threads_connected gets too high, you can specify \*(L"Threads_connected\*(R", and the tool will check the current value when it starts working and add 20% to that value. If the current value is 100, then the tool will pause when Threads_connected exceeds 120, and resume working when it is below 120 again. If you want to specify an explicit threshold, such as 110, you can use either \*(L"Threads_connected:110\*(R" or \*(L"Threads_connected=110\*(R". .Sp The purpose of this option is to prevent the tool from adding too much load to the server. If the data-copy queries are intrusive, or if they cause lock waits, then other queries on the server will tend to block and queue. This will typically cause Threads_running to increase, and the tool can detect that by running \s-1SHOW GLOBAL STATUS\s0 immediately after each query finishes. If you specify a threshold for this variable, then you can instruct the tool to wait until queries are running normally again. This will not prevent queueing, however; it will only give the server a chance to recover from the queueing. If you notice queueing, it is best to decrease the chunk time. .IP "\-\-preserve\-triggers" 4 .IX Item "--preserve-triggers" Preserves old triggers when specified. As of MySQL 5.7.2, it is possible to define multiple triggers for a given table that have the same trigger event and action time. This allows us to add the triggers needed for \f(CW\*(C`pt\-online\-schema\-change\*(C'\fR even if the table already has its own triggers. If this option is enabled, \f(CW\*(C`pt\-online\-schema\-change\*(C'\fR will try to copy all the existing triggers to the new table \s-1BEFORE\s0 start copying rows from the original table to ensure the old triggers can be applied after altering the table. .Sp Example. .Sp .Vb 6 \& CREATE TABLE test.t1 ( \& id INT NOT NULL AUTO_INCREMENT, \& f1 INT, \& f2 VARCHAR(32), \& PRIMARY KEY (id) \& ); \& \& CREATE TABLE test.log ( \& ts TIMESTAMP, \& msg VARCHAR(255) \& ); \& \& CREATE TRIGGER test.after_update \& AFTER \& UPDATE ON test.t1 \& FOR EACH ROW \& INSERT INTO test.log VALUES (NOW(), CONCAT("updated row row with id ", OLD.id, " old f1:", OLD.f1, " new f1: ", NEW.f1 )); .Ve .Sp For this table and triggers combination, it is not possible to use \-\-preserve\-triggers with an \-\-alter like this: \f(CW"DROP COLUMN f1"\fR since the trigger references the column being dropped and at would make the trigger to fail. .Sp After testing the triggers will work on the new table, the triggers are dropped from the new table until all rows have been copied and then they are re-applied. .Sp \&\-\-preserve\-triggers cannot be used with these other parameters, \-\-no\-drop\-triggers, \&\-\-no\-drop\-old\-table and \-\-no\-swap\-tables since \-\-preserve\-triggers implies that the old triggers should be deleted and recreated in the new table. Since it is not possible to have more than one trigger with the same name, old triggers must be deleted in order to be able to recreate them into the new table. .Sp Using \f(CW\*(C`\-\-preserve\-triggers\*(C'\fR with \f(CW\*(C`\-\-no\-swap\-tables\*(C'\fR will cause triggers to remain defined for the original table. Please read the documentation for \-\-swap\-tables .Sp If both \f(CW\*(C`\-\-no\-swap\-tables\*(C'\fR and \f(CW\*(C`\-\-no\-drop\-new\-table\*(C'\fR is set, the trigger will remain on the original table and will be duplicated on the new table (the trigger will have a random suffix as no trigger names are unique). .IP "\-\-new\-table\-name" 4 .IX Item "--new-table-name" type: string; default: \f(CW%T_new\fR .Sp New table name before it is swapped. \f(CW%T\fR is replaced with the original table name. When the default is used, the tool prefixes the name with up to 10 \f(CW\*(C`_\*(C'\fR (underscore) to find a unique table name. If a table name is specified, the tool does not prefix it with \f(CW\*(C`_\*(C'\fR, so the table must not exist. .IP "\-\-null\-to\-not\-null" 4 .IX Item "--null-to-not-null" Allows MODIFYing a column that allows \s-1NULL\s0 values to one that doesn't allow them. The rows which contain \s-1NULL\s0 values will be converted to the defined default value. If no explicit \s-1DEFAULT\s0 value is given MySQL will assign a default value based on datatype, e.g. 0 for number datatypes, '' for string datatypes. .IP "\-\-only\-same\-schema\-fks" 4 .IX Item "--only-same-schema-fks" Check foreigns keys only on tables on the same schema than the original table. This option is dangerous since if you have FKs refenrencing tables in other schemas, they won't be detected. .IP "\-\-password" 4 .IX Item "--password" short form: \-p; type: string .Sp Password to use when connecting. If password contains commas they must be escaped with a backslash: \*(L"exam\e,ple\*(R" .IP "\-\-pause\-file" 4 .IX Item "--pause-file" type: string .Sp Execution will be paused while the file specified by this param exists. .IP "\-\-pid" 4 .IX Item "--pid" type: string .Sp Create the given \s-1PID\s0 file. The tool won't start if the \s-1PID\s0 file already exists and the \s-1PID\s0 it contains is different than the current \s-1PID.\s0 However, if the \s-1PID\s0 file exists and the \s-1PID\s0 it contains is no longer running, the tool will overwrite the \s-1PID\s0 file with the current \s-1PID.\s0 The \s-1PID\s0 file is removed automatically when the tool exits. .IP "\-\-plugin" 4 .IX Item "--plugin" type: string .Sp Perl module file that defines a \f(CW\*(C`pt_online_schema_change_plugin\*(C'\fR class. A plugin allows you to write a Perl module that can hook into many parts of pt-online-schema-change. This requires a good knowledge of Perl and Percona Toolkit conventions, which are beyond this scope of this documentation. Please contact Percona if you have questions or need help. .Sp See \*(L"\s-1PLUGIN\*(R"\s0 for more information. .IP "\-\-port" 4 .IX Item "--port" short form: \-P; type: int .Sp Port number to use for connection. .IP "\-\-print" 4 .IX Item "--print" Print \s-1SQL\s0 statements to \s-1STDOUT.\s0 Specifying this option allows you to see most of the statements that the tool executes. You can use this option with \&\*(L"\-\-dry\-run\*(R", for example. .IP "\-\-progress" 4 .IX Item "--progress" type: array; default: time,30 .Sp Print progress reports to \s-1STDERR\s0 while copying rows. The value is a comma-separated list with two parts. The first part can be percentage, time, or iterations; the second part specifies how often an update should be printed, in percentage, seconds, or number of iterations. .IP "\-\-quiet" 4 .IX Item "--quiet" short form: \-q .Sp Do not print messages to \s-1STDOUT\s0 (disables \*(L"\-\-progress\*(R"). Errors and warnings are still printed to \s-1STDERR.\s0 .IP "\-\-recurse" 4 .IX Item "--recurse" type: int .Sp Number of levels to recurse in the hierarchy when discovering replicas. Default is infinite. See also \*(L"\-\-recursion\-method\*(R". .IP "\-\-recursion\-method" 4 .IX Item "--recursion-method" type: array; default: processlist,hosts .Sp Preferred recursion method for discovering replicas. Possible methods are: .Sp .Vb 6 \& METHOD USES \& =========== ================== \& processlist SHOW PROCESSLIST \& hosts SHOW SLAVE HOSTS \& dsn=DSN DSNs from a table \& none Do not find slaves .Ve .Sp The processlist method is the default, because \s-1SHOW SLAVE HOSTS\s0 is not reliable. However, the hosts method can work better if the server uses a non-standard port (not 3306). The tool usually does the right thing and finds all replicas, but you may give a preferred method and it will be used first. .Sp The hosts method requires replicas to be configured with report_host, report_port, etc. .Sp The dsn method is special: it specifies a table from which other \s-1DSN\s0 strings are read. The specified \s-1DSN\s0 must specify a D and t, or a database-qualified t. The \s-1DSN\s0 table should have the following structure: .Sp .Vb 6 \& CREATE TABLE \`dsns\` ( \& \`id\` int(11) NOT NULL AUTO_INCREMENT, \& \`parent_id\` int(11) DEFAULT NULL, \& \`dsn\` varchar(255) NOT NULL, \& PRIMARY KEY (\`id\`) \& ); .Ve .Sp To make the tool monitor only the hosts 10.10.1.16 and 10.10.1.17 for replication lag, insert the values \f(CW\*(C`h=10.10.1.16\*(C'\fR and \f(CW\*(C`h=10.10.1.17\*(C'\fR into the table. Currently, the DSNs are ordered by id, but id and parent_id are otherwise ignored. .Sp You can change the list of hosts while \s-1OSC\s0 is executing: if you change the contents of the \s-1DSN\s0 table, \s-1OSC\s0 will pick it up very soon. .IP "\-\-skip\-check\-slave\-lag" 4 .IX Item "--skip-check-slave-lag" type: \s-1DSN\s0; repeatable: yes .Sp \&\s-1DSN\s0 to skip when checking slave lag. It can be used multiple times. Example: \-\-skip\-check\-slave\-lag h=127.0.0.1,P=12345 \-\-skip\-check\-slave\-lag h=127.0.0.1,P=12346 Please take into consideration that even when for the MySQL driver h=127.1 is equal to h=127.0.0.1, for this parameter you need to specify the full \s-1IP\s0 address. .IP "\-\-slave\-user" 4 .IX Item "--slave-user" type: string .Sp Sets the user to be used to connect to the slaves. This parameter allows you to have a different user with less privileges on the slaves but that user must exist on all slaves. .IP "\-\-slave\-password" 4 .IX Item "--slave-password" type: string .Sp Sets the password to be used to connect to the slaves. It can be used with \-\-slave\-user and the password for the user must be the same on all slaves. .IP "\-\-set\-vars" 4 .IX Item "--set-vars" type: Array .Sp Set the MySQL variables in this comma-separated list of \f(CW\*(C`variable=value\*(C'\fR pairs. .Sp By default, the tool sets: .Sp .Vb 3 \& wait_timeout=10000 \& innodb_lock_wait_timeout=1 \& lock_wait_timeout=60 .Ve .Sp Variables specified on the command line override these defaults. For example, specifying \f(CW\*(C`\-\-set\-vars wait_timeout=500\*(C'\fR overrides the default value of \f(CW10000\fR. .Sp The tool prints a warning and continues if a variable cannot be set. .Sp Note that setting the \f(CW\*(C`sql_mode\*(C'\fR variable requires some tricky escapes to be able to parse the quotes and commas. .Sp Example: .Sp .Vb 1 \& \-\-set\-vars sql_mode=\e\*(AqSTRICT_ALL_TABLES\e\e,ALLOW_INVALID_DATES\e\*(Aq .Ve .Sp Note the single backslash for the quotes and double backslash for the comma. .IP "\-\-sleep" 4 .IX Item "--sleep" type: float; default: 0 .Sp How long to sleep (in seconds) after copying each chunk. This option is useful when throttling by \*(L"\-\-max\-lag\*(R" and \*(L"\-\-max\-load\*(R" are not possible. A small, sub-second value should be used, like 0.1, else the tool could take a very long time to copy large tables. .IP "\-\-socket" 4 .IX Item "--socket" short form: \-S; type: string .Sp Socket file to use for connection. .IP "\-\-statistics" 4 .IX Item "--statistics" Print statistics about internal counters. This is useful to see how many warnings were suppressed compared to the number of \s-1INSERT.\s0 .IP "\-\-[no]swap\-tables" 4 .IX Item "--[no]swap-tables" default: yes .Sp Swap the original table and the new, altered table. This step completes the online schema change process by making the table with the new schema take the place of the original table. The original table becomes the \*(L"old table,\*(R" and the tool drops it unless you disable \*(L"\-\-[no]drop\-old\-table\*(R". .Sp Using \f(CW\*(C`\-\-no\-swap\-tables\*(C'\fR will run the whole process, it will create the new table, it will copy all rows but at the end it will drop the new table. It is intended to run a more realistic \-\-dry\-run. .IP "\-\-tries" 4 .IX Item "--tries" type: array .Sp How many times to try critical operations. If certain operations fail due to non-fatal, recoverable errors, the tool waits and tries the operation again. These are the operations that are retried, with their default number of tries and wait time between tries (in seconds): .Sp .Vb 8 \& OPERATION TRIES WAIT \& =================== ===== ==== \& create_triggers 10 1 \& drop_triggers 10 1 \& copy_rows 10 0.25 \& swap_tables 10 1 \& update_foreign_keys 10 1 \& analyze_table 10 1 .Ve .Sp To change the defaults, specify the new values like: .Sp .Vb 1 \& \-\-tries create_triggers:5:0.5,drop_triggers:5:0.5 .Ve .Sp That makes the tool try \f(CW\*(C`create_triggers\*(C'\fR and \f(CW\*(C`drop_triggers\*(C'\fR 5 times with a 0.5 second wait between tries. So the format is: .Sp .Vb 1 \& operation:tries:wait[,operation:tries:wait] .Ve .Sp All three values must be specified. .Sp Note that most operations are affected only in MySQL 5.5 and newer by \&\f(CW\*(C`lock_wait_timeout\*(C'\fR (see \*(L"\-\-set\-vars\*(R") because of metadata locks. The \f(CW\*(C`copy_rows\*(C'\fR operation is affected in any version of MySQL by \&\f(CW\*(C`innodb_lock_wait_timeout\*(C'\fR. .Sp For creating and dropping triggers, the number of tries applies to each \&\f(CW\*(C`CREATE TRIGGER\*(C'\fR and \f(CW\*(C`DROP TRIGGER\*(C'\fR statement for each trigger. For copying rows, the number of tries applies to each chunk, not the entire table. For swapping tables, the number of tries usually applies once because there is usually only one \f(CW\*(C`RENAME TABLE\*(C'\fR statement. For rebuilding foreign key constraints, the number of tries applies to each statement (\f(CW\*(C`ALTER\*(C'\fR statements for the \f(CW\*(C`rebuild_constraints\*(C'\fR \&\*(L"\-\-alter\-foreign\-keys\-method\*(R"; other statements for the \f(CW\*(C`drop_swap\*(C'\fR method). .Sp The tool retries each operation if these errors occur: .Sp .Vb 5 \& Lock wait timeout (innodb_lock_wait_timeout and lock_wait_timeout) \& Deadlock found \& Query is killed (KILL QUERY ) \& Connection is killed (KILL CONNECTION ) \& Lost connection to MySQL .Ve .Sp In the case of lost and killed connections, the tool will automatically reconnect. .Sp Failures and retries are recorded in the \*(L"\-\-statistics\*(R". .IP "\-\-user" 4 .IX Item "--user" short form: \-u; type: string .Sp User for login if not current user. .IP "\-\-version" 4 .IX Item "--version" Show version and exit. .IP "\-\-[no]version\-check" 4 .IX Item "--[no]version-check" default: yes .Sp Check for the latest version of Percona Toolkit, MySQL, and other programs. .Sp This is a standard \*(L"check for updates automatically\*(R" feature, with two additional features. First, the tool checks its own version and also the versions of the following software: operating system, Percona Monitoring and Management (\s-1PMM\s0), MySQL, Perl, MySQL driver for Perl (DBD::mysql), and Percona Toolkit. Second, it checks for and warns about versions with known problems. For example, MySQL 5.5.25 had a critical bug and was re-released as 5.5.25a. .Sp A secure connection to Percona’s Version Check database server is done to perform these checks. Each request is logged by the server, including software version numbers and unique \s-1ID\s0 of the checked system. The \s-1ID\s0 is generated by the Percona Toolkit installation script or when the Version Check database call is done for the first time. .Sp Any updates or known problems are printed to \s-1STDOUT\s0 before the tool's normal output. This feature should never interfere with the normal operation of the tool. .Sp For more information, visit . .SH "PLUGIN" .IX Header "PLUGIN" The file specified by \*(L"\-\-plugin\*(R" must define a class (i.e. a package) called \f(CW\*(C`pt_online_schema_change_plugin\*(C'\fR with a \f(CW\*(C`new()\*(C'\fR subroutine. The tool will create an instance of this class and call any hooks that it defines. No hooks are required, but a plugin isn't very useful without them. .PP These hooks, in this order, are called if defined: .PP .Vb 10 \& init \& before_create_new_table \& after_create_new_table \& before_alter_new_table \& after_alter_new_table \& before_create_triggers \& after_create_triggers \& before_copy_rows \& after_copy_rows \& before_swap_tables \& after_swap_tables \& before_update_foreign_keys \& after_update_foreign_keys \& before_drop_old_table \& after_drop_old_table \& before_drop_triggers \& before_exit \& get_slave_lag .Ve .PP Each hook is passed different arguments. To see which arguments are passed to a hook, search for the hook's name in the tool's source code, like: .PP .Vb 10 \& # \-\-plugin hook \& if ( $plugin && $plugin\->can(\*(Aqinit\*(Aq) ) { \& $plugin\->init( \& orig_tbl => $orig_tbl, \& child_tables => $child_tables, \& renamed_cols => $renamed_cols, \& slaves => $slaves, \& slave_lag_cxns => $slave_lag_cxns, \& ); \& } .Ve .PP The comment \f(CW\*(C`# \-\-plugin hook\*(C'\fR precedes every hook call. .PP Here's a plugin file template for all hooks: .PP .Vb 1 \& package pt_online_schema_change_plugin; \& \& use strict; \& \& sub new { \& my ($class, %args) = @_; \& my $self = { %args }; \& return bless $self, $class; \& } \& \& sub init { \& my ($self, %args) = @_; \& print "PLUGIN init\en"; \& } \& \& sub before_create_new_table { \& my ($self, %args) = @_; \& print "PLUGIN before_create_new_table\en"; \& } \& \& sub after_create_new_table { \& my ($self, %args) = @_; \& print "PLUGIN after_create_new_table\en"; \& } \& \& sub before_alter_new_table { \& my ($self, %args) = @_; \& print "PLUGIN before_alter_new_table\en"; \& } \& \& sub after_alter_new_table { \& my ($self, %args) = @_; \& print "PLUGIN after_alter_new_table\en"; \& } \& \& sub before_create_triggers { \& my ($self, %args) = @_; \& print "PLUGIN before_create_triggers\en"; \& } \& \& sub after_create_triggers { \& my ($self, %args) = @_; \& print "PLUGIN after_create_triggers\en"; \& } \& \& sub before_copy_rows { \& my ($self, %args) = @_; \& print "PLUGIN before_copy_rows\en"; \& } \& \& sub after_copy_rows { \& my ($self, %args) = @_; \& print "PLUGIN after_copy_rows\en"; \& } \& \& sub before_swap_tables { \& my ($self, %args) = @_; \& print "PLUGIN before_swap_tables\en"; \& } \& \& sub after_swap_tables { \& my ($self, %args) = @_; \& print "PLUGIN after_swap_tables\en"; \& } \& \& sub before_update_foreign_keys { \& my ($self, %args) = @_; \& print "PLUGIN before_update_foreign_keys\en"; \& } \& \& sub after_update_foreign_keys { \& my ($self, %args) = @_; \& print "PLUGIN after_update_foreign_keys\en"; \& } \& \& sub before_drop_old_table { \& my ($self, %args) = @_; \& print "PLUGIN before_drop_old_table\en"; \& } \& \& sub after_drop_old_table { \& my ($self, %args) = @_; \& print "PLUGIN after_drop_old_table\en"; \& } \& \& sub before_drop_triggers { \& my ($self, %args) = @_; \& print "PLUGIN before_drop_triggers\en"; \& } \& \& sub before_exit { \& my ($self, %args) = @_; \& print "PLUGIN before_exit\en"; \& } \& \& sub get_slave_lag { \& my ($self, %args) = @_; \& print "PLUGIN get_slave_lag\en"; \& \& return sub { return 0; }; \& } \& \& 1; .Ve .PP Notice that \f(CW\*(C`get_slave_lag\*(C'\fR must return a function reference; ideally one that returns actual slave lag, not simply zero like in the example. .PP Here's an example that actually does something: .PP .Vb 1 \& package pt_online_schema_change_plugin; \& \& use strict; \& \& sub new { \& my ($class, %args) = @_; \& my $self = { %args }; \& return bless $self, $class; \& } \& \& sub after_create_new_table { \& my ($self, %args) = @_; \& my $new_tbl = $args{new_tbl}; \& my $dbh = $self\->{cxn}\->dbh; \& my $row = $dbh\->selectrow_arrayref("SHOW CREATE TABLE $new_tbl\->{name}"); \& warn "after_create_new_table: $row\->[1]\en\en"; \& } \& \& sub after_alter_new_table { \& my ($self, %args) = @_; \& my $new_tbl = $args{new_tbl}; \& my $dbh = $self\->{cxn}\->dbh; \& my $row = $dbh\->selectrow_arrayref("SHOW CREATE TABLE $new_tbl\->{name}"); \& warn "after_alter_new_table: $row\->[1]\en\en"; \& } \& \& 1; .Ve .PP You could use this with \*(L"\-\-dry\-run\*(R" to check how the table will look before and after. .PP Please contact Percona if you have questions or need help. .SH "DSN OPTIONS" .IX Header "DSN OPTIONS" These \s-1DSN\s0 options are used to create a \s-1DSN.\s0 Each option is given like \&\f(CW\*(C`option=value\*(C'\fR. The options are case-sensitive, so P and p are not the same option. There cannot be whitespace before or after the \f(CW\*(C`=\*(C'\fR and if the value contains whitespace it must be quoted. \s-1DSN\s0 options are comma-separated. See the percona-toolkit manpage for full details. .IP "\(bu" 4 A .Sp dsn: charset; copy: yes .Sp Default character set. .IP "\(bu" 4 D .Sp dsn: database; copy: no .Sp Database for the old and new table. .IP "\(bu" 4 F .Sp dsn: mysql_read_default_file; copy: yes .Sp Only read default options from the given file .IP "\(bu" 4 h .Sp dsn: host; copy: yes .Sp Connect to host. .IP "\(bu" 4 p .Sp dsn: password; copy: yes .Sp Password to use when connecting. If password contains commas they must be escaped with a backslash: \*(L"exam\e,ple\*(R" .IP "\(bu" 4 P .Sp dsn: port; copy: no .Sp Port number to use for connection. .IP "\(bu" 4 S .Sp dsn: mysql_socket; copy: yes .Sp Socket file to use for connection. .IP "\(bu" 4 t .Sp dsn: table; copy: no .Sp Table to alter. .IP "\(bu" 4 u .Sp dsn: user; copy: yes .Sp User for login if not current user. .SH "ENVIRONMENT" .IX Header "ENVIRONMENT" The environment variable \f(CW\*(C`PTDEBUG\*(C'\fR enables verbose debugging output to \s-1STDERR.\s0 To enable debugging and capture all output to a file, run the tool like: .PP .Vb 1 \& PTDEBUG=1 pt\-online\-schema\-change ... > FILE 2>&1 .Ve .PP Be careful: debugging output is voluminous and can generate several megabytes of output. .SH "EXIT STATUS" .IX Header "EXIT STATUS" .Vb 10 \& INVALID_PARAMETERS = 1 \& UNSUPORTED_MYSQL_VERSION = 2 \& NO_MINIMUM_REQUIREMENTS = 3 \& NO_PRIMARY_OR_UNIQUE_KEY = 4 \& INVALID_PLUGIN_FILE = 5 \& INVALID_ALTER_FK_METHOD = 6 \& INVALID_KEY_SIZE = 7 \& CANNOT_DETERMINE_KEY_SIZE = 9 \& NOT_SAFE_TO_ASCEND = 9 \& ERROR_CREATING_NEW_TABLE = 10 \& ERROR_ALTERING_TABLE = 11 \& ERROR_CREATING_TRIGGERS = 12 \& ERROR_RESTORING_TRIGGERS = 13 \& ERROR_SWAPPING_TABLES = 14 \& ERROR_UPDATING_FKS = 15 \& ERROR_DROPPING_OLD_TABLE = 16 \& UNSUPORTED_OPERATION = 17 \& MYSQL_CONNECTION_ERROR = 18 \& LOST_MYSQL_CONNECTION = 19 .Ve .SH "SYSTEM REQUIREMENTS" .IX Header "SYSTEM REQUIREMENTS" You need Perl, \s-1DBI,\s0 DBD::mysql, and some core packages that ought to be installed in any reasonably new version of Perl. .PP This tool works only on MySQL 5.0.2 and newer versions, because earlier versions do not support triggers. Also a number of permissions should be set on MySQL to make pt-online-schema-change operate as expected. \s-1PROCESS, SUPER, REPLICATION SLAVE\s0 global privileges, as well as \s-1SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER,\s0 and \s-1TRIGGER\s0 table privileges should be granted on server. Slave needs only \&\s-1REPLICATION SLAVE\s0 and \s-1REPLICATION CLIENT\s0 privileges. .SH "BUGS" .IX Header "BUGS" For a list of known bugs, see . .PP Please report bugs at . Include the following information in your bug report: .IP "\(bu" 4 Complete command-line used to run the tool .IP "\(bu" 4 Tool \*(L"\-\-version\*(R" .IP "\(bu" 4 MySQL version of all servers involved .IP "\(bu" 4 Output from the tool including \s-1STDERR\s0 .IP "\(bu" 4 Input files (log/dump/config files, etc.) .PP If possible, include debugging output by running the tool with \f(CW\*(C`PTDEBUG\*(C'\fR; see \*(L"\s-1ENVIRONMENT\*(R"\s0. .SH "DOWNLOADING" .IX Header "DOWNLOADING" Visit to download the latest release of Percona Toolkit. Or, get the latest release from the command line: .PP .Vb 1 \& wget percona.com/get/percona\-toolkit.tar.gz \& \& wget percona.com/get/percona\-toolkit.rpm \& \& wget percona.com/get/percona\-toolkit.deb .Ve .PP You can also get individual tools from the latest release: .PP .Vb 1 \& wget percona.com/get/TOOL .Ve .PP Replace \f(CW\*(C`TOOL\*(C'\fR with the name of any tool. .SH "AUTHORS" .IX Header "AUTHORS" Daniel Nichter and Baron Schwartz .SH "ACKNOWLEDGMENTS" .IX Header "ACKNOWLEDGMENTS" The \*(L"online schema change\*(R" concept was first implemented by Shlomi Noach in his tool \f(CW\*(C`oak\-online\-alter\-table\*(C'\fR, part of . Engineers at Facebook then built another version called \f(CW\*(C`OnlineSchemaChange.php\*(C'\fR as explained by their blog post: . This tool is a hybrid of both approaches, with additional features and functionality not present in either. .SH "ABOUT PERCONA TOOLKIT" .IX Header "ABOUT PERCONA TOOLKIT" This tool is part of Percona Toolkit, a collection of advanced command-line tools for MySQL developed by Percona. Percona Toolkit was forked from two projects in June, 2011: Maatkit and Aspersa. Those projects were created by Baron Schwartz and primarily developed by him and Daniel Nichter. Visit to learn about other free, open-source software from Percona. .SH "COPYRIGHT, LICENSE, AND WARRANTY" .IX Header "COPYRIGHT, LICENSE, AND WARRANTY" This program is copyright 2011\-2018 Percona \s-1LLC\s0 and/or its affiliates. .PP \&\s-1THIS PROGRAM IS PROVIDED \*(L"AS IS\*(R" AND WITHOUT ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.\s0 .PP This program is free software; you can redistribute it and/or modify it under the terms of the \s-1GNU\s0 General Public License as published by the Free Software Foundation, version 2; \s-1OR\s0 the Perl Artistic License. On \s-1UNIX\s0 and similar systems, you can issue `man perlgpl' or `man perlartistic' to read these licenses. .PP You should have received a copy of the \s-1GNU\s0 General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, \s-1MA\s0 02111\-1307 \s-1USA.\s0 .SH "VERSION" .IX Header "VERSION" pt-online-schema-change 3.2.1 .SH "POD ERRORS" .IX Header "POD ERRORS" Hey! \fBThe above document had some coding errors, which are explained below:\fR .IP "Around line 12985:" 4 .IX Item "Around line 12985:" Non-ASCII character seen before =encoding in 'Percona’s'. Assuming \s-1UTF\-8\s0