Bugzilla – Bug 3846
Workaround needed for bugs in ODBC, iodbc, MySQL ODBC, and Postgres ODBC for 64-bit platform support
Last modified: 2006-01-26 15:22:49
You need to
before you can comment on or make changes to this bug.
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
* unixODBC + MySQL ODBC
Without a workaround, the only viable combination on 64-bit platforms is with:
* unixODBC + Postgres ODBC
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.
ODBC SPEC AS I UNDERSTAND IT:
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
THE unixODBC AND iodbc INCONSISTENCY PROBLEMS:
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.
SUMMARY OF PROBLEMS:
* 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
THOUGHTS ON FIXING THE PROBLEMS:
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.
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.
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.
Committed to HEAD.
Also, updated in globus_4_0_branch.