Hi Workbench Users,

Now that we got the RC1 out, I would like to start collecting ideas of what you would like to see in the MySQL Workbench 5.1 release that will happen later this year.

Our current plan is (apart to improve the existing features and workflow) to add one major feature for WB 5.1 - and that is SQL querying functionality. Please think of it as an improved, extended MySQL Query Browser embedded right into the MySQL Workbench framework.

My vision is to turn MySQL Workbench into a fully featured SQL IDE. The single tool a MySQL database developer needs to start up to do all his database work.

A few key points that come to my mind.

• Database schema design, creation and maintenance (already there)
• Default data management (”CREATE INSERTS”, already there, can be improved)
• Database SQL querying
• Result set handling (like in QB, but improved)
…

Not sure about prizes yet. I have Open Query pencils (recycled newspaper) and they're transactional with rollback (the eraser ;-) but perhaps I'll come up with something else for this. Anyway, your challenge, should you choose to accept it, is:

Design a MySQL schema, and show me some sample queries (to prove your design works), for doing the following. Given a database of recipes that each contain a number of ingredients, I want to crossmatch this against what I have in my pantry/fridge/cocktailcabinet and find out which recipes I can actually make!

Depending on how you do it this may not seem too complicated; then go to the next step with possible substitute ingredients. This happens both with cooking as well as with cocktails. Devise some kind of equivalence magic (like a soundex for ingredients ;-)

Enjoy! Have some nice chocolate eggs too.
…

I live in a town that is the nemesis of e-commerce applications. The name of my town is Quartu Sant'Elena
Notice that the name contains an apostrophe, which for all practical purposes is represented by a single quote.
Single quotes have a bad reputation, because they may be a symptom of SQL injection. Whenever I enter the name of my town in a web form to buy something, I hold my breath, because I dread what comes next.
The smartest applications have a Ajax interface with online completion, and take the name of the town without problems. The less advanced ones show a multiple choice list containing my town name.
The bad ones refuse the name of the town as invalid, and force me to enter an alternate spelling (Quartu S.Elena), which is recognized by most Italians as being equivalent.
The very bad ones, after forcing me to …

I’m still looking for new entries. I get quite a few suggestions, but not all of them make it into quiz questions. Do send in your suggestions!

As the MySQL root user user, I grant access to a new employee in the organization:

GRANT SELECT ON abc.* TO "user_a"@"192.168.1.%";

Some time later, the new colleague becomes a former colleague, and being not only lazy but also unsure exactly what privileges user_a has been granted over time, I do

REVOKE ALL PRIVILEGES ON *.* FROM "user_a"@"192.168.1.%";

in order to remove all of user_a’s access to the database system.

Questions:

1. Has the original SELECT privilege granted in the GRANT statement been revoked for user_a by the REVOKE statement?
2. Suppose all privileges on all databases and tables have been revoked for user_a. Will user_a be able to log on to the MySQL server?

Show answer

One of the best deals at the MySQL Users Conference is the deep discount on certification exams. For a mere $25, you can take exams that are normally $200. But those of you planning to take your exams next month need to know some facts before your arrive.

First: All the really hot sessions with the great presenters who will fill your skull with knowledge just by their presence (and tell all the best SQL jokes) are scheduled at the same time as the exams. There are only six exam session and the six hottest presentations just happen to be at the same time. I am not sure if this is karma, bread slice falling butter side down physics, or part of an intricate plan but it falls into the 'it is a feature not a bug' realm. So read the agenda and plan your exam schedule. Trying to hurry through an exam to catch the end of a presentation will do justice to neither …

Today Sun and Zmanda announced our agreement to deliver a comprehensive, global data backup and recovery solution for MySQL Enterprise subscribers. Starting April 1st, MySQL Enterprise customers will be able to purchase ZRM for MySQL directly from Sun worldwide.

I think Zack’s comment in the press release captures the rationale for the deal:

“Protecting corporate data through effective backup and recovery is one of the most crucial tasks for a database administrator, and it can be a complex undertaking — especially for today’s large Web-scale applications,” said Zack Urlocker, VP of products, Sun Microsystems database group. “MySQL users have told us that global backup and recovery is very important to them, and we are thrilled that we can now offer ZRM for MySQL as an easy-to-use solution for protecting all of their MySQL data.”

Of …

Considering the few posts made recently, regarding Telecommuting, I thought I’d put in my $0.02, adjusted for inflation.
See Cal’s Post and this awesome job opportunity.
I want to discuss the various tools and options available to the telecommuter in 2008.

Disclaimer: I don’t want to make this another post on the benefits of Telecommuting, as I strongly believe there is no silver bullet for the problems an individual or a company faces. There are many drawbacks to telecommuting, and the positives don’t always neutralize the drawbacks.

Background: I’ve been telecommuting since 2003, that’s when I decided to quit working for the ‘man’. I chose a laptop to give myself …

I've just been pointed to the nice tool which I was waiting for years to see. It is fincore - little perl script which allows you to see what pages of file are cached in OS memory. This is really cool.

When it comes to MySQL it is very useful with MyISAM tables which has their data file cached by OS cache only so you do not have any good information from MySQL side on what data is cached. You can also use it with Innodb to see how much memory are you wasting with double buffering by not using of O_DIRECT.

Besides general clues such as 50% of my file is cached you should watch for dynamics - for example check it during backup process and compare it due to normal load - this can give you a clue if slow down happens because of extra IO pressure or just because pages were washed out. You can also check how pages are cached. For example every …

Tonight I am catching up on older e-mails — here’s another question that came to me about 2 weeks ago from a user group member that I never had time to research and answer. I have directed the original author to this post so questions you pose in the comments can be answered.

Do you know anything about indexed file systems? I’m looking for a ‘nearline’ storage solution to help with data archiving.

We have a system which at it’s peak will be accepting 15 million short records / day. In order to keep the Web front end moving nicely, we want to drop data after about 3 weeks and shift it into a higher latency, higher capacity storage system. Indexed file systems seemed like a perfect solution for this. Ideally it would have a good front end to allow execution of arbitrary queries in some language (SQL would be nice).

The only thing I’ve been able to locate is an MS product called “Microsoft Index Server” and …

Hi all,

I was asked this question in an e-mail — feel free to ask your questions in the comments, as I will point the original author to this post to answer those questions. There is not a lot of data here, so instead of me asking questions in an e-mail I figured I would open it up to the (MySQL) world. Without further ado, here’s the question:

Basically when we open more than one connection- we’re opening 5, and we do processing in mysql (innodb tables) the server cpu and memory max out and the processing grinds to a halt. One connection at a time is able to run well- even with lots of data. But once we try to process data under 5 concurrent connections, mysql gets bogged down to the point where it’s barely usable.

I’ve researched this and found a number of opinions, including whether this is just poor performance due to where the we are in the 5.1 life cycle (i.e. energies have gone into fixing big bugs not …