Implemented Method for truncating tables

[digitalpoint]

It would be handy (maybe for XF 2.0?) if the database handler had a method to empty tables. Some storage engines do different things internally when TRUNCATE TABLE is called. Most notably:

TRUNCATE TABLE is treated for purposes of binary logging and replication as DROP TABLE followed by CREATE TABLE—that is, as DDL rather than DML. This is due to the fact that, when using InnoDB and other transactional storage engines where the transaction isolation level does not permit statement-based logging (READ COMMITTED or READ UNCOMMITTED), the statement was not logged and replicated when using STATEMENT or MIXED logging mode.

So if you are doing something like a backup while a TRUNCATE statement happens, the statement hangs until the backup is done because drop table/create table isn't allowed during a hot backup. If the backup takes too long, the truncate statement ends up just failing after whatever timeout you have for SQL statements in your config.

Long story short is that a "DELETE FROM [table_name]" *is* allowed during hot backups since it doesn't internally drop the table and recreate it.

So it would be nice if we had a central "empty table" method of some sort so that we can override it's function in one place vs. extending a bunch of models with truncate SQL queries in them. A bonus step further would be to have a standard toggle option (similar to the one for using "INSERT DELAYED" [which btw is a deprecated MySQL function and should probably just be removed as an option]) that lets you toggle between "TRUNCATE TABLE xxxx" and "DELETE FROM xxxx" as the underlying statement.

 

[Chris D]

I think we missed this one when marking suggestions implemented. This was implemented by XF 2.0.0.