MySQL

MySQL is a popular open-source database that is distributed under a dual license policy. If you want to use MySQL in a DataFlex environment, you should obtain a MySQL Commercial License from MySQL AB.

MySQL supports different table types that have varying physical characteristics. How a MySQL database is stored depends on the table type used.

In our final testing of the connection to MySQL, we used MySQL version 4.0.20. The ODBC driver used was version 3.51.09.00.

Table Types

MySQL supports different ways to store tables, known as table types. Different table types are available, some of which support transactions while others do not. We recommend using a table type that supports transactions. The default table type is InnoDB. For more information on table types, see the MySQL documentation.

If you use InnoDB, make sure you set up the InnoDB buffer size to a reasonable value. A small InnoDB buffer will negatively impact performance. The default values after installing MySQL are very conservative. See the MySQL documentation on setting the InnoDB buffer size, specifically look for Innodb_Buffer_Pool_Size.

Database Configuration File

The name of the database configuration file for MySQL is MySQL.int.

A configuration file with recommended settings is part of the installation. Most settings reported by MySQL are accurate. In this file, the following settings are made:

Duplicate Record Keywords

The duplicate record keywords are set to the MySQL duplicate record error state and number as follows:

Duprec_Errornumber 1062
Duprec_State       23000

Use_Identity_Type

The Use_Identity_Type keyword is set to 1. This ensures the ODBC Driver uses the MySQL Auto Increment type for record identity columns. If you convert a database of an existing DataFlex application to MySQL, the DataFlex application does not need to be adjusted.

If you set this keyword to 0 (the default value), the Auto Increment type will not be used. This means you will have to adjust existing DataFlex code to assign record identifiers.

MySQLTableType

The MySQLTableType keyword can be used to set up the type of the MySQL tables created by the driver. MySQL supports several different table types, each having its advantages and disadvantages. See the MySQL documentation for more information on table types. It is recommended to use a table type that supports transactions.

In the MySQL database configuration file, the keyword is not set, so the default is used. The default table type is InnoDB. The entry is in the configuration file to document the possibility to set the keyword and its possible values.

Default_Max_Rows

In most ODBC connections, the default result set size of 0 works reasonably well. While testing the connection to MySQL, we found that the result set size must be limited to achieve decent performance.

The Default_Max_Rows keyword can be used to set up a default for the Max_Rows_Fetched setting of new tables. The recommended setting for this keyword is 10.

Native_Lockerror

The deadlock and lock timeout errors of MySQL are defined as follows:

Native_LockError 1205
Native_LockError 1213