Mysql – pt-online-schema-change tool will work for existing triggers for a table

MySQLtrigger

I have a table name "user" ,I have tried to do partition the table and it was having a trigger already.

When i have tried to alter the table with pt-online-schema-change.It was showing the below error.

"The table Client.user has triggers. This tool needs to create its own triggers, so the table cannot already have triggers."

I have tried to do partition by this below way,

pt-online-schema-change --execute --user=root --password=***** --host=localhost  --alter "partition by range(id) (partition po values less than (963299666),partition p1 values less than (963499666),partition p2 values less than (963699666),partition p3 values less than (MAXVALUE))" D=Client,t=user

The table structure is,

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `scrip` int(11) DEFAULT NULL,
  `entered` int(11) NOT NULL DEFAULT '0',
  `customer` varchar(50) NOT NULL DEFAULT '',
  `machine` varchar(64) NOT NULL DEFAULT '',
  `username` varchar(50) DEFAULT NULL,
  `clientversion` varchar(20) DEFAULT NULL,
  `clientsize` int(11) DEFAULT NULL,
  `priority` int(11) DEFAULT NULL,
  `description` varchar(80) DEFAULT NULL,
  `type` varchar(20) NOT NULL DEFAULT '',
  `path` varchar(255) DEFAULT NULL,
  `executable` varchar(20) DEFAULT NULL,
  `version` varchar(20) DEFAULT NULL,
  `size` int(11) DEFAULT NULL,
  `lenth` int(11) DEFAULT NULL,
  `windowtitle` varchar(255) DEFAULT NULL,
  `string1` varchar(255) DEFAULT NULL,
  `string2` varchar(255) DEFAULT NULL,
  `text1` text,
  `text2` text,
  `text3` mediumtext,
  `text4` text,
  `servertime` int(11) NOT NULL DEFAULT '0',
  `uuid` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `sservertime` (`servertime`,`customer`,`machine`,`scrip`),
  KEY `custmach` (`customer`,`machine`,`servertime`),
  KEY `customer` (`customer`,`scrip`,`servertime`),
  KEY `machine` (`customer`,`machine`,`scrip`,`servertime`),
  KEY `servertime` (`servertime`)
) ENGINE=InnoDB

Whether can I do partition with the existing trigger using pt-online-schema-change?

How to resolve this issue?

Can anyone give solution?

Best Answer

According to the documentation, the tool accomplishes its magic of keeping the old and new tables in sync while the copy process is happening via the creation of its own triggers and is therefore not compatible with tables already using triggers no matter what kind of change you intend to make.

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.

— http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html

Related Question