There's a campaign started by Monty Widenius to save MySQL from the evil clutches of Oracle. You can read about it here.

Happy New Year! Monday night is the first meeting of the North Texas MySQL Users Group for 2010! And the topic for discussion will be MySQL Workbench.

Workbench is a tool for visual database design, schema documentation, change management, and is replacing the old MySQL GUI tools. If you have not tried the latest releases, you will be for a pleasant surprise.

NorthTexasMySQL.org meets at 7:00PM sharp at the Sun Offices, Suite 700, 16000 Dallas Tollway, Dallas TX. We welcome all who want to know more about MySQL, databases, or Open Source. Admission is free and I will bring cookies!

Mark wrote about how to find situations where InnoDB read-ahead is a bottleneck. What he didn’t disclose, though, is his trick to disable read-ahead without restart or recompile of MySQL. See, there’s no internal “disable read ahead knob”. But there is…

buf_read_ahead_random(...){ ...
       if (srv_startup_is_before_trx_rollback_phase) {
                /* No read-ahead to avoid thread deadlocks */
                return(0);
        }

This variable is tested at two functions – buf_read_ahead_linear() and buf_read_ahead_random() and nowhere else. So yeah, “server startup is before transaction rollback phase” is another way of saying “don’t do read ahead, please please”.

gdb -ex "set  srv_startup_is_before_trx_rollback_phase=1" \
    --batch -p $(pidof mysqld)

And many …

If there’s one thing that will always make me angry, it’s people that should not be editing my code going and editing my code. If you want to change something on the server and you have sudo privileges please let the real admin know beforehand. I don’t mind people improving processes or scripts but if [...]

It's no real breakthrough, but it can have a very significant performance meaning to use covering indexes. Now you are sure to ask what a covering index really is? The answer is quite simple. If a index can provide all the data a query needs it becomes a covering index for it thus covering all the data the query needs. Still sounds too complicated? Let's have a look at a simple example. Let us

Others have done it, so why shouldn’t I do it, too? Well, usually that’s not my line of thought, but when today I read David Linsin's blog post about his stats I thought I might follow along.

Overall stats

The overall visits to my blog – and countless others with no doubt – display the workday/weekend jagged line one would expect. The summer months seem to be a little lower on average, but that’s ok, people deserve their vacations. Blue line is 2009, green line is 2008 for comparison.

We've all been there: Getting some totally unreadable portion of SQL queries from someone else's code, and you are hard working to find the logic in order to understand it. Now, HeidiSQL can reformat such SQL, so it gets readable again. Just press the relevant button on the main toolbar:


Example code, before reformatting:

select * # select
from bla join bla on b=1 left
join blub on /* big comment select */ b.ud = udfu
where biuaosdi=1 and concat( a , 'das' ) like date_sub(now(), interval 1 day)
order ' concat () ' # select
by rand() group by askdhja limit 10, 199

... and after reformatting:

SELECT * # select
FROM bla
JOIN bla ON b=1
LEFT JOIN blub ON /* big comment select */ b.ud = udfu
WHERE biuaosdi=1 AND CONCAT(a, 'das') LIKE DATE_SUB(NOW(), INTERVAL 1 DAY)
ORDER ' concat () ' # …

MySQL has an unusual grants system that allows a user to be specified by host, ip or network address. That is you identify a user as ’some_user’@'host.host.name’, ’some_user’@'1.2.3.4′ or ’some_user’@'10.3.%’.

That is quite a nice facility but using it is rather tricky. This potentially provides a lot more security as it allows you to specify that different types of database users can only perform certain actions from different types of hosts. So even if you know the user and password you may have trouble getting into a mysqld server. That’s good.

However, this flexibility comes at a price. There are no tools to help you manage this and I have often seen people resorting to using the simplest type of grant, for some_user@’%', or some_user@’10.%’.

I recently wrote a smalltemplate script which would allow me to …

MySQL provides some features for particular version numbers and on particular platforms only. So, in some situations it gets really important to find the MySQL version number installed. If you are faced with such a problem you can use any of the following techniques to find MySQL version number.

It’s New Year’s Eve, a date that should strike terror into the hearts of many, because tomorrow a bunch of their queries are going to fail.

Queries to “find all birthdays in the next week” and similar are always a nightmare to write. If you want to see a bunch of examples, go look at the user-contributed comments on the MySQL date and time function reference. This post is about a slightly saner way to do that. There’s still some nasty math involved, but a) a lot less of it, and b) at least the query will be able to use indexes[1].

So here’s my tip: instead of storing the user’s full birthdate, just store the month and day they were born. Try it. You’ll love it!

[1] Yes, I know Postgres can index a function. So this can be considered a jab at MySQL, which can’t.

Related posts:

1. …