dimitri
Mysql->Postgresql migration bug
Hi all.
this is my migration config: ` LOAD DATABASE FROM mysql://XXX_USER:XXX_USER_PASS@MYSQL_IP/db INTO postgresql://XXX2_USER:XXX2_USER_PASS@localhost/Test WITH no truncate, create tables, include drop, create indexes, reset sequences, foreign keys, downcase identifiers, uniquify index names, workers = 12, concurrency = 6, multiple readers per thread, rows per range = 20000 SET MySQL PARAMETERS net_read_timeout = '2800', net_write_timeout = '2800'
including only table names matching ~/aaa20220701/;
`
This is error: ` 2023-01-05T15:24:22.104002Z LOG Migrating from #<MYSQL-CONNECTION mysql://XXX_USER@MYSQL_IP:3306/db {1006547AA3}> 2023-01-05T15:24:22.104002Z LOG Migrating into #<PGSQL-CONNECTION pgsql://XXX2_USER@localhost:5432/Test {1006548613}> KABOOM! UNDEFINED-TABLE: Database error 42P01: relation "pg_sequence" does not exist CONTEXT: PL/pgSQL function inline_code_block line 25 at EXECUTE statement QUERY: DO $$ DECLARE n integer := 0; r record; BEGIN FOR r in SELECT 'select ' || trim(trailing ')' from replace(pg_get_expr(d.adbin, d.adrelid), 'nextval', 'setval')) || ', (select greatest(max(' || quote_ident(a.attname) || '), (select seqmin from pg_sequence where seqrelid = (''' || pg_get_serial_sequence(quote_ident(nspname) || '.' || quote_ident(relname), quote_ident(a.attname)) || ''')::regclass limit 1), 1) from only ' || quote_ident(nspname) || '.' || quote_ident(relname) || '));' as sql FROM pg_class c JOIN pg_namespace n on n.oid = c.relnamespace JOIN pg_attribute a on a.attrelid = c.oid JOIN pg_attrdef d on d.adrelid = a.attrelid and d.adnum = a.attnum and a.atthasdef WHERE relkind = 'r' and a.attnum > 0 and pg_get_expr(d.adbin, d.adrelid) ~ '^nextval' and c.oid in (select oid from reloids) LOOP n := n + 1; EXECUTE r.sql; END LOOP;
PERFORM pg_notify('seqs', n::text); END; $$; ^ An unhandled error condition has been signalled: Database error 42P01: relation "pg_sequence" does not exist CONTEXT: PL/pgSQL function inline_code_block line 25 at EXECUTE statement QUERY: DO $$ DECLARE n integer := 0; r record; BEGIN FOR r in SELECT 'select ' || trim(trailing ')' from replace(pg_get_expr(d.adbin, d.adrelid), 'nextval', 'setval')) || ', (select greatest(max(' || quote_ident(a.attname) || '), (select seqmin from pg_sequence where seqrelid = (''' || pg_get_serial_sequence(quote_ident(nspname) || '.' || quote_ident(relname), quote_ident(a.attname)) || ''')::regclass limit 1), 1) from only ' || quote_ident(nspname) || '.' || quote_ident(relname) || '));' as sql FROM pg_class c JOIN pg_namespace n on n.oid = c.relnamespace JOIN pg_attribute a on a.attrelid = c.oid JOIN pg_attrdef d on d.adrelid = a.attrelid and d.adnum = a.attnum and a.atthasdef WHERE relkind = 'r' and a.attnum > 0 and pg_get_expr(d.adbin, d.adrelid) ~ '^nextval' and c.oid in (select oid from reloids) LOOP n := n + 1; EXECUTE r.sql; END LOOP;
PERFORM pg_notify('seqs', n::text);
END;
$$;
`
I have the same issue error 42P01: relation "pg_sequence" does not exist were you able to solve this?
