… are not aware how IGNORE behaves on tables with FOREIGN KEYs, you could be in for a surprise. Let’s take a table… foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t2_c1`) REFERENCES `t1` (`t1_c1`) ON UPDATE CASCADE) 1 row in set… in set (0.00 sec) * MIXED mode will not work since the query in this example will be considered STATEMENT (http://dev.mysql…
Post: STOP: DELETE IGNORE on Tables with Foreign Keys Can Break Replication
February 2, 2012 By 1 Comment
Post: Instrumentation and the cost of Foreign Keys
September 20, 2010 By 9 Comments
I occasionally get in to light arguments healthy discussions with students about whether or not to use Foreign Key constraints on InnoDB tables… lock_data: 5 2 rows in set (0.00 sec) The same example without the foreign key constraints does not block on the last statement. We also see the expected output change to: mysql> SELECT…
Post: Percona Toolkit 2.1.8 released today with beta support for MySQL 5.6
January 9, 2013 By Leave a Comment
… handles this correctly. Fixed bug 1015590: pt-mysql-summary doesn’t handle renamed variables in Percona Server 5.5. Some renamed variables… MySQL time zone. Fixed bug 1079341: pt-online-schema-change checks for foreign keys on MyISAM tables. Since MyISAM tables can’t have foreign keys…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…the amazing MySQL manual, especially the option and variable …_commandsblogpercona.commanual flush_timeblogpercona.commanual foreign_key_checksblogpercona.commanual ft_boolean_syntaxblogpercona…. lock_wait_timeoutblogpercona.commanual locked_in_memoryblogpercona.commanual logblogpercona.commanual log_…
Post: Percona Server on the Raspberry Pi: Your own MySQL Database Server for Under $80
February 25, 2013 By 10 Comments
… locking, and foreign keys | YES | YES | YES | [...] mysql> SELECT “Hello World!” AS “Success!” \G *************************** 1. row *************************** Success!: Hello World! 1 row in set (0… uses Percona Server for MySQL with the latest mysql client, which is untested, but should be “ok/fine” in most situations If you…
Post: On Character Sets and Disappearing Tables
June 21, 2012 By 1 Comment
The MySQL manual tells us that regardless of whether or not we use “SET FOREIGN_KEY_CHECKS=0″ before making schema changes, InnoDB will not allow a column referenced by a foreign key constraint to be modified in such a way that the foreign key will reference a column with…
Post: MySQL Error Message Nonsenses
March 16, 2008 By 7 Comments
… ~]$ perror 150 MySQL error code 150: Foreign key constraint is incorrectly formed What is also interesting is – this error 150 is in fact internal… to our foreign keys example it is not only you need to run perror to find out this error is foreign key related but you also need to run SHOW INNODB STATUS to see what exactly Innodb did not like about foreign key…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 10 Comments
… transaction IDs and log sequence numbers that are stored in the tablespace files also differ between servers. Therefore our…var/lib/mysql/data/employees/ Import the new tablespace: mysql> set FOREIGN_KEY_CHECKS=0; mysql> ALTER TABLE salaries IMPORT TABLESPACE; mysql> set FOREIGN_KEY_CHECKS=1; mysql>…
Post: Percona Server 5.1.59-13.0
October 16, 2011 By Leave a Comment
… rebuild for foreign key changes. This unnecessarily delays their creation in a mysqldump output, so –innodb-optimize-keys should ignore foreign key constrains. #859078 (… clustered index by InnoDB in the absence of a PRIMARY KEY. #851674 (Alexey Kopytov). Backported fix for MySQL bug #53761 (Wrong …
Post: Percona Server 5.5.16-22.0
October 16, 2011 By 2 Comments
… the Percona Software Repositories). Based on MySQL 5.5.16, including all the bug fixes in it, Percona Server 5.5.16… table rebuild for foreign key changes. This unnecessarily delays their creation in a mysqldump output, so –innodb-optimize-keys should ignore foreign key constrains. #859078 (Alexey…
