My friend Milt recently told me that he was having problems getting MySQL and OpenOffice.org 1.0 to play together nicely. Actually, "having problems" is something of an understatement.

Milt is not a newbie. He's been using personal computers since before IBM's entry in the market. He does his own networking. Through the years, he has made DOS and various flavors of Windows do everything but make coffee. But when it came to getting MySQL and OpenOffice connected with Linux (running SuSE), he was getting nowhere fast.

I started looking around for resources on the Internet that might ease his pain. I found a wonderful "how-to" piece called "OpenOffice.org 1.0, ODBC and MySQL," by John McCreesh. In the introduction, McCreesh writes about OpenOffice.org 1.0's "best kept secret" — that secret being the fact that hidden away inside, completely unknown to most OpenOffice users, is a user-friendly front end for databases that is "a Microsoft Access (and more) equivalent." That may be so, but there is a very good reason why it's a secret: it's too damn hard getting OpenOffice and ODBC wired up correctly.

As of this morning, I'm happy to report that I have finally accomplished the hard part. After nearly a week of endless frustrations, retries and reinstalls, I finally have OpenOffice, MySQL, and ODBC playing together nicely.

This week, I'll describe how I did it on my Red Hat 8.0 desktop machine. McCreesh's how-to was very helpful to me. But there are just enough differences between Red Hat 7.3 (the version upon which he wrote about wiring together OpenOffice and MySQL) and Red Hat 8.0 that I simply couldn't follow it blindly with success. New gotchas appeared to blend in with the old ones. If you're not running RH 8.0, there is a good chance the path I took won't work for you. Perhaps between this article and McCreesh's how-to, you can get it working on your setup.

Following the how-to, I began installing the needed packages. I grabbed several of them via Red Hat's up2date program because it takes care of the dependencies for me. The rest I got from the MySQL or the unixODBC sites (see Resources below for the links).

I got nothing but pain for my efforts. I reinstalled. I reconfigured. I moved things around and I tweaked ini files. I tried different versions of the MySQL server. And the client. Nothing worked. Most often, the failure came when I tried to use the newly installed unixODBC with the newly installed MySQL. Trying to keep a record of exactly what I did each time was a nightmare. Often it was easier to simply erase, remove, delete, and reinstall than figure it out. Finally, blind luck smiled on me and I succeeded.

Installing the MySQL server

Here is how I did it:

1. I added mysql to the groups associated with my user ID.
2. I used the following commands (as root) to find and remove any remnants from previous installation attempts:

   rpm -qa | grep -i mysql
    rpm -e (any packages found) 
    updatedb
    locate mysql
    rm all files and directories found
    locate odbc
    rm all files and directories found

3. I downloaded mysql-3.23.55-pc-linux-i686.tar.gz from the MySQL site into a directory called odbc in my home directory.
4. As root, I installed and started the server:

   cd /usr/local
    tar xzf /home/warthawg/odbc/mysql-3.23.55-pc-linux-i686.tar.gz
    ln -s mysql.3.23.55-pc mysql 
    cd mysql
    scripts/mysql_install_db
    chown -R root .
    chown -R mysql data
    chgrp -R mysql .
    bin/safe_mysqld --user=mysql &

To check things thus far, I followed the how-to's instructions and started a mysql session as user "test":

>mysql -utest
 Welcome to the MySQL monitor. Commands end with ; or \g. 
 Your MySQL connection id is 1 to server version: 3.23.55
 Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

Then I entered "select version();" at the prompt:

 mysql> select version();
 +-----------+
 | version() |
 +-----------+
 | 3.23.54  |
 +-----------+
 1 row in set (0.00 sec)

So far, so good. Now on to three other downloads from MySQL that are required for our purposes. Note that instead of the binary install MySQL provided for the server, these three items are all in RPM format, again courtesy of MySQL.

After downloading the RPMS into my odbc directory, it was as easy as:

su
 rpm -Uvh /home/warthawg/odbc/MySQL-client-3.23.55-1.i386.rpm
 rpm -Uvh /home/warthawg/odbc/MySQL-shared-3.23.51-1.i386.rpm
 rpm -Uvh /home/warthawg/odbc/MyODBC-2.50.39-1.i386.rpm

All three installed without a complaint.

unixODBC

The final piece of the puzzle was unixODBC itself. This time I built the executable myself. Here's how I tackled this last part after downloading unixODBC-2.2.4.tar.gz into my odbc directory from the unixODBC Web site.

 cd ~/odbc
 tar xzf unixODBC-2.2.4.tar.gz
 su
 ./configure
 make
 make install

Before proceeding, I verified that a couple of libraries existed. I had been stung before by the gotcha bug when one or the other of them had turned up missing. They are libodbcmyS.so and libmyodbc.so, and there they were, sitting fat and happy in /usr/local/lib.

I also knew from previous failed attempts that the default location for those files in the odbcinst.ini configuration file was /usr/lib. So I edited /etc/odbcinst.ini to look for them there. My odbcinst.ini looked like this:

[MySQL]
Description   = ODBC Driver for MySQL
Driver     = /usr/local/lib/libmyodbc.so
Setup      = /usr/local/lib/libodbcmyS.so
FileUsate    = 1
CPTimeout    =
CPReuse     =

Then I created /etc/odbc.ini by entering:

[MySQL-test]
Description   = MySQL database test
Driver     = MySQL
Server     = localhost
Database    = test
Port      = 3306
Socket     = 
Option     =
Stmt      =

On a hunch, I checked to see where unixODBC had set up the templates for the ini files. Sure enough, it wasn't in /etc, but in /usr/local/etc. I copied both of the ini files I had edited as shown above to that directory.

Now it was time for the final test. Would ODBC and MySQL make nice and talk to each other, or would this go down as yet another failed attempt? I was about to find out. From the command line, as a normal user, I typed isql MySQL-test -v.

MySQL and ODBC, living together in perfect harmony

Bummer. The infamous "Could not SQLConnect" error message again. However, because I had used the verbose option, it also explained that it couldn't find /var/lib/mysql/mysql.sock. Not only was this a new reason for my failure, it was also one I thought could be fixed — maybe a couple of different ways. Sure enough, I found that mySQL was sticking mysql.sock in the /tmp directory. ODBC was looking in /var/lib/mysql. No wonder they couldn't talk.

I started the workaround by creating a /var/lib/mySQL directory (as root, naturally) and then entering:

chown -R mysql.mysql /var/lib/mysql

Then I restarted the mysqld specifying both the user and the socket location as follows:

bin/safe_mysqld --user=mysql --socket=/var/lib/mysql/mysql.sock &

Then it was time to try it again. This time it worked! I was elated. And exhausted. Here's what it looked like when I finally got there:

/ isql MySQL-test -v
+---------------------------------------+
| Connected!              |
|                    |
| sql-statement             |
| help [tablename]           |
| quit                 |
|                    |
+---------------------------------------+
SQL> select version();
+----------+
| version()|
+----------+
| 3.23.55 |
+----------+
1 rows affected
1 rows returned

At this point, I wasn't going to take anything for granted, so I decided to see if OpenOffice itself could communicate with MySQL via ODBC. Following closely along in McCreesh's how-to, I started OO Writer and then selected Tools->Data Sources.

Bummer! Another error! This time, OpenOffice was complaining that it couldn't find libodbc.so. I believe the how-to covered this gotcha, so I simply copied libodbc.so from /usr/local/lib to /usr/lib. Now it worked. I followed the how-to far enough to discover that I could design MySQL tables from within OpenOffice, then stopped. I had climbed the mountain. The screen shot below shows OpenOffice's table-design window.

Looking back now, I can see I still have a bunch to learn about setting up the configuration files like my.cnf and the two ini's discussed earlier. Perhaps when I get those better tweaked, both server and clients will be looking in the same spot for needed resources.

Conflict of interest for MySQL AB?

Milt says all of this is unnecessarily difficult, and I agree. This is like Linux in the old days when only geeks and uber-dweebs used it regularly. Perhaps the OpenOffice-MySQL problem has its roots in the MySQL AB business model. MySQL AB gives code away as a calling card for its support services. It's easy to conclude that MySQL AB has little incentive to write great documentation or installation scripts, since doing so might cut into MySQL AB's revenue stream. On the other hand, if it weren't for the fact that MySQL has won so many awards, including five consecutive Linux Journal Readers Choice awards, one might wonder how good their product and consulting services really are given the struggle involved in getting MySQL to work with ODBC.

Next week, I'll report on my experiences actually using MySQL from within OpenOffice. Maybe by then I'll have a better idea of whether it was worth the sweat and tears.