Error Migrating from MySQL to PostgreSQL after Converting

[bunlongheng]

#1

mysqldump --xml -u root benu-local > m.sql
#2

php convertor.php -i m.sql -o p.sql
#3

psql -U bheng -d portal -f p.sql
#4

Result

DROP TABLE
CREATE TABLE
psql:p.sql:11: NOTICE:  index "caches_caches_key_unique" does not exist, skipping
DROP INDEX
CREATE INDEX
INSERT 0 1
DROP TABLE
psql:p.sql:56: ERROR:  date/time field value out of range: "0000-00-00 00:00:00"
psql:p.sql:57: ERROR:  relation "captive_portals_id_seq" does not exist
LINE 1: SELECT setval('"captive_portals_id_seq"', 13, true);
                      ^
psql:p.sql:63: ERROR:  relation "captive_portals" does not exist
LINE 1: INSERT INTO "captive_portals" ("id","cpe_mac","power_by","po...
                    ^
DROP TABLE
psql:p.sql:76: ERROR:  date/time field value out of range: "0000-00-00 00:00:00"
psql:p.sql:77: ERROR:  relation "captures_id_seq" does not exist
LINE 1: SELECT setval('"captures_id_seq"', 66, true);
                      ^
psql:p.sql:80: ERROR:  relation "captures" does not exist
LINE 1: INSERT INTO "captures" ("id","type","cpe_mac","device_mac","...
                    ^
DROP TABLE
psql:p.sql:97: ERROR:  date/time field value out of range: "0000-00-00 00:00:00"
psql:p.sql:98: ERROR:  relation "cloud_securities_id_seq" does not exist
LINE 1: SELECT setval('"cloud_securities_id_seq"', 1, true);
                      ^
DROP TABLE
psql:p.sql:115: ERROR:  date/time field value out of range: "0000-00-00 00:00:00"
psql:p.sql:116: ERROR:  relation "devices_id_seq" does not exist
LINE 1: SELECT setval('"devices_id_seq"', 190, true);
                      ^
psql:p.sql:205: ERROR:  relation "devices" does not exist
LINE 1: INSERT INTO "devices" ("id","account_id","name","set_name","...
                    ^
DROP TABLE
psql:p.sql:225: ERROR:  date/time field value out of range: "0000-00-00 00:00:00"
psql:p.sql:226: ERROR:  relation "externals_id_seq" does not exist
LINE 1: SELECT setval('"externals_id_seq"', 2, true);
                      ^
psql:p.sql:228: ERROR:  relation "externals" does not exist
LINE 1: INSERT INTO "externals" ("id","logo_path","favicon_path","co...
                    ^
DROP TABLE
CREATE TABLE
INSERT 0 52
DROP TABLE
psql:p.sql:305: ERROR:  date/time field value out of range: "0000-00-00 00:00:00"
psql:p.sql:306: ERROR:  relation "mirror_settings_id_seq" does not exist
LINE 1: SELECT setval('"mirror_settings_id_seq"', 2, true);
                      ^
psql:p.sql:308: ERROR:  relation "mirror_settings" does not exist
LINE 1: INSERT INTO "mirror_settings" ("id","server","port","timeout...
                    ^
DROP TABLE
psql:p.sql:323: ERROR:  date/time field value out of range: "0000-00-00 00:00:00"
psql:p.sql:324: ERROR:  relation "notifications_id_seq" does not exist
LINE 1: SELECT setval('"notifications_id_seq"', 8, true);
                      ^
psql:p.sql:335: ERROR:  relation "notifications" does not exist
LINE 1: INSERT INTO "notifications" ("id","account_id","name","type"...
                    ^
DROP TABLE
psql:p.sql:351: ERROR:  date/time field value out of range: "0000-00-00 00:00:00"
psql:p.sql:352: ERROR:  relation "operators_id_seq" does not exist
LINE 1: SELECT setval('"operators_id_seq"', 2, true);
                      ^
psql:p.sql:354: ERROR:  relation "operators" does not exist
LINE 1: INSERT INTO "operators" ("id","power_by","power_by_bg","powe...
                    ^
DROP TABLE
psql:p.sql:377: ERROR:  date/time field value out of range: "0000-00-00 00:00:00"
psql:p.sql:378: ERROR:  relation "promotions_id_seq" does not exist
LINE 1: SELECT setval('"promotions_id_seq"', 8, true);
                      ^
psql:p.sql:384: ERROR:  relation "promotions" does not exist
LINE 1: INSERT INTO "promotions" ("id","cpe_mac","name","type","stat...
                    ^
DROP TABLE
psql:p.sql:402: ERROR:  date/time field value out of range: "0000-00-00 00:00:00"
psql:p.sql:403: ERROR:  relation "users_id_seq" does not exist
LINE 1: SELECT setval('"users_id_seq"', 225, true);
                      ^
psql:p.sql:604: ERROR:  relation "users" does not exist
LINE 1: INSERT INTO "users" ("id","account_id","email","fb_email","t...
                    ^
psql:p.sql:629: ERROR:  relation "users" does not exist
LINE 1: INSERT INTO "users" ("id","account_id","email","fb_email","t...
                    ^
DROP TABLE
psql:p.sql:683: ERROR:  date/time field value out of range: "0000-00-00 00:00:00"
psql:p.sql:684: ERROR:  relation "visitors_id_seq" does not exist
LINE 1: SELECT setval('"visitors_id_seq"', 15, true);
                      ^
psql:p.sql:703: ERROR:  relation "visitors" does not exist
LINE 1: INSERT INTO "visitors" ("id","firstname","lastname","email",...

[mihailShumilov]

can You attach Your dump? or send directly to me?

[bunlongheng]

I already solved my issue. I ended up fixing manually alot.

[mihailShumilov]

Can You send pull request with fixes?

[Noxilie]

Had the same problem.
fixed by adding "set search_path" directive at the start of the converted dump:
SET search_path TO %schemaname%;

In fact it would be nice if converter could accept name of the schema as argument... :)

[mihailShumilov]

Got point
I will add this soon