Mysql Error Code 1280. Incorrect Index Name
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up ERROR: Error 1280: Incorrect index name up vote 7 down vote favorite 1 I've got some problems using foreign keys in my database using InnoDB tables. I'm using MySQL Workbench for designing my ER models and have a Zend Server (OS X) for development with MySQL 5.1.54. Everything works fine without any error. When deploying this database to my live server it fails. It is a Debian installation with MySQL 5.1.58-1~dotdeb.1-log. I can't understand why these two systems work so different. ERROR: Error 1280: Incorrect index name 'fk_accounts_countries_idcountry' DROP TABLE IF EXISTS `countries` ; SHOW WARNINGS; CREATE TABLE IF NOT EXISTS `countries` ( `idcountry` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT , `name` CHAR(50) NOT NULL , `prefix` CHAR(2) NULL DEFAULT NULL , `tld` CHAR(4) NULL DEFAULT NULL , PRIMARY KEY (`idcountry`) ) ENGINE = InnoDB AUTO_INCREMENT = 270 DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci; SHOW WARNINGS; -- ----------------------------------------------------- -- Table `accounts` -- ----------------------------------------------------- DROP TABLE IF EXISTS `accounts` ; SHOW WARNINGS; CREATE TABLE IF NOT EXISTS `accounts` ( `idaccount` INT UNSIGNED NOT NULL AUTO_INCREMENT , `countries_idcountry` SMALLINT UNSIGNED NOT NULL , `fk_account` INT UNSIGNED NOT NULL , `fk_country` INT UNSIGNED NOT NULL , `username` CHAR(30) NOT NULL , `password` CHAR(32) NOT NULL , `mail` CHAR(50) NOT NULL , `address` CHAR(50) NULL , `city` CHAR(50) NOT NULL , `company` CHAR(50) NULL , `phone` CHAR(25) NULL , `regdate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , `ratedate` TIMESTAMP NULL , `lastlogin` TIMESTAMP NULL , `activated` TINYINT(1) NULL DEFAULT 0 , `activation` CHAR(32) NULL
Van de Paar (OCA) Email Updates: Status: Closed Impact on me: None Category:MySQL Server: InnoDB Plugin storage engine Severity:S1 (Critical) Version:5.1.47, 5.1.49 OS:Any Assigned to: Jimmy Yang Triage: Triaged: D3 (Medium) / R2 (Low) / E3 (Medium) View Add Comment Files Developer Edit Submission View Progress Log Contributions [22 Jul 2010 1:29] Roel Van de Paar Description: Last line of "how to repeat" gives: Using built-in InnoDB: http://stackoverflow.com/questions/6797522/error-error-1280-incorrect-index-name Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 Using InnoDB Plugin 1.0.8: ERROR 1280 (42000): Incorrect index name 'fk1' Also (using build-in InnoDB after creation of the index): mysql> ALTER TABLE t2 DROP INDEX fk1; ERROR 1025 (HY000): Error on rename of '.\bg1\#sql-1658_1' to '.\bg1\t2' (errno: 150) https://bugs.mysql.com/bug.php?id=55465 On playing around with this, error log also showing entries similar to these: ---------- 100722 11:22:43 InnoDB: Error: in ALTER TABLE `bg1`.`t2` InnoDB: has or is referenced in foreign key constraints InnoDB: which are not compatible with the new table definition. 100722 11:22:43 InnoDB: Error: table `bg1`.`t2` 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 ---------- How to repeat: DROP DATABASE IF EXISTS `bg1`; CREATE DATABASE `bg1`; USE `bg1`; CREATE TABLE IF NOT EXISTS `t1`(`id` MEDIUMINT NOT NULL,PRIMARY KEY (`id`)) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `t2`(`id` MEDIUMINT NOT NULL,CONSTRAINT `fk1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; CREATE INDEX `fk1` ON `t2` (`i
Alexey Kopytov Email Updates: Status: Won't fix Impact on me: None Category:MySQL Server: InnoDB Plugin storage engine Severity:S3 (Non-critical) Version:5.1, 5.5 OS:Any Assigned to: Tags: regression Triage: Needs Triage: D2 (Serious) View Add Comment Files Developer Edit https://bugs.mysql.com/bug.php?id=61146 Submission View Progress Log Contributions [12 May 2011 9:18] Alexey Kopytov Description: The following fails http://serverfault.com/questions/294905/mysql-cannot-create-index with InnoDB plugin in both 5.1 and 5.5, but passes with built-in InnoDB in 5.1: mysql> create table t1(a int, b int, primary key (a), key b (b)) engine=innodb; Query OK, 0 rows affected (0.02 sec) mysql> alter table t1 drop key b, add key (b, a); ERROR 1280 (42000): Incorrect index name 'b' Specifying the index name explicitly does mysql error work: mysql> alter table t1 drop key b, add key b (b, a); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 How to repeat: create table t1(a int, b int, primary key (a), key b (b)) engine=innodb; alter table t1 drop key b, add key (b, a); [12 May 2011 11:04] Valerii Kravchuk Thank you for the bug report. Verified just as described: C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot mysql error code -P3312 test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 24 Server version: 5.5.11 MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create table t1(a int, b int, primary key (a), key b (b)) engine=innodb; Query OK, 0 rows affected (0.11 sec) mysql> alter table t1 drop key b, add key (b, a); ERROR 1280 (42000): Incorrect index name 'b' [24 May 2011 23:32] Omer Barnir This bug s a duplicate of bug#54927 that is fixed in an upcoming release - the fix will not be back ported as a good workaround (split the drop and add to two separate commands exists. Legal Policies Your Privacy Rights Terms of Use Contact Us Portions of this website are copyright © 2001, 2002 The PHP Group Page generated in 0.027 sec. using MySQL 5.7.12-enterprise-commercial-advanced-log Content reproduced on this site is the property of the respective copyright holders.It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party. © 2016, Oracle Corporation and/or its affiliates
Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Server Fault Questions Tags Users Badges Unanswered Ask Question _ Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Mysql : Cannot create Index: up vote 0 down vote favorite Hi all we are having trouble to create/drop index(s) on a table I keep getting the following error: ERROR 1280 (42000): Incorrect index name '<>' Now the table has the following: 1. PRIMARY KEY (id), 2. UNIQUE KEY field_one (field_one), 3. KEY EAN (field_two), Meta Info: - ENGINE=InnoDB AUTO_INCREMENT=23149997 DEFAULT CHARSET=utf8 MAX_ROWS=4294967295 AVG_ROW_LENGTH=50 PACK_KEYS=0 About 20 000 000 entries. Tried thus far: - check table (medium & extended) table is ok optimize table - went fine tried to drop all index(s) but that also doesn't work. Server info: The server is a MASTER server is running MySQl 5(5.1.51-rel11.5-log) with Percona patches mysql innodb index share|improve this question edited Jul 28 '11 at 8:34 asked Jul 28 '11 at 7:15 Koos 11 The error description explains your problem already, I think. When in doubt, post the ALTER TABLE command you are trying to execute. –sw0x2A Jul 28 '11 at 8:20 alter table MyTable drop index