Issue with INSERT data not being available for immediate SELECT

You are viewing an old version of this question. View the current version here.

I've got an issue where it appears that data that I insert isn't available for an immediate SELECT statement executed by the same thread.

I'm inserting rows into "table_a", that has columns "id", "a", and "b". "id" is an auto_number column, and I have a separate index on "a". For each row, I grab the insert ID, then run "INSERT INTO table_b (table_a_id_1, table_a_id_2) SELECT ?, id FROM table_a WHERE a = ? AND id != ?" (the variables are replaced with the insert ID, the value of "a" I just inserted, and the insert ID again). Both tables are InnoDB tables.

My problem is that the query never inserts anything. It's not throwing an errors -- it just seems that the SELECT part of the query isn't matching anything, even when I know that it should be. I know the data is there -- I can take the query, run it manually through the mysql command line, and get the results I'm expecting. I've tried this a number of different ways -- separating it into two separate queries (a SELECT and an INSERT query), marking the INSERT query as HIGH_PRIORITY, wrapping the whole thing in a transaction...nothing seems to work. All signs point to "the SELECT query isn't returning any rows".

Is this caused by some sort of write caching issue in MariaDB? Is there some way I can work around this?

For reference, here's my code:

int save_to_database(char *a, char *b, my_ulonglong &insert_id) {
  const char *insert_str = "INSERT INTO table_a (a, b) VALUES (?, ?)";
  MYSQL_STMT *stmt;
  MYSQL_BIND bind[2];
  int retval;
  unsigned long buffer_length[2];
  my_bool is_null[2];

  stmt = mysql_stmt_init(boinc_db.mysql);
  if(!stmt) {
    // Print an error, return -1
  }

  if(mysql_stmt_prepare(stmt, insert_str, strlen(insert_str))) {
    // Print an error, close stmt, return -1
  }

  memset(bind, 0, sizeof(bind));

  buffer_length[0] = strlen(a);
  buffer_length[1] = strlen(b);

  is_null[0] = 0;
  is_null[1] = 0;

  bind[0].buffer_type = MYSQL_TYPE_STRING;
  bind[0].buffer = a;
  bind[0].buffer_length = buffer_length[0];
  bind[0].length = &buffer_length[0];
  bind[0].is_null = &is_null[0];
  bind[0].is_unsigned = 0;

  bind[1].buffer_type = MYSQL_TYPE_STRING;
  bind[1].buffer = b;
  bind[1].buffer_length = buffer_length[1];
  bind[1].length = &buffer_length[1];
  bind[1].is_null = &is_null[1];
  bind[1].is_unsigned = 0;

  if(mysql_stmt_bind_param(stmt, bind)) {
    // Print an error, close stmt, return -1
  }

  if(mysql_stmt_execute(stmt)) {
    // Print an error, close stmt, return -1
  }

  insert_id = mysql_stmt_insert_id(stmt);

  mysql_stmt_close(stmt);
  return 0;
}

int generate_matches(my_ulonglong insert_id, char *a) {
  const char *insert_str = "INSERT INTO table_b (table_a_id_1, table_a_id_2) SELECT ?, id FROM table_a WHERE a = ? AND id != ?";

  MYSQL_STMT *stmt;
  MYSQL_BIND bind[3];
  int retval;
  unsigned long buffer_length[3];
  my_bool is_null[3];

  stmt = mysql_stmt_init(boinc_db.mysql);
  if(!stmt) {
    // Print an error, return -1
  }

  if(mysql_stmt_prepare(stmt, insert_str, strlen(insert_str))) {
    // Print an error, close stmt, return -1
  }

  memset(bind, 0, sizeof(bind));

  buffer_length[0] = sizeof(insert_id);
  buffer_length[1] = strlen(a) + 1;
  buffer_length[2] = sizeof(insert_id);

  is_null[0] = 0;
  is_null[1] = 0;
  is_null[2] = 0;

  bind[0].buffer_type = MYSQL_TYPE_LONGLONG;
  bind[0].buffer = &insert_id;
  bind[0].buffer_length = buffer_length[0];
  bind[0].length = &buffer_length[0];
  bind[0].is_null = &is_null[0];
  bind[0].is_unsigned = 1;

  bind[1].buffer_type = MYSQL_TYPE_STRING;
  bind[1].buffer = a;
  bind[1].buffer_length = buffer_length[1];
  bind[1].length = &buffer_length[1];
  bind[1].is_null = &is_null[1];
  bind[1].is_unsigned = 0;

  bind[2].buffer_type = MYSQL_TYPE_LONGLONG;
  bind[2].buffer = &insert_id;
  bind[2].buffer_length = buffer_length[2];
  bind[2].length = &buffer_length[2];
  bind[2].is_null = &is_null[2];
  bind[2].is_unsigned = 1;

  if(mysql_stmt_bind_param(stmt, bind)) {
    // print an error, close stmt, return -1
  }

  if(mysql_stmt_execute(stmt)) {
    // Print an error, close stmt, return -1
  }

  mysql_stmt_close(stmt);

  return 0;
}

And then later on in the program I do:

      if(save_to_database(a, b, insert_id)) {
        // Print an error, rollback, turn autocommit on, return -1
      }

      if(generate_matches(insert_id, a)) {
        // Print an error, rollback, turn autocommit on, return -1
      }

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.