When using MySQL in batch mode, you may often find it useful to use the UNIX shell commands along with the SQL queries. This is a demo script showing how you can achieve that:

# Mixing shell commands and SQL queries in batch mode- demo script

use test;

#INSTALL PLUGIN example SONAME 'ha_example.so'; #Ignore statement

system cp /tmp/mysqld.trace logs/init.trace # Shell commands prefixed with a 'system'

create table new4(num integer) engine=EXAMPLE;

system cp /tmp/mysqld.trace logs/after_create.trace # Shell commands prefixed with a 'system'

system diff logs/init.trace logs/after_create.trace # Shell commands prefixed with a 'system'
 

This script makes use of the 'system' command:

system  (\!)    Execute a system shell command.

Note that this is available only on UNIX systems.

When using MySQL in batch mode, you may often find it useful to use the UNIX shell commands along with the SQL queries. This is a demo script showing how you can achieve that:

# Mixing shell commands and SQL queries in batch mode- demo script

use test;

#INSTALL PLUGIN example SONAME 'ha_example.so'; #Ignore statement

system cp /tmp/mysqld.trace logs/init.trace # Shell commands prefixed with a 'system'

create table new4(num integer) engine=EXAMPLE;

system cp /tmp/mysqld.trace logs/after_create.trace # Shell commands prefixed with a 'system'

system diff logs/init.trace logs/after_create.trace # Shell commands prefixed with a 'system'
 

This script makes use of the 'system' command:

system  (\\!)    Execute a system shell command.

Note that this is available only on UNIX systems.

Because of some weird bug my RSS feeds were broken ever since I’ve upgraded to WP 2.5. Today they were fixed and I hope they’ll have some new posts there soon Stay tuned.

I was chatting with Brian yesterday about my displeasure with Ubuntu's idea of requiring you have to apt-get every package including the kitchen sink in order to have a development box where things like autoconf Just Work (TM). I mentioned my problems and had a Freudian slip of the tongue and said "Unbuntu". I rather like this, because as nice as Ubuntu is in some ways, it's incredibly frustrating that you can't just specify "I WANT A DEVELOPMENT BOX" when you install the OS. I don't like having to figure out what to install when I'm busying hacking code. I hate having to mess around with OS installs. Back in 1993, I put in my time installing Slackware on 30 something odd floppies as well as trying to get X to run on my Packard Hells video card (headland technologies 5650 - something I'll never forget)-- point being, my days of tinkering with OS-isms are past.

Also, why the hell do you have to have multiple versions of automake and …

Sometimes MySQL Replication may run out of sync - because of its own buts or operational limitations or because of application mistake, such as writing to the slave when you should be only writing to the master. In any case you need slave to be synced with Master.

To discover the difference between Master and Slave you can use excellent Maatkit tool though if you you just need to resync small single table it may be easy to do it other way:

Assuming your table schema does not change you can do something like this on the master:

LOCK TABLE tbl WRITE;
SELECT * FROM table INTO OUTFILE '/tmp/tbl.txt';
DELETE FROM tbl;
LOAD DATA INFILE 'tmp/tbl.txt' INTO TABLE tbl;
UNLOCK TABLES;

This will dump master's table content to the file clean the table on master and slave and refresh it on both of them.

This obviously makes table …

I read Baron's blog about this challenge on how to limit rows accessed, not rows returned with a query. He had another requirement, no subqueries, so it would work on pre MySQL 4.x. At first, I started down the same path he did, looking for a way to limit the number of rows accessed. The only way I could do it was to either create 4 seperte queries directly spelling out which actor_id I was looking for. I could also use an IN clause as well, but that wasn't generic enough for me. There's got to be a way to avoid a full table scan and only access a minimum number of rows to return the result he was looking for.

In researching LIMIT optimization, the MySQL 5.0 reference manual states:

If you use LIMIT row_count with …

I read Baron's blog about this challenge on how to limit rows accessed, not rows returned with a query. He had another requirement, no subqueries, so it would work on pre MySQL 4.x. At first, I started down the same path he did, looking for a way to limit the number of rows accessed. The only way I could do it was to either create 4 seperte queries directly spelling out which actor_id I was looking for. I could also use an IN clause as well, but that wasn't generic enough for me. There's got to be a way to avoid a full table scan and only access a minimum number of rows to return the result he was looking for.

In researching LIMIT optimization, the MySQL 5.0 reference manual states:

If you use LIMIT row_count with …

I'll say beforehand that the following is not very clean - for a number of reasons. I'll discuss some issues after the query. The query returns all indexes in a db where the cardinality is lower than 30% of the rows, thus making it unlikely that the server will ever use that index.

SELECT s.table_name,
       concat(s.index_name,'(',group_concat(s.column_name order by s.seq_in_index),')') as idx,
       GROUP_CONCAT(s.cardinality ORDER BY s.seq_in_index) AS card,
       t.table_rows
  FROM information_schema.tables t
  JOIN information_schema.statistics s USING (table_schema,table_name)
 WHERE t.table_schema='dbname'
   AND t.table_rows > 1000
   AND s.non_unique
 GROUP BY s.table_name,s.index_name
HAVING (card + 0) < (t.table_rows / 3);

Let's discuss...
The number of rows in a table used here will be accurate for MyISAM; for InnoDB it's essentiall a rough guess.
Cardinality in this context is the number of distinct values …

Vervante Books Etc — MySQL 5.1 Cluster DBA Certification Study Guide

Only $40. Written by absolute brilliant people (and me). Needing to learn MySQL Cluster so you can go and use it? test it out? work out if it’s for you? Get this book!

Sun Microsystems Inc. today announced the immediate availability of a new offering to help customers achieve greater return on investment (ROI) and significantly reduce the costs of deploying and managing database and application server software. Sun GlassFish and MySQL Unlimited enables companies of all sizes to deploy the software on unlimited servers across their entire organization for a flat annual subscription. For more details, visit http://www.sun.com/mysql/glassfish.