MySQL Bugs: #15310: Truncated data warning from EXPLAIN query

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

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 |
+----------------------------------------------------------------------------------------------------------------------+

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.

Produced using Python mx.ODBC library. Fails with "String data, right truncated" warning.

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

Produced using Python pywin32 library. Succeeds.

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

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.

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.

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?

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.

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.

Sorry...I was not able to reproduce the bug. Could you please provide any test case on C for repeating  the bug ?

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.

N.B. The parameters for connect() are:
datasource name ("indexserver")
username ("indexserver_rw")
password ("indexserver")

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.

Test case

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

Test case

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

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.

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.

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?

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.

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".

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.

David, thanks for getting back to us.