MySQL 32-bit ODBC Invalid Attribute String 64-bit Windows 7
Having just migrated my development PC to Windows 7 I’m slowly encountering perculiar issues as I setup all of the software and connections I used to have in Windows XP. This morning I was trying to write a Business Objects report against a MySQL database and because Desktop Intelligence is a 32-bit application if I want it to talk to MySQL I have to use the 32-bit driver.
I obtained the latest driver (5.1.7) from MySQL’s standard ODBC Connector page, installed it and added a System DSN without a hitch, the odd part came when Business Objects was returning only one row from a query that should return a couple of hundred. Having run the same query on XP (I’m parallel running now) I suspected that the problem must be with the ODBC configuration so I attempted to delete the DSN only to receive the “Invalid attribute string” error…
A little Googling later led me to MySQL Bug #56233, in the discussion Fred Zappert frames the simplest solution suggested which is to uninstall version 5.1.7 and install version 5.1.6 instead. Oddly MySQL don’t make it especially clear how to get hold of previous minor versions of the ODBC drivers but you can get it from here: mysql-connector-odbc-5.1.6-win32.msi
Alternatively you could always visit bisql.net’s Tools, Utilities and ODBC Drivers page where I have a link for 5.1.6 which I’ll keep in place until the next Windows 7 compatible driver is released.
Categories: Business Objects, MySQL, Windows Tags: 32-bit, 64-bit, bug, business objects, Database, DeskI, Desktop Intelligence, error, MySQL, ODBC, Win7, Windows 7
32-Bit ODBC Drivers in Windows Server 2008 R2
Lately I’ve been taking advantage of my MSDN subscription (thanks to Ken Simmons‘ competition last month) and playing with some new technologies, initially experimenting with Windows Server 2008 R2. If you hadn’t heard prior to launch period, Microsoft made 2008 R2 64-bit only – probably the right decision to make but whilst the software industry is still making a transition from 32-bit to 64-bit there are bound to be some niggles here and there.
Being a database guy, one of the issues I noticed right away was that the ODBC Data Source Administrator accessible via Control Panel / Administrative Tools is the 64-bit version and can only be used to setup connections for 64-bit ODBC drivers. Not only was the 64-bit version missing the Postgres driver I had just installed, there were no drivers at all other than SQL Server…
It turns out that there are are two, entirely identical ODBC tools and the one that most of us will end up using initially (unless we’re lucky enough to have an all 64-bit architecture) is kept in the basement that is the c:\Windows folder. The 32-bit ODBC Data Source Administrator can be found by going to the Start Menu, selecting Run and executing c:\Windows\SysWOW64\odbcad32.exe as follows…
Once launched, you’ll see a tool that appears to be identical in every way, except that the ‘missing’ ODBC drivers are now available…
Please note that you can’t run both 32-bit and 64-bit tools at the same time so please make sure you close the 64-bit one first, it’s also worth bearing in mind that if you’re running 32-bit applications they will probably be looking for 32-bit DSNs so even if you can get a 64-bit driver for your data source it doesn’t mean that it’s the right thing to do.
As much as I understand Microsoft’s decision to stop developing 32-bit operating systems, a little bit of a helping hand during the transition period would’ve been nice - perhaps a second shortcut in the Administrative tools folder and the ability to run them simultaneously? The whole thing seems like a confisuing mess in their own words…
The 32-bit version of the ODBC Administrator tool displays 32-bit system DSNs, 32-bit user DSNs, and 64-bit user DSNs. The 64-bit version of the ODBC Administrator tool displays 64-bit system DSNs, 32-bit user DSNs, and 64-bit user DSNs.
To maintain backward compatibility, no resolution for this problem is currently available… to work around this problem, use the appropriate version of the ODBC Administrator tool.
I’ve made my own shortcuts to the 32-bit version and if you’re like me and constantly diving in and out of ODBC Administrator then you’ll probably want to do the same.
Categories: Operating Systems, Windows Tags: 2008 R2, 32-bit, 64-bit, Database, Drivers, Microsoft, MSDN, ODBC, Windows, Windows Server
Creating Databases and Users in MySQL
Historically I’ve been a Microsoft SQL Server guy but I’ve been doing quite a bit of query & analysis work on MySQL lately, though I’ve never performed any serious MySQL DBA. Well, the other day I was asked by a friend how to create a user and allow read/write access to a newly created database and this had to be done in SQL (i.e. no GUI tools or PHPMyAdmin).
Since I was starting from scratch I thought I’d put together a little script to create the database, add some data, create a user, prove that the user had write access and then tidy up after myself (always good to do!).
CREATE DATABASE ash_db;
USE ash_db;
CREATE TABLE ash_tbl (id INT NOT NULL,name CHAR(50) NOT NULL);
INSERT INTO ash_tbl (id,name) VALUES(1,'Ash');
SELECT * FROM ash_tbl;
CREATE USER 'ash_user' IDENTIFIED BY 'ash_pass';
GRANT ALL PRIVILEGES ON ash_db.* TO ash_user;
-- LOGIN AS ash_user THEN EXECUTE THIS...
INSERT INTO ash_tbl (id,name) VALUES(2,'Burton');
-- THEN LOG OUT AND COME BACK HERE
SELECT * FROM ash_tbl;
DROP TABLE ash_tbl;
DROP USER ash_user;
USE information_schema;
DROP DATABASE ash_db;
CREATE USER 'ash_user' IDENTIFIED BY 'ash_pass';
GRANT ALL PRIVILEGES ON ash_db.* TO ash_user;
-- LOGIN AS ash_user THEN EXECUTE THIS...
INSERT INTO ash_tbl (id,name) VALUES(2,'Burton');
-- THEN LOG OUT AND COME BACK HERE
SELECT * FROM ash_tbl;
DROP TABLE ash_tbl;
DROP USER ash_user;
USE information_schema;
DROP DATABASE ash_db;
