Lost and Found ?

Sometimes you just have to laugh at the crazy things that can kill a good evening.

I had this brilliant idea to change our replication setup on one of our Master-Master replication server setups this week. I got sick of having to restart MySQL every time we wanted to add a new database and have it included in the list of replicated databases - we were using replicate-do-db in our configs.

So it seems very straight forward to change to ignore-db or ignore-table (because of cross database updates).

After a few weeks in QA and a few weeks in staging - no problems, no issues, no complaints... let's go for it!


Yea, as soon as we deploy and restart MySQL to pickup the configs, replication fails and stops!

And of course to make it lots of fun, replication on a couple other servers failing at the same time for unrelated reasons, and then a migration of another application that night having issues...)

So look around a while, check the MySQL Monitor ... and, wala - lost+found - table does not exist errors!

Yea, ext3 rears it's ugly head again. I'm sure it's probably my responsibility to make sure that lost+found directories are clean up, etc, etc, but it sure made for a headache this week.

The fix (knock on wood) was straight forward - we just added ignore-table=lost%.%

Seemed to do the trick. Maybe we should check out ReiserFS or xfs or zfs.

Either way, if there's a way to break replication, I'm sure I'll find it... ;)


On that note - if you love the file system (linux based) that you are using for your MySQL servers, I'd love to hear your comments (good, bad, or ugly)

MySQL : the beauty in beta

Beta or not, here we come. Yea, you'd think I'd learn to check out the known bugs list before letting developers/qa have at the new database servers running in the lab with MySQL 5.1.21-b . I mean, how bad could beta really be?

Well, as we should all know, beta is called beta for a reason, but sometimes we open source fanatics tend to jump on board, dealing with issues along the way. This sometimes ends up causing a lot of heartache tracking down issues that turn out to be 'known bugs' in a beta version.

Lesson learned (again) :

Yesterday I received a note from one of our lead developers, that went something like this:

---

It seems that our test mysql instance is very picky about honoring desc versus asc queries (qa confirmed it).

If you run this query it will return in asc regardless of the order by.

select id
from registry_service.ALIAS
where (owner_uri_path_id = 700 and owner_uri_ref='Account/3760')
order by id desc;
----data returned ---
107538
107539
107540
107541
107542

Now if you change the column that you are ordering by, it seems to work…

select name
from registry_service.ALIAS persistent0_
where (owner_uri_path_id = 700 and owner_uri_ref='Account/3760')
order by name desc;
---- data returned----
'7SortAliasTest.20071018.141531980.141'
'7SortAliasTest.20071018.141531980.131'
'3SortAliasTest.20071018.141531980.151'
'0SortAliasTest.20071018.141531980.161'
'0SortAliasTest.20071018.141531980.121'

I’m very confused ; can you help me understand what’s going on?

---

Well it took all of 2 minutes and 1 Google query to get an answer.

Known beta bug ( http://bugs.mysql.com/bug.php?id=31001 ).

Just a little foresight on my part could have saved development and test both a lot of hair pulling and time wasted had I evaluated the known bugs list before letting dev and qa have at the new MySQL instance. They could have know that this issue was there, and it would be fixed in the 5.1.22-rc release, which I already have loaded on our new development servers.

Shoulda, Woulda, Coulda...

MySQL innodb plugin

Recently I've spent hours compiling MySQL 5.1.22 and trying to get my cent0s configured for innodb and partitioning. I tried every imaginable way to run configure, use different plugins, etc.

I messed with this forever, then finally looked at the config options, and sure enough, the ./configure expects innobase, not innodb as the plugin name:

from: http://www.innodb.com/support/tips

On MySQL 5.1, ./configure expects a different parameter:

$ ./configure –with-plugins=innobase

SQLServer: Setting up Access from UNIX

These are the detailed instructions for getting t-sql setup to run on UNIX.

This assumes that you have perl 5x installed, and CPAN as well....

1. Update CPAN to the current available version
1. cpan > install Bundle::CPAN
2. Use CPAN to install the DBI::Bundle
1. cpan > install Bundle::DBI
3. Install Freetds
1. Download files from here
2. Gunzip and untar the freetds tar.gz file
3. Read the README!
4. Run configure tool to customize for your system
1. ./configure
5. Install freetds
1. ./make install
6. Add valid SQLServer info to /usr/local/etc/freetds.conf
   
   [{server_connection_name}]
   
   host = {server dns name or ip}
   port = {1433}
   tds version = 8.0
   
   
7. Use CPAN to install the DBD::Sybase
1. Set SYBASE environment variable to /usr/local
   
2. cpan > force install DBD::Sybase
8. Test the install with a perl connection script

Here's a test script to use (note: pass in in clear text):

---

#!/usr/bin/perl

#
# test the perl freetds dbi driver
#

use DBI ;

$user = '{dbuser}' ;
$passwd = '{dbpass}' ;


$dbh = DBI->connect('DBI:Sybase:server={servername}',
$user, $passwd);
$dbh->do("use {dbname}");

$action = $dbh->prepare("select count(*) from master..sysprocesses with (nolock)") ;
$action->execute ;
$rows = $action->rows ;
print "rows is $rows\n";

while ( @first = $action->fetchrow_array ) {
foreach $field ( @first ) {
print "$field\t";
}
print "\n";
}

exit(0);

VMWare: The greatest thing since sliced bread

So if you like to try out new things, and you're not using VMWare yet, get with the program!

Really, though - it's so easy now (and the VMServer is free) . Just download and install.

VMWare lets you run mini virtualized server environments within your current operating system. Yea, there is a little overhead, but for testing and trying out new databases, configurations, applications, it's really cool.

The best part of it all, is the fact that you can download a host of pre-built applications to run with in the VMServer you just installed. Just startup the server and, wala... a running MYSql database in slackware on your Dell windows laptop. Yea, they have an Oracle 10g RAC appliance, as well as a SQLServer 2005 appliance.

Sweet. If you haven't checked it out, do so now...

ORACLE: Sending Logs/Files via mailx

This may be irrelevant depending on your choice of monitoring tools, but I tend to prefer good old fashioned shell scripts on UNIX. One issue I've always had with mailx is being able to attach error files.

Here's a sample script that seems to work on most UNIX flavors with sh,bash, or ksh:

#!/bin/sh
#
DT=`date +%d%m%y`
2>&1 echo "Hey - ORA- error !" >> ${ERR_DIR}/$0.$DT.err
ORA_ERRS=`grep -i ora- ${ERR_DIR}/$0.$DT.err wc -l sed "s/ //g"`
if [ $ORA_ERRS -gt 0 ]
then
mailx -s "$0 ORA Errors! ($DT) " dba@x.com <<-EOF WARNING! - $0 ($DT) had errors! Error log is attached. ~< ! uuencode ${ERR_DIR}/$0.$DT.err $0.$DT.err ~. EOF fi # #

SQLServer: Access from UNIX ?

Ahh... The age old question. Yup - who really want's to use ODBC anyway?

In an effort to consolidate all perl parsing/transformation code for all database extractions we went with perl DBI on Linux. Initially, we figured, no problem.

Here are some things we found out:

1. Default perl install on Fudora Core distribution of Linux had issues with SybPerl Resolution: Grab latest stable build from perl.org and re-compile without -ldb option
2. Install the following Packages: DBI::Bundle, DBD:Sybase, freetds
3. SYBASE environment variable must be set to install location (in our case /usr/local)
4. Perl Bind variables via ? not supported. Still investigating if there is a good work around for this issue.

All in all, once perl was re-compiled we didn't really have any issues... knock on wood...