Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I actually want to get rid of the existing constraints in the database which I know sounds crazy but we are upgrading our application to a new framework which relies on the foreign key constraint names plus we're doing a bunch of cleanup and renaming of fields, tables etc.

I suppose a better way to frame the question is that given an existing database that needs to go through the following:

  • Create all brand new foreign key constraints with names that work within the framework
  • Tables will be renamed and/or massively restructured including renaming fields that are currently part of existing foreign key relationships

What is the best way to accomplish this?

My solutions thus far have been:

  1. Create a conversion script (in php) that will run DROP FOREIGN KEY for all of the constraints in the database. This has resulted in weird errors occurring during the later steps of the process when trying to ALTER TABLES and add in the new constraints. Also, the constraints were removed but I get errors if I try to DROP the keys themselves.
  2. Instead of using PHP and PDO, write the conversion script using straight SQL and use a CURSOR to DROP the constraints.
  3. Instead of dropping the constraints themselves, convert the tables to MyIsam and DROP the keys then convert back to Innodb before continuing with the rest of the conversion process.
  4. Create a brand new schema from scratch and then move the data from the old schema to the new.

I would like to know which of these approaches would be considered the most valid? Or if there is a better way of doing this?

UPDATE (this information was requested in the chat room so I thought I would add it here):

  • The current schema has 68 tables, the new schema will be closer to 55.
  • The largest table has just over 800,000 rows and in general our tables are in the tens of thousands.
  • The entire database is roughly 240 MB but most of that is in a single table that stores a large text field. We'll be compressing this during the conversion.
share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

There comes a point when a step-by-step clean-up becomes more work than a clean slate and migrate approach. System availability and time to migrate may factor in to the decision when dealing with larger volumes but at this size, not an issue.

Key factors for me here are:

  • Renaming foreign key constraints to fit a new application framework.
  • Refactoring a significant proportion of existing tables.
  • Low volume of data.

In this situation I'd be very tempted to design a new schema that fits the model you now require and create the necessary scripts to migrate data across (your option 4).

share|improve this answer
add comment

I don't know about optimal, but it might be worth a shot for a quick conversion to write a bash script that follows a simple logic:

  • mysqldump database into a file
  • parse out the CONSTRAINT lines in the file
  • check if the CONSTRAINT name exists as a KEY, if it does remove it from the file
  • remove the constraint line from the file
  • reload the resulting dump file into the database.

Very important: Have a backup of your database.

share|improve this answer
    
I guess MacGyver wouldn't do it any different. +1 !!! –  RolandoMySQLDBA Oct 19 '11 at 17:28
add comment

Good morning, Mr. Phelps (I means Mr. Goodrich).

You have foreign key constraints you would like to bypass. Here is your itinerary for this assignment:

  • Step 01) Make absolutely sure you have backups of all you data
  • Step 02) Disable Foreign Keys using
    • SET GLOBAL UNIQUE_CHECKS = 0;
    • SET GLOBAL FOREIGN_KEY_CHECKS = 0;
  • Step 03) Proceed with your cleanup without worrying about foreign keys
  • Step 04) If anything goes wrong, restore the backup from Step 01

Your mission, should you decide to except it, it to carry out these steps with the hopes that Step 04 is not needed.

As always, should any of your data be caught or killed, the DBA StackExchange will disavow any knowledge of your actions.

This is not a tape, so it will not self-destruct in 5 seconds.

UPDATE 2011-10-19 12:50 EDT

I like your option 3. It seems the quickest and dirtiest. Here is how you can do this:

Step 01) mysqldump everything to /root/MySQLData.sql

Step 02) Drop all databases

Step 03) add this to /etc/my.cnf

[mysqld]
skip-innodb

Step 04) service mysql stop

Step 05) rm -f /var/lib/mysql/ib_logfile[01] /var/lib/mysql/ibdata1

Step 06) service mysql start

Step 07) mysql -uroot -p -A < /root/MySQLData.sql

Step 08) Remove skip-innodb from /etc/my.cnf

Step 09) service mysql restart

After step 7, all data is in MyISAM

After step 9

  • InnoDB storage engine is reenabled
  • ibdata1 is recreated
  • ib_logfile0 is recreated
  • ib_logfile1 is recreated

Now, here is how to mass convert all MyISAM tables back to InnoDB:

echo "SET SQL_LOG_BIN = 0;" > /root/ConvertMyISAMToInnoDB.sql
mysql -uroot -p -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY (data_length+index_length)" >> /root/ConvertMyISAMToInnoDB.sql
mysql -uroot -p -A < /root/ConvertMyISAMToInnoDB.sql

The script will convert all MyISAM tables in size order from the smallest to the biggest.

Give it a Try !!!

share|improve this answer
    
While the humor is great, I am actually trying to remove the existing constraints so I can create brand new ones. –  Noah Goodrich Oct 19 '11 at 16:07
add comment

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.