After database migration is completed, the person responsible for the process must check that everything has been migrated properly. This whitepaper explains how to validate results of MS Access to MySQL database migration.
First, it is necessary to answer the question: what kind of objects must be validated after database migration is completed? Below are the primary objects to verify:
- Schemas DDL (types mapping)
- Data
- Indexes and constraints
- Relationships between tables (foreign keys in MySQL)
- Queries (views in MySQL)
Table definitions
Microsoft Access allows to explore all tables in form of tree-view. Highlight the table, right click on its name and select ‘Design View’ menu. This will force opening new window with list all table columns and related properties. In MySQL there are two options to explore table structure:
- Run the query DESC `table name` in MySQL console client
or
- Highlight the table in the left pane and go to ‘Structure’ tab in phpMyAdmin
Conversion of table structures can be validated by comparison of column definitions in MS Access and MySQL tables. Size, attributes and default values must be preserved, while types are converted according to the list of safe mappings below:
MS Access | MySQL |
Text | VARCHAR(n), where n is size of Text column |
Memo | TEXT |
Byte | TINYINT UNSIGNED |
Integer | SMALLINT |
Long | INT |
Single | FLOAT |
Double | DOUBLE |
Currency | DECIMAL(13,4) |
AutoNumber | INT AUTO_INCREMENT |
Date/Time | DATE or TIME or DATETIME depending of column’s semantic |
Yes/No | BIT(1) or BOOL |
Ole Object | LONGBLOB |
Hyperlink | VARCHAR(255) |
Replication ID (guid) | VARCHAR(38) |
Data
Migration of data can be verified using visual comparison of random fragments in source and destination tables. Microsoft Access allows to browse the data by double-clicking on table name. MySQL provides two options for the same purpose:
- In MySQL console client run the query SELECT * FROM `table name` LIMIT start_record, number_of_records
or
- Highlight the table in the left pane and go to ‘Browse’ tab in phpMyAdmin
Make sure that number of rows in source and destination tables are equal for each table being migrated. MS Access displays the number of rows at the bottom of window containing the data. In MySQL the count of rows can be obtained as follows:
- In MySQL console client run the query SELECT COUNT(*) FROM `table name`
or
- In phpMyAdmin highlight table name, click on the ‘Browse’ tab and number of rows will be displayed in the status line at the top of the window
Indexes
To view indexes corresponding to specified table in MS Access, right click on the table name and select ‘Design View’ menu. Then click ‘Indexes’ icon in the toolbar and all necessary information will appear in new window. MySQL provides two options for the same purpose:
- In MySQL console client run the query SHOW INDEXES FROM ` table name`
or
- Highlight the table in the left pane, click ‘Structure’ tab and all information about indexes will be listed after the table definition in phpMyAdmin
Relationships between tables
To get information about relationships between tables in MS Access highlight a table, click on “Design” menu item and select “Relationship Report” icon on the toolbar. Then you will see a diagram like on the screenshot below. Double click on a line connecting two tables to get properties of the corresponding relationship:
MySQL provides two options for extracting foreign keys:
- In MySQL console client run the query SHOW CREATE TABLE `table name`. Information about foreign keys is at the bottom of the displayed statement.
or
- Highlight the table in the left pane, click ‘Structure’ tab and follow ‘Relations view’ link below the table definition in order to get foreign keys in phpMyAdmin
Queries
In order to validate migration of queries compare SELECT-statements of each Microsoft Access query and the corresponding MySQL view.
MS Access allows to extracts SELECT-statement of the specified query as follows:
- Highlight the query, right-click on its name and select “Design View” > “SQL View” option
MySQL extracts view’s definition via SQL-query: SHOW CREATE VIEW `view name`