Contents

Select tables and views

After you have created a project and selected your data sources, you can specify tables, views, and columns to be compared. Please, note, that Data Compare for MySQL automatically maps tables and views with the same name.

But keep in mind, that if there is any difference between the data sources, for example, if two views have different names, they may not be mapped automatically.

You can map such objects manually using the Mapping tab of the New Data Comparison wizard.

Auto-mapping options

The Mapping tab allows you to:

• Select a comparison key for each table or view
• Select the columns to be compared
• Map objects
• Unmap objects
• Reset mapping options to default
• Design a custom query
• Configure filter options

Select a comparison key for each table or view

To match rows in the two data sources, Data Compare for MySQL requires a comparison key for each table or view.

The tool automatically selects a comparison key if tables contain a matching primary or unique key.

To learn more, refer to What is a comparison key?

If Data Compare for MySQL is unable to identify a suitable comparison key for a table or view, a corresponding warning will be displayed.

To set the comparison key for an object, click an arrowed button in the corresponding row of the Comparison Key column.

Select Custom and the Column Mapping window opens where you can select the columns that will comprise the key. The difference between the columns will be ignored during comparison if the checkboxes in the Compare column are clear. The column will be excluded from the UPDATE statement and will not be synchronized if the checkboxes in the Sync column are clear.

Note

• You can not use as a comparison key those columns whose data type is text, blob, or clob.
• In case Compare is selected but Sync is not, the differences will not be displayed after the comparison. However, the synchronization will take place since the changes will be included into the synchronization script.

For more information about comparison keys, refer to What is a comparison key?

Select the columns to be compared

On the Mapping tab of the New Data Comparison wizard, the number of columns that will be compared for each table or view is displayed.

Data Compare allows customizing the comparison to consider only specific columns.

To select the columns to be compared, double-click the table or click … at the end of a row. A dialog will be displayed with checkboxes to include or exclude the columns.

Note

• You cannot map the columns having different names.
• You cannot map the columns having different data types.
• You cannot exclude the columns that are used for the comparison key.

Map objects

By default, when you select that databases to be compared, configure comparison options and move to the Mapping tab of the New Data Comparison wizard, Data Compare for MySQL automatically maps all the objects having the same names and data types in the selected databases.

To map objects that stay unmapped, on the Mapping tab, click Map Objects.

In the Objects Mapping window that opens, you can select a table or view you want to map.

Having selected the required objects, click Map. The selected objects will appear in a list of mapped objects on the Mapping tab.

Note

If tables or views that you are mapping contain columns with incompatible data types, Data Compare for MySQL cannot compare those columns and they will be excluded from the comparison. After the objects have been mapped for comparison and synchronization, you may encounter mapping warnings, displayed as Caution icons. We recommend study warnings thoroughly before moving on.

Unmap objects

You can unmap objects in several ways:

• Click Unmap in the upper menu bar. In this case, all the mapped objects will be unmapped.
• Select a required object from a list and click Unmap in the upper menu bar. In this case, only selected objects will be unmapped.
• Select a required object from a list, right-click it, and then select the Unmap option from the menu that appears.

Note

Other options available in the menu that appears after right-clicking the object from a list:

• Exclude all but this
• Unmap all but this
• Unmap all invalid

Reset mapping options to default

Data Compare for MySQL allows you to quickly and easily reset mapping options to default.

To do this, click Reset in the toolbar. The default mappings will be restored.

Design a custom query

You can use the Custom Query option to compare custom results received after query execution instead of comparing whole tables or views.

To do this:

1. Click Custom Query on the Mapping page of the wizard.

2. In the Custom Queries Mapping dialog that opens, select the Query or Table or View radio buttons both for the Source and the Target.

3.

• If you select the Table or View option, you will be able to map tables or views from the list.
• If you select the Query option for the Source, the Target or both, write query name in the field near Name (if the name assigned by default is not appropriate).

4. Type the required query in the SQL field.

5. Click Validate to validate your queries, if necessary.

6. Click Map.

7. Click OK to close the dialog window.

Tip

• Click Cancel to close the dialog and discard mappings.

• Click Reset to restore the default mapping of objects.

Configure filter options

By default, Data Compare for MySQL compares all rows in the selected tables or views. If they contain a large amount of data, the comparison can take a lot of time and even result in running out of disk space. You can simply avoid those by filtering the rows to be compared by applying a WHERE clause to the comparison.

Note

You can only filter rows if a database is selected as the data source. You can not use WHERE clauses for Scripts folders.

To filter the comparison with a WHERE clause:

1. On the Mapping tab, select the object to apply the filter to.

2. On the ribbon, click Filter.

3. In the WHERE Filter dialog, that opens, enter a valid WHERE clause.

You can easily get a list of columns by calling the context menu. For this, right-click a left field and hover over Insert Column:

This way, you don’t need to recollect columns names.

4. If you want to apply the expression to both the Source and the Target, select the Use the same expression for Source and Target checkbox.

5. Click OK to apply the filtering options.

6. The filtered object will be marked with a filter icon in the grid on the Mapping tab.

Mapping warnings

After objects are mapped for comparison and synchronization, you may encounter warnings on the Mapping tab of the New Data Comparison wizard. They are displayed with the Caution icon instead of a checkbox in the grid.

To read the warning messages, point to the warning icon. All warnings for this object will appear as a hint.

We recommend looking through the warnings before moving to the comparison.

The list of possible warnings:

Warning	Description
Objects to compare have no key column	It means that the custom comparison key is used but is not set. You should mark at least one column pair as a comparison key. For more information see: Selecting the comparison key for each table or view.
No columns to compare	It means, there are no columns with the same name in this pair of tables (views). To compare them you should map the columns manually.

The following table contains information about column-level warnings.

Warning	Description
Decimal columns have different precisions or scales, migrating the data may cause rounding or overflows.	This warning appears when Decimal columns in source have larger precision and/or scale than target columns. If the source column contains larger values than the target column can contain, such records cannot be synchronized. If the source records contain numbers with fractional part longer than target scale, such numbers will be rounded during synchronization.
Columns have different sizes, migrating the data may cause truncation.	This warning appears when comparing binary or text columns and source column has larger size than target column. If source contains records with longer data than target can contain, the data will be truncated in the target when synchronizing.
Columns have incompatible types.	These columns cannot be compared. Appears when data of the source column cannot be converted to the data type of the target column, for example TEXT and BINARY.
Source column allows Nulls which can not be stored in Target.	This warning appears when source column is nullable and target column is not. If source table contains data with nulls in this column, such data cannot be synchronized.
Data in timestamp columns cannot be synchronized.	This warning appears when target column has a timestamp data type. Such columns cannot be updated, so their values will not be synchronized.

Want to Find out More?

• Previous
• Next