| Bug #20925 | C API "show columns" report incorrect length of Type column | ||
|---|---|---|---|
| Submitted: | 9 Jul 2006 7:52 | Modified: | 14 Jul 2006 13:48 |
| Reporter: | Jacques Venter | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: C API (client library) | Severity: | S4 (Feature request) |
| Version: | 5.0.22 | OS: | Windows (XP SP2) |
| Assigned to: | CPU Architecture: | Any | |
[11 Jul 2006 20:04]
Valeriy Kravchuk
Thank you for a problem report. Can you send a complete test case that demonstrates the behaviour you described, with the results from it just copied and pasted?
[11 Jul 2006 21:28]
Jacques Venter
You're kidding, surely? How do you propose I copy and paste the value of one member of a C API structure which is at best only available while debugging? It is not as if I can goto the mysql command line and do a show columns and MySQL magickly will show me the length of the field structure retrieved. I told you already how to recreate it. Debug a C-API MySQL client application connected to a MySQL 5.0.22 server, execute a show columns on any table you wish, and then inspect the returned fields' length members. You will quickly see the Type column returns a very weird length (a length which makes no sense at all in the context of the table you picked). Sorry, but there is no easy way to capture and then copy and paste the results of these steps to you. This needs to be confirmed by a developer stepping a MySQL client app which uses the C API directly.
[12 Jul 2006 7:54]
MySQL Verification Team
Hi, On 5.0.24, CREATE TABLE `bug20925` ( `id` bigint(20) NOT NULL auto_increment, `name` varchar(240) default NULL, `birthday` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM; SHOW COLUMNS FROM bug20925; <cut> field->name: Type field->org_name: COLUMN_TYPE field->table: COLUMNS field->org_table: field->db: field->catalog: def field->def: (null) field->length: 196605 field->max_length: 12 field->name_length: 4 field->org_name_length: 11 field->table_length: 7 field->org_table_length:0 field->db_length: 0 field->catalog_length: 3 field->def_length: 0 field->flags: 17 Content of field->flags: NOT_NULL_FLAG BLOB_FLAG (deprecated) </cut> Please use field->max_length to determine how much memory to allocate. In my case it's 12, because strlen("varchar(240)") == 12; To see max_length you should use mysql_store_result instead of mysql_use_result. Let us know if that works.
[12 Jul 2006 7:58]
MySQL Verification Team
Jacques, Btw, you *can* use mysql client with --debug option to show this information :) See attached output from my a client.
[12 Jul 2006 7:58]
MySQL Verification Team
client debug output
Attachment: bug20925.client.txt (text/plain), 1.84 KiB.
[12 Jul 2006 8:23]
Jacques Venter
Ah, thanks for the heads up and confirmation. Well there you have it, proof in the pudding. I have to comment though, and back to the original point, that although using max_length would kind of solve the issue, the fact that length does not perform/contain expected data as per definition, should still be a cause for concern to MySQL Dev. Where does this number come from? What is its purpose? Thumbsucked? It can not be to fit potentially the maximum ENUM or SET definition as it is simply too small (64K*64chars+brackets and commas)? Or maybe using a 4 byte charset? These latter size can run into megs, so, what is the purpose of 196605? Point being, if length should reflect the maximum 'potential' data size in a column, as per definition, then it should do so in all cases for all tables and not grap some number out of thin air. This current value does not reflect anything specific, nor does it reflect the maximum data length of any standard MySQL columntype. It is in fact, as hinted already, thumbsucked. Any case, it seems to be a moot point. I'll use the max_length instead and not make any assumptions on length in future. Thanks all for the help and comments.
[14 Jul 2006 13:48]
Valeriy Kravchuk
This is not a bug, but almost reasonable value. According to the manual (http://dev.mysql.com/doc/refman/5.0/en/c-api-datatypes.html): "- unsigned long length The width of the field, as specified in the table definition. - unsigned long max_length The maximum width of the field for the result set (the length of the longest field value for the rows actually in the result set). If you use mysql_store_result() or mysql_list_fields(), this contains the maximum length for the field. If you use mysql_use_result(), the value of this variable is zero." So, length is from table definition, not from real data. You have to use max_length, as Shane explained. Now take a look at: ... field->name: Type field->org_name: COLUMN_TYPE field->table: COLUMNS ... It can be considered as taken from INFORMATION_SCHEMA.COLUMNS table. Now: mysql> desc information_schema.columns\G *************************** 1. row *************************** Field: TABLE_CATALOG Type: varchar(512) Null: YES Key: Default: NULL Extra: ... *************************** 15. row *************************** Field: COLUMN_TYPE Type: longtext Null: NO Key: Default: Extra: Longtext, potentially, can be longer. So, you are right. As for the value: mysql> select 65535*3; +-----------+ | 65534*3+3 | +-----------+ | 196605 | +-----------+ 1 row in set (0.00 sec) The only explanation for it that I can imagine is based on: "An enumeration can have a maximum of 65,535 elements." If it can be interpreted as "it can be 65,535 characters long", then, with metadata stored in utf8, we have that value above. If this is true, than it should be properly documented.
[20 Jul 2006 19:36]
Paul DuBois
As Valeriy points out: - unsigned long length The width of the field, as specified in the table definition But if COLUMN_TYPE is a LONGTEXT column, the maximum length should be 4294967295 (or perhaps that value times 3). (That's what you get for the metadata of a "real" table for a LONGTEXT column.) A value of 196605 makes no sense. Resetting report category to Server; there's something more here than just a documentation issue, I believe.
Description: The "show columns" query on MySQL caused the result to wrongly report the st_mysql_field C API structure's "length" member with a value of 196605 bytes for the Type column. This occurs independently of on which table the query is done or which types of columns the specific table actually has. The consequence of this is that no C API client (including the libmysql.dll) can make an informed decision concerning the potential amount of data to expect in the subsequent rows fetched. For example: The client can not make an informed decision to pick a blob editor instead of a varchar editor to display/edit/change said column values based on the maximum length returned. Even worse, the client side may potentially allocate megs of ram for the data which follows, even if the table has only 5 INT columns. This only occurs on the show columns and potentially show variables (still confirming), but not on any other normal select query. And only on the Type column, while all the rest of columns show the correct maximum length. How to repeat: Using MySQL 5.0.22-nt community edition do a show columns on any exiting table and inspect the length member of the returned result's st_mysql_field structure for the Type column. As far as we can tell, this issue was introduced recently, i.e. >5.0.15 Suggested fix: The length should be reported in context of the table's definition/schema. NOT in context of some arbitrary "maximum" Type column size for enum/set columns, especially not if the table has no such columns. Ex. If a table contains only "INT(1)" columns, I would expect MySQL to return only a max length of the Type column to be "6" bytes, and not 196605 bytes. If the table also contains a "SET('abc','cba')" column definition, I expect the Type column's length member to reflect strlength("SET('abc','cba')")=16 and not 196605.