List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 2 1999 4:46pm
Subject:Re: Rematch of modified record in update
View as plain text  
>>>>> "Hugo" == Hugo van der Sanden <hugo@stripped>
> writes:

Hugo> In <14272.14293.779425.635005@stripped>, Michael Widenius writes:
Hugo> Were that the case, I would have expected the same to happen in my
Hugo> test table with one record. However, it is not how I understand the SQL
Hugo> should work: regardless of implementation, I would expect the effect to
Hugo> be the same as if the where clause were first applied to find the set
Hugo> of matching records, and then the update clause applied (once!) to each
Hugo> member of the matching set.

Hugo> I suspect the fact that I am updating one field of a multi-field key is
Hugo> part of the reason for the problem.

Monty> Yes, this explain this case (This is fixed in 3.23.2)

Hugo> I've finally managed to get 3.23.2 installed here, and confirmed that
Hugo> the update works correctly with that version. Is there any prospect that
Hugo> the relevant patches will make it into a 3.22 release at some point in
Hugo> the near future? If not, I'd appreciate being pointed at the patches
Hugo> so I can attempt to retrofit them myself.

The problem is that there was a lot of things involved in this patch.
If we would put this in 3.22, then 3.22 have to be declared as 'alpha' 

You can find 'most things' that changed by doing:

diff -c mysql-3.22.25/sql/sql_update.c mysql-3.23.2/sql/sql_update.c 

Some calls have got more parameters (like make_select), so to get this
to work in 3.22 you have to change the call back to the old format and 
check that everything still works.

Hugo> As well as the reluctance to touch software marked 'alpha', the person
Hugo> who installed 3.23.2 on a test machine here also thought to check the
Hugo> benchmarks comparing 3.22.25 and 3.23.2, and while most operations are
Hugo> as fast or faster, there are a couple of worrying anomalies:

Hugo> Benchmark DBD suite: 2.2

Hugo> Running tests on:    SunOS 5.7 sun4u

Hugo> alpha:  Server version:      MySQL 3.23.2 alpha
Hugo> stable: Server version:      MySQL 3.22.25
Hugo> [...]
Hugo> alpha:  All 8 test executed successfully
Hugo> stable: All 8 test executed successfully

Hugo> Tests with estimated time have a + at end of line

Hugo> Totals per operation:
Hugo>         Operation             seconds     usr     sys     cpu   tests
Hugo> [...]
Hugo> alpha:  select_key              91476  123.29   53.27    0.00  200000 +
Hugo> stable: select_key                300  138.33   56.57    0.00  200000 
Hugo> alpha:  select_range             1945   38.02   30.16    0.00   25410 +
Hugo> stable: select_range              509   36.60   24.98    0.00   25420 
Hugo> alpha:  select_range_prefix      9551   13.44    7.20    0.00   25000 +
Hugo> stable: select_range_prefix        60   13.01    6.58    0.00   25010 
Hugo> [...]
Hugo> alpha:  TOTALS                 111222  747.01  569.89    0.00 1511298 ++++
Hugo> stable: TOTALS                  10908  781.22  489.97    0.00 1511318 +

I agree;  There seems to be something wrong in the above.  On the
other hand, I don't have this problems with my copy of 3.23.3.

I would suggest that you for a while remove the second part of the
index (message) until 3.23 reaches beta (hopefully within a couple of weeks).

Rematch of modified record in updatehugo18 Aug
  • Rematch of modified record in updatesinisa18 Aug
    • Re: Rematch of modified record in updateHugo van der Sanden18 Aug
      • Re: Rematch of modified record in updateMichael Widenius23 Aug
        • Re: Rematch of modified record in updateHugo van der Sanden23 Aug
        • Re: Rematch of modified record in updateHugo van der Sanden2 Sep
          • Re: Rematch of modified record in updateMichael Widenius2 Sep
            • Re: Rematch of modified record in updateHugo van der Sanden3 Sep
              • Re: Rematch of modified record in updateMichael Widenius7 Sep
  • Rematch of modified record in updateMichael Widenius23 Aug