Welcome!

Linux Containers Authors: Elizabeth White, XebiaLabs Blog, Aater Suleman, Dan Blacharski, Yeshim Deniz

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

@ThingsExpo Stories
DevOps is often described as a combination of technology and culture. Without both, DevOps isn't complete. However, applying the culture to outdated technology is a recipe for disaster; as response times grow and connections between teams are delayed by technology, the culture will die. A Nutanix Enterprise Cloud has many benefits that provide the needed base for a true DevOps paradigm.
Cybersecurity is a critical component of software development in many industries including medical devices. However, code is not always written to be robust or secure from the unknown or the unexpected. This gap can make medical devices susceptible to cybersecurity attacks ranging from compromised personal health information to life-sustaining treatment. In his session at @ThingsExpo, Clark Fortney, Software Engineer at Battelle, will discuss how programming oversight using key methods can incre...
Most technology leaders, contemporary and from the hardware era, are reshaping their businesses to do software in the hope of capturing value in IoT. Although IoT is relatively new in the market, it has already gone through many promotional terms such as IoE, IoX, SDX, Edge/Fog, Mist Compute, etc. Ultimately, irrespective of the name, it is about deriving value from independent software assets participating in an ecosystem as one comprehensive solution.
The 20th International Cloud Expo has announced that its Call for Papers is open. Cloud Expo, to be held June 6-8, 2017, at the Javits Center in New York City, brings together Cloud Computing, Big Data, Internet of Things, DevOps, Containers, Microservices and WebRTC to one location. With cloud computing driving a higher percentage of enterprise IT budgets every year, it becomes increasingly important to plant your flag in this fast-expanding business opportunity. Submit your speaking proposal ...
New competitors, disruptive technologies, and growing expectations are pushing every business to both adopt and deliver new digital services. This ‘Digital Transformation’ demands rapid delivery and continuous iteration of new competitive services via multiple channels, which in turn demands new service delivery techniques – including DevOps. In this power panel at @DevOpsSummit 20th Cloud Expo, moderated by DevOps Conference Co-Chair Andi Mann, panelists will examine how DevOps helps to meet th...
SYS-CON Events announced today that Juniper Networks (NYSE: JNPR), an industry leader in automated, scalable and secure networks, will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Juniper Networks challenges the status quo with products, solutions and services that transform the economics of networking. The company co-innovates with customers and partners to deliver automated, scalable and secure network...
SYS-CON Events announced today that Hitachi, the leading provider the Internet of Things and Digital Transformation, will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Hitachi Data Systems, a wholly owned subsidiary of Hitachi, Ltd., offers an integrated portfolio of services and solutions that enable digital transformation through enhanced data management, governance, mobility and analytics. We help globa...
SYS-CON Events announced today that T-Mobile will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. As America's Un-carrier, T-Mobile US, Inc., is redefining the way consumers and businesses buy wireless services through leading product and service innovation. The Company's advanced nationwide 4G LTE network delivers outstanding wireless experiences to 67.4 million customers who are unwilling to compromise on ...
Five years ago development was seen as a dead-end career, now it’s anything but – with an explosion in mobile and IoT initiatives increasing the demand for skilled engineers. But apart from having a ready supply of great coders, what constitutes true ‘DevOps Royalty’? It’ll be the ability to craft resilient architectures, supportability, security everywhere across the software lifecycle. In his keynote at @DevOpsSummit at 20th Cloud Expo, Jeffrey Scheaffer, GM and SVP, Continuous Delivery Busine...
20th Cloud Expo, taking place June 6-8, 2017, at the Javits Center in New York City, NY, will feature technical sessions from a rock star conference faculty and the leading industry players in the world. Cloud computing is now being embraced by a majority of enterprises of all sizes. Yesterday's debate about public vs. private has transformed into the reality of hybrid cloud: a recent survey shows that 74% of enterprises have a hybrid cloud strategy.
With major technology companies and startups seriously embracing IoT strategies, now is the perfect time to attend @ThingsExpo 2016 in New York. Learn what is going on, contribute to the discussions, and ensure that your enterprise is as "IoT-Ready" as it can be! Internet of @ThingsExpo, taking place June 6-8, 2017, at the Javits Center in New York City, New York, is co-located with 20th Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry p...
With major technology companies and startups seriously embracing Cloud strategies, now is the perfect time to attend @CloudExpo | @ThingsExpo, June 6-8, 2017, at the Javits Center in New York City, NY and October 31 - November 2, 2017, Santa Clara Convention Center, CA. Learn what is going on, contribute to the discussions, and ensure that your enterprise is on the right path to Digital Transformation.
SYS-CON Events announced today that SoftLayer, an IBM Company, has been named “Gold Sponsor” of SYS-CON's 18th Cloud Expo, which will take place on June 7-9, 2016, at the Javits Center in New York, New York. SoftLayer, an IBM Company, provides cloud infrastructure as a service from a growing number of data centers and network points of presence around the world. SoftLayer’s customers range from Web startups to global enterprises.
SYS-CON Events announced today that Hitachi, the leading provider the Internet of Things and Digital Transformation, will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Hitachi Data Systems, a wholly owned subsidiary of Hitachi, Ltd., offers an integrated portfolio of services and solutions that enable digital transformation through enhanced data management, governance, mobility and analytics. We help globa...
NHK, Japan Broadcasting, will feature the upcoming @ThingsExpo Silicon Valley in a special 'Internet of Things' and smart technology documentary that will be filmed on the expo floor between November 3 to 5, 2015, in Santa Clara. NHK is the sole public TV network in Japan equivalent to the BBC in the UK and the largest in Asia with many award-winning science and technology programs. Japanese TV is producing a documentary about IoT and Smart technology and will be covering @ThingsExpo Silicon Val...
The age of Digital Disruption is evolving into the next era – Digital Cohesion, an age in which applications securely self-assemble and deliver predictive services that continuously adapt to user behavior. Information from devices, sensors and applications around us will drive services seamlessly across mobile and fixed devices/infrastructure. This evolution is happening now in software defined services and secure networking. Four key drivers – Performance, Economics, Interoperability and Trust ...
SYS-CON Events announced today that Hitachi Data Systems, a wholly owned subsidiary of Hitachi LTD., will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City. Hitachi Data Systems (HDS) will be featuring the Hitachi Content Platform (HCP) portfolio. This is the industry’s only offering that allows organizations to bring together object storage, file sync and share, cloud storage gateways, and sophisticated search an...
SYS-CON Events announced today that CollabNet, a global leader in enterprise software development, release automation and DevOps solutions, will be a Bronze Sponsor of SYS-CON's 20th International Cloud Expo®, taking place from June 6-8, 2017, at the Javits Center in New York City, NY. CollabNet offers a broad range of solutions with the mission of helping modern organizations deliver quality software at speed. The company’s latest innovation, the DevOps Lifecycle Manager (DLM), supports Value S...
@GonzalezCarmen has been ranked the Number One Influencer and @ThingsExpo has been named the Number One Brand in the “M2M 2016: Top 100 Influencers and Brands” by Analytic. Onalytica analyzed tweets over the last 6 months mentioning the keywords M2M OR “Machine to Machine.” They then identified the top 100 most influential brands and individuals leading the discussion on Twitter.
SYS-CON Events announced today that Super Micro Computer, Inc., a global leader in compute, storage and networking technologies, will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Supermicro (NASDAQ: SMCI), the leading innovator in high-performance, high-efficiency server technology, is a premier provider of advanced server Building Block Solutions® for Data Center, Cloud Computing, Enterprise IT, Hadoop/...