Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

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

share|improve this answer
    
Thanks for your nice reply.Is there any other way to do partition the table in zero downtime? –  Raja Sep 30 '13 at 10:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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