Bug #14609 Unknown type '246 in column 2 of 12 in binary-encoded result set
Submitted: 3 Nov 2005 15:33 Modified: 10 Mar 2006 20:24
Reporter: Syvalta Sa Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.0 nightly, 3.1.11 OS:Microsoft Windows (WinXPPro SP2)
Assigned to: CPU Architecture:Any

[3 Nov 2005 15:33] Syvalta Sa
Description:
The error occurs when fetching data from db using openoffice base.

SQL Status: S1000
Unknown type '246 in column 2 of 12 in binary-encoded result set.

The table has a varchar, several decimal(12,3) and integer(11).

Tested with following jdbc drivers:
mysql-connector-java-3.1.11-bin.jar
mysql-connector-java-3.1-nightly-20051031-bin.jar
mysql-connector-java-5.0-nightly-20051103-bin.jar

Seems that the error occurs when there is  decimal field the table.

The server is "mysqld  Ver 5.0.15 for Win32 on ia32 (Official MySQL binary)"

I suspect the condition is the same as in #13291: "walk through the result set (while (rs.next())".

How to repeat:
I'm not sure what jdb function openoffice uses. Fetching data using rs.next() from resultset from table with decimal propably reproduces the bug.
[3 Nov 2005 18:05] Mark Matthews
I can't reproduce this with 5.0 nightly or 3.1.11. Also, consider that this type is tested extensively in the unit and regression tests.

If I had to guess, I think you've got an older C/J version laying around in your CLASSPATH (jre/lib/ext, some classpath you've configured in OOo?).
[3 Nov 2005 21:51] Syvalta Sa
JDBC driver is the only entry in OO classpath, and if I remove it, the result is "could not load driver" error. So wrong driver shouldn't be the problem. I worked around the problem just by changing the decimals to floats.

I'm running the server in localhost in windows, so I can't even trace the tcp connection. Is it possible to enable some debub printing in the driver? OO doesn't show even the stack trace.
[7 Nov 2005 13:41] pan li
I have the same problem with OOo2. 
But I try the same sql with other JDBC client and it is just OK.
Maybe it is OOo's problem.
[8 Nov 2005 7:30] Ronald Bradford
I'm experiencing this problem using JDBC via a JSP/JSTL Web Application under linux.

javax.servlet.ServletException: javax.servlet.jsp.JspException: 
SELECT * FROM Batch WHERE batchId = ?: Unknown type '246 in column 9 of 27 in binary-encoded result set.

This query works find by mysql client interface.

Versions in use.
MySQL 5.0.15-standard
Connector/J 3.1.7
[9 Nov 2005 10:19] Vasily Kishkin
Probably I need a definition of table. I tried to reproduce the bug but I was not able.
[9 Nov 2005 10:28] Mark Matthews
This bug was fixed after 3.1.7, so for the user seeing this bug w/ 3.1.7, you should upgrade.
[14 Nov 2005 16:42] Jiri Papez
I'm getting this error on the second column of
   select xxx, Count(yyy) from...
(or on counted columns)
column is created as   `yyy` int(11) default NULL,

Versions: MySQL5.0 final

mysql-connector-java-3.0.14-production.zip WORKS
mysql-connector-java-3.1.11.zip ERROR
mysql-connector-java-3.2.0-alpha.zip ERROR
mysql-connector-java-5.0-nightly-20051114.zip ERROR
[15 Nov 2005 13:50] Jiri Papez
it seems, that all problems comes only when using ResultSet.CONCUR_UPDATABLE !(?)
[24 Nov 2005 13:05] Per Newgro
Have the same problem with the following code:

<code>
String sql = "SELECT * FROM myTable WHERE "
                + "businessId = ? "
                + "AND fromDate <= ? "
                + "AND toDate >= ?";
PreparedStatement pst = mConnection.prepareStatement(sql, resultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
pst.setObject(1, new Integer(1));
pst.setDate(2, new java.sql.Date(System.currentMillis()));
pst.setDate(3, new java.sql.Date(System.currentMillis()));
ResultSet rs = pst.executeQuery(); --> Throws the exception
</code>

Caused by: java.sql.SQLException: Unknown type '246 in column 5 of 14 in binary-encoded result set.
	at com.mysql.jdbc.MysqlIO.unpackNativeEncodedColumn(MysqlIO.java:4148)
	at com.mysql.jdbc.MysqlIO.unpackBinaryResultSetRow(MysqlIO.java:3712)
	at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1320)
	at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:2259)
	at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:423)
	at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:1960)
	at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1385)
	at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1138)
	at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:675)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1027)
	at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:211)
...

My table is defined as
##
## Table structure for table `myTable`
##
DROP TABLE IF EXISTS `myTable`;
CREATE TABLE `myTable` (
  `businessId` int(11) default NULL,
  `fromDate` date NOT NULL default '0000-01-01',
  `receiptId` int(11) NOT NULL,
  `state` tinyint default NULL,
  `purchaseConditionId` int(11) NOT NULL,
  `amount` decimal(11,4) NOT NULL,
  `netPrice` decimal(11,4) NOT NULL,
  `currency` char(3) NOT NULL,
  `vat` decimal(11,4) NOT NULL,
  `suppliedAmount` decimal(11,4) NOT NULL,
  `returnedAmount` decimal(11,4) NOT NULL,
  `toDate` date NOT NULL default '9999-12-31',
  `userId` varchar(100) default NULL,
  `changeDate` bigint(20) NOT NULL,
  PRIMARY KEY (`fromDate`, `businessId`)
) TYPE=InnoDB;

mysql status
mysql  Ver 14.7 Distrib 4.1.13, for suse-linux (i686) using readline 5.0
Connection id:          161
Current database:
Current user:           rene.d@localhost
SSL:                    Not in use
Current pager:          less
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.15-standard-log
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /tmp/mysql.sock
Uptime:                 5 hours 47 min 47 sec

cat /proc/version
Linux version 2.6.13-15-default (geeko@buildhost) (gcc version 4.0.2 20050901 (prerelease) (SUSE Linux)) #1 Tue Sep 13 14:56:15 UTC 2005

manifest of mysql-connector-java-3.1.11-bin.jar
Manifest-Version: 1.0
Ant-Version: Apache Ant 1.6.2
Created-By: 1.4.2-b28 (Sun Microsystems Inc.)
Built-By: jdbcbuild
Name: common
Specification-Title: JDBC
Specification-Version: 3.0
Specification-Vendor: Sun Microsystems Inc.
Implementation-Title: MySQL Connector/J
Implementation-Version: 3.1.11
Implementation-Vendor: MySQL AB

java version
java version "1.5.0_05"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_05-b05)
Java HotSpot(TM) Client VM (build 1.5.0_05-b05, mixed mode, sharing)

Hope the informations can help to reproduce the problem.
[10 Dec 2005 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[11 Dec 2005 9:20] Per Newgro
Have added feedback. Please process this bug further. It still occurs.
I can not upgrade to MySQL 5.0 until this bug is solved.
[16 Dec 2005 9:20] Aleksey Kishkin
reopened.
[16 Dec 2005 21:17] Vasily Kishkin
I was able to reproduce the bug:

Connected to 5.0.16
START
E
Time: 0,407
There was 1 error:
1) testPound14609(Bug14609)java.sql.SQLException: Unknown type '246 in column 5 of 14 in binary-encoded result set.
        at com.mysql.jdbc.MysqlIO.unpackNativeEncodedColumn(MysqlIO.java:4061)
        at com.mysql.jdbc.MysqlIO.unpackBinaryResultSetRow(MysqlIO.java:3627)
        at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1282)
        at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:2198)
        at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:413)
        at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:1899)
        at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1347)
        at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1412)
        at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:952)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1722)
        at Bug14609.testPound14609(Bug14609.java:34)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at Bug14609.main(Bug14609.java:40)

FAILURES!!!
Tests run: 1,  Failures: 0,  Errors: 1

My test case is attached.
[16 Dec 2005 21:18] Vasily Kishkin
Test case

Attachment: Bug14609.java (text/java), 1.17 KiB.

[17 Dec 2005 10:33] Till Kahle
The bug seems to occur when ConnectorJ selects from a "decimal" column, using a PreparedStatement. 

So I have created a very simple test program (attached, no dependencies, please change login information in order to test.)
I have tested with decimal(3,0), decimal(12,0), all the same.
This does not occur when using mysql server 4.1.x, so it seems to be a new type identifier (246) used in mysql server 5.0.x for decimal columns in prepared(!) result sets.

I have tried with these versions (among other ConnectorJ versions):
Database driver mysql-connector-java-3.2.0-alpha ( $Date: 2004/12/13 22:22:04 $, $Revision: 1.27.4.35.2.9 $ ) with database 5.0.15-nt

This is the output:

Database driver mysql-connector-java-3.2.0-alpha ( $Date: 2004/12/13 22:22:04 $, $Revision: 1.27.4.35.2.9 $ ) with database 5.0.15-nt
still alive
java.sql.SQLException: Unknown type '246 in column 0 of 1 in binary-encoded result set.
	at com.mysql.jdbc.MysqlIO.unpackNativeEncodedColumn(MysqlIO.java:4149)
	at com.mysql.jdbc.MysqlIO.unpackBinaryResultSetRow(MysqlIO.java:3672)
	at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1335)
	at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:2253)
	at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:434)
	at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:1952)
	at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1400)
	at com.mysql.jdbc.Connection.serverExecute(Connection.java:4793)
	at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1251)
	at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:993)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1657)
	at weikatec.test.mysql5bug.LandmarkPS.<init>(LandmarkPS.java:39)
	at weikatec.test.mysql5bug.LandmarkPS.main(LandmarkPS.java:52)
[17 Dec 2005 10:35] Till Kahle
Sorry, can't attach a file, so here comes the plain java code:

-----------------

package weikatec.test.mysql5bug;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

/*
 * Created on 17.12.2005
 */

public class LandmarkPS {

    public LandmarkPS() {
        try {
            // bug occurs on: Database driver mysql-connector-java-3.2.0-alpha ( $Date: 2004/12/13 22:22:04 $, $Revision: 1.27.4.35.2.9 $ ) with database 5.0.15-nt
            String url = "jdbc:mysql://localhost:3306/test";
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection( url, "test", "test" );
            System.out.println( "Database driver " + con.getMetaData().getDriverVersion() +
                " with database " + con.getMetaData().getDatabaseProductVersion() );
            Statement stmt = con.createStatement();
            stmt.execute( "drop table if exists TillsDummy" );
            stmt.execute( "create table TillsDummy( id decimal(12,0) not null primary key )" );
            stmt.execute( "insert into TillsDummy (id) values(1)" );

            ResultSet rs = con.createStatement().executeQuery( "select id from TillsDummy" );
            rs.next();
            rs.close();
            System.out.println( "still alive" ); // this works until here
            
            PreparedStatement ps = con.prepareStatement(
                    "select id from TillsDummy " +
                    "where id=" + "?"
                );
            ps.setInt( 1, 1 );
            ResultSet rs2 = ps.executeQuery(); // this is where the exception occurs
            rs2.next();
            rs2.close();
            con.close();
            System.out.println( "survived" );
        } catch( Exception e ) {
            e.printStackTrace();
        }
    }

    /**
     */
    public static void main(String[] args) {
        new LandmarkPS();
    }

}
[19 Dec 2005 16:10] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/260
[19 Dec 2005 16:14] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/261
[29 Jan 2006 9:39] Per Newgro
I dont know how to apply the patch. I thought that the patch wiould be applied to a major version. But the BUG is present in 5.0.18 and not in list of changes for 5.0.19.

Is it possible to include the patch in major version?
[30 Jan 2006 15:14] Mark Matthews
The patch is to the _JDBC_ driver, not the server.

You can download a nightly snapshot to get the patched driver if you can't wait for a release:

http://downloads.mysql.com/snapshots.php#connector-j
[10 Mar 2006 20:24] Mark Matthews
Fix will be available in 5.0.1 and 3.1.13. You can try a nightly snapshot from http://downloads.mysql.com/snapshots.php#connector-j if you want to test the fix before it's officially released. Thanks for the bug report.
[27 Mar 2006 22:46] Radim Burget
###Error###
java.sql.SQLException: Unknown type '246 in column 1 of 6 in binary-encoded result set.
	at com.mysql.jdbc.MysqlIO.extractNativeEncodedColumn(MysqlIO.java:3710) 

###SQL query###
        Query q = getSession().createQuery(
                "Select u, sum(uz.pocetPolozek) as pocet from UmisteniZbozi uz, UmisteniPult u "
                        + "where uz.zbozi.id=:zbozi "
                        + "and uz.umisteni.id = u.id "
                        + "and uz.pocetPolozek > 0 GROUP BY u.id");
        q.setLong("zbozi", z.getId().longValue()); 

###Versions###
mysql v5.0.18
mysql-connector-java-5.0-nightly-20060327-bin.jar 

Have tryied also with 
mysql-connector-java-3.2-nightly-20060327-bin.jar 
mysql-connector-java-3.1.7-bin.jar 

###Same QUERY has worked with###
older MySQL (not sure - guess 4.?.?)
mysql-connector-java-3.1.7-bin.jar 

In MYSQL-QUERY-BROWSER as well
[27 Mar 2006 22:56] Mark Matthews
Looks like you're not loading the version you think you are. If you follow the stack trace's line numbers and look at the sources for the nightly build you report this against, you don't even end up in the same method.
[15 Jun 2006 7:00] Suraj Patnaik
I am having a different problem like this. I have FC5 supporting x86_64 OS with MySQL 5.0.18 with connector/J 3.1.13.  When I am running Reliable File Transfer I am getting a issue like

faultDetail:
        {http://xml.apache.org/axis/}stackTrace:java.rmi.RemoteException: Error in start; nested exception is:
        org.globus.transfer.reliable.service.database.RftDBException: Error retrieving data from database for id:
 &quot;24&quot; [Caused by: Unknown type '16 in column 7 of 20 in binary-encoded result set.]

In this case, how one can handle the situation. I think there may be some issue with the Connector.
[15 Jun 2006 19:36] Mark Matthews
Filed latest issue as a new bug, BUG#20485.