Sql-server – SQL Server to MySQL sync, using Workbench, FreeTDS, and iODBC on 10.9

freetdsMySQLmysql-workbenchsql-server-2008unixodbc

I've been struggling to get the MySQL Workbench to behave properly to complete the following:

With Mavericks (OS X 10.9), connect to a SQL Server and create a migration job to a MySQL server.

The main issue seems to be with ODBC drivers. iODBC was deprecated with 10.8, and the header files removed complete in 10.9 (Some discussion on this here. More in-depth discussion here).

Despite my best efforts, this is the farthest I have gotten in Workbench, when trying to Test the connection to a SQL Server on a local subnet:

The ODBC driver that was selected for the source connection was not found by the ODBC manager.
Ensure that you have the proper ODBC driver installed and retry.

Error Text: Error("('00000', '[00000] [iODBC][Driver Manager]dlopen({FreeTDS}, 6): image not found (0) (SQLDriverConnect)')"): error calling Python module function DbMssqlRE.connect

Here is what I have done up to this point:

  • installed FreeTDS .92.XX from their site (homebrew install of FreeTDS wasn't acceptable, as it uses .91. Apparently .92 or greater is required, according to the Docs I have been following on MySQL, located here

  • Installed the latest iODBC files I could find on iodbc.org (seemingly forgotten since 10.6)

  • Using homebrew, installed unixODBC, just in case that provided some alternative, which did not seem to be of any use either,

  • installed ODBC Administrator App, v 1.1.0 from Apple

Has anyone successfully connected to a SQL Server database as a source, using the Workbench on Mac OS 10.9?

About to give up and opt for the Windows app, because this iODBC stuff is becoming pretty frustrating 5+ hours in 🙁

Edits

It seems that PuTTY is showing simple telnet and ssh commands over ports 3606 and 22 are not accepted either.

The firewall is completely open on Windows, from what I can tell. Private, Public, and Corporate settings allow for all inbound and outbound traffic.

root and users with a wildcard host are not working either :-/

Best Answer

Was trying to solve the same problem and stumbled here... It's a year late, but better late than never...

OSX uses iODBC as ODBC layer, so FreeTDS must be compiled against iODBC for MySQL Workbench to connect.

Since I struggled with iODBC as well, what I ended up doing was to create a custom "library installation" to run this.

Steps are as follows:

  1. Make sure you have XCode and command line development utilities installed. You will compile lots of stuff!

  2. Download and compile iODBC (I used 3.52 from www.iodbc.org). Make sure you install somewhere "isolated" with "--prefix=/usr/local/iodbc" for instance;

  3. Download and compile FreeTDS:

    • cd to freetds source directory;

    • type export CFLAGS="-arch i386"

    • type export LDFLAGS="-arch i386";

    • run config with ./configure --prefix=/usr/local/freetds --with-iodbc=<path_to_iodbc_in_step_1> --enable-odbc-wide

    • (MySQL Workbench NEEDS 32-bit archtecture; the exports will ensure that, since Mavericks - my OSX - is 64 bit.)

  4. Download and install ODBC Administrator from Apple (https://support.apple.com/kb/DL895?locale=en_IS - version 1.0 worked for me);

  5. Run ODBC Admin, go to "Driver" tab and create a "TDS" driver; Point "driver file" to /usr/local/freetds/lib/libtdsodbc.so (it should be there after step 3!)

  6. Go to MySQL Workbench Migration Tools and choose as Connection Method "ODBC (FreeTDS)". In "Driver", type the name you used in step 5 ("TDS") and fill the other info as needed.

  7. "Test Connection" and go have a cup of your favorite drink before moving on! :)

One entire workday "invested", but it did work.

UPDATE

After my cup of coffee, I moved on with my test migration, only to find out my SQL Server connection was NOT actually connecting (correct user, correct password). Another morning's work and here are the adjustments:

  1. Make sure you compile iODBC to 32-bit architecture. To do so:

    • use the same export you will use when compiling FreeTDS (CFLAGS,LDFLAGS);
    • use --prefix=/usr/local/iODBC32. Make sure you compile to different directory or you may break your current ODBC install.

  2. Make sure you compile your "32-bit" FreeTDS version pointing to the "right" iODBC with:

    • ./configure --with-iodbc=/usr/local/iODBC32

This should do the trick.

Another piece of advice:

MySQL Migrations will use exec sys.sp_databases to fetch info about the schemas to be migrated. If you're not using sa to migrate (which you probably shouldn't!), make sure your login has enough privileges on the master database (I myself created a "temporary" clone of sa to migrate); otherwise, MySQL Migrations will have trouble fetching and copying.

All these things followed and I finally completed a successful migration (minor manageable collation problems).

Hope this helps,

Related Question