ODBC Question

[BJZeak]

A non-profit association is using a mature UBUNTU server with an intranet web application which has a MYSQL database ... currently they are accessing the data with custom reports by doing a complete dump and complete reload into a MYSQL database running on a Windows machine. My expectation was that I could just create a ODBC connection to this database.

As this machine is live and in use, I created a vm of this server on my laptop ... while the web apps work with no problems using a bridged network from windows I am having problems connecting to this database using an ODBC connection.

Loaded MYSQL 5.3 ODBC drivers the Laptop. Set up the Connection with the server's IP address, port #, user name, user password, and DB name ... the Test Button is just returning the following error:

Connection Failed
MySql ODBC 5.3(a)Driver Lost Connection to MySql Server at 'reading initial communication packet',system error:0:

Any idea what I might be missing?

 

[jpadie]

Why use odbc to intermediate the connection? Why not just connect directly with native mysql drivers?

But one reason for using a snapshot is that sometimes analytics can be very taxing for the server and might slow down production operations. Similarly sometimes you want all the analytics to use the same dataset for consistency. On a production server there are likely to be non idempotent transactions taking place on the database between running one report and the next.

 

[BJZeak]

Thank-you for the response.

I found that the server was only set up for local host connections

After I commented out the bind-address = 127.0.0.1 in my.cnf and added a new SQL user as <user>@'%' the connection via ODBC now works properly.

2 stones killed.

 

[jpadie]

ish. you are still using odbc which is inherently limited in the way that it connects. you will find odd interaction with large datasets and potential difficulties in handling blobs. Further odbc provides a much poorer set of error handling than a native client.

given that there are native drivers and clients it really doesn't make sense to use ODBC imo.

 

[BJZeak]

jpadie,

This mySQL database is not huge, < 1G and only has a maximum of 2 intranet connections at any one time, plus it doesn't use blobs ... most activity from the webapp is during evenings and weekends ... the ODBC connection would be only used during normal weekday hours from one terminal ... so ... while I understand your concern "ish", in this situation, the expectation is this ODBC connection should more then meet our needs.

Sorry, although I have spent a few decades working with various RDB systems, I haven't spent much time in the SQL realm ... assuming a native client could be used to link to this mySQL database ... and ... assuming the current installation of mySQL will support this client without requiring any software updates ... how could one use this client to link a MS Access 2K10 FE Application to the mySQL data? ODBC links the tables directly in Access allowing it to select, add, and or update records in the linked tables. Would a native client connection provide the same options to Access or is a native client connection much like localhost:/myphpadmin in that it provides an IDE shell directly to the mysql database? We are currently using Access but there is no reason that at some point we couldn't switch to VB and or some other MS .net Front End. The goal is to consolidate data entry and reporting.

We have had several challanges:

1) there is an associated custom webapp which is undocumented and comes as is with no configuration details or source ... we have received several qoutes for a suitable replacement application but all are far beyond our means ... luckily this webapp still meets most of our needs

having said that ...

2) a recent hardware failure has shown us that the server hardware is running on borrowed time

3) the current Ubuntu server version 7.10 is no longer supported (it will no longer will run update or upgrade scripts) ... MySQL is also restricted from updates as Ubuntu 7.10 doesn't meet minimum update requirements ... this boils down to rebuilding the server from scratch with the latest versions ... unfortunately, I have had no success in getting the existing webapp to run in a current version of Ubuntu

4) reporting and operational updates are being done using manual double and sometimes tripple entry, so we were also exploring ways to sync the 2 databases (mySQL and Access) using a simpler method then dump and reload

Current Solutions:
1-2-3) To satisfy the first 3 concerns, I built a VM which runs the existing Server Image on a newer W7 Pro machine ... after a recent success with a functional Xenix VM, it was a walk in the park to build a Ununtu VM.

4) To satisfy the last concern I was able to connect Access to mySQL using an ODBC connection and as stated above, for this application, I believe that should more then meet our needs for the moment.

 

[jpadie]

if you are accessing the data through the access linked table snap-in then you have no choice but to use odbc.

to answer the earlier question - there are a bunch of client libraries available for mysql. i was thinking of the normal command line client since you said you needed to access reports. typically these would be generated by a script in php or python or whatever. all of which also support the mysql libraries.

if you are using access to do this then as above, you have to use odbc.