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 …

I downloaded Maatkit 1877 and installed it on a data archive server, then did a parallel dump of the data. I also did a regular dump of the same data using mysqldump. This isn’t a benchmark or even a real comparison, just me playing with Maatkit to get a feel for it and see how it can make my life easier. First, some stats about the database:

mysql> select table_name, table_rows from information_schema.tables where table_schema = 'somedb';
+------------+------------+
| table_name | table_rows |
+------------+------------+
| table1 | 24758808 |
| table2 | 33713412 |
| table3 | 221633443 |
| table4 | 56869734 |
| table5 | 14017081 |
| table6 | 77559278 |
| table7 | 48453337 |
+--------+------------+

mysql> call database_size_profile('somedb')\G
*************************** 1. row ***************************
…

Just a quick note to say we have reached the production stage of the book project. Production is the process of transforming our OpenOffice.org files into the final page layout using a professional typesetting program. As you can probably guess, this is later than we would have wished. This also means we won’t have the book for sale at the upcoming MySQL Conference and Expo. We will have a display copy at the O’Reilly booth at the conference, and you will be able to pre-order the book at a discount at that booth.

Indeed, this is usually a big no-no. But I have a customer where this is used, validly. Except it wasn't quite right.

Information from branches is replicated back to HQ, and since a slave can't have multiple masters, they have one slave instance for each branch. Each branch uses its own database names and the mysql system db is not replicated, so no conflict resolution is necessary (which would be the other problem with multiple masters).
Log in to any instance, and you will see all databases. And yes you can access them. But in this case they didn't always deliver the correct results.... lots of missing rows, generally. Cause? The instance configs didn't contain the --external-locking option. Added that in, and now it's working fine.

Note that this is unique to MyISAM, don't ever ever try this with InnoDB or any other engine!!
Basically, the external locking option makes MySQL server use filesystem locks …