Paronity posted 8 years ago posted

I am seeing this issue on several tables accross several installations of MySQL. The way I am seeing it though, is the data is getting updated, but its running the query twice and the second one is failing (even though it shouldn't').



I also notices that if I submit the changes quickly, I don't see the error. Meaning if I change one row, I'll get the error, but then if I change the next row quickly, no error. It's only when the connection sits idle for a minute that it happens. (It only takes about a 15 second delay to hit the error between changes).



The simplest example I have is this table:

CREATE TABLE `survey` (

`id` INT(200) NOT NULL AUTO_INCREMENT,

`full_name` VARCHAR(200) NOT NULL COLLATE 'utf8_unicode_ci',

`email` VARCHAR(200) NOT NULL COLLATE 'utf8_unicode_ci',

`phone` VARCHAR(200) NOT NULL COLLATE 'utf8_unicode_ci',

`address` VARCHAR(200) NOT NULL COLLATE 'utf8_unicode_ci',

`city` VARCHAR(200) NOT NULL COLLATE 'utf8_unicode_ci',

`state` VARCHAR(20) NOT NULL COLLATE 'utf8_unicode_ci',

`age` VARCHAR(10) NOT NULL COLLATE 'utf8_unicode_ci',

`clan` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',

`clansite` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',

`gender` VARCHAR(10) NOT NULL COLLATE 'utf8_unicode_ci',

`longhave` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',

`hoursplayed` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',

`spendgames` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',

`spenddlc` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',

`spendacc` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',

`gamesplay` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',

`gamesplay2` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',

`gamesplay3` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',

`playcomp` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',

`playcomp2` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',

`playcomp3` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',

`clancom` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',

`tourny` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',

`gamingsites` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',

`pay` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',

`wins` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',

`games3` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',

`strangers` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',

`cheaters` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',

`foundhack` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',

`cheatfree` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',

`goal` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',

`newsletter` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',

PRIMARY KEY (`id`)

)

COLLATE='utf8_unicode_ci'

ENGINE=MyISAM

AUTO_INCREMENT=332;



(Stupid table structure, I know - I just work with what I'm given...).



All I am attempting to do is update one row. Any column triggers it.



Updating one row gives me this output:

SHOW STATUS;

SHOW VARIABLES;

USE `warzones_survey`;

UPDATE `survey` SET `clan`='f' WHERE `id`=82 LIMIT 1;

SELECT `id`, `full_name`, `email`, `phone`, `address`, `city`, `state`, `age`, `clan`, `clansite`, `gender`, `longhave`, `hoursplayed`, `spendgames`, `spenddlc`, `spendacc`, `gamesplay`, `gamesplay2`, `gamesplay3`, `playcomp`, `playcomp2`, `playcomp3`, `clancom`, `tourny`, `gamingsites`, `pay`, `wins`, `games3`, `strangers`, `cheaters`, `foundhack`, `cheatfree`, `goal`, `newsletter` FROM `stuff` WHERE `id`=82 LIMIT 1;

SHOW CREATE TABLE `stupid_database`.`stuff`;

SHOW COLLATION;

SHOW ENGINES;

UPDATE `survey` SET `clan`='f' WHERE `id`=82 LIMIT 1;

SELECT `id`, `full_name`, `email`, `phone`, `address`, `city`, `state`, `age`, `clan`, `clansite`, `gender`, `longhave`, `hoursplayed`, `spendgames`, `spenddlc`, `spendacc`, `gamesplay`, `gamesplay2`, `gamesplay3`, `playcomp`, `playcomp2`, `playcomp3`, `clancom`, `tourny`, `gamingsites`, `pay`, `wins`, `games3`, `strangers`, `cheaters`, `foundhack`, `cheatfree`, `goal`, `newsletter` FROM `stupid_database`.`stuff` LIMIT 1000;

SHOW CREATE TABLE `stupid_database`.`stuff`;



It's running it twice for some reason and the second one throws the error.