Upgrading from MySQL 5.0 to MySQL 5.1 or Percona Server 5.1 you may run into issues with mysql_upgrade - it will identify some tables to be upgraded and will attempt to run REPAIR TABLE for them. This will fail with "The storage engine for the table doesn't support repair" error message. This seems to confuse a lot of people and I've seen people doing failsafe upgrade path of dumping and reloading complete database confused by this error message, which of course works, but can take quite a lot of time.

Another solution is to simply run ALTER TABLE tbl ENGINE=INNODB which will rebuild table with new MySQL version and normally will fix issues identified by mysql_upgrade.
You can use mysqlcheck -A --check-upgrade to identify tables which need to be fixed such a way.

With Oracle intentions to make Innodb default storage engine in next MySQL release I'm hopeful minor annoyances …

Our Universe is full of ironies. But some ironies are just too hard to take.

As you may have guessed (!!!), I am an avid Linux developer and user. Though once upon a time I did develop under Windows. Yes, believe it. And on one particular case, I got to be on a first-name basis with some of the Microsoft Software Engineers to resolve issues we were having with their OLE crap — what the Holy Gods of Microsoft decided to redub as “Active-X”.

But I digress. For the past 10 years, I have been solid Linux and have defenestrated Windows for the most part. But as you know, you can never really completely eliminate Windows.  Despite your best efforts, it will always be (for now, at least) the 500 pound gorilla in any room you care to be in. The installed software base there is just staggering, and most have no Linux options.

But then that’s why projects like Wine and the many wonderful hypervisors …

TO_DAYS() prunes two partitions instead of one
If you are partitioning by date, chances are that you are using TO_DAYS(). And depending on how you have partitioned your table, your queries are as fast as you expect them to be. However, there are cases where your query takes twice as long as it should, and of course this will not make you happy.

For example, in a table partitioned by month, when …

Let me premise this post with the statement I think the MySQL documentation is an excellent and highly accurate resource. I think the MySQL docs team do a great job, however like software and people, documentation is not perfect.

As members of the MySQL community you can always contribute to improve the process by reading the documentation and logging any issues as Documentation Bugs.

Some time ago in a discussion with a friend and colleague, we were talking about changes in historical defaults that had been improved finally in MySQL 5.4 The specific discussion was on the new default innodb_buffer_pool_size and we both agreed it increased significantly. One said 1GB, the other said 128MB. Who was right? Well we both were, and we were both inaccurate depending on versions.

Referencing the 5.4 Manual in …

I caught some of the keynotes and discussion at the Linux Foundation Collaboration Summit today, and was particularly interested in the panel discussion on open source and cloud computing. While we are used to hearing and talking about how important open source software is to cloud computing (open source giving to cloud computing), moderator John Mark Walker posed the question of whether cloud computing gives back? The discussion also rightfully focused on openness in cloud computing, how open source might or might not translate to cloud openness and the importance of data to be open as well.

The discussion also centered on some issues regarding open standards and how open is open enough for cloud computing? …

As I wrote about 2 years ago the feature of Innodb to store copy of master's position in Slave's Innodb tablespace got broken. There is a lot of discussions at the corresponding bug report while outcome of the fix remained uncertain for me (the bug is market duplicate while the bugs it seems to be duplicate for describe different issues).
Anyway. The customer came to me today having the following message in the error log after Slave crash while running MySQL 5.1.41 (extra virgin version without XtraDB or Percona Patches)

InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 0 10000000, file name mysql-bin.005000
InnoDB: Last MySQL binlog file position 0 20000000, file name ./mysql-bin.003000

He …

If you are one of the few unfortunate blokes that’s pulling your hair out over random File Menu popups on OpenOffice — Word or Spreadsheet — and have been hitting your head against a brick wall trying to find the solution, I hear you.

I intially thought there was some interaction with Skype, but now I have to retract that statement. In all honesty, I have no clue what the problem is.

But I decided to just watch the “random” File Menu problem and time it. And now I am more confused than ever.

On my computer running 64-bit Ubuntu 9.10 (Karmic), I noticed that the File Menu toggles twice a second when OpenOffice has the focus. One toggle event happens precisely on the 11th second; the other toggle event happens around 35th to the 45th second. The second toggle seems to be related to when I launch OpenOffice; the first is always precisely on the 11th second.

And this is something peculiar to …

There was a discussion on LinkedIn one month ago that caught my eye:

Database search by "within x number of miles" radius?

Anyone out there created a zipcode database and created a "search within x numer of miles" function ?
Thankful for any tips you can throw my way..

J

A few people commented that some solutions wouldn't scale. To understand why these sorts of geographic search queries are problematic in MySQL, it's best to show some execution plans on dummy data:

PLAIN TEXT SQL:

1. EXPLAIN …

While I worked on several web projects I collected a few wishes for MySQL (this collection is more user specific - a MySQL internal wishlist will come later) - which I use in most cases. I still fixed a few things, but don't know if they are good enough for production (because I just started hacking MySQL).

Read the rest »

I had an interesting case recently. The customer dealing with large MySQL data warehouse had the table which was had data merged into it with INSERT ON DUPLICATE KEY UPDATE statements. The performance was extremely slow. I turned out it is caused by hundreds of daily partitions created for this table. What is the most interesting (and surprising) not every statement is affected equally as you can see from the benchmarks above:

I got the following test table created:

PLAIN TEXT SQL:

1. CREATE TABLE `p10` (
2.     `id` int(10) UNSIGNED NOT NULL,
3.     `c`  int(10) UNSIGNED NOT NULL,
4.      PRIMARY KEY (`id`),
5.      KEY(c)
6.      ) ENGINE=InnoDB
7.      PARTITION BY RANGE(id) (
8.     …