Unexplained MySQL behavior using C libmysqlclient -

i have written processing engine in c extracts 40k records table, processes data , writes results different table. when print out processed data screen, looks perfect of variables holding right values. need write data table has 3 columns primary key. create table this:

create table halfcomp  (     marketcode varchar(20) not null,     trznum varchar(20) not null,     origtrznum varchar(20) not null,     primary key (marketcode, trznum, origtrznum) ) engine=innodb default charset=utf8; 

when c program writes database, results trash. repeating values shouldn't, results out of order, columns switched in places, etc. have been playing couple of days , think has primary key.

the reason think because made table auto-incrementing integer primary key freed other values plain old table values. created table this:

create table halfcomp (     ind int(11) auto_increment not null,     marketcode varchar(20) default null,     trznum varchar(20) default null,     origtrznum varchar(20) default null,     primary key (ind) ) engine=innodb default charset=utf8; 

when use same c code write table, results perfect. no repeating values, no mixed columns. here thing, need marketcode, trznum, , origtrznum columns primary key. creating first table inappropriately? @ loss.

i have been on c program fine tooth comb , looks right, , same c code works on other tables. table, not work. insight hugely appreciated! thanks!

edit: have updated code. no longer using malloc arbitrarily or sprintf, setting buffer value directly value of string variables. tried adding "\0" end of strings well. far, nothing has helped, getting same erratic behavior. happens when values primary keys, otherwise load correctly. have assume c adds null terminator end of strings when passes database information me in beginning, , use strcpy pass values , forth when processing data, have believe null terminators there already. other thoughts??

int writedb(void) {     mysql *conn2;     mysql_stmt *stmt2;     mysql_bind bind2[3];      char *server = "localhost";     char *user = "root";     char *password = "";     char *database = "thekeydb";     int port = 3306;      int k;     int length = 15000;     unsigned long mc2_length, mn2_length, om2_length;     const char *insertstmt2 = "insert tempcomp (marketcode, trznum, origtrznum) values (?,?,?)";      conn2 = mysql_init(null);     mysql_real_connect(conn2, server,user,password,database, port, null, 0);     stmt2 = mysql_stmt_init(conn2);     mysql_autocommit(conn2, 0);     mysql_query(conn2, "start transaction");      (k=0; k < length; k++) {         memset((void *) bind2, 0, sizeof(bind2));          mc2_length = strlen(comps[k].marketcode);         mn2_length = strlen(comps[k].trznum);         om2_length = strlen(comps[k].origtrznum);          if (mysql_stmt_prepare(stmt2, insertstmt2, strlen(insertstmt2)) != 0) {             printf("unable create new session: not prepare statement\n");         }          bind2[0].buffer_type = mysql_type_string;         bind2[0].buffer = comps[k].marketcode;         bind2[0].buffer_length = string_size;         bind2[0].is_null = 0;         bind2[0].length = &mc2_length;          bind2[1].buffer_type = mysql_type_string;         bind2[1].buffer = comps[k].trznum;         bind2[1].buffer_length = string_size;         bind2[1].is_null = 0;         bind2[1].length = &mn2_length;          bind2[2].buffer_type = mysql_type_string;         bind2[2].buffer = comps[k].origtrznum;         bind2[2].buffer_length = string_size;         bind2[2].is_null = 0;         bind2[2].length = &om2_length;          if (mysql_stmt_bind_param(stmt2, bind2) != 0) {             printf("unable create new session: not bind parameters");         }         mysql_stmt_execute(stmt2);         printf("%s, %s, %s, %s\n", comps[k].marketcode, comps[k].trznum, comps[k].origtrznum, comps[k].address);     }      mysql_query(conn2, "commit");     mysql_autocommit(conn2, 1);     mysql_stmt_free_result(stmt2);     mysql_stmt_close(stmt2);     mysql_close(conn2);      return 1; } 

almost surely, not null terminating strings. if pass string parameter prepared statement in libmysqlclient must specify length of string. if strings not null terminated , using strlen() pass length mysql_bind structure or, are¹ passing them sprintf() or similar build query, unexpected behavior behavior of porgram expected.

---

¹_{don't if doing it, use prepared statements instead.}