11 million installations can't be wrong

MySQL Journal

Subscribe to MySQL Journal: eMailAlertsEmail Alerts newslettersWeekly Newsletters
Get MySQL Journal: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn


MySQL Journal Authors: Dynatrace Blog, Mamoon Yunus, Mat Rider, Progress Blog, Cloud Best Practices Network

Related Topics: MySQL Journal

MySQL Journal: Article

Trail of tears: MySQL, ODBC & OpenOffice 1.0

How to make MySQL, ODBC & OpenOffice share their toys at playtime

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.

OpenOffice 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.

More Stories By Joe Barr

Joe Barr is a freelance journalist covering Linux, open source and network security. His 'Version Control' column has been a regular feature of Linux.SYS-CON.com since its inception. As far as we know, he is the only living journalist whose works have appeared both in phrack, the legendary underground zine, and IBM Personal Systems Magazine.

Comments (6) View Comments

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


Most Recent Comments
Mark 06/19/08 09:29:45 AM EDT

My repairs were apparently less serious but greatly helped by this article. I have SUSE 10.3. I found that the libraries were installed to a different place, so I simply modified the odbcinst.ini file to reflect that they lived in: /usr/lib/unixODBC instead of the assumed location...

Brendan 05/01/04 09:20:27 AM EDT

You Forgot to note that making the unixodbc files would take an eternity.

Terry Cole 08/25/03 06:57:31 AM EDT

"How to make MySQL, ODBC & OpenOffice share their toys at playtime" accurately reflected the difficulties I had. But there is light at the end of the tunnel: Mr McCreesh has updated his how-to to cover Red Hat 9.

(http://www.unixodbc.org/doc/OOoMySQL9.pdf)

It now works, starting with a clean "workstation" install.

A good follow-up would look at the GUI tools ODBCConfig, DataManager, and DataManagerII (in beta but included in the RH9 RPMs).

K_aneda 07/31/03 05:26:24 PM EDT

Adds a whole new meaning to being politically correct. It's very hard to create a headline without annoying someone, considering what every country is done. Personally, MySQL, ODBC and OpenOffice on Linux COULD be a trail of tears for the technician, so I can see where he used that header. Bleh.

Bill 07/28/03 09:04:48 PM EDT

I admire your concern for the Native Americans, and atrocities such as those that you mentioned should not be forgotten or trivialized. However, I am puzzled by your objection. “Trail of tears” phrase is a universal phrase, much like “trial by fire”, etc. Yours is PC gone overboard.

Anonymous 07/28/03 12:29:30 PM EDT

Hi Joe,

I'm only a tiny bit American Indian (aka Native American) but when I was little I used to read a lot of books about "Cowboys and Indians." Each side in this long-running conflict had good people and bad people, and each did very bad things, but the Trail of Tears was one of the few times in American history when we as a nation deliberately hurt many innocent people so badly that many of them died. Many Indians died during that forced march and it's called the trail of tears for unjust and unwarranted cruelty that was thrust upon them.

http://www.google.com/search?q=trail%20of%20tears&ie=UTF-8&oe=UTF-8

I think that referring to your trials and frustrations with MySQL, ODBC, and OpenOffice as a "trail of tears" diminishes the sorrow these people when through 150 years ago. It'd be almost as bad as saying "the Holocaust: MySQL, ODBC, OpenOffice."

Like I said, I don't even personally know any Native Americans, but the title kind of hit me the wrong way. Excellent article otherwise.

-Jamie