781921 - problem with affliliates dev db

Status: RESOLVED FIXED

(Data & BI Services Team :: DB: MySQL, task)

Description

[Sheeri Cabral [:sheeri]]

120810 13:11:05  InnoDB: Error: in ALTER TABLE `affiliates_fb_dev_allizom`.`facebook_facebookaccountlink`
InnoDB: has or is referenced in foreign key constraints
InnoDB: which are not compatible with the new table definition.
120810 13:11:05  InnoDB: Error: table `affiliates_fb_dev_allizom`.`facebook_facebookaccountlink` does not exist in the InnoDB internal
InnoDB: data dictionary though MySQL is trying to drop it.
InnoDB: Have you copied the .frm file of the table to the
InnoDB: MySQL database directory from another database?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
120810 13:15:27  InnoDB: Error: in ALTER TABLE `affiliates_fb_dev_allizom`.`facebook_facebookaccountlink`
InnoDB: has or is referenced in foreign key constraints
InnoDB: which are not compatible with the new table definition.
120810 13:15:27  InnoDB: Error: table `affiliates_fb_dev_allizom`.`facebook_facebookaccountlink` does not exist in the InnoDB internal
InnoDB: data dictionary though MySQL is trying to drop it.
InnoDB: Have you copied the .frm file of the table to the
InnoDB: MySQL database directory from another database?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html

Comment 1

[Sheeri Cabral [:sheeri]]

Hrm, the 2nd error - the table does exist:
mysql> optimize table facebook_facebookaccountlink\G
*************************** 1. row ***************************
   Table: affiliates_fb_dev_allizom.facebook_facebookaccountlink
      Op: optimize
Msg_type: note
Msg_text: Table does not support optimize, doing recreate + analyze instead
*************************** 2. row ***************************
   Table: affiliates_fb_dev_allizom.facebook_facebookaccountlink
      Op: optimize
Msg_type: status
Msg_text: OK
2 rows in set (0.05 sec)

So I think that's not an issue.

Here's the table definition, there are 2 foreign keys...

mysql> show create table facebook_facebookaccountlink\G
*************************** 1. row ***************************
       Table: facebook_facebookaccountlink
Create Table: CREATE TABLE `facebook_facebookaccountlink` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `facebook_user_id` varchar(128) NOT NULL,
  `affiliates_user_id` int(11) NOT NULL,
  `activation_code` varchar(128) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `facebook_user_id` (`facebook_user_id`),
  UNIQUE KEY `affiliates_user_id` (`affiliates_user_id`),
  CONSTRAINT `affiliates_user_id_refs_id_2d52da2459422c18` FOREIGN KEY (`affiliates_user_id`) REFERENCES `auth_user` (`id`),
  CONSTRAINT `facebook_user_id_refs_id_5e05563a0afdbcf2` FOREIGN KEY (`facebook_user_id`) REFERENCES `facebook_facebookuser` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Comment 2

[Sheeri Cabral [:sheeri]]

AHA!

------------------------
LATEST FOREIGN KEY ERROR
------------------------
120810 13:28:19 Error in foreign key constraint of table affiliates_fb_dev_allizom/facebook_facebookaccountl
ink:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
,
  CONSTRAINT "affiliates_user_id_refs_id_2d52da2459422c18" FOREIGN KEY ("affiliates_user_id") REFERENCES "au
th_user" ("id")

Comment 3

[Sheeri Cabral [:sheeri]]

[16:35:50] <mkelly> FWIW the thing that's failing is a command to remove the unique constraint on affiliates_user_id
[16:36:01] <sheeri> oh!
[16:36:17] <sheeri> that's because the foreign key needs an index - it doesn't have to be a unique one though
[16:36:36] <sheeri> if you first add a regular (non-unique) index on affiliates_user_id, then drop the unique index, that should work.

Comment 4

[Osmose [:osmose, :mkelly]]

The push that ran the query that was failing has passed, it seems to be working now (other issues are still breaking the site, but the DB works!)

Thanks a ton!