Welcome!

Linux Containers Authors: Yeshim Deniz, Elizabeth White, Pat Romanski, Liz McMillan, Zakia Bouachraoui

Related Topics: Linux Containers

Linux Containers: 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

IoT & Smart Cities Stories
"Space Monkey by Vivent Smart Home is a product that is a distributed cloud-based edge storage network. Vivent Smart Home, our parent company, is a smart home provider that places a lot of hard drives across homes in North America," explained JT Olds, Director of Engineering, and Brandon Crowfeather, Product Manager, at Vivint Smart Home, in this SYS-CON.tv interview at @ThingsExpo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
DXWordEXPO New York 2018, colocated with CloudEXPO New York 2018 will be held November 11-13, 2018, in New York City and will bring together Cloud Computing, FinTech and Blockchain, Digital Transformation, Big Data, Internet of Things, DevOps, AI, Machine Learning and WebRTC to one location.
The current age of digital transformation means that IT organizations must adapt their toolset to cover all digital experiences, beyond just the end users’. Today’s businesses can no longer focus solely on the digital interactions they manage with employees or customers; they must now contend with non-traditional factors. Whether it's the power of brand to make or break a company, the need to monitor across all locations 24/7, or the ability to proactively resolve issues, companies must adapt to...
DXWorldEXPO LLC announced today that ICC-USA, a computer systems integrator and server manufacturing company focused on developing products and product appliances, will exhibit at the 22nd International CloudEXPO | DXWorldEXPO. DXWordEXPO New York 2018, colocated with CloudEXPO New York 2018 will be held November 11-13, 2018, in New York City. ICC is a computer systems integrator and server manufacturing company focused on developing products and product appliances to meet a wide range of ...
René Bostic is the Technical VP of the IBM Cloud Unit in North America. Enjoying her career with IBM during the modern millennial technological era, she is an expert in cloud computing, DevOps and emerging cloud technologies such as Blockchain. Her strengths and core competencies include a proven record of accomplishments in consensus building at all levels to assess, plan, and implement enterprise and cloud computing solutions. René is a member of the Society of Women Engineers (SWE) and a m...
@DevOpsSummit at Cloud Expo, taking place November 12-13 in New York City, NY, is co-located with 22nd international CloudEXPO | first international DXWorldEXPO and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. The widespread success of cloud computing is driving the DevOps revolution in enterprise IT. Now as never before, development teams must communicate and collaborate in a dynamic, 24/7/365 environment. There is no time t...
Founded in 2000, Chetu Inc. is a global provider of customized software development solutions and IT staff augmentation services for software technology providers. By providing clients with unparalleled niche technology expertise and industry experience, Chetu has become the premiere long-term, back-end software development partner for start-ups, SMBs, and Fortune 500 companies. Chetu is headquartered in Plantation, Florida, with thirteen offices throughout the U.S. and abroad.
DXWorldEXPO | CloudEXPO are the world's most influential, independent events where Cloud Computing was coined and where technology buyers and vendors meet to experience and discuss the big picture of Digital Transformation and all of the strategies, tactics, and tools they need to realize their goals. Sponsors of DXWorldEXPO | CloudEXPO benefit from unmatched branding, profile building and lead generation opportunities.
CloudEXPO New York 2018, colocated with DXWorldEXPO New York 2018 will be held November 11-13, 2018, in New York City and will bring together Cloud Computing, FinTech and Blockchain, Digital Transformation, Big Data, Internet of Things, DevOps, AI, Machine Learning and WebRTC to one location.
Disruption, Innovation, Artificial Intelligence and Machine Learning, Leadership and Management hear these words all day every day... lofty goals but how do we make it real? Add to that, that simply put, people don't like change. But what if we could implement and utilize these enterprise tools in a fast and "Non-Disruptive" way, enabling us to glean insights about our business, identify and reduce exposure, risk and liability, and secure business continuity?