The other day, a client mentioned they were getting strange results when running "ALTER TABLE". The episode involved modifying an existing primary key to add an "auto_increment" "primary key": it was "shifting" values. Say what?! As it turns out, it was a very special value getting changed: "zero". Some fiddling revealed the underlying reason. Care to join me?To understand what's going on, follow the example below as we start with an empty database, create a table and insert a few rows:mysql> use test;Database changedmysql> show tables;Empty set (0.00 sec)mysql> create table test_table (id int not null primary key) engine=innodb;Query OK, 0 rows affected (0.01 sec)mysql> desc test_table;+-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id | int(11) |  []

Recently, I had an opportunity to participate in a stress-testing exercise. By stress-testing, I mean simulating an expected peak load on the database and observing how the database performs. The objective is to make sure the particular hardware can handle the expected load and also to test where the physical limit of the machine lies if we keep increasing the number of concurrent users. I searched for tools available out there. On forums, most people were talking about Hammerora, and although some people recommended high end tools for big bucks, I decided to try this free tool that promised to do exactly what I needed it to do. The open source Hammerora turned out to be excellent tool for the purpose of simulating a typical transactions load in any number of threads. It's built with the Tcl scripting language and it can simulate a real workload with as many client user sessions as your system can handle.So if you're looking for  []

First off, let me wish you all a Happy 2009, and my apologies for being a bit slack with blogging in the last two months of 2008. It's been a bunch of busy weeks, but I'm fully expecting to remedy that in this year. Second, let me welcome you to this series on refactoring MySQL applications. I will cover best practices and useful tips, as well as show working examples of potential loopholes and solutions.So, you are a DBA or a developer, and you're faced with a problem. Your years-old application (perhaps you inherited it from a former co-worker) is now starting to keel over, and your choice of responses is limited.Let's start by saying that there are other ways to reach performance, but in some cases refactoring may be the only way that is possible to pursue, for one reason or another. Let's take the refactoring way, focusing on SQL rather than applications, as there's a lot that can be done on this side.But first, why  []

Last week, I was at the NetApp office in North Sydney for the presentation on

Welcome back to my series of blogposts, "Back to Basics". Today I wanted to share a few commands that could be useful in various situations, such as delaying replication, stopping the slave (or parts of it).I'm sorry, but I will not be going through how to set up replication, so my assumption is that you already have replication working and that you want to know some of the details surrounding it.Let's begin by taking a look at "STOP SLAVE"STOP SLAVE stops the slave threads

If you are using InnoDB Hot Backup and a recent version of mysqld (at least 5.0.67 or higher, including 5.1.30, though it may be later versions), your backup will run fine and output OK! at the end, as it should.Except for one thing.The binary log file and position do not appear in their rightful place. Here's a snippet of the output from the backup:innobackup: MySQL binlog position: filename 'Warning', position (Code 1287):'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead090210 03:55:04 innobackup: innobackup completed OK!That's pretty misleading -- looks like the backup completed OK, but it did not show us the binary log position. What about the "ibbackup_binlog_info" file?[mysql@db3:~] more ibbackup_binlog_info Warning (Code 1287): 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead[mysql@db3:~]Yikes! What happened to the binary log position and file information?Have no fear, it's actually in another file.

I had the chance to review the new Query Analyzer program from Sun over the last few days. I am very interested in how it performs as I have previously not had a chance to see the program in action (or the Enterprise Monitor program either for that matter). So, before getting into what the Query Analyzer can (and cannot) do let's look at what Sun says it does.From the November 19th, 2008 press release:The MySQL Query Analyzer saves time and effort in finding and fixing problem queries by providing:\tAggregated view into query execution counts, run time, result sets across all MySQL servers with no dependence on MySQL logs or "SHOW PROCESSLIST"\tSortable views by all monitored statistics\tSearchable and sortable queries by query type, content, server, database, date/time, interval range, and "when first seen"\tHistorical and real-time analysis of all queries across all servers\tDrill downs into sampled query  []

You probably know that "mysql -h host_or_ip" can connect you to a remote host.But did you know that you can change the host you are connected to from within "mysql"?The undocumented (as far as I can tell, in the MySQL manual and in the "help" on the mysql command line) "CONNECT" statement can help.

I realized tonight exactly why MySQL's default behavior of silent truncation bothers me.It reminds me of people who use a ticketing system and close every ticket as soon as they are done working on the issue instead of actually asking the other party if they are satisfied, because closing more tickets make it look like they're doing more work.It reminds me of workers at fast food restaurants who hit the button to make the order disappear as if they have already served me my food, because

One very helpful use of the technique Sheeri described in Remote connections without leaving the mysql shell is making sure that replication is working properly.According to the MySQL Reference Manual's section on SHOW SLAVE STATUS Syntax, it shows information corresponding to the slave thread in the slave server. When replication is broken, however, or not working properly due to network issues between master and slave, this information may not be accurate. This has improved over recent releases, but it's still not perfect.The question, then, is: how to be 100% sure (or as close as you can get to 100%) that replication is running fine? The answer, as offered by Sheeri: use "CONNECT".Example\tLog into the slave using the mysql client and issue "SHOW SLAVE STATUS:

We've been running into a problem with one client:SELECT COUNT(*) FROM tbl;takes 0.25 seconds on one db, and 0.06 seconds on another. Consistently. That's a fourfold difference. There aren't any significant configuration differences (like query cache, etc.), the software versions are the same, and the table fits into memory. This has been looked at by at least 3 in-house MySQL experts, and the only thing we can determine is that it's a hardware difference.Th

Or, When MySQL Lies!When I do a "show slave status\\G", sometimes mysqld will lie to me and give me a wrong "Exec_Master_Log_Pos". Let me explain with a situation from last night.This is the output of "show slave status\\G" from mysql version 5.0.41-community-log:mysql> show slave status \\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: XXX.XXX.XXX.XXX Master_User: replic_username Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000480 Read_Master_Log_Pos: 690470773 Relay_Log_File: db2-relay-bin.000028 Relay_Log_Pos: 683977007 Relay_Master_Log_File: mysql-bin.000480 Slave_IO_Running: Yes Slave_SQL_Running: No  []

"mysql-proxy" defaults to round-robin load balancing. There are fancy tricks around to get "mysql-proxy" to balance connections based on how many idle connections there are in a proxy-based connection pool. But there is no code that I found that would simply load balance based on "always go to one server, go to another server only when the first server is down."Well, I spent way t

Someone asked me what applications were good/bad for MySQL Cluster. As I've now actually had experience with a Cluster setup and a real-life application of it, and dug through the manual, I present a few characteristics of applications that will work with Cluster, and why they are so (so that if you have an application that meets some of the characteristics but not all of them, you can decide whether it's worth it to use Cluster or not).Firstly, I'll state this -- there's actually a very limited application to MySQL cluster. I haven't assessed the disk-based cluster, only the memory-based one, so I don't know what really changes with disk-based. But after you see this list, you certainly will want to re-think your use of disk-based cluster if a lot of the inner workings don't change.The factors are listed below, but the "ruler" I keep in my mind is the fact that MySQL Cluster was developed for telecom applications.  []

Over-the-Top Tales from the Trenches.Motto: Bringing order to the chaos of every day DBA life.So you have got your nice MySQL Master-Slave replication pair setup. Everything is sweet, then the master dies/restarts or you have a slightly extended network outage.Your monitoring software (in our case Avail) fires off a page and you are rudely interrupted from reading the Pythian blog.These real world interruptions, what can I say... it pays the bills.Anyway being the rounded DBA or enlightened DBA as Babette would say, you are capable of handling any type of database. You log into the machine and check out why the slave threw an error or if your monitoring is slow, why the slave is lagging by 2 hours.You run SHOW SLAVE STATUS\\Gmysql> show slave status \\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: x.x.x.x  []

Open Source means that the source code is open. There are many inferences that can be made from this, and many stereotypes that can be applied, but in the end, all it means is that you can read the source code as well as use the binaries.I am not a developer. I fully admit this. What I mean by this is that my strengths do not lie in engineering new code to do things. Give me some Lego™s or some Wedgits™ and I look at the instructions to see what to build.However, I am an engineer.One of my team's current tasks is to restore a backup (using InnoDB Hot Backup, and compressed) from a client's production machine to a development instance.....Weekly -- thus we want to automate it......And did I mention the development machine has 5 different MySQL instances on it, running all on 127.0.0.1 on different ports?The steps are:0) stop the desired mysql instance 1) move the development datadir and logs to a backup  []

This is the first place I am announcing this: The Pythian Group has made me a Team Lead. I am extremely honored and somewhat humbled by this, and I am determined to do a good job. I started officially on Monday, March 3rd, and my first week went pretty well. On Saturday, I spent a short bit of time automating one process. And while I was waiting for a 300G backup to copy from one machine to another, I worked on automatin

One of my colleagues made a typo in a query today that led to me discovering this issue -- I think it's a bug, though it may just be how the language is defined. But certainly a subquery that cannot run should not act as if it returns TRUE.mysql> use test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> create table foo (fid tinyint unsigned not null primary key); create tabl

Pythian DBA's have daily reports for each monitored database and some of the components are using charts to visualize the data. I'm a big fan of charts myself (when applied appropriately) and want to show how you can generate simple charts directly from the database. You'd be very surprised how easy it can be done from *any* database without installing any additional software or configuring something special.This method is not limited to Oracle by any means -- use it with MySQL, SQL Server or any other database as well as without a database -- yes, visualize your sar data now!In this example, we will plot a pie diagram with Oracle tablespaces. This would be very handy when you are starting to analyze the space allocation for a database. Here is the end result of the report for my Grid Control repository test database:

I don't want to post the link to this (perhaps, it was left public unintentional?) but here is what I stumbled upon recently. This is a log of production maintenance of IT systems in Perth, Western Australia (as far as I could say):Good idea but