wierd MySQL problem

[anshul]

Hi all,

I encountered mysterious problem with MySQL and PHP.

UPDATE command is not working on MySQL configured on Apache 2 with Windows 2000 Server.

It pulled my hairs for atleast 5 hours.

MySQL says Affected rows: 0 (Query took ~ sec) always?

Now, I've decided to move all PHP files and db to remote server.

Can anyone suggest possible reasons for above errors?
(I know everything was working until saturday; [U]may be I modified table structure that prevents table updates[/U]?)

[PeteM]

Can you paste your query here?

Pete

[anshul]

Yes of course. Thanks for reply.

CREATE TABLE `rcms_rankings_google` (
`id` bigint(50) unsigned NOT NULL auto_increment,
`campaign_id` int(10) unsigned NOT NULL default '0',
`keyword` varchar(255) NOT NULL default '',
`uri_result` varchar(255) NOT NULL default 'Not Found',
`count_recent` int(3) unsigned NOT NULL default '0',
`count_last` int(3) unsigned NOT NULL default '0',
`timestamp_recent` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`timestamp_last` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
)

No UPDATE query is working! Like:

UPDATE rcms_rankings_google SET count_recent='23', count_last='23' WHERE id='10'

I'm using persistent db connection mysql_pconnect()

[coopster]

The affected rows are zero so it is stating that it did not find a row with an id of value '10'. Have you looked at the data in the table? If there is an id with a value of '10' and the two columns you are trying to update already contain the value you are trying to update them with then the query is functioning as designed.

Similar thread with more details:
[webmasterworld.com...]

[anshul]

I'm not sure, however that problem is solved by changing table structure:

`timestamp_recent` varchar(255) default NULL,
`timestamp_last` varchar(255) default NULL,

you are trying to update already contain the value you are trying to update

Thanks you; this may also be the source of error. However I expected, MySQL should overwrite that row and reported 1 row updated.

[coopster]

If you set a column to the value it currently has, MySQL notices this and does not update it.

Resource: UPDATE [dev.mysql.com]

Nope, MySQL will not perform an update unless there really is an update to perform. If you tell it to update a row where a column is currently equal to the value you are trying to update to, MySQL will not update that column. More details in the link provided earlier.