We Host Sheep

Recently, I was getting a very strange error on my mySQL cluster. It kept telling me two things! When issuing:

alter table X engine=ndbcluster;

I would get:

Cannot Create Table ‘./database/#[a-zA-Z0-9]‘ (155)

Interesting - I thought to myself.

This seemed both annoying and un-helpful and googling led me nowhere. So I persisted in trying to work out what the hell was going wrong, however, this interesting little error led me to follow a new way of thinking, as when I issued the command:

CREATE TABLE `group` (
`group_id` int(7) NOT NULL AUTO_INCREMENT,
`group_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

I got the error:

 Table ‘group’ already exists.

Well, it didn’t exist, but this did give me a really good idea as to what was going on. Since, previously I was able to do this no problems and since the cluster was - as far as I knew, operating fine - and I was creating a table with an InnoDB engine, it didn’t make sense that the two errors happened to happen at the same time.

The answer lies in the ndb_mgm console, and issuing:

ndb_mgm> show

Gave me the answer - plain as day.

Connected to Management Server at: host.name.here:1186
Cluster Configuration
———————
[ndbd(NDB)]     2 node(s)
id=2    @host.name.here  (Version: 5.0.32, Nodegroup: 0, Master)
id=3    @host.name.here  (Version: 5.0.32, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @host.name.here  (Version: 5.0.32)

[mysqld(API)]   2 node(s)
id=4   (not connected, accepting connect from host.name.here)
id=5    @host.name.here  (Version: 5.0.32)

Now, I’m not entirely sure why - but, as soon as I issued a restart on the mysqld, and it reconnected all of my issues went away. I could create new tables on any engine and alter tables to NDB.

The interesting thing that I have yet to have answered, is why when an API disappeared from the cluster but all of the storage nodes were there, why the cluster suddenly had issues. Very weird, none the less, it fixed my problems.

Well hello retro!

In this modern day of search and sunshine (not sure where the sunshine is since it rains all the damn time), I thought I’d take a trip back to version 3.x of the google mini to bring you some of those answers that you may be asking, like, what is this &restrict= crap and why does my license have ‘unlimited collections’ isn’t that standard?

Well, to answer those questions and probably only those questions, here is some information on the 3.x to save you from potentially purchasing a dud! The google Mini 3.x has a LIMITED COLLECTION LICENSE, ie, you get a collection by collection license.

This is both pointless and frustrating, but I can see where they were heading with it. Basically, this is where the &restrict= flag comes from, with the ‘old’ way of doing things, you would create a master collection (ie, default) and then you could create sub collections underneath.

In this way, two very frustrating things come up, &restrict= is too easy to replace with null, and therefore your main index is open! (just like keeping the default_collection in 4.x and 5.x versions) and of course, the second being that you only have 1 front end per collection, ala, 1 frontend for the machine.

Well, you say, that sucks? Yes, yes it does.

There is also a severe lack in functionality, support and general usefulness and my personal advice to anyone looking at a google 3.x mini on the ‘aftermarket’, is do not purchase this device. At LEAST buy a 4.x.

Have a nice day.

So it seems a bit strange to say this, but most developers I know hate the idea of Views, Stored Procs and Custom Functions built into the database system. The simple reason for this is:

Application and Business Logic, belongs in the application.

Although I completely agree with this, my time spent recently with a highly skilled DBA has taught me that sometimes, you want to help shape what the developers see from the database. There’s a number of reasons that this works, firstly, you can support cross database queries in a highly controlled and secured manner - bonus, and secondly, you can apply additional logic or functionality that assists in the developers being able to quickly and easily map information in a complex way.

As an example, we control ‘active’ and ‘inactive’ database rows (yes, each row on every table) with a begin and end number. This may seem like a huge waste of time, but we keep meticulous records - so instead of deleting or updating, we create new rows with new numbers and close off the old ones.

This solves two problems, firstly, we don’t have to keep a ‘log’ of changes, as we can plainly see them with very simple SQL queries and secondly we have a complete history of records for the business boffins to be able to roll into nice reports that show the progressive nature of ‘change’ in our data.

Thus, we archive old data fairly regularly and we do this by looking at these numbers to ensure that we clear off anything we haven’t used in a while.

Okay, straight forward, but what does this have to do with views, stored procs and custom functions? Well, it’s simple, recently I have started writing views, in place of tables, that provide the records in an easy to use way, that automatically check for active records and inactive records, so that the developer doesn’t have to do this each time.

This doesn’t limit the developer from accessing the tables directly, but it does give them a great, quick and simple way to do something rather mundane, that we do constantly.

So in summary (brief and fairly non-descript), don’t discount this particular group of functionality from within mysql just because it belongs in the application .

Enjoy!