So I sometimes get asked if we funnel back bug reports or patches back to MySQL from Drizzle. Also, MariaDB adds some interest here as they are a lot closer (and indeed compatible with) to MySQL. With Drizzle, we have deviated really quite heavily from the MySQL codebase. There are still some common areas, but they’re getting rarer (especially to just directly apply a patch).

Back in June 2009, while working on Drizzle at Sun, I found a bug that I knew would affect both. The patch would even directly apply (well… close, but I made one anyway).

So the typical process of me filing a MySQL bug these days is:

• Stewart files bug
• In the next window of Sveta being awake, it’s verified.

This happened within a really short time.

Unfortunately, what happens next isn’t nearly as awesome.

Namely, nothing. For a year.

So a year later, I filed it in launchpad for MariaDB.

So, MariaDB is gearing up for a release, it’s a relatively low priority bug (but it does have a working, correct and obvious patch), within 2 months, Monty applied it and improved the error checking around it.

So MariaDB bug 588599 is Fix Committed (June 2nd 2010 – July 20th 2010), MySQL Bug 45377 is still Verified (July 20th 2009 – ….).

(and yes, this tends to be a general pattern I find)

But Mark says he gets things through… so yay for him.2

I started translation of Part 3 "Other cases". In this part I mostly describe how wrong settings can lead to misterious errors. First chapter in this part about large amount of data.

Part 3. Other cases.

Chapter 8. Large amount of data.

There are cases when query is just symptom of wrong behavior, but true reason is wrong settings.

One of the cases is too small max_allowed_packet for data sent. MySQL
server variable max_allowed_packet defines maximum possible amount of
data which MySQL server can receive or send. Amount of
max_allowed_packet is specified in bytes.

Error usually looks like:

This is short chapter which gives some clue what to do with concurrent transactions.

Chapter 7. Concurrent transactions.

Yet another frequent example of similar problem is "Lock wait timeout
exceeded" error while you use InnoDB tables. Most often running query
SHOW ENGINE INNODB STATUS is enough to find the problem, because it
will show last transactions. But output of this command does not
contain information about all queries in the same transaction, but only
about last one. What to do if SHOW ENGINE INNODB STATUS doesn't provide
all information?

...

 Rest of the chapter is here

This is new part which contains information about what to do if problem is repeatable only when queries run concurrently.

Chapter 6. Locks and deadlocks.

In the last part we discussed how to find cause of the problem in case
if it is always repeatable. But there are cases when problem occurs
only under particular circumstances.

For example, such easy query can run long enough:

Translation of chapter 5 which describes alternate methods for searching problem query just published. It starts like:

Chapter 5. Alternate methods for finding problem query.

I already wrote about using of general query log requires resources.
Part of the problem can be solved if use new feature of MySQL 5.1:
online logging which allows to turn general query log to on or to off
without stopping the MySQL server.

Unfortunately this doesn't always work: you can have old
version of MySQL server which has not such a feature, general query log
can contain very large amount of information, so you can just miss the
problem in thousands of correct queries, you can have some other own
reason.

But what to do if you can not use general query log?

One of the methods is write log files using your application.
Add a code which will write queries which your application sends to
MySQL to the log file.

With this method you can tune how and what to write to the log
file. Would be good if you will write return value and error messages
in the same log file.

Method #9: tune your application in such a way so it will write queries to log files itself.

Yet another method is using proxy which will get queries and write them to the file.

One of most preffered variants is MySQL Proxy, because this is
scriptable proxy made for working with MySQL server. It uses MySQL
client-server protocol. You can write programs for MySQL Proxy using
Lua programming language.

Below you can see example how to write general query log using MySQL Proxy:

...

 Rest of the chapter is here

This chapter starts as:

Chapter 4. Miscellaneous.

There are cases when wrong output is just symptom of wrong input made before.

For example, you start to receive wrong data at particular step of the
scenario. After analysis of the SELECT query (or queries) is clear what
queries are correct and return exactly same data which exists in the
table (or tables).

This means wrong data was inserted at the earlier step.

How to know when it happened?

Start from the step of the scenario which exists just before
step which does output, check every query as was described in earlier
chapters. If everything works correctly, examine earlier step, repeat
until you find the error.

We can examine example with list from Chapter 1 as example of such behavior.

Lets see at output one more time.

...

Really I could put here longer quotes, but I don't like how this interface prints code. So you can find rest of the chapter here.

Comments and translation corrections are welcome.

This Thursday (June 10th, 14:00 UTC), Darren Cassar will rerun his February 25 presentation of Securich - Security Plugin for MySQL. (Recording of the session failed in February; hopefully it will succeed this time.) According to Darren, the author of the plugin, Securich is an incredibly handy and versatile tool for managing user privileges on MySQL through the use of roles. It basically makes granting and revoking rights a piece of cake, not to mention added security it provides through password expiry and password history, the customization level it permits, the fact that it runs on any MySQL 5.0 or later and it's easily deployable on any official MySQL binary, platform independent.
More information here: http://www.securich.com/about.html.

For MySQL University sessions, point your browser to this page. You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session recording, and more.)

MySQL University is a free educational online program for engineers/developers. MySQL University sessions are open to anyone. All sessions (slides & audio) are recorded; the links will be on the respective MySQL University session pages which are listed on the MySQL University home page.

Translation of "Chapter 3. Wrong data in database or what to do with problem DML query." of  "Methods for searching errors in SQL application" just published.

 This is short chapter which discuss single method of dealing with mysterious DML query.

Chapter 3. Wrong data in database or what to do with problem DML query.

Problems with wrong data happens not only with SELECT queries like
cases we discussed in chapter 2, but in cases of querires which modify
data: DML queries.

Lets discuss example below

User can expect what DELETE query will remove all rows from tables
t1 and t2: "using t1, t2;" used and there is no WHERE clause. But as
you see this is not true.

Please pay attentsion for a row which says "0 rows affected". This means what 0 rows were removed! But why?

....

 Rest of the chapter is here

Comments and translation corrections are always welcome.

Glyn Moody has an interesting piece on Why Patents are Like Black Holes where he looks at the situation when a large patent holder goes bankrupt - or is about to. His point is that even if a company otherwise can go out of business cleanly, the patents often remain as a piece of "IPR" that can come back and haunt us like a zombie.

Also Matt Asay recently weighed in on the subject:

read more

This Thursday (June 3rd, 14:00 UTC), Reggie Burnett, head of Connector/NET development, will present the New Features in Connector/NET 6.3.

For MySQL University sessions, point your browser to this page. You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session recording, and more.)

MySQL University is a free educational online program for engineers/developers. MySQL University sessions are open to anyone. All sessions (slides & audio) are recorded; the links will be on the respective MySQL University session pages which are listed on the MySQL University home page.