can not update col2, but col1 no problem

Hi,

I checked a lot of corners, but I can't find the reason for not updating col2, while update of col1 works fine.
Also tested: creating table 'dummy' with statements as generated by Heidi -> update is fine !???

Here impressions of what I did and tested:

original table as imported in mySQL from flat-file
and generated by Heidi:

CREATE TABLE `t_cc_vr` (
...
`BETRAG` DOUBLE(10,2) NULL DEFAULT NULL,
`STAND` DOUBLE(10,2) NULL DEFAULT NULL,
...
)
COLLATE='latin1_german2_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

UPDATE T_CC_VR SET BETRAG=123456.12 WHERE K_ID=3 LIMIT 1;
/* 1 rows affected, 0 rows found. Duration for 1 query: 0,000 sec. */

UPDATE T_CC_VR SET STAND = 111 WHERE K_ID = 3 ;
/* 0 rows affected, 0 rows found. Duration for 1 query: 0,000 sec. */

refreshing the table-outpüut shows indeed the old value for STAND, while BETRAG was updated successfuly !

There is no warning, no error-message, just update is ignored for column STAND !

I created a dummy-table by geration from Heidi:

CREATE TABLE dummy (
`BETRAG` DOUBLE(10,2) NULL DEFAULT NULL,
`STAND` DOUBLE(10,2) NULL DEFAULT NULL
);

Insert and update happen successful without errors !?

What can I test more ?
How and where to search for not-updating-reasons ?

Thanks for any tip,

Laode

Check what SHOW WARNINGS says, please.

Hi,

Thanks for taking care for this case. I hope I can provide all Heidi-feedback as you need.
There is no pop-up message or anything like error-message.

Here is the feedback as of bottom-window:

UPDATE T_CC_VR SET STAND = 1958.02 WHERE K_ID = 3 ;
/* 0 rows affected, 0 rows found. Duration for 1 query: 0,016 sec. */
show warnings;
/* 0 rows affected, 0 rows found. Duration for 1 query: 0,000 sec. */

my next tests will be:

1. create identical table with other name
2. run "insert as select..." to copy all columns, all records into new table
3. test update in new table

if that does not solve, I will drop this DB and rebuild complete new, running the same loading.

This will cost some days. I will post result here.

LaoDe

You should not put so much effort in unrelated stuff. Could also be your row with k_id=3 already had the value you passed in the UPDATE, in which case the server does not affect any row.

Hi Anse,

Avoiding effort is a nice target, but how to figure out about a new tool, what it can and if not, which particular functionality might crete the problem ?

So far, I copied the table and here is a compare:

update t_cc_vr_copy set STAND = 555.02 where K_ID = 3;
/* 1 rows affected, 0 rows found. Duration for 1 query: 0,015 sec. */
update t_cc_vr set STAND = 555.02 where K_ID = 3;
/* 0 rows affected, 0 rows found. Duration for 1 query: 0,000 sec. */

so I see clearly now, that original table is blocking somehow for unknown reason any update on column STAND. And from the calues it is also very clear now, that this value wasn't in that field before.

Test 2 will be drop table -> create table -> load from flat-file.

I let you know about the result.

cheers Laode

I'm not fully sure of what the problem is but here's my advice:

1. Before doing the UPDATE, run a SELECT query with the same WHERE clause. That's the only way to make sure there are matching rows.

2. Take note of the old values. MySQL won't update a matching row if the new values are the same as the old ones.

3. Run the SELECT query again after the UPDATE to check if the final row values are the expected ones.