Wrong update or a Mysql error?


#1

Hi,

I am having problems with a mysql database hosted at dreamhost and didnt know what is wrong. I connect throught jdbc(java) to a mysql database. Got problems at some parts of the program so i have debuged & breakpoints at sql sentences & copy and paste to the phpmyadmin to see what is wrong with the sql code(so i can repeat the problem at java and phpmyadmin).

I want to do an update of all sets at the table with NULL value at a fixed field. The update is:

update tickets set (tickets.money=4) where ((tickets.host=‘athlon’) and (tickets.money is null))

The problem is that this update alters another field not included in the update. Using the example table:


phpMyAdmin SQL Dump

version 2.5.2-pl1

http://www.phpmyadmin.net

Host: mydatabaseat.dreamhost.com

Generation Time: Apr 07, 2005 at 12:51 PM

Server version: 4.0.20

PHP Version: 4.3.10

Database : mydatabase

--------------------------------------------------------

Table structure for table tickets

Creation: Apr 05, 2005 at 03:08 AM

Last update: Apr 07, 2005 at 12:36 PM

Last check: Apr 07, 2005 at 12:08 PM

DROP TABLE IF EXISTS tickets;
CREATE TABLE tickets (
TICKETID int(11) NOT NULL default ‘0’,
DATENEW timestamp(14) NOT NULL,
HOST varchar(100) NOT NULL default ‘’,
MONEY int(11) default NULL,
PAYTYPE int(11) default NULL,
PERSON varchar(100) NOT NULL default ‘’,
PRIMARY KEY (TICKETID),
KEY TICKETS_INX_1 (DATENEW)
) TYPE=MyISAM;

Dumping data for table tickets

INSERT INTO tickets (TICKETID, DATENEW, HOST, MONEY, PAYTYPE, PERSON) VALUES (9, 2005-04-04 11:41:49, ‘test’, 2, 102, ‘Admin’),
(7, 2005-04-04 11:41:49, ‘test’, 2, 102, ‘Admin’),
(40, 2005-04-07 13:11:09, ‘athlon’, NULL, 100, ‘Admindor’),
(49, 2005-04-07 10:11:09, ‘athlon’, NULL, 100, ‘Admindor’),
(50, 2005-04-07 16:11:09, ‘athlon’, NULL, 102, ‘Admindor’),
(66, 2005-04-06 15:16:33, ‘localhost’, 3, 100, ‘Admindor’),
(67, 2005-04-06 15:16:33, ‘localhost’, 3, 102, ‘Admindor’),
(71, 2005-04-07 19:11:09, ‘athlon’, NULL, 102, ‘Admindor’);

all sets changed by the update(4 in this case) got the column DATENEW change to a new value(the same one for 4 rows) but i dont use that column at the update sentence at all!!!

It pissed my a lot. So:

Somebody see what i am doing wrong at the update?
May be an error at the server?

I dont want to ask to dreamhost support until be sure that isnt a programing error.

Thanks,

Salva

P.D: if some dreamhost staff member read the post i can give you password/real data to test it.


#2

Timestamps always show the last modified date of the record.

I looked at mysql.org to find a way for it not to get updated, but it was not… “Intuitive”. If it were me I might try:

update tickets set (tickets.money=4 datenew=datenew) where ((tickets.host=‘athlon’) and (tickets.money is null))

And see if that worked.

I40.com - Home Page
MP3Mystic - Personal Streaming Music server.
(Neither of these sites are still hosted at dreamhost)


#3

Hi jrahaim ,

First, Thanks a lot for your help.

It works if i use your suggestion for the timestamp field at the update. I didnt know that feature of timestamp.

ty again