Connect MySQL 3.23 with pyodbc 3.07

Question

I'm trying to connect to an old MySQL 3.23 server from an Ubuntu 16 client with UnixODBC and pyodbc 3.07. I've tried three (3) versions of MySQL Connector/ODBC and two (2) from MariaDB:

MySQL-ODBC 5.3.9 Supports only the new mysql authentication method. Therefore it can not connect.

MySQL-ODBC 5.1.13 Has a switch for the authentication method but tells me on pyodbc.connect(dsn): [MySQL][ODBC 5.1 Driver]Driver does not support server versions under 4.1.1

MySQL-ODBC 3.51 Has two issues:

1. Fails with [MySQL][ODBC 3.51 Driver]Transactions are not enabled (4000) (SQLSetConnnectAttr(SQL_ATTR_AUTOCOMMIT)) as pyodbc sets autocommit to false as a default.
2. Gives me a connection when I connect with pyodbc.connect(dsn, autocommit=True). The connection gives me a cursor but all cursor.execute(sql) throw the exception ('HY000', 'The driver did not supply an error!').

Testing the connection with isql from the shell via isql -v [dsn] gives me a session but fails on all statements with [ISQL]ERROR: Could not SQLExecute. So this seems to be a unixodbc problem.

I installed mysql-client. But the programm mysql fails to connect the server.

mariadb-client can connect to the database and even execute statements. That looks more promising.

I downloaded the MariaDB ODBC-Driver 3.0.2. Using that driver with isql returns the error: [S1000][unixODBC][ma-3.0.2]Plugin old_password could not be loaded: lib/mariadb/plugin/old_password.so: cannot open shared object file: No such file or directory. That is a response one could work with. There is an ODBC-Option PLUGIN_DIR but I don't know where to get the plugin.

MariaDB ODBC-Driver 2.0.13 gives me ('HY000', "[HY000] [unixODBC][ma-2.0.13]You have an error in your SQL syntax near 'SQL_AUTO_IS_NULL=0' at line 1 (1064) (SQLDriverConnect)") on connect. As there seems to be no option to change this. Deadend here.

I would like to know if there is a way to access this old MySql via unixodbc/pyodbc?

Or does somebody know where to get the Plugin old_password.so for MariaDB?

The mariadb-client installed via apt-get can connect so there has to be a way.

Answer 1

I spent a day or so poking at this, and don't think it's possible without significant alterations to driver code, or an extremely difficult-to-create build environment for old versions.

I am putting this in an answer so that other people don't fall down the same rabbit hole I did (or, better yet, so other people can pick up where I left off and actually fix the problem!) ...and it didn't fit in a comment.

This will be a bit of a tome, sorry.

Overview

I was able to reproduce each of the error conditions you mentioned in your post (thanks for a thorough and excellent question!) using a pair of Ubuntu 16.04 containers, the MySQL 3.23 download available from Oracle, and all of the client libs you mentioned, and a few others.

Below are what I found while trying to find additional solutions in each place you mentioned, followed by some "next steps"-type info and some proselytizing about the moral of the story.

All of these tests were conducted with the latest versions of Python 2, UnixODBC, and pyodbc (via pip) available for a stock Ubuntu 16.04 Docker container as of 26/11/2017.

All the URLs used are linked, but, if history is any indication, they may die as time goes on, considering that a lot of this software is verging on two decades old. I am also happy to post any/all of my shellscripts/Dockerfiles/modified driver sources if you like; just ping me in the comments.

old_password.so and MariaDB Connector/ODBC 3.0.2

You were right that this was the troubleshooting option with the most potential. Here's what I did:

First, I installed the Connector/ODBC 3.0.2 binary and attempted to connect to it via Python. I hit the same error you did after configuring my ODBC .ini files for a data source named "maria", namely:

> pyodbc.connect('DRIVER={maria};Server=mysql;Database=mysql;User=admin;Password=admin')
pyodbc.Error: ('HY000', u'[HY000] [unixODBC][ma-3.0.2]Plugin old_password could not be loaded: lib/mariadb/plugin/old_password.so: cannot open shared object file: No such file or directory (2059) (SQLDriverConnect)')

The ODBC code attempts, when presented with a MySQL server announcing an authentication protocol old enough, to load compiled plugins built for the Connector/C MariaDB driver. straceing the output of the ODBC connect attempts determined this.

old_password.so turns out to be a component of the Connector/C MariaDB driver, but is not a library included with that driver's binary releases. Interesting.

It turns out that there are a bunch of plugin modules similar to old_password included with the source of the Connector/C driver. I downloaded the Connector/C 3.0.2 sources and opened up the documentation, sources, and build system for those "auth"-type plugins, which were distributed as .so files, to see what I could find.

I found that various components of the Connector/C can be compiled either as plugins "statically" linked into the main driver library, or as dynamic libraries themselves. I say "statically" in quotes, because the build process for the C driver creates both a static (.a) and dynamic (.so) version of mariadbclient, but if a specific plugin is declared as static in the build system, that plugin's code is statically included in both mariadbclient artifacts.

The sources for the old_password.so file appeared to be in a single small source file at plugins/auth/old_password.c.

It seemed that it would be possible to change the build system (CMake) to generate a dynamic library for the old_password plugin. In the Connector/C sources there is a cmake/plugins.cmake file which acts as a "registry" for all plugins. It contains a cmake macro REGISTER_PLUGIN which takes a STATIC or DYNAMIC argument. I searched in that file for old_password and found the following line:

REGISTER_PLUGIN("AUTH_OLDPASSWORD" "${CC_SOURCE_DIR}/plugins/auth/old_password.c" "old_password_client_plugin" "STATIC" "" 0)

That looked promising. Modelling off of similar lines that did generate .so files for their plugin, I changed that line to the following and ran the build:

REGISTER_PLUGIN("AUTH_OLDPASSWORD" "${CC_SOURCE_DIR}/plugins/auth/old_password.c" "old_password_client_plugin" "DYNAMIC" "old_password" 1)

The build failed a few times due to missing dependencies. I had to install a few -dev packages and other tools, but in the end I was able to build cleanly (for just the plugins, it turns out you don't need CURL or OpenSSL). Sure enough, a file called mysql_old_password.so was created in the plugins/auth directory as a build artifact. - Now, I needed my Python code to find that plugin; it still gave me the error about failing to find lib/mariadb/plugin/old_password.so. I supplied the PLUGIN_DIR argument you mentioned in your question to the ODBC connection string, renamed my compiled mysql_old_password.so to old_password.so, and ran the following code . . . and got a new error! Progress!

conn = pyodbc.connect('DRIVER={maria};Server=mysql;Database=mysql;User=admin;Password=admin;PLUGIN_DIR=/home/mysql/zclient/mdb-c/plugins/auth')
pyodbc.Error: ('HY000', u'[HY000] [unixODBC][ma-3.0.2]Plugin old_password could not be loaded: /home/mysql/zclient/mdb-c/plugins/auth/old_password.so: undefined symbol: ma_scramble_323 (2059) (SQLDriverConnect)')

Looks like the compiled artifact is broken, missing the ma_scramble_323 function definition. Since the plugin is dynamically loaded at runtime, the program will still start, but when it tries to dload the plugin it'll blow up. Worse yet, that function looks like it's the main password-hashing entry point for the "old" MySQL protocol authentication mechanism, so I couldn't just ditch it. In the Connector/C sources, I found the declaration for that function and the header (mariadb_com.h), but includeing that in various places in the old_password.c source file didn't seem to do the trick. My hunch is that this is the interaction of two unfortunate behaviors. First, the plugins compiled by the Connector/C build system are set up assuming that they will only be linked by the Connector/C plugin, or something similar. This means that the plugins themselves don't link to the "common" Connector/C functionality when they are compiled, since that stuff should already be available in the thing loading the plugin. Since we're using Connector/ODBC, and not Connector/C, those common functions aren't present or accessible. Now, building Connector/ODBC from source requires Connector/C, so Iit might be possible to compile a new Connector/ODBC library in such a way that it includes the right functions, but I didn't want to start down that rabbit hole. Second, even when told to build the old_password plugin in standalone (don't compile anything else) mode, CMake's dependency analysis did not discover or link the files that described the ma_scramble_323. That might be a CMake problem, but it is probably because the build system is not configured with this use case in mind as mentioned above.

Here, I got very lucky. The ma_scramble_323 function is defined in libmariadb/ma_password.c, which is a very small, simple source file with no significant dependencies on any other libraries in the Connector/C project that were not already depended-on by the old_password plugin. I did "poor man's linking" (yuck) and just copied the sources of the ma_scramble_323 function into the old_password.c file. Those functions called other functions in the ma_password.c file, so I copied those to. Again, this was only easy (or an option at all) since the ma_password.c file was so simple. If it had itself had dependencies or had been more complex, I would have had to stop, drop, and learn advanced CMake-fu to resolve the issue the "right" way. I am absolutely sure there is a better way to do this.

(Aside) at this point I had to cron up a regular run of mysqladmin flush-hosts on my DB server since my testing was causing so many failed attempts that I had to do this frequently. There's probably a better way around this, too, but I don't know it and I know cron.

With the newly-"inlined" sources, the mysql_old_password.so library compiled, I renamed it, and ran my test script again. This time, I got:

pyodbc.Error: ('HY000', u'[HY000] [unixODBC][ma-3.0.2]Plugin old_password could not be loaded: name mismatch (2059) (SQLDriverConnect)')

I figured this had something to do with the fact I was renaming the file so that ODBC could find it (it's looking for old_password.so not mysql_old_password.so). I tried the shotgun approach. In the plugins/auth/CMakeLists.txt build system config, I replaced all instances of mysql_old_password with old_password and compiled. Compilation succeeded, but it still didn't work.

It turns out that the plugin sources themselves (old_password.c in this case) have a struct declaration at the top that announces their name, and this one announced its name as mysql_old_password. This may well be a pre-existing issue (I.e. this has never worked), and I started to feel a bit of a chill: when you're building code that feels like nobody has built it or tested it in a given configuration before, your odds of success are not good. Regardless, I did the same s/mysql_old_password/old_password/ on the source file as well, and compiled. This time it generated an artifact with the right old_password.so name. I ran my test script again and got:

conn = pyodbc.connect('DRIVER={maria};Server=mysql;Database=mysql;User=admin;Password=admin;PLUGIN_DIR=/home/mysql/zclient/mdb-c/plugins/auth')
pyodbc.Error: ('HY000', u"[HY000] [unixODBC][ma-3.0.2]Access denied for user: 'admin@hostname' (Using password: NO) (1045) (SQLDriverConnect)")

This was odd. I had the mysql commandline client that came with the 3.23 server installed (via tarball, not in the system library path) on my client-testing box as well, and it could connect fine with those credentials (I couldn't test with isql because I couldn't get it to properly use PLUGIN_DIR and couldn't figure out where it wanted me to put the plugins; it wasn't in the system /usr directory, nor the relative ones). I could not figure out a way through this. I had set up my MySQL server with all of the usual "ultra-promiscuous, testing only" GRANTs, for localhost and %, for every database, for the admin user and eponymous password.

I gave up and set the password to empty/null, disabling password auth, making sure I could still log in via mysql on the command line, and trying one last time:

pyodbc.Error: ('HY000', u'[HY000] [unixODBC][ma-3.0.2]Error in server handshake (2012) (SQLDriverConnect)')

This proved to be the death knell. Researching this error, I found this GitHub issue, in which folks seemed pretty convinced that this represented a fundamental client/server protocol incompatibility. At this point I gave up on the old_password.so approach. It seems that the 3.0.2 version of the MariaDB driver code (C or ODBC) doesn't speak an old enough dialect of MySQL's protocol to work, though there are probably a lot of possible fixes I missed in that process.

Other paths tried

I tried a few other things you mentioned in your question which I'll briefly go over here:

• As you probably found, trying to disable SQL_AUTO_IS_NULL behavior in the MariaDB 2.0 ODBC driver family doesn't work well. This bug thread and the ODBC Connector parameters list have a couple of suggestions on how to disable the setting of that field (Option=8388608 is obvious and clear, right?), but none of those attempts to forcibly disable or enable the flag changed the behavior, whether they were in the connection string or the ODBC .ini files.

• The MySQL archive site has old versions of the ODBC connector available. Unfortunately, all of their compiled versions are for 32-bit Linux, which I don't have. I tried building from source, and it was a massive chore even to get the toolchain configured. At the point where I had to hand-install system identification files from 1999 I knew it was probably a lost cause, but I got all the deps and ancient versions installed and tried to compile it. The sheer number and variety of compile errors caused me to abandon this approach (C standard mismatches, plus a lack of compatibility with what appeared to be nearly every part of UnixODBC). It's totally possible there are simple fixes to these issues that I missed; I am not a C coder or old-linux-build-system expert.

• I tried some third party MySQL ODBC connectors, which didn't work; same errors as with the 5.* family.

• I compiled the 2.50.39 version of the Connector/ODBC library (only the sources were available on the archive). To do that, I first compiled the libmysqlclient.so.10 files for the 3.23 version of the server. This required altering the sources of the 3.23 server to solve some errno related problems (remove the #define clause for extern int errno in my_sys.h), copying the libtool OS definition files to various locations in the source directory (/usr/share/libtool/build-aux/config.{guess,sub} got copied to the ., mit-pthreads, and mit-pthreads/config/, if it matters). After that, I was able to compile and build the libmysqlclient libraries with the --with-mit-threads --without-server --without-docs --without-bench configure switches. Compilation failed with several inscrutable errors while evaluating the macros for the mysql client program after that, but the .so files for libmysqlclient had already been generated, so I grabbed them and moved on. After the libmysqlclient.so.10 library was compiled, I built the 2.50.39 version of Connector/ODBC from the archive. That required changing some of the sources from the main MySQL include files (removing references to asm/atomic.h), and the same system-identification libtool hack as the other libraries. It failed to find the iodbc libs (installed on Ubuntu via the libiodbc2-dev package) since they are now in /usr/include rather than /usr/local/include. I finally configured it with the switches --with-mysql-includes=$path_to_3.23_mysql_binary_dir/include --with-mysql-libs=$path_to_compiled_libmysqlclient.so.10_files_from_mysql_server_3.23_sources --with-iodbc-includes=/usr/include/iodbc, and it built without trouble other than the aforementioned atomic.h issue. However, after all that, connecting via my newly-compiled libmyodbc.so caused a segfault in Python/UnixODBC. Valgrind, gdb, and other tools were not useful in determining why; perhaps someone better versed in debugging compiled library interoperability issues could solve this problem.

• The MySQL archive has old, binary RPM versions of the Connector/ODBC. They are all 32-bit, and almost all modern Linuxes are 64-bit. I tried shimming those files by installing the i386 architecture and required libraries. The 64-bit Python/UnixODBC was unable to load the myodbc plugins successfully, returning the generic "file not found" error, which I eventually traced back to a failed call to dlopen. Libtool's dlopen wrappers (used by UnixODBC) are considered not-very-debuggable by most people, and after a significant hassle my rudimentary Valgrind tricks seemed to indicate, as I expected, that it wasn't possible to dynamically load an architecture-incompatible (i386 vs x86-64) ODBC backend.

Solutions/Remaining Options

In general it's probably going to be easier to rewrite the code on your end. For example, you could make a Python module that wraps a legacy Python non-ODBC MySQL driver (as @FlipperPA suggested in the comments on the question), hack 'enough' of the pyodbc interface onto that module that you don't have to refactor too much of your code that calls it, and thoroughly test before deploying. I know that sucks and is risky, but it is probably your best bet. In writing such a module, you may be able to make use of some of the internal code in pyodbc that handles generic ODBC syntax/et cetera.

You could even develop a "fake" ODBC backend for pyodbc that just called a non-ODBC Python MySQL driver, but I suspect that would be difficult, since pyodbc's backend-pluggability seems primarily geared towards compiled libraries rather than "dummy" shim code.

I'm not an expert in this stuff, so there totally might be solutions I've missed!

There are a few other possibilities I considered and gave up on:

You could file a bug with the MariaDB folks and it might be fixed. I don't have a good sense of whether the protocol error I ended up with is "this is fundamentally incompatible at every level" or "the auth system just needs a tweak then everything will work". It might be worth a shot.

Since there are 32-bit RPMs available of the version 2.50 Connector/ODBC code (they won't load into a 64-bit Python/UnixODBC environment), you could conceivably convert your whole stack (or even operating system distribution) to 32-bit code. However, if you use any non-common compiled stuff, this would likely be a significant hassle. While Ubuntu/Debian especially is very good about making packages available on old architectures, this still might be tricky. Even if you got everything converted, some behaviors might change, and it the old 32-bit characteristics would be an ongoing piece of strangeness for anyone working on your app. And that's only if the 2.50 driver works when accessed from a 32-bit runtime; there may be other issues after that which crop up. I'd only recommend trying this if the maintenance burden for all of your client code is likely to be very low in the future (if the project is small or unlikely to change).

Morals of the story

Software rots away bloody fast. Unless a project is continually committed to doing work to maintain backwards compatibility, things will quickly stop working, especially in web software.

It's not that the product itself breaks, it's that the universe changes out from under it in a million little ways. Unless someone is enough of a generalist and has been around long enough to know all those little changes and how to reverse them, it's very hard to move everything backwards in time/revisions to a place where things "just work".

If you get binaries for something, even if it's something supposedly "common" like a MySQL driver, keep them around. Ideally, share them with the internet.

If you have sources for something, rigorously document the entire list of dependencies/toolchain they need, and document it for humans. Assume that the tools needed to read programmatic dependency lists for e.g. autotools will themselves go obsolete. Nothing is too "obvious" to document; not architecture, kernel ABI, libc behavior--nothing. Now that we have "in a box on any kernel" things like Docker, you may be able to store more of your dependencies in a programmatic way, but don't count on it.

Answer 2

As Zac B has shown there is no real way to connect a MySQL 3.23 server from an Ubuntu 16 client with UnixODBC and pyodbc 3.07.

FlipperPA proposed to use pypi.python.org/pypi/MySQL-python/1.2.4 as the next solution possible. So I gave it a try:

MySQL-python installed via apt can not connect to that old MySQL. It uses the systems mysql-client libs which do not work with MySQL 3.23.

MySQL-python installed via pip is another thing: It gets the libs from mysql_config included in the libmysqlclient-dev package. Does not work with MySQL 3.23 either. But there is a chance to modify something here.

When I install libmariadb-client-lgpl-dev, I get a mariadb_config wich looks quite like the mysql_config. And the mariadb-client of Ubuntu 16 worked with MySQL 3.23 (as shown above).

ln -s /usr/bin/mariadb_config mysql_config
pip install MySQL-python

This does the job. I can connect that old MySQL Server from Python.

There are some problems with datatypes but I am not picky in this case.