Schema Sync – Constraints – Error

[maxhugen]

I changed some existing constraints to Cascade Delete in my dev db, but it failed to sync with the production db. Not a major prob, I just went to the production db, and updated the Constraints there, but thought you'd like to know.

Queries:

=======

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

USE `cams_kpn`;

/* Alter table in target */

ALTER TABLE `contractamenditems`

DROP FOREIGN KEY `ContractAmendID` ,

ADD CONSTRAINT `ContractAmendID`

FOREIGN KEY (`ContractAmendID`) REFERENCES `contractamends` (`ContractAmendID`) ON DELETE CASCADE ON UPDATE CASCADE ;

/* Alter table in target */

ALTER TABLE `contractamends`

DROP FOREIGN KEY `contractamends_ibfk_1` ,

ADD CONSTRAINT `contractamends_ibfk_1`

FOREIGN KEY (`ContractID`) REFERENCES `contracts` (`ContractID`) ON DELETE CASCADE ON UPDATE CASCADE ;

/* Alter table in target */

ALTER TABLE `contractitems`

DROP FOREIGN KEY `FK_ContractID` ,

ADD CONSTRAINT `FK_ContractID`

FOREIGN KEY (`ContractID`) REFERENCES `contracts` (`ContractID`) ON DELETE CASCADE ON UPDATE CASCADE ;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

Messages:

========

Query: /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */

0 row(s) affected

Execution Time : 0.205 sec

Transfer Time : 0 sec

Total Time : 0.205 sec

---

Query: USE `cams_kpn`

0 row(s) affected

Execution Time : 0.204 sec

Transfer Time : 0 sec

Total Time : 0.205 sec

---

Query: ALTER TABLE `contractamenditems` DROP FOREIGN KEY `ContractAmendID` , ADD CONSTRAINT `ContractAmendID` FOREIGN KEY (`ContractAme…

Error Code: 1005

Can't create table 'cams_kpn.#sql-794_dc4' (errno: 121)

Execution Time : 0 sec

Transfer Time : 0 sec

[peterlaursen]

* Can you share the table structure on target before running the sync script was executed as well as the complete sync script?

* Now if you try again is it still reproducible? Also after MySQL is restarted?

This

Error Code: 1005

Can't create table 'cams_kpn.#sql-794_dc4' (errno: 121)

.. is a server error that tells that the server was not able to create an internal temporary table required to process the statement. The same server error would probably have occured with any client executing the same statement. And the statement is valid (as no error 1064 is returned). There are lots of bugs reported with specific server versions resulting in this. There could also be temporary locking issues or the temporary table could require more diskspace than what is available in /tempdir.

So:

* Also please tell the MySQL server version (also target).

* If you have acces to do so, try to cleanup the /tempdir (“SHOW GLOBAL VARIABLES LIKE 'tmpdir';” will tell you what folder MySQL uses as /tempdir). Sometimes you will find a lot of orphaned temporary files there consuming a lot of diskspace.

[maxhugen]

Hi Peter, I'll do the best I can to help, but please note I'm no expert here – which is why I like, use and recommend SQLyog 🙂

TABLE

=====

I'll start with table `contractamenditems`:

CREATE TABLE `contractamenditems` (																																																																																																																																																																																																																																																																																																											  


`ContractItemID` int(11) NOT NULL,																																																																																																																																																																																																																																																																																																										     


`ContractAmendID` int(11) NOT NULL,																																																																																																																																																																																																																																																																																																										    


`ContractAmendItemID` int(11) NOT NULL AUTO_INCREMENT,																																																																																																																																																																																																																																																																																																					     


`VendorID` int(11) DEFAULT NULL,																																																																																																																																																																																																																																																																																																											   


`AmendItemNo` varchar(50) NOT NULL,																																																																																																																																																																																																																																																																																																										    


`AmendItemAmt` decimal(15,4) NOT NULL,																																																																																																																																																																																																																																																																																																									     


`DateStart` date DEFAULT NULL,																																																																																																																																																																																																																																																																																																											     


`DateEnd` date DEFAULT NULL,																																																																																																																																																																																																																																																																																																												   


`RelatedContractAmendID` int(11) DEFAULT NULL,																																																																																																																																																																																																																																																																																																							     


`AutoBalance` tinyint(2) NOT NULL DEFAULT '0',																																																																																																																																																																																																																																																																																																							     


`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,																																																																																																																																																																																																																																																																																															     


PRIMARY KEY (`ContractAmendItemID`),																																																																																																																																																																																																																																																																																																										   


KEY `ContractAmendID` (`ContractAmendID`),																																																																																																																																																																																																																																																																																																								     


KEY `ContractItemID` (`ContractItemID`),																																																																																																																																																																																																																																																																																																									   


KEY `RelatedContractAmendID` (`RelatedContractAmendID`),																																																																																																																																																																																																																																																																																																					   


KEY `contractamenditems_ibfk_1` (`VendorID`),																																																																																																																																																																																																																																																																																																								  


CONSTRAINT `ContractAmendID` FOREIGN KEY (`ContractAmendID`) REFERENCES `contractamends` (`ContractAmendID`) ON DELETE CASCADE ON UPDATE CASCADE,																																																																																																																																																																																																																																																																															  


CONSTRAINT `contractamenditems_ibfk_1` FOREIGN KEY (`VendorID`) REFERENCES `vendors` (`VendorID`) ON UPDATE CASCADE,																																																																																																																																																																																																																																																																																						   


CONSTRAINT `ContractItemID` FOREIGN KEY (`ContractItemID`) REFERENCES `contractitems` (`ContractItemID`) ON DELETE CASCADE ON UPDATE CASCADE																																																																																																																																																																																																																																																																																   


) ENGINE=InnoDB AUTO_INCREMENT=2106582237 DEFAULT CHARSET=utf8  

The only change to this table was the constraint `ContractAmendID`, which existed, but did NOT have “ON DELETE CASCADE “.

VERSIONS

========

The development db version is 5.1.40-community, production db is 5.1.51-community.

TEMP FILES

==========

Both machines use C:WindowsTEMP.

The Dev machine has 20,618 files, 330Mb (ouch!)

The Production server has 101 files, 54.5Mb

I have now deleted all files from both Temp folders, except for a couple of files being used.

NEXT TEST STEP?

==============

What should I do next? Should I revert the “test” table on the production server to the previous Constraint, and try the sync again?

[peterlaursen]

You shall not do anything. I believe it is a server bug and I have reported it here:

http://bugs.mysql.com/bug.php?id=68286

You can drop + add constraints manually from GUI, because it will generate two ALTER TABLE statements. But if you drop + add in one ALTER TABLE statement you get this error.

Let us wait for the reply from MySQL before we decide if we should generate two ALTER TABLE statements in the script. We would like not to for performance reasons.

[peterlaursen]

Actually you (we) cannot DROP and ADD a FK-CONSTRAINT in a single statement. Even though I think the the server should return a regular syntax error in such case, we will have to fix it in our code – and we are doing now.

[maxhugen]

OK, thanks Peter.

[Author]

Posts