Oracle + Open Source
two worlds inexorably colliding

We blogged about Facebook’s Open API over at #comments as a guest blogger. It seems everyone wants in on the social networking openness. Tomorrow, Google is slated to release it’s OpenSocial.

Oracle and Salesforce.com are also jumping on the bandwagon with Open Social support, along with Linkedin, Plaxo, hi5, and Friendster. Does anyone still use friendster?

The exciting thing for developers, and ultimately the user community who may use such apps is that developing for Google’s new API will easier, and will work across a lot of differen social networks. Granted Facebook has a huge inertia behind it, but still build-for-one and deploy-across-many is a powerful motivator for everyone.

And if my experience with Friendster, then Tribe, then MySpace, and then Facebook is any indication, people will get excited about the next great community, social networking site and forget about Facebook just like they did every single one before.

NOTE: As of Thursday, you’ll be able to go to Google’s homepage for the project: OpenSocial

The fifth and last abstract submitted for the O’Reilly MySQL Conference in April 2008.

As an independent consultant, there are quite a few trouble spots I see repeatedly. I’ll discuss five of them, and how to avoid them in your own infrastructure.

As an independent consultant for twelve years, I’ve encountered a lot of interesting and challenging projects. I’ll discuss five different cases, and what lessons I took away from each.

1. Intro
2. The Right Hardware
3. Importance of Good Testing
4. Patchwork or Good Design
5. Don’t Mix Opposites
6. Use The Technology
7. Conclusion

The fourth in a series of five abstracts for the O’Reilly MySQL Conference in April 2008.

Learn how to audit your systems, and run through the right checklists so you can sleep better at night knowing your systems are more secure.

Security is on everyone’s radar these days. You may be wondering yourself whether your database systems are really as secure as they should be. We’ll discuss some of the latest vulnerabilities, and what you can do to protect your systems.

1. Introduction
2. Authentication
3. SQL Injection
4. OS Security
5. Network Security
6. Conclusions

This is the third in a series of five abstracts submitted to the O’Reilly MySQL Conference in April 2008.

MySQL has a great facility for creating a read-only failover database. We’ll show you how to setup, start, failover, and monitor it.

Setting up MySQL to have a master + slave failover capability might be intimidating, but it needn’t be.

1. Intro
2. Anatomy of MySQL Replication
3. Initial Master copy
4. Setup + starting the slave
5. Failover from Master
6. Adding another slave
7. Monitoring your slave db
8. Conclusions

The second in a series of five abstracts for the O’Reilly MySQL Conference in April 2008.

Inevitably hackers are trying to get at your data, so you mine as well know what they can and can’t do. What better way to discover where you’re vulnerable than hacking your own systems.

Operating Systems have bugs, Database Software has bugs, and so does your application, probably. A better question is how hackable are you? We’ll look at some of the nefarious ways intruders can get in, so you’ll better know how secure your systems really are.

1. Intro
2. OS level
3. Database level
4. Application level
5. Conclusions

I’ve just put together my abstracts for O’Reilly’s MySQL Conference in April 2008. Some of them might sound familiar…

Learn to watch your database like a fitness diet. Trim down the SQL queries, use the right hardware, and monitor the right metrics to keep it running fast.

There are healthy databases and their are unhealthy ones. We’ll take a look at what you feed your database, and how to keep it fit with just the right diet of hardware, configuration, and SQL query tuning.

1. Introduction - Diet of a Champion Database
2. Disk, Memory, CPU - Body by Intel
3. Applications - Lean & Fit
4. SQL Queries - High Fiber, Low Fat
5. Conclusions

Typically a backup or dump of a MySQL server includes all of the databases available, using the -A or –all-databases options. But what of restoring, and recovering that dump?

One can simply go to the target machine, and delete everything in the data directory, right? Oops, you didn’t delete the initial MySQL database did you? How about the special “information_schema”, MySQL’s data dictionary? The other option of course is to use mysqladmin:

SQL> mysqladmin -f -u root -p drop mydatabase

But still I’ve had cases where I’ve dropped parts or all of these initial MySQL databases. So what to do if you do?

Luckily MySQL comes with a shell script to save you in just such cases. It’s called mysql_install_db and can typically be found in /usr/bin. For Oracle folks you can almost think of this like the catalog.sql which in turn runs the sql.bsq file. It is illustrative to take a look at this shell script, and see what’s contained in there. You’ll learn a lot about the bootstrapping process.

MySQL has documentation on the mysql_install_db script.

So when you go to building your backup scripts, and are putting all the pieces in place, be sure to make a note of this script, and remember where it is. If you are providing instructions for recovery for Unix Admins who may not know MySQL particularly well, be sure there is a note, or even better yet, a call to this script in your own restore scripts.

In Part II in this series, I talk about how these three databases compare in some particularly crucial areas.

For instance how do the optimizers of these different database engines behave, and why does that matter?  What type of indexes are available, particularly with respect to typical applications.  I then move on to datatypes available and which are missing.  You’ll find some surprises here.

Lastly the holy grail of any modern relational database, I discuss transactional support. Relevant concepts include ACID compliance, read-only versus insert and update activity, and so on.

Recently I had some trouble with the server where all of my websites are hosted.  Business site, various blogs, there is lots of stuff on there, not to mention backups of work, email, and all sorts of things I do not really want to lose.

I first noticed the trouble when I couldn’t login through the command line.  Strangely the websites were still running.  I called the hosting company, and after talking with them for a while, managed to login as root.  That was working.  But it was acting quite odd.  There were some errors in the /var/log/messages about ssh not being able to set uid 10003, the uid of my login, shull.  I pondered.  I thought.  I sat circumspect.

I investigated for a while, and called up 1 & 1 again.  I have a root server, but they’re not really supposed to support maintaining the machine itself.  Then I got to thinking, I could spend hours diagnosing this, searching for a rootkit, but why not just jump on a new server.  Cause things just don’t feel right with this situation as it is.

So that is what I did.  I got on the phone with support later in the day, after talking for a while with the guy it at first sounded like it would cost a *LOT* more for a new server.  But that was mostly because the names of server packages had changed quite a bit.  The $100/mo one worked quite well.  I asked how long it would take to setup.  The guy was being really helpful, but then he just said the party line, 24-48 hours, he explained.  I explained the urgency.  But there wasn’t much he could do.

I got off the phone, and ordered right away.  Checking a half hour later on my order status, what to my surprise, the server is setup already!  I got down to work right away.

I switched over all nineteen domain names.  This was easy enough since 1 & 1 handles them already.  Then I went to Godaddy, the registrar, and configured the nameservers as 1 & 1 instructed.

Then I went back and started copying over all of the home directories.  Most were small, so they copied over quickly.  Even my own at 6G only took about 30 minutes.  Both servers were on their own network, easy as pie!  I then copied over the mysql databases for each of the dynmic sites, six plus blogs, a couple sugar crm instances, and two phplist email list management configs.

The great part was I had already localized everything for apache into one iheavy_sites.inc file.  All I had to do was include that in the new server’s httpd.conf, edit some directory paths, and restart apache.  There were a few little things here and there, but primarily that was it.  After a few hours the domains started working, and I was so excited to see things really working.

The new server was PHP5 and MySQL5 and things just worked.  This is just soooo good, I thought!  I still had to get mail working.  My good friend Jing went ahead and configured postfix & imap, as he is the email guru.  Not long after ask (Active Spam Killer) was installed, and I moved my Maildir into place, and voila, I’m happily sifting through my mail on my Mac OS X Mail.app client!!

While I was at it I configured the new ftp backup system so files get automatically archived there.  This is something I had been meaning to do for some time.  And while I was on the Unix Sysadmin binge, I setup a few domains and Wordpress blog for a friend who has a dance company.

All told I was really only down 24 hours, and most of that time primarily email was out.  The switch to the new server was so smooth, I barely got a headache from the whole affair.  I guess this is bound to happen once every other year if you don’t patch your systems regularly.

And like clockwork, just yesturday I got an email from 1 & 1 saying they noticed some strange and illegal pinging and packet activity coming from the old server.  Surprise surprise, it was compromised as I suspected.  I explained to them the situation, and they blocked the relevant ports.  That way I could leave the old server online for a little while longer, in case I need to get any other data off of there.

Thanks to Felix for some suggestions and advice, and thanks to Jing for email setup.  We’re back!!

Recently I wanted to setup a little MySQL sandbox where I could hack away at MySQL with reckless abandon.? A sandbox is different than a test environment, it’s usually one which is very breakable.? You want to be able to break things, or rather take them completely apart and put them back together.? It’s the only way to understand all of the moving parts.

So searching google, I happened upon Giuseppe Maxia’s Replication Playground. It basically installs into an unprivileged directory, one master, and three slaves.? You can then test out various scenarios. Read his blog entry.

It is trivial to install, however I encountered some issues with MySQL 5.0, which caused me some troubles.? I sent him my feedback, and comments, and it looks like he has rereleased it as the MySQL Sandbox. Good stuff.

This is the fifth in a series of abstracts I’ve submitted for the Collaborate 2008 conference in Denver CO.
There are healthy databases and their are unhealthy ones. We’ll take a look at what you feed your database, and how to keep it fit with just the right diet of hardware, configuration, and SQL query tuning.

1. Introduction - Diet of a Champion Database
2. Disk, Memory, CPU - Body by Intel
3. Applications - Lean & Fit
4. SQL Queries - High Fiber, Low Fat
5. Conclusions

This is the fourth in a series of abstracts I’ve submitted for Collaborate 2008 in Denver CO.
As an independent consultant for twelve years, I’ve encountered a lot of interesting and challenging projects. I’ll discuss five different cases, and what lessons I took away from each.

1. Intro
2. The Right Hardware
3. Importance of Good Testing
4. Patchwork or Good Design
5. Don’t Mix Opposites
6. Use The Technology
7. Conclusion

This is the third in a series of abstracts I’ve submitted for Collaborate 2008 in Denver CO.

Security is on everyone’s radar these days. You may be wondering yourself whether your database systems are really as secure as they should be. We’ll discuss some of the latest vulnerabilities, and what you can do to protect your systems.

1. Introduction
2. Authentication
3. Privilege Escalation
4. SQL Injection
5. OS Security
6. Network Security
7. Conclusions

This is the second in a series of abstracts I’ve submitted for the Collaborate 2008 conference in Denver CO.

A little known fact is that Oracle’s standby technology - the stuff DataGuard is built on top of - is available in Standard Edition of Oracle. With a little elbow grease, and some simple scripts, we can have a rudimentary and functioning HA solution in Oracle SE.

1. Intro
2. Anatomy of a Standby Database
3. What’s there in Oracle SE
4. Setup standby
5. Shipping logs
6. Applying logs
7. Verifying setup
8. Manual Failover
9. Conclusions

This is the first in a series of abstracts I’ve submitted for Collaborate 2008 in Denver CO.

Marketing is one thing, bulletproof technology is quite another. Operating Systems have bugs, Database Software has bugs, and so does your application, probably. A better question is how hackable are you? We’ll look at some of the nefarious ways intruders can get in, so you’ll better know how secure your systems really are.

1. Unbreakable As a Pie In the Sky
2. OS level
3. Database level
4. Application level
5. Conclusions

If you’re interested in how these three databases measure up in terms of feature sets, take a look at part one in a two part series I wrote over at Database Journal.

I discuss stored procedures, views, materialized views or snapshots, triggers, and security. Stored procedures and functions are supported on all three databases, as are views and triggers. Although MySQL and Postgres aren’t there in terms of default snapshot support, there are ways to get that functionality in a somewhat roundabout way.

Security is always a tricky question, as all the bugs out there aren’t always publicized. It’s sort of a cat and mouse game. All three databases support user based authentication to login to the database, and various privilege levels to control access to objects and data. Oracle also supports FGA or fine grained access control for column level control.

That said I might tend to say that open-source products in general have better security, their source being an open book and all.

Everyone waxes on and on about google’s search being so good. Don’t get me wrong, it is good. But many of our desktops are not enjoying that same goodness.

I’ve recently returned to the Macintosh world, with this Mac Book Pro I pickedup a year ago. It is a gorgeous machine in so many ways. When I first started using it, I was comparing all the things in Mac OS X to what I remembered from System 7 + 8. Believe it or not I used to program the toolbox, MacApp, Codewarrior, you name it. But I digress.

The first thing that struck me was how good Spotlight was. And how easy it was to access via Cmd-SPACE. It also struck me that it always seemed to be up-to-date, even with new files I’d just added to the system. It’s speed rivaled Unix’s locate, that I had grown to love using Linux for so many years.

That’s all changed now. It’s all part of an almost laughable past that I barely remember just one week ago, when I discovered the pornographically good Quicksilver.

What’s the big deal you say, another way to launch apps, another way to search your computer. No, this thing is much more. It’s like a whole new way to use your computer. It is so damn good at doing the right thing, that it just reduces steps, and brings you that much closer to communicating with your Mac by pure thought alone!

For starters you change the Spotlight shortcut so Quicksilver can come up with the same Cmd-SPACE you’re used to. You *MAY* wanna tweak some settings, but I didn’t have to do much. Another thing I recommend, remove all your apps from your doc, and from plain site, and use Quicksilver to launch them for a week. You’ll see what I mean. Ok, so it can launch, ok, so it can search, ok so it’s fast. What else?

Here’s an example of some of the surprises I found. Recently I spent a good number of weeks integrating my contact databases. I had one in my phone (a Sidekick which wouldn’t sync with my mac) that had mobile numbers. I had another that I was just putting together in Apple’s Addressbook, importing data from my old PINE addressbook that I had used for so many years. That had email addresses of everyone I’ve ever emailed for 15+ years. And then I had an addressbook in Sugar CRM and contacts in Linkedin. Don’t ask how I got to this sorry state of multiple database contact book madness, me being a DBA and all… Pulling these records all into Apple’s addressbook took time. It was a combination of various manual and automatic processes, massaging data, and eyeing names, for spelling inconsistencies, and redundant entries. After spending all this time, Quicksilver just illuminates my contact database. Search for a name, and the vcard comes up, you can launch addressbook, or display it in a big monster font right in the center of your screen. Of course with copy & paste at the ready! Now here’s the kicker, I went and manually indexed my old pine addressbook text file in Quicksilver, and now when there’s an entry I can’t find, that somehow never made it into the Mac Addressbook, Quicksilver will find it and display it real friendly like!! This is good.

To be fair the integration of all my messed up databases, the purchase of a blackberry which has all 2000+ entries that my Mac Addressbook does, *and* the discovery of Quicksilver all kinda happened at the same time, so that might have inflated my excitement (and don’t even get me started on why I didn’t buy an iPhone). Nah, Quicksilver brings you closer to god!

By now I know that those of you who haven’t used it are gonna go and download a copy, and the rest of you who have it installed but just don’t use it much are gonna take all your apps off the Dock and start using it everyday, right?! If you wanna learn from the horses mouth, I really recommend you check out Nicholas Jitkoff’s Google Tech Talk.

FYI, for the time being Quicksilver is only available for the Mac, though I hear Launchy is quite good on Windows.

This article was originally written while I was guest blogging over at the glorious #comments blog!

After being thoroughly tired of Moveable Type for some time, and having some great experiences with Wordpress on some other blogs I edit, I decided to switch Oracle + Open Source, so here we are! Enjoy.