System Specifications

Limitations

PRIMARY KEYS

A primary key must be set for all MySQL tables for FlyData Sync to replicate.

UNIQUE KEYS

Unique keys are supported by FlyData. However, a table cannot only have unique keys and no primary keys. For FlyData Sync to replicate, a MySQL table will need to have an explicit primary key defined to go along with any unique keys.

RESERVED COLUMN NAMES

Redshift reserved the following names for system column names. These names may not be used in a user table.

  • oid
  • tableoid
  • xmin
  • cmin
  • xmax
  • cmax
  • ctid
  • deletexid
  • insertxid

If these name are used as a column name in the source database, FlyData adds an underscore “_” to its corresponding column on Redshift. (e.g. “oid” -> “_oid”)

CASE SENSITIVE TABLE/COLUMN NAME

Redshift does not support case sensitive table and/or column names. Case sensitive names in the source database are treated as lowercase on Redshift.

SPECIAL CHARACTERS IN COLUMN NAMES

Of the characters allowed in a MySQL column name, the following characters are not supported in Redshift and/or FlyData.

  • Non-ASCII characters – Redshift does not allow non-ASCII characters in a column name
  • Double quote (“) – Redshift does not allow the double quote character in a column name
  • Period (.) – FlyData does not support the period character in a column name

FlyData replaces each of these characters with an underscore when creating a corresponding table in Redshift. For example, a MySQL column “étudiant.honoré” becomes “_tudiant_honor_” in Redshift.

FOREIGN KEY CONSTRAINT

Foreign key constraints are not supported in FlyData nor Redshift. Queries including foreign key constraints are ignored and have no effect on Redshift.
Note that ON {DELETE|UPDATE} is not supported, either. If you sync a table with the constraint, data between MySQL and Redshift will be out of sync.

ALTER TABLE RENAME

At this point, FlyData does not support ALTER TABLE RENAME. When the query is run, user needs to re-register the table with the new name and sync again.

ALTER TABLE CHANGE COLUMN AND ALTER TABLE MODIFY COLUMN

ALTER TABLE CHANGE COLUMN is supported as long as it does not change the column type. Redshift does not allow changing the type of an existing column.
When the query is run to change a column type, the table’s sync must be reset.

ALTER TABLE {ADD|DROP} PRIMARY KEY

Redshift does not support ALTER TABLE {ADD|DROP} PRIMARY KEY. When the query is run, the table’s sync must be reset.

ALTER TABLE ORDER BY

ALTER TABLE ODER BY is not supported. When the query is run, the table’s Sync must be reset.

CREATE TABLE

CREATE TABLE is not supported. User can add a new table to sync manually, however, by adding the table from the data entry in the FlyData Console.

DROP TABLE

DROP TABLE is not supported. The corresponding table to a dropped table remains on Redshift as is.

PARTITION QUERIES

FlyData accepts and discards ALTER TABLE PARTITION queries without an error. However, some of the queries cause data inconsistency between MySQL and Redshift. When such a query is run, the table’s sync must be reset. The following queries cause data inconsistency.

  • DROP PARTITION
  • TRUNCATE PARTITION

The following queries are safe to run without causing data inconsistency.

  • ADD PARTITION
  • OPTIMIZE PARTITION
  • ANALYZE PARTITION
  • CHECK PARTITION
  • COALESCE PARTITION
  • REORGANIZE PARTITION
  • REMOVE PARTITIONING
STORAGE ENGINES

Only InnoDB storage engine is supported by FlyData. Other storage engines are not supported, but they may work with limitations except for Memory storage engine. Sync of a table using Memory storage engine will break when the MySQL database is shutdown or restarted.

VIEWS

Views cannot be synchronized. Only physical tables are supported with FlyData Sync.

ACCEPTED BUT IGNORED ALTER TABLE SUB-QUERIES

The following ALTER TABLE queries are ignored and have no effect on Redshift.

  • PARTITION queries (ADD, OPTIMIZE, ANALYZE, CHECK, COALESCE, REORGANIZE, REMOVE PARTITIONING
  • DROP FOREIGN KEY
  • DROP {KEY|INDEX}
  • ADD {FULLTEXT|SPATIAL} {KEY|INDEX}
  • {ENABLE|DISABLE} KEYS
  • ALTER COLUMN SET DEFAULT
  • ALTER COLUMN DROP DEFAULT
  • RENAME {KEY|INDEX}
  • FORCE
  • ALGORITHM
  • LOCK
  • ADD UNIQUE
  • NOT NULL column attribute in ADD, CHANGE, MODIFY or ALTER queries

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk