Error when Converting MySQL Table with Full-Text Index to Postgres Due to Index Size Limit

[slotix]

Summary:

Encountering an error when trying to convert records from a MySQL table containing a full-text index into a Postgres table due to an issue with the index size.

Error Message:

[2024/02/07 14:43:14] [error] target/target.go:361 failed to insert into table fulltext_index_table: ERROR: index row size 7136 exceeds btree version 4 maximum 2704 for index "IDX_fulltext_index_table_fulltext_index_table_data_index" (SQLSTATE 54000). Detail: Index row references tuple (0,1) in relation "fulltext_index_table".

Steps to Reproduce

Create Table in MySQL:

create table fulltext_index_table
(
    id         char(36)     not null  primary key,
    raw        longtext                     null ,
    data       longtext collate utf8mb4_bin null,
    created_at timestamp                    null,
    updated_at timestamp                    null,
    deleted_at timestamp                    null
)
    engine = InnoDB
    collate = utf8mb4_unicode_ci;

create created_at_index
    on fulltext_index_table(created_at);

create fulltext index fulltext_index_table_data_index
    on fulltext_index_table(data);

Insert random records into the source MySQL table.

INSERT INTO `fulltext_index_table` (`id`, `raw`, `data`, `created_at`, `updated_at`, `deleted_at`) VALUES
('00009ebe-2421-4a26-ae5e-e2bf6aa196f1',	'null	', '{\\\"uuid\\\":\\\"00009ebe-2421-4a26-ae5e-e2bf6aa196f1\\\",\\\"pureId\\\":94856500....', ,	'2022-12-31 18:42:49',	'2022-12-31 18:42:49',	'2022-12-31 18:42:49'),
('001dcb37-9071-48af-bcc4-9d6bd71081e6',	'null', '{\\\"uuid\\\":\\\"0000febe-3321-4a24-ae2e-e2bf6sa1963s\\\",\\\"pureId\\\":94856500....', ,	'2021-08-03 17:14:08',	'2021-08-03 17:14:08',	'2021-08-03 17:14:08');

Note that the actual size of the data field is approximately 40,000 symbols. It is trimmed here for brevity.

Send stream config to DBConvert Streams API endpoint:

curl --request POST --url http://0.0.0.0:8020/api/v1/streams\?file=./mysql2pg-fulltext_index_table.json

mysql2pg-fulltext_index_table.json contents

{
  "source": {
    "type": "mysql",
    "mode": "convert",
    "connection": "root:123456@tcp(0.0.0.0:3306)/source",
    "dataBundleSize": 40,
    "filter": {
      "tables": [
        {
          "name": "fulltext_index_table"
        }
      ]
    }
  },
  "target": {
    "type": "postgresql",
    "connection": "postgres://postgres:postgres@localhost:5433/postgres",
  }
}

Expected Behavior

The records should be inserted into the fulltext_index_table table on the Postgres target table without encountering any errors related to the index size.

Actual Behavior

Encountering the error mentioned above when attempting to insert records.

[slotix]

When creating an index in PostgreSQL without specifying the index type, the default and simplest btree index is used. In some cases, especially during migration from MySQL, you might encounter an error:

ERROR: index row size [X] exceeds btree version 4 maximum [Y] for index "[INDEX_NAME]" (SQLSTATE 54000).
Detail: Index row references tuple (0,1) in relation "[TABLE_NAME]".
This error occurs when the size of the indexed field is large, surpassing the maximum limit for a btree index.

To resolve this issue, we need to specify a different index type that can accommodate larger row sizes, such as GiST or GIN for PostgreSQL indexes targeted to eliminate this error.

CREATE INDEX idx_fulltext_index_table
ON fulltext_index_table
USING gin(to_tsvector('english', data));

[slotix]

There are several possible approaches for full-text indexes in PostgreSQL.

The new version 0.10.3 resolves the issue by converting MySQL full-text indexes to PostgreSQL tsvector columns.

-- Adding Full-Text Index
CREATE INDEX idx_articles_document
ON articles
USING gin(to_tsvector('english', title || ' ' || body));

Another approach is to skip index creation on the target using the new noCreateIndexes option. Read more about it at target configuration.

You can then create indexes manually.

[slotix]

Note: We've intentionally removed this from the latest release for more thorough testing.

Users will now receive a warning message, such as Full text index creation for PostgreSQL is not supported. Skipped index INDEX for TABLE.

Please recreate full text indexes manually until we reintroduce the feature in the upcoming releases.