Can't create table 'perfsoft.rg_temp_1943023349_186'

steveshourds

I can not seem to figure out why I am getting this error when trying to sync a local db to a remote server db, MySql (ver. 5.5.13):

Can't create table 'perfsoft.rg_temp_1943023349_186' (errno 121)

-- Script generated by MySQL Compare 1.0.0.241 on 8/27/2011 10:02:56 AM

SET @FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @SQL_MODE, SQL_MODE='TRADITIONAL';

USE `perfsoft`;

CREATE TABLE `perfsoft`.`RG_TEMP_1943023349_186` (
`ID` varchar(20) NOT NULL,
`LOCATION` varchar(5) NOT NULL,
`UNIT_MEASURE` varchar(10) NOT NULL,
`STANDARD_PACK` int(11) NULL,
`CURR_SOURCE` varchar(10) NULL,
`WH_COST` decimal(10,2) NULL,
`QOH` int(11) NULL,
`QOO` int(11) NULL,
`DUE_DATE` datetime NULL,
`REORDER_LEVEL` varchar(10) NULL,
`STOCK_LEVEL` varchar(10) NULL,
`WH_LOC` varchar(20) NULL,
`POP_CODE` varchar(5) NULL,
`BAR_CODE` varchar(20) NULL,
`QBO` int(11) NULL,
`WEIGHT` decimal(10,4) NULL,
`INV_TAX` decimal(10,4) NULL,
`TAX_TYPE` varchar(5) NULL,
`VOC` decimal(10,4) NULL,
`HAZ_MAT_CODE` varchar(10) NULL,
`VOC_TYPE` varchar(6) NULL,
`QHD` int(11) NULL,
`BONUS_POINTS` decimal(10,2) NULL,
`PURCH_QTY` int(11) NULL,
`MIN_SALE_QTY` int(11) NULL,
`HOLD_UNIT_POS` varchar(1) NULL,
`COST_OR_POS` varchar(1) NULL,
`DEF_QOH` int(11) NULL,
`RENTAL_OUT_QTY` int(11) NULL,
`EXC_MIN_FORCE_FRT` varchar(1) NULL,
`AUTO_COST_GP` decimal(6,2) NULL,
`CRITICAL` varchar(1) NULL,
PRIMARY KEY (`ID`,`LOCATION`,`UNIT_MEASURE`),
CONSTRAINT `FK_INVL_LOCATION_UNIT_MEASURE_INVENTORY` FOREIGN KEY (`ID`) REFERENCES `perfsoft`.`inventory` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `FK_INVL_LOCATION_UNIT_MEASURE_LOCATION` FOREIGN KEY (`LOCATION`) REFERENCES `perfsoft`.`location` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_INVL_LOCATION_UNIT_MEASURE_UNIT_MEASURE` FOREIGN KEY (`UNIT_MEASURE`) REFERENCES `perfsoft`.`unit_measure` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
KEY `FK_INVL_LOCATION_UNIT_MEASURE_LOCATION`(`LOCATION`),
KEY `FK_INVL_LOCATION_UNIT_MEASURE_UNIT_MEASURE`(`UNIT_MEASURE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `perfsoft`.`RG_TEMP_1943023349_186` SELECT `ID`,`LOCATION`,`UNIT_MEASURE`,`STANDARD_PACK`,`CURR_SOURCE`,`WH_COST`,`QOH`,`QOO`,`DUE_DATE`,`REORDER_LEVEL`,`STOCK_LEVEL`,`WH_LOC`,`POP_CODE`,`BAR_CODE`,`QBO`,`WEIGHT`,`INV_TAX`,`TAX_TYPE`,`VOC`,`HAZ_MAT_CODE`,`VOC_TYPE`,`QHD`,`BONUS_POINTS`,`PURCH_QTY`,`MIN_SALE_QTY`,`HOLD_UNIT_POS`,`COST_OR_POS`,`DEF_QOH`,`RENTAL_OUT_QTY`,`EXC_MIN_FORCE_FRT`,`AUTO_COST_GP`,`CRITICAL` FROM `perfsoft`.`invl_location_unit_measure`;

DROP TABLE `perfsoft`.`invl_location_unit_measure`;

ALTER TABLE `perfsoft`.`RG_TEMP_1943023349_186` RENAME TO `invl_location_unit_measure`;

SET FOREIGN_KEY_CHECKS=@ORIGINAL_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@ORIGINAL_UNIQUE_CHECKS;
SET SQL_MODE=@ORIGINAL_SQL_MODE;

steveshourds

'InnoDB', '', '
=====================================
110827 13:03:06 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 17 seconds

---

BACKGROUND THREAD

---

srv_master_thread loops: 290 1_second, 290 sleeps, 25 10_second, 41 background, 41 flush
srv_master_thread log flush and writes: 292

---

SEMAPHORES

---

OS WAIT ARRAY INFO: reservation count 16, signal count 16
Mutex spin waits 6, rounds 180, OS waits 2
RW-shared spins 14, rounds 420, OS waits 14
RW-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 0.00 RW-excl

---

LATEST FOREIGN KEY ERROR

---

110827 12:14:47 Error in foreign key constraint creation for table `perfsoft`.`rg_temp_1943023349_188`.
A foreign key constraint of name `perfsoft`.`FK_INVL_LOCATION_UNIT_MEASURE_INVENTORY`
already exists. (Note that internally InnoDB adds ''databasename''
in front of the user-defined constraint name.)
Note that InnoDB''s FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.

---

TRANSACTIONS

---

Trx id counter 40DD36
Purge done for trx''s n:o < 40DD23 undo n:o < 0
History list length 1193
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 85, query id 11813 localhost ::1 root
---TRANSACTION 0, not started
MySQL thread id 84, query id 11808 localhost ::1 root
---TRANSACTION 0, not started
MySQL thread id 83, query id 11803 localhost ::1 root
---TRANSACTION 0, not started
MySQL thread id 82, query id 11799 localhost ::1 root
---TRANSACTION 0, not started
MySQL thread id 81, query id 11752 localhost ::1 root
---TRANSACTION 0, not started
MySQL thread id 80, query id 11760 localhost ::1 root
---TRANSACTION 40DD35, not started
MySQL thread id 79, query id 11764 localhost ::1 root
---TRANSACTION 0, not started
MySQL thread id 78, query id 11738 localhost ::1 root
---TRANSACTION 40DD1C, not started
MySQL thread id 12, query id 11610 localhost 127.0.0.1 root
---TRANSACTION 0, not started
MySQL thread id 13, query id 11823 localhost 127.0.0.1 root
show engine innodb status
---TRANSACTION 40D808, not started
MySQL thread id 8, query id 11778 localhost ::1 root

---

FILE I/O

---

I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o''s: 0, sync i/o''s: 0
Pending flushes (fsync) log: 0; buffer pool: 0
4094 OS file reads, 438 OS file writes, 73 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

---

INSERT BUFFER AND ADAPTIVE HASH INDEX

---

Ibuf: size 1, free list len 5, seg size 7, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 4425293, node heap has 15 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 12689235210
Log flushed up to 12689235210
Last checkpoint at 12689235210
0 pending log writes, 0 pending chkp writes
43 log i/o''s done, 0.00 log i/o''s/second

---

BUFFER POOL AND MEMORY

---

Total memory allocated 2197815296; in additional pool allocated 0
Dictionary memory allocated 4049372
Buffer pool size 131072
Free buffers 126792
Database pages 4265
Old database pages 1594
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 3, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4083, created 182, written 383
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s
LRU len: 4265, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

---

ROW OPERATIONS

---

0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 5112, state: waiting for server activity
Number of rows inserted 19299, updated 0, deleted 0, read 5582812
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

---

END OF INNODB MONITOR OUTPUT
============================
'

steveshourds

There is one field in this table that is different, and the compare tools wants to rebuild it.

Why do a rebuild, when a simple ALTER TABLE would seem to be much simpler?

neil.anderson

Hi Steve,

Thanks for trying out the tool.

It looks like a bug to me. I would hazard a guess that when we do a rebuild and create a temp table the constraint names are not unique because we obtained them from the table we are rebuilding so MySQL is complaining. I'll look into that and get back to you asap.

I'll also try and find you an answer as to why we do a rebuild here instead of an alter.

Neil

steveshourds

Removing the CONSTRAINT clause fixed the issue. But still seems like simple ALTER TABLE should be provided instead of entire table rebuild.

Otherwise, the tool is exactly what is needed, and is very easy to use. Just need efficient scripts..

Thanks.

steveshourds

Any update on this issue. My trial period expires today and i 'd like to make a decision and whether or not to buy the tool.

thanks