… healthy discussions with students about whether or not to use Foreign Key constraints on InnoDB tables. My standard response has always… NOT NULL, bogus_column char(32), PRIMARY KEY (id), KEY (parent_id), CONSTRAINT child_ibfk_1 FOREIGN KEY (parent_id) REFERENCES parent (id) ) ENGINE… 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
…: pt-heartbeat handles timezones inconsistently. Previously, pt-heartbeat respected the MySQL time zone, but this caused false readings (e.g. very… 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
… constantly referring to the amazing MySQL manual, especially the option and variable reference table. But…commanual flushblogpercona.commanual Flush_commandsblogpercona.commanual flush_timeblogpercona.commanual foreign_key_checksblogpercona.commanual ft_boolean_syntaxblogpercona.commanual ft_max_…
Post: Percona Server on the Raspberry Pi: Your own MySQL Database Server for Under $80
February 25, 2013 By 10 Comments
… mysqld Connect using the mysql client: [root@alarmpi ~]# /usr/bin/mysql Welcome to the MySQL monitor. Commands end with ; or \g. [...] mysql> SHOW ENGINES… | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES | [...] mysql> SELECT “Hello World!” AS “Success!” \G *************************** 1. row…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 8 Comments
… tables (thank you ORM by joining all tables connected with foreign keys just in case) which would take 5 seconds even though… further I found the following explanation from Timour Katchaounov in MySQL mailing list archives I have some recollection that there were… plan computation would be more important than backward compatibility. In MySQL 5.6 things are likely to get even better handling…
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… primary key, fkto3 char(32) not null, fkfrom3 char(32) not null, index(fkto3), index(fkfrom3), foreign key (fkto3) references dos3(fkfrom1), foreign key (fkto2…
Post: MySQL Error Message Nonsenses
March 16, 2008 By 7 Comments
… until you run perror: [pz@sl1 ~]$ perror 150 MySQL error code 150: Foreign key constraint is incorrectly formed What is also interesting is… 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
…” table: Discard the tablespace of the salaries table: mysql> set FOREIGN_KEY_CHECKS=0; mysql> ALTER TABLE salaries DISCARD TABLESPACE; Copy the salaries.ibd… /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> SELECT…
Post: Percona Server 5.1.59-13.0
October 16, 2011 By Leave a Comment
…table rebuild for foreign key changes. This unnecessarily delays their creation in a mysqldump output, so –innodb-optimize-keys should ignore foreign key constrains. #…InnoDB in the absence of a PRIMARY KEY. #851674 (Alexey Kopytov). Backported fix for MySQL bug #53761 (Wrong estimate for RANGE …
Post: Percona Server 5.5.16-22.0
October 16, 2011 By 2 Comments
… available here and from the Percona Software Repositories). Based on MySQL 5.5.16, including all the bug fixes in it… 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…
