The latest from the MySQL community

Our friend Dathan recently suggested that The Quality of mySQL lately sucks.

I’ve just ran into yet another obvious bug that has made it’s way into production.

While I’m not going to argue that more unit tests would be a good thing, I’d like to point out that what he’s referring to is not so much a bug as it is a change that happened (and was documented) in 5.0.12:

Beginning with MySQL 5.0.12, natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. The changes include elimination of redundant output columns for NATURAL joins and joins specified with a USING clause and proper ordering of output columns. The precedence of the comma operator also …

When I was comparing performance of UNION vs MySQL 5.0 index merge algorithm Sinisa pointed out I should be using UNION ALL instead of simple UNION in my benchmarks, and he was right. Numbers would be different but it should not change general point of having optimization of moving LIMIT inside of union clause being cool thing.

But So is UNION ALL indeed faster than UNION DISTINCT (the UNION is shortcut for UNION DISTINCT) ?

Indeed it is. I did not have the same data as I used for the other test but I created similar test case - table with separate indexes on "a" and "b" columns with cardinality of 100, having about 40.000.000 of rows

PLAIN TEXT SQL:

1. SELECT * FROM test.abc WHERE i=5 union  SELECT * FROM test.abc …

Today I was (again) facing a log file from a machine that had for some reason not been able to start a temporary MySQL daemon during the night to prepare for a streaming MySQL slave installation. The necessary 2nd daemon had created its new ibdata files, however just after that aborted the startup process with the following message:

Can't start server: Bind on TCP/IP port: No such file or directory
071001 23:09:55 [ERROR] Do you already have another mysqld server running on port: 3310 ?
071001 23:09:55 [ERROR] Aborting
071001 23:09:55 [Note] mysql\bin\mysqld.exe: Shutdown complete

As you can see, the port is a different one from the default MySQL port, so I can be sure there was no conflict with the primary instance. Even more curiously the same process has been working on that and other machines flawlessly for some …

Today is make public old trees day. Someone is probably going to
wonder why I am making Gemini public again, and the answer is
"someone asked". I've discovered that the only real way for me to
keep code around is to put it up publicly on my revision control system.

http://hg.tangent.org/gemini

The code is old, and I've not made this branch compile in a while. It
will not work with a modern version of MySQL, in fact only ever
worked on a 4.0 Alpha version. I am told that the code for Progress's
current database is not all that different (in the core of the
storage engine), but I really do not know. I can tell you that the
code does require assembler for one operation and that this tree does
not have a working piece for my 64bit AMD. There are a couple of
additional branches out there of …

About one month ago we posted a call for interns to work with us on various MySQL Performance related things.

We got good number of replies and a lot of people wondered what tasks exactly we could offer as surely this sort of involvement should be fun and should teach you something.

Really we did not have a list - Cool opportunities and
ideas constantly pop up plus we expect you to bring your ideas on what
you would be interested to work on and we will see if there is an
interest for us to mentor and sponsor your work.

Generally the tasks we see Interns working on are either Open Source
projects or research which would be published so besides minor
financial compensation there is publicity and education components.

Here are few things we have in mind:

Work continues apace on High Performance MySQL, Second Edition (the link leads to the chapter outline). I'm working now on Chapter 6, Advanced SQL Functionality, and thought I'd solicit input on it. Are there things you'd like to see us cover? Do you have any favorite techniques you'd like to see us include? Feel free to leave feedback in the comments. The chapter is already significantly done, with 26 pages written, but the ink's not on paper yet, so there's still time to correct omissions!

I'm on the select board of elite people who were duped into reviewing proposals for the upcoming MySQL Conference and Expo 2008, and I'm here to tell you how to get your proposal accepted. Aside from bribing me with chocolate, that is.

MySQL Toolkit version numbers are based on Subversion revision number. This release is the first past the 1,000-commit milestone. It also marks several days of being in Sourceforge's top 100 most active projects. It has been in the top 300 for a couple of months, and the top 1000 for, um, a long time. While I would hasten to say I'm not a popularity-contest-focused person, it's rewarding to see that people think this project is important and useful.

This release of MySQL Toolkit updates MySQL Parallel Dump. I had been using it on a relatively small server; yesterday I took a deep breath and started using it to generate backups from a large server with lots of data and lots of queries. Of course I found a couple bugs and decided I needed more functionality and error handling. The major new functionality is for efficiency; it defers locking as late as possible and releases locks as soon as possible, and with the --setperdb option …

This is still a bug, a very serious obvious bug which break simple UPDATES.

http://bugs.mysql.com/bug.php?id=30485

This is yet another data corruption bug

http://bugs.mysql.com/bug.php?id=31104


Xaprb has given a great write up on what should be done. It's brain dead simple to do and would ensure that mysql does not produce obvious critical bugs-which tarnishes the reputation of this great database platform.

On a side note: http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html
Making incompatible changes between versions so late in the game is very confusing.
5.0.12 a change was made to JOIN that breaks upgrading from 4.1.

If a change is going to be made at a parser level to ensure some sort of standard. IMHO it should …

I am curious about character sets and collations, especially how they are used in databases. I got some time to play with them recently. I did some testing today on MySQL. I will do the same test on Sql Server, Oracle, and PostgreSql, time permitting. I am only dealing with simplified Chinese at this point. I may take up traditional Chinese too.

Here is my setup:

1. I created a table that stores simplified Chinese characters in different character set, along with collation used, pinyin, number of strokes, and tone value. There are 126 collations in MySQL, only 10 of which are suited for simplified Chinese.

2. I used the Chinese version of the golden rule,