3

I have a federated table on mySQL dbForge 6.0.265 which I created like this:

USE my_db;
CREATE TABLE my_db.federated_tbl_resources (
  Id int(11) NOT NULL AUTO_INCREMENT,
  resourceType varchar(255) NOT NULL,
  cultureCode varchar(10) NOT NULL,
  resourceKey varchar(128) NOT NULL,
  resourceValue longtext NOT NULL,
  PRIMARY KEY (Id),
  UNIQUE INDEX UK_tbl_string_resources (cultureCode, resourceKey, resourceType)
)
ENGINE=FEDERATED
AUTO_INCREMENT = 27339
AVG_ROW_LENGTH = 219
CHARACTER SET utf8
CONNECTION='connection_string'
COLLATE utf8_general_ci;
All the select queries work great.

Everything works well except UPDATE command fails on the resourceValue column.

The output of the update query shows that it was performed successfully, yet the value remains unchanged.

After a lot of digging around I have discovered this relevant post and this official mysql bug that has been around since 2008 and has been happily ignored, which prevents updates on blob columns (LONGTEXT being one of them).

Has anyone ever come across this and is there a trick to circumvent this mysql limitation?

Thanks

Nick
  • 2,877
  • 2
  • 33
  • 62
  • It's the desert out there regarding this problem. I had to change the longtext to varchar(21345) on target and source database to get everything working. (21345 being the largest allowed byte size). It works well now, but still curious about this bug – Nick Oct 15 '13 at 15:29

1 Answers1

1

A workaround is to first update the cell to NULL or to an empty string ('') and then update it again

Poul Kruijt
  • 69,713
  • 12
  • 145
  • 149