# Bug : Percona + XtraDB + C UDF

**URL:** <https://forums.percona.com/t/bug-percona-xtradb-c-udf/1651>\
**Category:** Other MySQL® Questions\
**Created:** [August 16, 2011, 2:18pm UTC](https://forums.percona.com/t/bug-percona-xtradb-c-udf/1651 "2011-08-16T14:18:18Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![DMWorkXpress](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/dmworkxpress/32/854_2.png) [@DMWorkXpress](https://forums.percona.com/u/DMWorkXpress)\
**Post date:** [August 16, 2011, 2:18pm UTC](https://forums.percona.com/t/bug-percona-xtradb-c-udf/1651/1 "2011-08-16T14:18:18Z")

</div>

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_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!!

---

<div class="post-metadata">

**Author:** ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)\
**Post date:** [August 16, 2011, 3:28pm UTC](https://forums.percona.com/t/bug-percona-xtradb-c-udf/1651/2 "2011-08-16T15:28:10Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![DMWorkXpress](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/dmworkxpress/32/854_2.png) [@DMWorkXpress](https://forums.percona.com/u/DMWorkXpress)\
**Post date:** [August 17, 2011, 1:59pm UTC](https://forums.percona.com/t/bug-percona-xtradb-c-udf/1651/3 "2011-08-17T13:59:39Z")

</div>

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.

Hmm…
