commit 175e6058fe25ac90bc797bea07b4d703529397f7
Author: Shaohua Wang
Date:   Tue Jul 11 16:52:05 2023 -0600

    bugfix#110557 create fts index fails when sort file cache is disabled
    
    Problem:
    ========
    when innodb_disable_sort_file_cache=ON, create fts index fails.
    
    Query 'ALTER TABLE test ADD FULLTEXT INDEX IDX_NAME(name) WITH PARSER
    ngram' failed.
    ERROR 1878 (HY000): Temporary file write failure.
    
    Analysis:
    =========
    Disables the operating system file system cache for merge-sort temporary
    files. The effect is to open such files with the equivalent of O_DIRECT.
    
    Under Linux 2.4, transfer sizes, O_DIRECT alignment of the user buffer and
    the file offset must all be multiples of the logical block size  of the file
    system.
    
    The root cause is that the function Builder::append() doesn't do the
    alignment, which is used only by fts index build.
    
    Solution:
    =========
    1. do the alignment in Builder::append().
    2. do the alignment in Context::scan_buffer_size() to avoid heap buffer
    overflow.
    Suppose the following scenario:
    First, io buffer size is 54613 bytes, initialized by Scan_buffer_size.first,
    which is calculated by scan_buffer_size().
    
    Second, io buffer is allocated and aligned with IO_BLOCK_SIZE(4k), so the
    actual allocated size is 55637 bytes. Note here the alignment is just for io
    buffer address, not for io buffer size.
    
    Third, io buffer size is aligned with IO_BLOCK_SIZE before pwrite() for
    DIRECT IO, so the aligned write length is 57344 bytes.
    
    The heap buffer overflow happens.

diff --git a/mysql-test/suite/innodb_fts/r/create_index_fail_with_disable_sort_file_cache.result b/mysql-test/suite/innodb_fts/r/create_index_fail_with_disable_sort_file_cache.result
new file mode 100644
index 00000000000..b8f796eabaa
--- /dev/null
+++ b/mysql-test/suite/innodb_fts/r/create_index_fail_with_disable_sort_file_cache.result
@@ -0,0 +1,32 @@
+CREATE TABLE test (
+FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
+title varchar(100),
+content varchar(255),
+comment varchar(50)
+) ENGINE = InnoDB;
+INSERT INTO test (title, content, comment) VALUE
+("mysql", "the world's most popular open source database", 'mysql');
+SELECT * FROM test;
+FTS_DOC_ID	title	content	comment
+1	mysql	the world's most popular open source database	mysql
+ALTER TABLE test ADD INDEX idx_title(title);
+ALTER TABLE test ADD FULLTEXT INDEX idx_content(content) WITH PARSER ngram;
+ALTER TABLE test ADD FULLTEXT INDEX idx_comment(comment);
+SELECT * FROM test WHERE title = 'mysql';
+FTS_DOC_ID	title	content	comment
+1	mysql	the world's most popular open source database	mysql
+SELECT * FROM test WHERE MATCH(content) AGAINST('mysql');
+FTS_DOC_ID	title	content	comment
+SELECT * FROM test WHERE MATCH(comment) AGAINST('mysql');
+FTS_DOC_ID	title	content	comment
+1	mysql	the world's most popular open source database	mysql
+ALTER TABLE test ENGINE=InnoDB;
+SELECT * FROM test WHERE title = 'mysql';
+FTS_DOC_ID	title	content	comment
+1	mysql	the world's most popular open source database	mysql
+SELECT * FROM test WHERE MATCH(content) AGAINST('mysql');
+FTS_DOC_ID	title	content	comment
+SELECT * FROM test WHERE MATCH(comment) AGAINST('mysql');
+FTS_DOC_ID	title	content	comment
+1	mysql	the world's most popular open source database	mysql
+DROP TABLE test;
diff --git a/mysql-test/suite/innodb_fts/t/create_index_fail_with_disable_sort_file_cache-master.opt b/mysql-test/suite/innodb_fts/t/create_index_fail_with_disable_sort_file_cache-master.opt
new file mode 100644
index 00000000000..ae39ab0bff1
--- /dev/null
+++ b/mysql-test/suite/innodb_fts/t/create_index_fail_with_disable_sort_file_cache-master.opt
@@ -0,0 +1 @@
+--innodb_disable_sort_file_cache=ON
diff --git a/mysql-test/suite/innodb_fts/t/create_index_fail_with_disable_sort_file_cache.test b/mysql-test/suite/innodb_fts/t/create_index_fail_with_disable_sort_file_cache.test
new file mode 100644
index 00000000000..712be3d5bac
--- /dev/null
+++ b/mysql-test/suite/innodb_fts/t/create_index_fail_with_disable_sort_file_cache.test
@@ -0,0 +1,32 @@
+#
+# aone#48976130 create fts index fails when sort file cache is disabled.
+#
+
+CREATE TABLE test (
+  FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
+  title varchar(100),
+  content varchar(255),
+  comment varchar(50)
+) ENGINE = InnoDB;
+
+
+INSERT INTO test (title, content, comment) VALUE
+ ("mysql", "the world's most popular open source database", 'mysql');
+
+SELECT * FROM test;
+
+ALTER TABLE test ADD INDEX idx_title(title);
+ALTER TABLE test ADD FULLTEXT INDEX idx_content(content) WITH PARSER ngram;
+ALTER TABLE test ADD FULLTEXT INDEX idx_comment(comment);
+
+SELECT * FROM test WHERE title = 'mysql';
+SELECT * FROM test WHERE MATCH(content) AGAINST('mysql');
+SELECT * FROM test WHERE MATCH(comment) AGAINST('mysql');
+
+ALTER TABLE test ENGINE=InnoDB;
+
+SELECT * FROM test WHERE title = 'mysql';
+SELECT * FROM test WHERE MATCH(content) AGAINST('mysql');
+SELECT * FROM test WHERE MATCH(comment) AGAINST('mysql');
+
+DROP TABLE test;
diff --git a/storage/innobase/ddl/ddl0builder.cc b/storage/innobase/ddl/ddl0builder.cc
index 27d59382c4b..d3d67ca2e5f 100644
--- a/storage/innobase/ddl/ddl0builder.cc
+++ b/storage/innobase/ddl/ddl0builder.cc
@@ -1169,14 +1169,23 @@ bool Builder::create_file(ddl::file_t &file) noexcept {
 }
 
 dberr_t Builder::append(ddl::file_t &file, IO_buffer io_buffer) noexcept {
-  auto err = ddl::pwrite(file.m_file.get(), io_buffer.first, io_buffer.second,
-                         file.m_size);
+  ut_a(!(file.m_size % IO_BLOCK_SIZE));
+
+  os_offset_t n = io_buffer.second;
+  if (n == 0) {
+    n = ut_uint64_align_down(io_buffer.second, IO_BLOCK_SIZE);
+  } else {
+    n = ut_uint64_align_up(io_buffer.second, IO_BLOCK_SIZE);
+  }
+  ut_a(n >= IO_BLOCK_SIZE);
+
+  auto err = ddl::pwrite(file.m_file.get(), io_buffer.first, n, file.m_size);
 
   if (err != DB_SUCCESS) {
     set_error(DB_TEMP_FILE_WRITE_FAIL);
     return get_error();
   } else {
-    file.m_size += io_buffer.second;
+    file.m_size += n;
     return err;
   }
 }
diff --git a/storage/innobase/ddl/ddl0ctx.cc b/storage/innobase/ddl/ddl0ctx.cc
index b4e352c026f..71fba6e3d26 100644
--- a/storage/innobase/ddl/ddl0ctx.cc
+++ b/storage/innobase/ddl/ddl0ctx.cc
@@ -194,6 +194,11 @@ Context::Scan_buffer_size Context::scan_buffer_size(
     size.first -= size.second;
   }
 
+  /* Buffer size should be aligned for DIRECT IO when
+  innodb_disable_sort_file_cache=ON and innodb_flush_method=O_DIRECT. */
+  size.first = ut_uint64_align_up(size.first, IO_BLOCK_SIZE);
+  ut_a(size.second % IO_BLOCK_SIZE == 0);
+
   return size;
 }