A few days ago, Ronald Bradford asked for a mysqldump –ignore-database option.

As a workaround, he proposes:

mysqldump --databases `mysql --skip-column-names \
   -e "SELECT GROUP_CONCAT(schema_name SEPARATOR ' ') \
   FROM information_schema.schemata WHERE schema_name \
   NOT IN ('mysql','performance_schema','information_schema');" \
   >` >/mysql/backup/rds2.sql

It's a clever solution, but unfortunately it only works if you have a handful of schemas. If your databases happens to have several dozens (or hundreds or thousands) of schemas (which is where you need this option more), then the output will be truncated to the length of group_concat_max_len (by default, 1024.)

There are two

  [Read more...]

After 15 years of slide show technology, I thought that we need to change the way we do presentations. And since I am advocating radical changes, I will eat my own dog food and be the first to present a MySQL session using 3D technology.

Since watching Avatar a few years ago, I thought that using this technology would make my presentations truly amazing. However, two years ago a 3d projector was prohibitively expensive. Now, instead, it is affordable, and fits in my briefcase!

What I needed, though, was a compelling reason for using 3d vs. traditional presentations. And I found it. As I have mentioned recently, I am working with the coolest replication technology on earth. Explaining this technology

  [Read more...]

SHOW statements are show stoppers on server side. While clients can get a SHOW statement as a result set just as any normal SELECT, things are not as such on server side.

On server side, that is, from within MySQL itself, one cannot:

SELECT `Database` FROM (SHOW DATABASES);

One cannot:

DECLARE show_cursor CURSOR FOR SHOW TABLES;

One cannot:

SHOW TABLES INTO OUTFILE '/tmp/my_file.txt';

So it is impossible to get the results with a query; impossible to get the results from a stored routine; impossible to get the results by file reading...

Bwahaha! A hack!

For some SHOW

  [Read more...]

It was my pleasure and privilege to attend Codebits in 2009. As Roland Bouman says, its talk choice method is based on public voting, and therefore everyone cha have contribute to the schedule.But that is not the main reason for attending this extraordinary event. It is not just a conference. It's an innovation fest. For 1 and 1/2 days, it's a conference, where the speakers are encouraged to bring to their audience the most innovative and inspiring talks. In the afternoon of the second day, the event becomes a competition, where the teams that have registered will have 24 hours to bring a project to completion, and they have  [Read more...]

If you haven't yet heard about Gearman, it's time to have a look at it. Its distributed client server architecture are a perfect match for today's cloud oriented applications.
When talking about Gearman, much stress is often given on its scalability features, such as map/reduce and distributed loads. But Gearman has also a distinctive advantage in the feature department, because of its design that I like to describe as cooperation of the fittest.
While a traditional application is written entirely in a given language, or it must find ways of integrating multiple languages parts, Gearman encourages easy cooperation between parts written in different



  [Read more...]

In Montreal with Dups and Kaj, we were looking at a number of technical problems, and each one of you got something valuable from the meeting.
One of Kaj's problems was a collection of Wordpress blogs infested by spam. Kaj has done something already but the situation was critical. Before applying Akismet to his comments, he needed to cleanup the majority of the spam in same easy way.

It is not rocket science, really, but it needs some care in the implementation.
The comment






  [Read more...]

Encouraged by Baron Schwartz tip on result set comparison, here are a few more, on the same vein.
First, you can send a result set to a file. Probably you will say "yeah, I know, using SELECT INTO OUTFILE". Correct. Except that you can't rewrite to an existing file, if you want to, and you will get a raw output, not the well formatted one that you usually see on the command line. For example:

mysql > select 1 into outfile '/tmp/f1.txt';
mysql > \! cat /tmp/f1.txt
1

mysql > select 1 into outfile '/tmp/f1.txt';
ERROR 1086 (HY000): File '/tmp/f1.txt' already exists

BTW, \! command is a handy shortcut for executing a shell command.
Let's see what










  [Read more...]

It is the first time I am going to write something serious after registering this domain. Probably I was too lazy to blog or I was working hard that I had no time to write blogs.

Any way I have decided to break the silence

Today I came across a problem where I had to add a new value to an enum column in a table with a few million rows. The traditional method of getting that done will take ages to finish and I could not afford to have a such a long window. Fortunately for me I was dealing with a MyISAM table and I had a dirty trick in mind ]:< .

I will list out a small example showing how I did it:

Suppose my table is

create table a( id int,
choice enum('agree','disagree')
)Engine=MyISAM

and I want add a

  [Read more...]

A few days ago I was describing a common grievance when using partitions.
When you care at a table, like the following

CREATE TABLE t1 ( d DATE ) 
PARTITION by range (to_days(d))
(
  partition p001 VALUES LESS THAN (to_days('2001-01-01')) 
, partition p002 VALUES LESS THAN (to_days('2001-02-01')) 
, partition p003 VALUES LESS THAN (to_days('2001-03-01'))
);

Then you have the problem of finding out the original values. SHOW CREATE TABLE doesn't help.
show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`d` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (to_days(d)) (PARTITION p001 VALUES LESS THAN (730851) ENGINE = MyISAM, PARTITION p002














  [Read more...]

One of the projects I completed last week was the upgrade of the house phone system to the latest version of Trixbox (aka asterisk, aka defunct Asterisk@Home, more to come on that later).

I rarely answer my home phone line though. To even get to my extension you have to know what it is ahead of time, since the message you get when you dial my home phone line is "go away". If you know my extension though you can still get to me, which most likely means you will get my voicemail. All voicemail from the house line is sent to me as email, unlike my cell phone where it sits and rots.

If you can navigate my home phone system, I probably want to hear from you. With the upgrade I wanted my phone system to push data to me. To do this I wanted to integrate it into Laslow (and yes this is the next generation of Wolsal for those who remember it).

Laslow is an AIM bot that I run.





  [Read more...]

While traveling this last week I read up on converting a Linksys WRT54G to better firmwares. I just so happen to have one of these and while it works, it has several limitations that I would like to do away with :)

Getting home on Saturday means that when I get up on Sunday this is one of the first things on the list to do!

Take option one and risk my working router? No, I go to Best Buy to pick up a second WRT54G. While I may already have one, I did not want to chance turning my current one into a brick.

The one I picked up at Best Buy of course turns out to be a version 5, which is a nightmare to upgrade and will soon be returned. Linksys decided to change the hardware they use in their





  [Read more...]

Sudoku solver

There are probably tons of these already available, but here is a quick sudoku solver (in Perl):

#! /usr/bin/perl

# Example:
#
# echo $'      2  \n  1 9  4 \n 2 1 5  9\n3     6  \n  68 41 5\n  427 8  \n   51    \n     7 3 \n79    5  '| perl sudoku-solve.pl
#

use strict;
use warnings;

my $s = read_sudoku();

my $res= solve($s);

if($res) {
  print_sudoku($res);
  print "Got it!\n";
} else {
  print "Failed :-(\n";
}

exit 0;

sub solve {
  my ($s)= @_;

  my $res= try_solve($s);

  return $s if $res eq 'SOLVED';

  return undef if $res eq 'FAIL';

  # Make a guess, backtracking if we were wrong.
  # Try to find some field where there are only two possibilities.
  my ($a, $b);

 OUTER:
  for my $i (0..8) {
  INNER:
    for my $j (0..8) {
      next INNER if keys(%{$s->[$i][$j]}) == 1;
      if(keys(%{$s->[$i][$j]}) == 2) {
        ($a,$b)= ($i,$j);
        last OUTER;

  [Read more...]

My dual opteron which has gobs (more then 2gigs) of memory will not let a single process spawn more then 12,000 threads at any time.

Well, somewhere between 12,000 and 13,000 threads things go haywire. I had a program I put together yesterday break because it was let loose with thread creation. So last night I wrote a little loop around an application just to see where consistent thread creation could occur (each thread did nothing more then maybe sleep for a second (it had a one in seven chance of the sleep event occurring)).

I am sure there is a system limit I am ignoring, but I wanted to see what the practical numbers where in this case.

Completely random and probably worthless information :)

Its crazy to generate this many threads in the first place. I saw Nusphere once do this with Gemni to prove a point about concurrency, but it didn't prove much of a







  [Read more...]

[brian@zim tdb]$ make test
mkdir -p /usr/local/bin
mkdir -p /usr/local/include
mkdir -p /usr/local/lib
mkdir -p /usr/local/lib/pkgconfig
mkdir: cannot create directory `/usr/local/lib/pkgconfig': Permission denied
make: *** [install] Error 1

Now why do I need to install your software just to run the test program?

The answer is that I should not have too.

I think I will go play Settlers now...

And yes, I am working on porting a storage engine this evening that I want to toy with.

Why is this?

Because I have a huge stack of expense reports I need to get done for mysql before I leave on Monday for a trip, and I will do almost anything I can to avoid doing my expense reports.

Just as a reminder, there is a MySQL Seattle Meetup on Monday at the Elysian. People show up at 7:00.

More Information is here:
http://mysql.meetup.com/31/events/4796298/

I believe I should have some O'Reilly books on hand to give out.

Topics have included: MySQL, MySQL, favorite science fiction shows, Asterisk, and the ever popular "how many channels of TV are you recording on your MyTH system"

After a little bit of playing around I got distcc to fully work when compiling MySQL today. None of this really had to do anything with MySQL in particular, and really had more to do with just getting all of the permissions setup correctly for distcc. My account on the build machine now has the following information:

export CCACHE_PREFIX=distcc
export MYSQL_BUILD_PREFIX=/home/brian/mysql-builds/example
export MTR_BUILD_THREAD=42
export DISTCC_HOSTS="localhost hell heaven purgatory"

The first tells ccache to use distcc, the second is to tell the MySQL BUILD/ scripts to always build binaries that install with a particular build directory, the third tells mysql_test to restrict itself to a certain set of ports, and the final variable just tells distcc where to send code to be compiled.

All in all it is pretty slick, though one big annoyance is the








  [Read more...]