Problems migrating from Postgres to MySQL

Rails
1

Hello everyone,

I’m trying to migrate a working app from Postgres to MySQL, and I’m
trying to use a migration to get it done rather than alter all my
database table creation scripts to MySQL syntax. I’ve done loads of
work with Postgres, but am a real newbie with MySQL…

I did a ‘rake db_schema_dump’ from my Postgres app, and got a nice
looking schema file with all my tables and fields, exactly as
expected. I then created a migration file, pasted the schema info
into the self.up section in the schema file and made sure there were
no obvious problems. So far, so good.

I then did a ‘gem install mysql’, created a new MySQL database, set up
a MySQL user, tweaked database.yml to point to the new MySQL database
and tried a ‘rake db_schema_import’.

Wham! Loads of warnings, ending in an ‘uninitialized constant Mysql’.
I’ve confirmed that the database.yml file is OK. I then did a ‘rake
db_schema_import --trace’; here’s what I think is the relevant section
of the output:

** Invoke db_schema_import (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute db_schema_import
rake aborted!
uninitialized constant Mysql
/usr/lib/ruby/gems/1.8/gems/activesupport-1.2.5/lib/active_support/dependencies.rb:200:in
const_missing' /usr/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/connection_adapters/mysql_adapter.rb:35:inmysql_connection’
/usr/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:145:in
connection_without_query_cache=' /usr/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/query_cache.rb:54:inconnection=’
/usr/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:106:in
retrieve_connection' /usr/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:20:inconnection’
/usr/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/migration.rb:170:in
method_missing' ./db/schema.rb:7 /usr/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/schema.rb:43:indefine’
./db/schema.rb:5
/usr/lib/ruby/gems/1.8/gems/activesupport-1.2.5/lib/active_support/dependencies.rb:207:in
load' /usr/lib/ruby/gems/1.8/gems/rails-1.0.0/lib/tasks/databases.rake:27 /usr/lib/ruby/site_ruby/1.8/rake.rb:232:inexecute’
/usr/lib/ruby/site_ruby/1.8/rake.rb:232:in execute' /usr/lib/ruby/site_ruby/1.8/rake.rb:202:ininvoke’
/usr/lib/ruby/1.8/thread.rb:135:in synchronize' /usr/lib/ruby/site_ruby/1.8/rake.rb:195:ininvoke’
/usr/lib/ruby/site_ruby/1.8/rake.rb:1719:in run' /usr/lib/ruby/site_ruby/1.8/rake.rb:1719:inrun’
/usr/bin/rake:8

I poked around in Google and found a few people have seen the
‘uninitialized constant Mysql’ error, but these always seem to be due
to using old versions of Rails, gems or MySQL. I’m running the latest
Rails, just updated all gems, and MySQL 5.1, so I can’t see that I’ve
got any old stuff left.

Has anyone got any suggestions?

Thanks in advance

Dave M.

2

OK I think I got it sorted - it was probably due to an update that I
did to (something) that had caused the wires to get crossed on the
system. The box hadn’t been rebooted for nearly 4 months, and
possibly a library update had crept by me unnoticed.

A reboot fixed it.

Regards

Dave M.

3

Glad to hear you got it working.

Just so you know, there’s no need to copy the generated schema file
into a new migration. Just leave it in db/schema.rb and do rake
db_schema_import to create the database. It generates a schema_info
table to hold the version, so you can just do migrations normally
after that.

Pat

4

On 1/31/06, David M. [email protected] wrote:

after a while…
Yep. I had that same experience. I now run Jamis B.'s “verbose
migrations” plugin. Very handy.
http://svn.jamisbuck.org/rails-plugins/verbose_migrations/

5

Thanks Pat,

Actually this was my first chance to use migrations, and it worked
reasonably well - made things a lot easier. Following through the
migrations video made things a lot simpler.

The biggest problem I had was the lack of referential information that
comes out of rake when there’s a problem with the migration code - it
can be tough to spot a problem when you’re trawling through hundreds
of lines of create_table / t_column statements that all blur together
after a while…

Regards

Dave M.

6

Ooooh, that’s nice.

Now, as long as nobody tells me this was around yesterday, when I was
going through migration hell, no-one’s gonna get hurt and we can all
walk out of here quietly and go back to our normal lives…!

Regards

Dave M.