Description:
show variables failed with Out of memory error:
mysql> show variables;
ERROR 1041 (HY000): Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
How to repeat:
1. Change the temptable memory limit to the minimal possible value, so we can easily repro by hand. (You can use large value, and repro with high concurrent "show variables"). And also disable using mmap if temptable limitation is reached.
SET GLOBAL temptable_max_ram = 2097152;
SET GLOBAL temptable_use_mmap = OFF;
2. Open 2 session manually, and run "show variables" in each of them. In my debug building, the 1st succeeded, the 2nd failed
session 1 > show variables;
session 2 >show variables;
ERROR 1041 (HY000): Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
Suggested fix:
In temptable::Handler::create()
```
122 Result ret = Result::OUT_OF_MEM;
123
124 try {
125 // clang-format off
126 DBUG_EXECUTE_IF(
127 "temptable_create_return_full",
128 ret = Result::RECORD_FILE_FULL;
129 throw std::bad_alloc();
130 );
131 // clang-format on
132
133 const auto insert_result = tls_tables.emplace(
134 std::piecewise_construct, std::make_tuple(table_name),
135 std::forward_as_tuple(mysql_table, all_columns_are_fixed_size));
136
137 ret = insert_result.second ? Result::OK : Result::TABLE_EXIST;
138 } catch (...) {
139 /* ret is already set above. */
140 }
```
The default ret code is set to `Result::OUT_OF_MEM`, and if exception happened for tls_tables.emplace, Result::OUT_OF_MEM will be returned as result.
In the upper level create_tmp_table_with_fallback()
```
2152 int error =
2153 table->file->create(share->table_name.str, table, &create_info, nullptr);
2154 if (error == HA_ERR_RECORD_FILE_FULL &&
2155 table->s->db_type() == temptable_hton) {
2156 table->file =
2157 get_new_handler(table->s, false, &table->s->mem_root, innodb_hton);
2158 error = table->file->create(share->table_name.str, table, &create_info,
2159 nullptr);
2160 }
2161
2162 if (error) {
2163 table->file->print_error(error, MYF(0)); /* purecov: inspected */
2164 table->db_stat = 0;
2165 DBUG_RETURN(true);
2166 } else {
2167 if (table->s->db_type() != temptable_hton) {
2168 table->in_use->inc_status_created_tmp_disk_tables();
2169 }
2170 DBUG_RETURN(false);
2171 }
```
if the return error is HA_ERR_RECORD_FILE_FULL, the on disk innodb tmp table will be tried. But HA_ERR_OUT_OF_MEM is returned from temptable::Handler::create().
Actually, HA_ERR_RECORD_FILE_FULL is never returned from temptable::Handler::create(), so the fallback logic will never be touched.
For temptable::Table::m_columns, the TempTable allocator is used to provide memory, and capped by temptable_use_mmap, as bellow stack:
```
#0 temptable::Allocator<temptable::Column>::mem_fetch (this=0x7f16e8023988, bytes=1048576) at /home/fungo/Projects/mysql-server-8/storage/temptable/include/temptable/allocator.h:663
#1 0x0000000006790a89 in temptable::Allocator<temptable::Column>::block_create (this=0x7f16e8023988, first_alloc_size=32) at /home/fungo/Projects/mysql-server-8/storage/temptable/include/temptable/allocator.h:902
#2 0x000000000678e4b7 in temptable::Allocator<temptable::Column>::allocate (this=0x7f16e8023988, n_elements=2) at /home/fungo/Projects/mysql-server-8/storage/temptable/include/temptable/allocator.h:530
#3 0x000000000678cd29 in std::allocator_traits<temptable::Allocator<temptable::Column> >::allocate (__a=..., __n=2) at /opt/rh/devtoolset-7/root/usr/include/c++/7/bits/alloc_traits.h:301
#4 0x000000000678b480 in std::_Vector_base<temptable::Column, temptable::Allocator<temptable::Column> >::_M_allocate (this=0x7f16e8023988, __n=2) at /opt/rh/devtoolset-7/root/usr/include/c++/7/bits/stl_vector.h:172
#5 0x0000000006789922 in std::vector<temptable::Column, temptable::Allocator<temptable::Column> >::_M_allocate_and_copy<std::move_iterator<temptable::Column*> > (this=0x7f16e8023988, __n=2, __first=..., __last=...)
at /opt/rh/devtoolset-7/root/usr/include/c++/7/bits/stl_vector.h:1260
#6 0x0000000006787d89 in std::vector<temptable::Column, temptable::Allocator<temptable::Column> >::reserve (this=0x7f16e8023988, __n=2) at /opt/rh/devtoolset-7/root/usr/include/c++/7/bits/vector.tcc:73
#7 0x0000000006784792 in temptable::Table::Table (this=0x7f16e80238e0, mysql_table=0x7f16e800a0f8, all_columns_are_fixed_size=false) at /home/fungo/Projects/mysql-server-8/storage/temptable/src/table.cc:82
#8 0x000000000677e92b in std::pair<std::string const, temptable::Table>::pair<char const*, 0ul, TABLE*&, bool&, 0ul, 1ul> (this=0x7f16e80238d8, __tuple1=..., __tuple2=...) at /opt/rh/devtoolset-7/root/usr/include/c++/7/tuple:1652
#9 0x000000000677e571 in std::pair<std::string const, temptable::Table>::pair<char const*, TABLE*&, bool&> (this=0x7f16e80238d8, __first=..., __second=...) at /opt/rh/devtoolset-7/root/usr/include/c++/7/tuple:1641
#10 0x000000000677e0e7 in __gnu_cxx::new_allocator<std::pair<std::string const, temptable::Table> >::construct<std::pair<std::string const, temptable::Table>, std::piecewise_construct_t const&, std::tuple<char const*>, std::tuple<TABLE*&, bool&> >(std::pair<std::string const, temptable::Table>*, std::piecewise_construct_t const&, std::tuple<char const*>&&, std::tuple<TABLE*&, bool&>&&) (this=0x7f179c072def, __p=0x7f16e80238d8, __args#0=..., __args#1=...,
...
__args#2=...) at /opt/rh/devtoolset-7/root/usr/include/c++/7/bits/hashtable.h:1641
#14 0x000000000677b73c in std::_Hashtable<std::string, std::pair<std::string const, temptable::Table>, std::allocator<std::pair<std::string const, temptable::Table> >, std::__detail::_Select1st, std::equal_to<std::string>, std::hash<std::string>, std::__detail::_Mod_range_hashing, std::__detail::_Default_ranged_hash, std::__detail::_Prime_rehash_policy, std::__detail::_Hashtable_traits<true, false, true> >::emplace<std::piecewise_construct_t const&, std::tuple<char const*>, std::tuple<TABLE*&, bool&> >(std::piecewise_construct_t const&, std::tuple<char const*>&&, std::tuple<TABLE*&, bool&>&&) (this=0x7f179c0766b0, __args#0=..., __args#1=..., __args#2=...)
at /opt/rh/devtoolset-7/root/usr/include/c++/7/bits/hashtable.h:736
#15 0x000000000677aa7a in std::unordered_map<std::string, temptable::Table, std::hash<std::string>, std::equal_to<std::string>, std::allocator<std::pair<std::string const, temptable::Table> > >::emplace<std::piecewise_construct_t const&, std::tuple<char const*>, std::tuple<TABLE*&, bool&> >(std::piecewise_construct_t const&, std::tuple<char const*>&&, std::tuple<TABLE*&, bool&>&&) (this=0x7f179c0766b0, __args#0=..., __args#1=..., __args#2=...)
at /opt/rh/devtoolset-7/root/usr/include/c++/7/bits/unordered_map.h:387
#16 0x000000000676e32b in temptable::Handler::create (this=0x7f16e801ed98, table_name=0x7f16e8021ae8 "/flash1/xiangluo.wb/Projects/mysql-server-8/mysql-test/var/tmp/mysqld.1/#sqlf517_b_2", mysql_table=0x7f16e800a0f8)
at /home/fungo/Projects/mysql-server-8/storage/temptable/src/handler.cc:135
#17 0x0000000003000f6b in create_tmp_table_with_fallback (table=0x7f16e800a0f8) at /home/fungo/Projects/mysql-server-8/sql/sql_tmp_table.cc:2153
#18 0x0000000003001b07 in instantiate_tmp_table (thd=0x7f16e8000c00, table=0x7f16e800a0f8) at /home/fungo/Projects/mysql-server-8/sql/sql_tmp_table.cc:2231
#19 0x0000000003a0330a in TABLE_LIST::create_materialized_table (this=0x7f16e802ec48, thd=0x7f16e8000c00) at /home/fungo/Projects/mysql-server-8/sql/sql_derived.cc:768
```
How to fix:
Change the default ret code of temptable::Handler::create() to Result::RECORD_FILE_FULL
```
diff --git a/storage/temptable/src/handler.cc b/storage/temptable/src/handler.cc
index e5898e0de69..9fa9e1fc675 100644
--- a/storage/temptable/src/handler.cc
+++ b/storage/temptable/src/handler.cc
@@ -119,7 +119,7 @@ int Handler::create(const char *table_name, TABLE *mysql_table,
}
}
- Result ret = Result::OUT_OF_MEM;
+ Result ret = Result::RECORD_FILE_FULL;
try {
// clang-format off
```
Description: show variables failed with Out of memory error: mysql> show variables; ERROR 1041 (HY000): Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space How to repeat: 1. Change the temptable memory limit to the minimal possible value, so we can easily repro by hand. (You can use large value, and repro with high concurrent "show variables"). And also disable using mmap if temptable limitation is reached. SET GLOBAL temptable_max_ram = 2097152; SET GLOBAL temptable_use_mmap = OFF; 2. Open 2 session manually, and run "show variables" in each of them. In my debug building, the 1st succeeded, the 2nd failed session 1 > show variables; session 2 >show variables; ERROR 1041 (HY000): Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space Suggested fix: In temptable::Handler::create() ``` 122 Result ret = Result::OUT_OF_MEM; 123 124 try { 125 // clang-format off 126 DBUG_EXECUTE_IF( 127 "temptable_create_return_full", 128 ret = Result::RECORD_FILE_FULL; 129 throw std::bad_alloc(); 130 ); 131 // clang-format on 132 133 const auto insert_result = tls_tables.emplace( 134 std::piecewise_construct, std::make_tuple(table_name), 135 std::forward_as_tuple(mysql_table, all_columns_are_fixed_size)); 136 137 ret = insert_result.second ? Result::OK : Result::TABLE_EXIST; 138 } catch (...) { 139 /* ret is already set above. */ 140 } ``` The default ret code is set to `Result::OUT_OF_MEM`, and if exception happened for tls_tables.emplace, Result::OUT_OF_MEM will be returned as result. In the upper level create_tmp_table_with_fallback() ``` 2152 int error = 2153 table->file->create(share->table_name.str, table, &create_info, nullptr); 2154 if (error == HA_ERR_RECORD_FILE_FULL && 2155 table->s->db_type() == temptable_hton) { 2156 table->file = 2157 get_new_handler(table->s, false, &table->s->mem_root, innodb_hton); 2158 error = table->file->create(share->table_name.str, table, &create_info, 2159 nullptr); 2160 } 2161 2162 if (error) { 2163 table->file->print_error(error, MYF(0)); /* purecov: inspected */ 2164 table->db_stat = 0; 2165 DBUG_RETURN(true); 2166 } else { 2167 if (table->s->db_type() != temptable_hton) { 2168 table->in_use->inc_status_created_tmp_disk_tables(); 2169 } 2170 DBUG_RETURN(false); 2171 } ``` if the return error is HA_ERR_RECORD_FILE_FULL, the on disk innodb tmp table will be tried. But HA_ERR_OUT_OF_MEM is returned from temptable::Handler::create(). Actually, HA_ERR_RECORD_FILE_FULL is never returned from temptable::Handler::create(), so the fallback logic will never be touched. For temptable::Table::m_columns, the TempTable allocator is used to provide memory, and capped by temptable_use_mmap, as bellow stack: ``` #0 temptable::Allocator<temptable::Column>::mem_fetch (this=0x7f16e8023988, bytes=1048576) at /home/fungo/Projects/mysql-server-8/storage/temptable/include/temptable/allocator.h:663 #1 0x0000000006790a89 in temptable::Allocator<temptable::Column>::block_create (this=0x7f16e8023988, first_alloc_size=32) at /home/fungo/Projects/mysql-server-8/storage/temptable/include/temptable/allocator.h:902 #2 0x000000000678e4b7 in temptable::Allocator<temptable::Column>::allocate (this=0x7f16e8023988, n_elements=2) at /home/fungo/Projects/mysql-server-8/storage/temptable/include/temptable/allocator.h:530 #3 0x000000000678cd29 in std::allocator_traits<temptable::Allocator<temptable::Column> >::allocate (__a=..., __n=2) at /opt/rh/devtoolset-7/root/usr/include/c++/7/bits/alloc_traits.h:301 #4 0x000000000678b480 in std::_Vector_base<temptable::Column, temptable::Allocator<temptable::Column> >::_M_allocate (this=0x7f16e8023988, __n=2) at /opt/rh/devtoolset-7/root/usr/include/c++/7/bits/stl_vector.h:172 #5 0x0000000006789922 in std::vector<temptable::Column, temptable::Allocator<temptable::Column> >::_M_allocate_and_copy<std::move_iterator<temptable::Column*> > (this=0x7f16e8023988, __n=2, __first=..., __last=...) at /opt/rh/devtoolset-7/root/usr/include/c++/7/bits/stl_vector.h:1260 #6 0x0000000006787d89 in std::vector<temptable::Column, temptable::Allocator<temptable::Column> >::reserve (this=0x7f16e8023988, __n=2) at /opt/rh/devtoolset-7/root/usr/include/c++/7/bits/vector.tcc:73 #7 0x0000000006784792 in temptable::Table::Table (this=0x7f16e80238e0, mysql_table=0x7f16e800a0f8, all_columns_are_fixed_size=false) at /home/fungo/Projects/mysql-server-8/storage/temptable/src/table.cc:82 #8 0x000000000677e92b in std::pair<std::string const, temptable::Table>::pair<char const*, 0ul, TABLE*&, bool&, 0ul, 1ul> (this=0x7f16e80238d8, __tuple1=..., __tuple2=...) at /opt/rh/devtoolset-7/root/usr/include/c++/7/tuple:1652 #9 0x000000000677e571 in std::pair<std::string const, temptable::Table>::pair<char const*, TABLE*&, bool&> (this=0x7f16e80238d8, __first=..., __second=...) at /opt/rh/devtoolset-7/root/usr/include/c++/7/tuple:1641 #10 0x000000000677e0e7 in __gnu_cxx::new_allocator<std::pair<std::string const, temptable::Table> >::construct<std::pair<std::string const, temptable::Table>, std::piecewise_construct_t const&, std::tuple<char const*>, std::tuple<TABLE*&, bool&> >(std::pair<std::string const, temptable::Table>*, std::piecewise_construct_t const&, std::tuple<char const*>&&, std::tuple<TABLE*&, bool&>&&) (this=0x7f179c072def, __p=0x7f16e80238d8, __args#0=..., __args#1=..., ... __args#2=...) at /opt/rh/devtoolset-7/root/usr/include/c++/7/bits/hashtable.h:1641 #14 0x000000000677b73c in std::_Hashtable<std::string, std::pair<std::string const, temptable::Table>, std::allocator<std::pair<std::string const, temptable::Table> >, std::__detail::_Select1st, std::equal_to<std::string>, std::hash<std::string>, std::__detail::_Mod_range_hashing, std::__detail::_Default_ranged_hash, std::__detail::_Prime_rehash_policy, std::__detail::_Hashtable_traits<true, false, true> >::emplace<std::piecewise_construct_t const&, std::tuple<char const*>, std::tuple<TABLE*&, bool&> >(std::piecewise_construct_t const&, std::tuple<char const*>&&, std::tuple<TABLE*&, bool&>&&) (this=0x7f179c0766b0, __args#0=..., __args#1=..., __args#2=...) at /opt/rh/devtoolset-7/root/usr/include/c++/7/bits/hashtable.h:736 #15 0x000000000677aa7a in std::unordered_map<std::string, temptable::Table, std::hash<std::string>, std::equal_to<std::string>, std::allocator<std::pair<std::string const, temptable::Table> > >::emplace<std::piecewise_construct_t const&, std::tuple<char const*>, std::tuple<TABLE*&, bool&> >(std::piecewise_construct_t const&, std::tuple<char const*>&&, std::tuple<TABLE*&, bool&>&&) (this=0x7f179c0766b0, __args#0=..., __args#1=..., __args#2=...) at /opt/rh/devtoolset-7/root/usr/include/c++/7/bits/unordered_map.h:387 #16 0x000000000676e32b in temptable::Handler::create (this=0x7f16e801ed98, table_name=0x7f16e8021ae8 "/flash1/xiangluo.wb/Projects/mysql-server-8/mysql-test/var/tmp/mysqld.1/#sqlf517_b_2", mysql_table=0x7f16e800a0f8) at /home/fungo/Projects/mysql-server-8/storage/temptable/src/handler.cc:135 #17 0x0000000003000f6b in create_tmp_table_with_fallback (table=0x7f16e800a0f8) at /home/fungo/Projects/mysql-server-8/sql/sql_tmp_table.cc:2153 #18 0x0000000003001b07 in instantiate_tmp_table (thd=0x7f16e8000c00, table=0x7f16e800a0f8) at /home/fungo/Projects/mysql-server-8/sql/sql_tmp_table.cc:2231 #19 0x0000000003a0330a in TABLE_LIST::create_materialized_table (this=0x7f16e802ec48, thd=0x7f16e8000c00) at /home/fungo/Projects/mysql-server-8/sql/sql_derived.cc:768 ``` How to fix: Change the default ret code of temptable::Handler::create() to Result::RECORD_FILE_FULL ``` diff --git a/storage/temptable/src/handler.cc b/storage/temptable/src/handler.cc index e5898e0de69..9fa9e1fc675 100644 --- a/storage/temptable/src/handler.cc +++ b/storage/temptable/src/handler.cc @@ -119,7 +119,7 @@ int Handler::create(const char *table_name, TABLE *mysql_table, } } - Result ret = Result::OUT_OF_MEM; + Result ret = Result::RECORD_FILE_FULL; try { // clang-format off ```