patternsqlMinor
pt-online-schema-change tool will work for existing triggers for a table?
Viewed 0 times
tableonlinewillworkforexistingtriggerschangeschematool
Problem
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
I have tried to do partition by this below way,
The table structure is,
Whether can I do partition with the existing tri
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=userThe 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=InnoDBWhether can I do partition with the existing tri
Solution
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
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
Context
StackExchange Database Administrators Q#50572, answer score: 3
Revisions (0)
No revisions yet.