MySQL Bugs: #22425: Prepared Statements fail to correctly bind parameters

Bug #22425	Prepared Statements fail to correctly bind parameters
Submitted:	17 Sep 2006 14:30	Modified:	22 Jan 2007 17:10
Reporter:	Christopher Schultz	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	4.1.21	OS:	Linux (Linux 2.6.14 and 2.6.17)
Assigned to:		CPU Architecture:	Any

Description:
I recently upgraded to MySQL 4.1.21 (from 4.1.20, I think). I have been running Connector/J 3.1.13 for some time now.

Today, I started getting errors logging into my web application. This error appears in the log file:

java.sql.SQLException: Data truncation: Data truncated for column 'ip_address' at row 1

Before upgrading to Connector/J 3.1 from 3.0.x, I read the ChangeLog and was aware that data truncation would now throw a SQLException as per the JDBC spec. But it doesn't appear that the data is being truncated.

Here is the query being executed (using dumpQueriesOnException to be sure that I'm not wrong about the query):

com.mysql.jdbc.ServerPreparedStatement[12] - INSERT INTO user_login_failure (user_id, username, ip_address, reason, ts) VALUES (null,'chris','192.168.1.50','no such user','2006-09-17 10:07:49')

Here is the table definition:

CREATE TABLE `user_login_failure` (
  `username` varchar(255) NOT NULL default '',
  `user_id` int(11) default NULL,
  `ip_address` varchar(15) NOT NULL default '',
  `reason` varchar(255) NOT NULL default '',
  `ts` datetime NOT NULL default '0000-00-00 00:00:00',
  KEY `idx_login_failure__user_id` (`user_id`),
  CONSTRAINT `fk_user_login_failure__user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I tried issuing that same query directly to the mysql command-line interface, and it executed successfully with no warnings or errors.

Curiously, the reason that this record is even being inserted in the first place is because it cannot find the user that I use all the time: 'chris'.

I think that there might be a character conversion problem somewhere... like maybe putting everything into UTF-16 or something. That would certainly double the length of my strings, and cause string matching to fail (i.e. 'chris' in UTF-8 != 'chris' in UTF-16).

Here are the various charset settings that I have:

/etc/mysql/my.cnf:
[mysqld]
character-set-server            = utf8
default-character-set           = utf8

JDBC URL:
jdbc:mysql://${database.host}/${diagnosis.database.name}?characterEncoding=utf8&amp;dumpQueriesOnException=true

The table itself is configured to use UTF8 as the default character set, too.

So, it looks like everything is matching up.

I even checked the compile settings of MySQL (I build MySQL from source on Gentoo linux) and it looks like the option to "use latin1 instead of UTF8" is turned /off/, so it should be using UTF8.

Just for the heck of it, I tried to set the JDBC driver to use latin1, and that did not appear to work.

I'm pretty much out of ideas. :(

How to repeat:
I can reliably repeat this on my setup (happens every time I try to login).

I would imagine that writing a short Java program to issue the query mentioned above would result in the same error.

Here's a small test that I wrote to verify that I wasn't crazy. It looks like the problem is prepared statement parameter replacements, 'cause strings used with java.sql.Statement objects seem to work.

I used my 'test' database (which has a 'db' character set of latin1, as opposed to my real database, which says that it is 'utf8').

=========
SQL SETUP
=========
CREATE TABLE `user` (
  `username` varchar(100) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8
;

insert into user values ('foo');
insert into user values ('bar');
insert into user values ('baz');
insert into user values ('chris');

=========
Java Code
=========
import java.sql.*;

public class JDBCTest
{
    public static void main(String[] args)
	throws Exception
    {
	Class.forName("com.mysql.jdbc.Driver");

	Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test?characterEncoding=utf8&dumpQueriesOnException=true", "user", "password");

	System.out.println("===========================");
	System.out.println("Using Statement");
	System.out.println("===========================");
	Statement s = conn.createStatement();

	ResultSet rs = s.executeQuery("SELECT * from user WHERE username='chris'");

	dump(rs);

	rs.close(); rs = null;
	s.close(); s = null;

	System.out.println("===========================");
	System.out.println("Using PreparedStatement");
	System.out.println("===========================");
	PreparedStatement ps = conn.prepareStatement("SELECT * from user WHERE username=?");

	ps.setString(1, "chris");

	rs = ps.executeQuery();

	dump(rs);
    }

    private static void dump(ResultSet rs)
	throws SQLException
    {
	ResultSetMetaData rsmd = rs.getMetaData();

	for(int i=0; i<rsmd.getColumnCount(); ++i)
	{
	    System.out.print(rsmd.getColumnName(i + 1) + "\t");
	}
	System.out.println();

	if(rs.next())
	{
	    do
	    {
		for(int i=0; i<rsmd.getColumnCount(); ++i)
		{
		    String value = rs.getString(1+i);
		    if(rs.wasNull())
			value = "NULL";
		    
		    System.out.print(value + "\t");
		}
	    
		System.out.println();
	    } while(rs.next());
	}
	else
	{
	    System.out.println("NO RESULTS");
	}
    }
}

Okay, the problem appears to be with server-side prepared statements.

Adding "useServerPrepStmts=false" fixes my problem.

Of course, this is just a workaround; there's either a bug or misconfiguration somewhere.

Let me know if I can help out with any kind of testing or giving you guys more information to help solve this problem.

Hello Christopher,

We're experiencing a similar issue (I've raised bug #22290). The workaround we've opted for is to use the JDBC connection parameter 'jdbcCompliantTruncation=false'. This stops the warning from being turned into an exception by the JDBC driver.

Ben

Although the error I was getting was "data truncation error", I think the problem has more to do with the server-side prepared statements.

For example, I was getting an error inserting "192.168.1.50" into a VARCHAR(15) field, which doesn't make a whole lot of sense. It looks like a character set issue, even though all the character sets of the client, server, database, and table were all the same.

Hi Christopher and thanks for your problem report. This is not a bug.
If you turn on general log in your MySQL server you'll notice STRICT_TRANS_TABLES is being set. We introduced 'STRICT_TRANS_TABLES' server mode because it's much more efficient than checking warnings after every statement for truncations. You can still use jdbcCompliantTruncation=false, or check other connector/J options (like ServerSidePreparedStatement...).

I'm afraid I don't understand. I was getting an error inserting "192.168.1.50" into a VARCHAR(15) field, which doesn't make a whole lot of sense (12 chars is less than 15, so why get a truncation error?).

Also, by turning off server-side prepared statements, I was able to work around my problem.

If you run my test program, you'll notice that the SELECT query DOES NOT WORK when using server-side prepared statements. All it does is do a SELECT ... WHERE col=?.

This is definitely a bug as far as I'm concerned. Perhaps I should have changed the description to "server side prepared statements do not work", because that is what the bug turns out to be. The data truncation turned out to be a side-effect of the problem.

Please re-examine.

The data truncation exception was a symptom of the real problem: server site prepared statements do not work properly.

Please re-examine.

Hi Christopher.
I will attach my test case and my.ini you can find in BUG#22290 (minus strict_trans_tables ofcourse).
I was unable to repeat your problem on:
  MySQL server 4.1.22BK and 5.0.24BK on WinXP Pro SP2 localhost
  JDK 1.5.07
  Latest sources of c/J 3.1 from SVN repo
  &jdbcCompliantTruncation=true&useServerPrepStmts=true&characterEncoding=utf8

Test results (I also tried varying statements, this is just last text):
Connected to 5.0.24-log
Query to be executed: com.mysql.jdbc.ServerPreparedStatement[1] - INSERT INTO testbug22425 VALUES ('tonci')
Query to be executed: com.mysql.jdbc.ServerPreparedStatement[2] - INSERT INTO testbug22425 VALUES ('tonci 10.192.192.99')
Query to be executed: com.mysql.jdbc.ServerPreparedStatement[3] - SELECT * from testbug22425 WHERE username LIKE 'tonci%'
Dumping data...
username	
tonci	
tonci 10.192.192.99	

Time: 0,546

OK (1 test)

Connected to 4.1.22-log
Query to be executed: com.mysql.jdbc.ServerPreparedStatement[1] - INSERT INTO testbug22425 VALUES ('tonci')
Query to be executed: com.mysql.jdbc.ServerPreparedStatement[2] - INSERT INTO testbug22425 VALUES ('tonci 10.192.192.99')
Query to be executed: com.mysql.jdbc.ServerPreparedStatement[3] - SELECT * from testbug22425 WHERE username LIKE 'tonci%'
Dumping data...
username	
tonci	
tonci 10.192.192.99	

Time: 0,625

OK (1 test)

Could you please upgrade to latest nightly snapshot from http://downloads.mysql.com/snapshots.php and retest?

Test case

Attachment: TestBug22425.java (text/x-java), 2.46 KiB.

You might not be able to repeat it because you are using a different version of MySQL server. Did you run my test as-is, or write your own? From your sample output, it's not clear if you were using prepared statements or not. Also, I was not using a LIKE query, but an exact string match (=) instead.

I may be wrong, but strict_trans_tables looks like an option only available after MySQL 5.0.2 (according tottp://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html), so what does it matter if it is disabled?

I will be glad to download and install the nightly build of MySQL 4.1.22 as long as it is (relatively) safe to run two versions of MySQL against the same set of tables (of course, only one at a time).

Christopher, I'll try to answer your questions.
 - I am obliged to use latest server version in my tests but I don't think that matters here.
 - I made my own test (attached to the report) according to our SOP.
 - I used PS, LIKE, = and many variants of them. LIKE is just last thing I tried.
 - strict_trans_tables is mentioned because I used my.ini from another bug report tested on 5.0 and this should be removed from *that* my.ini to test on 4.1
 - I refered to nightly build of connector/J not MySQL server

My apologies; I was unclear about which component to try a snapshot. Trying Connector/J snapshots are much easier than upgrading MySQL ;)

Okay, I have run this same test using Connector/J nightly 20060920 with the same results (or lack thereof).

For whatever reason, using prepared statements parameter replacement like this:

ps.setString(1, "username")

results in a query that returns no results for me. When I turn off server-side prepared statements, everything works as expected.

Is there a way I can get some more debugging output? I have turned on "profileSQL" and everything looks okay to me: the prepared statement is "prepared" as I would expect (i.e. the text looks right) and then the execution includes the query as I expect (i.e. "WHERE username='chris'").

I experienced the same problem with MySQL 5.0.24a and Connector/J 3.1.13.  I then experienced the same problem with MySQL 5.0.26 and Connector/J 5.0.3 -- the latest GA releases.  

By specifying useServerPrepStmts=false in the JDBC URL, the problem disappeared.  In my case, I was inserting values into a DECIMAL(15,2) column in an InnoDB table using a PreparedStatement. The value, as a Double, 72028.43 would caues a Data truncation whereas 62028.43 would not.

I spent the better part of the day researching this problem.  There are a lot of posts about suppressing the exception (jdbcCompliantTruncation=false) and diableing strict mode.  But the problem, I believe, is in ServerPreparedStement.

Hi Vidur. Your problem is not related *directly* to this report. There was a problem with DECIMAL parameters but it's solved. Please search Bugs DB (BUG#22290 + BUG#22869 for example) and use latest connector/J snapshot.

Hi Christopher. There is one more thing you can do. You can set up local SVN repo (use TortoiseSVN or any ther client) from following URL's:
http://svn.mysql.com/svnpublic/connector-j/branches/branch_3_1/connector-j
or
http://svn.mysql.com/svnpublic/connector-j/branches/branch_5_0/connector-j
and test from inside Eclipse. There you'll see all that's happening in connector.
Probably you are hitting something closely connected to your configuration.

If you give me the rest of your test case (the attched test case is incomplete as it does not include the testsuite.BaseTestCase class or code), I'll see if it fails on my setup.

There is one question that I just can't seem to get you to answer: why does this work when I turn off server-side prepared statements, but fails when I turn them on. That's the only variable that is being changed, here.

I've updated my test so that it does not even require a table. Executing a simple "SELECT ? AS broken" will fail with using a parameterized server-side prepared statement. It works when the prepared statement is handled on the client side.

I have even gone so far as to uninstall MySQL and destroy all databases and tables, re-build it from source, and re-try the attached testcase, and it still fails (even without re-installing my own databases).

I have also asked others to try the same test case on other instances of MySQL that they have available, and nobody else can reproduce it. :(

I realize that you cannot reproduce this bug (and apparently only one other person in the world can do it). That probably means that I have some small difference in configuration to everyone else. Given a completely fresh 4.1.21 build from Gentoo-patched sources, with no configuration changes and alterations to the stock mysql and test databases that come with MySQL out-of-the-box, my test case fails in my environment.

I would love to figure this out. Please help me get more information from either MySQL or the driver. Turning on "traceProtocol" doesn't seem to get me anything. I feel like me stepping through the code in a debugger is sort of a waste, since I don't know how the code (or protocol) works in the first place.

If you can direct me to add a ton of logging or something that I can turn over to you for analysis, I'd love to do that. I just think I'm underqualified to this kind of analysis myself.

Test case that can demonstrate this bug in my environment.

Attachment: JDBCTest.java (application/octet-stream, text), 2.53 KiB.

Added my simplified test case (requires no tables).

When I run it like this on my freshly rebuilt MySQL 4.1.21 environment, I get the following output:

$ java -classpath .:mysql-connector-java-3.1.13.jar -Dusername=root -Dpassword= -DuseServerPrepStmts=false JDBCTest
Using connection string: jdbc:mysql://localhost/test?characterEncoding=utf8&dumpQueriesOnException=true&profileSQL=false&traceProtocol=false&useServerPrepStmts=false
===========================
Using Statement
===========================
works
non-blank string
===========================
Using PreparedStatement
===========================
works
non-blank string
===========================
Using Parameterized PreparedStatement
===========================
broken
non-blank string
$

When I run this test and turn on server-side prepared statements using the system property I rigged, I get this output:

$ java -classpath .:mysql-connector-java-3.1.13-bin.jar -Dusername=root -Dpassword= -DuseServerPrepStmts=true JDBCTest
Using connection string: jdbc:mysql://localhost/test?characterEncoding=utf8&dumpQueriesOnException=true&profileSQL=false&traceProtocol=false&useServerPrepStmts=true
===========================
Using Statement
===========================
works
non-blank string
===========================
Using PreparedStatement
===========================
works
non-blank string
===========================
Using Parameterized PreparedStatement
===========================
broken

$

Note the blank line after "broken" at the end, there. I checked, and it returns a 0-length string, there.

C version of same (failing) test

Attachment: mysql_test.c (application/octet-stream, text), 2.50 KiB.

I added another test case, this time using the C API.

Whatever is going on, it's not a Java or Connector/J problem, so this bug should probably be either re-assigned (and re-described), or closed and re-opened as a new bug (which I am happy to do, since the description is now totally wrong).

I have confirmed this behavior on another Gentoo Linux system that I manage, so if it is a configuration issue, then it is a common one across these two systems.

I am happy to provide full configuration information to help resolve this issue.

Finally, I am attempting to reproduce this in a VMware instance in order to demonstrate the problem in a way that can be transferred to you so you can actually see it ;) Would that be helpful?

I have encountered the same problem when trying to use a prepared statement to insert a row. I have tested both Connector/J 3.1.13 and 5.0.3 with mysql-4.1.21 and got the same result. The stacktrace below is from Connector/J 5.0.3.

CREATE TABLE `users` (
  `user_id` char(200) default NULL,
  `username` char(200) default NULL,
  `password` char(200) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

public class MySQLTest {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = 
            DriverManager.getConnection("jdbc:mysql://localhost/jbossdb", "jboss", "seam");
        
        String sql = "insert into users(user_id, username, password) values(?, ?, ?)";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1, "x"); 
        ps.setString(2, "y");
        ps.setString(3, "z");
        ps.execute();
    }
}

Exception in thread "main" com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for column 'username' at row 1
	at com.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.java:717)
	at com.mysql.jdbc.MysqlIO.scanForAndThrowDataTruncation(MysqlIO.java:3031)
	at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1241)
	at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:685)
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:790)
	at MySQLTest.main(MySQLTest.java:18)

Christopher, can you post output from following statements (in mysql cl client):

prepare pr1 from 'select ? as col1';
set @v1='some string';
execute pr1 using @v1;

My output is just fine:
C:\mysql\bin>mysql -uroot test -T
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.22-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> prepare pr1 from 'select ? as col1';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @v1='some string';
Query OK, 0 rows affected (0.02 sec)

mysql> execute pr1 using @v1;
Catalog:    'def'
Database:   ''
Table:      ''
Name:       'col1'
Type:       254
Length:     11
Max length: 11
Is_null:    0
Flags:      1
Decimals:   0

+-------------+
| col1        |
+-------------+
| some string |
+-------------+
1 row in set (0.00 sec)

mysql>

As for your C API test case, it is faulty. I am checking it now.

Christopher, I am attaching VS2005 solution so you can try it. You will need debug version of libmysql.dll put in Debug\ folder for things to work. If you don't have VS there's compiled exe in Debug\ folder (again, debug version of libmysql.dll needed). Test case is basically quick rework of your test so you should be familiar with it.

VS 2005 project, mind the paths!

Attachment: bug22425.zip (application/x-zip-compressed, text), 186.84 KiB.

Your mysql-client test case seems to work.
------------------------------------------

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 122 to server version: 4.1.21-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> prepare pr1 from 'select ? as col1';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @v1='some string';
Query OK, 0 rows affected (0.00 sec)

mysql> execute pr1 using @v1;
+-------------+
| col1        |
+-------------+
| some string |
+-------------+
1 row in set (0.00 sec)

mysql>

I'm running Gentoo Linux for my server. Should I attempt to install the client programs on a Windows box and test over the network?

I intentionally rigged my C program to connect using sockets (using 127.0.0.1 instead of "localhost") to mimin my Connector/J circumstances. I would imagine that testing from another machine would still be okay.

Does the "Windows Essentials" package have the server and everything else included? I'd prefer not to actually install anything. I'll try the non-installer version and just unzip it somewhere convenient. I'll also try to dig up a win32 C compiler ;)

Hi Christopher. 
Essential has the C developer files without debug information so it's not suitable. Anyway, you do not need to test on Windows. Linux test with 
gcc /path_to/test_case.c -o /path_to/test_case -L/path_to/lib/mysql -I/path_to/include/mysql -lmysqlclient_r -lz
LD_LIBRARY_PATH=/path_to/lib/mysql ./path_to/test_case
is just fine by me. Did you cross-examine your code with mine? I tried to mark all changes I did to your original.

>Your mysql-client test case seems to work.
>------------------------------------------
So it seems not to be server issue...

I get the same (broken) output:

status: Init
status: Connect
status: init prepared statement
status: prepare statement
status: bind parameters
status: set param string to 'non-empty-string'
status: executing statement
status: binding result to buffers
status: store result
status: fetch row
RESULTS
Expected one result ('non-empty-string'). Got:
''
status: close statement
status: Disconnect

I checked-over your changes, and it looks like you added full metadata use and printing, much more like my orignial Java-based example. I did notice a change to the setting of params[0].buffer_length which shouldn't matter since it's an IN parameter. The C API documentation suggests that MYSQL_STMT.buffer_length is irrelevant for IN-only parameters. At any rate, this test still fails for my setup.

I'm currently building a VMWare-based virtual machine that will hopefully be able to reproduce this bug for you. Not sure how to get it to you, though, since it will be maybe 500MB. :(

Christopher, thanks for youe efforts. I'm waiting on results.

Tonci,

Not sure what feedback you need. I compiled and ran your modified test and got the same (broken) output on my setup.

Christopher,
> I'm currently building a VMWare-based virtual machine that will hopefully be
able to reproduce this bug for you.

I would like to see this happen on clean machine since I think that could give us a better idea.

I finished building the VM the other day, and it appears to work as expected. :(

Strange that I have two environments that fail and one (which I attempted to match as closely as possible) that does not. The only difference right now between the VM and the "real" machines that I have that I can think of is the kernel itself. I have not yet re-built the kernel with AMD Athlon-specific optimizations in it. I've been trying, but I need to hit a sweet spot of optimizations that I know I need versus device drivers that need to exist for it to work in the VM itself.

Christopher, thanks for your efforts. I'm not convinced this is x64 issue but will try it myself on AMDx64 and FC 5. Waiting on more info from you.

This isn't x64. This is AMD k7 Athlon XP. Both machines having this problem are Athlon XP (32-bit) machines. I'm hosting the VM on yet another 32-bit AMD Athlon XP processor. The two servers exhibit the problem but the VM does not.

The only difference I can detect is the kernel.

I have a similar problem too - has any further light been thrown on what the cause is?

The statement is as follows:
            int result = stmnt.executeUpdate(Query.toString());

The platform is as follows:  ( I am going to try others)

+-----------------+
| version()       |
+-----------------+
| 4.1.21-standard |
+-----------------+
1 row in set (0.66 sec)

mysql-connector-java-5.0.3-bin.jar

The message is as follows:

Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for column 'CodeID' at row 1
        at com.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.java:717)
        at com.mysql.jdbc.MysqlIO.scanForAndThrowDataTruncation(MysqlIO.java:3031)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1812)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3118)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3047)
        at com.mysql.jdbc.Statement.executeQuery(Statement.java:1166)
        at com.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.java:710)
        at com.mysql.jdbc.MysqlIO.scanForAndThrowDataTruncation(MysqlIO.java:3031)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1812)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3118)
        at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1313)
        at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1232)
        at General_Classes.General_Model.add(General_Model.java:362)
        ... 27 more

Charles,

You'll have to post more information than that.

For example, if you have a field that should accept 5 characters, and you're trying to insert 10 characters, then you are seeing expected behavior.

Please read all the comments in this bug... it really has nothing to do with data truncation.

I am sorry I forgot to add the query, which I will do when I have time but I cannot now reproduce it. Meanwhile, I have gone back to 

mysql-connector-java-3.0.14-production-bin.jar

and all is well again. I suppose it is possible that I was trying to truncate data and that 3.0.14 doesn't report it - is that possible?

[Off-topic: It is a pity that the messages do not incorporate the query text. Comment also applies to Syntax error messages which only give from the first token which mysql thinks is wrong. I assume this is the server rather than the driver, though. It makes life hard for those of us who construct queries on the fly]

Charles Whittington

Putting the query text in error messages by default is considered "information leakage", which is a security hazard.

However, if you add "dumpQueriesOnException=true" to your JDBC URL configuration properties, the driver will do what you're asking for, even for prepared statements (it's not enabled by default).

Charles,

According to http://dev.mysql.com/doc/refman/4.1/en/cj-news-3-1-0.html, server-side prepared statements were added in the 3.1 versions of the connector.

It looks like the data truncation became an error (at least in ResultSet.getX()) in 3.1.4: http://dev.mysql.com/doc/refman/4.1/en/cj-news-3-1-4.html

Perhaps switching back to 3.0.x simply avoids the "problem".

Try turning off server-side prepared statements as suggested in this bug and let us know if that fixed the problem.

OK thanks - looks useful. I have switched back to 5.0.3 for alpha testing and will report if it happens again.

Charles Whittington

OK. This is what I am getting with 5.0.3: When adding a row to a table with an int(10) autoincrement column, CodeID, I am supplying CodeID='' and it is 
complaining about truncation, which 3.0.x driver certainly didn't. You may well ask why I am supplying CodeID='' to an autoincrement column and it is because the query is being generated. This will probably be fixed at some stage, but if this information helps you find the problem, you are welcome! We are going back to 3.0.x for the time being unless/until you ask for more tests.

Charles Whittington

The Code:
            con = new JDCConnectionPool().getConnection();
            stmnt = con.createStatement();
            Query = new StringBuffer(30);
.
. generate the Query
.
            ResultSet Rs = stmnt.executeQuery(Query.toString());
.
. etc

The SQLException:

<my reporting> followed by:

Caused by: java.sql.SQLException: Data truncation: Data truncated for column 'CodeID' at row 1

Query being executed when exception was thrown:

SHOW WARNINGS

Query being executed when exception was thrown:

insert into CodeValue set Value='' , TableName='' , CodeID='' , EllsoUserID='1' , Notes='' , AuthorID='1' , UsedBy='Members Panel' , CodeName='UserInstrumentsAny Instrument' , RoleID='' 
        at com.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.java:717)
        at com.mysql.jdbc.MysqlIO.scanForAndThrowDataTruncation(MysqlIO.java:3031)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1812)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3118)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3047)
        at com.mysql.jdbc.Statement.executeQuery(Statement.java:1166)
        at com.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.java:710)
        at com.mysql.jdbc.MysqlIO.scanForAndThrowDataTruncation(MysqlIO.java:3031)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1812)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3118)
        at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1313)
        at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1232)
        at General_Classes.General_Model.add(General_Model.java:362)
        ... 27 more

This is the table desc:
+-------------+------------------+------+-----+-------------------+----------------+
| Field       | Type             | Null | Key | Default           | Extra          |
+-------------+------------------+------+-----+-------------------+----------------+
| CodeID      | int(10) unsigned |      | PRI | NULL              | auto_increment |
| CodeName    | text             | YES  |     | NULL              |                |
| Notes       | text             | YES  |     | NULL              |                |
| UsedBy      | text             | YES  |     | NULL              |                |
| TableName   | text             | YES  |     | NULL              |                |
| Value       | text             | YES  |     | NULL              |                |
| EllsoUserID | int(10) unsigned | YES  |     | NULL              |                |
| RoleID      | int(10) unsigned | YES  |     | NULL              |                |
| DateChanged | timestamp        | YES  |     | CURRENT_TIMESTAMP |                |
| AuthorID    | int(11)          | YES  |     | NULL              |                |
+-------------+------------------+------+-----+-------------------+----------------+
10 rows in set (0.24 sec)

Charles,

Can you download and run some of the tests that are attached to this bug? (Look under the "Files" section).

My tests in Java anc C both compile with everything provided. Tonci's Java test has external dependencies, so that won't work for you, but his C test will.

Can you try one of them out? My Java test will run a series of queries, the last of which fails on my setup. Check the code for the system properties which control connection information. The test does not require any tables, and is a read-only test.

Finally, please post the details of your system configuration: OS type (and kernel version and distro name if Linux), MySQL Server version, and Java version.

Thanks.

Christopher, I am closing this report as "Can't repeat". I hope you've found what's wrong with your config by now.
One final word on my test; please see comment (and URL's) about our public SVN repository. If you connect to our source repo from inside Eclipse you'll be able to run all of our c/J test cases.

Regards

An upgrade to MySQL 5.0 on one of the affected systems has apparently fixed the bug. I'm not sure if was a compiler setting or what, but anyone who is having this problem should upgrade and test: it worked for me.

Of course, another option is to use client-side prepared statements instead of server-side prepared statements. Doing this with the Connector/J library is easy... I'm not sure about libraries for other languages.

Hi Christopher.

Can you be more specific of versions you used to bypass this problem? My guess is it might help someone.

This bug actually has nothing to do with Connector/J or "SQLException: Data truncated for column". I have adjusted this bug accordingly.

This affected me with the following configuration:
- AMD x86 hardware (2 different machines with the same problem)
- Gentoo Linux (kernel version 2.6.17)
- MySQL 4.1.21 - built from source through Gentoo's portage package manager
- Connection through TCP/IP or socket (using "127.0.0.1" or "localhost") both fail.

Gentoo-specific environment information:
- Gentoo profile: "default-linux/x86/2006.1/server" and "default-linux/x86/2006.1"
- Use flags for MySQL: +berkdb +perl +ssl -(everything else)
- Not sure which patches were applied (see below for 4.1.22 patches)

Both Java and C clients fail to properly prepare statements. This is not a problem with the client library.

I'm currently trying an upgrade to 4.1.22. Same USE flags as above. Patches:
 * using '010_all_default-dir-4.1.15.patch'
 * >    Move config directory to the gentoo default
 * using '030_all_thrssl-4.1.21.patch'
 * >    Link libmysqlclient_r against ssl
 * using '035_x86_asm-pic-fixes-4.1.12.patch'
 * >    remove page relocations
 * >    Most part of the original patch has already been accepted by MysQL,
 * >    here is the remaining.
 * >    _many_ thanks to pageexec@freemail.hu
 * using '040_all_tcpd-vars-fix-4.0.14.patch'
 * using '105_all_mysql_config_cleanup.patch'
 * >    fix bug #156301 mysql_config wrongly retains too much info from CFLAGS
 * using '200_all_query-logging-bypass-4.1.19.patch'
 * >    Fix bug #128713
 * >    dev-db/mysql: Query Logging Bypass Vulnerability (CVE-2006-0903)
 * using '700_all_mysqldump-missing-cfgfile-fix-4.1.21.patch'
 * >    Add configuration section for mysqldump
 * using '701_all_rpl000015-portsobst-fix-4.1.21.patch'
 * >    normalize port 3306 in tests
 *       010_all_default-dir-4.1.15.patch ...                             [ ok ]
 *       030_all_thrssl-4.1.21.patch ...                                  [ ok ]
 *       035_x86_asm-pic-fixes-4.1.12.patch ...                           [ ok ]
 *       040_all_tcpd-vars-fix-4.0.14.patch ...                           [ ok ]
 *       105_all_mysql_config_cleanup.patch ...                           [ ok ]
 *       200_all_query-logging-bypass-4.1.19.patch ...                    [ ok ]
 *       700_all_mysqldump-missing-cfgfile-fix-4.1.21.patch ...           [ ok ]
 *       701_all_rpl000015-portsobst-fix-4.1.21.patch ...                 [ ok ]

After this build and a MySQL restart (to startup the new version), this issue has been corrected; that is, my test programs run properly.

An upgrade to MySQL 5.0 also results in the successful execution of my test programs -- both in C and Java.

The bottom line is that 4.1.21 is a bum version of MySQL. There are other problems with it (like ORDER BY doesn't work in some cases) and it basically should not be used for any reason. Fortunately, there's another production-ready version in the 4.1 line available that solves many of these problems.