… of locking in Innodb is a single row (well, the PRIMARY KEY index entry for that row). This means typically on a… exceeded you will still get the deadlock error. This is not implemented for full BEGIN … COMMIT multi-statement transactions since it… us are used to the tradeoffs we take when deploying conventional stand-alone MySQL Innodb with asynchronous slaves. We may not think…
Post: Repair MySQL 5.6 GTID replication by injecting empty transactions
March 26, 2013 By 1 Comment
… I explained how to repair MySQL 5.6 GTID replication using two different methods. I didn’t mention the famous SET… simple reason, it doesn’t work anymore if you are using MySQL GTID. Then the question is: Is there any easy… in slave server is not working because of an error: Last_SQL_Error: Error ‘Duplicate entry ’4′ for key ‘PRIMARY” on query. Default…
Post: Long PRIMARY KEY for Innodb tables
October 3, 2006 By 8 Comments
… other key will refer to the rows by primary key. I also recommended to use sequential primary keys so you do not end up having random primary key BTREE…` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`url`,`thumb_width`,`thumb_height`) ) ENGINE=InnoDB; Why did I use this… crash is painful and great to be avoided. Long primary key – why did not I use auto_increment id in this case ? I could…
Post: MySQL Optimizer and Innodb Primary Key
October 3, 2006 By 9 Comments
… used the following simple table for tests: CREATE TABLE `innodb` ( `id` int(10) unsigned NOT NULL, `a` int(11) default NULL, PRIMARY KEY (`id`), KEY… every key also holds primary key value so primary key value can be read from index, making some queries index covered which previously was not: Notice “Using…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… are being fetched do not belong to the secondary index definition (and hence covering index optimization is not used), then primary key lookups have to be performed for each secondary key entry fetched. This means that secondary key lookups for…
Post: To UUID or not to UUID ?
March 13, 2007 By 28 Comments
… an article comparing UUID and auto_increment primary keys, basically advertising to use UUID instead of primary keys. I wanted to clarify this a bit… BTREE buffer. The most efficient approach here is not to use auto_increment but use certain partitioned sequences, for example you can have…
Post: Logging Foreign Key errors
September 27, 2012 By Leave a Comment
… post I wrote about how to log deadlock errors using Percona Toolkit. Foreign key errors have the same problems. InnoDB only logs… to store the information: CREATE TABLE foreign_key_errors ( ts datetime NOT NULL, error text NOT NULL, PRIMARY KEY (ts) ); 2- Run the tool as… by the FK, a PRIMARY KEY. So, the command that causes the error was: mysql> ALTER TABLE employees DROP PRIMARY KEY; Conclusion If you…
Post: Instrumentation and the cost of Foreign Keys
September 20, 2010 By 9 Comments
…light arguments healthy discussions with students about whether or not to use Foreign Key constraints on InnoDB tables. My standard response … PRIMARY KEY (id) ) ENGINE=InnoDB; CREATE TABLE child ( id INT NOT NULL AUTO_INCREMENT, parent_id INT NOT NULL, bogus_column char(32), PRIMARY KEY (id), KEY…
Post: How (not) to find unused indexes
October 16, 2009 By 2 Comments
…(45) NOT NULL DEFAULT ”, `HeadOfState` char(60) DEFAULT NULL, `Capital` int(11) DEFAULT NULL, `Code2` char(2) NOT NULL DEFAULT ”, PRIMARY KEY (`Code`), KEY `Population… index on query 3 had high cardinality but should not be used since too many countries have a population greater than 1000…
Post: STOP: DELETE IGNORE on Tables with Foreign Keys Can Break Replication
February 2, 2012 By 1 Comment
…(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`t1_c1`) ) ENGINE=InnoDB; CREATE TABLE `t2` ( `t2_c1` int(10) unsigned NOT NULL, PRIMARY KEY (`t2_c1… this? Simple, 1) do not use IGNORE, be critical about your data 2) use ROW* based replication. When using the latter, MySQL will log…
