Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite
3

I use Django 1.2 and 1.3 and MySql backends.

Once in while a get an error message when migrating my MySql database with South:

! Error found during real run of migration! Aborting.

! Since you have a database that does not support running
! schema-altering statements in transactions, we have had 
! to leave it in an interim state between migrations.
...
! The South developers regret this has happened, and would
! like to gently persuade you to consider a slightly
! easier-to-deal-with DBMS

I was wondering if migrating my databases from MySql to PostgreSQL might prevent this error. Secondly, would migrating from MySql to PostgreSQL be more involved dan doing a dumpdata on the MySql dbase, change the settings to point to PostgreSQL and the loaddata on the new backend?

I saw this stackoverflow question, but it talks about his database being too big. I don't think that will be the case with my databases. I don't have any custom SQL commands in my Django projects.

share|improve this question
just try the mysql2postgres given in answer stackoverflow.com/a/8385094/540341 - if everything goes OK it will take minutes. you can backup your db and configs before migration to be 100% safe. – filiprem Jan 17 '12 at 14:22
another option is to stay on MySQL but change tables InnoDB engine. see stackoverflow.com/questions/4834415/… – filiprem Jan 17 '12 at 14:26
@filiprem, thanks, that probably works. I'm interested though if specifically migrating to postgreSQL will also avoid this. I have several other reasons to want to migrate. I read about mysql2postgres, it will be a good fallback in case something goes wrong with simple dumpdata/loaddata. – Density 21.5 Jan 17 '12 at 15:08
@filiprem does InnoDB support DDL Transactions? – armonge Jan 17 '12 at 15:58
incidentally this stackoverflow.com/questions/4834415/… does not show that South stops complaining if you use InnoDB. South works on InnoDB, but it still complains when your migrations fail, due to lack of transaction functionality. – Anentropic Jan 19 '12 at 16:33

1 Answer

up vote 4 down vote accepted

I got tired of seeing this error using South and yes, switching to PostgreSQL has banished it!

The mysql2postgres app, written in Ruby, suggested in comments above didn't work for me (it would run, output some details to screen but not copy any rows of data, for me). Not sure why. But gladly there's a Python rewrite of it that worked flawlessly (for me, eventually):
http://pypi.python.org/pypi/py-mysql2pgsql

The only gotcha I found was:

Initially I thought it would be safest to set up the tables in the PostgreSQL db via a syncdb and then migrate the data only. I tried this, but the tables are migrated across in alphabetical order and this violates the foreign key constraints for some tables (rows relate to rows in tables not yet imported).

I next tried a structure+data migration. This migrated fine, but I encountered some problems in Django afterwards, especially the admin site. It seemed like the migration script had created some different table constraints from what Django would have.

I solved this by hacking the mysql2pgsql script to respect the order of tables given in the yaml config only_tables property... and then doing a syncdb + data-only migration. By trial and error I shuffled around the ordering of the tables for my migration until they all imported successfully.

UPDATE:
My pull request for the hack described above got accepted so you can do this now from the main version:
https://github.com/philipsoutham/py-mysql2pgsql

share|improve this answer
Thanks, this is exactly the information I needed. I am going to try your successful migration route. – Density 21.5 Jan 19 '12 at 22:44
I am currently deploying this solution. It works, only problem encountered so far is this: stackoverflow.com/questions/6466836/… – Density 21.5 Apr 2 '12 at 19:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.