Mysql – disable the redo log for a DELETE query or a whole transaction

innodbMySQLmysql-5.6transaction-log

I'm working on an existing database, whose structure I can't change. I need to clear (empty) several InnoDB tables (that I can't TRUNCATE due to references in other tables' FK constraints), but I have no interest in any recovery – i.e. if a crash occurs during those operations, I'll clear those tables again on the first occasion.

Can I tell MySQL to not create the redo log about those queries?

I am concerned about speed, as the first thing that can be noted is the time the operation takes. But, space too, I guess. Tables can store 20k-1M rows depending on the specific installation (same schema structure, different usages). I am operating on a bunch of table whose data are related (i.e. products, products_images, products_votes, …), so FK are not an issue because all data will be deleted consistently without the need of any additional check.

Best Answer

If I understand correctly, the problem is:

We have one or more tables (lets call them "parents") that we want to empty, either with DELETE or TRUNCATE. There are several other tables ("children") that have FOREING KEY constraints which reference the first table(s) or some other "child" and we want to empty those tables as well.

If we DELETE, the operation will succeed, as all the FKs have been defined with ON DELETE CASCADE actions. But it will be a very slow operation.
If we TRUNCATE, the operation fails due to the FKs. It's not allowed to TRUNCATE a table that other tables reference it.

To solve this conundrum, I don't think that messing with the redo log is advisable (if at all possible).

I suggest the more simple:

ALTER the children tables and DROP the offending FOREIGN KEY constraints.
TRUNCATE all the tables.
ALTER the children tables and add the constraints again.

If some reason altering the tables even temporarily is forbidden, you should at least follow MySQL's guidelines about optimizing the redo logs: Optimizing InnoDB Redo Logging

Related Solutions

SQL Server – View Delete Statements in the Transaction Log

you wont find the exact scripts that were executed on sql.(in the transaction log)

A transaction log is a file that contains information regarding every change that has been made to the database. This includes data modifications (transactions), database modifications, and backup/restore events.

The primary purpose the transaction log is to provide a method to be able to restore a database to a point-in-time when necessary. This can include rolling back transactions to a certain time, or to roll forward transactions from a full backup restoration.

to know more about Transaction log http://www.sqlservercentral.com/articles/Design+and+Theory/63350/

here is a script to show you recent ran delete queries

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
where dest.TEXT like '%Delete%from%'
ORDER BY deqs.last_execution_time DESC

MySQL Logs – Difference Between Transaction Log and Redo Log

REDO log is Oracle terminology, transaction log is InnoDB terminology. Now that all are Oracle engineers, people use both to refer to the same thing in MySQL.

The transaction log is, by default- it can be changed- the two files located in $DATADIR called ib_logfile0 and ib_logfile1. It serves the same functions as the REDO log in other databases- storing writes in a safe way and recovering in the case of a crash, although there are some details in implementation that differ in functionality from other RDMS. It is the main component for InnoDB to be a transactional engine.

Do not confuse the transaction log with the binary logs in MySQL. The binlog, by default, is on the $DATADIR and is *hostname*-bin.index and several *hostname*-bin.00001, etc. It is particular confusing for people coming from other databases, because it is used for other things that other databases use the REDO log for: replication and point in time recovery. The main difference is that the transaction log is InnoDB-only, the binary log is (mostly) transaction-independent, as it is for all storage engines, transactional or not. MyISAM will write (if enabled) to the binary log. InnoDB will write to the transaction log and the binary log.

More info on the manual: REDO log, binary log.

Best Answer

Related Solutions

SQL Server – View Delete Statements in the Transaction Log

MySQL Logs – Difference Between Transaction Log and Redo Log

Related Question