innodb commit strangeness when auto_commit is disabled

[Thomas Farthing]

Hi,

The code below is a bit long, but is pretty simple and should build as is (in VC++). My problem is that the last select in the main() returns no data when it should return a row. This only happens because the 2 inserts are performed on a different connection than the two selects.

This is what this program does:
- drop then create table_a and child table_b on connection #0
- insert row into table_a on connection #0
- select row from table_a on connection #1
- insert row into table_b on connection #0
- select row from table_b on connection #1

This code fails for the second select if run as is. But the second select behaves correctly if:
- auto commit is left active (line #10)
- or if the first select is skipped (line #108)
- or if the selects are performed on connection #0

If you place a breakpoint just before the second select (the one that fails to return data) and run the program to this point, data will be returned from another process if the same select is performed. This suggests that the data as been committed correctly, so why would this select fail to return data? Seems like there is a bug here.

Can someone please verify that they get the same behavior? Is there anything wrong with how I perform queries here? Maybe I'm missing a critical part of my query logic?

Thank you for any help you can offer!
Thomas

------------------------------

#include <winsock2.h>
#include <base/ext/mysql/include/mysql.h>

bool connect(MYSQL * connection, const char * host, const char * schema, const char * user, const char * password)
{
    if (!mysql_init(connection))
        { return false; }
    if (!mysql_real_connect(connection, host, user, password, schema, 0, 0, 0))
        { return false; }
    if (mysql_autocommit(connection, false) != 0)
        { return false; }
    return true;
}

bool execute(MYSQL * connection, const char * sql, MYSQL_BIND * binds)
{
    bool result = false;
    MYSQL_STMT * statement = mysql_stmt_init(connection);
    if (statement)
    {
        do
        {
            if (mysql_stmt_prepare(statement, sql, strlen(sql)) != 0)
                { break; }
            unsigned long type = CURSOR_TYPE_READ_ONLY;
            if (mysql_stmt_attr_set(statement, STMT_ATTR_CURSOR_TYPE, (const void *)&type) != 0)
                { break; }
            if (mysql_stmt_execute(statement) != 0)
                { break; }
            if (binds)
            {
                if (mysql_stmt_bind_result(statement, binds) != 0)
                    { break; }
                int fetchResult = mysql_stmt_fetch(statement);
				if (fetchResult != 0)
                    { break; }
            }
            if (mysql_stmt_free_result(statement) != 0)
                { break; }
            result = true;
        } while (false);
        mysql_stmt_close(statement);
    }
    return result;
}

bool query(MYSQL * connection, const char * sql)
{
    if (mysql_real_query(connection, sql, strlen(sql)) != 0)
        { return false; }
    MYSQL_RES * result = mysql_use_result(connection);
    if (result)
        { mysql_free_result(result); }
    return true;
}

struct RecordA
{
    RecordA();
    unsigned m_id;
    char m_name[41];
    unsigned long m_namelen;
    my_bool m_null[2];
    MYSQL_BIND m_bind[2];
};

RecordA::RecordA() 
{ 
    memset(this, 0, sizeof(RecordA)); 
    m_bind[0].buffer_type     = MYSQL_TYPE_LONG;
    m_bind[0].buffer          = &m_id;
    m_bind[0].is_unsigned     = true;
    m_bind[0].is_null         = &m_null[0];

    m_bind[1].buffer_type     = MYSQL_TYPE_STRING;
    m_bind[1].buffer          = m_name;
    m_bind[1].buffer_length   = 40;
    m_bind[1].length          = &m_namelen;
    m_bind[1].is_null         = &m_null[1];
}

typedef RecordA RecordB;

int main(int argc, char ** argv)
{
    MYSQL connection[2];
    memset(connection, 0, sizeof(connection));

    int result = 1;

    if (mysql_library_init(0, NULL, NULL)) 
        { return result; }
    
    if (!connect(&connection[0], "mysql_host", "schema", "username", "password") || 
        !connect(&connection[1], "mysql_host", "schema", "username", "password"))
        { return result; }

    query(&connection[0], "drop table table_b");
    query(&connection[0], "drop table table_a");
    if (query(&connection[0], "create table `table_a` (`id` int unsigned not null auto_increment, `name` varchar(40), primary key(`id`)) engine=innodb") && 
        query(&connection[0], "create table `table_b` (`id` int unsigned not null, `attr` varchar(40) unique, primary key(`id`, `attr`), index(`attr`), foreign key(`id`) references `table_a` (`id`) on delete cascade) engine=innodb"))
    { 
        do
        {
            if (!query(&connection[0], "insert into table_a (name) values ('name')") || mysql_commit(&connection[0]) != 0)
                { break; }

            if (!query(&connection[1], "select * from table_a where id = 1"))
                { break; }
            /*
            RecordA record_a;
            if (!execute(&connection[1], "select * from table_a where id = 1", record_a.m_bind))
                { break; }
            */

            if (!query(&connection[0], "insert into table_b (id, attr) values (1, 'attr')") || mysql_commit(&connection[0]) != 0)
                { break; }

            RecordB record_b;
            if (!execute(&connection[1], "select * from table_b where attr='attr'", record_b.m_bind))
                { break; }

            result = 0;
        } while (false);
    }

    mysql_library_end();
    return result;
}

------------------------------