commit e9bbffa8827cd595bd412b606a7affc5f0648956
Author: Shaohua Wang
Date:   Thu Aug 10 10:26:07 2023 +0800

    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:
    =========
    Just do the alignment in Builder::append().

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 e6d51e2af74..84407e68f90 100644
--- a/storage/innobase/ddl/ddl0builder.cc
+++ b/storage/innobase/ddl/ddl0builder.cc
@@ -1164,14 +1164,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;
   }
 }