dolthub / pg2mysql Goto Github PK

Script to convert Postgres dump files to MySQL dump files

License: Apache License 2.0

Perl 34.61% Shell 65.39%

mysql postgresql postgres mysqldump pgdump

pg2mysql's Introduction

pg2mysql

pg2mysql transforms a pgdump file on STDIN into a MySQL dump file on STDOUT. Dump format must be INSERT statements, not binary or COPY statements (ie. pg_dump --insert <dbname>).

Usage:

./pg2mysql.pl < file.pgdump > mysql.sql
./pg2mysql.pl --skip table1 --skip table2 --insert_ignore < file.pgdump > mysql.sql 2>warnings.txt

It handles:

CREATE TABLE statements, types converted to MySQL equivalents
INSERT INTO statements, some values (like timestamp strings) massaged to work with MySQL
CREATE INDEX statements
ALTER TABLE statements (for foreign keys, other constraints)
Sequence nextval column defaults translated to auto_increment
With --insert_ignore, uses INSERT IGNORE statements to be more lenient with non-confirming values (at the cost of import accuracy)

All other statement types other than the ones above are ignored, and echoed as SQL comments to STDERR. If you want them in your final script, redirect STDERR to STDOUT with 2 >&1 when running the script.

Known issues

The script has a lot of limitations and there are surely bugs. If you find some, file an issue to tell us. But these are the things we know about:

Only tables supported. Other schema entities like triggers, views, stored procedures are not created
Some types badly / not supported
Will convert a character varying type to longtext if no length is specified, which means MySQL won't be able to make it a key

Requirements

You must have Perl installed at /usr/bin/perl to run the script directly.

Credits

pg2mysql is heavily inspired and informed by this project of the same name: https://github.com/ChrisLundquist/pg2mysql

Which in turn was adapated from this web form: http://www.lightbox.ca/pg2mysql.php

pg2mysql's People

Contributors

Stargazers

Watchers

Forkers

isabella232 osztosa devseraji alexrogalskiy oz sohonet quentium-forks ekosha4ka phpbot s0uvik-r0y

pg2mysql's Issues

fields named serial are incorrectly converted

Likely field name serial is being treated as if it's a serial type https://github.com/dolthub/pg2mysql/blob/main/pg2mysql.pl#L167

$ cat test.pgsql
CREATE TABLE public.dcim_device (
    id uuid NOT NULL,
    created date,
    serial character varying(255) NOT NULL,
);

$ perl pg2mysql.pl < test.pgsql
--
-- Generated by pg2mysql
--
set foreign_key_checks = off;
--
DROP DATABASE IF EXISTS public;
CREATE DATABASE public;
CREATE TABLE public.dcim_device (
    `id` varchar(36) NOT NULL,
    `created` date,
    `integer` auto_increment varchar(255) NOT NULL,
);

LIKE ~~ operators not supported

We want to translate PostgreSQL dump into MySQL (for MariaDB ultimatively) and from a ~2GB dump we saw that some researchers use the Postgres LIKE operator as ~~ and NOT LIKE !~~ also. Could you add support for those? I manually translated them now with sed.

Thank you for your project!

Support Numeric Array types like `int[]`

It's possible we could just convert these to JSON types. Seems like similar syntax.

Add bats tests

This project is perfect for bats tests.

Check in pg_dumps we want to convert
Using bats, run the conversion
Use assertions to verify the results.

Add option to change schema name

Since in Postgres a database contains one or more named schemas and in MySQL schema=database, when converting from Postgres, add an option to set the database name for MySQL.

In our case, we have a database named "testdb" and the schema named "public", but in MySQL we only have the schema "testdb", however in the converted file it will keep the Postgres schema name.

Right now we are using sed to replace:

sed -i -e 's/\s${POSTGRES_SCHEMA};/ ${MYSQL_DB};/gi' "${MYSQL_DUMP}.sql"
sed -i -e 's/\s${POSTGRES_SCHEMA}\./ ${MYSQL_DB}\./gi' "${MYSQL_DUMP}.sql"

interval type fields not supported

I'm not sure what these should be converted to, but internal isn't a valid type in mysql

$ cat test.pgsql
CREATE TABLE public.dcim_device (
    id uuid NOT NULL,
    created date,
    diff_time interval,
);

$ perl pg2mysql.pl < test.pgsql
--
-- Generated by pg2mysql
--
set foreign_key_checks = off;
--
DROP DATABASE IF EXISTS public;
CREATE DATABASE public;
CREATE TABLE public.dcim_device (
    `id` varchar(36) NOT NULL,
    `created` date,
    `diff_time` interval,
);

Translate `setval` to `auto_increment` syntax

SELECT pg_catalog.setval('public.my_table_id_seq', 33, true);

Above statement remains in stdout output, but MySQL does not support setval function. This errors with:

SELECT pg_catalog.setval('public.my_table_id_seq', 33, true): function: 'setval' not found
function: 'setval' not found

Should be translated to auto_increment syntax.

Explicitly handle statement opening and closing

The approach currently in this script is to process dumps line by line switching between cases when a new SQL statement is matched. For instance, we handle a CREATE TABLE statement until we see an INSERT statement. We make no attempt to determine the end of statements.

The issue here is things like functions have BEGIN/END blocks where INSERTS or CREATES can be defined.

We need to make an attempt to determine the end of statements so we get a better state machine.

Carriage Return not escaped in JSON strings

i.e.

$ cat test.pgsql
INSERT INTO public.extras_objectchange VALUES ('feadb65c-4721-4fc1-815c-0675a7c1ff93', '2023-07-28 10:31:18.364849+01', 'johan.vandendorpe', 'c9feb6aa-828b-4c01-ab7c-2b1674a80900', 'create', '{"physical_address": "900 Athena Drive\r\n30605\r\nUnited States\r\n", "shipping_address": ""}');

$ perl pg2mysql.pl < test.pgsql
--
-- Generated by pg2mysql
--
set foreign_key_checks = off;
--
line 1 ended, num quotes is 12 and in_insert is 0
marking statement ended at pg2mysql.pl line 404, <> line 1.
INSERT INTO public.extras_objectchange VALUES ('feadb65c-4721-4fc1-815c-0675a7c1ff93', '2023-07-28 10:31:18.364849', 'johan.vandendorpe', 'c9feb6aa-828b-4c01-ab7c-2b1674a80900', 'create', '{"physical_address": "900 Athena Drive\r\\n30605\r\\nUnited States\r\\n", "shipping_address": ""}');

Wrong datetime format

On some datetime values that are exported we have the following error:

ERROR 1292 (22007) at line 677: Incorrect datetime value: '2022-01-31 16:18:56+01'

Right now we are using sed to replace:

sed -i -e 's/\+[[:digit:]]\{2\}//gi' "${MYSQL_DUMP}.sql"

Check constraints using ARRAY types are mangled

pg_dump:

CONSTRAINT npc_monter_type_check CHECK ((npc_monster_type = ANY (ARRAY['UNDEF'::text, 'NORMAL'::text, 'RARE'::text, 'HEROIC'::text, 'CITADEL'::text]))),

Converted to:

CONSTRAINT npc_monter_type_check CHECK ((npc_monster_type = ANY (ARRAY['UNDEF',

Create Table definition gets extra commas.

pg_dump:

CREATE TABLE common.bank_links (
    factory_id integer NOT NULL,
    client_condition_visible text DEFAULT 'Enabled()'::text NOT NULL,
    target_payment_id json DEFAULT '{}'::json NOT NULL,
    target_group_id text DEFAULT ''::text NOT NULL,
    icon text DEFAULT ''::text NOT NULL,
    icon_text text DEFAULT ''::text NOT NULL,
    back_visual json DEFAULT '{}'::json NOT NULL,
    button_text text DEFAULT ''::text NOT NULL,
    client_condition_visible_bin text DEFAULT ''::text
);

Script generates:

`CREATE TABLE common.bank_links (
    `factory_id` integer NOT NULL,
    `client_condition_visible` longtext',
    `target_payment_id` json DEFAULT '{}',
    `target_group_id` longtext,,
    `icon` longtext,,
    `icon_text` longtext,,
    `back_visual` json DEFAULT '{}',
    `button_text` longtext,,
    `client_condition_visible_bin` longtext,,
);`

Hex format for MySQL

There is an issue when importing hex into MySQL comming from a Postgres dump.

Example in Postgres dump:

INSERT INTO public.ipaddress VALUES ('4f6ae4b5-8eb1-4966-8457-e19d9c19850d', '\x7ee572fa');

Result in MySQL:

+----------------------------------+----------------------+
| id                               | host                 |
+----------------------------------+----------------------+
| 00015636142b499ea8a782606466f4b7 | 0x783765653537326661 |
+----------------------------------+----------------------+

What works inserting in MySQL:

INSERT INTO public.ipaddress VALUES ('4f6ae4b5-8eb1-4966-8457-e19d9c19850d', X'7ee572fa');