MYSQL & ODBC Connections

A client needed to have all of his employees be able to access a company database containing client and job information. He originally wanted us to purchase and install Microsoft Access. This would have cost approximately $1600 by the time all of the software and licenses were purchased. Instead we offered to find an alternate solution using Open Office witha MYSQL database. Long story short, we could connect to the databse but not edit the tables. After trying multiple connections with no luck, we tabled the project thinking that there must be a bug in the system.

Today we downloaded the latest version of the connector:

http://dev.mysql.com/downloads/connector/odbc/3.51.html

Unfortunately, we encountered the same problem as before. We can connect to the database, see the information, but cannot edit the tables.

Since I have absolutely no knowledge about these databses or connectors, however, I am a decent Googler. Here’s what I found at this wonderful site:

http://dba.openoffice.org/FAQ/specific.html#editmysqltable

I cannot edit a newly created table!
The first reason may be that the table was created without a primary key or a unique index. In this case open the table design for that specific table and select the columns which identify a row uniquely, open the context menu and select the entry “Primary key” and save the table. After that the table should be editable.

Second reason, the table privileges are not set right. This is the case when the table “Mysql.tables_priv” doesn’t contain the right privileges. To verify this, open a new query design and insert in the text view the statement “SELECT * FROM mysql.tables_priv” and switch the mode to native in toolbar. When executing this statement, you should see your table name with the necessary privileges. If your table name doesn’t appear in this list, you have to grant yourself the privileges. This isn’t done automatically. Open the context menu upon your tables entry on the left side in your data source browser and choose SQL. In the appearing window insert “GRANT ALL ON yourtablename TO yourusername” (find more about the GRANT syntax at the MySQL documentation site) and execute this statement. Now your table should be editable.
If you’re connecting to MySQL via JDBC, the useHostsInPrivileges=false option in the connection URL may be worth additional consideration. It tells MySQL to ignore the host which you are connecting from, when calculating your privileges.

Third reason, your driver which you are using isn’t up to date. In earlier releases of MyODBC we recognize that the privileges and the currently username are not correctly returned.

The very first reason was our Eureka! moment.