Topic: MySQL Issue in My Rails App

Hello,

  I have just inherited a rails application for which I plan on doing continued development.  I am trying to move the MySQL tables into my development system.  I basically took a SQL dump of the production database table and I am trying to import it into my development database.  I get an error on the following statement:

CREATE TABLE `newsletter_sections` (
`id` int(11) NOT NULL auto_increment,
`newsletter_id` int(11) NOT NULL default '0',
`position` int(11) NOT NULL default '0',
`title` varchar(100) NOT NULL default '',
`link` varchar(255) default NULL,
`body` text,
PRIMARY KEY  (`id`),
KEY `newsletter_id` (`newsletter_id`),
KEY `position` (`position`),
CONSTRAINT `newsletter_sections_ibfk_1` FOREIGN KEY (`newsletter_id`) REFERENCES `newsletters` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB

I get the following error:

Error occured at:2007-06-19 14:22:04
Line no.:1915
Error Code: 1005 - Can't create table './my_development/newsletter_sections.frm' (errno: 150)

Now when I remove the following block, everything imports no problem.

CONSTRAINT `newsletter_sections_ibfk_1` FOREIGN KEY (`newsletter_id`) REFERENCES `newsletters` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

I also get the same error on other create statements that contain a "CONSTRAINT".  When I remove the "CONSTRAINT", the import goes through.

  Can anyone shed light on this?  I'm not even sure why I need these "CONSTRAINT" statements.

Thanks,

AC

Re: MySQL Issue in My Rails App

Hi AC,

Not sure how well-versed you are in rails/ruby, but have you looked to see if the application you've inherited uses migrations? If it does, and you've updated config/database.yml with your database details, all you need to do to have the correct database schema is run "rake db:migrate" from the command line in your app's root directory. To check to see if you have migrations, just look in the db/migrate directory of your application's root directory. If there are a bunch of numbered files there, you are in luck.

EDIT: BTW, you are probably getting an error on the constraints because of the order the tables are being created. ie: you have a constraint on one table to another, but the other table hasn't yet been created.

Last edited by fabio (2007-06-19 17:38:24)

Re: MySQL Issue in My Rails App

I just figured out the issue I posted about.  The SQL dump did hot have a statement to set foreign key checks to zero.  This is because the production DB runs on an older version of MySQL.  I found the answer here:  http://dev.mysql.com/doc/refman/4.1/en/ … aints.html

In order to fix my problem, I added the following line in my SQL Dump file right below the "USE <tabel name>;" statement:

set foreign_key_checks = 0;

Re: MySQL Issue in My Rails App

Thanks for the help Fabio. I actually had another question for you regarding migrations.  I believe that the previous developer of this rails application did not create migration file for all the db tables.  For some of them he did and for others he directly created the table in the db.  In this scenario, I can't run rake without errors.  Any suggestions on how to get around this?  Do I need to manually create migrate files for all the missing tables?

Re: MySQL Issue in My Rails App

You can put all the tables that don't have migrations into a single migration. I would go to migration 0 (type "rake db:migrate VERSION=0") and copy all the create_table blocks from schema.rb into your up method of the migration; the down method you will have to do manually.

You will want this new migration to be numbered 001, because you want it to be the first, or you will still have problems. So you will have to rename all the current migration files, bumping them up by one, then rename the new migration to "001_whatever.rb".

Once that's all done, a simple "rake db:migrate" should make it all good.

Last edited by fabio (2007-06-19 18:05:58)