Bug #10166 Signed byte values cause data to be padded when converting <> BLOB/Bytes
Submitted: 26 Apr 2005 2:54 Modified: 13 Jul 2006 3:24
Reporter: Stuart Hudson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.9, Connector/J 3.1.8 OS:Windows (WINDOWS XP SP2)
Assigned to: Alexey Botchkov CPU Architecture:Any

[26 Apr 2005 2:54] Stuart Hudson
Description:
We are storing geometry data (example is based on a MULTIPOINT) in the DB through Connector/J, specifying the data as binary data (well-known-binary).

Storage works correctly, but accessing the data through Connector/J as binary data does not. The -ve byte values introduce padding into the binary data, corrupting it.

The exeample below (based on the How-To-Repeat code) shows the padding.

Utilising a BLOB as an intermediate data-type has 0 effect.

Data as stored in DB:

mysql> use scratch;
Database changed
mysql> SELECT ID, asText(DATA) FROM geom_test;
+----+---------------------------------+
| ID | asText(DATA)                    |
+----+---------------------------------+
| 1  | MULTIPOINT(75 75,200 200,50 50) |
+----+---------------------------------+
1 row in set (0.01 sec)

Console Output:

WRITTEN BYTES  = 10 40 00 00 00 30 00 00 00 10 10 00 00 00 00 00 00 00 00 0F 20 00 00 00 00 00 00 0F 20 00 10 10 00 00 00 00 00 00 00 00 00 90 00 00 00 00 00 00 00 90 00 10 10 00 00 00 00 00 00 00 00 00 90 00 00 00 00 00 00 00 90 00
RECEIVED BYTES = 10 40 00 00 00 30 00 00 00 10 10 00 00 00 00 00 00 00 00 FF FF DF 20 00 00 00 00 00 00 FF FF DF 20 00 10 10 00 00 00 00 00 00 00 00 00 90 00 00 00 00 00 00 00 90 00 10 10 00 00 00 00 00 00 00 00 00 90 00 00 00 00 00 00 00 90 00
Expected Length: 72 Actual Length: 76
Mismatch expected: -64 actual: -17
Mismatch expected: 82 actual: -65
Mismatch expected: 64 actual: -67
Mismatch expected: 0 actual: 82
Mismatch expected: 0 actual: 64
Mismatch expected: -64 actual: 0
Mismatch expected: 82 actual: 0
Mismatch expected: 64 actual: -17
Mismatch expected: 1 actual: -65
Mismatch expected: 1 actual: -67
Mismatch expected: 0 actual: 82
Mismatch expected: 0 actual: 64
Mismatch expected: 0 actual: 1
Mismatch expected: 0 actual: 1
Mismatch expected: 105 actual: 0
Mismatch expected: 64 actual: 0
Mismatch expected: 0 actual: 105
Mismatch expected: 0 actual: 64
Mismatch expected: 105 actual: 0
Mismatch expected: 64 actual: 0
Mismatch expected: 1 actual: 0
Mismatch expected: 1 actual: 0
Mismatch expected: 0 actual: 105
Mismatch expected: 0 actual: 64
Mismatch expected: 0 actual: 1
Mismatch expected: 0 actual: 1
Mismatch expected: 73 actual: 0
Mismatch expected: 64 actual: 0
Mismatch expected: 0 actual: 73
Mismatch expected: 0 actual: 64
Mismatch expected: 73 actual: 0
Mismatch expected: 64 actual: 0

How to repeat:
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * Demonstrates MySQL bug
 * <p>
 * Geometry coercion between WKB & Byte Data where WKB includes -ve byte values
 * adds padding into Byte Data.
 * <p>
 * 1. Creates a table with a MultiPoint field <br>
 * 2. Create a record generating MultiPoint from WKB - Byte Data <br>
 * 3. Select the record from the table as Byte Data <br>
 * 4. Contrast & Compare
 * 
 * @author Stuart Hudson
 */
public class MySQLBlobBug2 {
	public static void main(String[] args) throws Exception {

		String CREATE_TABLE = "CREATE TABLE GEOM_TEST (ID VARCHAR(10) PRIMARY KEY, DATA MULTIPOINT) ENGINE=MyISAM";
		String SELECT = "SELECT ID, asBinary(DATA) AS 'BYTES' FROM GEOM_TEST WHERE ID = ?";
		String INSERT = "INSERT INTO GEOM_TEST (ID, DATA) VALUES (?, GeometryFromWKB(?))";

		String URL = "jdbc:mysql://localhost/scratch";
		String USER = "root";

		// Set this value in order to test.
		String PASS = "ak51yjo";
		String DRIVER = "com.mysql.jdbc.Driver";
		String ID = "1";

		// MULTIPOINT(75 75,200 200,50 50)
		byte[] TEST_DATA = new byte[]{1, 4, 0, 0, 0, 3, 0, 0, 0, 1, 1, 0, 0, 0,
				0, 0, 0, 0, 0, -64, 82, 64, 0, 0, 0, 0, 0, -64, 82, 64, 1, 1,
				0, 0, 0, 0, 0, 0, 0, 0, 0, 105, 64, 0, 0, 0, 0, 0, 0, 105, 64,
				1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 73, 64, 0, 0, 0, 0, 0, 0, 73,
				64};

		Connection con;
		Statement stmt;
		PreparedStatement ps;

		DriverManager.registerDriver((Driver) Class.forName(DRIVER)
				.newInstance());

		// 1. Creates a table with a MultiPoint field
		con = DriverManager.getConnection(URL, USER, PASS);
		stmt = con.createStatement();
		stmt.execute(CREATE_TABLE);
		stmt.close();
		con.close();

		// 2. Create a record generating MultiPoint from WKB - Byte Data
		con = DriverManager.getConnection(URL, USER, PASS);
		ps = con.prepareStatement(INSERT);
		ps.setString(1, ID);
		ps.setBytes(2, TEST_DATA);
		System.out.println("WRITTEN BYTES  = "
				+ MySQLBlobBug2.toHex(TEST_DATA, 0, TEST_DATA.length));
		ps.execute();
		ps.close();
		con.close();

		// 3. Select the record from the table as Byte data
		con = DriverManager.getConnection(URL, USER, PASS);
		ps = con.prepareStatement(SELECT);
		ps.setString(1, ID);
		ResultSet rs = ps.executeQuery();

		byte[] result = null;
		if (rs.next()) {
			result = rs.getBytes("BYTES");
			System.out.println("RECEIVED BYTES = "
					+ MySQLBlobBug2.toHex(result, 0, result.length));
		}

		rs.close();
		ps.close();
		con.close();

		// 4. Contrast & Compare
		System.out.println("Expected Length: " + TEST_DATA.length
				+ " Actual Length: " + result.length);

		if (result != null) {
			for (int i = 0; i < result.length && i < TEST_DATA.length; i++) {
				if (result[i] != TEST_DATA[i]) {
					System.out.println("Mismatch expected: " + TEST_DATA[i]
							+ " actual: " + result[i]);
				}
			}

		} else {
			throw new Exception("This shouldn't happen");
		}
	}

	public static String toHex(byte[] bytes, int pos, int length) {
		StringBuffer buf = new StringBuffer();
		String sep = "";
		for (int idx = pos; idx < pos + length; idx++) {
			buf.append(sep);
			for (int s = 0; s < 16; s += 8) {
				switch ((bytes[idx] >>> s) & 0xF) {
					case 0 :
						buf.append('0');
						break;
					case 1 :
						buf.append('1');
						break;
					case 2 :
						buf.append('2');
						break;
					case 3 :
						buf.append('3');
						break;
					case 4 :
						buf.append('4');
						break;
					case 5 :
						buf.append('5');
						break;
					case 6 :
						buf.append('6');
						break;
					case 7 :
						buf.append('7');
						break;
					case 8 :
						buf.append('8');
						break;
					case 9 :
						buf.append('9');
						break;
					case 10 :
						buf.append('A');
						break;
					case 11 :
						buf.append('B');
						break;
					case 12 :
						buf.append('C');
						break;
					case 13 :
						buf.append('D');
						break;
					case 14 :
						buf.append('E');
						break;
					case 15 :
						buf.append('F');
						break;
				}
			}
			sep = " ";
		}
		return (buf.toString());
	}

}
[26 Apr 2005 3:58] Stuart Hudson
Appears to be similar to bug discoverred with Connector/ODBC (Bug #9781)
[26 Apr 2005 5:11] Stuart Hudson
Re-Tested against 4.1.11 (windows)

Bytes are no longer padded (hurrah !) but bytes are still no correct (boo !!) ..

Console output (from test-case supplied) :

WRITTEN BYTES  = 10 40 00 00 00 30 00 00 00 10 10 00 00 00 00 00 00 00 00 0F 20 00 00 00 00 00 00 0F 20 00 10 10 00 00 00 00 00 00 00 00 00 90 00 00 00 00 00 00 00 90 00 10 10 00 00 00 00 00 00 00 00 00 90 00 00 00 00 00 00 00 90 00
RECEIVED BYTES = 10 40 00 00 00 30 00 00 00 10 10 00 00 00 00 00 00 00 00 F0 20 00 00 00 00 00 00 F0 20 00 10 10 00 00 00 00 00 00 00 00 00 90 00 00 00 00 00 00 00 90 00 10 10 00 00 00 00 00 00 00 00 00 90 00 00 00 00 00 00 00 90 00
Expected Length: 72 Actual Length: 72
Mismatch expected: -64 actual: 63
Mismatch expected: -64 actual: 63
[26 Apr 2005 6:31] Stuart Hudson
FWIW : Had a look at the 4.1.11 src (urg ... long time since I've had to delve into C++) ...

The "AsBinary" lex is in lex.h to poke create_func_as_wkb (in item_create.cpp) which instantiates an Item_func_as_wkb (from item_geofunc.h/cpp).  This is a subclass of Item_str_func & therefore has a STRING_RESULT.  

Consequently the data is returned as a string buffer.

Somewhere (??) an Item_buff is created (through the new_item_buff func in item_buff.cpp) for the item representing the "AsBinary(blah)" call which does the returns an Item_str_buff.

I guess if it returned an Item_field_buff (which is there for BLOBS) it might be better,but I'd leave that up to someone who understands what is going on ..good luck whoever !
[26 Apr 2005 6:34] Stuart Hudson
I should mention that I'm using a work-around in my code until an official fix is available (if that means an upgrade to 5.x then the workaround may stay for a while).

Basically I'm calling the protected method on the ResultSet in java to avoid the conversions i.e. 

	protected byte[] getBytes(ResultSet rs, String name, boolean noconversion) {
		try {
			Method m = rs.getClass().getDeclaredMethod("getBytes",
					new Class[]{int.class, boolean.class});
			m.setAccessible(true);
			return (byte[]) m.invoke(rs, new Object[]{
					new Integer(rs.findColumn(name)), Boolean.TRUE});
		} catch (Exception ex) {
			throw new RuntimeException(ex);
		}
	}
[27 Apr 2005 17:38] Mark Matthews
Are you stating that you believe this is actually a server bug then?
[30 Apr 2005 14:16] Stuart Hudson
I guess the answer would be yes :)

Also, whe I look at the server code I get the overwhelming feeling that the addition of the spatial data-types & functions was a bit of a 'hack' & that introducing a new return time for Item_func (i.e. something other that STRING_RESULT, INTEGER_RESULT, REAL_RESULT, ROW_RESULT) was outside the scope of the original implementation.

Anyway ... next question ... do I submit a new bug, or can I change this to a "server bug" ?

And BTW - yes I have never submitted a bug into the MySQL system before.
[2 May 2005 4:00] Stuart Hudson
The problem is still there, but there is also a typo (cut & paste) in the "how to repeat code" :

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

/**
 * Demonstrates MySQL bug
 * <p>
 * Geometry coercion between WKB & Byte Data where WKB includes -ve byte values
 * adds padding into Byte Data.
 * <p>
 * 1. Creates a table with a MultiPoint field <br>
 * 2. Create a record generating MultiPoint from WKB - Byte Data <br>
 * 3. Select the record from the table as Byte Data <br>
 * 4. Contrast & Compare
 * 
 * @author Stuart Hudson
 */
public class MySQLBlobBug2 {
	public static void main(String[] args) throws Exception {

		String CREATE_TABLE = "CREATE TABLE GEOM_TEST (ID VARCHAR(10) PRIMARY KEY,
DATA MULTIPOINT) ENGINE=MyISAM";
		String SELECT = "SELECT ID, asBinary(DATA) AS 'BYTES' FROM GEOM_TEST WHERE ID
= ?";
		String INSERT = "INSERT INTO GEOM_TEST (ID, DATA) VALUES (?,
GeometryFromWKB(?))";

		String URL = "jdbc:mysql://localhost/scratch";
		String USER = "root";

		// Set this value in order to test.
		String PASS = "ak51yjo";
		String DRIVER = "com.mysql.jdbc.Driver";
		String ID = "1";

		// MULTIPOINT(75 75,200 200,50 50)
		byte[] TEST_DATA = new byte[]{1, 4, 0, 0, 0, 3, 0, 0, 0, 1, 1, 0, 0, 0,
				0, 0, 0, 0, 0, -64, 82, 64, 0, 0, 0, 0, 0, -64, 82, 64, 1, 1,
				0, 0, 0, 0, 0, 0, 0, 0, 0, 105, 64, 0, 0, 0, 0, 0, 0, 105, 64,
				1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 73, 64, 0, 0, 0, 0, 0, 0, 73,
				64};

		Connection con;
		Statement stmt;
		PreparedStatement ps;

		DriverManager.registerDriver((Driver) Class.forName(DRIVER)
				.newInstance());

		// 1. Creates a table with a MultiPoint field
		con = DriverManager.getConnection(URL, USER, PASS);
		stmt = con.createStatement();
		stmt.execute(CREATE_TABLE);
		stmt.close();
		con.close();

		// 2. Create a record generating MultiPoint from WKB - Byte Data
		con = DriverManager.getConnection(URL, USER, PASS);
		ps = con.prepareStatement(INSERT);
		ps.setString(1, ID);
		ps.setBytes(2, TEST_DATA);
		System.out.println("WRITTEN BYTES  = "
				+ MySQLBlobBug2.toHex(TEST_DATA, 0, TEST_DATA.length));
		ps.execute();
		ps.close();
		con.close();

		// 3. Select the record from the table as Byte data
		con = DriverManager.getConnection(URL, USER, PASS);
		ps = con.prepareStatement(SELECT);
		ps.setString(1, ID);
		ResultSet rs = ps.executeQuery();

		byte[] result = null;
		if (rs.next()) {
			result = rs.getBytes("BYTES");
			System.out.println("RECEIVED BYTES = "
					+ MySQLBlobBug2.toHex(result, 0, result.length));
		}

		rs.close();
		ps.close();
		con.close();

		// 4. Contrast & Compare
		System.out.println("Expected Length: " + TEST_DATA.length
				+ " Actual Length: " + result.length);

		if (result != null) {
			for (int i = 0; i < result.length && i < TEST_DATA.length; i++) {
				if (result[i] != TEST_DATA[i]) {
					System.out.println("Mismatch expected: " + TEST_DATA[i]
							+ " actual: " + result[i]);
				}
			}

		} else {
			throw new Exception("This shouldn't happen");
		}
	}

	public static String toHex(byte[] bytes, int pos, int length) {
		StringBuffer buf = new StringBuffer();
		String sep = "";
		for (int idx = pos; idx < pos + length; idx++) {
			buf.append(sep);
			for (int s = 0; s < 8; s += 4) {
				switch ((bytes[idx] >>> s) & 0xF) {
					case 0 :
						buf.append('0');
						break;
					case 1 :
						buf.append('1');
						break;
					case 2 :
						buf.append('2');
						break;
					case 3 :
						buf.append('3');
						break;
					case 4 :
						buf.append('4');
						break;
					case 5 :
						buf.append('5');
						break;
					case 6 :
						buf.append('6');
						break;
					case 7 :
						buf.append('7');
						break;
					case 8 :
						buf.append('8');
						break;
					case 9 :
						buf.append('9');
						break;
					case 10 :
						buf.append('A');
						break;
					case 11 :
						buf.append('B');
						break;
					case 12 :
						buf.append('C');
						break;
					case 13 :
						buf.append('D');
						break;
					case 14 :
						buf.append('E');
						break;
					case 15 :
						buf.append('F');
						break;
				}
			}
			sep = " ";
		}
		return (buf.toString());
	}

}
[24 May 2005 9:48] Vasily Kishkin
Tested on Win 2000 Sp4, MySQL server 4.1.11
[24 May 2005 9:49] Vasily Kishkin
Test case

Attachment: test.java (text/plain), 4.19 KiB.

[22 Jan 2006 3:46] Stuart Hudson
Happy New Year ...
Any progress on this bug and/or #9781
[7 Apr 2006 21:20] P Eger
I can confirm that this bug still exists (windows build, mysql 5.0.19-nt, connecing using latest 3.1.12 JDBC driver), any progress?

Also, anybody have a workaround? The Well Known Text format is imprecise (float <-> text conversion) and so is generally not sufficient for our needs. IE how can we set/get the spatial data precisely without a properly functioning binary format?
[27 Apr 2006 3:27] Dan Greening
Yes, this remains a problem.  There are many reasons why it should be classified as a bug.  In particular, you cannot create a MySQL object that contains a spatial element with EJB3 entity beans, unless this is fixed.
[14 Jun 2006 17:05] P Eger
Any progress as yet? This bug is a show-stopper for us in multiple projects... Please stop mangling my binary data!
[14 Jun 2006 17:27] Mark Matthews
Can you do without server-side prepared statements for now as a workaround? If you add "useServerPrepStmts=false" to your JDBC URL configuration properties, it appears the metadata is not causing an issue for the JDBC driver when it's emulating prepared statements, rather than relying on the server to do the work.
[23 Jun 2006 12:09] Alexander Barkov
The patch  "bk commit - 4.1 tree (holyfoot:1.2508) BUG#10166"
looks ok to push.
[27 Jun 2006 9:58] Alexey Botchkov
Pushed in 4.1.21
[3 Jul 2006 20:13] Dan Greening
Still want it in MySQL 5.0 (where spatial seems to work for me)
[4 Jul 2006 7:42] Alexey Botchkov
Should be up-pushed into 5.0 dev-tree already.
[6 Jul 2006 18:21] Paul DuBois
Documented in 4.1.21 changelog.

For spatial data types, the server formerly returned these as
VARSTRING values with a binary collation. Now the server returns
spatial values as BLOB values.
[8 Jul 2006 17:45] Ingo Strüwing
Pushed to 5.1.12.
[13 Jul 2006 3:24] Paul DuBois
5.0.x fix went to 5.0.25.

Noted in 5.0.25, 5.1.12 changelogs.