General guidelines
Review limitations and implement suggested workarounds to successfully migrate data from MySQL, Oracle, or PostgreSQL to YugabyteDB.
Contents
-
Index on timestamp column should be imported as ASC (Range) index to avoid sequential scans
-
Exporting data with names for tables/functions/procedures using special characters/whitespaces fails
Index on timestamp column should be imported as ASC (Range) index to avoid sequential scans
GitHub: Issue #49
Description: If there is an index on a timestamp column, the index should be imported as a range index automatically, as most queries relying on timestamp columns use range predicates. This avoids sequential scans and makes indexed scans accessible.
Workaround: Manually add the ASC (range) clause to the exported files.
Example
An example schema on the source database is as follows:
CREATE INDEX ON timestamp_demo (ts);
Suggested change to the schema is to add the ASC
clause as follows:
CREATE INDEX ON timestamp_demo (ts ASC);
Exporting data with names for tables/functions/procedures using special characters/whitespaces fails
GitHub: Issue #636, Issue #688, Issue #702
Description: If you define complex names for your source database tables/functions/procedures using backticks or double quotes for example, `abc xyz` , `abc@xyz`, or "abc@123", the migration hangs during the export data step.
Workaround: Rename the objects (tables/functions/procedures) on the source database to a name without special characters.
Example
An example schema on the source MySQL database is as follows:
CREATE TABLE `xyz abc`(id int);
INSERT INTO `xyz abc` VALUES(1);
INSERT INTO `xyz abc` VALUES(2);
INSERT INTO `xyz abc` VALUES(3);
The exported schema is as follows:
CREATE TABLE "xyz abc" (id bigint);
The preceding example may hang or result in an error.
Importing with case-sensitive schema names
GitHub: Issue #422
Description: If you migrate your database using a case-sensitive schema name, the migration will fail with a "no schema has been selected" or "schema already exists" error(s).
Workaround: Currently, yb-voyager does not support case-sensitive schema names; all schema names are assumed to be case-insensitive (lower-case). If required, you may alter the schema names to a case-sensitive alternative post-migration using the ALTER SCHEMA command.
Example
An example yb-voyager import-schema command with a case-sensitive schema name is as follows:
yb-voyager import schema --target-db-name voyager
--target-db-hostlocalhost
--export-dir .
--target-db-password password
--target-db-user yugabyte
--target-db-schema "\"Test\""
The preceding example will result in an error as follows:
ERROR: no schema has been selected to create in (SQLSTATE 3F000)
Suggested changes to the schema can be done using the following steps:
-
Change the case sensitive schema name during schema migration as follows:
yb-voyager import schema --target-db-name voyager --target-db-hostlocalhost --export-dir . --target-db-password password --target-db-user yugabyte --target-db-schema test
-
Alter the schema name post migration as follows:
ALTER SCHEMA "test" RENAME TO "Test";