| Bug #3697 | varchar(255) accepts longer data and trims it silently | ||
|---|---|---|---|
| Submitted: | 9 May 2004 1:12 | Modified: | 11 May 2004 1:20 |
| Reporter: | Antoine Martin | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0 | OS: | Linux (Linux 2.6.5 glibc 2.3.3) |
| Assigned to: | CPU Architecture: | Any | |
[9 May 2004 2:34]
Mark Matthews
> However, when running through the same steps via JDBC, the driver returns > the > original string (untrimmed). It looks like the JDBC layer is caching this > data > somehow? The JDBC driver does not _cache_ data, unless you're talking about updatable result sets. Are you using updatable result sets in this case?
[9 May 2004 13:06]
Antoine Martin
I am not using updatable result set to produce this problem. How come JDBC gets this result then? Is the data stored with all characters (>255) in the database and only trimmed for display through mysql cli?
[9 May 2004 13:31]
Antoine Martin
This doe snot seem to reproduce the bug... but it should!
Attachment: MySQLLongString.java (text/x-java), 2.58 KiB.
[9 May 2004 13:41]
Antoine Martin
Ok, thanks to this bug report I found a bug in our regression test! So the JDBC driver does not return the too long string but the trimmed version... That makes more sense! The problem remains that I think that the database should not accept the string and trim it but throw an error. Sorry about the false alarm.
[10 May 2004 15:34]
Alexander Keremidarski
Sorry, but the bug system is not the appropriate forum for asking
support questions. Your problem is not the result of a bug.
For a list of more appropriate places to ask for help using MySQL
products, please visit http://www.mysql.com/support/
Thank you for your interest in MySQL.
Additional info:
Up to 4.0 MySQL silently accepts values longer than column sizes as you mention.
Since 4.1 however it set's up an Warning which can be seen by SHOW WARNINGS command.
This ensures both backward compatibility with old behaviour and way to see what exactly happens.
mysql> INSERT INTO A(B) VALUES
-> ("012345678901234567890123456789012345678901234567890123456789012345678901234567
"> 89012345678901234567890123456789012345678901234567890123456789012345678901234567
"> 89012345678901234567890123456789012345678901234567890123456789012345678901234567
"> 89012345678901234567890123411111111111111111111111111111");
Query OK, 1 row affected, 1 warning (0.00 sec)
# Note the status line above which reports 1 warning
mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'B' at row 1 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)
[11 May 2004 0:19]
Antoine Martin
The JDBC driver does not report this as a warning, if you add the following lines to the code attached, you will see warnings=null:
stmt.executeUpdate(u);
//Read warnings
SQLWarning sqlw = stmt.getWarnings();
System.out.println(".update("+u+") warnings="+sqlw);
int wi = 0;
while (sqlw!=null && wi<1) {
System.out.println(".update("+u+") warning["+(wi++)+"]= "+sqlw.getMessage());
sqlw = sqlw.getNextWarning();
}
[11 May 2004 0:20]
Antoine Martin
Shall I report this as a JDBC bug?
[11 May 2004 0:33]
Mark Matthews
Statement.getWarnings() is only supported in Connector/J 3.1.x...What version of the JDBC driver are you using?
[11 May 2004 0:40]
Antoine Martin
driver version 'mysql-connector-java-3.1.1-alpha ( $Date: 2004/02/06 17:15:32 $, $Revision: 1.27.4.29 $ )' (I saw your earlier posts about getWarnings() too)
[11 May 2004 1:01]
Antoine Martin
Ignore me from now on. Junit uses a different classpath and loaded another driver... I am crawling under the carpet
[11 May 2004 1:11]
Antoine Martin
Sorry again for having wasted so much of your precious time. Your prompt and useful response will be featured in our survey results to be published shortly. In fact it is looking more and more like a mysql/postgres advocacy paper.
[11 May 2004 1:11]
Mark Matthews
Don't crawl too fast, I might have to join you. I'm working on a testcase that _seems_ to show this as a bug in the driver...More info to follow tomorrow...it's dinner time here.
[11 May 2004 1:20]
Mark Matthews
Okay, not a bug with the driver, but with my testcase (I wasn't creating a long-enough string to cause truncation). Statement.getWarnings() seems to work as intended. Please let us know if you get the warnings you expect in your testcase, and we can finally close this issue!
[11 May 2004 11:24]
Antoine Martin
All good with driver v3.1.1alpha on all classpaths.
[11 May 2004 11:43]
Antoine Martin
Just one last comment on this, it reports a SQLWarning for data truncation, when I believe it should use: http://java.sun.com/j2se/1.4.2/docs/api/java/sql/DataTruncation.html But I can live with that. Here is the warning: warning[0]= class java.sql.SQLWarning message=Data truncated for column 'TESTVC' at row 1, sql state=S1000, error code=1264 What is the best way to identify this state? sql state, error code or 'Data truncated %' Which one is the least likely to change?
Description: Inserting a string that is too long for the varchar column still succeeds, when I believe it should fail (most other RDBMS do fail) Or at least if not default behaviour, we should have the option of making sure such things do fail. This was picked up by our benchmarking / regression testing tools. How to repeat: CREATE TABLE A(B VARCHAR(255)); INSERT INTO A(B) VALUES ("012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123411111111111111111111111111111"); -- (This string is longer than 255) select length(TESTVC) from TEMPINDEX; 255 When the data is read back it is 255 characters in length. However, when running through the same steps via JDBC, the driver returns the original string (untrimmed). It looks like the JDBC layer is caching this data somehow? This is quite dangerous as for the application it looks as if the data has been saved properly (no error on write), but on next server restart the value is re-read from the store and will not match what was saved (and read) earlier. Suggested fix: Cause an error on insert.