MySQL Bugs: #29130: TRUNCATE misimplemented with row-based logging

Bug #29130	TRUNCATE misimplemented with row-based logging
Submitted:	15 Jun 2007 9:13	Modified:	23 Jun 2007 10:28
Reporter:	Mats Kindahl
Status:	Closed
Category:	Server: General	Severity:	S2 (Serious)
Version:	5.1	OS:	Any
Assigned to:	Mats Kindahl	Target Version:

Description:
The logic for using delete_all_rows() when issuing a TRUNCATE statement is wrong. The
code caused delete_all_rows() to not be used for the TRUNCATE statement when row-based
logging was in effect.

With TRUNCATE, the binlogging format in effect is irrelevant and the logic should reflect
that. The TRUNCATE statement is always logged as a statement.

How to repeat:
Read the code in sql_delete.cc

Suggested fix:
Change the logic in sql_delete.cc to reflect that delete_all_rows() can be used when all
other conditions are in effect and disregard the binlogging format in effect.

The logic should be: Use delete_all_rows() if and only if:
- We allow new functions (not using option --skip-new), and are
  not in safe mode (not using option --safe-mode)
- There is no limit clause
- The condition is constant
- If there is a condition, then it produces a non-zero value
- If the current command is DELETE FROM with no where clause
  (i.e., not TRUNCATE) then:
  - We should not be binlogging this statement row-based, and
  - there should be no delete triggers associated with the table.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/28860

ChangeSet@1.2556, 2007-06-15 13:50:56+02:00, mats@kindahl-laptop.dnsalias.net +1 -0
  BUG#29130 (The logic for using delete_all_rows() is wrong):
  Correcting the logic for deciding when to use delete_all_rows() so that
  the behavior of TRUNCATE to not be dependent on binary logging
  format in effect.
  
  A TRUNCATE statement is always logged as a statement, so in this case,
  delete_all_rows() can always be used provided the other logic is correct.
  If a DELETE FROM without a WHERE clause is used, and row-based binlogging
  is used, the rows has to be deleted from the table on a per-row basis.

Pushed into 5.1.20-beta

Thank you for your bug report. This issue has been committed to our source repository of
that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available
version, including the bug fix. More information about accessing the source trees is
available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.1.20 changelog as:

The <literal>TRUNCATE</literal> statement was handled
          differently by the server when row-based logging was in
          effect, even though the binlogging format in effect does not
          effect the fact that <literal>TRUNCATE</literal> is always
          logged as a statement.

Updated synopsis.