lifebsideben

I spent probably 6 hours today trying to get Bacula working fully. I’d never tried it, but have been thinking about trying to implement a “real” backup system for some time. By “real”, I mean, something that can back up the most important bits of my two Macs, my remote webserver, and even bits of the RAID5 array that are “super critical”, to a separate harddrive, handle incrementals, and not involve lots of hackery on the clients. Bacula does seem to fit the bill, and I picked up a 400G eSATA drive to add to the server last week for about $100 to be the backup media (tapes? we don’t need no stinkin’ tapes!)
Read more »

Tags: bacula, hardware, linux, mac, NAS, software

It really annoys me that the WordPress plugin administration page lists the plugins in a seemingly random order. Upon closer inspection, it’s actually the order they are discovered on disk, which isn’t necessarily the most useful, especially if you have a lot installed (like I do) and are scanning the list for something to activate/deactivate/etc. On my server, the list is essentially sorted in case-sensitive alpha order. I just wanted this to be sorted case-insensitively sorted.

So, I made a quick tweak to the plugins.php page to do this. Here’s the changes, if you’d like to do the same:

Right around line 73 of wp-admin/plugins.php (immediately following the call to get_plugins()), I added:

[code lang="php"]function iksort($a,$b) { return strcasecmp($a,$b); }
uksort($plugins, ‘iksort’);
[/code]

Next step: sorting the list of configurable plugins in the submenu under ‘Options’!

Due to some other work I’ve been doing in PHP5, and wanting to host on the prince.org server, I finally decided to bite the bullet and upgrade the server’s PHP (for the sites). I had been dreading it, because I knew the eAccelerator wouldn’t work properly with the PHP 5.1 code, and I have my misgivings about APC, (even though we use it in production at Yahoo everywhere) but I desperately need the cache. Plus, the legacy “org” code was written in the PHP4.0 days, so you know there’s going to be issues… like all that wonderful “you really can’t return a reference here… now that’s an error, we used to ignore it (and sometimes segfault)” thing.

I did recompile it though and slam it up, along with building the APC (”pear install apc”, whoo hoo… except for the gotcha in the docs, if you can even find them… it’s an “extension” now not a “zend_extension”). I also built in both the mysql and mysqli stuff (old code uses my own large mysql wrapper class, new code uses ADODB with mysqli underneath for cursors, etc.) All went pretty
smoothly, once I hunted down and killed a rogue line in my php.ini.

I did have a couple places where I had to change the return values of functions to not be references, and now I’m getting a lot more undefined variable warnings (my code sets error_reporting(E_ALL), and apparently it’s stricter now), but basically all went pretty well. And so far, I think I actually like APC better than eAccelerator. I definitely like that some of the Yahoo extensions (for loading constants and storing arbitrary values in the cache) are now exposed. Yay Open Source, yay Yahoo.

So far, so good. Only thing left is to get a weird older version of the Pear class NNTP_Realtime working again (it went missing, after the PEAR install on PHP5, oddly). I need this for some of the Usenet interfacing code. But hey, that shouldn’t be too bad. Way to go PHP5!

Tags: linux, php, princeorg

So recently my team built this tool which essentially enables ‘experts’ to blog for Yahoo (unlike 360, there’s editorial workflow, fine-grained access control, etc.). The first folks to launch with it were Health. One of the most popular blogs for them is named “Real and Revealing” (or as I simply call it, “the sex blog”). Recently there was an entry about Female Orgasms. (Insert your own joke about there’s-no-such-thing-as-female-orgasms here.) Anyhow, the best part was one of the comments:

its very hard for me to have an orgasm. the sex would just feel sweet and it makes me want more and sometime dont want to stop. i have never expierence an oral some say it feel damn great to be honest i would like to explore that part of sex but not to return favour. sometime i really think my boyfriend realy suck at making love and he is not very romantic.
Posted by whilybo_2 on Tue, Jun 28, 2005, 10:30 am PDT

Nothing like complaining about your sex life, your boyfriend, and highlighting your lack of willingness to reciprocate all at once… what are you, Republican?

It’s been a while since I hacked up any DHTML (checking the cvs logs, a year or so since I implemented the ‘orgPop’ feature with xmlhttprequest.) Every once in a while I get the hankerin’ to mess about with client-side stuff, and the past week seems to have be the latest spurt of activity. First it was a Greasemonkey script to more-easily expose info that the work apaches are configured to inject into served pages, now it’s tinkering with prototypes of small bits of functionality for an app my team’s working on. I think I also got a bit inspired by the rollover-news-summary feature on the new Beta of news.yahoo.com, which was a nifty bit of last-minute (as in I think it was 30 mins before the beta went live) engineering by that team (nice!)

So far I’ve hacked up (with little snippets of js code from around the ‘net),

• a dynamic html table that you can add rows to based on selections in a select box (i.e., pick a new user->adds a row for that user, with columns for their attributes)
• a pair of html lists (<ul>s) in which you can click on the items, moving them between the two lists
• a textarea paired with an input box, where text entered in the input box can be autocompleted by any word in the textarea (it’s pretty slick, very gmail-email-address-autocomplete-like)

I’m pretty happy with the results, especially because I haven’t really done anything special to test for IE/Moz, and everything seems to basically work on both. I don’t know if any of these will actually make it into our project, but I hope so. The list-thingy (the second one above), however, isn’t that stellar from a user-interface point of view. I think I’ll end up arguing for paired select boxes with similar behaviors, like Matt Kruse’s Options Transfer example.

One link… since almost all my programming books/references are in storage 2000 miles away, this excerpt from JavaScript: The Definitive Guide (4th ed.) has been awfully useful. I have the 2nd edition in storage, might be time to pick up the newer one.

Tags: DHTML, javascript

I haven’t posted in forever. I know. I will start up again soon. But I just looked down and thought, hmm, this is kind of interesting and says something about my workflow… how many and what windows (applications) I have open. I’m currently working on the tail end of a lot of changes to prince.org 3.0, and thought it’s interesting. Here’s what’s currently open:

Tags: engineering, Firefox, software

Hey, remember VisiCalc, the precursor to Lotus 1-2-3? Well, maybe you don’t. I do, mostly as a “business” app that I couldn’t afford (on a platform I couldn’t afford–the Apple ][). And I wasn’t that interested in that kind of software at the time, mostly games caught my fancy when I was trying to get a VIC-20 of my own.
Anyhow, what’s really cool, is that you can download the reference card and a working executable of VisiCalc (for the PC), from Dan Bricklin’s site. And great reading for the geeks is some of the design/programming notes from Bob Frankston’s pages. If you’ve never coded in a memory-limited environment, or in assembly, or for performance, it’ll be eye-opening reading. Lots of stuff that has been ingrained into my habits throws back to those days–including a few habits I’ve had to break to avoid premature optimization. I often think about students who learn Java as their first (does this happen?) programming language, are missing so much of the coding landscape, context which is entirely necessary to building quality systems. Sigh.

Got my my first issue of “ACM Queue” in the mail the other day. I was shocked they actually sent it to me in Spain, for the free intro offer, and supposedly at the same subscription rate as for US orders. Anyhow, it’s decent. A little bummed the first issue I got was the June (maybe the mail is just that slow), but content-wise there’s some really interesting stuff. The focus of this issue is ’storage’ and the interview with Jim Gray was pretty interesting; the article (primary) on physical disk stuff from a Seagate guy (Dave Anderson) was not that thrilling, nothing too new there. But the overall level of the articles is good, not quite up to some of the orther ACM pubs but worth the time. In the end, I doubt I’ll subscribe–I’ll just read the interesting-sounding articles on their free site. As much as for any other reason, I don’t feel the need to have trees destroyed and stuff mailed around the globe, for me to receive this info. On the other hand, I’m not giving up my New Yorker subscription anytime soon

I forgot whose blog I found this link on originally (sorry), but I’ll link to this here too, ‘cuz it’s high on the geek factor: not quite a DIY project, but a nice one-off: a homebrew Segway clone.

Tool links: URL Bandit watched the clipboard and snatches out URLs it sees, handy, especially for blogging…

Tags: engineering, software, Spain

I thought a good, non-personal-info-divulging post I could make would be about various tools I use, this way I can vent, and maybe someone can suggest improvements to my workflow, etc. I won’t get into the things I deploy on servers for apps, mostly because this tends to depend on the project more than any personal bias (I hope). In this post I’m just going to share the applications I use daily for my tasks, not the things I deploy. Maybe in the future I’ll do one for libraries I use a lot…

Email
Corporate: Outlook. It’s just the best windows-based reader, and I need the Exchange integration, so options are limited. I like it and am used to it, anyhow.
Personal: Yahoo business webmail. Yeah, it’s a little clunky, and the spam filters aren’t perfect, but it’s pretty damn good webmail, that works everywhere, it fairly light, and “reasonably” priced. Aside from a few weeks 1 year ago when they kept screwing up my mailbox, it’s been flawless from a reliability/stability standpoint. It’s my browser’s homepage.

Word processing, Spreadsheets
Microsoft Word & Excel: I give in, I’ve just been using them too damn long to use anything else. If I could convince my CEO to switch the whole company to openoffice, I’m sure I could live with OOWriter etc… but I sort of know where everything is in the MS apps, I have my little style-macros set up, blah blah. Anytime I use Word to write something longer than a few pages I run into the various ugly spots (list numbering, outline vs. normal text authoring, master document bugs, etc.) but I generally know the workarounds. I seem to remember loving AmiPro in a previous life, but M$ has the corporate world beholden at the moment. And Excel, well, it’s possibly the most polished app Microsoft makes, I think. An assload of features that actually work, and eminently useful for light- to medium-weight analysis. I’m going to ditch my pirated copy of office at home (well, technically I think I am covered by our corporate license somehow) one of these days, and really try StarOffice or OOO. Certainly on the Mac at home, I’ll give it a go–I have a no-pirate policy on the wife’s computer (whoo hoo licensed Adobe products!)

OS
To put it simply: If I want a desktop, I run Windows. If I want a server, I run Linux/FreeBSD/Solaris, depending on the purpose and pocketbook.

Coding
Ah, now we’re on to the serious stuff!
Editing/IDE
On unix-ish machines: Emacs, with my php-model.el and some other goodies. vi only when absolutely necessary. On windows, we get into a world of pain. Yes, I know I can get emacs for windows, but I just… well… hate it for some reason. It feels kludgy, and if I can drop files on a window to open them, I like to.
I’ve been trying out a few different editors. For C# of C++, no argument, I use work’s copy of VisualStudio… the Intellisense, and GUI builder integration, is so tight, I think it’s insanity to use anything else. But mostly I don’t do that kind of stuff these days. (Strangely enough I always got away without a “smart” IDE for Java when I did a lot of that… I typically just used Emacs. Probably these days I’d go for IDEA or Eclipse, though.)
Anyhow, I have purchased EditPlus in the past–it’s super small, light, fast, and basically pretty nice, but isn’t really IDE-ish–but a very strong text editor, and fits nicely on a floppy. I am glad I bought it, but only use it for lightweight stuff now.
Most of what I write these days is PHP and Perl, so I have been looking into IDEs that support that. I like the freeware-ish PHPEdit, but their release management is horrid, and each version seems to have different, but significant bugs, than the last. I was using a year-old version for quite a while, and it was 95% right. I don’t trust them to be able to support it in the future though. Editors are tough, for open source.
I also just bought the personal edition of ActiveState Komodo, and in the first 10 minutes of using it I found 2 bugs. Over the next few days, I’ve hit two distinct, important bugs–one is a crash, the other is a weird some-keys-no-longer-work thing. Also, it’s still pretty slow–faster than the previous releases, but 2.5 is still a bit of a dog. I don’t know if that NSPR library is really the right choice, but it would be comfortable to have it on both Linux and Windows. Right now I’m really just interested in Windows. The quality so far is making me wonder if anyone at ActiveState actually uses Komodo for coding tasks…! I’m hoping to get some use out of the Perl, XSLT and Python dev modes though, they look promising.

Revision control
For now, I’m a cvs man. I’m playing with Subversion for a new mini-project, I love the atomic aggregate commits (you know them as ‘changesets’), but the tool still seems a little rough around the edges, and the biggest thing is how happy I’ve become on TortoiseCVS. I still use cvs command-line on linux a ton, but more and more of my coding is done in windows, and Tortoise just kicks ass. I think they should start charging for it It’s a shell extension that provides interface to CVS, and it’s really slick. Excellent piece of software, and they’ve just about got all the bugs worked out of it (for a long time it was in the “promising, but crashes explorer” phase). If they make a version of Tortoise that supports Subversion, I think I’m sold, that’s it, I’m done, source code control is a solved problem for me. (If you ever feel the need for entertainment, buy me a beer and I’ll tell you about my previous employer’s experiences with BitKeeper!)
I also use ViewCVS, and can’t live without CVSSpam (it’s in Ruby!).

Instant messaging
Trillian Pro. It rocks, especially now that it supports Jabber (which I honestly haven’t gotten working with it yet, but they claim it works.) Pro 2.0 adds nice polish. Seamlessly supports MSN, AOL, ICQ, Yahoo, and looks good doing it. That reminds me, I really need to start looking more seriously at the Jabber protocol/specs…

Password management
OK, I think there may be better (or cheaper) tools out there, but I use Eldos Keylord. It’s some shareware I bought for doing pw repository tasks. I keep the repository (apparently blowfish-encrypted based on the pw you enter every time you open or save), on my removable flashdisk key-thingamabob, so I always have it with me. It’s pretty small, and has some intelligent features, plus a pocketPC and palm version, if you’re into the PDA thing. A nice touch is that if you buy all 3 versions (it’s pretty cheap), you get the source… so you can audit it for security if you’re paranoid about that kind of thing. I love the “lock on minimize” and “timed minimize” features, just in case I forget to lock my notebook (ctrl-alt-del, enter) when I walk away.

Shell/misc
Gotta have Cygwin on windows–I can’t live without tail, grep, wget, rsync, etc. etc. from the command line. Also I bought PowerArchiver, a really nice windows zip tool. It’s not perfect, but really close, and understands .tgz .bz better than Winzip, it seems.

Web browser
Firebird all the way. I still use IE for final site testing and corporate Exchange webmail, but that’s about it. Firebird is just the cat’s meow. Must-have extensions: the Live HTTP Headers, Venkman JS debugger (amazing), and the Web Developer toolbar. These extensions, plus Firebird, make my life so much easier it’s ridiculous. I can’t honestly think of doing web development (especially the front-side stuff) without them.

RSS Aggregator
I used to be a big fan of Dare Obasanjo’s RSS Bandit, but now I’m leaning towards the web-based Bloglines. More on this as I get used to Bloglines… it’s just a huge hassle to only have one place with my feeds. The web is the obvious location for this kind of app, though, IMO. When they start charging for it (inevitable) I’m sure lots of imitators, probably even sourceforge projects, will pop up to duplicate it–it’s pretty simple. But a good idea.

Bug tracking
Bugzilla. I know it’s old, creaky, and the code isn’t great. But it works, and has a zillion features, and is free. You can’t beat that. Fairly easy to integrate into CVS with a little scripting, or do anything else you want with it… and scales to large projects and teams pretty nicely.

Remote access
ssh on windows: Well, I advocate other folks use PuTTY, because it’s free and good (and the key agent can be utilized for TortoiseCVS, which is sweet), but I usually use SecureCRT. Partially because I’ve paid for it, but also because I’m so comfortable with it, it’s copy-and-paste semantics, etc. It’s pretty solid, and pricey for a single home user compared to PuTTY, but I like it.
scp on windows: WinSCP, it rocks. And faster than the cygwin tools…
FTP on windows: I don’t do much of this. When I do, I use SecureFX (because I bought a license along with my SecureCRT one), but I don’t really like it. When I’ve been forced to do a bunch of ftp from windows, I’ve used LeechFTP, but it’s not being maintained and has some weird bugs that can corrupt transfers, it seems.
Xwindows on windows: I am a big fan of XWin32, although honestly I don’t have a legit license for it, and have stopped using it. I find it’s not really necessary, just nice to have. The cygwin xfree32 stuff will sort of do the job too, but it’s really painful–XWin32 is much nicer. But not nice enough to justify the cost, for me personally. If I can get an employer to pick up the tab, I’ll run it.

Database & other design stuff
Of course for SQL server, I use their tools; mostly Query Analyzer, less so Enterprise Manager (can do a lot of the DBA stuff from QA by calling stored procs, anyhow.) For Oracle work (I don’t do much of it…) I use an ancient copy of FreeTOAD. For mysql, I have tried a ton of GUI tools, and decided actually, I like phpMyAdmin (web-based) the best! They just continue to pile on the features for it, and it actually is really fast to use once you’re used to it, and it’s running on a box on the LAN.
For DB design, I like DeZign, although I’ve never bought enough copies for all the people that should really have it.
I’ve played with Rational Rose and a couple other UML tools, Visio was my favorite of the bunch, but basically all of them were too expensive/too formal/too restrictive for me. I am not a huge UML guy at the moment. We need the depth of thinking that is enforced by these tools, but I’m not convinced the notation itself is necessary to actually get most things done, or even the best way to communicate it, although a shared nomenclature for expressing functionality and interactions is a good goal. Most of the apps I’ve devloped didn’t require that level of “interaction management” if you will, though. <soapbox>We were usually successful with: Get a bunch of smart people together; hash it out on the whiteboard; everyone break for a few hours or days to have a good think, and while the project architect drafts up a proposal (and thinks about/creates a small prototypes); return to the discussion with proposal and clear heads, and repeat steps as necessary. At the end of that, document the decisions. You’ll almost certainly have a good solution, if you have quality people. </soapbox>

Project management and related
I’m forced to use MS Project, but I really don’t like it. It was a bastard stepchild for the longest time, with no updates from Project 98, and now the new version is bloated and heavy. I’ve actually started just using Excel and a few small macros for sorting, etc. I’m in search of a good web-based on, the most promising seems to be dotProject, but I’m not convinced about it either, yet. I’ve found a bunch of bugs, and the dev team seems a little on the green side. But, they are making progress, so I hope it becomes really usable soon… I’m unlikely to advise any org I work for to fork out the bucks required to run Project with ProjectServer, etc…!
PDF Generation: I’m actually using the free PDFCreator at work, due to lack of enough Acrobat licenses. It’s good enough for translating stuff to PDF; no editing, etc. For handing off specs, or excel files with data I don’t want messed with, it’s nice. Again, can’t beat the price.
Group documentation: TWiki. I don’t love it, but it works. I wish it worked properly with JGraph, which rocks.

Sysadmin/IT type stuff
I won’t get into all the junk we have installed, but here’s the ones that I actually “use” every day:
Performance monitoring: Cacti is the way to go for me. Small, free, decent, in PHP (hackable). Scales reasonably well…
System monitoring: Nagios with a bunch of custom plugins for application monitoring. Everything you need, but not much more. Scales well and the config language is worlds better than BigBrother.

Miscellaneous/Trivialities/Home-use stuff
Souds: Winamp lately, and when I’m entertaining, run MilkDrop on the projector for the Ibiza-club-in-your-livingroom vibe I even have a MusicMatch license, and use Winamp, so whatever that says…
CDR: Nero. It came with my drive, and I’ve been happy with it, it just works.
Graphics: I’m no pro–but my wife has legit copies of Illustrator, Photoshop and InDesign–so on the rare occasion I need to toy with that kind of thing, I use this stuff on her PowerBook. Whee.
DVDs: WinDVD. The only thing that seems to work properly with my SP/DIF soundcard and both my LCD monitor and projector.
Remote control: uICE. It rocks with the remote that came with my soundcard (ATI remote wonder thingy)

Hmm, that’s about it I guess. Anyone have suggestions? Comments? Pointers to other useful tools I might need? I accomplish all the types of tasks I need to do, with the stuff above.

Tags: apple, engineering, Firefox, linux, software

One of my developers asked how to get a .sh file to be runnable from windows today. I told him it should probably just work, with Cygwin… but apparently, it doesn’t. He went to lunch, so I gave it a go… my first attempt at making the file association through the GUI wasn’t fruitful, so Googling got me on the right track. Actually, the second bit of info was most elegant, and shows how to make file associations from the windows command prompt… slick. Here it is: (idea stolen from here)…

To make cygwin able to run “.sh” shell scripts (like .bat batch files), automatically, you need to do this on the machine:

C:\> assoc .sh=bash
C:\> ftype bash=c:\cygwin\bin\bash.exe %1 %*

I never knew about the ‘assoc’ and ‘ftype’ commands at the cmd.exe prompt before. Cool!
You can then double-click on .sh files and they’ll run properly (assuming of course you have cygwin installed in c:\cygwin… but this is where it is on our Copenhagen servers…)

Tags: microsoft, software

I must admit, there are some cool features in SQL Server 2000. It’s fast, has OLAP tools, at least one (of 3) functioning replication system, and the usual froofy stuff like stored proc’s and cascading triggers. One thing I didn’t realize it had until today, was “linked tables”. It does, and it works. Unfortunately, the MySQL ODBC driver, MyODBC (oh, excuse me, now it’s “Connector/ODBC” or some such nonsense) isn’t as full-featured as it could be. This caused me some unnecessary grief today.

Here’s what I needed to do: when someone gets an account created on the primary website (ASP, Win2K, MSSQL, they get access also to a secondary website (Apache/PHP/Mysql). How to do this, with low latency… and for ’special accesses’. That is, on the 2nd site, we serve large files that should only be available to registered users–but these are not controlled by a script in front of them (I suppose we could do that, but I’d really prefer not to, for logging and performance reasons, plus that later I might make that impossible for reasons too long to explain here.) Ideally, I want to use regular HTTP basic auth to protect this content. No problem, except how do we get the credential information where Apache can check it?
Best-case scenario, we’d use something like mod_auth_mssql. There is such a module, or at least I think there is, but of course, it seems to have dropped off the face of the earth now; and definitely, nothing like that is listed as working with Apache 2. We could use mod_auth_* — any number of alternate, available ‘backends’, but the issue is, how to replicate the data from the main server? Poll the main server for new records, and duplicate them into BDB, or mysql? That would work, but seems horrendously inefficient. Ideally, as soon as a row is inserted into the main MSSQL server granting auth, the backend on the second site gets it too; but don’t want to modify any code on the frontend (and besides, it’s really a stored procedure that does the dirty work.) Wouldn’t it be cool if we could insert the data into mysql or similar, when the data is updated, automatically? A ha, let’s throw a trigger on the table to start the process–great. Now, how to get the data over to the other server? T/SQL only lets you do so much (maybe it can be extended with user DLLs as UDFs, I don’t know… and didn’t feel like waiting forever for Visual Studio to fire up, so bypassed that concept for a start). I thought if I could do a REST data pass, or even SOAP call to the other site in the trigger, that would be good, and have the added benefit of showing up in the logs. If that’s possible, I couldn’t figure out how. On to the next idea… it’d be cool if we could connect to the mysql server from the SQL server via ODBC, and drop the data in that way. Wait, you can do that, via “linked tables”! Excellent. So how do you set those puppies up? Well, it’s as usual, a lot of fun. Not difficult once you know how, but painful figuring it out. Here’s what I ended up with:
1. Install MyODBC on the SQL server box
2. Create a system DSN on the SQL server machine, appropriately configured to point at the Mysql server.
3. Add the linked server in Query Analyzer:
EXEC sp_addlinkedserver @server=’pstars_mysql’, @srvproduct=’MySQL’, @provider=’MSDASQL’, @datasrc=’pstars’
Where @server is your mnemonic name, provider is magic (this is the constant for ODBC conns), and datasrc is the name of the system DSN.
4. Map the users and passwords from SQL credentials -> mysql credentials. I don’t think this is absolutely necessary (the exact params I show below, there are ‘NULL’ equivalents), due to the DSN setup, but doesn’t hurt to be explicit:
EXEC sp_addlinkedsrvlogin @rmtsrvname=’pstars_mysql’, @useself=’false’, @locallogin=NULL, @rmtuser=’mssql2k’, @rmtpassword=’mssql2k’
This says ‘forget the credentials of the connection I have, and use the rmtuser and rmtpassword’ instead. This is a server on our backend, isolated network, behind the firewall etc., but you might want better passwords on yours
5. See if it worked… EXEC sp_helplinkedsrvlogin @rmtsrvname=’pstars_mysql’. Get a display confirming it’s there. Whoo hoo.
Well, I thought, that’s the hard part–now my data’s just a query away, right? Wrong. Well, actually, right if you use a ODBC driver that actually supports everything. Wrong if you’re using MyODBC. What is missing, in particular? The ability to specify ‘catalogs’ and/or ’schemas’. Not sure which is really the problem–you’d think MyODBC would silently ignore what it doesn’t support (schemas, I guess–catalogs are roughly analogous to databases in Mysql, I think.) For example, it’d be nice to do:

SELECT * FROM pstars_mysql.pstars..test
or
SELECT * FROM pstars_mysql.pstars.someuser.test

or similar. Nope, can’t do that–mysql doesn’t expose the proper interface for that kind of 4-part specified table. other people were having the same problem, without any forthcoming answers. So how to do it? Well, there IS a workaround. The catch is, you can’t use the comfortable syntax. Instead it looks like this:

SELECT * FROM OPENQUERY(pstars_mysql, ‘SELECT * FROM TEST’)

Oh yeah, that’s pretty. At least it’s somewhat readable. But what if you want to do INSERTs? That’s kind of the whole point for my trigger to push the change to mysql. My first attempt:

SELECT * FROM OPENQUERY(pstars_mysql, ‘INSERT INTO test (id, line) VALUES (6, “some text”)’)

gave an error, but actually DID insert the data. Cool, I can live with the error!
Or not. If you get this type of error, the trigger fails, and the update/insert is aborted. Grrrr.

30 minutes of googling and MSDN reading later, I’m on to something promising; and finally arrive at:

INSERT INTO OPENQUERY(pstars_mysql, ‘SELECT id,line FROM test WHERE id<0′) (id, line) VALUES (8, ‘blahh’)

and it actually works, no error. Perfect. but what’s up with that? Why does this work? It’s just plain odd. The where clause is added so it doesn’t pull all the rows–to throw them out–and anything less than selecting a resultset with both columns, makes the query fail. Strange. But it works, so cool. It’s also nice because if the 2nd update does fail (mysql went away, ??), it can fail the primary update, which is actually desirable. Mini-distributed transaction, nifty. (You can do real distributed transactions, too! Just wrap as appropriate. Neato.) In reality, it’s a pretty slick solution.

Anyhow, all of that, plus mod_auth_mysql, and we’re in like flynn finally. But I gotta admit, MSSQL does surprise me with nice stuff sometimes. I wish the MyODBC driver was a little more refined though, being able to do direct queries and joins, etc., without the OPENQUERY nonsense would be really nice… so today’s score: Microsoft SQL Server: 1, MySQL: 0