Hi everyone,
this is my first blog entry and my maiden language is french, so, it is not as easy as it should be. Also, I will never pretend being a good writer so, be warned. I joined MySQL earlier in July as a senior consultant and I have created this blog at the suggestion of Ronald Bradford, a senior consultant on the same team as I am. I am just back from vacation and I currently flying to Cupertino CA for a performance tuning and high availability course. I am rather new to MySQL so I hope to learn a lot, especially from the performance tuning part. What I really like from MySQL is the scalability it allows, I don’t know any other generally available database that allows a similar scaling.

I spend my last week of vacation doing camping with my wife and three daughters at the Oka national park camping near Montreal. It’s a very nice camping in a mature forest maid of large pines and oak trees. I have to say, there are some …

Recently I’ve been making more modifications to the MySQL source. Part of making modifications is testing them. For years I’ve been curious why there are both tests and mysql-test sub directories in the source tarballs. Arjen Lentz tells me that tests is old and that mysql-test is the current testing framework. This makes sense since the manual testing pages are all about mysql-test.

Are there plans to clean up the remnants of the old testing framework? What else is there in the tarball that’s outdated and needs to be removed?

Are you getting ready for MySQL 5.1? You should. I know that it's taken a long way from alpha to beta, but now it is really close to GA. Really. There is no way it's going to stay beta forever. Very soon someone in the high management will realize that all the outstanding bugs were fixed, and MySQL 5.1 is ready for prime time.
And then, you will have to get used to it. Meaning that there a lot of stuff that is so attractive, that you will want to explore the manual in search of guidance.
Let me give you a preview of what's there for you.

Partitioning

Better start reading some theory on this stuff. If you have performance problems with huge loads of …

So, if anybody is crazy enough to grab patches from the commits@lists.mysql.com list and try and use them… you may have run into this problem (which I do every few weeks/months): new files aren’t created. That’s right kids, BitKeeper (or at least the post-commit hook that mails out the patches) produces patch files where new files are in a completely different format than GNU patch expects. Not even BK can import these patches (bk import -temail or -tpatch just don’t do it).

So… I present this script - unfuck_bk_patches.pl which once run across a patch that includes new files, allows you to apply it using patch (or, for example, quilt).

I release it under the “sworn at software license”… which means you’re allowed to do anything you want with it as long as at some point you have sworn at computer software for being crappy.

For code segement:

while (fp {
#if defined(__i386__) || defined(__x86_64__)
uchar** new_fp = (uchar**)*fp;
fprintf(stderr, "%p\n", frame_count == sigreturn_frame_count ?
*(fp + SIGRETURN_FRAME_OFFSET) : *(fp + 1));
#endif /* defined(__386__) || defined(__x86_64__) */

fp = new_fp;
++frame_count;
}

As suggested by Paul,
if it is a signal frame, its frame OFFSET is different.
It is defined by SIGRETURN_FRAME_OFFSET.

Since we are going to print stacktrace inside a normal function, we don't need consider this.
Here, fp (frame pointer) is EBP (Extend Base Pointer) of this caller. Its content *fp is the EBP of its caller.
That's why we can trace to the stack frame of callers by using
new_fp = *fp;

The return address of the callee is next to EBP. In this code segment,
we print *(fp + 1) to …

This is one of those things that seemed extremely difficult and cumbersome, but turned out to be trivially simple, and relatively painless. Here is the case:
Using PHP to generate XML documents from a MySQL Database
I am working with a MySQL database storing lots human readable text. The text takes the form of semi-structured, well-formed XML. This particular XML-format is based on a very small set of elements borrowed from HTML and is used mostly for semantic markup of the text.

The XML fragments are fairly small, and not represent an end product. They are part of a larger unit: ultimately, they are a part of some document. The final document is not stored as a whole, because it is conceived and maintained on the level of its parts: all these individual XML fragments logically form distinct units.

…

MySQL AB's recent changes to the Community/Enterprise split have made people go as far as calling the split a failure. I don't think it's working well either, but it could be fixed. Here's what I think would make Enterprise a compelling offer.

      $ for N in `seq 1 10` ; do mysql -e "CREATE TABLE t$N (id int)"; done

Some time ago, I created …

A friend of mine called me today, asking for advice on a baffling error.
He has just finished upgrading a database server from 4.1.20 to 5.0.45but, when attempting a backup to all databases with mysqldump, he gets a discomforting message:

mysqldump: Got error: 1044:
Access denied for user 'root'@'localhost' to database 'information_schema'
when using LOCK TABLES

Of course, the error is doubly strange.

• First, because mysqldump is not supposed to attempt dumping the information schema. I know for sure, since I had to write a special script to dump the I_S when I really needed to export its data.
• Second, because there was actually …

I start to see applications being built utilizing VIEWs functionality which appeared in MySQL 5.0 and quite frequently VIEWs are used to help in writing the queries - to keep queries simple without really thinking how it affects server performance.

Even worse than that - looking at the short table which just gets single row from the table by the key we think this is simple query, while can be real monster instead with complexity hidden away in VIEW definition.

Just another day I worked on optimizing application which uses VIEWs and was looking at the long running query which just joined 2 tables... I ran EXPLAIN for it and got 200 of rows in the result set just for explain due to several layers of cascaded views built on top of one another so it is easy to write the queries, some of them it turn used subqueries subselects and derived tables.

It is also very dangerous if you assume MySQL would optimize your VIEWs same way …