search

mattermost / migration-assist

A helper tool to automate MySQL -> Postgres migration for Mattermost
https://docs.mattermost.com/deploy/postgres-migration.html
2 stars 2 forks source link

ERROR mysql: 76 fell through ECASE expression - when using pgloader - I think we need a statically compiled pgloader to ship with this #29

Closed ylluminate closed 1 month ago

ylluminate commented 1 month ago

When using pgloader to migrate a Mattermost MySQL database to PostgreSQL, the following error occurs during the metadata fetching phase:

ERROR mysql: 76 fell through ECASE expression.
       Wanted one of (2 3 4 5 6 8 9 10 11 14 15 17 20 21 23 27 28 30 31 32 33
                      35 41 42 45 46 47 48 49 50 51 52 54 55 56 60 61 62 63 64
                      65 69 72 77 78 79 82 83 87 90 92 93 94 95 96 97 98 101
                      102 103 104 105 106 107 108 109 110 111 112 113 114 115
                      116 117 118 119 120 121 122 123 124 128 129 130 131 132
                      133 134 135 136 137 138 139 140 141 142 143 144 145 146
                      147 148 149 150 151 159 160 161 162 163 164 165 166 167
                      168 169 170 171 172 173 174 175 176 177 178 179 180 181
                      182 183 192 193 194 195 196 197 198 199 200 201 202 203
                      204 205 206 207 208 209 210 211 212 213 214 215 223 224
                      225 226 227 228 229 230 231 232 233 234 235 236 237 238
                      239 240 241 242 243 244 245 246 247 254).

This error suggests that pgloader encountered a MySQL column type or condition it wasn't programmed to handle, resulting in a fallback to an unhandled case.

MySQL Server version: 8.0.36-0ubuntu0.22.04.1 PostgreSQL version: psql (17.0 (Ubuntu 17.0-1.pgdg22.04+1), server 16.4 (Ubuntu 16.4-1.pgdg22.04+2)) pgloader version: pgloader version 3.6.7~devel

The only thing I've found semi-close to this issue appears to be with pgloader itself needing to be built from source to get the newest version.

There's a problem though, apparently, where Ubuntu 22.04 and 24.04 both ship with a lie. Their repo and installation info say they are shipping version 3.6.10-3 as you can verify here if you like: http://apt.postgresql.org/pub/repos/apt/pool/main/p/pgloader/

But when you extract them and run them to verify that they are newer versions, they are both OLD:

$ pgloader-extracted_3.6.10-3.pgdg22.04+1_amd64/usr/bin/pgloader --version
pgloader version "3.6.7~devel"
compiled with SBCL 2.1.11.debian

$ pgloader-extracted_3.6.10-3.pgdg24.04+1_amd64/usr/bin/pgloader --version
pgloader version "3.6.7~devel"
compiled with SBCL 2.2.9.debian

And unfortunately compilation via standard methods are now failing - at least on x86_64 Ubuntu 22.04...

We really need a static version of pgloader if that will indeed fix this issue:

Here's the full output:

$ pgloader --debug migration.load | tee migration.log
pgloader version 3.6.7~devel
compiled with SBCL 2.1.11.debian
sb-impl::*default-external-format* :UTF-8
tmpdir: #P"/tmp/pgloader/"
2024-10-21T04:11:08.007000Z NOTICE Starting pgloader, log system is ready.
2024-10-21T04:11:08.027001Z INFO Starting monitor
2024-10-21T04:11:08.035001Z LOG pgloader version "3.6.7~devel"
2024-10-21T04:11:08.080003Z INFO Parsed command:
LOAD DATABASE
    FROM       mysql://mmuser:PASSWORD@192.168.1.21:3306/mattermost
    INTO       pgsql://mmuser:PASSWORD@192.168.1.22: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", "$user", public', false); $$,
    $$ ALTER USER mmuser SET SEARCH_PATH TO '"$user", public'; $$;

2024-10-21T04:11:08.157005Z DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://mmuser@192.168.1.22:5432/mattermost {10083F7A93}>
2024-10-21T04:11:08.157005Z DEBUG SET client_encoding TO 'utf8'
2024-10-21T04:11:08.157005Z DEBUG SET maintenance_work_mem TO '128MB'
2024-10-21T04:11:08.157005Z DEBUG SET work_mem TO '12MB'
2024-10-21T04:11:08.157005Z DEBUG SET application_name TO 'pgloader'
2024-10-21T04:11:08.173006Z NOTICE Executing SQL block for before load
2024-10-21T04:11:08.204007Z DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://mmuser@192.168.1.22:5432/mattermost {10083F7A93}>
2024-10-21T04:11:08.204007Z DEBUG SET client_encoding TO 'utf8'
2024-10-21T04:11:08.204007Z DEBUG SET maintenance_work_mem TO '128MB'
2024-10-21T04:11:08.204007Z DEBUG SET work_mem TO '12MB'
2024-10-21T04:11:08.204007Z DEBUG SET application_name TO 'pgloader'
2024-10-21T04:11:08.209007Z DEBUG BEGIN
2024-10-21T04:11:08.209007Z SQL ALTER SCHEMA public RENAME TO mattermost;
2024-10-21T04:11:08.210007Z SQL TRUNCATE TABLE mattermost.systems;
2024-10-21T04:11:08.212008Z SQL DROP INDEX IF EXISTS mattermost.idx_posts_message_txt;
2024-10-21T04:11:08.213008Z SQL DROP INDEX IF EXISTS mattermost.idx_fileinfo_content_txt;
2024-10-21T04:11:08.219008Z LOG Migrating from #<MYSQL-CONNECTION mysql://mmuser@192.168.1.21:3306/mattermost {10083F6BC3}>
2024-10-21T04:11:08.220008Z LOG Migrating into #<PGSQL-CONNECTION pgsql://mmuser@192.168.1.22:5432/mattermost {10083F7A93}>
2024-10-21T04:11:08.271010Z DEBUG CONNECTED TO #<MYSQL-CONNECTION mysql://mmuser@192.168.1.21:3306/mattermost {10083F6BC3}>
2024-10-21T04:11:08.275010Z SQL MySQL: sending query: set net_read_timeout = 120;
2024-10-21T04:11:08.275010Z SQL MySQL: sending query: set net_write_timeout = 120;
2024-10-21T04:11:08.280010Z SQL MySQL: sending query: -- params: db-name
--         table-type-name
--         only-tables
--         only-tables
--         including
--         filter-list-to-where-clause incuding
--         excluding
--         filter-list-to-where-clause excluding
  select c.table_name, t.table_comment,
         c.column_name, c.column_comment,
         c.data_type, c.column_type, c.column_default,
         c.is_nullable, c.extra
    from information_schema.columns c
         join information_schema.tables t using(table_schema, table_name)
   where c.table_schema = 'mattermost' and t.table_type = 'BASE TABLE'

         and (table_name NOT REGEXP 'IR_' and table_name NOT REGEXP 'focalboard' and table_name NOT REGEXP 'calls' and table_name != 'schema_migrations' and table_name != 'db_migrations' and table_name != 'db_lock' and table_name != 'configurations' and table_name != 'configurationfiles' and table_name != 'db_config_migrations')
order by table_name, ordinal_position;
2024-10-21T04:11:08.323012Z ERROR mysql: 76 fell through ECASE expression.
       Wanted one of (2 3 4 5 6 8 9 10 11 14 15 17 20 21 23 27 28 30 31 32 33
                      35 41 42 45 46 47 48 49 50 51 52 54 55 56 60 61 62 63 64
                      65 69 72 77 78 79 82 83 87 90 92 93 94 95 96 97 98 101
                      102 103 104 105 106 107 108 109 110 111 112 113 114 115
                      116 117 118 119 120 121 122 123 124 128 129 130 131 132
                      133 134 135 136 137 138 139 140 141 142 143 144 145 146
                      147 148 149 150 151 159 160 161 162 163 164 165 166 167
                      168 169 170 171 172 173 174 175 176 177 178 179 180 181
                      182 183 192 193 194 195 196 197 198 199 200 201 202 203
                      204 205 206 207 208 209 210 211 212 213 214 215 223 224
                      225 226 227 228 229 230 231 232 233 234 235 236 237 238
                      239 240 241 242 243 244 245 246 247 254).
2024-10-21T04:11:08.323012Z NOTICE Executing SQL block for after load
2024-10-21T04:11:08.357013Z DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://mmuser@192.168.1.22:5432/mattermost {10083F7A93}>
2024-10-21T04:11:08.357013Z DEBUG SET client_encoding TO 'utf8'
2024-10-21T04:11:08.357013Z DEBUG SET maintenance_work_mem TO '128MB'
2024-10-21T04:11:08.357013Z DEBUG SET work_mem TO '12MB'
2024-10-21T04:11:08.357013Z DEBUG SET application_name TO 'pgloader'
2024-10-21T04:11:08.359013Z DEBUG BEGIN
2024-10-21T04:11:08.359013Z SQL UPDATE mattermost.db_migrations set name='add_createat_to_teamembers' where version=92;
2024-10-21T04:11:08.361013Z SQL ALTER SCHEMA mattermost RENAME TO public;
2024-10-21T04:11:08.362013Z SQL SELECT pg_catalog.set_config('search_path', '"$user", "$user", public', false);
2024-10-21T04:11:08.363013Z SQL ALTER USER mmuser SET SEARCH_PATH TO '"$user", public';
2024-10-21T04:11:08.364013Z LOG report summary reset
       table name     errors       read   imported      bytes      total time       read      write
-----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
      before load          0          4          4                     0.041s
  fetch meta data          0          0          0                     0.000s
-----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
-----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
       after load          0          4          4                     0.042s
-----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
Total import time          ✓          0          0                     0.042s
2024-10-21T04:11:08.443016Z INFO Stopping monitor
isacikgoz commented 1 month ago

Hey @ylluminate would you be able to use our docker image? You can get it from https://hub.docker.com/r/mattermost/mattermost-pgloader/ and it's the static version that should work out of the box.

ylluminate commented 1 month ago

Hmm, I don't know. I sure do hate me some Docker in the morning, afternoon, evening or any time... I'll have to see. We really need a statically compiled version handy to save the sanities.

agnivade commented 1 month ago

This is the Dockerfile: https://github.com/mattermost/mattermost-pgloader-docker/blob/main/Dockerfile that we use to compile. But if you are facing difficulties while compiling, we suggest to use the docker image for convenience. Feel free to choose any approach that works for you.

ylluminate commented 1 month ago

Thanks, yes, this is effectively the same approach I took, but the compilation was failing ad infinitum for Ubuntu 22.04 and 24.04. I did, however, see you're using the golang Docker image at golang:1.22.3 so this appears to be Debian 12 and thus there does likely appear to be a problem with building it on Ubuntu vs Debian.

I did manage to build it though after installing a Debian 12 system and emulating the environment as per the Dockerfiles for both golang and this.

For posterity, here's a download for the most recent two versions (note, both of these execute on Ubuntu 22.04 and likely newer):

I did get further via the tool, but still ran into problems - tried both the 3.6.9 version from here + the newer version and both failed with some potentially different nuances. Will report in a new ticket.