ERROR Database error 22007: invalid input syntax for type timestamp with time zone: "current_timestamp(6)"

[zap51]

Hi, Thank you for helping us with this tool. I was able to run pretty much but stuck when doing boards.load and the subsequent ones.

I used the below mentioned container images and the result has been the same: https://docs.mattermost.com/deploy/manual-postgres-migration.html#pull-the-docker-image-and-verify-pgloader https://pgloader.readthedocs.io/en/latest/install.html#docker-images

The error is as follows:

# pgloader boards.load
2024-08-17T04:35:55.012001Z LOG pgloader version "3.6.10~devel"
2024-08-17T04:35:55.092002Z LOG Migrating from #<MYSQL-CONNECTION mysql://mmuser@x.x.x.x:3306/mattermost {1006E3C1C3}>
2024-08-17T04:35:55.096002Z LOG Migrating into #<PGSQL-CONNECTION pgsql://mmuser@x.x.x.x:5432/mattermost {1006E3C393}>
2024-08-17T04:35:55.244005Z ERROR Database error 22007: invalid input syntax for type timestamp with time zone: "current_timestamp(6)"
QUERY: CREATE TABLE mattermost.focalboard_blocks 
(
  id          varchar(36) not null,
  insert_at   timestamptz not null default 'current_timestamp(6)',
  parent_id   varchar(36) default NULL,
  schema      bigint default NULL,
  type        text default NULL,
  title       text default NULL,
  fields      json default NULL,
  create_at   bigint default NULL,
  update_at   bigint default NULL,
  delete_at   bigint default NULL,
  root_id     varchar(36) default NULL,
  modified_by varchar(36) not null,
  channel_id  varchar(36) not null,
  created_by  varchar(36) not null,
  board_id    varchar(36) default NULL
);
2024-08-17T04:35:55.248005Z FATAL Failed to create the schema, see above.
KABOOM!
UNDEFINED-TABLE: Database error 42P01: relation "mattermost.focalboard_blocks" does not exist
QUERY: UPDATE mattermost.focalboard_blocks SET "fields" = '{}'::json WHERE "fields"::text = '';
An unhandled error condition has been signalled:
   Database error 42P01: relation "mattermost.focalboard_blocks" does not exist
QUERY: UPDATE mattermost.focalboard_blocks SET "fields" = '{}'::json WHERE "fields"::text = '';

2024-08-17T04:35:55.256005Z ERROR Database error 42P01: relation "mattermost.focalboard_blocks" does not exist
QUERY: UPDATE mattermost.focalboard_blocks SET "fields" = '{}'::json WHERE "fields"::text = '';

What I am doing here?

Database error 42P01: relation "mattermost.focalboard_blocks" does not exist
QUERY: UPDATE mattermost.focalboard_blocks SET "fields" = '{}'::json WHERE "fields"::text = '';

Looks like a known issue https://github.com/dimitri/pgloader/issues/1403

[agnivade]

Interesting. What is your MySQL version?

[zap51]

@agnivade

# mysql --version
mysql  Ver 15.1 Distrib 10.3.39-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

[agnivade]

I think that might be your problem. We only support MySQL, not MariaDB. I'd suggest migrating from MariaDB to MySQL first. And then re-attempt the migration.

[zap51]

@agnivade, Thanks for the update. Will migrate and get back.

[zap51]

Hi @agnivade, I created a sandbox env, copied our production DB from MariaDB to MySQL 8.0. Here's the version

# mysql --version
mysql  Ver 8.0.39-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

# SHOW server_version;
            server_version             
---------------------------------------
 12.19 (Ubuntu 12.19-0ubuntu0.20.04.1)
(1 row)

I was successfully able to use pgloader for migration of the rest but the boards.load resulted in one error. Please let me know if any further details are required.

# pgloader boards.load
2024-08-19T16:07:27.012000Z LOG pgloader version "3.6.10~devel"
2024-08-19T16:07:27.088001Z LOG Migrating from #<MYSQL-CONNECTION mysql://mmuser@x.x.x.x:3306/mattermost {1006E64473}>
2024-08-19T16:07:27.088001Z LOG Migrating into #<PGSQL-CONNECTION pgsql://mmuser@x.x.x.x:5432/mattermost {1006E64623}>
2024-08-19T16:07:30.812056Z ERROR Database error 22P02: invalid input syntax for type json
DETAIL: The input string ended unexpectedly.
CONTEXT: JSON data, line 1: 
COPY focalboard_blocks_history, line 276, column fields: ""
2024-08-19T16:07:30.872058Z LOG report summary reset
                                 table name     errors       rows      bytes      total time
-------------------------------------------  ---------  ---------  ---------  --------------
                                before load          0          1                     0.008s
                            fetch meta data          0         47                     0.084s
                             Create Schemas          0          0                     0.000s
                           Create SQL Types          0          0                     0.004s
                              Create tables          0         36                     0.052s
                             Set Table OIDs          0         18                     0.008s
-------------------------------------------  ---------  ---------  ---------  --------------
       mattermost.focalboard_blocks_history          1          0                     3.524s
               mattermost.focalboard_blocks          0      17848     7.8 MB          0.608s
        mattermost.focalboard_subscriptions          0       7317   703.3 kB          0.148s
       mattermost.focalboard_boards_history          0       7756    12.7 MB          1.032s
      mattermost.focalboard_category_boards          0       1375   191.7 kB          0.636s
               mattermost.focalboard_boards          0        330   486.0 kB          0.636s
   mattermost.focalboard_notification_hints          0          0                     0.572s
                mattermost.focalboard_teams          0          0                     0.564s
           mattermost.focalboard_categories          0        394    52.8 kB          0.008s
            mattermost.focalboard_file_info          0          0                     0.004s
              mattermost.focalboard_sharing          0          1     0.1 kB          0.008s
        mattermost.focalboard_board_members          0       1134    70.7 kB          0.012s
      mattermost.focalboard_system_settings          0          6     0.2 kB          0.008s
             mattermost.focalboard_sessions          0          0                     0.008s
mattermost.focalboard_board_members_history          0       1254   110.0 kB          0.068s
    mattermost.focalboard_schema_migrations          0         40     1.0 kB          0.052s
          mattermost.focalboard_preferences          0          0                     0.052s
                mattermost.focalboard_users          0          0                     0.052s
-------------------------------------------  ---------  ---------  ---------  --------------
                    COPY Threads Completion          0          8                     3.520s
                             Create Indexes          0         29                     0.444s
                     Index Build Completion          0         29                     0.008s
                            Reset Sequences          0          0                     0.012s
                               Primary Keys          0         17                     0.012s
                        Create Foreign Keys          0          0                     0.000s
                            Create Triggers          0          0                     0.000s
                            Set Search Path          0          1                     0.000s
                           Install Comments          0          0                     0.000s
                                 after load          0          8                     0.012s
-------------------------------------------  ---------  ---------  ---------  --------------
                          Total import time          1      37455    22.1 MB          4.008s

Thanks!

[agnivade]

cc @isacikgoz

[agnivade]

@isacikgoz - Just checking if you have any thoughts on this?

[isacikgoz]

Sorry this went out of my radar.. Since the fields data type in focalboard_blocks_history was type of text in MySQL it allowed values that don't comply to JSON structure. On Postgres however, it's strictly defined as JSON so a value somehow went in before the migration and it caused an issue during the migration.

I think we should just fix the problematic fields whenever similar thing occurs. Unfortunately that should never go into MySQL DB either. Probably some checks were missing in the app code.

[agnivade]

What would be the next steps for @zap51 to fix the incorrect rows. Is there any way to check them?

[isacikgoz]

I think the result of following query will help to check (this is an example for focalboard_blocks_history and fields)

SELECT id, fields FROM focalboard_blocks_history WHERE JSON_VALID(fields) = 0;

doc: https://dev.mysql.com/doc/refman/5.7/en/json-attribute-functions.html#function_json-valid