Migration to v 10.0.0 fails in `pgloader` *SQLITE-OPTIONS* error

[ch3sn3k]

migration-assist:
Version:        0.3
CommitDate:     2024-09-18T12:34:27Z
GitCommit:      0cfc3e55802524e10f8c08b8e82f3f5598dd5168
GitTreeState:   clean
GoVersion:      go1.22.2
Compiler:       gc
Platform:       amd64/linux

#uname -a
Linux chat 5.10.0-32-amd64 #1 SMP Debian 5.10.223-1 (2024-08-10) x86_64 GNU/Linux

# lsb_release -a
No LSB modules are available.
Distributor ID: Debian
Description:    Debian GNU/Linux 11 (bullseye)
Release:        11
Codename:       bullseye

pgloader --version
pgloader version "3.6.022ae38"
compiled with SBCL 2.1.1.debian

# mysql -V
mysql  Ver 8.0.37 for Linux on x86_64 (MySQL Community Server - GPL)

root@chat:~/mattermost-migration# cat migration.load                                                                
LOAD DATABASE                                                                                                       
    FROM       mysql://root:<password>@127.0.0.1:3306/mattermost                                               
    INTO       pgsql://mmuser:<password>@127.0.0.1:5432/mattermost                                        

WITH data only,                                                                                                     
    workers = 8, concurrency = 1,                                                                                   
    multiple readers per thread, rows per range = 10000,                                                            
    prefetch rows = 10000, batch rows = 2500,                                                                       
    create no tables, create no indexes,                                                                            
    preserve index names                                                                                            

SET PostgreSQL PARAMETERS                                                                                           
    maintenance_work_mem to '128MB',                                                                                
    work_mem to '12MB'                                                                                              

SET MySQL PARAMETERS                                                                                                
    net_read_timeout  = '120',                                                                                      
    net_write_timeout = '120'                                                                                       

 CAST column Channels.Type to "channel_type" drop typemod,                                                          
    column Teams.Type to "team_type" drop typemod,                                                                  
    column UploadSessions.Type to "upload_session_type" drop typemod,                                               
    column ChannelBookmarks.Type to "channel_bookmark_type" drop typemod,                                           
    column Drafts.Priority to text,                                                                                 
    type int when (= precision 11) to integer drop typemod,                                                         
    type bigint when (= precision 20) to bigint drop typemod,                                                       
    type text to varchar drop typemod using remove-null-characters,                                                 
    type tinyint when (<= precision 4) to boolean using tinyint-to-boolean,                                         
    type json to jsonb drop typemod using remove-null-characters                                                    

EXCLUDING TABLE NAMES MATCHING ~<IR_>, ~<focalboard>, ~<calls>, 'schema_migrations', 'db_migrations', 'db_lock',    
    'configurations', 'configurationfiles', 'db_config_migrations'                                                  

BEFORE LOAD DO                                                                                                      
    $$ ALTER SCHEMA public RENAME TO mattermost; $$,                                                                
    $$ TRUNCATE TABLE mattermost.systems; $$,                                                                       
    $$ DROP INDEX IF EXISTS mattermost.idx_posts_message_txt; $$,                                                   
    $$ DROP INDEX IF EXISTS mattermost.idx_fileinfo_content_txt; $$                                                 

AFTER LOAD DO                                                                                                       
    $$ UPDATE mattermost.db_migrations set name='add_createat_to_teamembers' where version=92; $$,                  
    $$ ALTER SCHEMA mattermost RENAME TO public; $$,                                                                
    $$ SELECT pg_catalog.set_config('search_path', '"$user", public', false); $$,                                   
    $$ ALTER USER mmuser SET SEARCH_PATH TO 'public'; $$;                                                           

The command

 pgloader --verbose migration.load | tee migration.log
``

fails with 
```sh
What I am doing here?

At  WITH data only,
      workers = 8, concurrency = 1,

                                  ^ (Line 6, Column 32, Position 210)

In context SQLITE-OPTIONS:
While parsing SQLITE-OPTIONS. Expected:

It seems that multiple readers per thread is not known for the pgloader

Note: I want to do the migration on the same VM as a Mattermost server is running

Can you advice what to try next?

[isacikgoz]

Hey @ch3sn3k are you able to run migration with our pgloader docker image? It's a build that we tested thoroughly so it should work out of the box. https://hub.docker.com/r/mattermost/mattermost-pgloader/

[ch3sn3k]

Hello @isacikgoz, Thank you for the fast reaction. Unfortunately, the result is the same:

docker pull mattermost/mattermost-pgloader
docker run -it --rm -v $(pwd):/home/migration --network="host" mattermost/mattermost-pgloader:latest pgloader migration.load > migration.log

See migration.log

[isacikgoz]

No worries @ch3sn3k

Now reading the logs again, I see

In context SQLITE-OPTIONS:
While parsing SQLITE-OPTIONS.

wondering if there is anything odd for the database DSN values? Can you please check that? It looks like pgloader is trying to run on sqlite driver?

[ch3sn3k]

@isacikgoz Ok, the problem seems to be in the MariaDB DSN and the wrong authentication method. After I have changed mariadb to use

cat /etc/mysql/mariadb.conf.d/default-auth-override.cnf
# This file is automatically generated by MySQL Maintainer Scripts
[mysqld]
default-authentication-plugin = mysql_native_password

and updated the auth method for the mmuser as is written in your documentation https://docs.mattermost.com/deploy/postgres-migration.html#unsupported-authentication-for-mysql

the pgloader worked. I have used the docker container as you have recommended.

Once again, thank you for your help since the problem seems to be with authentication to the mysql database. I have also used special characters in the root user password like & and @, and these also mess up with pqloader

Now I am running on the postgress, so thank you for your assistance

[isacikgoz]

Glad that it worked out! Thanks for sharing your experience 💪