Bug 3846 - Workaround needed for bugs in ODBC, iodbc, MySQL ODBC, and Postgres ODBC for 64-bit platform support
: Workaround needed for bugs in ODBC, iodbc, MySQL ODBC, and Postgres ODBC for ...
: Replica Location
: development
: PC All
: P3 enhancement
: ---
Assigned To:
  Show dependency treegraph
Reported: 2005-10-26 21:06 by
Modified: 2006-01-26 15:22 (History)

Diff with unified context for readability (43.37 KB, text/plain)
2005-10-31 19:58, Rob S
Patch to db.c to work around bugs in odbc software (17.44 KB, patch)
2005-10-31 19:59, Rob S


You need to log in before you can comment on or make changes to this bug.

Description From 2005-10-26 21:06:07
There are a few problems with ODBC, iodbc, MySQL ODBC and Postgres ODBC that
prevent usage of RLS on 64-bit platforms.

One symptom of this bug is the following message seen during RLS Server startup.

2005-10-26 20:00:55 T182928092192: gettablecount(1): [MySQL][ODBC 3.51
Driver][mysqld-4.0.18]You have an error in your SQL syntax.  Check the manual
that corresponds to your MySQL server version for the right syntax to use near
'null)' at line 1

I do not know how the bug manifests itself with Postgres, but it will probably
be similar to the above.

On 64-bit platforms, the bug prevents using RLS with:
* iodbc + MySQL ODBC
* iodbc + Postgres ODBC

Without a workaround, the only viable combination on 64-bit platforms is with:
* unixODBC + Postgres ODBC
------- Comment #1 From 2005-10-26 21:20:35 -------
For those with a curiosity about the details of the ODBC / 64-bit problem, I 
think I understand the ODBC spec vs. implementation differences better. I 
think Microsoft’s definition of types is OK. It may be the (inconsistent?) way 
implementers have chosen to implement the spec that is an issue. In 
particular, the definition and/or use of the “SQLINTEGER”, “long”, 
and “SDWORD” types are causing us a problem.


There is a type defined by ODBC called SQLINTEGER and a flag used to identify 
this type SQL_C_LONG. The SQLINTEGER is (according to ODBC specs) a typedef 
for “long int”. So it would seem logical to me that if a user passes a 
variable of type SQLINTEGER to a given ODBC call then the SQL_C_LONG flag 
should be used to tell the ODBC function what type of variable was passed. The 
way I see it, the proper behavior for the ODBC driver would be to cast a 
variable from a void* to a SQLINTEGER*, when given a SQL_C_LONG flag.

One other thing, I noticed that the ODBC spec states that the “BufferLength” 
variable is ignored when the data variable in question is not “variable 
length” (e.g., byte arrays, char buffers, etc.). So this is NOT a valid way to 
protect against overwriting the buffer. What I think would have been better in 
the RLS code would have been to use SQLINTEGER for the variables rather than 
what was done originally which was to just make them “int” types.

Bottom line, RLS and other ODBC apps must depend on the SQL_C_LONG flag and 
the SQLINTEGER (or other) typedefs. Unfortunately, as you will see below, they 
cannot depend on them in reality. Therefore, even if we had used SQLINTEGER 
instead of int during ODBC calls, we still would have this exact problem.


More details below, but suffice to say there are inconsistencies between 
unixODBC and iodbc in how they specify various typedefs and between MySQL ODBC 
and Postgres ODBC in how they cast types based on SQL_C_LONG (and other) 
flags. To make matters worse, Microsft’s ODBC spec defines SQLINTEGER as 
a “long int” which thus varies in precision based on the computer architecture 
(4 or 8 bytes depending on 32bit or 64bit architectures), thus leaving things 
open to confusion. It is not pretty.


The problem with the MySQL ODBC driver is that it casts the data variable from 
a void* to a long* (based on a SQL_C_LONG flag) rather than using the 
SQLINTEGER type defined by the ODBC spec. So yes, anyone can read the spec and 
see that the SQLINTEGER typedef anyways is a “long int”, but it is not good 
practice that they didn’t stick to using the typedef. This definitely seems 
wrong to me.


I checked the Postgres ODBC driver and it casts the data variable from a void* 
to a SDWORD* (based on a SQL_C_LONG flag). This works out because the DWORD is 
a typedef for a “int” when using unixODBC. DWORD is actually a Microsoft type 
(at least, that’s where I know it from) but for some reason the ODBC managers 
(unixODBC and iodbc) both make sure it is defined. SDWORD is a fixed-precision 
type always to be a 32-bit integer (AFAIR).

Still, even with Postgres, I do not understand why they do not cast the 
variable to a SQLINTEGER. Again, unless I misunderstood the spec, it seems 
like better practice to stick to using the spec’s typedef for SQLINTEGER 
rather than using anything else – even if SDWORD should have a consistent 
meaning across architectures. It is also divergent from the ODBC spec since 
the SQL_C_LONG indicates a SQLINTEGER which is a typedef for “long int”. It 
seems that Postgres may be more wrong than MySQL though they are both wrong 
ultimately IMO.


The unixODBC library makes some adjustments to its typedefs based on whether 
the architecture is 64-bit. For instance on a SuSE box like the oceans11 LANL 
machine, unixODBC will conditionally typedef the SDWORD type as a 32-bit 
integer “int” whereas iodbc blindly typedefs a SDWORD to a “long” thus making 
it a 64-bit integer on SuSE. From what I understand the SDWORD is always 
supposed to be a 32-bit value, thus making iodbc somewhat broken on a 64-bit 
platform. It also means that RLS WILL NOT WORK when using Postres with iodbc 
on a 64-bit machine.


* unixODBC provides reasonable support for 64-bit platforms

* iodbc is broken for 64-bit platforms

* Postgres may be somewhat broken for 64-bit platforms but it will work with 
RLS with unixODBC on 64-bit platforms because we too are assuming SQL_C_LONG 
means 32-bit always (but it may just be that RLS and Postgres make the same 

* MySQL is broken on 64-bit platforms irregardless of using unixODBC or iodbc

* RLS is somewhat broken on 64-bit platforms because we directly use “int” 
instead of SQLINTEGER – having said that, even if we did it the right way by 
following Microsoft’s ODBC spec and using the SQLINTEGER type, it still 
wouldn’t have worked because of the fact neither driver casts to the 
SQLINTEGER (which is what I would assert is the right thing for all to be 


Solving this problem will amount to getting Microsoft, unixODBC, iodbc, MySQL, 
and Postgres to be consistent in how they define the specification 
(Microsoft), typedef per the spec (unixODBC, iodbc), and then properly 
implement type conversions per the spec (MySQL, Postgres). So that seems like 
a hopeless proposition.

The “right” solution as I see it is to make RLS use integer variables of type 
SQLINTEGER when calling ODBC routines, however, this will not work because of 
(what I consider to be) bugs in the MySQL and Postgres drivers.

What makes sense to me at this point is to continue with the RLS fix the way I 
was going about it. That is, to pass a variable of type “long int” when 
flagging with SQL_C_LONG. I have tested with MySQL and Postgres and it appears 
to work. So providing a 64-bit buffer when the driver will cast to a 32-
bit “int” (as will be the case with Postgres) does not cause any apparent 

It isn’t pretty though.
------- Comment #2 From 2005-10-31 19:58:15 -------
Created an attachment (id=730) [details]
Diff with unified context for readability

This diff illustrates the code changes that appear sufficient to work around
the odbc bugs.
------- Comment #3 From 2005-10-31 19:59:50 -------
Created an attachment (id=731) [details]
Patch to db.c to work around bugs in odbc software

This patch has been sufficient so far in working around the odbc bugs.
------- Comment #4 From 2005-12-20 17:17:03 -------
Committed to HEAD.
------- Comment #5 From 2006-01-26 15:22:49 -------
Also, updated in globus_4_0_branch.