pt-online-schema-change 使用基础9

--alter
type: string

The schema modification, without the ALTER TABLE keywords. You can perform multiple modifications to the table by specifying them with commas. Please refer to the MySQL manual for the syntax of ALTER TABLE.

The following limitations apply which, if attempted, will cause the tool to fail in unpredictable ways:

* In almost all cases a PRIMARY KEY or UNIQUE INDEX needs to be present in the table. This is necessary because the tool creates a DELETE trigger to keep the new table updated while the process is running.

A notable exception is when a PRIMARY KEY or UNIQUE INDEX is being created from existing columns as part of the ALTER clause; in that case it will use these column(s) for the DELETE trigger.

* The RENAME clause cannot be used to rename the table.

* 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.

* If you add a column without a default value and make it NOT NULL, the tool will fail, as it will not try to guess a default value for you; You must specify the default.

* DROP FOREIGN KEY constraint_name requires specifying _constraint_name rather than the real constraint_name. 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:

CONSTRAINT `fk_foo` FOREIGN KEY (`foo_id`) REFERENCES `bar` (`foo_id`)
You must specify --alter "DROP FOREIGN KEY _fk_foo".

* The tool does not use LOCK IN SHARE MODE with MySQL 5.0 because it can cause a slave error which breaks replication:

Query caused different errors on master and slave. Error on master:
'Deadlock found when trying to get lock; try restarting transaction' (1213),
Error on slave: 'no error' (0). Default database: 'pt_osc'.
Query: 'INSERT INTO pt_osc.t (id, c) VALUES ('730', 'new row')'

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.

This is a MySQL bug, similar to http://bugs.mysql.com/bug.php?id=45694, but there is no fix or workaround in MySQL 5.0. Without LOCK IN SHARE MODE, tests pass 100% of the time, so the risk of data loss or breaking replication should be negligible.

Be sure to verify the new table if using MySQL 5.0 and converting from MyISAM to InnoDB!

[root@h101 ~]# echo $i
49
[root@h101 ~]#  while true; do  echo $i; mysql -u root -pmysql -e "insert into pt.forpttest(id,name,comment,abc)  values($i,$i,$i,$i)" ;let "i=$i+1"; sleep 1 ; done
49
Warning: Using a password on the command line interface can be insecure.
50
Warning: Using a password on the command line interface can be insecure.
51
Warning: Using a password on the command line interface can be insecure.
52
Warning: Using a password on the command line interface can be insecure.
53
Warning: Using a password on the command line interface can be insecure.
54
Warning: Using a password on the command line interface can be insecure.
55
Warning: Using a password on the command line interface can be insecure.
56
Warning: Using a password on the command line interface can be insecure.
...
...
77
Warning: Using a password on the command line interface can be insecure.
78
Warning: Using a password on the command line interface can be insecure.
79
Warning: Using a password on the command line interface can be insecure.
80
Warning: Using a password on the command line interface can be insecure.
81

[root@h101 ~]# pt-online-schema-change -u root -h localhost  -pmysql  --alter='add column newcolumn char(20) not null default "duang" ' --execute D=pt,t=forpttest  
No slaves found.  See --recursion-method if host h101.temp has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `pt`.`forpttest`...
Creating new table...
Created new table pt._forpttest_new OK.
Altering new table...
Altered `pt`.`_forpttest_new` OK.
2015-10-23T14:27:33 Creating triggers...
2015-10-23T14:27:33 Created triggers OK.
2015-10-23T14:27:33 Copying approximately 47 rows...
2015-10-23T14:27:33 Copied rows OK.
2015-10-23T14:27:33 Swapping tables...
2015-10-23T14:27:33 Swapped original and new tables OK.
2015-10-23T14:27:33 Dropping old table...
2015-10-23T14:27:33 Dropped old table `pt`.`_forpttest_old` OK.
2015-10-23T14:27:33 Dropping triggers...
2015-10-23T14:27:33 Dropped triggers OK.
Successfully altered `pt`.`forpttest`.
[root@h101 ~]#

mysql> desc forpttest;
+-----------+----------+------+-----+----------+-------+
| Field     | Type     | Null | Key | Default  | Extra |
+-----------+----------+------+-----+----------+-------+
| id        | int(6)   | NO   | PRI | 0        |       |
| name      | char(10) | YES  |     | NULL     |       |
| comment   | char(10) | YES  |     | NULL     |       |
| abc       | char(10) | YES  |     | NULL     |       |
| newid     | char(20) | YES  |     | NULL     |       |
| newid2    | char(20) | NO   |     | fucktest |       |
| newcolumn | char(20) | NO   |     | duang    |       |
+-----------+----------+------+-----+----------+-------+
7 rows in set (0.00 sec)

mysql>