4

Create table:

CREATE TABLE `phppos_register_log` (
  `register_log_id` int(10) NOT NULL AUTO_INCREMENT,
  `employee_id` int(10) NOT NULL,
  `shift_start` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `shift_end` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `open_amount` decimal(23,10) NOT NULL,
  `close_amount` decimal(23,10) NOT NULL,
  `cash_sales_amount` decimal(23,10) NOT NULL,
  `deleted` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`register_log_id`),
  KEY `phppos_register_log_ibfk_1` (`employee_id`),
  CONSTRAINT `phppos_register_log_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `phppos_employees` (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

SQL CODE to rename column: (This works on mysql 5.5 and 5.6 but one user is reporting an issue. (Exact version number 5.5.40)

//this is where error is happening
ALTER TABLE phppos_register_log DROP FOREIGN KEY phppos_register_log_ibfk_1;


ALTER TABLE  `phppos_register_log` CHANGE  `employee_id`  `employee_id_open` INT( 10 ) NOT NULL ;
ALTER TABLE `phppos_register_log`ADD CONSTRAINT `phppos_register_log_ibfk_1` FOREIGN KEY (`employee_id_open`) REFERENCES `phppos_employees` (`person_id`);

Is anything in mysql 5.5.40 that would cause this code to NOT work?

ERROR reported:

#1025 - Error on rename of './sole_phppos_144/phppos_register_log' to './sole_phppos_144/#sql2-6224-22975' (errno: 152)

NOTE: I am renaming column so I drop the constraint, rename column, then add the constraint back.

Chris Muench
  • 17,444
  • 70
  • 209
  • 362
  • Why drop the foreign key at all? The `ALTER TABLE phppos_register_log CHANGE employee_id employee_id_open INT(10) NOT NULL;` should work just fine without dropping the FK. –  Jan 14 '15 at 23:11
  • When doing your suggestion I get the following error `#1025 - Error on rename of './pos/#sql-12f9_3e' to './pos/phppos_register_log' (errno: 150)` – Chris Muench Jan 15 '15 at 04:36
  • 1
    Seems to be a limitation of 5.5: http://sqlfiddle.com/#!9/1a9fa/1 –  Jan 15 '15 at 07:06
  • Ah I see. But at least what I have now works on most version of mysql 5.5. I am just stumped why the above fails. – Chris Muench Jan 15 '15 at 16:40

2 Answers2

3

UPDATE: I have experimented some more and previous found solution doesn´t work properly, you can´t remove the index before removing the foreign key, as indicated by the other reference on stackoverflow.

However, I have been able to find circumstances to reproduce the error, namely when executing the DROP FOREIGN KEY on the same foreign key twice. The second time it will produce exactly this error.

Before update:

This guy seems to have figured it out: http://lists.mysql.com/mysql/204199 as his answer has been approved and referenced in unable to drop the foreign key

He mentions that the index key (KEY) prevents the foreign key from dropping. So his advice is to first drop the index key, sth like:

ALTER TABLE phppos_register_log DROP KEY phppos_register_log_ibfk_1;

and then the foreign key: 

ALTER TABLE phppos_register_log DROP FOREIGN KEY phppos_register_log_ibfk_1;

I hope it works, however, unfortunately I haven´t been able to reproduce the error myself, so neither this solution. On my system it simply works to drop the foreign key.

Community
  • 1
  • 1
Frido Emans
  • 5,120
  • 2
  • 27
  • 42
  • Ok, I will add this fix. I am not sure why this only happened on one machine so far. – Chris Muench Jan 26 '15 at 00:26
  • I looked at the post on the MySQL forum and the user was actually trying to remove a column. I still want the index. I guess I will have to find a way to reproduce issue (reported by 1 user) to see if this suggestion will fix. – Chris Muench Jan 26 '15 at 00:32
  • Do you have an idea on how I can try to reproduce? I installed the exact same version of MySQL that the problem was reported on and I didn't have the issue. – Chris Muench Jan 26 '15 at 00:33
  • dropping keys does not remove any column, but I added new information that can be important. – Frido Emans Jan 26 '15 at 01:01
  • Thanks for the experiment. Maybe the user double clicked? – Chris Muench Jan 26 '15 at 01:13
  • That is more or less how I found out, so I suppose that could be possible. But this is not typically something a mere user does. – Frido Emans Jan 26 '15 at 01:57
  • Either way, please remove the other fix (if you added it), because that didn´t work. – Frido Emans Jan 26 '15 at 01:59
  • I provide users an sql file to migrate their database so I am wondering if they ran it twice or had a problem with a constraint missing? – Chris Muench Jan 26 '15 at 02:01
  • yes, this is typically when you run the migration script twice. The database is not corrupt and I bet that running the rest of the script from the first next line will still make a proper migration. (Though of course I can´t guarantee it without looking at the rest of the script / schema.) You can try this out yourself, on a clean copy of your db. – Frido Emans Jan 26 '15 at 02:09
0

I wasn't able to reproduce this with the statements you provided. But are you sure they aren't trying to drop the foreign key by column name?

ALTER TABLE phppos_register_log DROP FOREIGN KEY employee_id;
ALTER TABLE phppos_register_log CHANGE employee_id employee_id_open INT NOT NULL;
ALTER TABLE `phppos_register_log`ADD CONSTRAINT `phppos_register_log_ibfk_1` 
FOREIGN KEY (`employee_id_open`) REFERENCES `phppos_employees` (`person_id`);

Produces the same error you posted, in SQLFiddle 5.5.

EternalHour
  • 8,308
  • 6
  • 38
  • 57
  • I am dropping based on the name of the constraint and not the column name. I have tried many versions of MySQL and have yet been able to reproduce the reported error by 1 user. Is it possible that the db could be corrupt? – Chris Muench Jan 26 '15 at 02:03
  • I would have to say it's highly unlikely since it's only one user getting the error, for me it points to user error. When you say user, they are renaming this column? – EternalHour Jan 26 '15 at 02:16
  • Yes. When I mean user they are running the queries. – Chris Muench Jan 26 '15 at 16:51