…servers. Example: mysql> create trigger Copy_data AFTER INSERT on t FOR EACH ROW INSERT INTO t_copy VALUE(NEW.i); mysql> insert into t …events before the statement itself. So all used variables are replicated on the slave. Example: mysql> SELECT YEAR(CURDATE()) INTO @this_year; mysql> insert into…
Post: Statement based replication with Stored Functions, Triggers and Events
December 16, 2011 By 4 Comments
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual,…Com_purge_before_dateblogpercona….insert_limitblogpercona.commanual Delayed_insert_threadsblogpercona.commanual delayed_insert…Select_full_joinblogpercona.commanual Select_full_range_joinblogpercona.commanual Select_rangeblogpercona.commanual Select_range_checkblogpercona.commanual Select…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
…before transaction which is running INSERT .. SELECT statement. This would make this transaction to be applied on the slave before INSERT… SELECT…MySQL thread id 1794751, query id 6994931 localhost root Sending data insert into test select * from sample ——– As you can see INSERT… SELECT…
Post: Using VIEW to reduce number of tables used
September 28, 2007 By 12 Comments
…insert data in the view which will be invisible. The VIEW approach works for SELECT, UPDATE, DELETE queries but not for INSERT: mysql> insert… triggers on views, specially INSTEAD OF triggers so you could write triggers to perform insert inserts to base tables instead of views. BEFORE INSERT triggers…
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 26 Comments
…triggers must be changed. The work in the trigger is immediate and affects every transaction. Finally, MySQL has limited trigger… `test`.`demo`. mysql> select * from flexviews.test_demo\G …insertions happened inside of the same transaction, and that the insertions happened before…
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
…is quite helpful for MySQL Performance optimization tasks…before lookup queries are not normalized (would require parsing) before… number of your selects – Com_select and see …inserts so you can see how much of inserted queries are used: Qcache_hits/Qcache_inserts…_prunes can be triggered even if there …
Comment: Using VIEW to reduce number of tables used
October 5, 2007 By 12 Comments
…BEFORE INSERT trigger for the shared table (owner_id field should be in the view as well): [CODE]CREATE TRIGGER post_insert BEFORE INSERT…: [CODE]CREATE VIEW owner_posts AS SELECT * FROM posts WHERE owner_id = @owner_id…18/using-views-with-variables-in-mysql/]outlined[/URL] a simple workaround for …
Post: The story of one MySQL Upgrade
October 8, 2010 By 8 Comments
…triggers. The problem should be fixed by upgrade so we just have to keep this into account. We move database to MySQL… there are only few INSERT ON DUPLICATE KEY UPDATE …reporting wrong checksums while running SELECT INTO OUTFILE and diffing …put them in production. Before upgrading Master however we…
Post: Knowing what pt-online-schema-change will do
November 1, 2012 By 1 Comment
….`score`) CREATE TRIGGER `pt_osc_test_t_ins` AFTER INSERT ON `test…` <= ?) ) /*pt-online-schema-change 10451 copy nibble*/ SELECT /*!40001 SQL_NO_CACHE */ `guest_country`,…MySQL server (privileges, etc.), or other oddities, there is a good likelihood that a dry run will reveal them. In summary: before…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…dropping secondary indexes from the new table before copying the data, and then recreating them…mysql> INSERT INTO t(c) VALUES (RAND()); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO t(c) SELECT…the VARCHAR column to trigger table rebuilds without affecting the table size. mysql> SET expand_…
