MDEV-26007 Rollback unnecessarily initiates redo log write

trx_t::commit_in_memory(): Do not initiate a redo log write if
the transaction has no visible effect. If anything for this
transaction had been made durable, crash recovery will roll back
the transaction just fine even if the end of ROLLBACK is not
durably written.

Rollbacks of transactions that are associated with XA identifiers
(possibly internally via the binlog) will always be persisted.
The test rpl.rpl_gtid_crash covers this.

7 files changed, 17 insertions, 13 deletions

diff --git a/mysql-test/suite/innodb/r/read_only_recover_committed.result b/mysql-test/suite/innodb/r/read_only_recover_committed.result
index e7895ce6d46..2d41ab2157d 100644
--- a/mysql-test/suite/innodb/r/read_only_recover_committed.result
+++ b/mysql-test/suite/innodb/r/read_only_recover_committed.result
@@ -19,7 +19,8 @@ SET DEBUG_SYNC='now WAIT_FOR committed';
 SET GLOBAL innodb_flush_log_at_trx_commit=1;
 BEGIN;
 INSERT INTO t VALUES(-10000);
-ROLLBACK;
+DELETE FROM t WHERE a=-10000;
+COMMIT;
 # restart: --innodb-force-recovery=3
 disconnect con1;
 disconnect con2;
diff --git a/mysql-test/suite/innodb/r/read_only_recovery.result b/mysql-test/suite/innodb/r/read_only_recovery.result
index e83bf66432e..2db12863246 100644
--- a/mysql-test/suite/innodb/r/read_only_recovery.result
+++ b/mysql-test/suite/innodb/r/read_only_recovery.result
@@ -13,7 +13,8 @@ FLUSH TABLES;
 SET GLOBAL innodb_flush_log_at_trx_commit=1;
 BEGIN;
 INSERT INTO t VALUES(0);
-ROLLBACK;
+DELETE FROM t WHERE a=0;
+COMMIT;
 # restart: --innodb-force-recovery=3
 disconnect con1;
 SELECT * FROM t;
@@ -40,4 +41,3 @@ SELECT * FROM t;
 a
 3
 DROP TABLE t;
-FOUND 1 /Rolled back recovered transaction [^0]/ in mysqld.1.err
diff --git a/mysql-test/suite/innodb/t/read_only_recover_committed.test b/mysql-test/suite/innodb/t/read_only_recover_committed.test
index 9ad09bb9b3a..236d37897e2 100644
--- a/mysql-test/suite/innodb/t/read_only_recover_committed.test
+++ b/mysql-test/suite/innodb/t/read_only_recover_committed.test
@@ -45,7 +45,8 @@ SET DEBUG_SYNC='now WAIT_FOR committed';
 SET GLOBAL innodb_flush_log_at_trx_commit=1;
 BEGIN;
 INSERT INTO t VALUES(-10000);
-ROLLBACK;
+DELETE FROM t WHERE a=-10000;
+COMMIT;
 --let $restart_parameters= --innodb-force-recovery=3
 --let $shutdown_timeout= 0
 --source include/restart_mysqld.inc
diff --git a/mysql-test/suite/innodb/t/read_only_recovery.test b/mysql-test/suite/innodb/t/read_only_recovery.test
index 7da012efb74..30876c2fbcf 100644
--- a/mysql-test/suite/innodb/t/read_only_recovery.test
+++ b/mysql-test/suite/innodb/t/read_only_recovery.test
@@ -19,7 +19,8 @@ FLUSH TABLES;
 SET GLOBAL innodb_flush_log_at_trx_commit=1;
 BEGIN;
 INSERT INTO t VALUES(0);
-ROLLBACK;
+DELETE FROM t WHERE a=0;
+COMMIT;
 --let $restart_parameters= --innodb-force-recovery=3
 --let $shutdown_timeout= 0
 --source include/restart_mysqld.inc
@@ -41,6 +42,3 @@ SELECT * FROM t;
 --source include/restart_mysqld.inc
 SELECT * FROM t;
 DROP TABLE t;
-let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err;
---let SEARCH_PATTERN= Rolled back recovered transaction [^0]
---source include/search_pattern_in_file.inc
diff --git a/mysql-test/suite/innodb_fts/r/crash_recovery.result b/mysql-test/suite/innodb_fts/r/crash_recovery.result
index 74287f509b7..e8305cbb2f1 100644
--- a/mysql-test/suite/innodb_fts/r/crash_recovery.result
+++ b/mysql-test/suite/innodb_fts/r/crash_recovery.result
@@ -68,9 +68,11 @@ connection default;
 # Make durable the AUTO_INCREMENT in the above incomplete transaction.
 connect  flush_redo_log,localhost,root,,;
 SET GLOBAL innodb_flush_log_at_trx_commit=1;
+SET SQL_MODE = NO_AUTO_VALUE_ON_ZERO;
 BEGIN;
-DELETE FROM articles LIMIT 1;
-ROLLBACK;
+UPDATE articles SET id=0 WHERE id=1;
+UPDATE articles SET id=1 WHERE id=0;
+COMMIT;
 disconnect flush_redo_log;
 connection default;
 # restart
diff --git a/mysql-test/suite/innodb_fts/t/crash_recovery.test b/mysql-test/suite/innodb_fts/t/crash_recovery.test
index 229f5affe87..702aaefad83 100644
--- a/mysql-test/suite/innodb_fts/t/crash_recovery.test
+++ b/mysql-test/suite/innodb_fts/t/crash_recovery.test
@@ -143,9 +143,11 @@ connection default;
 --echo # Make durable the AUTO_INCREMENT in the above incomplete transaction.
 --connect (flush_redo_log,localhost,root,,)
 SET GLOBAL innodb_flush_log_at_trx_commit=1;
+SET SQL_MODE = NO_AUTO_VALUE_ON_ZERO;
 BEGIN;
-DELETE FROM articles LIMIT 1;
-ROLLBACK;
+UPDATE articles SET id=0 WHERE id=1;
+UPDATE articles SET id=1 WHERE id=0;
+COMMIT;
 --disconnect flush_redo_log
 --connection default
 
diff --git a/storage/innobase/trx/trx0trx.cc b/storage/innobase/trx/trx0trx.cc
index 0f735faacfa..dc7a719d5b0 100644
--- a/storage/innobase/trx/trx0trx.cc
+++ b/storage/innobase/trx/trx0trx.cc
@@ -1347,7 +1347,7 @@ inline void trx_t::commit_in_memory(const mtr_t *mtr)
     serialize all commits and prevent a group of transactions from
     gathering. */
 
-    commit_lsn= mtr->commit_lsn();
+    commit_lsn= undo_no || !xid->is_null() ? mtr->commit_lsn() : 0;
     if (!commit_lsn)
       /* Nothing to be done. */;
     else if (flush_log_later)