MySQL Bugs: #2334: fputs doesn't work inside of UDF

Bug #2334	fputs doesn't work inside of UDF
Submitted:	9 Jan 2004 7:23	Modified:	12 Jan 2004 13:33
Reporter:	Alexander	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: User-defined functions ( UDF )	Severity:	S3 (Non-critical)
Version:	4.0.15	OS:	Linux (Linux Mandrake 9.0)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
I've made the UDF function which, in particular, creates the file for writing in init block, add some strings in add block and close the handler in deinit block. It DOES create the file, it DOES say it adds the strings (fputs returns 1), but actually it doesn't write anything to file and it's of zero size.

How to repeat:
Add FILE *fp to user structure, which will be available via initid->ptr for working with intermediate data - let's say it's userData.
So,
struct userData {
....
FILE *fp;
....
}
myfunc_init(....) {
....
userData *myData;
//making malloc ....
myData->fp = fopen("seq_file.txt", "w");
....
initid->ptr = (char*)myData;
....
}

myfunc_add(....) {
....
userData *myData = (userData*)initid->ptr;
fputs( some_string, myData->fp ); //nothing actually writes, although fputs //returns 1!
....
}

myfunc_deinit(....) {
....
userData *myData = (userData*)initid->ptr;
fclose(myData->fp);
....
}

Suggested fix:
I've got no idea :) I want to ask you, why fputs doesn't work :))

Ok, I suppose after "init" function finishes its work, it automatically closes the descriptor? I've checked - if I make fopen and fclose in "add" function it works. But in my case "add" function is called thousands times. Why should I open and close the file every time? I suspect it makes execution time much longer. I want to open and close working file only once. Is there a way to do it, using aggregate function?

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

I cannot repeat it.
fputs() works perfectly for me - it writes to file as expected

Sergei, everything's ok - I had to add fflush to get data from buffer. Strange... fclose should do it automatically (I've placed it into deinit block). So, I've put fflush into add UDF block. I handle about 10000 records length of ~2kb. Would adding fflush affect perfomance very largely? UDF in MySQL can return only string, not table. So, I guess, writing one string per one "add" call to file is better, than returning one big string of 10000*2kb, right?

I could use one string result per function call, but in this case the SQL query is cumbersome (have to use "group by id, id"): SELECT myfunc(t1.seq, t2.seq) FROM tbl as t1, tbl as t2 WHERE t1.id < n+1 and t2.id > n GROUP BY t1.id, t2.id.
My function makes "by pair" comparison of long symbolic sequences (seq), which are stored in a table.

Sorry for some off-topic - the task is serious, but I wasn't able to find serious MySQL forum...

Thank you.

no, in my tests everything worked without fflush()

yes, fllush() on every add() may cause a noticeable performance degradation.

In any case, this is not a bug issue, so nothing to discuss here.

You may use internals@lists.mysql.com as a "serious MySQL forum"