Bug #6744 HEAP / Memory tables randomly result in update errors
Submitted: 22 Nov 2004 2:38 Modified: 22 Nov 2004 13:22
Reporter: Scott MacVicar Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.7 OS:Any (All)
Assigned to: Assigned Account CPU Architecture:Any

[22 Nov 2004 2:38] Scott MacVicar
Description:
Multiple users have reported errors since upgrading to MySQL 4.1.7, specifically it is regarding the HEAP table type.

Error log basically reports
041028  2:21:05 [ERROR] Got error 123 when reading table './vb_database/cpsession'

MySQL error:  123 = Someone has changed the row since it was read; Update with is recoverable

MySQL client library affected are both 4.0 and 4.1 and operating systems that are resulting in this error include FreeBSD, RHEL 3 and Fedora.

Downgrading to MySQL 4.0 resolves this issue as well as issuing a FLUSH TABLE query prior to the SELECT query.

How to repeat:
CREATE TABLE cpsession (
  `userid` int(10) unsigned NOT NULL default '0',
  `cphash` varchar(32) NOT NULL default '',
  `dateline` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`userid`,`hash`)
) ENGINE=HEAP DEFAULT CHARSET=latin1;

Run one script randomly inserting data and another selecting

SELECT *
FROM cpsession
WHERE userid = 1
LIMIT 1

Suggested fix:
Correct locking of HEAP tables.
[22 Nov 2004 13:22] Sergei Golubchik
fixed in 4.1.8
(duplicate of http://bugs.mysql.com/6748