How to read PostgreSQL database dump with multiple tables into mySQL

Question

I am fairly new to mySQL. Today, I practiced reading in .sql file using a tutorial (http://www.elated.com/articles/mysql-for-absolute-beginners/). The .sql file was called books.sql, and just contained one table with no records:

USE bookstore;

DROP TABLE IF EXISTS books;
CREATE TABLE books
(
  id              int unsigned NOT NULL auto_increment, # Unique ID for the record
  title           varchar(255) NOT NULL,                # Full title of the book
  author          varchar(255) NOT NULL,                # The author of the book
  price           decimal(10,2) NOT NULL,               # The price of the book

  PRIMARY KEY     (id)
);

Now, I am trying to read in a .sql file called db.sql. This contains about 200 lines of header lines and several table and field listings. Then, it contains the actual records. It is very large (751,933 lines).

What I tried to do was open mysql, create a database, and read in this db.sql file. I was hoping to have all the tables (with their records) from db.sql placed into this database:

mysql -u root
create database myDataBase;
source /path/db.sql

I received a long list of errors that began with:

ERROR 1193 (HY000): Unknown system variable 'statement_timeout'
ERROR 1193 (HY000): Unknown system variable 'client_encoding'
ERROR 1193 (HY000): Unknown system variable 'standard_conforming_strings'
ERROR 1193 (HY000): Unknown system variable 'check_function_bodies'
ERROR 1193 (HY000): Unknown system variable 'client_min_messages'
ERROR 1193 (HY000): Unknown system variable 'escape_string_warning'
ERROR 1193 (HY000): Unknown system variable 'search_path'
ERROR 1193 (HY000): Unknown system variable 'default_tablespace'
ERROR 1193 (HY000): Unknown system variable 'default_with_oids'
ERROR 1046 (3D000): No database selected
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SEQUENCE academic_academic_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAX' at line 1

And then ended with repeated statements of:

ERROR 2005 (HY000): Unknown MySQL server host 'corey.vorland        0' (0)
No connection. Trying to reconnect...
ERROR 2005 (HY000): Unknown MySQL server host 'corey.vorland        0' (0)
ERROR: 
Can't connect to the server

The beginning of the db.sql file is as follows:

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: academic; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE academic (
    academic_id integer NOT NULL,
    family_name text,
    given_name text,
    other_names text,
    standrews_link text,
    comments text,
    reference text,
    descendant_count integer
);

And also

--
-- Name: academic_academic_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE academic_academic_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


--
-- Name: academic_academic_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE academic_academic_id_seq OWNED BY academic.academic_id;


--
-- Name: academic_academic_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
--

SELECT pg_catalog.setval('academic_academic_id_seq', 190374, true);


--
-- Name: advises; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE advises (
    advisor integer NOT NULL,
    advisee integer NOT NULL,
    advice_type character varying(20),
    degree integer NOT NULL
);

I tried a few things, such as removing some of the header lines from db.sql before the first CREATE TABLE command, but that still had many errors. As I am very inexperienced with databases and SQL, I am at a lost as to how to trouble-shoot this. I am assuming there is some issue about this db.sql file apparently coming from PostgreSQL and there being multiple tables that is causing a problem with me trying to read this db.sql file into mysql with the source command? Is it even possible for me to read this into mysql?

Thank you for any advice.

Is there a specific reason why you want to get it into MySQL and can't just use PostgreSQL? — Sami Kuhmonen
– Sami Kuhmonen, Commented May 28, 2015 at 6:32
It is just my opinion, but Postgresql is on another planet. Learn and use it if you can. — user_0
– user_0, Commented May 28, 2015 at 7:41
SQL is (somewhat unfortunately), a set of incompatible dialects. You can't just load a database dump from PostgreSQL or MySQL or vice versa. — Craig Ringer
– Craig Ringer, Commented May 28, 2015 at 10:38
Thank you all. I am getting lots of errors trying to convert from PostgreSQL to MySQL. I did not think about using PostgreSQL to be honest. I will give it a try now... — user2808302
– user2808302, Commented May 28, 2015 at 17:39

BK435 · Accepted Answer · 2015-05-27 21:12:44Z

2

As you have noticed you cannot just import a .sql file from one database engine to another. There are syntactical difference in the dump output that will cause errors when importing the file. You must first convert the dump file from postgre syntax/format to mysql compliant syntax/format.

Luckly, you are not the first (nor I believe to be the last) to encounter this issue.

First you should take a look at convert postgresql dump to mysql.

You will notice that the second step is to use a PHP function that called 'pg2mysql'. It accepts one input (the postgres code to be converted) and returns the mysql code.

For more information on this useful tool, I will refer you to the creator's github and website. On the website you can actually use the textarea provided to convert from PG to Mysql. Although, using the textarea conversion is not recommended for larger files.

answered May 27, 2015 at 21:12

BK435

3,1863 gold badges22 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user2808302 Over a year ago

Wow, I am really grateful for your help! If i am understanding this correctly, it seems I would have to run three commands: 1) psql dbname < ./db.sql, 2) pg_dump dbname --inserts --format p -f ./newdb.sql 3) php pg2mysql_cli.php ./newdb.sql ./newdb_mysql.sql.

user2808302 Over a year ago

However, when I even run the first command (1) above, I get the error: "psql: FATAL: database "dbname" does not exist". I am unsure what dbname should be? I assumed that was the output. I only have one input (I think) that is the db.sql file, which I call in that same command. Any help would be greatly appreciated. I apologize for my lack of savviness with interpreting some of this.

user2808302 Over a year ago

I am worried now. All I received was the db.sql file! Yes, I could see it was a placeholder name, but how do I know what I should put in that placeholder? It may seem obvious to you, but I am not sure.

user2808302 Over a year ago

In case this is adding to the confusion, I should also add that I received the db.sql file from a contact from a website that keeps this database. I did not create it myself.

BK435 Over a year ago

ask your contact what the Database Name is then...or look for something that looks like "create table dbname.tablename..."

Collectives™ on Stack Overflow

How to read PostgreSQL database dump with multiple tables into mySQL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related