[prev in list] [next in list] [prev in thread] [next in thread] 

List:       msql-mysql-modules
Subject:    Re: update query returns wrong number of rows affected
From:       "Vladimir V. Kolpakov" <w () sfgate ! com>
Date:       2004-09-16 5:25:23
Message-ID: 20040916052523.GA25207 () sfgate ! com
[Download RAW message or body]

Rudy, --

On Wed, Sep 15, 2004 at 06:24:07PM -0700, Dave Dyer wrote:
> > > > and to me "affected rows" means changed rows.
> > For one, it's reliable to say "change this and tell me
> > how many changed" it's not reliable to say "if I changed this, how many
> > would change" and then say "ok do it" and expect the number of changed
> > records to be the same.

I would add "reliable" here means lack of race conditions.
For those of us who care about consistency of apps based on
non-transactional engine, it makes lots of sense.
Mysql provides such info, so it should not be an issue for perl layer.

> > > Dave Dyer wrote:
> > > > Maybe it's always been this way, but the C api makes the number
> > > > of changed rows easily available, I find it very useful.

No, that's DBD has been changed at some point,
for compatibility with oracle and other DBD-s.

mysql_client_found_rows() can alter this behavior,
however to me something like $sth->affected_rows()
would be more convenient.

On Wed, Sep 15, 2004 at 09:51:24PM -0400, Rudy Lippan wrote:
> eval {
> $dbh->begin_work();
> my $count = $do_count->execute();
> $do_update->execute();  # this better not be updateing a different set
> # Assuming ANSI default transaction isolation level.
> $dbh->commit();
> }; if (my $e = $@) {
> eval {$dbh->rollback()};
> die die die "I am aweary, aweary, /  Oh God, that I were dead! $e";
> }

Use transaction just for return updated rows count? Wow.
Why don't just implement something like $sth->updated_rows() instead?
Why switch over to InnoDB at all, if apps is happy with MyISAM?
Pretty strong design decision...
I guess it was a joke.

> And think of it from the point of a record that had a last-updated-time that
> *should* probably be changed when you do the update and that would cause
> thousands of extra timestamp updates that you don't need, or think of it with 
> update triggers in mind they would have to fire, right? So then
> you would have a metric boatlaod of extra trigers that could or could not be
> updating something.

Yes, if apps *must do so* (update timestamp) always, it makes sense.

Assumption here is mysql does timestamp update always,
even if engine detected no actual updates made
in other [then timestamp] columns.
Is it really so?
Let's see...
    --------------------------------------------
    w@kane:~>mysql -hkane test
    Your MySQL connection id is 11244 to server version: 4.0.20-standard-log
    mysql> DROP TABLE IF EXISTS t1
    Query OK, 0 rows affected (0.00 sec)

    mysql> CREATE TABLE t1 (id INT , txt TIMESTAMP )
    Query OK, 0 rows affected (0.10 sec)

    mysql> INSERT INTO t1 (id) VALUES (11)
    Query OK, 1 row affected (0.00 sec)

    mysql> INSERT INTO t1 (id) VALUES (12)
    Query OK, 1 row affected (0.00 sec)

    mysql> SELECT * FROM t1
    +------+----------------+
    | id   | tstamp         |
    +------+----------------+
    |   11 | 20040915213930 |
    |   12 | 20040915213930 |
    +------+----------------+
    2 rows in set (0.00 sec)

    mysql> SELECT BENCHMARK(5000000,LOG2(2))
    +----------------------------+
    | BENCHMARK(5000000,LOG2(2)) |
    +----------------------------+
    |                          0 |
    +----------------------------+
    1 row in set (3.46 sec)

    mysql> UPDATE t1 SET id=11 WHERE id=11
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 1  Changed: 0  Warnings: 0

    mysql> SELECT * FROM t1
    +------+----------------+
    | id   | tstamp         |
    +------+----------------+
    |   11 | 20040915213930 |
    |   12 | 20040915213930 |
    +------+----------------+
    2 rows in set (0.00 sec)
    --------------------------------------------

I don't see any updated timestamp in id=11 row.
That's correct behaviour,
as long as we define that "update attempt" means different then "updated".
It would be really bad, if trigger would fire on any attempt
of update instead actual change of value.

Back again,

> *should* probably be changed when you do the update

in this case there is no difference between "updated"
and "matched" rows *anyway*: since 1 column (timestamp)
forced to update always, "matched rows" and "updated rows"
counts will be always the same, and whole subject our
discussion will not have a ground.

On the other hands, in more natural case, when timestamp
need to be updated *only if* any other columns updated,
hiding "updated" count seems to be a problem.

Good designed apps requires to use "updated" count.

In oracle, for example,
it's real pain (if even possible) to define trigger firing on
"update timestamp only if any of other columns were really updated".
If it gets defined, it conflicts with explicit set value.

In mysql such behaviour is set by default.

What is the reason push mysql behave badly way?
Mysql (so far) is able to fire trigger only if real updates made,
and still being usable for event-driven applications.

Compatibility is important, but why not with mysql? :)

Wherever we do bulk insert or massive replace,
it's necessary to work around oracle weakness anyway.
AFAIK oracle can not
  - REPLACE
  - bulk INSERT

How mysql API can be compatible with something does not exist?
How perl application can be designed portable way here,
other then having separate logic blocks for every database type?

Oracle's "affected rows" won't care about actual updates:
    --------------------------------------------
    w@kane:~>yasql --host=kane --sid=test -q
    @test!kane> DROP TABLE t1;
    0 rows affected (0.11 seconds)

    @test!kane> CREATE TABLE t1 (id NUMBER , txt VARCHAR2(2) );
    0 rows affected (0.03 seconds)

    @test!kane> INSERT INTO t1 (id,txt) VALUES (11,11);
    1 row affected (0.01 seconds)

    @test!kane> INSERT INTO t1 (id,txt) VALUES (12,12);
    1 row affected (0.00 seconds)

    @test!kane> SELECT * FROM t1;
      ID TXT
    ---- ----
      11 11
      12 12
    2 rows selected (0.01 seconds)

    @test!kane> UPDATE t1 SET txt=12 WHERE id=12;
    1 row affected (0.02 seconds)
    --------------------------------------------
Oracle returns just matched rows as "affected",
even if no real affects made.

Bulk INSERT and REPLACE are missing in ora9i:
    --------------------------------------------
    @test!kane> INSERT INTO t1 (id,txt) VALUES (14,14), (15,15), (16,16);
    ORA-00933: SQL command not properly ended (DBD ERROR: error possibly near <*> \
indicator at char 38 in 'INSERT INTO t1 (id,txt) VALUES (14,14)<*>, (15,15), \
(16,16)')  Query: INSERT INTO t1 (id,txt) VALUES (14,14), (15,15), (16,16)

    @test!kane> REPLACE INTO t1 (id,txt) VALUES (12,12);
    ORA-00900: invalid SQL statement
    Query: REPLACE INTO t1 (id,txt) VALUES (12,12)
    --------------------------------------------

So it's clear for oracle here is "nothing to count about".
Here we [mysql] go, our own [incompatible] way.
;)


Regards,
--w

-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/perl?unsub=msql-mysql-modules@progressive-comp.com



[prev in list] [next in list] [prev in thread] [next in thread]