MySQL consolidated database

The MobiLink server supports MySQL Community and Enterprise servers 5.1.22 or later. QAnywhere and MobiLink models do not support MySQL.

Setting up MySQL as a consolidated database

To set up MySQL to work as a MobiLink consolidated database, you must run a setup procedure that adds MobiLink system tables, stored procedures, triggers, and views that are required for MobiLink synchronization. There are two ways you can do this:

  • Using the MySQL command line tool or the MySQL Query Browser, run the syncmys.sql setup script, located in the MobiLink\setup subdirectory of your SQL Anywhere installation. Make sure that your MySQL user ID has privileges to create triggers.
  • In the MobiLink plug-in in Sybase Central, choose Mode » Admin and connect to your server database. Right-click the database name and choose Check MobiLink System Setup. If your database requires setup, you are prompted to continue. Note that if you want to use an existing MobiLink system setup, then your default_schema should be the schema of the MobiLink system setup.
Note

The database user who runs the setup script is the only user who has permission to change the MobiLink system tables, which is required for configuring MobiLink applications. See Required permissions.

ODBC driver

You must set up an ODBC DSN for your MySQL consolidated database using the ODBC driver that is provided on the MySQL web site. The MobiLink server supports MySQL ODBC driver 5.1.3 or later. See:

To specify your ODBC configuration file in UNIX, do one of the following,

If any of your synchronization scripts contain batched SQL commands separated by semicolons, you may need to select the Allow Multiple Statements check box on the Flags 3 page of the MySQL Connector/ODBC Data Source Configuration window when you configure a DSN for the MobiLink server to make connections to your MySQL database.

MySQL issues

  • Storage Engine   The MobiLink server requires the default storage engine to be ACID compliant. If the default storage engine is not ACID compliant, make sure that all MobiLink server tables are created using an ACID compliant storage engine, such as InnoDB and Falcon.

  • Stored Procedures   You cannot use INOUT or OUT parameters in stored procedure calls. Procedures that require these parameters must be implemented as functions that return an OUT value.

    Server events that require INOUT parameters, such as authenticate_user and modify_user, must be implemented as functions and run using a SELECT statement instead of a CALL statement.

    Since user-defined named parameters are not modified after server events run, they are not supported.

  • Cursor Scripts   The events upload_fetch, download_cursor, and download_delete_cursor must be called using a SELECT statement, which the MobiLink server runs using a read-committed isolation level. A bug in the MySQL ODBC driver causes the server to read uncommitted operations, such as INSERT, UPDATE, and DELETE statements, which results in inconsistent data between the consolidated database and the remote database.

    To work around this problem, affix a LOCK IN SHARE MODE clause to your SELECT statements. For example,

    SELECT column1 FROM table1 WHERE id > 0 LOCK IN SHARE MODE

    This clause protects the SELECT statement from uncommitted operations.

  • Bulk upload   The MobiLink server relies on the MySQL ODBC driver, which does not currently support bulk upload.

  • MLSD   The MobiLink server relies on the MySQL ODBC driver, which does not currently support MSDTC.

  • SQLLEN Datatypes on the 64-bit MobiLink server for UNIX   The MySQL ODBC driver defines SQLLEN as a 32-bit integer, causing a discrepancy with the 64-bit MobiLink server, which defines SQLLEN as a 64-bit integer. If you are running MobiLink on a 64-bit UNIX environment, you must add the following to your ODBC configuration file, 
    length32=1

    This entry forces the server to read SQLLEN as a 32-bit integer. Your configuration should look similar to the following example,

    [a_mysql_dsn]
Driver=full_path/libmyodbc5.so
server=host_name
uid=user_name
pwd=user_password
database=database_name
length32=1

  • MySQL Server Configuration   The MobiLink synchronization scripts are stored in the ml_script table as TEXT and are retrieved when needed. You may need to set max_allowed_packet equal to 16m or greater in the my.ini file.

Isolation level

See MobiLink isolation levels.

Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0