A windsurfing, CSS-grudging, IE-hating, web-developing, gigantic-machine-puzzling blog

Category: perl

MS Access ODBC with Windows 7 64-bit and Perl

MS Access ODBC 64-bit Windows 7 driverJust writing that headline’s enough to make me cringe. Hell, I almost gave up at the thought of dealing with the ODBC error in the first place:

Can't connect to Access database: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (SQL-IM002) at [script name] line [line number].

But turns out it’s not so bad, just a little tricky.

An important detail: I’m using Strawberry Perl 64-bit. It’s v5.12.3 multi-thread, but that doesn’t really matter. 64-bit OS, 64-bit Perl, 64-bit ODBC driver … don’t mix 64s with 32s & everything is good in the world. If you’re trying to use some twisted combination, this blog post may not be quite as helpful. But who knows? You’ll find out soon enough.

First step is make sure the MS Access 64-bit ODBC driver is present & functioning: Administrative Tools > Data Sources (ODBC), which brings up the ODBC Administrator. Switch to the Drivers tab to view the installed 64-bit drivers.

(Note to 32-bit-driver-on-64-bit-OS-type people: to see your installed 32-bit ODBC drivers, you’ll need to run the 32-bit ODBC Administrator instead. Tricky or what?)

My copy of Windows 7 only had the SQL Server ODBC driver listed. Head over to Microsoft’s website to download the MS Access 64-bit ODBC driver.

Once that’s installed, pull up your Data Sources window again & a bunch of new drivers will be listed on the Drivers tab. In my Perl program I’m using DBI, so I need the driver name exactly as it appears on the Drivers tab:

Microsoft Access Driver (*.mdb, *.accdb)

So in Perl-ese, that becomes:

use DBI;
my $dbh = DBI->connect('dbi:ODBC:driver=Microsoft Access Driver (*.mdb, *.accdb);dbq=[path to mdb file]') or die "\nCan't connect to Access database: " . $DBI::errstr;

Get the path right & it should work! Hope this helps someone.

Lost Connection To MySQL Server on MediaTemple Grid Server

I’ve been a MediaTemple Grid Server (gs) customer since it was released. From the beginning, Mediatemple has had MySQL problems with “Lost connection to MySQL server during query at [script name] [line number]” errors.

I have several system update Perl scripts for CarComplaints.com that I run as cron jobs or from the command line that take a few minutes to run. They access a MySQL database via the standard DBI Perl module. Maybe 1/3 of the time, the mysql connection is lost 2-3 minutes into the update.

I’ve ignored MediaTemple’s lost mysql connection problem for years. Lately though, the dropped connection errors have become more of a headache so I decided to do some troubleshooting.

I upgraded from the MediaTemple MySQL SmartPool to a MySQL Container & played around with the various mysql config timeout settings, but there was no change in when & how often the lost mysql connections occurred. Everything pointed to a connectivity issue with MediaTemple’s service.

Next move was to contact MediaTemple support about the issue. MediaTemple has a pretty good reputation for customer service. Wow. Turns out lost MySQL connections are their Achilles’ heel.

First they blamed slow mysql queries. I pointed out the scripts encountering the error are not public-facing, so 300,000 queries or 1 query that evaluates 300,000 rows in one shot makes no difference. All my slightly “slow queries” are for good reason — the tables have the proper indexes, etc.

Next they blamed cron job processing limitations. I reiterated that the lost mysql connections errors happen just as often when the script is run manually from the command prompt, & that the errors occur very inconsistently indicating a time/cpu limit is probably not involved.

Finally they combined both theories (!!) to suggest the lost mysql connections were “a problem with MySQL optimization” & also that I may be “overloading the GridContainer” by running scripts concurrently with cron jobs … definitely not happening.

And the kicker from MediaTemple support tech “Joel M.”:

Please note that (mt) Media Temple only supports the basic operation and uptime of your (gs) Application Container Technology.

… conveniently implying that lost mysql connections on the GridServer were not an issue of basic operation & uptime. I’d love to hear the logic behind that one.

At that point I decided my time would be better spent coding a way around MediaTemple’s Grid Server / MySQL / tech support shortcomings.

I wrote a MySQL reconnection handler so that it reconnects a few times before giving up. So far so good.

Here are more people experiencing this same problem with lost mysql connections on Mediatemple.

MySQL.com has some advice about “Lost connection to MySQL server” errors:

Usually it indicates network connectivity trouble and you should check the condition of your network if this error occurs frequently. If the error message includes “during query,” this is probably the case you are experiencing.

Prototype mismatch: sub main::head ($) vs none

Turns out that when you use the LWP::Simple & CGI modules together in a Perl script, each has a conflicting head() function that throws that error. Who knew? Everyone who took the time to read the CAVEAT note at the end of the LWP::Simple docs, apparently:

Note that if you are using both LWP::Simple and the very popular CGI.pm module, you may be importing a head function from each module, producing a warning like “Prototype mismatch: sub main::head ($) vs none”. Get around this problem by just not importing LWP::Simple’s head function, like so:

use LWP::Simple qw(!head);
use CGI qw(:standard);  # then only CGI.pm defines a head()

Then if you do need LWP::Simple’s head function, you can just call it as LWP::Simple::head($url).

Powered by WordPress & Theme by Anders Norén