Bug #15310 Truncated data warning from EXPLAIN query
Submitted: 29 Nov 2005 12:26 Modified: 9 Jun 2009 15:11
Reporter: David Walker Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:3.51.12 OS:Microsoft Windows (Windows 2000)
Assigned to: CPU Architecture:Any

[29 Nov 2005 12:26] David Walker
Description:
I am connecting to MySQL 4.1.11-nt from Python using mx.ODBC and MyODBC 3.51.12

When fetching results after calling certain "EXPLAIN SELECT..." queries, the following warning is returned:

"Warning: ('01004', 501, '[MySQL][ODBC 3.51 Driver][mysqld-4.1.11-nt]String data, right truncated', 3526)"

This behaviour does not occur with version older than 3.51.12 of the MySQL Connector/ODBC. (I have verified this with 3.51.11 and 3.51.9)

Because mx.ODBC treats warnings as exceptions, this is effectively an error that prevents my program from working. (There appears to be no way around this, short of recompiling mx.ODBC or installing an older driver).

This only seems to occur when the explanation would list a select_type of DEPENDENT SUBQUERY with a type of unique_subquery.

How to repeat:
CREATE TABLE `tbl_test` (
  `test_id` int(10) unsigned NOT NULL auto_increment,
  `test_string` varchar(45) character set latin1 NOT NULL default '',
  PRIMARY KEY  (`test_id`),
  UNIQUE KEY `Index_unique` (`test_string`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

INSERT INTO tbl_test VALUES (1, 'alpha'),(2, 'beta'), (3, 'gamma');

Now via an 3.51.12 ODBC connection, execute the following query...
EXPLAIN SELECT * from tbl_test WHERE test_id in (SELECT test_id FROM tbl_test)

This results in the error:
[MySQL][ODBC 3.51 Driver][mysqld-4.1.11-nt]String data, right truncated

(This may require a Python mx.ODBC connection to reproduce..?)

With the 3.51.11 version of the ODBC driver (or earlier) the query succeeds with the following table:
+----------------------------------------------------------------------------------------------------------------------+
| id | select_type        | table    | type            | possible_keys | key     | key_len | ref  | rows | Extra       |
+----------------------------------------------------------------------------------------------------------------------+
|  1 | PRIMARY            | tbl_test | ALL             | NULL          | NULL    | NULL    | NULL |    3 | Using where |
|  2 | DEPENDENT SUBQUERY | tbl_test | unique_subquery | PRIMARY       | PRIMARY |       4 | func |    1 | Using index |
+----------------------------------------------------------------------------------------------------------------------+
[29 Nov 2005 13:41] Valeriy Kravchuk
Thank you for a problem report. Sorry, but I was not able to repeat the problem you described with 3.51.12 connectin to 5.0.15 on XP, using other ODBC-based tool (not in Python). Can you, please, try to make the same EXPLAIN ... query from any standard ODBC client (even MS Query in any Office program is OK). Or, alternatively, can you, please, switch on ODBC tracing and send the trace file you get while trying to perform this EXPLAIN.
[29 Nov 2005 15:01] David Walker
Produced using Python mx.ODBC library. Fails with "String data, right truncated" warning.

Attachment: odbc_mx.log (application/octet-stream, text), 22.63 KiB.

[29 Nov 2005 15:01] David Walker
Produced using Python pywin32 library. Succeeds.

Attachment: odbc_win32.log (application/octet-stream, text), 18.51 KiB.

[29 Nov 2005 15:06] David Walker
Thanks for the quick response.

I do not have MS Query, so wasn't able to test using that. However, I did try running the query using a different Python ODBC library, and the problem did not occur, so the problem might lie with mx.ODBC rather than MySQL Connector/ODBC itself.

I have uploaded the ODBC traces of the two queries in case they are of use to you in determining whether this really is a bug in the ODBC driver, or whether it's the fault of mx.ODBC.
[30 Nov 2005 14:20] Valeriy Kravchuk
Thank you for the additional information. You have:

bsadmin" isrdb  5c8-48c	EXIT  SQLFetch  with return code 1 (SQL_SUCCESS_WITH_INFO)
		HSTMT               00A824B8

		DIAG [01004] [MySQL][ODBC 3.51 Driver][mysqld-4.1.11-nt]String data, right truncated (501) 

in both logs. So, it is the difference in your Python libraries used. They got the same return code, but treat it differently. I believe, SQL_SUCCESS_WITH_INFO is success. So, data should be displayed. Please, check for any additional settings, attributes etc. and/or ask the author of the library.
[30 Nov 2005 14:44] David Walker
That is consistent with mx.ODBC's documented behaviour, which essentially treats warnings as errors, whereas most ODBC connectors ignore them. It would appear that mx.ODBC is behaving correctly. (Annoying, but correct!).

So, the question remains: why does MySQL Connector/ODBC 3.51.12 produce this warning when MySQL Connector/ODBC 3.51.11 and earlier do not?

Running the same EXPLAIN query in 3.51.11 does not produce any warning, and there is no apparent truncation of string data. So why does this happen in 3.51.12, and is there any way to prevent it?
[30 Nov 2005 22:05] Vasily Kishkin
I tried to repeat the bug on C test case but I was not able to reproduce it. I used 4.1.16. There is not any problem. Could you please upgreate mysql server ? Maybe the bug was fixed.
[1 Dec 2005 12:14] David Walker
I have tested with 4.1.15 and 4.1.11, on two separate machines (one with a fresh install of MySQL 4.1.15).

The warning is always produced, but only for that particular EXPLAIN SELECT query, and only with MySQL Connector 3.51.12. Previous versions of the driver do not produce the warning.
[5 Dec 2005 8:55] Vasily Kishkin
Sorry...I was not able to reproduce the bug. Could you please provide any test case on C for repeating  the bug ?
[5 Dec 2005 12:37] David Walker
Unfortunately I do not have the time or resources to code this in C at present.

However, here is the simplest Python code that reproduces the bug:

import mx.ODBC.Windows
conn = mx.ODBC.Windows.connect('indexserver', 'indexserver_rw', 'indexserver')
cursor = conn.cursor()
cursor.execute('EXPLAIN SELECT * from tbl_test WHERE test_id in (SELECT test_id FROM tbl_test)')
cursor.fetchall()

When this is run, ODBC produces the following:

mxODBC.Warning: ('01004', 501, '[MySQL][ODBC 3.51 Driver][mysqld-4.1.15-nt]String data, right truncated', 3526)

Since upgrading to MySQL 4.1.15, I now seem unable to reproduce this problem using the PyWin32 odbc library. The warning now only occurs with mx.ODBC.
[5 Dec 2005 12:38] David Walker
N.B. The parameters for connect() are:
datasource name ("indexserver")
username ("indexserver_rw")
password ("indexserver")
[7 Dec 2005 14:27] Vasily Kishkin
I was able to reproduce the bug on Python 2.4 and on C. 
The Python test case returned:

Traceback (most recent call last):
  File "Bug15310.py", line 8, in ?
    cursor.fetchall()
mxODBC.Warning: ('01004', 501, '[MySQL][ODBC 3.51 Driver][mysqld-4.1.16-debug]String data, right truncated', 3526)

The C test case returned garbage:

Connect....
Test....
0   А ∟ x 1244868 x 9377688 |
Disconnect....

Both test cases are attached.
[7 Dec 2005 14:28] Vasily Kishkin
Test case

Attachment: test.c (text/plain), 4.95 KiB.

[7 Dec 2005 14:28] Vasily Kishkin
Test case

Attachment: Bug15310.py (text/plain), 286 bytes.

[8 Dec 2005 18:54] David Walker
I have also discovered that this same problem can occur when calling .fetchall() after "SHOW COLUMNS FROM ..." for some tables.

Here is an example...
CREATE TABLE `value_int` (
  `value_id` int(10) unsigned NOT NULL auto_increment,
  `value` int(11) default NULL,
  PRIMARY KEY  (`value_id`),
  UNIQUE KEY `Index_value_unique` USING BTREE (`value`),
  KEY `Index_value_id` (`value`,`value_id`),
  KEY `Index_id_value` (`value_id`,`value`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin

After creating that table, running...
SHOW COLUMNS FROM value_int;
...using MySQL Connector/ODBC 3.51.12 raises the following warning:
Warning: ('01004', 501, '[MySQL][ODBC 3.51 Driver][mysqld-4.1.15-nt]String data, right truncated', 3526)

This did not occur prior to MyODBC 3.51.12, so I assume it is related.
[29 Mar 2007 14:08] Martin Davies
For what it's worth - just had the same error using Microsoft Query and 3.51.12 ODBC connector. Tried the 3.51.14 connector - same error. Found the 3.51.11 connector on in the archive section "http://downloads.mysql.com/archives.php?p=myodbc-3.51&v=3.51.11" and loaded that (had to uninstall first) and that works fine.
Definitely seems to be a problem with the 3.51.12 and subsequent ODBC connectors.
[13 Apr 2007 15:23] Tom Hines
I have the same problem and just upgraded to the 3.51.14 and it produces the same error.
I retroverted to the only older driver I could find 2.50.39 that works, but it's 5.5 years old.
I looked at the archive page and there is nothing there.
Is 3.51.11 still available somewhere?
[12 Jun 2007 13:27] Tonci Grgin
I don't see anyone attaching my.ini/cnf file nor do I see the SQL_MODE server is running in. Please add requested info so we can look into this again.
[14 Jul 2007 23: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".
[9 Jun 2009 15:11] David Walker
This bug is no longer present with mx.ODBC and connector 3.51.27. I guess Jess Balint's fix for bug#30890 (3.51.25) has squashed this one also, as they look like manifestations of the same problem.
[9 Jun 2009 15:32] Tonci Grgin
David, thanks for getting back to us.