Configure Connection to a MySQL database

Before creating your Data Share connection you will first need to download and install a MySQL OleDB driver. We recommend downloading the MyOleDB driver from Sourceforge.

Please note that other MySQL OleDB drivers may not be free or open source.

Having installed the driver you can now create a new Data Share Connection using the Standard Data Source,

Click on the Configure Source Database button.
When you select the Provider tab you should be able to select the MySQL OLEDB Provider from the list.

On the Connection tab

Data Source

Enter the name of your MySQL database

Location

Enter the name of the server where your MySQL database resides.

Enter information to log on to the server

Select the radio button Use a specific user name and password and enter the User name and Password for Data Share to connect to the database.

User name and Password are case sensitive.

Once you have entered the data use the Test Connection button to check that you have entered the details correctly and then click OK.

Name

Enter a name for your MySQL connection.

SQL

The next task is to select the table and fields from the database that you wish to use. You will need to know the name of the table as you need to manually do a "SELECT * FROM table". Enter a FROM statement followed by the table name from which you wish to select your fields and then click on the Construct SQL button.

Gotchas

The most common issue will be an incompatibility of data types between MySQL and PostgreSQL. Modify your select statement to include one field at a time to narrow down the problem, and use the

CAST(column AS char)

function to convert problematic columns to strings. For example, if you get synchronisation errors due to date columns, then you can convert the date column to text within your SQL query using the following syntax:

Convert the date column to text

''SELECT CAST(datecolumn AS char) FROM tablename''

If your credentials are correct, but you get an “Unspecified Error” when testing the connection, check your password. If this contains any unusual symbols, these may not be encoded correctly. Test with ogrinfo using the command below. If your credentials are not properly encoded then this will return an “access denied” error, and will show you the incorrect encoding:

Test with ogrinfo

''ogrinfo MySQL:databasename,user=youruser,password=yourpassword,host=yourhost,port=yourport tablename -so''