Skip to content

# Category: MySQL

MySQL related posts

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

MySQL Data Types July 27, 2011July 31, 2011

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

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 common_schema Open Source July 20, 2011

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 Analysis common_schema Data Types Development Indexing INFORMATION_SCHEMA InnoDB Monitoring Open Source Schema Security SQL Stored routines July 13, 2011

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:

- Did you know you can work out simple monitoring of your server with a query? There’s a view to do that for you.
- How about showing just the good parts of the processlist?
- Does your schema have redundant keys?
- Or InnoDB tables with no PRIMARY KEY?
- Is AUTO_INCREMENT running out of space?
- Can I get the SQL statements to generate my FOREIGN KEYs? To drop them?
- And can we finally get SHOW GRANTS for all accounts, and as an SQL query?
- Ever needed a 64 bit CRC function?
- And aren’t you tired of writing the cumbersome SUBSTRING_INDEX(SUBSTRING_INDEX(str, ‘,’, 3), ‘,’, -1)? There’s an alternative.

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 Configuration InnoDB mysqldump scripts July 6, 2011

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 INFORMATION_SCHEMA Open Source Security Stored routines Views June 22, 2011June 27, 2011

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 INFORMATION_SCHEMA Security June 22, 2011

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 User Group June 18, 2011June 19, 2011

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!

## Pop quiz: what is the most basic privilege an account can be assigned with?

MySQL Security June 14, 2011

I asked this during my presentation on the MySQL Conference this year. And I got a unanimous answer from the crowd. Take a moment to think this over, before coming up with the answer. Meanwhile, an intermezzo.

Tam dam dam…

Pom pom Pom pom Pom pom…

If your answer is that the most basic privilege an account can be assigned with is the USAGE privilege, you are right!

And then again, you’re also wrong.

Continue reading » “Pop quiz: what is the most basic privilege an account can be assigned with?”

## TIMESTAMP vs. DATETIME, which should I be using?

MySQL Data Types May 22, 2011May 26, 2011

They both look the same, have roughly the same use. Nevertheless, they differ in many respects. I’ll note the difference, and note a few pitfalls and peculiarities.

#### Range

TIMESTAMP starts with the epoch, ‘1970-01-01 00:00:01’ UTC and ends with ‘2038-01-19 03:14:07’ UTC. This is all very nice today, and may actually hold up till our retirement, but the younger readers may yet have to deal with the Bug2K+38™, certain to arrive.

DATETIME starts with ‘1000-01-01 00:00:00’ and lasts up to ‘9999-12-31 23:59:59’. More on that later.

In respect of range, your current event logs may well use a TIMESTAMP value, while your grandfather’s and granddaughter’s birth dates may require DATETIME.

In general I would suggest that anything that relates to now, can be a TIMESTAMP. A new entry is added? It is added now. It can be represented by a TIMESTAMP. Anything has an expiry time of a few minutes, perhaps a few days or a month? You’ll be safe using it up till late 2037. Anything else had better use a DATETIME. In particular, dates relating to issues such as birth, insurance, the market etc. fall into this category.

History, however, does not even hold up with DATETIME. Rome fell long before MIN(DATETIME). You will have to manage your own. Not even YEAR will help you out.

#### Storage

TIMESTAMP makes for 4 bytes. DATETIME makes for 8 bytes. Now that we have this behind us, let’s see why. Continue reading » “TIMESTAMP vs. DATETIME, which should I be using?”

## Tales of the trade #5: How to author a super successful MySQL blog

MySQL Humor Planet May 18, 2011May 18, 2011

Check out the following tutorial that teaches you how to become a popular MySQL blog author:

Complete strip: tales-of-the-trade-make-your-mysql-blog-popular-full.png