java.sql.PreparedStatement.setString Function getting Varchars confused with doubles

Question

I have a servlet/tomcat server written in java.

I have a mysql class that I have written, and I have been using the functions in it to insert prepared statements into a mysql database using jdbc.

The function I call uses java.sql.PreparedStatement.setString in order to set the paramaters of the prepared statement. This has been working perfectly for thousands of different inputs for months on end without issue.

Recently however, when trying to use the function to insert an ip address into a VARCHAR type mysql column I am getting an exception thrown as follows:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: '10.1.1.101'

This is bizarre, There is no notion of a DOUBLE anywhere in my code, and a "Show Columns" on the mysql table ensures that the data type is in fact a VARCHAR. I have had my colleagues look at this as well to double check that I wasn't missing something simple. However we are all stumped.

My only theory is that the JDBC driver or the SetText function is taking a liberty and assuming a DOUBLE data type because the first part of the ip address is in the form of XX.XX

Any help would be great, please don't tell me to do obvious stuff like check my column data types etc. I have spent a lot of time double and tripple checking everything.

Answer 1

The problem is not with JDBC Driver. The problem lies with MySQL. Here is what i get on MySQL commadline:

mysql> INSERT INTO route_table (SYSTEM, IP, PORT) VALUES ("192.168.1.24:8080","192.168.1.24","8080") ON DUPLICATE KEY UPDATE IP=values(IP) AND PORT=values(PORT);
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO route_table (SYSTEM, IP, PORT) VALUES ("192.168.1.24:8080","192.168.1.24","8080") ON DUPLICATE KEY UPDATE IP=values(IP) AND PORT=values(PORT);
ERROR 1292 (22007): Truncated incorrect DOUBLE value: '192.168.1.24'
mysql>

The problem is , the syntax of INSERT INTO .... ON DUPLICATE KEY UPDATE used by you is incorrect. You have used a keyword AND instead of using a ,(comma). So the query should be:

mysql> INSERT INTO route_table (SYSTEM, IP, PORT) VALUES ("192.168.1.24:8080","192.168.1.24","8080") ON DUPLICATE KEY UPDATE IP=values(IP), PORT=values(PORT);
Query OK, 2 rows affected (0.00 sec)