MDEV-24348 InnoDB shutdown hang with innodb_flush_sync=0

This hang was caused by MDEV-23855, and we failed to fix it in
MDEV-24109 (commit 4cbfdeca840098b9ed0d8147d43288c36743a328).

When buf_flush_ahead() is invoked soon before server shutdown
and the non-default setting innodb_flush_sync=OFF is in effect
and the buffer pool contains dirty pages of temporary tables,
the page cleaner thread may remain in an infinite loop
without completing its work, thus causing the shutdown to hang.

buf_flush_page_cleaner(): If the buffer pool contains no
unmodified persistent pages, ensure that buf_flush_sync_lsn= 0
will be assigned, so that shutdown will proceed.

The test case is not deterministic. On my system, it reproduced
the hang with 95% probability when running multiple instances
of the test in parallel, and 4% when running single-threaded.

Thanks to Eugene Kosov for debugging and testing this.

4 files changed, 23 insertions, 0 deletions

diff --git a/mysql-test/suite/innodb/r/autoinc_debug.result b/mysql-test/suite/innodb/r/autoinc_debug.result
index 5856c75f784..e82695ede43 100644
--- a/mysql-test/suite/innodb/r/autoinc_debug.result
+++ b/mysql-test/suite/innodb/r/autoinc_debug.result
@@ -1,6 +1,12 @@
 CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY)ENGINE=INNODB;
 # SETTING auto_increment_increment IN CONNECTION DEFAULT
 SET AUTO_INCREMENT_INCREMENT = 1;
+# MDEV-24348 InnoDB shutdown hang with innodb_flush_sync=0
+SET GLOBAL innodb_flush_sync=OFF;
+# For the server to hang, we must have pages for temporary tables
+# (and this is only effective as long as MDEV-12227 is not fixed).
+CREATE TEMPORARY TABLE t (id SERIAL) ENGINE=InnoDB;
+SET debug_dbug= '+d,ib_log_flush_ahead';
 INSERT INTO t1 VALUES(NULL);
 SELECT * FROM t1;
 id
diff --git a/mysql-test/suite/innodb/t/autoinc_debug.test b/mysql-test/suite/innodb/t/autoinc_debug.test
index 2e662565490..2ec7db33e43 100644
--- a/mysql-test/suite/innodb/t/autoinc_debug.test
+++ b/mysql-test/suite/innodb/t/autoinc_debug.test
@@ -8,6 +8,14 @@ CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY)ENGINE=INNODB;
 
 --echo # SETTING auto_increment_increment IN CONNECTION DEFAULT
 SET AUTO_INCREMENT_INCREMENT = 1;
+
+--echo # MDEV-24348 InnoDB shutdown hang with innodb_flush_sync=0
+SET GLOBAL innodb_flush_sync=OFF;
+--echo # For the server to hang, we must have pages for temporary tables
+--echo # (and this is only effective as long as MDEV-12227 is not fixed).
+CREATE TEMPORARY TABLE t (id SERIAL) ENGINE=InnoDB;
+SET debug_dbug= '+d,ib_log_flush_ahead';
+
 INSERT INTO t1 VALUES(NULL);
 SELECT * FROM t1;
 SHOW CREATE TABLE t1;
diff --git a/storage/innobase/buf/buf0flu.cc b/storage/innobase/buf/buf0flu.cc
index d1a55bae456..bdb1f3404a1 100644
--- a/storage/innobase/buf/buf0flu.cc
+++ b/storage/innobase/buf/buf0flu.cc
@@ -2104,6 +2104,7 @@ furious_flush:
 
     if (!dirty_blocks)
     {
+unemployed2:
       if (UNIV_UNLIKELY(lsn_limit != 0))
       {
         buf_flush_sync_lsn= 0;
@@ -2126,6 +2127,13 @@ unemployed:
 
     const lsn_t oldest_lsn= buf_pool.get_oldest_modification(0);
 
+#if 0 /* MDEV-12227 FIXME: enable this */
+    ut_ad(oldest_lsn); /* dirty_blocks implies this */
+#else
+    if (!oldest_lsn)
+      goto unemployed2;
+#endif
+
     if (UNIV_UNLIKELY(lsn_limit != 0) && oldest_lsn >= lsn_limit)
       buf_flush_sync_lsn= 0;
 
diff --git a/storage/innobase/mtr/mtr0mtr.cc b/storage/innobase/mtr/mtr0mtr.cc
index 4c22f05c4ba..60c91364b15 100644
--- a/storage/innobase/mtr/mtr0mtr.cc
+++ b/storage/innobase/mtr/mtr0mtr.cc
@@ -889,6 +889,7 @@ inline std::pair<lsn_t,bool> mtr_t::finish_write(ulint len)
 	m_log.for_each_block(write_log);
 	m_commit_lsn = log_sys.get_lsn();
 	bool flush = log_close(m_commit_lsn);
+	DBUG_EXECUTE_IF("ib_log_flush_ahead", flush=true;);
 
 	return std::make_pair(start_lsn, flush);
 }