Problem adding Foreign Key using Alter Table with existing MYSQL Database - can't add it! Help!

Question

I have a production database where I have renamed several column's that are foreign keys. Obviously mysql makes this a real pain to do in my experience.

My solution was to drop all the indexes and foreign keys, rename the id columns, and then re-add the indexes and foreign keys.

This works great on mysql 5.1 on windows for the development database.

I went to run my migration script on my debian server, which is also using mysql 5.1, and it gives the following error:

mysql> ALTER TABLE `company_to_module`
    -> ADD CONSTRAINT `FK82977604FE40A062` FOREIGN KEY (`company_id`) REFERENCES `company` (`company_id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ERROR 1005 (HY000): Can't create table 'jobprep_production.#sql-44a5_76' (errno: 150)

There are no values in this table that would conflict with the foreign key I am trying to add. The database hasn't changed. The foreign key DID exist before... so the data is fine. Let's not mention that I took the SAME database that I have on the server and it migrates fine on Windows. But these same foreign key migrations are not taking on Debian.

The columns are using the same type - BIGINT (20)

The names do in fact exist in their respective tables.

The tables are innodb. They already have foreign keys in other columns as it is. This is not a new database.

I cannot drop tables because this is a production database.

The tables "as is" in my database:

 CREATE TABLE `company_to_module` (
  `company_id` bigint(20) NOT NULL,
  `module_id` bigint(20) NOT NULL,
  KEY `FK8297760442C8F876` (`module_id`),
  KEY `FK82977604FE40A062` (`company_id`) USING BTREE,
  CONSTRAINT `FK8297760442C8F876` FOREIGN KEY (`module_id`) REFERENCES `module` (`module_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

And

Create Table: CREATE TABLE `company` (
  `company_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `postal_code` varchar(255) DEFAULT NULL,
  `province_id` bigint(20) DEFAULT NULL,
  `phone_number` varchar(255) DEFAULT NULL,
  `is_enabled` bit(1) DEFAULT NULL,
  `director_id` bigint(20) DEFAULT NULL,
  `homepage_viewable` bit(1) NOT NULL DEFAULT b'1',
  `courses_created` int(10) NOT NULL DEFAULT '0',
  `header_background` varchar(25) DEFAULT '#172636',
  `display_name` varchar(25) DEFAULT '#ffffff',
  `tab_background` varchar(25) DEFAULT '#284767',
  `tab_text` varchar(25) DEFAULT '#ffffff',
  `hover_tab_background` varchar(25) DEFAULT '#284767',
  `hover_tab_text` varchar(25) DEFAULT '#f2e0bd',
  `selected_tab_background` varchar(25) DEFAULT '#f5f5f5',
  `selected_tab_text` varchar(25) DEFAULT '#172636',
  `hover_table_row_background` varchar(25) DEFAULT '#c0d2e4',
  `link` varchar(25) DEFAULT '#4e6c92',
  PRIMARY KEY (`company_id`),
  KEY `FK61AE555A71DF3E03` (`province_id`),
  KEY `FK61AE555AAC50C977` (`director_id`),
  CONSTRAINT `company_ibfk_1` FOREIGN KEY (`director_id`) REFERENCES `user_account` (`user_account_id`),
  CONSTRAINT `FK61AE555A71DF3E03` FOREIGN KEY (`province_id`) REFERENCES `province` (`province_id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8

Here is the innodb status:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
110415  3:14:34 Error in foreign key constraint of table jobprep_production/#sql-44a5_1bc:
 FOREIGN KEY (`company_id`) REFERENCES `company` (`company_id`) ON DELETE RESTRICT ON UPDATE RESTRICT:
Cannot resolve column name close to:
) ON DELETE RESTRICT ON UPDATE RESTRICT

If I try and drop the index from 'company_to_module', I get this error:

#1025 - Error on rename of './jobprep_production/#sql-44a5_23a' to './jobprep_production/company_to_module' (errno: 150) 

Here are my innodb variables:

+---------------------------------+------------------------+
| Variable_name                   | Value                  |
+---------------------------------+------------------------+
| innodb_adaptive_hash_index      | ON                     |
| innodb_additional_mem_pool_size | 1048576                |
| innodb_autoextend_increment     | 8                      |
| innodb_autoinc_lock_mode        | 1                      |
| innodb_buffer_pool_size         | 8388608                |
| innodb_checksums                | ON                     |
| innodb_commit_concurrency       | 0                      |
| innodb_concurrency_tickets      | 500                    |
| innodb_data_file_path           | ibdata1:10M:autoextend |
| innodb_data_home_dir            |                        |
| innodb_doublewrite              | ON                     |
| innodb_fast_shutdown            | 1                      |
| innodb_file_io_threads          | 4                      |
| innodb_file_per_table           | OFF                    |
| innodb_flush_log_at_trx_commit  | 1                      |
| innodb_flush_method             |                        |
| innodb_force_recovery           | 0                      |
| innodb_lock_wait_timeout        | 50                     |
| innodb_locks_unsafe_for_binlog  | OFF                    |
| innodb_log_buffer_size          | 1048576                |
| innodb_log_file_size            | 5242880                |
| innodb_log_files_in_group       | 2                      |
| innodb_log_group_home_dir       | ./                     |
| innodb_max_dirty_pages_pct      | 90                     |
| innodb_max_purge_lag            | 0                      |
| innodb_mirrored_log_groups      | 1                      |
| innodb_open_files               | 300                    |
| innodb_rollback_on_timeout      | OFF                    |
| innodb_stats_on_metadata        | ON                     |
| innodb_support_xa               | ON                     |
| innodb_sync_spin_loops          | 20                     |
| innodb_table_locks              | ON                     |
| innodb_thread_concurrency       | 8                      |
| innodb_thread_sleep_delay       | 10000                  |
+---------------------------------+------------------------+

I also want to add that while I was playing with adding the foreign keys, mysql corrupted my database and destroyed it. I had to reload from a backup to try again.

Help? :/

Claqueur

Are both tables InnoDB type? – Elah

Does the company table have an index on company_id ? – Elah

I guess that your table is MyISAM (the default if you haven't chnaged the config) and I think you can't create foreign key constraints in MyISAM. See the description of the CREATE TABLE company_to_module. – Elah

If both tables are empty, drop them and re-create them, choosing InnoDB as engine. You could also add the FOREIGN KEY constraints in the tables creation script(s). – Elah

@Ypercube: thanks for the tips. Both tables are not empty - this is a production database. I must user alter table. All the tables are innodb. In fact, there is existing foreign keys on these tables - it's not like these are the first ones. This database has 40 tables a tons of foreign keys in all of the tables. So I don't think it's any of those problems :( And yes, company has the id "company_id". I was sure to do that. The application actually works with the database despite not having the foreign keys... but I simply want to add them anyway. – Claqueur

@mu is too short: jobprep_production is the database name. I am not sure at all why it's spitting out this error though. – Claqueur

Looks like ALTER TABLE is not able to create a temporary copy of your table. Can you create such a table manually? That is, run the CREATE TABLE company_to_module statement manually (of course, replacing the table name)? If it fails, the error message might give you a hint... – Gorgonian

@titanoboe: you're right! Holy crap. I made the exact same table with a new name and it failed with the same error. Solutions? – Claqueur

Actually, scratch what I said above. I just had to rename the foreign key name. If I try and drop indexes on "company_to_module", I get an error that says, "#1025 - Error on rename of './jobprep_production/#sql-44a5_23a' to './jobprep_production/company_to_module' (errno: 150) " – Claqueur

The company_to_module key is using BTREE: KEY FK82977604FE40A062 (company_id) USING BTREE. The company.company_id primary key is it btree too? Perhaps that's the reason. – Elah

Yes, company_id is also using btree. I got so fed up with this problem that I exported the database, added the foreign keys on windows and then re-imported the database. Worked. I have no idea what the hell is up with mysql on linux. – Claqueur

@egervari: Can you check again the tables definitions on the two tables in the linux db? That they are exactly as you have them posted? – Elah

yes. I use both the mysql console and phpMyAdmin where I can see the types and the type of btree index all on the same screen. The thing that doesn't make sense is that if my types, column names, data, etc. are "truly" wrong, why do they work on windows with the exact same database? This doesn't make any sense at all. I am actually more concerned about some kind of corruption or damage to this server now. – Claqueur

Answer 1

I have simply applied the refactorings using Windows and then reimported the database into Debian - it works.

I think it's safe to say that something was messed up on the Debian server, or with the linux version of Mysql - perhaps a bug in 5.1 build?

Anyway, I have also upgraded the ram on the server from 1gb to 2gb, and these problems have gone away.

I think MySQL maybe just didn't have enough ram to complete the operation. If that was the case (and it seems to be), I think MySQL should have simply said so rather than spitting out these errors - making me and everyone here think it was a syntax or a schema-related problem.

Anyway, thanks for those that tried to help. At least it helped me to isolate all the things it couldn't have been.

Claqueur

Answer 2

Are both tables InnoDB type?

Does the company table have an index on company_id ?

I guess that your table is MyISAM (the default if you haven't changed the config) and you can't create foreign key constraints in MyISAM. See the description of the CREATE TABLE for yout two tables.

If both tables are empty, drop them and re-create them, choosing InnoDB as engine. You could also add the FOREIGN KEY constraints in the tables creation script(s).

---

From MySQL Reference Manual:

Foreign keys definitions are subject to the following conditions:

• Both tables must be InnoDB tables and they must not be TEMPORARY tables.

• Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

• InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. (This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.) index_name, if given, is used as described previously.

• InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

• Index prefixes on foreign key columns are not supported. One consequence of this is that BLOB and TEXT columns cannot be included in a foreign key because indexes on those columns must always include a prefix length.

• If the CONSTRAINT symbol clause is given, the symbol value must be unique in the database. If the clause is not given, InnoDB creates the name automatically.

---

@egervari: What happens if you run this:

CREATE TABLE `test` (
  `company_id` bigint(20) NOT NULL,
  `module_id` bigint(20) NOT NULL,

  KEY  (`module_id`),
  KEY  (`company_id`),

  CONSTRAINT `test_fk_module`
    FOREIGN KEY (`module_id`)
    REFERENCES `module` (`module_id`),

  CONSTRAINT `test_fk_company`
    FOREIGN KEY (`company_id`)
    REFERENCES `company` (`company_id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

---

And if you run:

ALTER TABLE `company_to_module`
  ADD CONSTRAINT `company_to_module_fk_company` 
    FOREIGN KEY (`company_id`)
    REFERENCES `company` (`company_id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT;

Environ

Yep. I am doing all of these things. The tables are Innodb. The ids are named correctly. The data is correct. The types are correct. Question: Assuming I did violate these rules... why would these migrations work on Windows using the exact same database? This is the most baffling thing. – Claqueur

Could low memory be the cause of this error? This is just a cloud server and "free" usually says I have 29-9 megs free of 1 gig. Just curious :/ A lot is used for buffers though, so I'm not sure what this means. – Claqueur

@egervari: Do you get similar message when you run the above CREATE TABLE ? – Elah

@egervari: And if you run the ALTER TABLE ? – Elah

It didn't work on the debian mysql (I obviously tried this before... this is what I was trying to do), but your alter table worked on the windows database (same .sql file) just like mine had. – Claqueur

Answer 3

Ensure that company_to_module.company_id and company.company_id are the EXACT same datatype. I had this happen when the primary key was setup as an UNSIGNED INT but the foreign key field was just an INT. Adding UNSIGNED to the datatype fixed the problem.

Spokesman

It was worth a shot to check. All of my ids are BIGINT (20) :( – Claqueur

I'm just going to paste my table definitions – Claqueur

worked for me - i had one unsigned int and another int that i was trying to link. thank you! – Sulfapyridine

Answer 4

Since it doesn't seem to be anything syntax-related, my best guess would be that you're running out of space for creating InnoDB tables.

EDIT: Can you paste your InnoDB configuration:

SHOW VARIABLES LIKE "inno%";

Subprincipal

"innodb_data_file_path" is actually not located at all in my.cnf for some reason. Neither is "innodb_file_per_table". I have not done anything to my.cnf personally - these are the defaults. Could this be the problem? Is the default like really low? – Claqueur

This is the stats on ibdata1: "35651584 Apr 15 03:20 ibdata1" – Claqueur

i pasted the variables above ;) – Claqueur

Answer 5

Since trying to create a copy of company_to_module manually gives you the same error, you should carefully check the fk constraint already present in company_to_module. Is it still valid, or did you modify the table module?

From the MySQL-Docs:

1005 (ER_CANT_CREATE_TABLE) Cannot create table. If the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed.

Gorgonian

It gave the error because I forgot to change the foreign key name in my duplicate table definition of company_to_module. Once I realized that, creating the table worked - which meant creating new tables wasn't the problem. However, dropping the index for 'module_id' caused the new error I wrote above. Honestly, I got so sick and tired of this "guess work" that I just exported my database, added the foreign keys on windows (yes, they work!), and re-imported the database back into debian. It works. I am at a loss as to why it works on windows and not linux. So much for cross-compatibility. – Claqueur

Answer 6

@egervari You wrote - My solution was to drop all the indexes and foreign keys, rename the id columns, and then re-add the indexes and foreign keys.

Agree with you. But it might be that something went wrong. I reproduced the error, and (in my case) fixed it.

I'd suggest you to run OPTIMIZE TABLE command for table where column was renamed. Documentation says - For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index.

---

One more solution:

Drop unique key in the referenced table (key that is used by foreign key, in your case it is a primary key). Then add new foreign key and recreate droped unique key.

---

One more solution:

Try to add and drop new column to the referenced table, then try to create your foreign key.

ALTER TABLE company ADD COLUMN column1 VARCHAR(255) DEFAULT NULL;
ALTER TABLE company DROP COLUMN column1;

Carcinogen

Although I never mentioned it, I did optimize table on everything. Unfortunately, that also didn't work. – Claqueur

I can't drop any keys. It's a production database. :/ – Claqueur