Investigation of MySQL Shared Temporary Tablespace Limits and JDBC useCursorFetch Behavior

Background : The application sets useCursorFetch=true for JDBC connections, causing query result sets to be stored in MySQL's temporary tablespace (ibtmp1). Without limits, ibtmp1 grew beyond 90 GB, exhausting disk space.

Problem Description : After limiting the temporary tablespace size (e.g., innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:2G), queries that exceed the limit cause the connection thread to remain in a sleep state without reporting an error, making debugging difficult.

Analysis Process :

Manually simulated a temporary table exceeding the limit by setting ibtmp1:12M:autoextend:max:30M and deleting an index on a 5‑million‑row table, then running a GROUP BY query, which immediately returned ERROR 1114 (HY000): The table '/tmp/#sql_…' is full.

Observed that the same query via JDBC with useCursorFetch=true did not return an error; the thread stayed in sleep until timeout.

Checked MySQL driver settings via the general log and found no special configuration that suppressed the error.

Tested the effect of the useCursorFetch flag: with the flag, the driver hides the error; without the flag, the error is reported.

Conclusion :

When the temporary table reaches the ibtmp1 limit, MySQL normally reports an error.

When JDBC uses useCursorFetch=true, the error is suppressed, causing the client to wait indefinitely.

Solution :

Limit ibtmp1 size via innodb_temp_data_file_path (e.g., max:2G) to prevent uncontrolled growth.

Replace segment‑fetch ( useCursorFetch=true) with a stream‑fetch approach so that errors are propagated to the application.

Adjust fetch size in code (e.g., st.setFetchSize(Integer.MIN_VALUE)) to enable streaming without the problematic flag.

Additional Knowledge :

MySQL 5.7 separates the temporary tablespace from the shared ibdata file, allowing size reset on restart.

Temporary tablespace usage can be queried with:

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

To limit size, set innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:2G and restart MySQL.

Temporary tablespace differs from tmpdir; the former stores non‑compressed InnoDB temporary tables, while tmpdir stores compressed temporary tables.

Code Example (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");
    }

    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(); } catch (InterruptedException e) {}
        long end = System.currentTimeMillis();
        System.out.println("Test end,total cost:" + (end-start) + "ms");
    }
}

class TestThread implements Runnable {
    Properties props;
    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;
        try {
            connection = DriverManager.getConnection(url, props);
            connection.setAutoCommit(false);
            st = connection.createStatement();
            st.setFetchSize(Integer.MIN_VALUE); // enable streaming
            ResultSet rs = st.executeQuery("select sum(k) from sbtest1 group by k");
            while (rs.next()) { /* process rows */ }
        } catch (Exception e) { e.printStackTrace(); }
        finally {
            try { if (st != null) st.close(); } catch (SQLException ignored) {}
            try { if (connection != null) connection.close(); } catch (SQLException ignored) {}
            countDownLatch.countDown();
        }
    }
}

The above code demonstrates how to configure a streaming fetch size to avoid the hidden error caused by useCursorFetch=true while still preventing excessive memory consumption.