Blog by Shlomi Noach
Apparently the message from the comic below was not well conceived.
Following the strip I’ve added some spoiler notes.
This is not about bashing NoSQL. Apparently some people feel this way.
This is about:
I would like to get the community’s opinion about supporting older (minor) versions of MySQL in open source projects.
If I were to develop some open source project, and a bug report came which only applied to MySQL 5.0.51 (but more recent versions worked fine), would I need to fix the code so as to support this older version?
How about supporting 5.0.22 (released almost 4 years ago, with almost 70 revisions since)? Would you expect an open source project to support this MySQL version because, say, this is the default version in your yum repository?
I would like to concentrate on the currently stable MySQL versions: 5.0 and 5.1. Versions 4.x are out of the question for me, and 5.5 is not yet GA.
Sure, it would be great to support everything. But also time and effort consuming. So, I would greatly appreciate your feedback!
Continue reading » “Poll: what (minor) versions of MySQL should be supported by an open source MySQL related project?”
Revision 118 of mycheckpoint has been released. New and updated in this revision:
With this new revision mycheckpoint turns into a monitoring solution for MySQL. One can now:
mycheckpoint is SQL oriented. As such, it allows for creation of alert conditions, which are nothing more than SQL conditions.
Continue reading » “mycheckpoint (Rev. 118): alerts, email notifications and more”
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”
MySQL is known for its willingness to accept invalid queries, data values. It can silently commit your transaction, truncate your data.
It would be nice to: Continue reading » “But I DO want MySQL to say “ERROR”!”
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”
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”
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!
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”
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”
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.