Schema Diff \ MySQL to PostgreSQL\ toSQL()\ Invalid Query

[vafrcor]

Doctrine\dbal version 2.5

I use Schema Comparator to synchronize between 2 database (MySQL into PostgreSQL). The query generator return this SQL Statement:

CREATE TABLE failed_jobs (
    id SERIAL NOT NULL, 
    connection TEXT NOT NULL COLLATE "utf8_unicode_ci", 
    queue TEXT NOT NULL COLLATE "utf8_unicode_ci", 
    payload TEXT NOT NULL COLLATE "utf8_unicode_ci", 
    failed_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL, 
    PRIMARY KEY(id)
);

When I run this SQL Statement on PostgreSQL database, there is a problem with COLLATE "utf8_unicode_ci" section.

The error displayed as below

DriverException in AbstractPostgreSQLDriver.php line 91: An exception occurred while executing 'CREATE TABLE job_tasks (id BIGSERIAL NOT NULL, type VARCHAR(255) NOT NULL COLLATE "utf8_unicode_ci", category VARCHAR(255) NOT NULL COLLATE "utf8_unicode_ci", start_date VARCHAR(255) NOT NULL COLLATE "utf8_unicode_ci", end_date VARCHAR(255) NOT NULL COLLATE "utf8_unicode_ci", data TEXT DEFAULT NULL COLLATE "utf8_unicode_ci", batch_total INT DEFAULT 0 NOT NULL, batch_pending INT DEFAULT 0 NOT NULL, batch_done INT DEFAULT 0 NOT NULL, batch_failed INT DEFAULT 0 NOT NULL, record_total INT DEFAULT 0 NOT NULL, record_pending INT DEFAULT 0 NOT NULL, record_done INT DEFAULT 0 NOT NULL, record_failed INT DEFAULT 0 NOT NULL, created_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, updated_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, PRIMARY KEY(id))':

SQLSTATE[42704]: Undefined object: 7 ERROR: collation "utf8_unicode_ci" for encoding "UTF8" does not exist
LINE 1: ...id BIGSERIAL NOT NULL, type VARCHAR(255) NOT NULL COLLATE "u...
^

Any solution for this SQL Generator?

[Ocramius]

@vafrcor what are you doing, exactly? Consider writing a test scenario.

[vafrcor]

@Ocramius

• I wanted to convert MySQL Database into PostgreSQL database.
• I have read about this reference to compare 2 database schema and generate SQL Queries for PostgreSQL Database.
• When I was tried to run the queries, the query wasn't work correctly on PostgreSQL (see above code preview for error message).

Any suggestion? Thank You

[Ocramius]

@vafrcor maybe make a gist (using DBAL only) to show what you are doing? We can then discuss a test case strategy starting from there...

[vafrcor]

ok. I will provide it shortly

[vafrcor]

@Ocramius : this is my snippet code.

<?php
//# This code used to synchronize MySQL database (Schema 1) into PostgreSQL database (Schema 2)

//# composer require vendors
require __DIR__ . '/vendor/autoload.php';

//# set configurations
$config1 = new \Doctrine\DBAL\Configuration();
$config2 = new \Doctrine\DBAL\Configuration();
$conn1_params = array(
    'dbname' => 'db_source',
    'user' => 'user',
    'password' => 'secret',
    'host' => 'localhost',
    'driver' => 'pdo_mysql'
);
$conn2_params = array(
    'dbname' => 'db_target',
    'user' => 'postgres',
    'password' => 'secret',
    'host' => 'localhost',
    'driver' => 'pdo_pgsql'
);
$db_conn1 = \Doctrine\DBAL\DriverManager::getConnection($conn1_params, $config1);
$db_conn2 = \Doctrine\DBAL\DriverManager::getConnection($conn2_params, $config2);
$schema_manager1=$db_conn1->getSchemaManager();
$schema_manager2=$db_conn2->getSchemaManager();
$schema1=$schema_manager1->createSchema();
$schema2=$schema_manager2->createSchema();

//# compare between 2 DB Schema
$comparator=new \Doctrine\DBAL\Schema\Comparator();
$schema_diff = $comparator->compare($schema2, $schema1);

$schema2_update_queries = $schema_diff->toSql($db_conn2->getDatabasePlatform());

//# print out Schema 2 update queries:
echo '<pre>';
print_r(var_export($schema2_update_queries));
echo '</pre>';

//# Problems:
// 1. If MySQL Table contain column which have "Collation", Query Statement not compatible with PostgreSQL standart
// 2. If MySQL Tables contain multiple index with same name (not unqie), Query Statement not compatible with PostgreSQL standart (duplicate index name)