Forum Replies Created
-
AuthorPosts
-
maxhugen
ParticipantYes, I realised that when I looked into ENUM. I already have a lot of Validation code that handles business rules, so I’m adding a section for ‘database rules’ in the code.
Cheers, Max
ParticipantThank you. I’ll read up on ENUM.
ParticipantHi Peter
I have another MySQL problem, “Unable to Create Database”, see http://forums.mysql.com/read.php?10,609753,609753#msg-609753
Would it be possible to ask for your help with this issue?
ParticipantThanks Peter.
I accept that I should have posted to MySQL forum, guess it’s just that I use SQLyog regularly and didn’t think.
I’ve used your trick as above, and it appears that the MySQL server is 64-bit, as the variable was not rounded down.
The version variables confused me:
version_compile_machine x86_64
version_compile_os Win64
Cheers, Max
ParticipantHi. I don't know why the attachment didn't work, so I zipped it and attached.
ParticipantFWIW, I tried a different way to re-create the local db. Starting with an empty local db, I first did a Schema Sync from the server, and then did the Database Sync. This worked.
I still have no idea why using the dump from the server failed to work correctly though.
ParticipantOK, thanks Peter.
ParticipantHi 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=utf8The 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?
ParticipantThanks very much – working AOK now! 😎
ParticipantPeter, is there a way to do a 'dump' of just one table? I can certainly send you the data as it is not sensitive, and cannot be identified to the client. I guess you would need both the production table data and my dev data (which I sync to)?
The MySQL versions are:
Server: 5.1.51-community
My development machine: 5.1.40-community
Thank you.
ParticipantIt is possible, but I have tested the sync at various times during the day and night (US), and it's the same.
ParticipantHi Peter
I'm using SQLyog v8.71
The table is:
CREATE TABLE `changetracking` (
`ChangeTrackingID` int(11) NOT NULL AUTO_INCREMENT,
`DocID` int(11) NOT NULL,
`TrackingReason` varchar(50) NOT NULL,
`From_PeopleID` int(11) NOT NULL,
`From_Title` varchar(50) DEFAULT NULL,
`To_PeopleID` int(11) NOT NULL,
`To_Title` varchar(50) DEFAULT NULL,
`To_Representing` varchar(50) DEFAULT NULL,
`To_Capacity` varchar(50) DEFAULT NULL,
`To_SortOrder` smallint(4) DEFAULT NULL,
`ApproveMin` decimal(15,4) DEFAULT NULL,
`ApproveMax` decimal(15,4) DEFAULT NULL,
`DateSent` datetime DEFAULT NULL,
`DateReceived` datetime DEFAULT NULL,
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`ChangeTrackingID`),
KEY `DocID` (`DocID`),
KEY `From_PeopleID` (`From_PeopleID`),
KEY `To_PeopleID` (`To_PeopleID`),
CONSTRAINT `changetracking_ibfk_1` FOREIGN KEY (`From_PeopleID`) REFERENCES `apppeople` (`appPeopleID`) ON UPDATE CASCADE,
CONSTRAINT `changetracking_ibfk_2` FOREIGN KEY (`To_PeopleID`) REFERENCES `apppeople` (`appPeopleID`) ON UPDATE CASCADE,
CONSTRAINT `changetracking_ibfk_3` FOREIGN KEY (`DocID`) REFERENCES `changedocs` (`DocID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2147324833 DEFAULT CHARSET=utf8
ParticipantThanks for your help Peter. On a positive note, this was the first time I had to Restore something, so I've learnt a few things. 😎
Participant1. Yes, the comment was on one line:
#element delete – should always be OK2. Yes, this time the Restore was successful !
Peter, while researching this problem, I I think I did find a reference (by you) regarding possible issues with comments in stored procedures during Restore, but I can't find that post now!
Is there a potential problem using the # comment? Should I use /* … */ or — instead?
ParticipantThe err.log showed this:
Query:
/*!50003 CREATE PROCEDURE `DeleteProject`( IN Fproject_id INTEGER, OUT Fresult INTEGER )
BEGIN DECLARE iBeforeCount INT; DECLARE iDeleteCount INT; Set Fresult = 0; START TRANSACTION; foo:BEGIN #element delete – should always be OKSELECT COUNT(*) FROM element WHERE project_id=Fproject_id INTO iBeforeCount; DELETE FROM element WHERE project_id = Fproject_id; SET iDeleteCount = ROW_COUNT(); IF iBeforeCount <> iDeleteCount THEN leave foo; END IF; #elementtype delete – should always be OK
SELECT COUNT(*) FROM elementtype WHERE project_id=Fproject_id INTO iBeforeCount; DELETE FROM elementtype WHERE project_id = Fproject_id; SET iDeleteCount = ROW_COUNT(); IF iBeforeCount <> iDeleteCount THEN leave foo; END IF; #module element – should always be OK
SELECT COUNT(*) FROM moduleelement, module WHERE moduleelement.module_id = module.module_id AND module.project_id = Fproject_id INTO iBeforeCount; DELETE moduleelement FROM moduleelement INNER JOIN module ON module.module_id=moduleelement.module_id WHERE module.project_id = Fproject_id; SET iDeleteCount = ROW_COUNT(); IF iBeforeCount <> iDeleteCount THEN leave foo; END IF; #module delete – should always be OK
SELECT COUNT(*) FROM module WHERE project_id=Fproject_id INTO iBeforeCount; DELETE FROM module WHERE project_id = Fproject_id; SET iDeleteCount = ROW_COUNT(); IF iBeforeCount <> iDeleteCount THEN leave foo; END IF; #job delete – could be locked
SELECT COUNT(*) FROM job WHERE project_id=Fproject_id INTO iBeforeCount; DELETE FROM job WHERE project_id = Fproject_id; SET iDeleteCount = ROW_COUNT(); IF iBeforeCount <> iDeleteCount THEN leave foo; END IF; #projectmember delete – could be locked
SELECT COUNT(*) FROM projectmember WHERE project_id=Fproject_id INTO iBeforeCount; DELETE FROM projectmember WHERE project_id = Fproject_id; SET iDeleteCount = ROW_COUNT(); IF iBeforeCount <> iDeleteCount THEN leave foo; END IF; #project delete – could be locked
SELECT COUNT(*) FROM project WHERE project_id=Fproject_id INTO iBeforeCount; DELETE FROM project WHERE project_id = Fproject_id; SET iDeleteCount = ROW_COUNT(); IF iBeforeCount <> iDeleteCount THEN leave foo; END IF; #
INSERT INTO update_log (task_id, update_type) VALUES (Ftask_id, 3); COMMIT; SET Fresult = 1; END; If Fresult = 0 THEN ROLLBACK; END IF; END */$$
DELIMITER ;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;.. but I think I had SHOW CREATE returned by MySQL returned as single-line once. Very weird. Did you have the dump opened in an editor?
Anyway: Please
1) Execute SHOW CREATE PROCEDURE … . Do you get a single line or not (display in TEXT-mode in SQLyog)
2) Could you try to backup again and see if it repeats itself?
The problem definitely is that all what follows # (on same line) will be a comment.
-
AuthorPosts
