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

Bug #29130	TRUNCATE misimplemented with row-based logging
Submitted:	15 Jun 2007 7:13	Modified:	23 Jun 2007 8:28
Reporter:	Mats Kindahl	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.1	OS:	Any
Assigned to:	Mats Kindahl	CPU Architecture:	Any

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.