Welcome!

Linux Containers Authors: Mehdi Daoudi, Elizabeth White, Liz McMillan, Yeshim Deniz, XebiaLabs Blog

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
To get the most out of their data, successful companies are not focusing on queries and data lakes, they are actively integrating analytics into their operations with a data-first application development approach. Real-time adjustments to improve revenues, reduce costs, or mitigate risk rely on applications that minimize latency on a variety of data sources. Jack Norris reviews best practices to show how companies develop, deploy, and dynamically update these applications and how this data-first...
Intelligent Automation is now one of the key business imperatives for CIOs and CISOs impacting all areas of business today. In his session at 21st Cloud Expo, Brian Boeggeman, VP Alliances & Partnerships at Ayehu, will talk about how business value is created and delivered through intelligent automation to today’s enterprises. The open ecosystem platform approach toward Intelligent Automation that Ayehu delivers to the market is core to enabling the creation of the self-driving enterprise.
In his session at @ThingsExpo, Dr. Robert Cohen, an economist and senior fellow at the Economic Strategy Institute, presented the findings of a series of six detailed case studies of how large corporations are implementing IoT. The session explored how IoT has improved their economic performance, had major impacts on business models and resulted in impressive ROIs. The companies covered span manufacturing and services firms. He also explored servicification, how manufacturing firms shift from se...
The question before companies today is not whether to become intelligent, it’s a question of how and how fast. The key is to adopt and deploy an intelligent application strategy while simultaneously preparing to scale that intelligence. In her session at 21st Cloud Expo, Sangeeta Chakraborty, Chief Customer Officer at Ayasdi, will provide a tactical framework to become a truly intelligent enterprise, including how to identify the right applications for AI, how to build a Center of Excellence to ...
SYS-CON Events announced today that Massive Networks will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Massive Networks mission is simple. To help your business operate seamlessly with fast, reliable, and secure internet and network solutions. Improve your customer's experience with outstanding connections to your cloud.
SYS-CON Events announced today that CAST Software will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. CAST was founded more than 25 years ago to make the invisible visible. Built around the idea that even the best analytics on the market still leave blind spots for technical teams looking to deliver better software and prevent outages, CAST provides the software intelligence that matter ...
Because IoT devices are deployed in mission-critical environments more than ever before, it’s increasingly imperative they be truly smart. IoT sensors simply stockpiling data isn’t useful. IoT must be artificially and naturally intelligent in order to provide more value In his session at @ThingsExpo, John Crupi, Vice President and Engineering System Architect at Greenwave Systems, will discuss how IoT artificial intelligence (AI) can be carried out via edge analytics and machine learning techn...
Everything run by electricity will eventually be connected to the Internet. Get ahead of the Internet of Things revolution and join Akvelon expert and IoT industry leader, Sergey Grebnov, in his session at @ThingsExpo, for an educational dive into the world of managing your home, workplace and all the devices they contain with the power of machine-based AI and intelligent Bot services for a completely streamlined experience.
SYS-CON Events announced today that Datera, that offers a radically new data management architecture, has been named "Exhibitor" of SYS-CON's 21st International Cloud Expo ®, which will take place on Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Datera is transforming the traditional datacenter model through modern cloud simplicity. The technology industry is at another major inflection point. The rise of mobile, the Internet of Things, data storage and Big...
In his session at @ThingsExpo, Arvind Radhakrishnen discussed how IoT offers new business models in banking and financial services organizations with the capability to revolutionize products, payments, channels, business processes and asset management built on strong architectural foundation. The following topics were covered: How IoT stands to impact various business parameters including customer experience, cost and risk management within BFS organizations.
SYS-CON Events announced today that CA Technologies has been named "Platinum Sponsor" of SYS-CON's 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. CA Technologies helps customers succeed in a future where every business - from apparel to energy - is being rewritten by software. From planning to development to management to security, CA creates software that fuels transformation for companies in the applic...
SYS-CON Events announced today that Pulzze Systems will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Pulzze Systems Inc, provides the software product "The Interactor" that uniquely simplifies building IoT, Web and Smart Enterprise Solutions. It is a Silicon Valley startup funded by US government agencies, NSF and DHS to bring innovative solutions to market.
From 2013, NTT Communications has been providing cPaaS service, SkyWay. Its customer’s expectations for leveraging WebRTC technology are not only typical real-time communication use cases such as Web conference, remote education, but also IoT use cases such as remote camera monitoring, smart-glass, and robotic. Because of this, NTT Communications has numerous IoT business use-cases that its customers are developing on top of PaaS. WebRTC will lead IoT businesses to be more innovative and address...
WebRTC is great technology to build your own communication tools. It will be even more exciting experience it with advanced devices, such as a 360 Camera, 360 microphone, and a depth sensor camera. In his session at @ThingsExpo, Masashi Ganeko, a manager at INFOCOM Corporation, will introduce two experimental projects from his team and what they learned from them. "Shotoku Tamago" uses the robot audition software HARK to track speakers in 360 video of a remote party. "Virtual Teleport" uses a...
When shopping for a new data processing platform for IoT solutions, many development teams want to be able to test-drive options before making a choice. Yet when evaluating an IoT solution, it’s simply not feasible to do so at scale with physical devices. Building a sensor simulator is the next best choice; however, generating a realistic simulation at very high TPS with ease of configurability is a formidable challenge. When dealing with multiple application or transport protocols, you would be...
SYS-CON Events announced today that CA Technologies has been named “Platinum Sponsor” of SYS-CON's 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. CA Technologies helps customers succeed in a future where every business – from apparel to energy – is being rewritten by software. From planning to development to management to security, CA creates software that fuels transformation for companies in the applic...
SYS-CON Events announced today that Elastifile will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Elastifile Cloud File System (ECFS) is software-defined data infrastructure designed for seamless and efficient management of dynamic workloads across heterogeneous environments. Elastifile provides the architecture needed to optimize your hybrid cloud environment, by facilitating efficient...
SYS-CON Events announced today that Golden Gate University will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Since 1901, non-profit Golden Gate University (GGU) has been helping adults achieve their professional goals by providing high quality, practice-based undergraduate and graduate educational programs in law, taxation, business and related professions. Many of its courses are taug...
Recently, WebRTC has a lot of eyes from market. The use cases of WebRTC are expanding - video chat, online education, online health care etc. Not only for human-to-human communication, but also IoT use cases such as machine to human use cases can be seen recently. One of the typical use-case is remote camera monitoring. With WebRTC, people can have interoperability and flexibility for deploying monitoring service. However, the benefit of WebRTC for IoT is not only its convenience and interopera...
When shopping for a new data processing platform for IoT solutions, many development teams want to be able to test-drive options before making a choice. Yet when evaluating an IoT solution, it’s simply not feasible to do so at scale with physical devices. Building a sensor simulator is the next best choice; however, generating a realistic simulation at very high TPS with ease of configurability is a formidable challenge. When dealing with multiple application or transport protocols, you would be...