Announcement Module
No announcement yet.

Bug : Percona + XtraDB + C UDF

Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Bug : Percona + XtraDB + C UDF

    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, "", "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_f`SET `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_f`INNER JOIN `table_t`WHERE `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!!

  • #2
    Is this reproducible on vanilla MySQL 5.5? I do not think we have modified anything that could cause this. It is always possible the law of unintended consequences has struck us, though.

    Repro/nonrepro on standard MySQL 5.5 is the first step to figuring out what's wrong here, IMO.


    • #3
      Thank you for the speedy reply.

      I haven't yet put a system together with "vanilla" 5.5 to see if it's reproducible there.

      I *have* found, however, that it fails on both 5.1 and 5.5 on 64 bit architecture, but works on both 5.1 and 5.5 on 32 it architecture...

      I wonder where this knowledge would lead me to look next. We only run 64 bit servers, but I have a developer using a 32 bit machine who discovered it's fine for him. He had to re-compile the .so UDF Library on his machine to get it to work, of course, but used the same commands I used on the 64 bit system to produce the .so.



      • #4
        Update : Working on a Vanilla 5.5 test today/tomorrow. Will post when I know more.