MySQL5.lsp problem on linux64

[Jeff]

When I run fetch-row, every other field, starting with the second, is nil.  num-fields is giving the right number, and I am using the offsets generated by sql.c.  When I added a println form to see each field_ptr, every other one is 0.  If I set the multiplier from 4 to 8, I get the right field addresses, but I'm still getting the wrong data types.  I checked the the numbers seem correct for the types enum:

Code Select Expand

enum enum_field_types { MYSQL_TYPE_DECIMAL, MYSQL_TYPE_TINY,
                        MYSQL_TYPE_SHORT,  MYSQL_TYPE_LONG,
                        MYSQL_TYPE_FLOAT,  MYSQL_TYPE_DOUBLE,
                        MYSQL_TYPE_NULL,   MYSQL_TYPE_TIMESTAMP,
                        MYSQL_TYPE_LONGLONG,MYSQL_TYPE_INT24,
                        MYSQL_TYPE_DATE,   MYSQL_TYPE_TIME,
                        MYSQL_TYPE_DATETIME, MYSQL_TYPE_YEAR,
                        MYSQL_TYPE_NEWDATE, MYSQL_TYPE_VARCHAR,
                        MYSQL_TYPE_BIT,
                        MYSQL_TYPE_NEWDECIMAL=246,
                        MYSQL_TYPE_ENUM=247,
                        MYSQL_TYPE_SET=248,
                        MYSQL_TYPE_TINY_BLOB=249,
                        MYSQL_TYPE_MEDIUM_BLOB=250,
                        MYSQL_TYPE_LONG_BLOB=251,
                        MYSQL_TYPE_BLOB=252,
                        MYSQL_TYPE_VAR_STRING=253,
                        MYSQL_TYPE_STRING=254,
                        MYSQL_TYPE_GEOMETRY=255

};

...and from the mysql 5 c api docs, the index of the field type seems to be correct.  Any idea why I would be getting such wrong numbers for my field types?  It's mysql compiled for 64, as well as newlisp compiled for 64.

[Jeff]

In fact, using the (* 19 4) I'm getting inconsistent types- two different int(11) fields are reporting as types 2 and 17...

[Jeff]

It's really odd.  I printed out a table of field offset, field name, data type, and what that translates to:

Code Select Expand

0|id|2|MYSQL_TYPE_SHORT
1|slug|4|MYSQL_TYPE_FLOAT
2|classification_id|17|???
3|short_title|11|MYSQL_TYPE_TIME
4|title|5|MYSQL_TYPE_DOUBLE
5|description|11|MYSQL_TYPE_TIME
6|custom|6|MYSQL_TYPE_NULL
7|active|6|MYSQL_TYPE_NULL
8|listed|6|MYSQL_TYPE_NULL
9|error_flag|10|MYSQL_TYPE_DATE
10|max_photos|10|MYSQL_TYPE_DATE
11|sort_field|10|MYSQL_TYPE_DATE
12|sort_direction|14|MYSQL_TYPE_NEWDATE
13|manual_sorting|14|MYSQL_TYPE_NEWDATE
14|thumbnails|10|MYSQL_TYPE_DATE
15|usage|5|MYSQL_TYPE_DOUBLE
16|created|7|MYSQL_TYPE_TIMESTAMP
17|modified|8|MYSQL_TYPE_LONGLONG

The real field types should be:



00 int

01 varchar

02 int

03 varchar

04 varchar

05 longtext

06 longtext

07 tinyint

08 tinyint

09 tinyint

10 int

11 int

12 int

13 longtext

14 longtext

15 int

16 datetime

17 datetime

[Lutz]

You could compile types.c from the same directory as sql.c to see the sizes of C data types, and relate that to the results from sql.c. Make sure the MySQL client library libmysqlclient.so is also compiled for 64-bit. Offsets would step up by 8 not 4 bytes.



And ... are you sure MySQL (and client library) is compiled as a 64-bit application? Even on 64-bit Linux its unusual to run a 64-bit app API. The normal thing is to run a 32-API, compiling newLISP using:



make -f makefile_linux64ILP32



for this you would compile sql.c and types.c using:



gcc -m32 types.c



or



gcc -m32 sql.c



the -m32 forces a 32-bit API even on a 64-bit Linux.



Most stock Linux installs on 64-bit CPUs will put the 32-bit libraries into /usr/lib but the 64-bit API libraries into some other specially marked place.

[Jeff]

Lutz,



I will try that in the morning; I'm pretty sure its mysql compiled for 64bit, since libmysqlclient is in /usr/lib64.  Thanks!



Jeff

[Jeff]

Well, I verified that libmysqlclient is in /usr/lib64 (which I had to add to mysql5.lsp).  From the c api docs (http://dev.mysql.com/doc/refman/5.0/en/c-api-datatypes.html">http://dev.mysql.com/doc/refman/5.0/en/ ... types.html">http://dev.mysql.com/doc/refman/5.0/en/c-api-datatypes.html) for MYSQL_FIELD, every field in the struct should be 8 bytes (they are all char*, uint, or ulong, and the types enum).  But when I set the offset to (* 19 8), I get radically out of range values.  I poked through mysql_com.h and some of the other headers and it looks like everything is where it should be, but I'm just not getting what I'm expecting :(

[Lutz]

yes, /usr/lib64 means definitely 64-bit. In that case you would compile newLISP using:

Code Select Expand

make -f makefile_linux64LP64

in mysql51.l you may have to change 'get-int' into 'get-long', but the 'int' cast/conversions as in (int offset) would stay.



here is the output of types.c when compiling with -m64

Code Select Expand

type      bytes
---------------
char        1
char *      8
void *      8
short int   2
int         4
long        8
long int    8
long long   8
size_t      8
float       4
double      8
long double 16
wchar_t     4


Note that pointers and longs are all 8 byte but int is still 4 bytes, that means in that case you would still do: 'get-int', but for size_t and long you would use 'get-long'.



You also would compile sql.c accordingly for a 64-bit API:

Code Select Expand

gcc -m64 sql.c

you may have to consult the mysql.h headerfile to inspect the following C structures:

Code Select Expand

MYSQL  * mysql;
MYSQL_RES  * mysql_res;


Most of the data fields used by mysql5.lsp are contained in these structures.



It may need some experimentation and binary inspection of the numbers returned to get it right in mysql5.lsp. Do a: (format "%lx" the-number) to inspect results coming from 'get-long' to see potential wrong shifts of the number to either lower or higher numbers.



Again, if you can stay with a 32-bit API, but if your must go 64-bit all the way, then these are the methods to get there when converting the mysql module file.



Good luck ;)

[Jeff]

Ok, I got it working.  I was using 8 bytes across the entire structure, when on 64-bit, the first 9 fields are 8 bytes, then next 10 are 4 bytes.  So, the total changes made for libmysqlclient (64 bit) and 64-bit newlisp:



file paths

Code Select Expand

(set 'files '(
  "/usr/lib64/libmysqlclient.so" ; Linux64
"/usr/lib/libmysqlclient.so" ; Linux, UNIX
"/usr/local/mysql/lib/libmysqlclient.dylib" ; MacOS X
))

keep-type

Code Select Expand

(set 'data (get-int (int (+ type_ptr (* 19 4)))))
to
(set 'data (get-int (int (+ type_ptr (+ (* 9 8) (* 10 4))))))

fetch-row

Code Select Expand

(set 'field_addr (get-int (int (+ rdata (* field 4)))))
to
(set 'field_addr (get-int (int (+ rdata (* field 8)))))

[Jeff]

Oops - forgot.  Had to update the offsets at the beginning of the file, too:

Code Select Expand

(constant 'NUM_ROWS_OFFSET (if big-endian 4 0))
(constant 'NUM_FIELDS_OFFSET 96)
(constant 'ERROR_OFFSET 141)
(constant 'INSERT_ID_OFFSET (if big-endian 836 832))
(constant 'AFFECTED_ROWS_OFFSET (if big-endian 828 824))

[Lutz]

Congratulations!



but careful here:

Code Select Expand

(set 'field_addr (get-int (int (+ rdata (* field 8)))))

addresses/pointers (as in field_addr) are all 64-bit(8 bytes) so 'get-int' gets only the lower 4 significant bytes of the address assuming you are on a i386 CPU little Endian architecture.



This is fine when your machine has less then 4G of memory, where the higher 4 bytes are all 0 anyway, but could go wrong on machines with  more memory or big Endian architectures (e.g. Sparc), where you would only get the high bytes (all 0) or miss higher bits on little Endian architectures.



So you may want to use 'get-long' and try:

Code Select Expand

(set 'field_addr (get-long (int (+ rdata (* field 8)))))

and then have more portable code and code running on machines with more than 4G of memory, or running on big Endian CPUs.

[Jeff]

Is the same true for the 'data pointer in keep-type?

[Lutz]

No, 'data' is just an enum data type of a variable, which is some small integer used in function (keep-type ...), but 'field_addr' is a memory address or memory pointer.

[Jeff]

According to the docs, num-rows, affected-id, and inserted-id should all be returning longs as well.

[Lutz]

Ok, a long in 64-bit C would be 64 bit too and you would use 'get-long'. In 32-bit C a long is only 32 bit. Only the C int is 32 bit in both 32-bit C and 64-bit C.



They call this the LP64 memory model. The following is an interesting link about memory models in 64-bit programming.



http://www.unix.org/version2/whatsnew/lp64_wp.html">http://www.unix.org/version2/whatsnew/lp64_wp.html