MySQL Tab

How To Access

  • Main Menu | Tools | Generate Scripts | SQL Script for Creating Table MySQL tab.

This tab allows you to select the data and index tablespace and set migration options that are unique to MySQL & MariaDB databases.

Note

If not connected to an MySQL or MariaDB server, all the controls on this tab page will be disabled.

GenerateScripts_MySQLTab.png

Use Database

Select the database where the converted data will be saved on the back-end.

Select Table Handler

Selects the table type or storage engine for the migrated files. MySQL & MariaDB support the following storage engines:

  • ISAM: The first storage engine created for MySQL. These tables resemble DataFlex files.

  • MyISAM: The new binary portable storage engine that is used in lieu of ISAM. MyISAM provides high-speed storage and retrieval, as well as full text searching capabilities.

  • InnoDB: A transaction-safe storage engine that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB also supports row-level locking. To maintain data integrity, InnoDB tables also supports FOREIGN KEY referential-integrity constraints. Note: Mertech officially only supports the InnoDB table handler.

  • BDB: Transaction safe tables that support page locking (similar to Oracle).

  • Merge: A collection of MyISAM tables used as a single table.

  • Heap: The data in these structures are only stored in memory. This option is only available if AUTO_INCREMENT (RECNUM column) table property is not selected. The HEAP storage engine is also referred to as the MEMORY storage engine.

Note

ISAM, MyISAM, MERGE, and HEAP storage engines handle nontransactional tables. The InnoDB and BDB storage engines provide transaction-safe tables.

Table Creation Parameters

Enables and disables access to the parameters below. If this option is not checked the parameters below are not included in the CREATE TABLE statement.

  • Use Default: When this option is checked, user-entered values for the parameters below are ignored and Flex2SQL creates the table(s) using default values.

  • Apply Selection to All Tables: Check this option if the updated values should be stored and used for future table conversions.

  • AVG_ROW_LENGTH: This option is used to pre-allocate space for large tables with variable size records. If an average row length is not specified Flex2SQL uses the value from the DataFlex file property “DF_FILE_RECORD_LENGTH” as its default.

  • CHECKSUM: This option maintains a checksum for all rows (MyISAM). It also slows the performance when updating but facilitates the search for corrupted tables. This option is turned ON in Flex2SQL by default.

  • COMMENT: A 60-character text string used to better identity the table and its content. Flex2SQL uses the value from the DataFlex file property “DF_FILE_DISPLAY_NAME” as its default.

  • MAX_ROWS: This option is used to pre-allocate disk space. It is the maximum number of rows you plan to store in the table. Flex2SQL uses a calculation (MIN_ROWS * 1.5) as its default.

  • MIN_ROWS: The minimum number of rows stored in the table. Flex2SQL uses the value from the DataFlex file property “DF_FILE_RECORDS_USED” as its default.

  • PACK_KEYS: This option compresses the indexes saving disk space. Due to its compression mechanism it makes updates slower and reads faster (MyISAM & ISAM). Setting it to 0 disables packing of keys. Setting this to DEFAULT (MySQL 4.0) tells the storage engine to only pack long CHAR/VARCHAR columns. This option is turned ON in Flex2SQL by default.

  • PASSWORD: Encrypts the .frm file with a password. This option does not apply to the standard MySQL version.

  • DELAY_KEY_WRITE: This option delays key table updates until the table is closed (MyISAM). This option is turned OFF in Flex2SQL by default.

ROW FORMAT

Defines how the rows should be stored. Currently this option only works with MyISAM tables. Flex2SQL checks if DataFlex has compressed the table. If compressed it defaults to “Dynamic” otherwise it defaults to “Default”.

Row Format

Description

Default

The default row format is static unless MySQL or MariaDB encounters columns defined as VARCHAR, BLOB, or TEXT in the table. In that case Dynamic ROW_FORMAT is used.

Static

This is the most secure and fastest format.

Dynamic

Used on tables containing variable length data types.

Compressed

Tables that are compressed take up very little disk space, which minimizes disk usage. Compressed tables are read-only.

Note

Do not select compressed format for tables that require updating.

Bottom Controls

The controls at the bottom of the dialog remain the same regardless of the tab selected.

What to do radio buttons

There are 3 options:

  • Create Table and index: This will generate a script that can be used to create the tables and the indexes from the selected .Dat file(s) onto the selected backend.

  • Create Table Only: This will generate a script that can be used to generate the create the table(s) from the selected .Dat file(s) onto the selected backend.

  • Create Indexes Only: This will generate a script that can be used to create the indexes from the selected .Dat file(s) on the seleted backend.

The Buttons

  • Preferences: Shows the Preferences dialog.

  • SaveSet: Shows the Save the files to save as a set dialog.

  • Generate All: Generates the scripts for all the tables selected in Filelist dialog based on the radio button selected in What to do radio buttons.

  • Convert: Generates script for the only the table in the Table Name form based on the radio button selected in What to do radio buttons.

  • Help: Shows the help page of the selected tab.

  • Cancel: Closes the dialog.