Thanks to everyone who signed up and attended the webinar I gave this week with Tim Callaghan on Scaling MySQL. For those who missed it and are interested, the video and slides are now posted here.

A brief description of the webinar is also below.

MySQL implementations are often kept relatively small, often just a few hundred GB or less. Anything beyond this quickly leads to painful operational problems such as poor insertion rates, slow queries, hours to days offline for schema changes, prolonged downtime for dump/reload, etc. The promise of scalable MySQL has remained largely unfulfilled, until TokuDB.

TokuDB v5.0 delivers

• Exceptional Agility — Hot Schema Changes allow read/write operations during index creation or …

Dr. Dobb’s published yesterday an article by Jimmy Yang and John Russel about Full-Text Search with InnoDB:

"MySQL's latest InnoDB engine can now do extensive, high-performance, full text search. A quick primer delivers all the goodies.

Oracle recently provided access to many new MySQL 5.6 features through http://labs.mysql.com for the user community to test and comment on. One notable feature is the InnoDB Full-Text Search (FTS) engine. It lets users build FULLTEXT indexes on InnoDB tables to represent text-based content and speed up searches for words and phrases. Combining full-text search with InnoDB tables opens up text capability to transactional applications, where the textual data is frequently inserted, updated, and deleted. Given the importance of this feature to the MySQL audience, this article explains the design of InnoDB FTS and …

Dr. Dobb’s published yesterday an article by Jimmy Yang and John Russel about Full-Text Search with InnoDB:

"MySQL's latest InnoDB engine can now do extensive, high-performance, full text search. A quick primer delivers all the goodies.

Oracle recently provided access to many new MySQL 5.6 features through http://labs.mysql.com for the user community to test and comment on. One notable feature is the InnoDB Full-Text Search (FTS) engine. It lets users build FULLTEXT indexes on InnoDB tables to represent text-based content and speed up searches for words and phrases. Combining full-text search with InnoDB tables opens up text capability to transactional applications, where the textual data is frequently inserted, updated, and deleted. Given the importance of this feature to the MySQL audience, this article explains the design of InnoDB FTS and …

This is a somewhat uncommon error, so I wanted to take a moment and post the error and an explanation in order to make it easier for those who run into this in the future.

The main error is this (full pasted below):

InnoDB: Assertion failure in thread 139838283589376 in file log/log0recv.c line 1094
InnoDB: Failing assertion: !page || (ibool)!!page_is_comp(page) == dict_table_is_comp(index->table)

Basically, this is what it can look like after a power outage. In fact, this is the only time I have seen it, but I won’t say that it is the only way you could encounter it.

How do I deal with this, you ask?

Well, the above error shows that the internal InnoDB dictionary is corrupt.

Unfortunately, in this situation, you must recreate the tablespace. And if you don’t have a current backup + binlogs, then that means you will also need to …

I found Dennis the Menace, he now has a job as system administrator for a hosting company. Scenario: client has a problem with a server becoming unavailable (cause unknown) and has it restarted. MySQL had some page corruption in the InnoDB tablespace.

The hosting provider, being really helpful, goes in as root and first deletes ib_logfile* then ib* in /var/lib/mysql. He later says “I am sorry if I deleted it. I thought I deleted the log only. Sorry again.”  Now this may appear nice, but people who know what they’re doing with MySQL will realise that deleting the iblogfiles actually destroys data also. MySQL of course screams loudly that while it has FRM files it can’t find the tables. No kidding!

Then, while he’s been told to not touch anything any more, and I’m trying to see if I can recover the deleted files on ext3 filesystem (yes there are tools for that), he goes in again and puts an ibdata1 file back. No, …

I work for a real estate website and we are running out of property numbers for some of our listings. Many moons ago logic in the code stated that listing types. For example a Commercial listing or Residential listing ID start with a different digit. Residential listings start with a '1' and Commercial listings start with a '5' for example. 


Putting aside that logic for a second, we are in a situation whereby our Commercial listings are now in the range of 599000. Meaning we only have a few new listings before, according to the 'business' here, the world ends. (As the numbers will rollover to 600000 and the hard coded numeric logic in the code will blow up and cause a fire in the datacenters we run).


So the thoughts are to re-set the Auto Increment value appending a few zeros to keep the logic of the listing starting with a '5'. The change SQL is …

Recently, someone emailed me:I have a requirement in MYSQL as follows:
we have a table EMP and we have to restrict the users not delete employees with DEPT_ID = 10. If user executes a DELETE statement without giving any WHERE condition all the rows should be deleted except those with DEPT_ID = 10.

We are trying to write a BEFORE DELETE trigger but we are not able to get this functionality.

I have seen your blog where you explained about Using an UDF to Raise Errors from inside MySQL Procedures and/or Triggers. Will it helps me to get this functionality? Could you suggest if we have any other alternatives to do this as well?Frankly, I usually refer people that write me these things to a public forum, but this time I felt like …

I’ve been benchmarking and testing TokuDB for a few months now. One goal of benchmarking is to understand what is limiting the performance of a particular configuration. I frequently use “show engine [innodb/tokudb] status;” from within the MySQL command line client as part of my research.

As I run most of my benchmarks on InnoDB as well as TokuDB, I noticed that there are significant differences in the way each present status information. InnoDB returns a single row, with various sections and carriage returns to maintain readability. In contrast, TokuDB presents one piece of status information per row (currently 139 rows as of TokuDB v5.0.5). This is an important distinction if you want to parse, compare, or store discrete status values. Here is sample output from each engine. I’ve cut out portions of each to maintain readability.

InnoDB plugin v1.0.13

mysql> show engine innodb status; …

Typical MySQL environment involves one Master receiving writes and multiple slaves to scale the reads. The “slave” term has been used in MySQL because the Slave servers have to perform every task in copying from the Master binlog, then updating their relay logs and finally committing to the Slave databases. The Master plays no role in replication here other than storing the replication events in the binlog.
With this kind of Master- Slave set up, there are several limitations-
-       Slave lag -       Stale or old data -       Data loss -       Manual failover which is error-prone and time consuming
In SchoonerSQL, there is no concept of “Slaves” inside synchronous cluster. We refer to it as "Read Masters" because of our synchronous approach and different replication architecture. It is …

Everyone who started using MySQL before 5.5 started off with MyIsam. It was the default storage engine and you had to go out of your way to use anything else. It was a good looking database, rugged, simple and fast in many respects. But crash recovery was not a MyIsam forte. Neither was locking or transactions. Speed was pretty good on reads. But many database snobs pointed to the short comings of MyIsam and claimed it was proof that MySQL was a ‘toy database’.

But InnoDB arrived with transactions, row level locking, and better crash recovery than MyIsam. Many detractors of MySQL now started talking positively about it. In some cases the performance for some bench marks was behind MyIsam. Notice the use of ‘was‘. Now the InnoDB and server teams report to the same management and are going through all the code carefully to achieve better performance. Now the REDO log in its own table space, buffer pools can be …