Installing a driver for Microsoft SQL Server and Sybase ASE in Linux and Mac

In a recent post we showed you how to migrate a SQL Server database to MySQL. There, we used the oficial Microsoft ODBC driver and that’s OK if you are running MySQL Workbench in Windows. But what if your desktop OS is some Linux variant or Mac OS X?

It turns out that Microsoft has recently released an ODBC driver for Linux. However, you can’t use this driver with MySQL Workbench for Linux. (Actually you can, but you would have to rebuild Workbench). The main reason is that this ODBC driver was linked against unixODBC (an ODBC driver manager), while Workbench uses another ODBC driver manager: iODBC and the two of them can’t coexist in the same system.

So for Linux and Mac we prefer to go in a different direction and use FreeTDS, an alternate ODBC driver for Microsoft SQL Server. As a bonus, the very same driver can be used for connecting to Sybase Adaptive Server Enterprise, an RDBMS also supported in the MySQL Workbench Migration Wizard.

In spite that you can find FreeTDS in the software repositories of several modern Linux distributions, you are encouraged to download it and compile it yourself. Most distros provide fairly old FreeTDS versions and virtually all of them come linked against unixODBC, which is not supported by Workbench. Fortunately, the process for compiling and installing it is pretty straightforward. Here are the steps you should follow:

  1. Go to the FreeTDS homepage and download a recent version of the driver’s source code. Make sure that you have at least version 0.92. We advise you to use the latest stable version rather than a nightly snapshot. Save this tarball in a directory where you have write permissions (something like /home/<your_user>/freetds would do).
  2. If you are in Linux, make sure that you have installed the essential build tools (C/C++ compiler, linker, etc.) and the development files for iODBC. Try running this from the command line if your are using a Debian based distro like Ubuntu:
    $> sudo apt-get install build-essential libiodbc2-dev

    If your distro uses rpm packages (Red Hat, Fedora, etc.) the command would be like this:

    $> su -
    $> yum install make automake gcc gcc-c++ libiodbc-devel

    Mac users can skip this step since the apropriate development files are already included in their system.

  3. Since there are a couple of options you would have to pass to the driver’s configure script, we have created a script file to help you pass this with no pain. You can find this script file in /usr/share/mysql-workbench/extras/build_freetds.sh if you are in Linux or inMySQLWorkbench.app/Contents/SharedSupport/build_freetds.sh if you use a Mac. Locate this helper script and copy it to the same directory where you put the driver’s tarball.
  4. Now it’s the time to actually build the driver. If you are not already there, cd to the directory where you put the helper script and run the helper script from there:
    $> ./build_freetds.sh

    Wait until the compilation finishes.

  5. Now that the driver is built, you should proceed to install it by running make install as root. And there you go, you should have the FreeTDS driver installed inside the /usr/lib or the /usr/local/lib directory. It will be named libtdsodbc.so.
  6. Proceed to register the installed FreeTDS driver as explained here.

Now you should be ready to use the driver to migrate Microsoft SQL Server and Sybase ASE databases to MySQL using the MySQL Workbench Migration Wizard.

Sergio de la Cruz

ODBC and Unicode in Linux

Recently I’ve had to spend countless hours trying to debug a problem mixing various ODBC components. Since I couldn’t find any answers online, at least not in a way that was easy for a ODBC beginner like me to understand, I’ve decided to write it down here so it can maybe save some frustration to other people.

ODBC in Linux

ODBC Managers

An ODBC manager is a library that’s responsible for interfacing between a ODBC driver and the application. The app tells the ODBC manager to open a connection and run queries; the manager loads the correct driver and forwards the ODBC API calls to the driver and the driver talks to the server, which does whatever the app told it to.

There are two ODBC manager libraries in Linux. unixODBC and iODBC. Both seem to work well, but unixODBC is somehow more popular, while iODBC is shipped as part of Mac OS X. We picked iODBC.

ODBC for Python

If you want to use ODBC from Python, you’ll need a module that does the role of the ODBC manager. Most will be just wrappers over a ODBC manager written in C, meaning either unixODBC or iODBC. We picked pyodbc.

Compiling pyodbc against iODBC is not very straightforward, because it just assumes everyone uses unixODBC in Linux (although it works just fine with iODBC, since that’s what is assumed when it’s compiled for the Mac). I won’t go into details, but the 2 things that need to be done to compile pyodbc with iODBC are:

  • edit setup.py so that the settings[‘libraries’].append(‘odbc’) line for linux is changed to settings[‘libraries’].append(‘iodbc’), making pyodbc link to iodbc
  • set the CFLAGS and LDFLAGS environment variables to your iODBC install paths and run setup.py. Example:

CFLAGS=-I/usr/include/libiodbc LDFLAGS=-L/usr/lib python setup.py install

Connecting to MS SQL Server (FreeTDS)

FreeTDS is a free driver/library that talks the Sybase and the MS SQL Server protocols. Apparently you can use it directly or you can use it as a ODBC driver.

As with pyodbc, it is probably a good idea to compile it against the driver manager you’re using.

The big wchar_t mess

In ODBC, there are 2 string types: SQL_CHAR and SQL_WCHAR. The 1st is just a plain char* array representing a plain string string. The 2nd is a Unicode string.

In Windows, MS chose to hardcode the type of SQL_WCHAR to unsigned short (2 bytes per char). The driver assumes 2 bytes per char, the ODBC manager assumes 2 bytes per char and the app assumes 2 bytes per char, so everybody understands each other because they’re talking in 2 bytes per char.

In Linux, the default is to use wchar_t. wchar_t is 4 bytes.

By default, unixODBC uses unsigned short, like Windows. But you can also compile it with SQL_WCHART_CONVERT, which will define SQL_WCHAR as wchar_t instead.

iODBC uses wchar_t always.

You can see the mess brewing up, but there’s more.

FreeTDS uses 2 bytes per char, like Windows. No matter what the driver manager is used. So by now, you can see that every component of the ODBC stack can use either 2 bytes per char or 4 bytes per char depending on how you setup everything. It may not seem like a big deal, but if you’re a ODBC newbie and have no idea about how ODBC handles Unicode data and just assumed everything would just play together nicely and in the same way (because ODBC is supposed to be a standard after all), then you’re set up for the same countless hours of hairpulling and sleepless nights as me; trying to figure out why is the Unicode data from the server presented by Python as a series of characters with 0s sprinkled between each char or just fails with some MemoryError exception.

If you use FreeTDS with unixODBC the pair will work OK, because both use 2 bytes per char by default. But if you need to use pyodbc, then it won’t, because pyodbc assumes the ODBC manager talks wchar_t. That will cause Unicode resultset strings from the server to be displayed garbled, as if it had forgotten to decode from UCS-2. Or just crash, depending whether you’re running 32 or 64bits.

If you use FreeTDS with iODBC it will not work, because FreeTDS sends data as unsigned short and iODBC sends the data to Python as wchar_t. That will cause some error in Python and you’ll get some MemoryError exception, which says nothing at all about what is the prolem.

So by default, there’s no way that things will work out OK, except in some some few lucky combinations.

BUT it seems the FreeTDS developers realized that just assuming 2 bytes per char for SQL_WCHAR is not something that’s good enough for everyone and added “experimental” support for properly using wchar_t. Unfortunately that is not the default and there’s no auto-detection either. So if you’re having to use FreeTDS with iODBC (or unixODBC configured for SQL_WCHART_CONVERT), you’ll need to use the magic –enable-odbc-wide configure option when compiling it. Also, that only actually works in version 0.92, 0.91 apparently has the option too, but it doesn’t work correctly. Then everything will finally talk in wchar_t from end to end and you get to sleep without worrying about the deadline in the next day.

 

 

MySQL Workbench 5.2.36 GA available

MySQL Workbench 5.2.36 has been released. This release is part of an ongoing effort committed to improving the day to day usability of the product based on accumulated experience from tools such as MySQL Query Browser and input from user feedback.

This specific version was focused on improving usability of the Query Editor. Some of the changes introduced are:

  • New, redesigned Query Editor layout. Output messages are always visible while resultsets and the query editor can be resized according to the task at hand. Resultsets are now in the same tab as their generating query editor.
  • Several minor changes that make the difference for a frustration-free, comfortable use of the tool. Sidebar sizes, the last selected schema and other state information is now properly remembered between sessions. Keyboard navigation of resultsets has been fixed to properly handle Tab key navigation in all platforms.
  • SELECT queries are now analyzed as in the old MySQL Query Browser tool and, if possible, its resultsets can be edited
  • The schema tree now allows applying actions to multiple selected objects at once.
  • The schema tree also shows more comprehensive information. In addition to schemas, tables, column, views and routines it also displays information about triggers, indexes and foreign keys. The object information box has been improved.
  • A new plugin containing various utilities useful for generating directly usable PHP code from your queries. For example, you can generate code that will run a SELECT statement, with SQL injection-safe variable binding and resultset iteration, from a query being edited in the SQL editor.
  • Export recordsets to XLS format files.

The Administrator was also improved in the following areas:

  • Server Start and Shutdown page was updated to include server error log output
  • The Log browser support was improved to support log files, when managing local servers

In addition, 82 bugs and feature requests have been fixed. A detailed list of closed bugs can be viewed at our Release Info Page.

We hope the included enhancements will make use of MySQL Workbench a lot more productive and fun. This effort is far from complete and the Workbench team will continue working on even more changes and additions to make Workbench the database tool of choice for users both advanced and inexperienced. We still have a lot of nice things in the oven, so keep an eye on new versions coming out!

Please get your copy from our Download site. Sources and binary packages are available for several platforms, including Windows, Mac OS X and Linux.

http://www.mysql.com/downloads/workbench/

Workbench Documentation can be found here

http://dev.mysql.com/doc/workbench/en/index.html

In addition to the new Query/SQL Development and Administration modules, version 5.2 features improved stability and performance – especially in Windows, where OpenGL support has been enhanced and the UI was optimized to offer better responsiveness. This release also includes improvements to the scripting capabilities of the SQL Editor. You can read more about it in

http://mysqlworkbench.org/workbench/doc/

For a detailed list of resolved issues, see the change log.

http://dev.mysql.com/doc/workbench/en/wb-change-history.html

If you need any additional info or help please get in touch with us.

Post in our forums, leave comments on our blog pages or if you want to talk to us directly you can visit us on our IRC channel #workbench on irc.freenode.net.

– The MySQL Workbench Team

Building MySQL Workbench from sources on Ubuntu/Debian

To build MySQL Workbench one would need to install dependencies, fetch source code, configure it and actually do a build. Here’s how to do it on a Ubuntu/Debian system

 

1) Get source code

Open the browser and go to http://www.mysql.com/downloads/workbench/

When in that page, select “Source Code” from the select box. Usually the TAR file will do the job, so download the “Generic Linux” option and uncompress it:

$> tar xf mysql-workbench-community-<version>-src.tar.gz

 

2) Install dependencies necessary to build

We’ll need to remove iodbc, if it’s installed

$> sudo apt-get remove iodbc

 

Now, we can install the full set of dependencies needed to build Workbench

$> sudo apt-get install build-essential cmake cmake-data autoconf automake pkg-config libtool libzip-dev libxml2-dev libsigc++-2.0-dev libglade2-dev libgtkmm-2.4-dev libglu1-mesa-dev libgl1-mesa-glx mesa-common-dev libmysqlclient-dev libmysqlcppconn-dev uuid-dev libpixman-1-dev libpcre3-dev libgnome2-dev libgnome-keyring-dev libgtk2.0-dev libpango1.0-dev libcairo2-dev python-dev libboost-dev libctemplate-dev mysql-client python-pysqlite2 libsqlite3-dev libtinyxml-dev swig libvsqlitepp-dev libgdal-dev libproj0

 

3) Prepare to build

Go to the directory where you uncompressed the TAR file

$> cd mysql-workbench-community-<version>-src

Now, create the directory where the build files will reside and go there

$> mkdir wb-build

$> cd wb-build

 

4) Build and install

Build and install Workbench, and optionally set the install prefix (defaults to /usr/local)

$> cmake [-DCMAKE_INSTALL_PREFIX=/usr] ..
$> make
$> sudo make install

If you don’t want to install it on the system directories or don’t have permissions to do so, try to install on the current directory. Be aware that the Workbench launch script must be updated to the proper directories

$> make install DESTDIR=.

 

Notes:

  • You will need swig 1.3 to build WB
  • Antlr 3.4 is needed, but the current release has many bugs. You will need it patched with https://github.com/antlr/antlr3/pull/43 if you don’t want to use the bundled version. Then you’ll need to

          export ANTLR_JAR_PATH=<path_to_antlr_complete_file>

  • Instead of iodbc you can use unixodbc, to do that you need to install unixodbc and add -DUSE_UNIXODBC=True to cmake
  • Ubuntu Packages require unixodbc, so if you’re using it, there won’t be any problem. If you want to use iodbc, you’ll need to build it from source.
  • On a Core2 Quad 2.4 GHz and a 4G of RAM it takes about 30-40 minutes to build Workbench. Also it uses about 5G of hard drive space to build and install.

MySQL Workbench 5.2.35 GA Available

The MySQL Developer Tools team is proud to announce the next release of it’s flagship product, MySQL Workbench, version 5.2.35. This is the next maintenance release containing 29 bug fixes as well as new feature additions. We also updated the supported platforms to include most recent editions of Fedora and Ubuntu Linux.

MySQL Workbench 5.2.35 now supports the Native Windows Authentication Method – available in the commercial MySQL server product – and the creation of user accounts utilizing this new authentication method.

For the PHP developers out there, Workbench now also comes with a new set of plugins to turn SQL Queries into PHP code to be put right into your scripts.

A big “Thank You” again to everyone for the large amount of feedback and ideas we have received on how to further improve and extend MySQL Workbench. We are continuously working on improving the functionality and stability of MySQL Workbench – please keep sending us your ideas!

MySQL Workbench 5.2 GA

  • Data Modeling
  • Query (replaces the old MySQL Query Browser)
  • Administration (replaces the old MySQL Administrator)

Please get your copy from our Download site. Sources and binary packages are available for several platforms, including Windows, Mac OS X and Linux.

http://dev.mysql.com/downloads/workbench/

Workbench Documentation can be found here.

http://dev.mysql.com/doc/workbench/en/index.html

In addition to the new Query/SQL Development and Administration modules, version 5.2 features improved stability and performance – especially in Windows, where OpenGL support has been enhanced and the UI was optimized to offer better responsiveness. This release also includes improvements to the scripting capabilities of the SQL Editor. You can read more about it in

http://wb.mysql.com/workbench/doc/

For a detailed list of resolved issues, see the change log.

http://dev.mysql.com/doc/workbench/en/wb-change-history.html

If you need any additional info or help please get in touch with us.

Post in our forums, leave comments on our blog pages or if you want to talk to us directly you can visit us on our IRC channel #workbench on irc.freenode.net.

– The MySQL Workbench Team

MySQL Workbench, Windows XP and SSH public key auth.

It happens that sometimes you need to access a remote box which supports ssh key authentication. Recently I was trying to reproduce a bug related to SSH public key authentication, so here I would like to share some of my experience.

There will be no explanation of the public key authentication itself here, rather the actual setup and steps to have a public key auth for Windows(client) -> Linux(server) working. Why Windows you would ask? Because interactions for Linux->Linux and for Mac OS X -> Linux simply work using the Unix way, while for Windows you may need some extra actions to do.

 

Setup

What I had at endpoints:

    Linux – Ubuntu 11.04, sshd is set up to deny password auth.
    Windows – well, it is an XP SP3 i386 box. MySQL Workbench 5.2.34+ is installed

First of all I created an encrypted pair of RSA keys, using Linux box’s ssh-keygen. After that the public key was added to ~/.ssh/authorized_keys and the private one was moved to the Windows box.

Naturally my first attempt was to simply specify path to the private key file in the server settings, just as I would do in Linux or OS X.

 

Remote management section

 

That did not work, just as the bug report had said. Moving key to $HOME/.ssh/id_rsa did not help. Could that be that paramiko can not handle openssh keys on Windows, or openssh’s encryption method?

 

Error message on connect via SSH public keys

 

Bazaar has similar issues on Windows, the solution they suggest is to either put keys into .ssh dir, or use pageant tool from PuTTY.  I tried .ssh, that did not work. So the latter way turned into conversion of the openssh private key into PuTTY ppk format. The conversion is done using PuTTYgen, then the key is loaded in the pageant. More details are given in the mentioned bazaar guide Bzr and SSH.

And this worked! Let me sum up the steps:

    1. Generate keys, using either openssh on Linux, OSX, Cygwin, or using PuTTYgen;
    2. Specify private ssh key in the appropriate section of the Workbench’s “Server Instance Editor”;
    3. Add key to pageant tool.
    4. At this moment passwords to unlock keys have to be entered both in MySQL Workbench and the pageant.
    5. Use it…

MySQL Workbench 5.2.34 GA Available

The MySQL Developer Tools team is pleased to announce the next release of it’s flagship product, MySQL Workbench, version 5.2.34. This is a maintenance release containing 100 bug fixes; ranging from stability improvements on all supported platforms – including some that prevented startup in certain environments – to minor but significant usability corrections.

As always, we want to thank everyone for the great feedback we have received. This helps us to continuously improve the functionality and stability of MySQL Workbench – we appreciate all your ideas for improving MySQL Workbench.  Please keep sending us your ideas!

MySQL Workbench 5.2 GA

  • Data Modeling
  • Query (replaces the old MySQL Query Browser)
  • Administration (replaces the old MySQL Administrator)

Please get your copy from our Download site. Sources and binary packages are available for several platforms, including Windows, Mac OS X and Linux.

http://dev.mysql.com/downloads/workbench/

Workbench Documentation can be found here.

http://dev.mysql.com/doc/workbench/en/index.html

In addition to the new Query/SQL Development and Administration modules, version 5.2 features improved stability and performance – especially in Windows, where OpenGL support has been enhanced and the UI was optimized to offer better responsiveness. This release also includes improvements to the scripting capabilities of the SQL Editor. You can read more about it in

http://wb.mysql.com/workbench/doc/

For a detailed list of resolved issues, see the change log.

http://dev.mysql.com/doc/workbench/en/wb-change-history.html

If you need any additional info or help please get in touch with us.

Post in our forums, leave comments on our blog pages or if you want to talk to us directly you can visit us on our IRC channel #workbench on irc.freenode.net.

– The MySQL Workbench Team

MySQL Workbench 5.2.33 GA Available

The MySQL developer tools team announces the next release of it’s flagship product, MySQL Workbench – version 5.2.33. This is a maintenance release only which corrects some problems we didn’t cover in last release. It contains fixes for 7 bugs or enhancement requests.

As always, we want to thank everyone for the great feedback we have received. This helps us to continuously improve the functionality and stability of MySQL Workbench – we appreciate all your ideas for improving MySQL Workbench.  Please keep sending us your ideas!

MySQL Workbench 5.2 GA

  • Data Modeling
  • Query (replaces the old MySQL Query Browser)
  • Administration (replaces the old MySQL Administrator)

Please get your copy from our Download site. Sources and binary packages are available for several platforms, including Windows, Mac OS X and Linux.

http://dev.mysql.com/downloads/workbench/

Workbench Documentation can be found here.

http://dev.mysql.com/doc/workbench/en/index.html

In addition to the new Query/SQL Development and Administration modules, version 5.2 features improved stability and performance – especially in Windows, where OpenGL support has been enhanced and the UI was optimized to offer better responsiveness.
This release also includes improvements to the scripting capabilities of the SQL Editor. You can read more about it in

http://wb.mysql.com/workbench/doc/

For a detailed list of resolved issues, see the change log.

http://dev.mysql.com/doc/workbench/en/wb-change-history.html

If you need any additional info or help please get in touch with us.

Post in our forums, leave comments on our blog pages or if you want to talk to us directly you can visit us on our IRC channel #workbench on irc.freenode.net.

– The MySQL Workbench Team

MySQL Workbench 5.2.32 GA Available

We’re proud to announce the next release of MySQL Workbench, version 5.2.32. This is a maintenance release featuring
a new and improved UI appearance and several corrections and other enhancements.

The tabbed interface has been refreshed to obtain a clearer separation between different modules of Workbench, while improving responsiveness when switching between tabs. The Query Formatter has been rewritten and is now faster and more robust on its handling of queries. The layout of the Administration module has been changed to allow for easier future expansion and use less vertical screen space. Parts that had problems managing MySQL 5.5 servers have been fixed along other total of 53 bugs or enhancement requests have been addressed.

As always, we want to thank everyone for the great feedback we have received. This helps us to continuously improve the functionality and stability of MySQL Workbench – we appreciate all your ideas for improving MySQL Workbench.  Please keep sending us your ideas!

MySQL Workbench 5.2 GA

  • Data Modeling
  • Query (replaces the old MySQL Query Browser)
  • Administration (replaces the old MySQL Administrator)

Please get your copy from our Download site. Sources and binary packages are available for several platforms, including Windows, Mac OS X and Linux.

http://dev.mysql.com/downloads/workbench/

To get started quickly, please take a look at this short tutorial.

MySQL Workbench 5.2 RC Tutorial

http://wb.mysql.com/?p=406

Workbench Documentation can be found here.

http://dev.mysql.com/doc/workbench/en/index.html

In addition to the new Query/SQL Development and Administration modules, version 5.2 features improved stability and performance – especially in Windows, where OpenGL support has been enhanced and the UI was optimized to offer better responsiveness.
This release also includes improvements to the scripting capabilities of the SQL Editor. You can read more about it in

http://wb.mysql.com/workbench/doc/

For a detailed list of resolved issues, see the change log.

http://dev.mysql.com/doc/workbench/en/wb-change-history.html

If you need any additional info or help please get in touch with us.

Post in our forums, leave comments on our blog pages or if you want to talk to us directly you can visit us on our IRC channel #workbench on irc.freenode.net.

– The MySQL Workbench Team

Workbench and MySQL server at non-standard location in Linux.

Recently I had to test MySQL Workbench against fresh version of the server. Naturally, the default mysql-server was already installed from repository of my Linux distro. So I installed the latest server version into my /opt/server directory. And then I had several points to resolve, for example, how to start detached server process from Workbench Adminstrator; how to detect if the server is running or not, given that there are many of them running; how to stop the server.

Now I will show several workarounds to perform tasks listed in the previous paragraph. Below are the commands I put into Server Instance Editor, there are corresponding text entries labeled: ‘Start MySQL’, ‘Stop MySQL’, ‘Check MySQL Status’:

Staring server – (nohup /opt/server/5.5.9/bin/mysql_safe –defaults-file=/opt/server/5.5.9/my.cnf & disown %1)
Note! the command above must be used including parentheses
Stopping server – kill `cat /opt/server/5.5.9/mysql.pid`
Note! the command contains backticks
Checking MySQL Status – ps ax | wba_filter(/opt/server/5.5.9/my.cnf

Also I had to tweak /opt/server/5.5.9/my.cnf, here is the content of the file:
[mysqld]
port = 3308
basedir = /opt/server/5.5.8
datadir = /opt/server/5.5.8/data
tmpdir = /opt/server/5.5.8

socket = /opt/server/5.5.8/mysql.sock
pid-file = /opt/server/5.5.8/mysql.pid
log-error = /opt/server/5.5.8/mysql.error.log

That is all I had to change to make the server installed in custom location work from WBA.