MySQL5.lsp problem on linux64

Jeff · Post by **Jeff** » Wed Jul 23, 2008 3:14 pm

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 all

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 · Post by **Jeff** » Wed Jul 23, 2008 3:31 pm

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

Jeff · Post by **Jeff** » Wed Jul 23, 2008 4:50 pm

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

Code: Select all

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 · Post by **Lutz** » Wed Jul 23, 2008 9:19 pm

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 · Post by **Jeff** » Thu Jul 24, 2008 1:56 am

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 · Post by **Jeff** » Thu Jul 24, 2008 12:04 pm

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/ ... types.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 · Post by **Lutz** » Thu Jul 24, 2008 2:22 pm

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

Code: Select all

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 all

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 all

gcc -m64 sql.c

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

Code: Select all

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 · Post by **Jeff** » Thu Jul 24, 2008 3:47 pm

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 all

(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 all

(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 all

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

Jeff · Post by **Jeff** » Thu Jul 24, 2008 4:36 pm

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

Code: Select all

(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 · Post by **Lutz** » Thu Jul 24, 2008 5:57 pm

Congratulations!

but careful here:

Code: Select all

(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 all

(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 · Post by **Jeff** » Thu Jul 24, 2008 6:10 pm

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

Lutz · Post by **Lutz** » Thu Jul 24, 2008 7:30 pm

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 · Post by **Jeff** » Thu Jul 24, 2008 7:32 pm

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

Lutz · Post by **Lutz** » Thu Jul 24, 2008 7:43 pm

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