Every once in a while, I find myself needing to round a number up to the nearest even power of 10, or round a time interval to the nearest quarter of an hour, or something like that. This is actually quite simple, but for some reason I always find myself confused about how to do it. I have to reason it out all over again, instead of just remembering how to do it. Perhaps writing this blog post will help me remember next time.

The basic idea for rounding to whole multiples is to divide the number, losing precision. Then round, floor, or ceiling the resulting number, and multiply to get back to the original magnitude. For rounding to fractions, reverse the process: multiply, round and divide again.

This actually works for any programming language, not just SQL. But I find myself doing it in SQL most often.

Here’s an example of how to turn a year into a decade:

mysql> SELECT FLOOR(YEAR(NOW()) / 10) * 10 AS …

Over a chat on the #workbench IRC channel, Collin Cusce has written a handy little Lua script to automatically create relationships (through foreign keys) for his reverse engineered database.

Reverse engineering the DB to import tables into a diagram was easy, but their database used no “hard” foreign keys and an ER diagram without relationships wouldn’t be of much use. So one option would be to individually connect each foreign key column pair by hand, using the relationship picking tool . But doing that for the thirty-something tables in the database would be too much work and something could be overlooked and left out. The other option would be to automate that, since all such foreign keys followed a naming convention like <table>_id<column> or fk_id<table>. And that led to the following (slightly modified) …

I tweaked the mext script so it auto-detects the necessary column widths for each sample.

Get mext here.

Hi,

We are currently running code coverage for MySQL Cluster testing and have hit a few bumps along the road when it cam to collecting the "block" coverage for the NDBD.

I wanted to share them here for others that might run into similar frustrations when doing testing code coverage.

Gcov accumulates during each run information on which functions and lines of code are called and a total for the times called.

The problem comes from when gcov dumps this information out. The actual data is dumped on the "exit" of the program.

After much time tracking this issue down, it turns out that the NDBD code had been changed to use _exit() instead of just exit()

What is the difference?

exit()
Terminate the process after cleanup.

_exit()
Terminate process immediately.

So by calling _exit(), gcov never had a …

Have you ever looked at the source code of MySQL Workbench? There are quite some hidden gems there, e.g. the OpenGL canvas and our latest addtion: a new UI port of the famous open source edit control Scintilla. This port allows us to use this fantastic editor control natively on Mac OS X Leopard as a Cocoa framework.

In fact, we are using Scintilla for a long time already, mainly on Windows. It allows us to give you a powerful editor interface for SQL code. This includes features like syntax highlighting, text markers (e.g. for marking syntax errors), code folding, code completion and many more. However, so far we could not use it on OS X. There is a Carbon port available, but that did not work well (particularly, if the rest of the UI is using Cocoa) so we decided to write our own …

I always say that the ultimate MySQL tuning script is an expert human. To that end, I generally try to build tools that help a human be more productive with the raw information from MySQL. One of the things we look at during a performance audit is the MySQL status counters. It’s useful to look at a) absolute values and b) several incremental snapshots. I’ve written a small shell script called “mext” that can make this a little easier.

It looks like this:

baron@kanga:~$ mext -- mysqladmin ext -ri1 -c3
Aborted_clients                               1      0      0
Aborted_connects                              0      0      0
Binlog_cache_disk_use                         0      0      0
Binlog_cache_use                              0      0      0
Bytes_received                             1167     35     35
Bytes_sent …

If you are a Mac developer, interested in a new professional challenge and like what we have done with MySQL Workbench so far, please consider applying for our open position in the Workbench team.We are looking for an Objective-C / UI coding wunderkind who is also not easily scared by C++.

You can be located anywhere around the world, only hard requirements are that you are comfortable with working from home, have a decent internet connection and are not too far from an airport.We would be happy to have you on the team.

More details about the job below. To speed up the hire process please send your CV to workbench@sun.com. We are preferring people with open source background so please do not forget to include links to your open source work.

Job Summary:

Sun Microsystems is looking for a strong Mac UI developer with in-depth knowledge of Objective-C, C++, X-Code and the …

SQL and Relational Theory

SQL and Relational Theory How to Write Accurate SQL Code by C. J. Date, O’Reilly 2009. Page count: 266 pages of “real” text, plus hefty appendixes. (Here’s a link to the publisher’s site: SQL and Relational Theory How to Write Accurate SQL Code).

This is a very important book for anyone involved with databases. Before I say why, I need to apologize to Mr. Date. I tech-reviewed part of the book and did not care for it. I am afraid I was quite a curmudgeon in my review comments. So, Mr. Date, if you’re reading this — I want to say I …

The time is just running and it's already time, again, for students to apply for this year's Google Summer of Code program. PHP and MySQL are among the list of 150 participating organizations. So if you're a student and are interested to learn how OpenSource works, do some networking with some famous people or just want a Google T-Shirt it's your time to take a look at the different idea pages (PHP, MySQL, others) or come up with an own idea and apply. Oh if accepted you even can earn some money as part of the program ...

If you’ve been holding out for that golden opportunity, now’s a great time to get involved in Maatkit.

Until now I haven’t really made a conscious effort to open-source the decision process and get people involved; Maatkit has been largely driven by so-called “real-world needs,” as perceived through my little lens on the world (and emails from the whole Percona team telling me when something’s wrong). I guess I am likely to remain some kind of benevolent dictator, because I created Maatkit and historically I’m the main hacker. But it doesn’t have to stay that way, and the project and users will be better off if it doesn’t.

So I’ve been trying to break out of the rut of just having some little email exchange with people using the tools, and bring things onto the …