It’s been a hectic week, and it’s only Tuesday.

My wife and I recently made a decision to move on from MySQL. My passion is software development, and my experience doesn’t lend itself well to database development. Therefore, I’m returning to Amazon on Monday, if my intel is correct.

Through a strange series of coincidences, my resume was submitted to the same group which I left 14 short months ago. I will be returning to HTML, JavaScript, Perl, shell script and a tad bit of Java development.

It has been a pleasure working for and with MySQL AB. They make a wonderful database, and their support folks are top notch. I’ll miss them all. But heck, I can stay in touch just as easily as ever. That’s one nice thing about working in a virtual company: you learn to keep in touch by any means necessary.

The roses are blooming. The lawn is mowed. Summer vacation starts Real Soon Now™, and I’ll be taking …

just a few days before reports surfaced that hp would be cutting back on telecommuting, mysql was cited in an article about great teams because we operate without a headquarters. (not quite true, except relative to most companies.)

working from home is great for me, but i can understand how a boss with control issues couldn?t stomach it.

Please share good ideas implemented poorly you have seen.

For example, in the past 24 hours I have come across 2 such beasts:

1) A company doing vertical scaling based on username. Unsurprisingly, they found that 70% of their users had usernames beginning with 1 of 2 letters. A better idea? Do it by last 2 digits of a user id. That way, even though users are created and perhaps deleted all the time, the distribution is effectively random. Language is not.

With 2 significant figures, you can divide vertically many different ways — 1 large table of all the uids, 2 tables of uids ending in 00-49 and 50-99, 4 tables, 10 tables, 25 tables, 50 tables, 100 tables. This eliminates the need for a separate table for each user.

For example, the online dating site I work for uses this method for messaging. So a user’s inbox is just one part of a table. If the user has uid 12345, their “Inbox” is the result of …

I’m sure most would agree when one does a programming mistake they are a lot of times trivial mistakes. However when one stares at the code for long periods of time we seem to go blind and then in the process of frustration loose all logically thinking as to what could be causing the problem. Here is one such problem with arrays.

ShareThis

While I was at the FOSDEM 2006 in Brussels, I got approached by the folks from Source21.nl about performing an interview about MySQL with them. But as David Axmark (who is one of the co-founders of MySQL) was around as well, I quickly directed them to talk with him instead. The vidcast (length is ~10 minutes, the WMV is ~30MB) is now available online in various formats (OGG, MP4 and WMV) from their web site. Have fun.

ApacheCon Europe is the official European conference of the Apache Software Foundation (ASF). This outstanding event creates a unique platform for the Open Source community in Europe to come together to gain deep insight into techniques and methodologies critical to the advancement of Open Source technologies, and gain skills to optimize the power and versatility of Apache software. The event also provides a forum to discuss key issues facing the community and to hear about developments to come in 2006.

At ApacheCon Europe, you can experience first-hand what the ASF technologies and development communities do for you and your enterprise. You will meet the big players, project leaders, inside experts and independent innovators at this annual gathering of all things Apache. Come share your knowledge, hit upon new ideas, find solutions and connect with your peers at this educational, fun-filled gathering of users, developers, and vendors …

Continued work on the backup API. I'm pretty much done with the logic; now I need to look at interfacing, which could take longer. Set up a test server with debug tracing enabled to investigate what happens when the client issues specific commands:

• Build a tree (in this case, the most recent 5.1).

• === grog@eucla (/dev/pts/1) /home/MySQL/5.1-Online-Backup 7 -> cd mysql-test/
  === grog@eucla (/dev/pts/1) /home/MySQL/5.1-Online-Backup/mysql-test 8 -> ./mysql-test-run \
  --skip-ndb --start-and-exit --debug
  Logging: ./mysql-test-run --skip-ndb --start-and-exit --debug
  Installing Test Databases
  Removing Stale Files
  Installing Master Databases
  running  ../sql/mysqld --no-defaults --bootstrap --skip-grant-tables     --basedir=.
  --datadir=./var/master-data --skip-innodb --skip-ndbcluster --skip-bdb
  --language=../sql/share/english/ --character-sets-dir=../sql/share/charsets/
  Installing Slave Databases
  running  ../sql/mysqld …

The German translation of the MySQL 5.1 Reference Manual is complete.

Today I faced with a problem that could arise when dealing with replication.
I had on the master host a table with 534,587 rows and the replicated one on one of the slave hosts with 534,603 rows (16 rows more).
This happened because of some old script that made INSERTs connecting directly to that slave. Unfortunately the replication didn't stop because new INSERTs made by the master host never duplicated entries. I discovered this situation just few days ago.

I wanted to find out how this happened.
I was faced to the problem to determine the IDs of the sixteen phantom-rows on the slave table.

Instead of writing a Perl script (as I would have done a year ago) to read all IDs from master and then find mismatching IDs reading the same values from the slave, I created a Federated table on a non-replicating database (binlog-ignore-db=test in the my.cnf file):

CREATE …

Using stored procedures is a good thing to reduce complexity of PERL scripts.
Since my company switched to MySQL 5 (last month) I started to write some SPs.
But a problem arised.

DBD::MySQL for Perl has a problem dealing with multiple rows statements like simple SELECTs in SP.
So, I was not able to grab results from routines.

As you can see here the problem is well known and a patch was proposed , but at the moment there's no solution. My DBD::MySQL version is 3.0003

So, the only way you can grab the SP's results in a perl script is to store them into OUT parameters and read them through user variables.

Let's see a simple example.

Suppose you have the SP below to count the number of published news articles …