I'm trying to connect to a MySQL database on localhost from within a UDF written in C. I created a user defined function, provided it to the MySQL DB as shared object in the plugin folder and made the UDF available:
CREATE FUNCTION test_udf RETURNS INTEGER SONAME 'test_udf.so';
That means I can use:
SELECT test_udf();
As test_udf() is called, it should connect to the database on localhost, SELECT data from the database, process the data and return an Integer Value if successful.
This works perfectly under Mac OSX but there is no chance for me to get this working under CentOS 7. As soon as the UDF tries to connect to the DB on localhost I get the error:
Can't connect to MySQL server on 'localhost'
Of course I did a proper setup of my.cnf and took care about the firewall settings.
Does anybody have an idea? Has anybody ever tried something like this?
This code compiles both on Mac and CentOS perfectly using gcc.
gcc $(mysql_config —cflags —include —libs) -shared -fPIC -o test_udf.so test_udf.c
/// DEBUG MODE FOR IDE (comment out if UDF need´s to be compiled using gcc from commandline) ///////////////////////////
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//#define DEBUG //<---------- COMMENT OUT IF COMPILATION AS SHARED OBJECT
//#define LINUX
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/***********************************************************************************************************************
* MySQL udf function prototypes
*
* for STRING functions:
* ++++++++++++++++++++++
* char *xxx(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length,char *is_null,
* char *error);
*
* for INTEGER functions:
* ++++++++++++++++++++++
* long long xxx(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);
*
* for REAL functions:
* ++++++++++++++++++++++
* double xxx(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);
*
* for DECIMAL functions:
* ++++++++++++++++++++++
* DECIMAL functions return string values and should be declared the same way as STRING functions.
* ROW functions are not implemented.
*
* Initialization / Deinitialization
* +++++++++++++++++++++++++++++++++
* The initialization and deinitialization functions are declared like this:
* my_bool xxx_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
* void xxx_deinit(UDF_INIT *initid);
*
***********************************************************************************************************************/
/***********************************************************************************************************************
* UDF global defines (needed for debug & UDF BOTH!)
***********************************************************************************************************************/
#define QUERY_1 "SELECT * FROM test_table"
/***********************************************************************************************************************
* UDF global defines (needed for debug & UDF)
***********************************************************************************************************************/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
#ifdef DEBUG
#define DEBUG_ON 1 // Debugging mode is on -> for testing in IDE
#else
/***********************************************************************************************************************
* automatic UDF function name generation
***********************************************************************************************************************/
#define DEBUG_ON 0 // Debugging mode is of when compiled as shared object
#define UDF_RETURN_TYPE my_ulonglong // the return value type <---------- TYPE IN RETURN VALUE TYPE
#define UDF_FCT_NAME test_udf // the UDF function name <---------- TYPE IN UDF FUNCTION NAME
#define INIT_FCT_NAME test_udf_init // the UDF init function name <---------- TYPE IN UDF INIT FUNCTION NAME
#define DEINIT_FCT_NAME test_udf_deinit // the UDF deinit function name <---------- TYPE IN UDF DEINIT FUNCTION NAME
#endif
/***********************************************************************************************************************
* Includes
***********************************************************************************************************************/
#ifdef DEBUG
#include <stdio.h>
#include <stdlib.h>
#ifndef LINUX
#include "include/mysql.h"
#else
#include "mysql/mysql.h"
#endif
#include <string.h>
#include "my_mysql_header.h"
#else
#include "my_mysql_header.h"
#ifdef STANDARD
/* STANDARD is defined, don't use any mysql functions */
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#ifdef __WIN__
typedef unsigned __int64 ulonglong;/* Microsofts 64 bit types */
typedef __int64 longlong;
#else
typedef unsigned long long ulonglong;
typedef long long longlong;
#endif /*__WIN__*/
#else
#include <my_global.h>
#include <my_sys.h>
#if defined(MYSQL_SERVER)
#include <m_string.h>/* To get strmov() */
#else
/* when compiled as standalone */
#include <string.h>
#define strmov(a,b) stpcpy(a,b)
#define bzero(a,b) memset(a,0,b)
#define memcpy_fixed(a,b,c) memcpy(a,b,c)
#endif
#endif
#include <mysql.h>
#include <ctype.h>
#endif
//#endif
#ifndef DEBUG
/***********************************************************************************************************************
* MySQL UDF function prototypes
***********************************************************************************************************************/
my_bool INIT_FCT_NAME(UDF_INIT *initid, UDF_ARGS *args, char *message);
void DEINIT_FCT_NAME(UDF_INIT *initid __attribute__((unused)));
UDF_RETURN_TYPE UDF_FCT_NAME(UDF_INIT* initid,
UDF_ARGS* args __attribute__((unused)),
char* is_null __attribute__((unused)),
char* error __attribute__((unused)));
#endif
/***********************************************************************************************************************
* Other function prototypes
***********************************************************************************************************************/
/***********************************************************************************************************************
* Other functions
***********************************************************************************************************************/
int finish_with_error(MYSQL *con)
{
mysql_close(con);
return 1;
}
/***********************************************************************************************************************
* MySQL udf function definitions
***********************************************************************************************************************/
#ifdef DEBUG
int main ()
{
#else
my_bool INIT_FCT_NAME(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
if(!(args->arg_count == 0)) {
strcpy(message, "No Arguments expected!");
return 1;
}
return 0;
}
void DEINIT_FCT_NAME(UDF_INIT *initid __attribute__((unused)))
{
}
UDF_RETURN_TYPE UDF_FCT_NAME(UDF_INIT* initid,
UDF_ARGS* args __attribute__((unused)),
char* is_null __attribute__((unused)),
char* error __attribute__((unused)))
{
#endif
MYSQL *con = mysql_init(NULL);
if (con == NULL)
{
fprintf(stderr, "%s\n", mysql_error(con));
exit(1);
}
if (mysql_real_connect(con, ADDRESS, USER, PASSWORD, DATABASE, 0, NULL, 0) == NULL)
{
finish_with_error(con);
}
if (mysql_query(con, QUERY_1))
{
finish_with_error(con);
}
MYSQL_RES *result = mysql_store_result(con);
if (result == NULL)
{
finish_with_error(con);
}
/*
* UDF CODE GOING HERE!
*/
mysql_free_result(result);
mysql_close(con);
return 0;
}
Okay finally i figured out how to get this working.
1) Added mysql_options before connect:
mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,"client");
2) Changed from mysql_query to mysql_real_query
mysql_real_query(&mysql, "select * from new_schema.new_table", 34);
3) Set my.cnf to:
[client]
port=3306
socket=/var/lib/mysql/mysql.sock
4) Use 127.0.0.1 instead of "localhost" in mysql_real_connect function, else the socket configured won´t be used