As I putter around the MySQL "INFORMATION_SCHEMA", I am finding lots of undocumented behavior for fields that should be straightforward. For example, the "VIEWS" table holds information about views, and the "VIEW_DEFINITION" field contains the view definition, right?Well, when I was looking at the "VIEW_DEFINITION" today, I noticed an odd thing. Even though I had permissions to see the view definition (as proven by the "SHOW CREATE VIEW

It's been a while since the MySQL Management Plug-in 0.42 was released. Since then, I quietly updated it to version 1.0. The changes were very few; the biggest news was that the plug-in was certified by Oracle and added to OTN Oracle 10g Grid Control Extensions Exchange (see at the bottom).I think the ne

One of the most frequently needed functionality in the MySQL Proxy is the need to know which server you are on. This is not given, on purpose, by the proxy, because the proxy is supposed to be transparent. It is not supposed to matter which back-end server you are on.However, for testing purposes we often want to know which back-end server we're on. Thus I developed functionality for "SHOW PROXY BACKEND [INDEX ADDRESS OTHER]"."SHOW PROXY BACKEND INDEX" -- gives t

Almost 2 years ago, in How Open Do You Have To Be To Be Open Source? I wrote:Google and Yahoo! are not rich because they have secrets. They are rich because they started with secrets, but I believe they could safely let their secrets out with very little loss of revenue.Matt Asay’s recent post Google’s slow transformation into an open, transparent company made me dig up that post, which by many standards is old in terms of time, but it’s only now that some of this change is actually happening.Matt ponders,It remains to be seen what, if anything, Google will actually open, but I trust its track record on living up to its word more than Microsoft’s, which also went through a flurry of “We’re now really open!” announcements lately that actually netted the industry…not much.In interesting news, at last night’s Boston Sun/MySQL event (more on that  []

According to the manual, FLUSH LOGS is supposed to:Closes and reopens all log files. If binary logging is enabled, the sequence number of the binary log file is incremented by one relative to the previous file. On Unix, this is the same thing as sending a SIGHUP signal to the mysqld server (except on some Mac OS X 10.3 versions where mysqld ignores SIGHUP and SIGQUIT).If the server is writing error output to a

I was asked today about the ANSI_QUOTES SQL mode.According to http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html, ANSI_QUOTES mode changes the functionality of double quotes (") to be like the backtick (`). Normally the functionality of double quotes is more like that of single quotes (').You might use this when you have a table with spaces or other special characters you would like to escape, without hav

So, how does one gather statistics on indexes? With InnoDB it's one thing, with MyISAM it's another thing. This post however, will focus on MyISAM. There are various ways, each having their own drawbacks and positive sides.What about "ANALYZE TABLE"? It's great -- very fast in InnoDB. But with MyISAM a lock occurs while scanning the table. "OPTIMIZE TABLE"? Still relatively quick with MyISAM, unless the table in question is huge. InnoDB requires an "ALTER TABLE" -- it might take forever, depending on the server configuration, as "OPTIMIZE TABLE" for InnoDB maps to a "ALTER TABLE tableName ENGINE=InnoDB"We all know how "ANALYZE TABLE", "REPAIR TABLE", and "OPTIMIZE TABLE" work with MyISAM. However, there's a less explored way, for MyISAM -- the "myisam_stats_method".

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

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: