Migration from other databases to MySQL or MariaDB

Database migration tools

A generic database migration tool to MySQL and MariaDB is the MySQL Workbench.

An alternative is the old MySQL Migration Tool Kit.

Recommended by MariaDB Foundation: Sqlines.

Adabas D to MariaDB migration

Project migrating an Adabas D database to MariaDB is in progress. More information are expected before end of 2015.

Oracle to MySQL or MariaDB migration

Data Migration Tools

• MyOraDump is a tool to export data from an Oracle database to MySQL or MariaDB.
• Oracle-to-MySQL-DataMigrator
• SQLWays by Inspire
• Successful migration of Oracle to MySQL with utf8mb4 character set with SQL Workbench/J on command line

Exasol to Galera Cluster for MySQL migration

Exasol is a shared in-memory column store for data analysis.

• Data was extracted by the Exasol GUI as .CSV file.

• Structure was extracted by the Exasol GUI as DDL commands and adapted manually because we failed to use the MySQL Workbench.

• Data was loaded with the LOAD DATA INFILE command (20 Gbyte in about 70 minutes).

  LOAD DATA INFILE '/tmp/oli.csv'
  IGNORE INTO TABLE test.test
  FIELDS TERMINATED BY ','
  ENCLOSED BY '"'
  LINES TERMINATED BY "<br>n"
  (id, @data, ts, @d1, @d2)
  SET
  data = IF(@data='', NULL, @data),
  d1 = IF(@d1='', NULL, @d1),
  d2 = IF(@d2='', NULL, @d2)
  ;
  

Problems:

• MySQL Workbench on Linux requires the iODBC driver manager, Exasol provides an ODBC driver for unixODBC. On Max OS X Exasol supports iODBC, so we tried there…

• Exasol TIMESTAMP should be converted to MySQL DATETIME.

• Exasol TIMESTAMP starts with 1900-00-00 00:00:00 and ends with 9999-12-31.

• Exasol seems NOT to be a Generic SQL92 Compliant RDBMS. We used Generic RDBMS instead in the MySQL Workbench.

• Then we got the following MySQL Workbench error retrieving data. A bug at MySQL was filed (bug #77808).

  Starting...
  Connect to source DBMS...
  - Connecting to source...
  Connect to source DBMS done
  Check target DBMS connection...
  - Connecting to target...
  Reconnecting to Mysql@10.0.0.140:3306...
  Connection restablished
  Check target DBMS connection done
  Retrieve schema list from source....
  - Checking connection...
  - Fetching catalog names...
  Traceback (most recent call last):
    File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/db_generic_re_grt.py", line 715, in getCatalogNames
      return GenericReverseEngineering.getCatalogNames(connection)
    File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/db_generic_re_grt.py", line 193, in getCatalogNames
      return list(set(row[0] for row in cls.get_connection(connection).cursor().tables())) 
    File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/db_generic_re_grt.py", line 193, in 
      return list(set(row[0] for row in cls.get_connection(connection).cursor().tables())) 
  pyodbc.Error: ('HY000', '[HY000] [EXASOL][EXASolution driver]Character set conversion error or output buffer to small: Illegal byte sequence column 3, cursor position 751, codepage US-ASCII. (1) (SQLGetData)')
  
  Traceback (most recent call last):
    File "/Applications/MySQLWorkbench.app/Contents/Resources/libraries/workbench/wizard_progress_page_widget.py", line 192, in thread_work
      self.func()
    File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/migration_source_selection.py", line 456, in task_fetch_schemata
      self.main.plan.migrationSource.doFetchSchemaNames(only_these_catalogs)
    File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/migration.py", line 250, in doFetchSchemaNames
      catalog_names = self.getCatalogNames()
    File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/migration.py", line 214, in getCatalogNames
      return self._rev_eng_module.getCatalogNames(self.connection)
  SystemError: Error("('HY000', '[HY000] [EXASOL][EXASolution driver]Character set conversion error or output buffer to small: Illegal byte sequence column 3, cursor position 751, codepage US-ASCII. (1) (SQLGetData)')"): error calling Python module function DbGenericRE.getCatalogNames
  ERROR: Retrieve schema list from source: Error("('HY000', '[HY000] [EXASOL][EXASolution driver]Character set conversion error or output buffer to small: Illegal byte sequence column 3, cursor position 751, codepage US-ASCII. (1) (SQLGetData)')"): error calling Python module function DbGenericRE.getCatalogNames
  Failed
  

• MySQL sql_mode strict_trans_table made troubles with NULL values.

Other thoughts: Possibly the Inforbright or the InfiniDB storage engines are better suited to solve this task than a Galera Cluster for MySQL?