Migration from mysql to postgresql == MYSQL-UNSUPPORTED-AUTHENTICATION

Technical assistance
1

Infos:

  • Used Zammad version: 6.5.2
  • Used Zammad installation type: package
  • Operating system: Ubuntu 22.04.5
  • Browser + version: Firefox 150

Expected behavior:

  • “pgloader --dry-run …” should connect to mysql successfully

Actual behavior:

  • pgloader does not connect to mysql
    …
    FATAL Failed to connect to #<MYSQL-CONNECTION mysql://zammad@127.0.0.1:3306/zammad {1008299CA3}>: Failed to connect to mysql at “127.0.0.1” (port 3306) as user “zammad”: Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled.
    …

Steps to reproduce the behavior:

$ cat /tmp/pgloader-command
LOAD DATABASE
  FROM mysql://zammad:mypw1@127.0.0.1/zammad

  -- Adjust the PostgreSQL URL below to correct value before executing this command file.
  INTO pgsql://zammad:mypw2@127.0.0.1/zammad

ALTER SCHEMA 'zammad' RENAME TO 'public'

AFTER LOAD DO
  $$ alter table smime_certificates alter column email_addresses type varchar[] using translate(email_addresses::varchar, '[]', '{}')::varchar[] $$,
  $$ alter table pgp_keys alter column email_addresses type varchar[] using translate(email_addresses::varchar, '[]', '{}')::varchar[] $$,
  $$ alter table public_links alter column screen type varchar[] using translate(screen::varchar, '[]', '{}')::varchar[] $$,
  $$ alter table checklists alter column sorted_item_ids type varchar[] using translate(sorted_item_ids::varchar, '[]', '{}')::varchar[] $$,
  $$ alter table checklist_templates alter column sorted_item_ids type varchar[] using translate(sorted_item_ids::varchar, '[]', '{}')::varchar[] $$

WITH BATCH CONCURRENCY = 1
SET timezone = 'UTC'
SET client_timezone TO '00:00'

$ pgloader --verbose --dry-run /tmp/pgloader-command
2026-05-15T14:47:40.004000Z NOTICE Starting pgloader, log system is ready.
2026-05-15T14:47:40.008000Z LOG pgloader version "3.6.3~devel"
2026-05-15T14:47:40.012000Z LOG Data errors in '/tmp/pgloader/'
2026-05-15T14:47:40.012000Z LOG Parsing commands from file #P"/tmp/pgloader-command"
2026-05-15T14:47:40.036001Z LOG DRY RUN, only checking connections.
2026-05-15T14:47:40.036001Z LOG Attempting to connect to #<MYSQL-CONNECTION mysql://zammad@127.0.0.1:3306/zammad {1008299CA3}>
2026-05-15T14:47:40.052002Z FATAL Failed to connect to #<MYSQL-CONNECTION mysql://zammad@127.0.0.1:3306/zammad {1008299CA3}>: Failed to connect to mysql at "127.0.0.1" (port 3306) as user "zammad": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled.
2026-05-15T14:47:40.052002Z LOG Attempting to connect to #<PGSQL-CONNECTION pgsql://zammad@127.0.0.1:5432/zammad {100829AEB3}>
2026-05-15T14:47:40.084003Z LOG Success, opened #<PGSQL-CONNECTION pgsql://zammad@127.0.0.1:5432/zammad {100829AEB3}>.
2026-05-15T14:47:40.084003Z LOG Running a simple query: SELECT 1;
2026-05-15T14:47:40.084003Z LOG report summary reset
       table name     errors       read   imported      bytes      total time       read      write
-----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
-----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------

mysql> SELECT user, host, plugin FROM mysql.user WHERE user = 'zammad';
+--------+-----------+-----------------------+
| user   | host      | plugin                |
+--------+-----------+-----------------------+
| zammad | localhost | mysql_native_password |
+--------+-----------+-----------------------+

pgloader 3.6.3 and mysql 8.0.45 is installed.

I can login with user zammad to mysql successfully, but why does it not work pgloader?

2

Claude returns this, explicitly not verified nor qualified by me. It sounds plausible…:

That error comes from qmynd (the Common Lisp MySQL client). It only supports mysql_native_password authentication — modern MySQL (8.0+) defaults to   
  caching_sha2_password, which qmynd can't handle.                                                                                                      
                                                                                                                                                      
  Fix: change the zammad user's auth plugin on the MySQL server.                                                                                        
   
  ALTER USER 'zammad'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY '<password>';                                                                
  FLUSH PRIVILEGES;                                                                                                                                     
   
  If mysql_native_password isn't loaded (MySQL 8.4+ disables it by default), enable it in my.cnf:                                                       
  [mysqld]                                                                                                                                            
  mysql_native_password=ON                                                                                                                              
  …and restart mysqld.
3

Now it works.
First I set in /etc/mysql/mysql.conf.d/mysqld.cnf :
default_authentication_plugin=mysql_native_password

Due to mysql distinguishes between localhost and 127.0.0.1 I added to mysql :

CREATE USER 'zammad'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY 'mypw1';
GRANT ALL PRIVILEGES ON zammad.* TO 'zammad'@'127.0.0.1';
FLUSH PRIVILEGES;

The dry-run did run successfully, but the actual run showed and error, because my older Ubuntu comes with an older version of pgloader. After updating pgloader with the latest docker version, the the migration was successful.

1 Like