August 21, 2014

Recovering from a bad UPDATE statement

Did you just run an UPDATE against your 10 million row users table without a WHERE clause?  Did you know that in MySQL 5.5 that sometimes you can recover from a bad UPDATE statement?  This is possible if you are running in binlog_format=ROW ! Imagine this scenario:

We run an accidental UPDATE statement that […]

STOP: DELETE IGNORE on Tables with Foreign Keys Can Break Replication

DELETE IGNORE suppresses errors and downgrades them as warnings, if you are not aware how IGNORE behaves on tables with FOREIGN KEYs, you could be in for a surprise. Let’s take a table with data as example, column c1 on table t2 references column c1 on table t1 – both columns have identical set of rows for […]

Hijacking Innodb Foreign Keys

I guess I’m first to post in 2012 so Happy New Year all blog readers ! Now back to HardCore MySQL business – foreign Keys. MySQL supported Foreign Keys for Innodb for many years, yet rudimentary support initially added in MySQL 3.23.44 have not been improved in new releases as much as I’d like. We […]

Instrumentation and the cost of Foreign Keys

I occasionally get in to light arguments healthy discussions with students about whether or not to use Foreign Key constraints on InnoDB tables.  My standard response has always been: “it depends on how much of a tradeoff you are willing to make for performance. In some situations the cost can be considerable”. .. that’s when […]

Innodb Recovery Update – The tricks what failed.

As I wrote we had been recovering corrupted Innodb Tablespace and it is finally done now. As this was over than 1TB worth of data we really tried to avoid dumping the data and find some other way to recovery. Examining Innodb page content and crash information we figured out it should be page of […]

Innodb locking and Foreign Keys

Today I was working with application which uses Innodb and foreign keys and got into locking problems possibly due to foreign keys, so I did a little investigation on that matter. Interesting enough it looks like most people do not think about foreign keys overhead in terms of locking. The overhead about checking referenced table […]

Long PRIMARY KEY for Innodb tables

I’ve written and spoke a lot about using short PRIMARY KEYs with Innodb tables due to the fact all 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 updates which can be very expensive. Today […]

INSERT ON DUPLICATE KEY UPDATE and summary counters.

INSERT … ON DUPLICATE KEY UPDATE is very powerful but often forgotten MySQL feature. It was introduced in MySQL 4.1 but I still constantly see people unaware of it. Myself I like this feature big deal because it is designed in truly MySQL style – very efficient solution for freqent task while keeping it beautiful […]

Percona XtraDB Cluster 5.6.19-25.6 is now available

Percona is glad to announce the new release of Percona XtraDB Cluster 5.6 on July 21st 2014. Binaries are available from downloads area or from our software repositories. We’re also happy to announce that Ubuntu 14.04 LTS users can now download, install, and upgrade Percona XtraDB Cluster 5.6 from Percona’s software repositories. Based on Percona […]

Managing shards of MySQL databases with MySQL Fabric

This is the fourth post in our MySQL Fabric series. In case you’re joining us now, we started with an introductory post, and then discussed High Availability (HA) using MySQL Fabric here (Part 1) and here (Part 2). Today we will talk about how MySQL Fabric can help you scale out MySQL databases with sharding. Introduction At the […]