From: william drescher
Date: August 20 2012 2:35pm
Subject: Re: update doesn't
List-Archive: http://lists.mysql.com/mysql/228018
Message-Id: <k0thv3$h8$1@ger.gmane.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 8/20/2012 10:09 AM, Mogens Melander wrote:
>
> On Sun, August 19, 2012 18:19, william drescher wrote:
>> On 8/17/2012 12:13 PM, Rik Wasmus wrote:
>>>> I get 1 row affected, but the status does not change when I look
>>>> at the row.
>>>>
>>>> If I set it to 'X' it does change.
>>>>
>>>> To make it even more wacky, if I (using phpMyAdmin) change it to
>>>> 'H' it will change and the row is shown change, but when I go to
>>>> examine the row (using the pencil icon=Edit) it changes back to 'W'.
>>>>
>>>> Either there is something really strange or my mysql is possessed.
>>>>
>>>> I am using Server version: 5.1.63-0ubuntu0.10.04.
>>>>
>>>> Anyone have any thoughts about this or suggestions on how to
>>>> debug it?
>>>
>>> 1) One thing that _could_ do this is a trigger. Does SHOW TRIGGERS; show
>>> any
>>> that could be doing this?
>>>
>>> 2) However, in 99.999% of cases, it is just a logic error in the
>>> application
>>> (be it your application or PHPMyAdmin), not anything in MySQL. Can you
>>> connect
>>> with the command line client, run the UPDATE statement, en then check
>>> what the
>>> SELECT shows? If it shows a correct result... the problem ain't in MySQL
>>> itself.
>>>
>> mysql> select status from tasks;
>> +--------+
>> | status |
>> +--------+
>> | W      |
>> +--------+
>> 1 row in set (0.00 sec)
>>
>> mysql> update tasks set status= 'H';
>> Query OK, 1 row affected (0.00 sec)
>> Rows matched: 1 Changed 1 Warnings: 0
>>
>> mysql> select status from tasks;
>> +--------+
>> | status |
>> +--------+
>> | W      |
>> +--------+
>> 1 row in set (0.00 sec)
>>
>> whoops
>>
>> bill
>>
>
> Maybe a "SHOW CREATE TABLE `tasks`\g" could shed some light.
>
I can't figure out how to get puTTY to log the session so I can't 
cut and paste the output, but here is a phpMySQL export of the 
table with the extra stuff typed in from the create table

task_id mediumint(9) NOT NULL AUTO_INCREMENT
status char(1) NOT NULL DEFAULT ''
priority char(1) NOT NULL
due_date_time datetimeNOT NULL
hold_date_time datetime NOT NULL
review_date_time datetime  Default NULL
requestor varchar(10) NOT NULL
performer varchar(10) NOT NULL
repeat_frequency char(1) NOT NULL
repeat_time char(2) NOT NULL
repeat_from char(1) NOT NULL
task_title varchar(60) NOT NULL
description text Yes NULL
history text Yes NULL
function_to_run varchar(80) DEFAULT NULL
last_access datetime NOT NULL
completed datetime NOT NULL
notify tinyint(1) DEFAULT NULL

PRIMARY kEY('TASK_ID')
UNIQUE KEY 'performer' ('performer', 'status', priority', 
due_date_time', 'task_id')
UNIQUE KEY 'requestor' ('requestor', prirority','due_date_time', 
'task_id')
ENGINE=InnoDB AUTO_INCREMENT=312 DEFAULT CHARSET=ascii

As a workaround I changed "status" to "task_status" and now it 
works just fine.


bill