Blog by Shlomi Noach
…you’re traveling on family holiday, and each time you wish to suggest you should check up on the tourist information center, you repeatedly come up with: “Let’s ask at the INFORMATION_SCHEMA and see what we can get”.
Also, doing so while wearing an OpenSQL Camp T-shirt doesn’t help.
Have just read MySQL Global status difference using MySQL procedures / functions, by Andres Karlsson. Have commented, but realized I did not provide with a direct answer. In the comment, I suggested checking out a solution based on views, found in common_schema. But the solution in common_schema is split into two views, due to the fact views cannot handle derived tables subqueries.
Well, here’s a single query to do that: it checks GLOBAL_STATUS twice, 10 seconds apart in the following sample. It uses SLEEP() to actually wait between the two reads. Yes, you can do that with a query.
The following query shows all GLOBAL_STATUS values that have changed during the sample period.
[UPDATE] query updated to work with MySQL 5.6 optimizer Continue reading » “MySQL Global status difference using single query”
A MySQL account is a user/host combination. A MySQL connection is done by a user connecting from some host.
However, the user/host from which the connection is made are not the same as the user/host as specified in the account. For example, the account may be created thus:
CREATE USER 'temp'@'10.0.0.%' IDENTIFIED BY '123456';
The host as specified in the above account is a wildcard host. A connection by the ‘temp’ user from ‘10.0.0.3’ can map into that account. It thus happens that the connected user is ‘temp’@’10.0.0.3’, yet the assigned account is ‘temp’@’10.0.0.%’.
MySQL provides with the USER() and CURRENT_USER() which map to the connected user and the assigned account, respectively, and which lets the current session identify the relation between the two. Read more on this on the MySQL docs.
And the trouble is: MySQL only provides this functionality for the current session. Surprisingly, given a user/host combination, I cannot get MySQL to tell me which account matches those details.
And I care because there is an inconsistency. Namely, when I do SHOW PROCESSLIST MySQL tells me the user & host from which the connection is made. It does not tell me the account for which the process is assigned. Continue reading » “Finding CURRENT_USER for any user”
I will be speaking at August Penguin 2011 (אוגוסט פינגווין), on August 12th in Ramat-Gan, Israel.
August Penguin is the annual meeting of Hamakor society: an Israeli society for Free Software and Open-Source Code (read more here).
I’ll be holding a non-technical talk about MySQL, titled “MySQL and the Open Source Sphere”. In this talk I will be presenting my impressions of the nature of open source development of MySQL and surroundings: the core server, the various forks, patches, 3rd party tools, companies involved, etc. So this is a general “get to know who’s who & what’s what in the MySQL world”.
This is a 30 minutes talk. I will surely not cover every open source development in this field, so my apologies in advance to those I leave out. The truth is, there’s so much going on lately I can hardly keep up with reading the announcements. Truly, this is a wonderful time for open source development with MySQL.
Talk will be held in Hebrew.
See you there!
How would you check whether some TIMESTAMP column falls within a given day, say July 26th, 2011?
This is a question I tend to ask students, and usually I get the same range of answers. Some are wrong; some cannot utilize an index, some are correct, and some are temporarily correct. I wish to take a closer look at the last. Such an answer looks like this:
SELECT * FROM some_table WHERE timstamp_column BETWEEN '2011-07-26 00:00:00' AND '2011-07-26 23:59:59'
Yikes! I get my allergies when I see this one.
Technically this seems correct. And it seems to work so far for people. There are two things that disturb me:
Of course, this works when timestamps are in a one second resolution. Which leads me to:
Continue reading » “23:59:59 is not the end of the day. No, really!”
In my announcement for common_schema I have failed to deliver the following message:
I will be happy to receive contributions to common_schema, and I will be happy to have contributors on this project
So, I’m mostly interested right now in:
Less interested in:
I’ve got a few ideas already, and have received contributions and ideas from others (I’m looking at you, Roland).
If you have a good idea you’d like to share, why don’t you submit it under the Issues page (submit as “Request for new component”)
Everything is done on my spare time, and there’s not much of that. So, if you do submit something and it’s not in right away, please be patient. Thanks!
Today I have released common_schema, a utility schema for MySQL which includes many views and functions, and is aimed to be installed on any MySQL server.
There are views answering for all sorts of useful information: stuff related to schema analysis, data dimensions, monitoring, processes & transactions, security, internals… There are basic functions answering for common needs.
Some of the views/routines simply formalize those queries we tend to write over and over again. Others take the place of external tools, answering complex questions via SQL and metadata. Still others help out with SQL generation.
Here are a few highlights:
There’s more. Take a look at the common_schema documentation for full listing. And it’s evolving: I’ve got quite a few ideas already for future components.
Some of these views rely on heavyweight INFORMATION_SCHEMA tables. You should be aware of the impact and risks.
There’s no script or executable file. It’s just a schema. The distribution in an SQL file which generates common_schema. Much like a dump file.
Listing some useful sed / awk liners to use with MySQL. I use these on occasion.
sed, awk & grep have many overlapping features. Some simple tasks can be performed by either. For example, stripping empty lines can be performed by either:
grep '.' awk '/./' sed '/./!d' grep -v '^$' awk '!/^$/' sed '/^$/d'
It’s a matter of taste & convention which tool and variation to use. So for any script I suggest, there may be many variations, possibly cleaner, shorter; feel free to comment.
The output of mysqldump is in particular useful when one wishes to make transformation on data or metadata. Continue reading » “Useful sed / awk liners for MySQL”
Following up on MySQL security: inconsistencies, and on MySQL bug #61596, I was thinking it may take a long time till the non-existent ROUTINE_PRIVILEGES view is implemented. Here’s my own implementation of the view.
I’ve followed the somewhat strange conventions used in the *_PRIVILEGES tables in INFORMATION_SCHEMA, where the IS_GRANTABLE is a separate column, although in 2nd 1st normal form.
I present it here as a query, using session variables, rather than a view definition: Continue reading » “ROUTINE_PRIVILEGES implementation”
Doing some work with MySQL security, I’ve noticed a few inconsistencies. They’re mostly not-too-terrible for daily work, except they get in my way right now.
The preferred way of assigning account privileges in MySQL is by way of using GRANT.
With GRANT, one assigns one or more privileges to an account, such as SELECT, UPDATE, ALTER, SUPER ,etc. Sometimes it makes sense for an account to have complete control over a domain. For example, the root account is typically assigned with all privileges. Or, some user may require all possible privileges on a certain schema.
Instead of listing the entire set of privileges, the ALL PRIVILEGES meta-privilege can be used. There is a fine issue to notice here; typically this is not a problem, but I see it as a flaw. Assume the following account:
root@mysql-5.1.51> GRANT ALL PRIVILEGES ON world.* TO 'world_user'@'localhost'; root@mysql-5.1.51> SHOW GRANTS FOR 'world_user'@'localhost'; +---------------------------------------------------------------+ | Grants for world_user@localhost | +---------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'world_user'@'localhost' | | GRANT ALL PRIVILEGES ON `world`.* TO 'world_user'@'localhost' | +---------------------------------------------------------------
This makes sense. We granted ALL PRIVILEGES and we see that the account is granted with ALL PRIVILEGES.
Now notice the following: Continue reading » “MySQL security: inconsistencies”