We've inherited a site running Postgres - oh joy!

So i've been looking at using DBTNG to convert the pgsql to mysql. Before having a go at converting the the postgres db, I'm just trying out some tests on my local machine using MAMP.

I have a test drupal installation and have created a new, empty database.
I've added the database settings to settings.php and the databases appear in the UI.

1) When I run a test to migrate the Drupal default DB to my test database I get the following... testdb is the name of my test destination database.

variable_store does not existing in destination
PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'testdb.block' doesn't exist: SELECT b.* FROM {block} b WHERE (b.theme = :db_condition_placeholder_0) AND (b.status = :db_condition_placeholder_1) ORDER BY b.region ASC, b.weight ASC, b.module ASC; Array ( [:db_condition_placeholder_0] => seven [:db_condition_placeholder_1] => 1 ) in _block_load_blocks() (line 727 of /Applications/MAMP/htdocs/drupal-base/modules/block/block.module).

2) With regards to getting going on the Postgres installation - is it possible to use a dump files as the source database?
as the README files seems to suggest this by pointing to a files in the sites/default/files directory - 'database' => 'sites/default/files/.ht.sqlite',
3) If this is so, how does the DBTNG read the files without the postgres php drivers?
4) Or do I need to have postgres running on my machine to convert the database?

Hope someone can help - many thanks

Comments

hixster’s picture

hixster commented

ignore point 3 - it looks like i have PDO pgsql installed

josh waihi’s picture

josh waihi commented

Hi hixster,
To migrate from PostgreSQL to MySQL you'll need your Drupal site to be able to connect to both servers. Both servers must already have there databases created. Since PostgreSQL will have the Drupal site already on it, you'll need to create a MySQL database to migrate too. You can find out about how to do that in INSTALL.mysql.txt in Drupal's root directory. But since you're moving to MySQL, I assume you already know how to do that part.

The file you're referring to in #3 is actually a SQLite database. For that to work you must be trying to migrate to or from a SQLite database.

I guessing you tried this through the UI? Have you tried it through drush? It seems a bit more reliable given you usually have more memory and time available.

hixster’s picture

hixster commented

Hey Josh, thanks for coming back to me on this so quickly and thanks for the pointers. I see how this works now - i'm just trying to get a local version of the site up with the postgres database and then will come back and try again. getting loads of unserialize() offset errors at the moment, but nothing to do with this module.
I'll come back once I have a working versions of the sites and databases. Thanks again!

hixster’s picture

hixster commented

O.k. so i've now got my site up and running and fully working on Postgres. i have created an empty mySQL database.

When I run the database check, i just get the error: The website encountered an unexpected error. Please try again later.
and that's it.

If i completely ( and irresponsibaly) ignore the check and just try to run the conversion, I get the following error...

An AJAX HTTP error occurred. HTTP Result Code: 500 Debugging information follows. Path: /htdocs/xxxxxx/batch?render=overlay&id=21&op=do StatusText: Internal Server Error ResponseText: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 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 'DEFAULT NULL COMMENT 'The transaction status, pending/authorised/canceled/failed' at line 5: CREATE TABLE {purchases} ( `pid` INT unsigned NOT NULL auto_increment COMMENT 'Primary key for the purchases table.', `nid` INT unsigned NOT NULL COMMENT 'Node id foreign key.', `amount` DECIMAL(10, 2) NOT NULL DEFAULT 0 COMMENT 'The transaction amount.', `status` VARCHAR DEFAULT NULL COMMENT 'The transaction status, pending/authorised/canceled/failed.', `response` TEXT DEFAULT NULL COMMENT 'Serialised payment response array.', `transaction_date` INT DEFAULT NULL COMMENT 'A unix timestamp containing the transaction date.', PRIMARY KEY (`pid`) ) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COMMENT 'Stores purchase information.'; Array ( ) in db_create_table() (line 2685 of /Applications/MAMP/htdocs/xxxxxx/includes/database/database.inc).

Really not sure where to start with this.??

Edit -> I should say that the first 4 tables get converted prior to the conversion bailing - these are block, block_custom, block_role and cache_block.

josh waihi’s picture

josh waihi commented

What version of MySQL? Does it match the minimum requirements to run Drupal 7? That looks like the MySQL database isn't accepting the syntax Drupal is generating. Can you run that create table command manually?

CREATE TABLE purchases (
  `pid` INT unsigned NOT NULL auto_increment COMMENT 'Primary key for the purchases table.',
  `nid` INT unsigned NOT NULL COMMENT 'Node id foreign key.',
  `amount` DECIMAL(10, 2) NOT NULL DEFAULT 0 COMMENT 'The transaction amount.',
  `status` VARCHAR DEFAULT NULL COMMENT 'The transaction status, pending/authorised/canceled/failed.',
  `response` TEXT DEFAULT NULL COMMENT 'Serialised payment response array.',
  `transaction_date` INT DEFAULT NULL COMMENT 'A unix timestamp containing the transaction date.',
  PRIMARY KEY (`pid`)
) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COMMENT 'Stores purchase information.'
hixster’s picture

hixster commented

Hi Josh, thanks for the help - yes, I have Drupal 7 running on this version of mySQL. From my phpinfo it looks like i am running mysql 5.5.9.

I tried running that SQL statement in phpmyadmin and got the following:

#1064 - 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 'DEFAULT NULL COMMENT 'The transaction status, pending/authorised/canceled/failed' at line 5

hixster’s picture

hixster commented

HI josh, any thoughts?

josh waihi’s picture

josh waihi commented

Yes, can you install and run Drupal fine on the MySQL Server your trying to use? The issue your currently experiencing is not really with the DBTNG migrator module but with the syntax Drupal is choosing to use for the MySQL database. That leads me to think that either the module you're using hasn't structured its schema correctly or the version of MySQL is not supported by Drupal. I've never used MySQL 5.5 or greater.

busla’s picture

busla commented

I got the same error using MAMP after I dragged an image from Finder to the body textbox in node/add. The path for the image was a LOOOONG string of code that halted even my browser when I tried to edit the field in phpMyAdmin.

When I was finally able to access the field_data_body table y phpMA I edited the article and removed the code. Now everything is slick and dandy.

josh waihi’s picture

josh waihi commented
Status: Active » Closed (works as designed)