| Bug #57763 | SQLColumnPrivileges returns error when it encounters a nullable column. | ||
|---|---|---|---|
| Submitted: | 27 Oct 2010 9:33 | Modified: | 15 Aug 2011 9:21 |
| Reporter: | Ben Engbers | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S1 (Critical) |
| Version: | 5.1.5r1144 | OS: | Linux |
| Assigned to: | Bogdan Degtyariov | CPU Architecture: | Any |
| Tags: | ODBC, SWI-Prolog | ||
[27 Oct 2010 10:48]
Tonci Grgin
Hi Ben and thanks for your report. Please do the following before getting back to me: o Update c/ODBC to most recent version o Do paste table structure o What is your driver manager and it's version? o Can you produce ODBC trace (see out manual on how to do that) and attach it. o What is your MySQL server version? It is not uncommon that some tools/clients do not follow ODBC specs so you can, if you have windows box available, test sequence of calls (that you will see in ODBC trace) Prolog does in Microsoft generic ODBC client, odbcte32.exe. If it passes there, the error is most likely in your DM or Prolog.
[27 Oct 2010 10:56]
Tonci Grgin
Sorry, server is "MySQL 5.1.47".
[27 Oct 2010 11:08]
Tonci Grgin
I will require a DNS info too... What options are in use?
[27 Oct 2010 11:09]
Tonci Grgin
Not my day obviously, DSN...
[27 Oct 2010 15:01]
Ben Engbers
Trace for mysql-connector-odbc
Attachment: mysql_odbc_v1.log (application/octet-stream, text), 23.09 KiB.
[27 Oct 2010 15:04]
Ben Engbers
Below are the table description and information on the drivermanager.
I didn't succeed in installing the latest connector. Output from rpm -Uvh mysql-connector-odbc-5.1.7-0.i386.rpm is
fout: Failed dependencies:
libltdl.so.3 is needed by mysql-connector-odbc-5.1.7-0.i386
libodbc.so.1 is needed by mysql-connector-odbc-5.1.7-0.i386
libodbcinst.so.1 is needed by mysql-connector-odbc-5.1.7-0.i386
This is trange since i have version 7.2.1 of libltdl.so and both the other libs are installed.
-------
mysql> desc ALMM_score;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| apl_id | decimal(22,0) | NO | PRI | NULL | |
| AG | tinyint(4) | NO | | 0 | |
| OR_Basis | tinyint(4) | NO | | 0 | |
| OR | tinyint(4) | NO | | 0 | |
| BS_Basis | tinyint(4) | NO | | 0 | |
| BS | tinyint(4) | NO | | 0 | |
| SS_Basis | tinyint(4) | NO | | 0 | |
| SS | tinyint(4) | NO | | 0 | |
| BA_Basis | tinyint(4) | NO | | NULL | |
| BA | tinyint(4) | NO | | 0 | |
| AS_Basis | tinyint(4) | NO | | NULL | |
| AS | tinyint(4) | NO | | 0 | |
| DF | tinyint(4) | NO | | 0 | |
| IM | tinyint(4) | NO | | 0 | |
| KG_Basis | tinyint(4) | NO | | 0 | |
| KG | tinyint(4) | NO | | 0 | |
| GD_Basis | tinyint(4) | NO | | 0 | |
| GD | tinyint(4) | NO | | 0 | |
| FK | tinyint(4) | NO | | 0 | |
| CL_Basis | tinyint(4) | NO | | 0 | |
| CL | tinyint(4) | NO | | 0 | |
| SO_Basis | tinyint(4) | NO | | 0 | |
| SO | tinyint(4) | NO | | 0 | |
| RB_Basis | decimal(22,0) | NO | | 0 | |
| RB | tinyint(4) | NO | | 0 | |
| LF | tinyint(4) | NO | | 0 | |
| CO | tinyint(4) | NO | | 0 | |
| SI | tinyint(4) | NO | | 0 | |
| OI | tinyint(4) | NO | | 0 | |
| GG | tinyint(4) | NO | | 0 | |
| MG | tinyint(4) | NO | | 0 | |
| TD | tinyint(4) | NO | | 0 | |
| TK | tinyint(4) | NO | | 0 | |
| Score_BV | tinyint(4) | YES | | NULL | |
| Score_TV | tinyint(4) | YES | | NULL | |
| X_as | tinyint(4) | YES | | NULL | |
| Y_as | tinyint(4) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
37 rows in set (0.00 sec)
Drivermanager: yum info unixodbc =>
Name : unixODBC
Arch : i686
Version : 2.2.14
Release : 12.fc12
Size : 1.1 M
Repo : installed
From repo : updates
Summary : A complete ODBC driver manager for Linux
URL : http://www.unixODBC.org/
PostgreSQL.
[3 Nov 2010 23:22]
Ben Engbers
I first have removed mysql-connector-odbc 5.1.5 and than tried to install the 5.1.7-version with rpm -ivh mysql-connector-odbc-5.1.7. Install still fails because of the missing dependencies. After reinstallation off mysql-connector-odbc-5.1.5 from the fedora repository with yum mysql-connector-odbc I could connect again to the database. I have created a new schema and created new tables with create table <newschema>.<name> select * from <name>. The new tables were from type MyISAM. After creating a new odbc-datasource and issuing the same prolog-question to this new datasource, the same error was produced so probably(?) the table-type is not relevant for this error.
[5 Nov 2010 16:43]
Ben Engbers
I have updated the connector with the command: rpm -Uvh --nodeps mysql-connector-odbc-5.1.7-0.i386.rpm. This command returned the following output: Preparing... ########################################### [100%] 1:mysql-connector-odbc ########################################### [100%] myodbc-installer: error while loading shared libraries: libodbc.so.1: cannot open shared object file: No such file or directory waarschuwing: %post(mysql-connector-odbc-5.1.7-0.i386) scriptlet failed, exit status 127 I also created a file /etc/profile.d/odbcini.sh containing the line "export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib:/usr/lib" This seems to have solved my problem
[15 Aug 2011 8:58]
Bogdan Degtyariov
Ben, Your ODBC trace does not contain any records indicating that SQLColumnPrivileges has been called at all. None of ODBC function calls ended with error (not counting the last SQLFetch(), which returned SQL_SUCCESS_WITH_INFO). SQL_SUCCESS_WITH_INFO status means that operation encountered a non-critical issue. Also, the last call set SQLSTATE "01004" meaning the string data has been truncated. This could happen if the length of data from SQLColumns() was bigger than the result buffer size (SQLBindCol()) and the driver had to truncate it to avoid SEGFAULT. Recently we made few patches for SQLColumns() function and what is more important, it takes data from Information_Schema database instead of parsing CREATE TABLE statements. All these improvements will be available in Connector/ODBC 5.1.9.
[15 Aug 2011 9:21]
Bogdan Degtyariov
Unfortunately, we cannot provide the pre-release binaries of Connector/ODBC, but you can always build it from the latest sources. The procedure is described here: http://dev.mysql.com/doc/refman/5.5/en/connector-odbc-installation-source-development.html
Description: On my laptop, running Fedora 12', I use the Mysql-connector-odbc to connect a Prolog-application (SWI-prolog 5.10.1) to my MySQL (MySQL 5.1.47) database. All tables I use are from the type InnoDB. Version-number 5.1.5r1144 is given by "yum info mysql-connector-odbc" The prolog-command "?- odbc_table_column(apmrap, 'ALMM_score', X)." returns: X = 'AS' ; % ODBC: State 01004: [MySQL][ODBC 5.1 Driver][mysqld-5.1.47] X = 'DF' ; false. The column that follows 'DF' is null-able and after issueing the mysql-command "ALTER TABLE `apm`.`ALMM_score` MODIFY COLUMN `IM` TINYINT(4) NOT NULL;" the same prolog-command now returns: X = 'AS' ; X = 'DF' ; X = 'IM' ; ... X = 'TD' ; % ODBC: State 01004: [MySQL][ODBC 5.1 Driver][mysqld-5.1.47] X = 'TK' ; false. The command that follows 'TK' is nullable again. I guess that odbc_table_column maps to SQLColumnPrivileges. The same ODBC error also shows up with the command "?- odbc_current_table(apmrap, X, Y).": ?- odbc_current_table(apmrap, X, Y). X = 'ALMM_score', Y = qualifier('') ; X = 'ALMM_score', Y = owner('') ; X = 'ALMM_score', Y = type('TABLE') ; X = 'ALMM_score', Y = comment('ALM tussen-resultaten') ; % ODBC: State 01004: [MySQL][ODBC 5.1 Driver][mysqld-5.1.47] X = 'ALMM_score', Y = arity(33) ; X = 'Final_Results', Y = qualifier('') . Here however execution of the prolog-command is not stopped. How to repeat: The same behaviuor was shown on every table.