3

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?

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.