Category: MySQL

MySQL related posts

MySQL Global status difference using single query

MySQL

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”

Finding CURRENT_USER for any user

MySQL

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.

The problem

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.

The inconsistency

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”

Speaking at “August Penguin 2011”

MySQL

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!

23:59:59 is not the end of the day. No, really!

MySQL

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:

  1. ’23:59:59′ refers to the beginning of the last second of the day. This means a full second is missing in concept. No one would write such a code on a C, Java or PHP application: that would be a newbie’s mistake. But people feel at ease doing it with SQL

    2. Of course, this works when timestamps are in a one second resolution. Which leads me to:

  2. One second resolution is a temporary issue. Already MariaDB 5.3 presents with microsecond support for NOW() and TIMESTAMP, TIME and DATETIME columns. Sometime in the not-so-far future the standard MySQL distribution will have that, too.

Continue reading » “23:59:59 is not the end of the day. No, really!”

common_schema: looking for contributions

MySQL

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

What kind of contributions are wanted?

So, I’m mostly interested right now in:

  • Views: providing more insight on metadata (data types, schemata, volumes, transactions, locks, connections, etc.)
  • Views: SQL code generation (e.g. generate my FOREIGN KEYs)
  • Functions: DETERMINISTIC, NO SQL, preferably no control flow inside, making for simple calls what is usually a typical, repeating function combination.
  • Tables: small, static data that is handy to have around. But really, small.

Less interested in:

  • Procedures
  • OS dependent solutions (e.g. calling LOAD_FILE(‘/proc/meminfo’) which only makes sense on Linux)

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!

Announcing common_schema: common views & routines for MySQL

MySQL

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.

What does it do?

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.

What do I need to install?

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.

Continue reading » “Announcing common_schema: common views & routines for MySQL”

Useful sed / awk liners for MySQL

MySQL

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.

mysqldump

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”

ROUTINE_PRIVILEGES implementation

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”

MySQL security: inconsistencies

MySQL

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 ALL PRIVILEGES inconsistency

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”

MySQL User Group Meeting, June 2011, Israel

MySQL

It’s been quite a while since a MySQL User Group Meeting was held in Israel. I’m happy to announce that the next meeting will take place on June 28th, starting 16:30. Talks will be in Hebrew. Full details here.

This meeting is organized by Valinor, who will also be hosting it, and who are planning to make this a tradition. Thumbs up!

  • Rotem Fogel will be presenting on Reaching best Scalability on MySQL Based Applications
  • Gabi Davis will be presenting on Working with MySQL in Real Life: Do’s and Don’ts
  • I will be presenting on MySQL Replication Solutions and Enhancements.

The meeting is open to everyone, do come!