MySQL Bugs: #53295: Setting FK from another db throws "Attempted to read or write protected memory"

Bug #53295	Setting FK from another db throws "Attempted to read or write protected memory"
Submitted:	29 Apr 2010 19:01	Modified:	7 Jun 2010 5:51
Reporter:	Antonio Fajardo	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S2 (Serious)
Version:	5.2.20	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any

Description:
I have several databases. When I try to add a new Foreign Key constraint, related to other data base table, the system throws the error "Attempted to read or write protected memory" and hangs, showing the same error if I try to close the "Alter table" subwindow. The only option to continue is to quit the application and start again, but the error persists every time I want to alter the altered table.

How to repeat:
Create 2 databases and create a foreign key constraint from one table of the 1st database related with another table of the 2nd database. Here is my SQL query before the error:

ALTER TABLE `HostStore`.`H_CustOrder` 
  ADD CONSTRAINT `FK_H_CustOrder_H_Organization`
  FOREIGN KEY (`HostId` , `UserOrgId` )
  REFERENCES `HostGlobal`.`H_Organization` (`HostId` , `OrgId` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
, ADD INDEX `FK_H_CustOrder_H_Organization` (`HostId` ASC, `UserOrgId` ASC) ;

I guess the severity should be downgraded to 'Serious'.

Do you use local or remote server?

Do you have same problem by using CLI instead of Workbench?

Hi Susanne,

I'm using local database (localhost) and from CLI the ALTER TABLE works fine. The error only shows from WorkBench.

Regards,
Antonio

Could you please provide the create table statement for both tables involved?. Thanks in advance.

Hi,

Here are the 2 CREATE statements (I'm not including all tables involved).

delimiter ;
USE HostStore;

CREATE TABLE `H_CustOrder` (
  `HostId` varchar(10) CHARACTER SET utf8 NOT NULL,
  `OrderId` varchar(15) CHARACTER SET utf8 NOT NULL,
  `UserId` varchar(15) CHARACTER SET utf8 NOT NULL,
  `UserOrgId` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `StatusOrderId` varchar(10) CHARACTER SET utf8 NOT NULL,
  `BudgetReqId` varchar(12) CHARACTER SET utf8 DEFAULT NULL,
  `ShippingCostBase` decimal(10,2) NOT NULL,
  `OrgFromId` varchar(10) CHARACTER SET utf8 NOT NULL,
  `CCardNumber` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  `CCardHolderName` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  `CCardExpirationDate` datetime DEFAULT NULL,
  `TypeCCardId` varchar(2) CHARACTER SET utf8 DEFAULT NULL,
  `CCardCompleteDate` datetime DEFAULT NULL,
  `ShipRecipient` varchar(100) CHARACTER SET utf8 NOT NULL,
  `ShipCountryId` varchar(3) CHARACTER SET utf8 NOT NULL,
  `ShipStateId` varchar(2) CHARACTER SET utf8 NOT NULL,
  `ShipDistrictId` smallint(5) NOT NULL,
  `ShipAddress1` varchar(80) CHARACTER SET utf8 NOT NULL,
  `ShipAddress2` varchar(80) CHARACTER SET utf8 NOT NULL,
  `ShipZip` varchar(10) CHARACTER SET utf8 NOT NULL,
  `ShipComments` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `InvName` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `InvTaxId` varchar(13) CHARACTER SET utf8 DEFAULT NULL,
  `InvCountryId` varchar(3) CHARACTER SET utf8 DEFAULT NULL,
  `InvStateId` varchar(2) CHARACTER SET utf8 DEFAULT NULL,
  `InvDistrictId` smallint(5) DEFAULT NULL,
  `InvAddress1` varchar(80) CHARACTER SET utf8 DEFAULT NULL,
  `InvAddress2` varchar(80) CHARACTER SET utf8 DEFAULT NULL,
  `InvZip` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `CreateDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ActivateDate` datetime DEFAULT NULL,
  `CancelDate` datetime DEFAULT NULL,
  PRIMARY KEY (`HostId`,`OrderId`),
  KEY `FK_H_CustOrder_C_StatusOrder` (`StatusOrderId`),
  CONSTRAINT `FK_H_CustOrder_C_StatusOrder` FOREIGN KEY (`StatusOrderId`) REFERENCES `c_statusorder` (`StatusOrderId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

USE HostGlobal;

CREATE TABLE `H_Organization` (
  `HostId` varchar(10) CHARACTER SET utf8 NOT NULL,
  `OrgId` varchar(10) CHARACTER SET utf8 NOT NULL,
  `Description` varchar(80) CHARACTER SET utf8 NOT NULL,
  `TypeOrgId` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT 'C',
  `TaxId` varchar(15) CHARACTER SET utf8 NOT NULL,
  `CountryId` varchar(3) CHARACTER SET utf8 NOT NULL,
  `StateId` varchar(2) CHARACTER SET utf8 NOT NULL,
  `DistrictId` smallint(5) NOT NULL,
  `Address1` varchar(80) CHARACTER SET utf8 NOT NULL,
  `Address2` varchar(80) CHARACTER SET utf8 NOT NULL,
  `Zip` varchar(10) CHARACTER SET utf8 NOT NULL,
  `LanguageId` varchar(5) CHARACTER SET utf8 NOT NULL,
  `Comments` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`HostId`,`OrgId`),
  KEY `FK_H_Organization_C_District` (`CountryId`,`StateId`,`DistrictId`),
  KEY `FK_H_Organization_C_TypeOrganization` (`TypeOrgId`),
  KEY `FK_H_Organization_C_Language` (`LanguageId`),
  CONSTRAINT `FK_H_Organization_C_District` FOREIGN KEY (`CountryId`, `StateId`, `DistrictId`) REFERENCES `c_district` (`CountryId`, `StateId`, `DistrictId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_H_Organization_C_Host` FOREIGN KEY (`HostId`) REFERENCES `c_host` (`HostId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_H_Organization_C_Language` FOREIGN KEY (`LanguageId`) REFERENCES `c_language` (`LanguageId`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `FK_H_Organization_C_TypeOrganization` FOREIGN KEY (`TypeOrgId`) REFERENCES `c_typeorganization` (`TypeOrgId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Thank you for the feedback.

I can not repeat described behavior with version 5.2.22 and 2 tables. Please try with version 5.2.22 and if problem still exists provide ouptut of SHOW CREATE TABLE for all tables foreign key constraint set for.

I tried the troubled foreign key with version 5.2.22 and the error didn't show up.

Thank you,
Antonio

Thank you for the feedback.

Closed as "Verified" because last comment.