How to fix CR_NO_RESULT_SET (2053): Attempt to read row with no result set in MySQL

MySQLINTERMEDIATEMEDIUM

This MySQL client error occurs when trying to fetch rows from a prepared statement that doesn't return a result set. It typically happens in C/C++ applications using the MySQL C API when calling mysql_stmt_fetch() on statements that execute non-SELECT queries like INSERT, UPDATE, or DELETE.

What this error means

The CR_NO_RESULT_SET error (code 2053) is a client-side error that occurs when using MySQL prepared statements. This error happens when you attempt to fetch rows using `mysql_stmt_fetch()` on a prepared statement that doesn't produce a result set. Unlike SELECT queries that return rows of data, statements like INSERT, UPDATE, DELETE, and CREATE TABLE don't return result sets. When you execute these statements, there are no rows to fetch. The MySQL client library prevents you from calling fetch operations on such statements to avoid undefined behavior. This error indicates a programming mistake where the application assumes a query returns data when it doesn't. It's commonly encountered when: - Generic database code handles different query types incorrectly - Applications dynamically build queries and misclassify their type - Migrating from older MySQL APIs to prepared statements - Writing database abstraction layers or ORMs

How to fix "CR_NO_RESULT_SET (2053): Attempt to read row with no result set"

1Identify which query type is causing the error

First, determine what type of query is being executed when the error occurs:

// Add logging to see the query being executed
const char *query = "INSERT INTO users (name) VALUES (?)";
printf("Executing query: %s\n", query);

MYSQL_STMT *stmt = mysql_stmt_init(conn);
if (mysql_stmt_prepare(stmt, query, strlen(query)) != 0) {
    fprintf(stderr, "Prepare failed: %s\n", mysql_stmt_error(stmt));
    return;
}

// Check if this is a SELECT query
if (mysql_stmt_field_count(stmt) > 0) {
    printf("This is a SELECT query (returns result set)\n");
} else {
    printf("This is a non-SELECT query (no result set)\n");
    // DON'T call mysql_stmt_fetch() on this!
}

The key function is mysql_stmt_field_count() which returns:
- > 0: Query returns a result set (SELECT, SHOW, DESCRIBE, EXPLAIN)
- = 0: Query doesn't return a result set (INSERT, UPDATE, DELETE, etc.)

2Fix the fetch logic for non-SELECT queries

Modify your code to only fetch rows for queries that return result sets:

MYSQL_STMT *stmt = mysql_stmt_init(conn);
mysql_stmt_prepare(stmt, query, strlen(query));

// Bind parameters if needed
mysql_stmt_bind_param(stmt, bind);

// Execute the statement
mysql_stmt_execute(stmt);

// Check if this query returns a result set
if (mysql_stmt_field_count(stmt) > 0) {
    // This is a SELECT-like query - bind results and fetch rows
    mysql_stmt_bind_result(stmt, bind_result);

    // Fetch rows
    while (mysql_stmt_fetch(stmt) == 0) {
        // Process row data
    }

    // Optionally store result for random access
    mysql_stmt_store_result(stmt);
} else {
    // This is INSERT/UPDATE/DELETE - no rows to fetch
    // Get affected rows instead
    my_ulonglong affected_rows = mysql_stmt_affected_rows(stmt);
    printf("Query affected %llu rows\n", affected_rows);

    // Get last insert ID for AUTO_INCREMENT tables
    my_ulonglong insert_id = mysql_stmt_insert_id(stmt);
    if (insert_id != 0) {
        printf("Last insert ID: %llu\n", insert_id);
    }
}

mysql_stmt_close(stmt);

For non-SELECT queries, use:
- mysql_stmt_affected_rows() - number of rows changed
- mysql_stmt_insert_id() - AUTO_INCREMENT value for INSERT

3Refactor generic database handling code

If you have generic database code, create separate handling for different query types:

typedef enum {
    QUERY_SELECT,
    QUERY_INSERT,
    QUERY_UPDATE,
    QUERY_DELETE,
    QUERY_OTHER
} QueryType;

QueryType classify_query(const char *query) {
    // Skip leading whitespace
    while (*query && isspace(*query)) query++;

    // Check first word (case-insensitive)
    if (strncasecmp(query, "SELECT", 6) == 0) return QUERY_SELECT;
    if (strncasecmp(query, "INSERT", 6) == 0) return QUERY_INSERT;
    if (strncasecmp(query, "UPDATE", 6) == 0) return QUERY_UPDATE;
    if (strncasecmp(query, "DELETE", 6) == 0) return QUERY_DELETE;
    return QUERY_OTHER;
}

void execute_query(MYSQL *conn, const char *query, MYSQL_BIND *params) {
    MYSQL_STMT *stmt = mysql_stmt_init(conn);
    mysql_stmt_prepare(stmt, query, strlen(query));

    if (params) {
        mysql_stmt_bind_param(stmt, params);
    }

    mysql_stmt_execute(stmt);

    QueryType type = classify_query(query);

    switch (type) {
        case QUERY_SELECT:
            // Handle SELECT - fetch rows
            handle_select_result(stmt);
            break;

        case QUERY_INSERT:
        case QUERY_UPDATE:
        case QUERY_DELETE:
            // Handle data modification - no fetch
            handle_non_select_result(stmt);
            break;

        default:
            // Use field_count for other query types
            if (mysql_stmt_field_count(stmt) > 0) {
                handle_select_result(stmt);
            } else {
                handle_non_select_result(stmt);
            }
    }

    mysql_stmt_close(stmt);
}

4Add comprehensive error checking and logging

Implement robust error handling to catch issues early:

int execute_prepared_statement(MYSQL_STMT *stmt, const char *query, MYSQL_BIND *params) {
    // Prepare
    if (mysql_stmt_prepare(stmt, query, strlen(query)) != 0) {
        fprintf(stderr, "Prepare error %d: %s\n",
                mysql_stmt_errno(stmt), mysql_stmt_error(stmt));
        return -1;
    }

    // Bind parameters if provided
    if (params && mysql_stmt_bind_param(stmt, params) != 0) {
        fprintf(stderr, "Bind param error: %s\n", mysql_stmt_error(stmt));
        return -1;
    }

    // Execute
    if (mysql_stmt_execute(stmt) != 0) {
        fprintf(stderr, "Execute error: %s\n", mysql_stmt_error(stmt));
        return -1;
    }

    // Check query type
    int field_count = mysql_stmt_field_count(stmt);
    printf("Query type: %s (field_count=%d)\n",
           field_count > 0 ? "SELECT-like" : "Non-SELECT", field_count);

    if (field_count > 0) {
        // SELECT query - handle result set
        return handle_result_set(stmt);
    } else {
        // Non-SELECT query - handle affected rows
        return handle_affected_rows(stmt);
    }
}

int handle_result_set(MYSQL_STMT *stmt) {
    MYSQL_BIND bind[10]; // Adjust based on expected columns
    // ... bind result columns ...

    if (mysql_stmt_bind_result(stmt, bind) != 0) {
        fprintf(stderr, "Bind result error: %s\n", mysql_stmt_error(stmt));
        return -1;
    }

    // Now safe to fetch
    int fetch_result;
    int row_count = 0;
    while ((fetch_result = mysql_stmt_fetch(stmt)) == 0) {
        row_count++;
        // Process row...
    }

    if (fetch_result != MYSQL_NO_DATA && fetch_result != 0) {
        fprintf(stderr, "Fetch error: %s\n", mysql_stmt_error(stmt));
        return -1;
    }

    printf("Fetched %d rows\n", row_count);
    return row_count;
}

int handle_affected_rows(MYSQL_STMT *stmt) {
    my_ulonglong affected = mysql_stmt_affected_rows(stmt);
    if (affected == (my_ulonglong)-1) {
        fprintf(stderr, "Affected rows error\n");
        return -1;
    }

    printf("Affected rows: %llu\n", affected);
    return (int)affected;
}

5Test with different query types and update code

Create test cases for all query types:

void test_query_types(MYSQL *conn) {
    const char *queries[] = {
        "SELECT * FROM users WHERE active = 1",
        "INSERT INTO users (name, email) VALUES ('test', '[email protected]')",
        "UPDATE users SET active = 0 WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR)",
        "DELETE FROM users WHERE id = 999",
        "CREATE TABLE IF NOT EXISTS temp_data (id INT PRIMARY KEY)",
        "SHOW TABLES",
        "DESCRIBE users",
        NULL
    };

    for (int i = 0; queries[i] != NULL; i++) {
        printf("\nTest %d: %s\n", i + 1, queries[i]);

        MYSQL_STMT *stmt = mysql_stmt_init(conn);
        if (execute_prepared_statement(stmt, queries[i], NULL) < 0) {
            printf("  FAILED\n");
        } else {
            printf("  SUCCESS\n");
        }
        mysql_stmt_close(stmt);
    }
}

Also check your MySQL client library version and update if needed:

bash

# Check current version
mysql_config --version

# Update MySQL Connector/C on Ubuntu/Debian
sudo apt update
sudo apt install libmysqlclient-dev

# On macOS with Homebrew
brew install mysql-client

Consider using higher-level abstractions if appropriate:
- MySQL++ for C++
- libdbi for database-independent access
- ORM frameworks that handle query type detection

Advanced notes

### Understanding MySQL Query Classification

MySQL queries fall into two main categories regarding result sets:

1. Queries that return result sets:
- SELECT
- SHOW
- DESCRIBE / EXPLAIN
- CHECK TABLE, REPAIR TABLE (with certain options)
- CALL (for stored procedures that return result sets)

2. Queries that don't return result sets:
- INSERT, UPDATE, DELETE
- CREATE, ALTER, DROP
- TRUNCATE, RENAME
- GRANT, REVOKE
- SET, USE
- BEGIN, COMMIT, ROLLBACK

### The mysql_stmt_field_count() Function

This function is crucial for determining query type at runtime:
- Returns 0: No result set (or result set already exhausted)
- Returns > 0: Result set available
- Must be called AFTER mysql_stmt_execute()
- Value equals number of columns in result set for SELECT queries

### Performance Implications

1. Avoid unnecessary field_count checks: If you know the query type at compile time, don't check at runtime.
2. Batch operations: For bulk INSERT/UPDATE, consider using multi-statements or LOAD DATA INFILE instead of repeated prepared statements.
3. Connection pooling: Ensure statement handles are properly reset between uses in connection pools.

### Language-Specific Considerations

PHP with mysqli:

php

$stmt = $mysqli->prepare("INSERT INTO users (name) VALUES (?)");
$stmt->execute();
// No fetch() call needed for INSERT
$affected_rows = $stmt->affected_rows;

Python with mysql-connector:

python

cursor.execute("INSERT INTO users (name) VALUES (%s)", ("test",))
# No fetchall() for INSERT
rowcount = cursor.rowcount

Java with JDBC:

java

PreparedStatement stmt = conn.prepareStatement("INSERT INTO users (name) VALUES (?)");
stmt.setString(1, "test");
int rows = stmt.executeUpdate();  // Use executeUpdate() for non-SELECT
// Not executeQuery() which expects result set

### Common Real-World Scenarios

1. Dynamic query builders: ORMs that generate SQL need to track query type.
2. Stored procedures: May or may not return result sets depending on implementation.
3. Multi-queries: mysql_more_results() and mysql_next_result() for handling multiple result sets.
4. Asynchronous operations: Need to track query type across async boundaries.

### Debugging Strategies

1. Enable MySQL client tracing:

mysql_debug("d:t:O,/tmp/mysql.trace");

2. Use Wireshark/tcpdump to see actual MySQL protocol packets.

3. Check server logs for query execution details.

4. Test with mysql command-line client to verify query behavior.

### Official Documentation References

- MySQL 8.0 C API Prepared Statements: Complete function reference
- MySQL Client/Server Protocol: Understanding result set protocol
- MySQL Error Codes: All CR_* error code definitions
- Prepared Statement Programming Guide: Best practices and examples

How to fix CR_NO_RESULT_SET (2053): Attempt to read row with no result set in MySQL

What this error means

Typical symptoms

Common causes

How to fix "CR_NO_RESULT_SET (2053): Attempt to read row with no result set"

Advanced notes

Related errors

Official resources & further reading