Investigation of MySQL Temporary Tablespace Limits with JDBC useCursorFetch and Alternative Solutions

Background : Using JDBC with the connection parameter useCursorFetch=true stores query result sets in MySQL's temporary tablespace (ibtmp1). In the test environment the ibtmp1 file grew beyond 90 GB, exhausting disk space. To limit its size the configuration innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:2G was applied.

Problem Description : After limiting the temporary tablespace, queries that exceed the limit cause the ibtmp1 file to reach the maximum size (2 GB) and the client thread remains in a SLEEP state without reporting an error, making debugging difficult.

Test Environment :

MySQL 5.7.16

Java 1.8u162

JDBC driver 5.1.36

OS: Red Hat 6.4

Test 1 – Manual simulation of exceeding the temporary table limit : Set ibtmp1:12M:autoextend:max:30M and executed a GROUP BY query on a 5‑million‑row table. The query failed with ERROR 1114 (HY000): The table '/tmp/#sql_60f1_0' is full.

Test 2 – Driver settings inspection : Checked MySQL general log for special driver settings; none were found.

Test 3 – JDBC connection with and without useCursorFetch=true :

With useCursorFetch=true, the same query does not raise an error; the server reports the temporary‑table overflow but keeps the connection alive, causing the client to wait until timeout.

Without the parameter, the query raises the error as expected.

Conclusion :

Normally MySQL reports an error when the temporary table reaches the ibtmp1 limit.

When useCursorFetch=true is enabled, MySQL does not propagate the error, leading to silent hangs.

Solution :

Recognize that useCursorFetch=true is intended to prevent large result sets from exhausting JVM memory, but it also causes temporary tables to be created and can overflow the shared temporary tablespace.

Instead of segment‑fetch, switch to a streaming read approach (see attached SimpleExample.java) so that errors are reported and memory usage stays low.

Limit ibtmp1 size via innodb_temp_data_file_path and restart MySQL when necessary.

Memory Usage Comparison :

Normal read: ~100 MB after execution.

Segment read (useCursorFetch): ~60 MB.

Stream read: ~60 MB.

Additional Knowledge – MySQL Shared Temporary Tablespace :

MySQL 5.7 separates the temporary tablespace from the shared ibdata file. The parameter innodb_temp_data_file_path controls its size and auto‑extension behavior. When the limit is reached, queries return an error. The temporary tablespace stores non‑compressed InnoDB temporary tables, related objects, and rollback segments, while tmpdir stores compressed temporary tables.

Example query to inspect the temporary tablespace:

SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE AS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME = 'innodb_temporary'\G

Creating compressed vs. uncompressed temporary tables can be tested with:

CREATE TEMPORARY TABLE compress_table (id int, name char(255)) ROW_FORMAT=COMPRESSED;

CREATE TEMPORARY TABLE uncompress_table (id int, name char(255));

The attached Java example ( SimpleExample.java) demonstrates how to set Statement.setFetchSize(Integer.MIN_VALUE) to enable streaming and handle errors correctly.

Attachment – SimpleExample.java :

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.atomic.AtomicLong;

public class SimpleExample {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Properties props = new Properties();
        props.setProperty("user", "root");
        props.setProperty("password", "root");
        SimpleExample engine = new SimpleExample();
        engine.execute(props, "jdbc:mysql://10.186.24.31:3336/hucq?useSSL=false&useCursorFetch=true");
    }

    final AtomicLong tmAl = new AtomicLong();
    final String tableName = "test";

    public void execute(Properties props, String url) {
        CountDownLatch cdl = new CountDownLatch(1);
        long start = System.currentTimeMillis();
        TestThread insertThread = new TestThread(props, cdl, url);
        Thread t = new Thread(insertThread);
        t.start();
        System.out.println("Test start");
        try {
            cdl.await();
            long end = System.currentTimeMillis();
            System.out.println("Test end,total cost:" + (end - start) + "ms");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

class TestThread implements Runnable {
    Properties props;
    private CountDownLatch countDownLatch;
    String url;
    public TestThread(Properties props, CountDownLatch cdl, String url) {
        this.props = props;
        this.countDownLatch = cdl;
        this.url = url;
    }
    public void run() {
        Connection connection = null;
        Statement st = null;
        long start = System.currentTimeMillis();
        try {
            connection = DriverManager.getConnection(url, props);
            connection.setAutoCommit(false);
            st = connection.createStatement();
            // st.setFetchSize(500);
            st.setFetchSize(Integer.MIN_VALUE); // enable streaming
            st.executeQuery("select sum(k) from sbtest1 group by k");
            ResultSet rstmp = st.getResultSet();
            while (rstmp.next()) {
                // process rows
            }
        } catch (Exception e) {
            System.out.println(System.currentTimeMillis() - start);
            e.printStackTrace();
        } finally {
            try { if (st != null) st.close(); } catch (SQLException e1) { e1.printStackTrace(); }
            try { if (connection != null) connection.close(); } catch (SQLException e1) { e1.printStackTrace(); }
            this.countDownLatch.countDown();
        }
    }
}