• Connecting to a remote MySQL database
• Using MySQL in a 64-bit Mac OS X environment
• Shared object name for Unix, Linux or Mac OS X platform
• Specifying InnoDB Configuration Values for MySQL
• Making the MySQL Database Option Visible in Windows
• Specifying InnoDB Configuration Values for MySQL

This article contains useful information for connecting to MySQL databases.

If you are using a 64 bit operating system you should usually use the 64 bit version of MySQL.

Note: If you are having trouble getting Windows to recognize MySQL you should look at the last page Making the MySQL Database Option Visible in Windows.

Connecting to a remote MySQL database [top]

If your script uses connection object methods conn:query{}, conn:execute{} or conn:merge{} to connect to a remote MySQL database, the location of the remote host is specified as part of the name parameter:

   local conn = db.connect{   
      api=db.MY_SQL, 
      name='mydatabase@remote_machine',
      user='fred', 
      password='secret',
      use_unicode = true,
      live = true
   }

   conn:execute{sql='INSERT INTO Patient VALUES("Fred", "Smith")'}

Set live = true to execute SQL interactively in the Iguana script editor.

The location of the remote host can be specified as any of the following:

• The machine name: mydatabase@remote_machine
• The server name: mydatabase@remote_server
• The IP address: mydatabase@10.10.10.10

If the remote MySQL server is using a port other the standard port of 3306, you can specify it as part of the name parameter:

• mydatabase@remote_machine:1111

See also:

• Using Database Connection Objects

Using MySQL in a 64-bit Mac OS X environment [top]

If you are using Iguana on a 64-bit Mac OS X, Iguana will not work with a local 64-bit installation of MySQL.

To use Iguana with MySQL on the Mac, you must install the 32-bit version of MySQL.

To do this:

1. Download the 32-bit MySQL version 5.5 Mac OS X distribution.
   Note: We recommend using the DMG archive rather than the TAR archive.
2. Double click the DMG file to open it.
3. Install the three MySQL packages:
   1. The main mysql package.
   2. The startup package.
   3. The Preference Pane.
4. In Iguana, click the Settings tab.
5. Click Database Settings to view the available database APIs.
6. Click the Edit button for MySQL.
   
7. Browse for the location of the 32-bit MySQL shared library that you have just installed.
   By default: /usr/local/mysql/lib/libmysqlclient_r.dylib
   
   Note: The Database Settings reference has more information (see the bottom of the page).
8. Open the MySQL Preference Pane in System Preferences, and confirm that:
   1. MySQL is running.
   2. MySQL is set to start automatically.
      

Shared object name for Unix, Linux or Mac OS X platform [top]

If you are using Iguana 4.0 or later, you can use the Database Settings page to search for the MySQL library that you want to use. For more information on the Database Settings page, see Changing the Shared Library (at the end of the page).

The table below shows the shared object name for each supported platform:

Specifying InnoDB Configuration Values for MySQL [top]

The following tuning tips may help improve the performance of your database in an Iguana environment.

Specifying InnoDB Configuration Values for MySQL

If you are using a MySQL database, the following InnoDB configuration values are recommended:

• innodb_log_buffer_size=8M
• innodb_buffer_pool_size=512M
• innodb_log_file_size=512M

Setting up a Database Maintenance Plan for MS SQL

If you are using an MS SQL database, a Database Maintenance Plan is recommended. This will ensure that your database is safe and properly maintained. The following resources provide more information on creating a Database Maintenance Plan:

• Creating a SQL Server Maintenance Plan.
• A video can be found here: Creating a Maintenance Plan in SSMS (free registration required).

Installing MS SQL on a Separate Partition

If you are using MS SQL, you should not install your database on the system. A better solution is to install your database on a separate partition with a large amount of available disk space. This will ensure that there will be enough space to store your database tables as they grow.

Making the MySQL Database Option Visible in Windows [top]

Installation Notes

• You must have MySQL installed and the system PATH must be set correctly to ensure that the MySQL database option is visible in the API drop-down menu.

Unsupported MySQL Versions

• MySQL 5.0.41 is not supported by Iguana (and Chameleon) because of a defect in MySQL; more specifically, MySQL 5.0.41 is unable to dynamically load libmysql.dll.
• For details, see MySQL’s website at http://bugs.mysql.com/bug.php?id=28358.

Note: The screenshots in this section are from Windows 2000 – the screens will be similar but slightly different for Windows XP or later operating systems. However, note that iNTERFACEWARE does not advise using Windows ME or Windows 98 for running important, mission-critical server applications.

To make the MySQL database option visible:

• Click Start > Settings > Control Panel > System. The System Properties window appears.

• Click the Advanced tab > Environment Variables. The Environment Variables window appears.
• Under System Variables, scroll down and highlight Path, and click Edit. The Edit System Variable window appears.

Note: In the above example, the path of the libmysql.dll file is C:\mysql\bin. However, the actual path for your libmysql.dll file may vary if mysql has been installed in a different directory.

• Add the location of your libmysql.dll file to the Variable Value field of the system PATH. Make sure to separate the path from the last value with a semi-colon as shown above.
• Once the path has been set, click OK to save your changes.
• Restart your computer for the changes to take effect. The MySQL database option should now be visible in the API drop-down menu.

Specifying InnoDB Configuration Values for MySQL [top]

If you are using a MySQL database, the following InnoDB configuration values are recommended:

• innodb_log_buffer_size=8M
• innodb_buffer_pool_size=512M
• innodb_log_file_size=512M