Previously I wrote about the manual search and how it had been fixed. Now I think it’s time for polish. Most of the basic keyword searches work but not all of them. The syntax keyword searches should always be spot on.

mysql.com/select syntax

Works great but the less common syntax searches fail

mysql.com/grant syntax

There really is no excuse for the syntax searches failing. These are the sections that people need the most on quick reference. I noticed tha recently the keyword searches that do work put me into the correct page and not just the search results with that page at the top. I love this! I just needs to work for every syntax search.

While I’m on the subject of manual search I would like to request a few features. Migration to newer versions isn’t as simple as just …

One of the things I always like about growing up with MySQL is that I had to learn joins. There was no sub select to fall back on if I couldn’t get a join to work correctly. With MySQL 4.1 the norm and 5.0 quickly on it’s heels I see more and more problems that people are solving with sub selects that could as easily be solved with joins if they knew how to write them. In my experience joins are faster and easier to optimize than sub selects but I seem to be in the minority now with that idea.

Don’t worry join syntax. I won’t forget you!

No, not the kind that wakes you up a 2am. The kind that lets you easily slice and present data from the mysql client in a more managable form. This little known feature of the mysql client is one of my favoriate and has helped me slice through large process lists and page through result sets for years.

Basically the pager command allows you to attach commands to the output of the mysql client to manipulate the output just like pipes in bash. Do you hate wading through all those sleeping connections to find the important ones? Yeah me too:

Normal Processlist:

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 5 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 6 | root | …

The slow query log can be a great tool for diagnosing and fixing slow queries. It can also slow the server to a crawl if you are doing bulk inserting. MySQL isn’t afraid to write any query to the slow query log. Even ones that are hundreds of megs. If you are using the multiple-row insert syntax and a query takes longer than long_query_time when it completes mysql will write the entire query out to the slow query log effectively doubling the ammount of data written to the disk.

I was trying to come up something funny|cute that used new features in 5.0 and settled on a quick little bit with a coffee pot and mug:

create table pot (coffee int unsigned);
create table mug (coffee int unsigned);

delimiter |

create trigger pour_cup after delete on pot
        for each row begin
                insert into mug (coffee) values (1);
        end
|

delimiter ;

Try it out:

mysql> insert into pot values (1), (1), (1), (1);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from pot;
+--------+
| coffee |
+--------+
|      1 |
|      1 |
|      1 |
|      1 |
+--------+
4 rows in set (0.00 sec)

mysql> select * from mug;
Empty set (0.00 sec)

mysql> delete from pot limit 1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from mug;
+--------+
| coffee |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

Whenever someone deletes coffee from the pot it …

There has been a lot of buzz going around lately about Oracle’s recent aquizition of Innobase Oy the company that produces the innodb storage engine for MySQL. Most of this buzz is centered around MySQL being doomed or at least being set back to the years before it was considered enterprise quality. None of this is true.

First a bit of background. MySQL is unique (as far as I know) in the relational database industry with the concept of storage engines. This gives users the choice of many different features for storage making MySQL the most flexible database in the world (opinion of course, rebut if you dare ) innodb is only one of several storage engines available to users. Contrary to popular belief it is not the only storage engine that supports transactions. MySQL also supports the bdb storage engine created by Sleepycat software. Innodb is recognized as the better of the two but the choice is still there.

Now back the …

Most people who have had someone look over their shoulder while typing at a prompt have been hit with the question, “How did you do that?” Followed by a quick example of whatever nifty command they just executed. Of all the little bash tricks I know the one that I have explained to more people than any other is brace expansion. It’s a must have in the trick box of any bash user. Consider the following:
firewire@cartman:~> cp /etc/my.cnf /etc/my.cnf.bak
firewire@cartman:~> vim my.cnf
*edit edit edit*

Sometimes, before I knew about brace expanstion I would type my.cfn.bak then get slowed down by having to look for the right file. Other times when copying the bak file over the original I would mis-type my.cnf.

Introducing brace expansion. Brace expansion allows you to create multiple modified command line arguments out of a single argument.
firewire@cartman:~> echo …

Copy and paste is normally a great idea. It saves time. Who wants to retype things over and over again? Not me. There are times when copy and paste can get you in trouble. This has bit me a few times in the past as well as other people. When this happens it usually causes and “oh shit! CTRL+c” to kill the query. The query gets cancelled on the master but still propagates to the slave. The master will happily continue executing while replication silently stays stopped on the slave.

The solution? Stop writes on the master. It really depends on the situation whether you want reads to continue or not. If not shutdown mysql (this will stop your clients from hanging) If reads can continue a simple flush tables with read lock; will be fine. Once the slave has caught up flush tables on the slave and copy the table to the master. Once the table is in place on the master start mysql or flush tables and unlock tables if you left it running. The …

A few days ago I was contacted by a user that was getting table full errors. I explained that by default MySQL uses 4 byte pointers for row data and if the data file grows larger than 4 bytes MySQL will run out of space for pointers. I also explained about max_rows and avg_row_length and how they can be used with alter or create table to give himself larger row pointers. He took this information and happily altered his table expecting all of his data to be there and MySQL to begin adding new rows. What he got was a new table with only a few hundred rows of data. He pinged me in a panick asking what he did wrong. Having never seen such a thing myself I logged into the box. I checked mysqld.err and found that for the previous 10 days or so mysqld had been writing corrupt index warnings to the log. Fortunately he had a slave that we could recover the data from.

After some research the best explanation I can offer as to what went wrong is that …

MySQL has a little know feature for numerical types known as zerofill. This feature effects the display size of numerical types. Unlike the string types the number inside the parentheses is not the storage size in characters for the type. For numerical types the type name itself determines storage size.