Skip to content

## oak-hook-general-log: streaming general log

MySQL logs openark kit python scripts March 21, 2010March 21, 2010

I’m seeking input on a new openark kit utility I’ve started to implement.

The tool, oak-hook-general-log, will hook up to a MySQL (>= 5.1) server, and stream the general log into standard output. It looks like this:

>     bash$ python src/oak/oak-hook-general-log.py --socket=/tmp/mysql.sock --user=root
>     2010-03-21 10:18:42     root[root] @ localhost []       79      1       Query   SELECT COUNT(*) FROM City
>     2010-03-21 10:18:48     root[root] @ localhost []       79      1       Query   DELETE FROM City WHERE id=1000
>     2010-03-21 10:18:54     root[root] @ localhost []       79      1       Query   SHOW PROCESSLIST
>     2010-03-21 10:19:06     root[root] @ localhost []       79      1       Quit
>     2010-03-21 10:19:07     root[root] @ localhost []       93      1       Connect root@localhost on
>     2010-03-21 10:19:07     root[root] @ localhost []       93      1       Query   select @@version_comment limit 1
>     2010-03-21 10:22:33     root[root] @ localhost []       93      1       Query   SELECT City.Name, Country.Name FROM Country JOIN City ON Country.Capit
>     2010-03-21 10:22:58     root[root] @ localhost []       93      1       Quit

Since output is written to stdout, one can further:

>     bash$ python src/oak/oak-hook-general-log.py --socket=/tmp/mysql.sock --user=root | grep Connect
>     bash$ python src/oak/oak-hook-general-log.py --socket=/tmp/mysql.sock --user=root | grep webuser@webhost

What the tool does is to enable table logs, and periodically rotate the mysql.general_log table, read and dump its content.

Continue reading » “oak-hook-general-log: streaming general log”

## But I DO want MySQL to say “ERROR”!

MySQL Configuration Data Types sql_mode Syntax March 12, 2010March 12, 2010

MySQL is known for its willingness to accept invalid queries, data values. It can silently commit your transaction, truncate your data.

- Using GROUP_CONCAT with a small group_concat_max_len setting? Your result will be silently truncated (make sure to check the warnings though).
- Calling CREATE TEMPORARY TABLE? You get silent commit.
- Issuing a ROLLBACK on non-transactional involved engines? Have a warning; no error.
- Using LOCK IN SHARE MODE on non transactional tables? Not a problem. Nothing reported.
- Adding a FOREIGN KEY on a MyISAM table? Good for you; no action actually taken.
- Inserting 300 to a TINYINT column in a relaxed sql_mode? Give me 255, I’ll silently drop the remaining 45. I owe you.

#### Warnings and errors

It would be nice to: Continue reading » “But I DO want MySQL to say “ERROR”!”

## Proper SQL table alias use conventions

MySQL Java Opinions python SQL Syntax March 11, 2010March 11, 2010

After seeing quite some SQL statements over the years, something is bugging me: there is no consistent convention as for how to write an SQL query.

I’m going to leave formatting, upper/lower-case issues aside, and discuss a small part of the SQL syntax: table aliases. Looking at three different queries, I will describe what I find to be problematic table alias use.

Using the sakila database, take a look at the following queries: Continue reading » “Proper SQL table alias use conventions”

## mk-schema-change? Check out ideas from oak-online-alter-table

MySQL openark kit Schema scripts March 10, 2010

In response to Mark Callaghan’s post mk-schema-change.

I apologize for not commenting on the post itself, I do not hold a Facebook account. Anyway this is a long write, so it may as well deserve a post of its own.

Some of the work Mark is describing already exists under openark kit‘s oak-online-alter-table. Allow me to explain what I have gained there, and how the issue can be further pursued. There is relevance to Mark’s suggestion.

oak-online-alter-table uses a combination of locks, chunks and triggers to achieve an almost non-blocking ALTER TABLE effect. I had a very short opportunity to speak with Mark on last year’s conference, in between bites. Mark stated that anything involving triggers was irrelevant in his case.

The triggers are a pain, but I believe a few other insights from oak-online-alter-table can be of interest. Continue reading » “mk-schema-change? Check out ideas from oak-online-alter-table”

## Things to monitor on MySQL, the user’s perspective

MySQL Execution plan Monitoring mycheckpoint March 10, 2010

Working on mycheckpoint, I have the intention of adding custom monitoring. That is, letting the user define things to monitor. I have my own thoughts, I would be grateful to get more input!

#### What would the user want to monitor?

Monitoring for the number of SELECT statements per second, InnoDB locks, slave replication lag etc. is very important, and monitoring utilities provide with this information. But what does that tell the end user? Not much.

The experienced DBA may gain a lot. The user would be more interested in completely other kind of information. In between, some information is relevant to both.

Say we were managing an on-line store. We want to monitor the health of the database. But the health of the database is inseparable from the health of the application. I mean, having little to no disk usage is fine, unless… something is wrong with the application, which leads to no new purchases.

And so a user would be interested in monitoring the number of purchases per hour, or the time passed since last successful purchase. This kind of data can only be generated by a user’s specific query. Looking at the charts, the user would then feel safer and confident in the wellness of his store app.

Continue reading » “Things to monitor on MySQL, the user’s perspective”

## Tip: faster than TRUNCATE

MySQL InnoDB SQL March 9, 2010

TRUNCATE is usually a fast operation (much faster than DELETE FROM). But sometimes it just hangs; I’ve has several such uncheerful events with InnoDB (Plugin) tables which were extensively written to. The TRUNCATE hanged; nothing else would work; minutes pass.

TRUNCATE on tables with no FOREIGN KEYs should act fast: it translate to dropping the table and creating a new one (and it all depends on the MySQL version, see the manual).

What’s faster than TRUNCATE, then? If you don’t have triggers nor FOREIGN KEYs, a RENAME TABLE can come to the rescue. Instead of:

>     TRUNCATE log_table

Do:

>     CREATE TABLE log_table_new LIKE log_table;
>     RENAME TABLE log_table TO log_table_old, log_table_new TO log_table;
>     DROP TABLE log_table_old;

I found this to work well for me. Do note that AUTO_INCREMENT values can be tricky here: the “new” table is created with an AUTO_INCREMENT value which is immediately taken in the “working” table. If you care about not using same AUTO_INCREMENT values, you can: Continue reading » “Tip: faster than TRUNCATE”

## Quick reminder: avoid using binlog-do-db

MySQL Configuration Replication March 2, 2010

Nothing new about this warning; but it’s worth repeating:

Using binlog-do-db is dangerous to your replication. It means the master will not write to binary logs any statement not in the given database.

Ahem. Not exactly. It will not write to binary logs any statement which did not originate from the given database.

Which is why a customer, who was using Toad for MySQL as client interface to MySQL, and by default connected to the mysql schema, did not see his queries being replicated. In fact, he later on got replication errors. If you do:

>     USE test;
>     INSERT INTO world.City VALUES (...)

Then the statement is assumed to be in the test database, not in the world database.

Slightly better is using replicate-do-db on the slave machines. At least we allow the master to write everything. But still, for the same reasons, slaves may fail to repeat a perfectly valid query, just because it has been issued in the context of the wrong database. replicate-ignore-db is somewhat safer yet, but the trap is still there.

My advice is that replication should replicate everything. Make sure you and everyone else you work with understand the implications of binlog-do-db and replicate-do-db before implementing it.

## Static charts vs. interactive charts

MySQL Analysis Graphs Monitoring mycheckpoint Web March 2, 2010

I’m having my usual fun with charts. Working on mycheckpoint, I’ve generated monitoring charts using the Google Chars API. But I’ve also had chance to experiment and deploy interactive charts, JavaScript based. In particular, I used and tweaked dygraphs.

I’d like to note some differences in using charts of both kinds. And I think it makes a very big difference.

#### Static charts

I’ll call any image-based chart by “static chart”. It’s just a static image. Example of such charts are those generated by Google Image Charts (they now also have new, interactive charts), or RRDtool. Show below is an example of a static chart; in this example, generated by Google: Continue reading » “Static charts vs. interactive charts”

## What I look forward to hear on “State of the Dolphin”, 2010

MySQL mysqlconf Opinions March 1, 2010

Though most probably I won’t be there in person, here’s what I expect to hear from Edward Screven, Oracle, on the State of the Dolphin keynote, coming MySQL Conference & Expo.

I’m under the assumption that no shocking news are delivered. That is, that for the near future, it’s business as usual for MySQL.

Last year’s great message, delivered by Karen Padir, was “more community”. More community participation, more community patches. Looking back, I’m not sure I saw that coming true. The 5.4 version was announced at that same conference, and was criticized for being community-oriented yet community-hidden. The latest 5.5 milestones announcement took everyone by surprise again. Ideas from Google patches were incorporated into 5.5M2. but, to the best of my understanding, no community patch was delievered.

I have both congratulated and expressed my desire that community took greater part in this.

#### So what am I looking forward to hear?

1. Like everyone else, the general plans Oracle holds for MySQL. Again, I’m not expecting shocking news here.
2. The expected roadmap for MySQL, technically speaking. I don’t actually know if there is a roadmap right now.
3. The intended role for the MySQL community. Frankly, it would be just fine with me if Oracle were to say: “we will not accept community patches”, and that would be the end of it. That’s fine, because it’s their right, and it would be an honest announcement. Naturally, I’ll be much happier to hear “we will incorporate the best 20 community patches withing the next three days”. Somewhere in between, I’ll be really satisfied with a clear explanation of how Oracle sees the community, and how it would like to cooperate with it. Will it share the development plan with the community? Will it allow the community to have a say about what goes in or not?

Continue reading » “What I look forward to hear on “State of the Dolphin”, 2010″

## Defined your MySQL backup & recovery plan recently?

MySQL Backup Replication February 17, 2010February 17, 2010

Following up on Ronald Bradford‘s Checked your MySQL recovery process recently? post, I wish to add a prequel.

To see whether you have a clear definition of your backup requirements, ask yourself these questions:

- Is there a backup/restore plan?
- Is there a written backup/restore plan?
- How fast do you need to recover a backup? What’s the longest downtime you will allow from the point of failure to the point of restored, functional database?
- How much data are you willing to lose in case of crash? A second’s worth of data? An hour’s worth? A day’s worth? None?
- Are you willing to allow that the database becomes read-only when taking the backup? Or completely down?
- Are you willing to take the risk that the backup will not be 100% compatible with the data? (Backing up your slave holds this risk)
- Is your manager willing to all that you are willing?
- Is the backup plan known to the management team, or do they just know that “the database has backups“?

The above checklist is by no means complete.

I have a vivid memory of a very good sys admin who failed on the last two points. He had some very sour days when recovering a lost file from tape took much longer than was affordable to some contract.

I found that technical people rarely share the same views as marketing/management. Make sure to consult with the management team; they will have a clearer view on what the company can afford and what it cannot afford.