leyton.org

One of the developers I support recently came across the following error message in Microsoft SQL Server 2000 (SP4):

Server: Msg 8104, Level 16, State 2, Line 1
The current user is not the database or object owner of table ‘table_name’. Cannot perform SET operation.

Unfortunately, there’s little out on the interwebs about this beyond references to a Microsoft Knowledge Base workaround (KB 302621), which concedes it’s a bug and you need to grant serious permissions to the user.

Unfortunately, we just didn’t have that luxury. With a complex permissions system and very sensitive data, we simply couldn’t let users have db_ddladmin or db_owner on this system. We’ve had inadvertent accidents where this had been erroneously granted, and we’d finally worked those out. We just didn’t want to resort to that.

Whilst alternatives to the BULK INSERT operation were possible, they weren’t desirable. And I’m afraid I’m the sort of DBA who likes to understand the specifics of an issue before accepting alternatives. In this issue, it just didn’t feel ‘right’ to grant such powerful roles. So the developer (let’s call him Andrew) and I worked through various theories. The BULK INSERT was working elsewhere, so why not on this specific table? We ruled out permissions and constraints, but finally came to realise it was because the table had an IDENTITY column. The proof was when BULK INSERT worked correctly, without the above error, when we didn’t use the KEEPIDENTITY option.

This makes sense, as the ’seed’ for the IDENTITY column is meta information, and stored elsewhere on the table structures. If the bulk insert is providing values for the column, the ’seed’ needs to be updated afterwards for any subsequent (normal) inserts that do not provide a value. Our presumption is that the permission model is simply over zealous in this case.

Sadly, Microsoft don’t seem to mention that losing IDENTITY values is another workaround. In our case, because we still needed unique values, we could switch to a number fountain (ie. select max(id)+1 from table with repeatable read) – The IDENTITY was a nice simplification. Others may not be so lucky. But figured it was high time I posted some technical details to ‘give something back’, given this was the very reason I setup this website in the first place.

Oh dear. History appears to be repeating itself.

Some years ago Sun bought the Clustra database product. I was a big fan, and had a great relationship with the company. The sale made sense, but Sun just didn’t know what it had bought, and buried it deep inside another product.

I held out a bit of hope they’d actually seen the light when there were noises in 2005 about Sun coming out with it’s own database product, but sadly I (and perhaps a few folk in Trondheim, Norway) were disappointed when they simply re-badged PostgreSQL.

Now they’ve bought MySQL (See here and here). Amusingly MySQL bought NDB, a telecoms database product from Ericsson’s labs, that was attempting similar things as Clustra, but far less effectively. So now with Sun having bought MySQL, perhaps if they’ve finally found a way to capitalise properly on their investments, they’ll bring Clustra out of retirement and pop it in to MySQL as a distributed storage engine.

But I’m far too cynical and long in the tooth for that. Much as Computer Associates have an eery ability to buy products and then complicate it beyond all rhyme and reason so it barely resembles the product they bought (Some perverted Midas touch), Sun just bury things like accidental squirrels.

Don’t get me wrong, MySQL is a great product. But it’s not my choice for enterprise database platforms: It’s missing too many features for that, and is a little too esoteric in how it solves certainly problems to ever seriously threaten that market, or command serious enterprise money. MSSQL, DB2, Sybase and their ilk have that licked.

So turning into a solid revenue stream will be difficult for Sun. Perhaps Sun will do the sensible thing and leave it be as a separate entity, continue to do what it does best, and just turn over the Clustra code and forget they ever bought it.

A geek can hope, can’t he?

A list of freely available Classic texts – not least my personal favourites The Unix Time Sharing System, by Ritchie and Thompson and the awesome A Relational Model of Data for Large Shared Data Banks.

So there I am, with a mysql replication setup, with one box getting ready to take over from another as the live database server. Beforehand though we wanted to get it running replication, staying in sync, and also replicating to the new backup database server. So I needed a slave to replicate on, as a master, to the other box, ie.

Master -> Slave/Master -> Slave

But nothing from the original master was replicating down to the final slave. Things run on the middle box replicated down, but not stuff from the top of the tree. Search as I might, nothing jumped out at me, despite having various books spread about me, and google weeping out of frustration…

There must be a configuration option to pass on updates to slaves, I thought, but what? Eventually, after an hour or two of searching, I finally found it: log-slave-updates in my.cnf ensures that a slave writes updates to the binary log, and replicates it down to slaves that are treating it as a master. Switch that option on, and there’s an almost audible – and wonderfully healthy – noise from the computer as the replicated operations pass through the setup, to my new slaves.

Shame the mysql online references aren’t quite as useful, or keyword ridden, as I hope this page proves!

There are more Sun rumours circulating that they’re considering a move into the database market (the so-called “Sun DB”), but that this time “we are looking at PostgreSQL” (in the words of a senior VP).

I’m still convinced^whopeful it’s Clustra, and that this PostgreSQL is nothing more than smoke and mirrors, not least that I’ve used the product in anger, so know that Sun have a gem on their hands.

However, Sun have a reputation for burying things. That the SunDB rumours haven’t vanished altogether is still cause for optimism, so here’s hoping they’re just keeping the speculation going. Although they could do with a lesson or two from Apple on managing rumour to the benefit of all

More here at /..

You could have been forgiven for thinking that not much had happened at Sybase in the last few years, as they’ve seen their market share dwindle and dwindle in the face of opposition from Microsoft (with it’s sister (of sorts) product to ASE), IBM and Oracle. The low single digits are about the best Sybase do these days, often prefaced with “Finance industry”.

Unfortunately for them (you’ll see why in a minute), they have actually been rather busy, having recently announced v15 of their flagship database product, and it’s probably the biggest overhaul of the product ever. Certainly since v11. For Sybase fans, there are some great features in this new release.

So why unfortunately? Well, the big release has been met with a big, resounding, awe-inspiring, silence by all of the computer press sites I make a point of reading these days, including the Register and news.com, which talks about the planned launch, but not much about it, or after it.

You’d almost think people don’t care these days… Or they’ve not actually done much in the way of moving the industry forward very much…. Or both.

I suspect it’s both. And I say that as somebody who is a Sybase DBA at the moment. There’s very little in either the Sybase product that can’t be done better in other products, more flexibly, more powerfully, and with less management. There’s certainly a lot of catching up going on in the product, witness the talk of a RAC like feature coming in the next release of v15 (I always worry when a vendor is selling you the next release of the product, rather than the product itself). That I can see, there’s really not much that’s being addressed that matters so much to enterprises out there at the moment, at least enterprises that care about minimising the amount of fiddling necessary to run a database platform.

So here’s the rub, to me at least. I think the DBA’s function as a guru of configuration options and optimiser tricks is not what’s needed by enterprises. Sure, they want people who understand the products, but they (should) want DBA’s further up the stack, working with developers to build better applications, solving business problems, and not fine-tuning obscure parts of their infrastructure at significant cost in both time and expense. Most of the problems can be fixed with good, proper, sensible data modelling techniques (the earlier the better), and using products that (as they rightly should) take care of the maintenance tasks (index rebuilds, statistics updates or de-fragmentation processes) automatically. You know, built-in. There are enough problems higher up, closer to the business to solve that most database platforms just aren’t approaching yet, and it’s here Sybase missed a march.

But I fear Sybase haven’t realised that, and for that reason I’m not sure we’re likely to see much of them in the computing press, at least until they get bought by somebody. But who’s likely to do that?

A school of thought exists in many database and system administrators that performance tuning is the highest of arts, the greatest of skills, and the loftiest of aims. Buy any system and tune it well – so goes the theory – and it will perform to a level unanticipated by your peers. You will receive praise and adulation.

I fully understand that it is a rewarding and enjoyable occupation, for the task itself is beautifully self-contained. No messy “user requirements” or “business processes” to consider, just pure unadulterated tuning. Gather your metrics, dig out your obscure, imported, reference book (preferably with a cool red Porsche on the front), stick on the headphones (Radiohead preferred), sip your coffee, and start tunin’ them there systems!

You can probably tell that this is something I’ve done before. You certainly wouldn’t be wrong, and I do – I’ll confess – slightly yearn to find myself in that situation again where I can geek out and optimise to my hearts content, sure in the knowledge I’m taking the greatest test a geek system administrator can take, with a proof that is irrefutable: The system now runs five percent faster because I’ve tweaked insert_complicated_term

But there’s a problem with this. It’s not actually the best thing to be doing in most situations.

This might sound a bit controversial at first – especially to any sysadmins or DBA’s reading this – but do allow me to explain over the course of the next few paragraphs. I’m certainly all for doing it when the situation necessitates it. But as a means in itself, as a reason-d’etre, it’s really isn’t the best way of expending effort.

It’s my experience, gained over years of supporting systems that are often quite closely tied to the users – rather than abstracted away through layers of support and departmental obligations – that almost all of the performance problems I’ve encountered have been down to simple things, often simple programmatic problems or errors brought about by a misunderstanding (from the developer or administrator), an architectural choice, or simple resource contention.

Ok, so maybe in some places the developers are all top of the class, fully jargon compliant, aspect, object, extreme, agile, pure-jargon oriented folk with the lastest iPod and geek-chic t-shirt. But I’m yet to experience such a place, and heck, even the best developers (and certainly system administrators!) should admit they’re prepared to learn a thing or two from each other. Eagerness to learn is, in my mind, more admirable and beneficial than complacency and over confidence.

It’s true that it’s the administrators role is to ensure that the best performance is achieved from the systems in use, but all too often I see administrators and DBA’s zooming in on the minutiae, rather than looking at the bigger picture. Not seeing the wood for the trees, if you like (although that’s perhaps a bit unfair).

The rub is us system administrators are often quite an antisocial lot, no laughing matter the moniker “BOFH“. We find it easier to deal with machines, rather than engage with the developers. Yet it’s precisely the engaging with the developers where most of the big performance problems can be solved. Work together to find and correct those approaches – drawing on the best knowledge from the team – so that the application and the database work well together, and things will be much easier/faster – both now – in the future.

Give a man a fish; you have fed him for today. Teach a man to fish; and you have fed him for a lifetime

What I’m saying here then is, rather than focusing on tuning out the best performance as a means in itself, it’s better to sit up, take stock of the entire application infrastructure, and engage with the people working on it elsewhere. Take a holistic view. Don’t focus purely on the performance tuning detail, but engage with the users, the developers, the managers and the other administrators, and try and build a picture. Is the approach right? Challenge assumptions, and question decisions. But build a picture. With that picture, I’d bet that better performance can be built over time, certainly that can be built purely by focusing on the detail.

I just received this in my e-mail, which whilst hardly major, is still quite a nice compliment:

This is to inform you that the website leap.sourceforge.net has been designated a Cool Site in the Netscape Open Directory Computers/Software/Databases/Relational/Implementations/Quasi-relational

It’s also just past the tenth anniversary of LEAP’s first hesitating release, in May 1995. I had a search about Google, and found the original Usenet announcement posting.

That first release, the original DOS only, Borland Pascal version, was the basis of my final year project. The version distributed today is a complete rewrite in C, which runs on a whole host of Operating Systems and is still used, I’m pleased to say, in a lot of Universities. It’s hugely rewarding to hear from students and lecturers alike so long after I initially released it.

If doubt either Stefan Stanczyk or myself would have guessed at the response it received, and continues to receive. Many thanks to everybody who has helped out by either e-mailing, commenting, contributed or simply used LEAP (or bought the book!).

I get a number of queries and e-mails hitting the website on all manner of topics, one of the most common items being simple questions regarding management of transaction logs in Microsoft SQL Server 2000. I thought it might be useful therefore to put together a few posts on simple technology issues that might be of use for visitors. There are other resources out on the internet of course, so these are just some quick pointers to guide you on your way.

This first post is on the simple task of truncating down the SQL Server transaction log.

Read the rest of this entry »

The last few weeks have seen a number of very strange and very weird problems with a Microsoft SQL Server installation I support. Over the course of the weeks I’ve seen a number of very horrible errors, culminating last week with a SQL Assertion error (Error 3624), and repeated 17883 errors (Scheduler problems).

Read the rest of this entry »