A friend of mine asked me:I'm hoping you can help me out with something -- I'm trying to optimize a search feature. Since it uses a MySQL database, the search already uses the "LIKE" statement to get matches for a search query, we might be needing something more flexible. I found mention on MySQL's website about something called the "SOUNDS LIKE" expression that can be more flexible than "LIKE". Do you know anything about this? If you do, can you point me a direction where I might be able to learn more about it? Thanks in advance for your help!My response:

The listing of Dynamic System Variables on the MySQL Reference Manual's page is supposed to show those variables you can change on-the-fly."innodb_data_home_dir" is listed there as a dynamic variable, but it is not one, and trying to change it on-the-fly doesn't work: mysql> set global innodb_data_home_dir="/data/mysql/data";ERROR 1193 (HY000): Unknown system variable 'innodb_data_home_

It felt like the right time for us to look back at some useful commands for table maintenance that some of us may not have mastered as much as we might like to think.In my post about gathering index statistics, I referred to "OPTIMIZE TABLE", "ANALYZE TABLE", and "REPAIR TABLE" -- but I never explained in depth what the different commands do, and what the differences between them are. That is what I thought I would do with this post, focusing on InnoDB and MyISAM, and the differences in how they treat those commands. I will also look at different cases and see which one is right for in each case.

I recently needed to set up multiple MySQL servers on a test computer to simulate a master-slave setup. I had never done this before, so I think it might be useful for others if I documented what occurred. This setup was done on a Linux server, however other platforms should operate similarly. I choose to use the "mysqld_multi" script to manage these instances. There is a way that you can compile the MySQL server with different network interfaces, but in my opinion, using "mysqld_multi" to manage activities is much easier.So shall we begin?

The Maatkit toolkit is a real blessing for the MySQL DBA. And while its documentation is pretty good, in some cases it's necessary to read carefully a second and third time to make sure you are not missing an important piece of information. In this article I will comment on mk-table-chksum and mk-table-sync. My comments are mostly aimed at those DBAs who are considering using these utilities with medium or larger-sized databases.--replicateThis option allows you to store the checksum results on the master, in a table that will get replicated to the slaves. Although it might seem like overhead for a simple check, it really simplifies your life, especially when used in combination with "mk-table-sync". I always use it, for the following reasons:\tYou only need to run "mk-table-chksum" on the master.\tA simple query will tell you the slave status.\tWhen used with "--chunksize", it divides the checking  []

After I moved back to Europe and Malta in order to set up our operations here, I was approached by a old friend of mine who wanted to know how to add a "UNIQUE" constraint and remove duplicates on a table, while keeping the newest records. He had been trying with "ALTER TABLE" but ran into problems as the older values were taken. So, to help him out, I first solved it based on his original idea, and then figured I would post a small note about the solution here.So, let's say we have the following structure . . .sql01 blogs> SHOW CREATE TABLE post1164\\G*************************** 1. row *************************** Table: post1164Create Table: CREATE TABLE `post1164` ( `a` int(10) unsigned NOT NULL AUTO_INCREMENT, `b` varchar(5) DEFAULT NULL, `c` varchar(5) DEFAULT NULL, PRIMARY KEY (`a`)) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin11 row in set (0.00  []

By "Good SQL Querying", I am not referring to "how to make your queries more perfomant." I am about to go on a mini-rant about how to make readable and self-documenting SQL queries.One practice that will get me instantly going on a rant is using a comma join. There is NO reason to do the following:-- uses the sakila sample databaseSELECT first_name, last_name, addressFROM customer,address;What kind of join did the original author intend? A CROSS JOIN? Or did they really want an INNER JOIN and forget the WHERE clause?The answer: you do not know for sure; you can only guess. Had the query beenSELECT first_name,last_name,addressFROM customer INNER JOIN address;you would know that the author intended an INNER JOIN; had the query beenSELECT first_name,last_name,addressFROM customer CROSS JOIN address;you would know that the author intended a CROSS JOIN. I advocate using INNER JOIN instead of JOIN because like  []

Let me first say that the PBXT storage engine has some great people behind it. At the users conference last April, I had a chance to meet Paul McCullagh, who created PBXT, and some of the people who work on it. They are dedicated individuals who are creating something unique.Like the InnoDB storage engine, which is backed by the Innobase company, PBXT has a company that backs it, Primebase Technologies. This means that if needed, support can be got from the company that created the product. For enterprise companies this might be important.The basics characteristics of PBXT:\tMVCC: Multi-version concurrency control, enables reading without locking.\tTransactional: Support for "BEGIN", "COMMIT" and "ROLLBACK" and recovery on startup.\tACID compliant: Atomic, Consistent, Isolated, Durable (once committed, changes cannot be lost).\tRow-level locking: updates use row-level locking, allowing for maximum  []

Over the last few weeks I've been looking at several customers' slow query logs, and I found in many of them an odd type of query. These are "SELECT" statements that contain an "IN" clause that includes dozens, sometimes hundreds of values. These statements often end in the slow query log. I'm not sure if these queries are this way by design or if they are generated by a specific database development tool. I did some tests in one of my own databases, one with only around 10K rows in its largest table. The database corresponds to the Amarok media player. For example, I queried for songs by B. B. King (spelled "BB King", "B.B. King", etc. or with other artists: "B. B. King & Eric Clapton"). The first query used a "JOIN" and an "IN" clause with all the spellings in my db; the second used the same "JOIN" and "WHERE ... name LIKE  []

Scenario: Someone wants to know which of the over 50 MySQL users have certain privileges.There are many ways to solve this problem. Some of these scenarios are tedious and repetitious, others take no time at all.The issue, of course, lies in what the "certain" privileges are. If it is "who has the SUPER privilege?" then a simpleSELECT user,host FROM mysql.user WHERE Super_priv='Y';is sufficient. If it is "who has write access to the foo database", you might write:SELECT user,host FROM db WHERE Db='foo' AND Select_priv='Y';but that only shows who explicitly has read permissions on that database; it does not include those who have global read permissions. The full query would be:

So, you have a binlog. You want to find out something specific that happened inside of it. What to do? "mysqlbinlog" has some neat features, which I thought we would look at here. I should first explain what "mysqlbinlog" really is. It is a tool that lets you analyze and view the binlogs/relaylogs from mysql, which are stored in binary format. This tool converts them to plaintext, so that they're human-readable.For the first tip, let's start with the "--read-from-remote-server" option, which allows you to examine a binlog on a master server in order, perhaps, to dump it onto your slave and compare master/slave logs for potential problems*.$ mysqlbinlog --read-from-remote-server -uwesterlund -p mysql-bin.000001 -h 127.0.0.1 -P 3306 | head -5Enter password: /*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;#  []

Or, "Missing information in the MySQL Manual". Just earlier today, I was using "POW()", which I've grown quite fond of, simply because it makes life easier. I prefer using it like "SELECT 512*POW(1024,2)" to find out the number of bytes to put in a variable, for example.First, let's take a look at the "POW" function: Name: 'POW'Description:Syntax:POW(X,Y)Returns the value of X raised to the power of Y.Okay, so it gives us a value; but what about the data type? Let's take "512*POW(1024,2)" as an example.5067 (blogs) > SELECT 512*POW(1024,2) AS example;+-----------+| example |+-----------+| 536870912 | +-----------+1 row in set (0.00 sec)What is that? Well, it sure does look like an "INT" at this point, doesn't it?

Note: This article is about the "WHERE" extension to "SHOW". I specifically use "SHOW STATUS" as an example, but "WHERE" is an extension to many "SHOW" statements.Often DBAs will assess the health of a system by looking at some of the status variables returned by "SHOW GLOBAL STATUS" (specifying "GLOBAL" is important; remember that "SHOW STATUS" is the same as "SHOW SESSION STATUS"). There are many status variables that "SHOW GLOBAL STATUS" returns. ("SHOW GLOBAL STATUS" on a Windows machine, MySQL version 5.0.67 returned 249, 5.1.22 returned 256 and 6.0.6-alpha returned 295 status variables!). I have used the "SHOW STATUS LIKE" syntax to help give me the output I really want, particularly when I forget the exact names of the status variables I am looking for.But I did not know of a way to perform "SHOW  []

Every so often we have a client worrying about unauthenticated users. For example, as part of the output of "SHOW PROCESSLIST" they will see:+-----+----------------------+--------------------+------+---------+------+-------+------------------+| Id | User | Host | db | Command | Time | State | Info |+-----+----------------------+--------------------+------+---------+------+-------+------------------+| 235 | unauthenticated user | 10.10.2.74:53216 | NULL | Connect | NULL | login | NULL || 236 | unauthenticated user | 10.120.61.10:51721 | NULL | Connect | NULL | login | NULL || 237 | user | localhost | NULL | Query | 0 | NULL | show processlist |+-----+----------------------+--------------------+------+---------+------+-------+------------------+Who are these unauthenticated users, how do they get there, and why aren't  []

In two words: online operations. In a paragraph: Forget partitioning, row-based replication and events. The big reasons most people are going to salivate over 5.1, and probably start plans to upgrade now, are the online operations: \tonline ALTER TABLE for column rename, column default value change, and adding values to the end of an ENUM/SET\tOnline, table-based logging. No more need to restart your server to enable or change the general or slow query logs. You can have the standard file-based output or choose a table format...which you can query.

Do you "have_community_features"? I do!SHOW GLOBAL VARIABLES LIKE 'have_community_features';+-------------------------+-------+| Variable_name | Value |+-------------------------+-------+| have_community_features | YES | +-------------------------+-------+1 row in set (0.00 sec)I am pretty sure this is one of those variables that MySQL has put in as an unused placeholder, but for now, it is not even documented as unused (as are table_lock_wait_timeout,

So there's a whole bunch of MySQL scripts included with any release, who can say they know them all? In this blog post, we will take a look at some of these and show usage examples that may help you in your environment.mysql_accessLet's say you want to manage your users' rights, and you have this user 'john.doe' that can connect from 'host1', but you aren't sure what he can do to your db: company-staging as well as company-dev.What do you do? Well, for example, you could use "mysql_access" and take a look:

Wheel of protocol, turn turn turn.Tell us the lesson that we should learn. (with apologies to the original source)Writing a book comes with many challenges. For me, writing a MySQL book for MySQL beginners, who may or may not be database beginners, has fed my compulsion to research and test bizarre interactions.Today's lesson is on what protocol is used when connecting to a local "mysqld" instance on a non-Windows machine. The TCP/IP protocol is used by default when connecting on a Windows machine, and connecting from any operating system to a non-local "mysqld" instance. I am assuming the connections are being made by a command line client such as "mysql", "mysqladmin" or "mysqldump". Connections made via connectors such as Connector/J, an ODBC connector, DBD::mysql, etc are not covered in this post.If you seem to be having trouble connecting, check your options against your  []

Had an interesting situation come up today with a client. We had a situation where a server crashed because it ran out of memory. The calculation we used to monitor memory usage did not take into account all factors. When looking at this, I noticed a couple of things:\tThere are numerous calculations available online.\tIt seems that none of them take everything in account.Wouldn't it be nice if there was a total possible memory consumption status value? It can be a valuable piece of information. (And while I am requesting things, what about peak memory usage by the mysqld server since it has been online?) Realistically, there is almost no case where you would actually use the maximum amount of memory. But knowing how much memory that MySQL server could possibly use would be a very useful bit of information to know.Second, why isn't there an "official" formula that actually shows everything? It's easy to forget things.  []

A while ago me and Singer Wang (another DBA here at pythian) was working on a problem where the slave went from 800M to 1200 to 2700 to 3200 to ~800M again. I have to admit I haven't seen this before.The specific setting was 5.0.45 slave that also acted as a master, and the instance the experienced the problems was running 5.0.50.After upgrading to 5.0.74, it worked again. But here's a few of the show slave status while experiencing these problems: