I was contacted by the folks at MONyog and asked if I would review MONyog. Since using MONyog is something I have been wanting to do for a while, I jumped at the chance. Of course, "jumped" is relative; Rohit asked me at the MySQL User Conference back in April, and here it is two months later, in June. My apologies to folks for being slow.This review is an overall review of MONyog as well as specifically reviewing the newest features released in the recent beta (Version 2.5 Beta 2). Feature requests are easily delineated with (feature request). This review is quite long, feel free to bookmark it and read it at your leisure. If you have comments please add them, even if it takes a while for you to read this entire article.While the webyog website gives some information about what MONyog can do, it is a bit vague about what MONyog is, although there is a link to a PDF whitepaper on What is MONyog? which does answer  []

I have *never* had this happen to me.Maybe it's because it's MySQL 6.0.4, maybe it's because it's on Windows, or perhaps I am just up working too late.I have seen mojibake before, but usually it is unintelligible. But this? After I post this I am backing away slowly from my computer.Welcome to the MySQL monitor. Commands end with ; or \\g.Your MySQL connection id is 3Server version: 6.0.4-alpha-community MySQL Community

A few days back I read the Workbench Team's blog and was curious about the printing capabilities of MySQL Workbench Community edition. As we already know by now, it only allows you to print a single page. I needed to review a customer query which had several tables and some complicated relationships, so I decided to take Workbench Community for a spin (I already knew the Standard edition from my previous job) and tested the following steps:imported the database after doing a "mysqldump -d" (Workbench Community can't connect to the database to get the schema directly).had Workbench rearrange the diagram.followed the Workbench Team's blog instructions to spread it among more pages, since it ended really cramped.created a PDF file, which was barely readable on paper.Here's where the heavy testing started. Besides the PDF file I also created an SVG and an EPS. All of these are scalable. My thinking was that if I imported  []

The other day we began to encounter weird and random errors on small and innocent queries that shouldn't give any errors at all. It all lead to one of our most basic health checks failing for no apparent reason. The first clue that popped into our minds was related to case-sensitivity, since the failing check was looking for the column names of the only table in the schema with UPPER CASE name. This symptom was especially weird since all of this was happening on MySQL setups running on Windows, and so we're not sure if this was being caused by some internal code library, MySQL, or Windows itself.Since that was the only clue we then had, it seemed obvious that we should start fiddling with the lower_case_table_names system variable. After a couple of restarts, this approach was leading us nowhere, so we finally gave up on it. Then, we had the brilliant idea of actually executing that statement by hand on the command line to see  []

Recently I had an interesting issue crop up. Due to an unfortunate migration incident in which involved master/master replication and not checking to see if replication was caught up, we ended up with an infinite replication loop of a number of SQL statements. "awk" helped immensely in the aftermath cleanup.The basics of the replication infinite loop were

Or, There is a Use for GUI Tools!Recently I was working on a problem for a customer. They are converting a server with two InnoDB data files to "innodb_file_per_table":$ ls -l ibdata*-rwxr-xr-x   1 mysql    mysql       2.0G Oct 14 13:10 ibdata1-rwxr-xr-x   1 mysql    mysql       350G Oct 14 13:10 ibdata2I honestly don't recall ever seeing (or hearing about) so large a data file.The method chosen for conversion boils down to this:\tstop and start the server to enable "innodb_file_per_table"\talter all tables to myisam\tstop server and delete ibdata file\trestart server\tconvert tables back to InnoDB\tadd foreign keysThis post isn't about how we did it, or about whether or not it was the best way, or anything like that. This post is really about the the last two steps.There were over 500 InnoDB tables and something like 375 foreign keys. So I needed to somehow create the "ALTER TABLE" statements for  []

About six months ago, the question of storing images in a database came up. This is one of my favorite topics, and has many database-agnostic parts.Personally, I think "tell me about storing images in a database" is actually a great interview question, because you will be able to see the difference between someone who has just memorized "what's right" versus someone who is really thinking. It also helps you see how someone will communicate -- if they just say "NEVER do it, it's as bad as crossing the streams!" then they are a type of person that gives you a short answer, without much explanation, and without many nuances. (That may be what you are looking for, but usually you want someone who gives reasons for why they strongly feel one way or another).Consider the following cases:

Martin Brown's blog shows a pretty good way of navigating the MySQL Reference Manual. It's worth noting, however, that finding the different topics has been a lot easier since mysql.com started using a Google appliance for its search.I use the documentation all the time and have been doing so for years (I won't claim that I can remember +2000 pages worth of ever-changing content). A few years back, I stopped using the search box on dev.mysql.com because the result sets were enormous, with lots of unrelated references. My technique was to do a Google site search:For replication use the expression: replication site:http://dev.mysql.com/doc/refman/5.0/en/index.htmlThe result set was smaller and I would find what I was looking for relatively easily, usually within the first page.Since the documentation team implemented the Alphabetical Index, it has succeeded the Google search as my favorite way to get the information I needed.  []

Quite often we need to perform a so-called "MySQL instance audit". This common DBA procedure should give you a general view of the MySQL environment. You may be interested in a basic understanding of what kind of operation MySQL performs, how much memory does it use, or how well does it look from the performance point of view. There is no easy out-of-the-box way to do such an audit on a MySQL server. You can use "SHOW STATUS" and check the list of system variables, but this way can hardly be called DBA-friendly. Fortunately there are several tools to make this process easier. Among most popular are mysqlreport and MySQLTuner. In this post I'd like to give a brief overview of MySQLTuner.So, what can MySQLTuner do? Quoting the documentation: "MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability. Within  []

Do you have a master-slave MySQL set up?  Ever do DDL changes on the master?  You may be hit with a serious data integrity bug.  Read on.One of our clients does a regular "rename tables" on the master to keep the current table small and archive off old data.  We'd occasionally be hit by a 'duplicate key' error on the slave on the current table and have to resolve it manually.  Digging into the issue, I managed to replicate it on demand and filed bug 39675 with MySQL, which subsequently has been verified and slated for fix, though from what it seems only in version 6.0.  The bug affects all versions of MySQL from 4.1 to 6.0.In a nutshell, here is what happens. The "rename tables" command only checks for pending transactions or locks in the current session.  If there is a pending transaction in another session on the table being renamed, the rename will succeed, but the order in which the transaction is written  []

This is a post about "SYSDATE()" and "NOW()" and "CURRENT_TIMESTAMP()" functions in MySQL.Firstly, note is that of these three, only "CURRENT_TIMESTAMP()" is part of the SQL Standard. "NOW()" happens to be an alias for "CURRENT_TIMESTAMP()" in MySQL.Secondly, note that replication does not work well with non-deterministic functions. And "hey, what time is it?" is non-deterministic. Ask it twice, with a second apart between asking, and both times you get different results (with at least second precision).You can start to see the problem here....but there's more....

"There are 10 types of people in the world -- those who understand binary, and those who don't."The term "binary" in MySQL has many different meanings. How many can you come up with? I have 6, but I am willing to believe there are more!0) "Binary distribution" is the name for the package that contains a binary. Another use is "binary installation" but that's pretty much the same usage pattern as "binary distribution", so I won't count "binary installation" as

Last week I had to confront one of those situations where you can't really tell what is going on with a piece of software, and the final conclusion would sound completely crazy if postulated as the initial hypothesis. The regular MySQL commands and utilities fall short in these cases, so I had to resort to the three tools reviewed in this article.The problem we were diagnosing was this: at some point in time, a number of queries that use to take less than one minute to execute, started to take between five to 15 minutes. We needed to get an insight into what was going on inside the MySQL server.MySQL TunerAt some point in a long diagnosis process, MySQL's "SHOW [GLOBAL] VARIABLES" and "SHOW [GLOBAL] STATUS" are nothing more than a long list of numbers. Going through a team mate's notes on another issue, I came across MySQL Tuner. This is an extremely simple tool that takes the information from the  []

A quiz, in 4 parts:Given the following table definition in the sakila database:

Today I've been checking out a new client environment. My mission is to figure out (cold) some of the characteristics of the queries being run, and particularly if they're "good" or "bad". In my arsenal of "tools I really want to check out" has been Ma'atkit's Query Profiler.They're very different tools. Ma'atkit's query profiler profiles a batch of queries, without granularity (at least not the way I ran it) to see what query is doing what. So I ran this against a production machine:(I ran the query profiler for a while and then hit "enter" (apparently after about 17 minutes))> ./mk-query-profiler --external --host localhost --user <user> --password <password> Press <enter> when the external program is finished +----------------------------------------------------------+ | 1 (1028.2091 sec) |  []

I had so much to say in response to a recent post asking about virtualization from Jennifer Glore that I realized it was long enough to be a blog post.It really depends on what you're looking to do. Many companies don't have the money and staff to have an in-house data center with proper power and network redundancy; others don't want the depreciati

I was doing some research over the weekend on how transactions work "under the hood" in MySQL. I thought it might be enlightening if I wrote about what I found.The database system must resolve the situation where, for whatever reason, the database server stops working in the middle of a transaction.  Perhaps the power goes out, the hard drive crashes, or the CPU disappears in a cloud of smoke. Maybe the MySQL Server itself crashes for some reason. What does the MySQL Server do when operation resumes? Potentially, there are transactions in an inconsistent state. How are inconsistent transactions resolved? They are resolved through a recovery process involving log files that are called transactional logs. There are two kinds of transactional logs: redo logs and undo logs.

The maximum length of a "VARCHAR" is only restricted by the maximum row length. In most storage engines, the maximum row length is the maximum allowed by MySQL, which is 65,535 bytes. Only the NDB storage engine has a different maximum value.The "VARCHAR" data type has some overhead. If the length of "VARCHAR" is less than 255, one byte per row is used to store the actual length of the string. If the length of "VARCHAR" is greater than 255, the

Hello everyone,Reading PlanetMySQL today, I discovered that Alex Gorbachev's announcement that he has released the first public beta of his Oracle Grid Control plugin for MySQL was not aggregated! This is probably because Alex is primarily working on our Oracle space and so his feed isn't on planet.This p

At the 2008 MySQL Conference and Expo, The Pythian Group gave away "EXPLAIN" cheatsheets. They were very nice, printed in full color and laminated to ensure you can spill your coffee* on it and it will survive.For those not at the conference, or those that want to make more, the file is downloadable as a 136Kb PDF at explain-diagram.pdf* or tea, for