Comment 5 for bug 1498128

Revision history for this message
Jaime Sicam (jssicam) wrote :

Looks like there's an issue if the existing constraints have both underscore and no underscore:

[user@sandbox employees_db]$ mysql --port=5625 employees -e "show create table employees"
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  `emp_no_2` int(11) NOT NULL,
  PRIMARY KEY (`emp_no`),
  KEY `fk2` (`emp_no_2`),
  CONSTRAINT `_fk1` FOREIGN KEY (`emp_no`) REFERENCES `employees2` (`emp_no`),
  CONSTRAINT `fk2` FOREIGN KEY (`emp_no_2`) REFERENCES `employees2` (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
[user@sandbox employees_db]$ pt-online-schema-change --execute --alter "engine=innodb" h=127.0.0.1,P=5625,D=employees,t=employees --alter-foreign-keys-method=rebuild_constraints
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
 LANGUAGE = (unset),
 LC_ALL = (unset),
 LC_CTYPE = "UTF-8",
 LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
No slaves found. See --recursion-method if host sandbox has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Child tables:
  `employees`.`dept_emp` (approx. 331570 rows)
  `employees`.`dept_manager` (approx. 24 rows)
  `employees`.`salaries` (approx. 2763755 rows)
  `employees`.`titles` (approx. 441891 rows)
Will use the rebuild_constraints method to update foreign keys.
Altering `employees`.`employees`...
Creating new table...
`employees`.`employees` was not altered.
Error creating new table: DBD::mysql::db do failed: Can't write; duplicate key in table '_employees_new' [for Statement "CREATE TABLE `employees`.`_employees_new` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  `emp_no_2` int(11) NOT NULL,
  PRIMARY KEY (`emp_no`),
  KEY `fk2` (`emp_no_2`),
  CONSTRAINT `fk1` FOREIGN KEY (`emp_no`) REFERENCES `employees2` (`emp_no`),
  CONSTRAINT `fk2` FOREIGN KEY (`emp_no_2`) REFERENCES `employees2` (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1"] at /usr/bin/pt-online-schema-change line 10044.