WorkXpress is performance testing our switch to INNODB, specifically XtraDB on Percona 5.5 for our Platform as a Service. As part of the transition we’ve had to re-write several stored procedures as C-based UDF’s.
Some of our UDF’s reach back into the Database to grab additional data, using the MySQL C Library and use that data as part of the UDF’s operation.
Some of these UDF’s have been failing in Percona 5.5, that are not failing in 5.1, and as we want to use 5.5 for production, we’re concerned. I have simplified the tables, queries, and UDF into the simplest form I can find that will reproduce the problem.
Tables/Data :
DROP TABLE IF EXISTS table_t;CREATE TABLE table_t ( id char(32) DEFAULT NULL, udf_result char(255) DEFAULT NULL, KEY target_alias (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 DELAY_KEY_WRITE=1;insert into table_t (id) values (‘u11’), (‘u12’), (‘u13’), (‘u14’);DROP TABLE IF EXISTS table_f;CREATE TABLE table_f ( id char(32) NOT NULL, field_id char(32) NOT NULL, value char(255) NOT NULL, PRIMARY KEY (value,field_id,id), UNIQUE KEY ii_fi (id,field_id), KEY fi_ii (field_id,id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into table_f values (‘u11’, ‘a1’, ‘u1’), (‘u12’, ‘a1’, ‘u2’), (‘u13’, ‘a1’, ‘u3’), (‘u14’, ‘a1’, ‘u4’);DROP TABLE IF EXISTS table_v;CREATE TABLE table_v ( id char(32) NOT NULL, value char(255) NOT NULL, PRIMARY KEY (id), KEY v_ii (value,id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into table_v values (‘u1’, ‘A’), (‘u2’, ‘B’), (‘u3’, ‘C’), (‘u4’, ‘D’);
UDF :
/* select_test */my_bool select_test_init(UDF_INIT *initid, UDF_ARGS *args, char message);void select_test_deinit(UDF_INIT initid);char select_test(UDF_INIT initid, UDF_ARGS args, char result, unsigned long length, char is_null, char error);/ * select_test /my_bool select_test_init(UDF_INIT initid, UDF_ARGS args, char message){ if (args->arg_count != 2) { strmov(message,“Usage: select_test( value, query )”); return 1; } args->arg_type[0] = STRING_RESULT; args->arg_type[1] = STRING_RESULT; struct mysql_conn data; if (!(data = (struct mysql_conn)malloc(sizeof(struct mysql_conn)))) { strcpy(message, “mysql_conn() couldn’t allocate memory”); return 1; } //Initialize variables for the mysql connection MYSQL mysql = NULL; MYSQL_RES qryResult = NULL; // Initialize the MySQL Connection mysql = mysql_init( mysql ); if ( !mysql ) { strcpy(message, “mysql_conn() couldn’t allocate memory”); fprintf( stderr, “Error on Init: %s\n”, mysql_error( mysql )); return 1; } // Connect if(!mysql_real_connect(mysql, “127.0.0.1”, “USERNAME”, “PASSWORD”, “DATABASE”, 3306, “/var/run/mysqld/mysqld.sock”, 0)) { strcpy(message, “Error connecting”); fprintf( stderr, “Error connecting: %s\n”, mysql_error( mysql )); mysql_close( mysql ); return 1; } data->mysqlConn = mysql; data->mysqlQryResult = qryResult; initid->ptr = (char)data; return 0;} // end function select_test_init()void select_test_deinit(UDF_INIT *initid){ // read the mysql data from initid->ptr struct mysql_conn *data = (struct mysql_conn )initid->ptr; mysql_free_result( data->mysqlQryResult ); mysql_close( data->mysqlConn ); free(initid->ptr);}char select_test(UDF_INIT initid, UDF_ARGS args, char result, unsigned long length, char *is_null, char *error){ if(args->lengths[0] == 0 || args->args[0] == 0) { is_null = 1; return; } char * arg0 = substr(args->args[0], 0, args->lengths[0]); // Query char * arg1 = substr(args->args[1], 0, args->lengths[1]); // Query // read the timing data from initid->ptr struct mysql_conn data = (struct mysql_conn )initid->ptr; char query = NULL; query = str_replace(arg1, “VALUE”, arg0, NULL); // Get the MySQL Connection from ‘data’ MYSQL mysql = data->mysqlConn; MYSQL_RES qryResult = data->mysqlQryResult; if ( mysql_real_query( mysql, query, strlen( query ) ) ) { fprintf( stderr, “Error No : %d\n”, mysql_errno( mysql )); fprintf( stderr, “Error : %s\n”, mysql_error( mysql )); fprintf( stderr, “on Query : %s\n”, query); fprintf( stderr, “SQL State : %s\n”, mysql_sqlstate( mysql )); mysql_close( mysql ); *error = 1; *length = 0; *is_null = 1; return; } free(query); free(arg0); free(arg1); if ( ( qryResult = mysql_store_result( mysql ) ) ) { MYSQL_ROW row; while ( ( row = mysql_fetch_row( qryResult ) ) ) { fprintf( stderr, “Spin…: %s | %s\n”, row[0], row[1]); strcpy(result, row[1]); *length = strlen(result); mysql_free_result(qryResult); return result; } mysql_free_result(qryResult); } else { fprintf( stderr, “Error on Storing Query Result: %s\n”, mysql_error( mysql )); mysql_close( mysql ); *error = 1; return; } *length = 0; *is_null = 1; return NULL;} // End select_test
The query we intended to run was :
UPDATE table_t, table_fSET table_t.udf_result = select_test(table_f.value, “SELECT id, value FROM table_v WHERE id in (‘VALUE’)”) WHERE table_t.id = table_f.id AND table_f.field_id = ‘a1’;
This query fails, with a timeout and MYSQL restarting itself.
If, however, I first run…
SELECT select_test(table_f.value, “SELECT id, value FROM table_v WHERE id in (‘VALUE’)”) FROM table_fINNER JOIN table_tWHERE table_f.field_id = ‘a1’ and table_t.id = table_f.id ;
Then the UPDATE statement will run just fine. We’re assuming it’s because the results of the internal queries are cached, and so it doesn’t have to actually run the queries during the subsequent Update.
At this point we’re in trouble, and not able to go to 5.5 if we can’t get this to work :-/ 5.1 is an option, and certainly better then vanilla MySQL with MyISAM… but I’d like to upgrade to the latest and greatest while we’re at it.
Any help is appreciated!!