Recently, a customer wondered if they should start using the "innodb_file_per_table" option, or if they should continue to use the large InnoDB tablespace files created by the "innodb_data_file_path" option in the "my.cnf" option file.Many people still use the older "innodb_data_file_path" option because it is the default for MySQL server. So, what are the benefits of using "innodb_file_per_table" instead?The "innodb_file_per_table" makes for easier-to-manage files. With this option each InnoDB table has its own data and index file under the database directory. As an example, if you had table "foo" located in database "xyz" the InnoDB data file for table "foo" would be "/var/lib/mysql/data/xyz/foo.idb". Each table would have its own idb table in the appropriate database directory. This is in contrast to using the  []

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".

InnoDB is a storage engine that uses MVCC (described shortly) to provide ACID-compliant transactional data storage using row-level locking.  MVCC stands for Multi-Version Concurrency Control.  It is how InnoDB allows multiple transactions to look at a data set of one or more tables and have a consistent view of the data. MVCC keeps a virtual snapshot of the dataset for each transaction.  An example will make this clear. Let's assume you have two transactions (and only two transactions) running on a system. If transaction A starts at 10:45:56 and ends at 10:45:89, it gets a consistent view of the dataset during the time that the transaction runs.  If transaction B starts at 10:45:65, it would see exactly the same view of the dataset that transaction A saw when it began the transaction.  If transaction B started at 10:45:95, it would see the modified dataset after transaction A made modifications. During the duration of each  []

It's time to continue our series on the transactional storage engines for MySQL. Some might question why I even include Falcon because it is very much beta at this time. MySQL, however, has made quite an investment into Falcon, and while it is currently beta, the code is improving and it looks like that it will be production-worthy when MySQL server 6.0 hits GA.If this is the case, it is important to begin to understand what Falcon was designed for and how it differs from other transactional engines such as InnoDB. I am going to concentrate quite a bit on the Falcon/InnoDB comparison as that is what everyone wants to talk about. This is despite my having heard MySQL employees repeatedly make statements to the effect of, "Falcon is not going to replace InnoDB," or "Falcon is not competing with InnoDB." Well, take that with a grain of salt. It certainly seems to me that they are competing for the same  []

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....