Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2010
    Posts
    5

    Unanswered: User defined procedures issues mysql 5.1

    Hi

    I am running a database in which I store lots of float values in a blob. Not always I need the whole set, sometimes a part of it and often just one value. So I wrote a small piece of C-Code, to extract 4 bytes from the blob and cast it to a float.

    This function works well on version 5.0 and earlier, but since i migrated to mysql 5.1 64 bits The return values are corrupted. Within the c-code you can write to the error log and at the last moment before I leave the c-code the value of the float is still correct.

    My questions:
    Is this a known 5.1 issue and do I have to change my mysql version?
    Is this a 64 bits version?

    Thanks
    Kees Kling

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi Kees,

    I am not sure I follow what you are saying above? The c-code is working fine when you print it to the error log prior to the end of your C-code but then what happens? Is is that the calling process using the returned value is incorrect?

    What version of the client are you using? Are you also using the 64 bit version or the 32 bit version?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Apr 2010
    Posts
    5
    Hi

    here is the piece of code I'm talking about
    Code:
    float bytes2float(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error) 
    {
        int i;
        char *cbuf = args->args[0]; 
    	for ( i=0; i<4; i++){
    		fprintf(stderr,"cbuf[%d] = %d\n", i, cbuf[i]);
    		} 
        if ( !is_bigendian() ){
    	ReverseArray(args->args[0]);
        };
        	for ( i=0; i<4; i++){
    		fprintf(stderr,"cbuf[%d] = %d\n", i, cbuf[i]);
    		} 
    	float *fp = (float *) cbuf;
    	//printf("*fp = %3.3f\n\n", *fp);
    	fprintf(stderr,"return is %3.5f\n",*fp);
    
    	return *fp;
    }
    It is inserted in the db
    CREATE FUNCTION bytes2float RETURNS REAL SONAME 'bytes2float.so';

    Normally this piece of code is called within a stored procedure, but I also tried it in the mysql commandline interface

    SELECT bytes2float( 'AK33'[) which are the bytes for float 12.70
    You can see in one of the last lines that I print the calculated value to the errorlogflie and that gives me '12.70' and that is correct, but mysql is returning 5.41222900980635e-315

    I am working with a complete 64 bits ubuntu linux version.
    I tried this code also on my laptop with version 5.0 but then the 32 bits version and there the returnvalue is correct.

    Regards Kees

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi Kees,

    the code should work i.e. is returning the float at the pointer fp. I have not checked this but on a 64 bit version does the float retain the same number of bits as with a 32 bit OS? Have you tried returning a fixed float number i.e.

    float test = 123.43;
    return test;

    and see what results you get? If you get the correct answer it could be that the number of bits used in the float has changed between 32 and 64 bit versions of the OS which implies that it is using the contents at the pointer location of the float plus more data which is causing this result.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Apr 2010
    Posts
    5
    Hi

    I did what you suggested. made a float

    float test = 123.43;
    fprintf(stderr,"return is &#37;3.5f\n",test);
    return test;

    In the logfile I get the value 123.43000 and mysql is returning:

    mysql> select bytes2float('AK33');
    +-----------------------+
    | bytes2float('AK33') |
    +-----------------------+
    | 5.55070141088899e-315 |
    +-----------------------+
    1 row in set (0.00 sec)

    So i am a bit stuck with it

    Kees

  6. #6
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi Kees,

    actually looking at your definition of the function you are returning REAL. Should this not be FLOAT?

    Ronan
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  7. #7
    Join Date
    Apr 2010
    Posts
    5
    hi,

    if you read the docu you only can return STRING,INTEGER,REAL or DECIMAL and DECIMAL is a stringtype return. If you would try FLOAT, there is an errormessage generated. The reason I think the all the declaration are good is bcause the same code is running fine on a 5.0 server version.

    Kees

  8. #8
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi Kees,

    I found this link about precisions that have changed between before 5.0.3 and from that point onwards. This may give more information as to why this is now not working? MySQL :: MySQL 5.0 Reference Manual :: B.5.5.8 Problems with Floating-Point Values

    Ronan
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  9. #9
    Join Date
    Apr 2010
    Posts
    5
    I have read the article, but it doesn't give a solution. I'm going to try it at the forum on forums.mysql.com. If I find a solution I will post it to this threat

    Many thanks till now

    Regards
    Kees

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •