MySQL Protocol quirks
The MySQL Protocol is a very old thing. Current version is 10, however I have never seen a server which uses anything but version 10. And new features are added without bumping the number. The changes are usually backwards compatible and sometimes you break stuff to be backward compatible.
You know that character set, and most notably Unicode, support was introduced in 4.1 . Other things got added to 4.1 but let's stay with the charsets.
When you select data from MySQL the server sends so called Metadata about it. It contains information for every column in the result set - it might be directly from a table or created on the fly, the metadata can tell you that. It tells you also what is the maximum length of the column in bytes, as per column definition, as well as the maximal length for the result set.
Since MySQL Server 5.1, the client tool mysql can show you this metadata. It is nice for adding info to bugreports. Before 5.1 we had to write C proggies to dump the metadata.
Here is my example where the metadata is not consistent because the protocol doesn't allow it so.
andrey@poohie:~$ mysql --column-type-info
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 122
Server version: 5.1.48 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table texts(tiny tinytext character set utf8, normal text character set utf8, medium mediumtext character set utf8, longt longtext character set utf8);
Query OK, 0 rows affected (0.00 sec)
mysql> select * from texts;
Field 1: `tiny`
Catalog: `def`
Database: `test`
Table: `texts`
Org_table: `texts`
Type: BLOB
Collation: latin1_swedish_ci (8)
Length: 255
Max_length: 0
Decimals: 0
Flags: BLOB
Field 2: `normal`
Catalog: `def`
Database: `test`
Table: `texts`
Org_table: `texts`
Type: BLOB
Collation: latin1_swedish_ci (8)
Length: 65535
Max_length: 0
Decimals: 0
Flags: BLOB
Field 3: `medium`
Catalog: `def`
Database: `test`
Table: `texts`
Org_table: `texts`
Type: BLOB
Collation: latin1_swedish_ci (8)
Length: 16777215
Max_length: 0
Decimals: 0
Flags: BLOB
Field 4: `longt`
Catalog: `def`
Database: `test`
Table: `texts`
Org_table: `texts`
Type: BLOB
Collation: latin1_swedish_ci (8)
Length: 4294967295
Max_length: 0
Decimals: 0
Flags: BLOB
0 rows in set (0.00 sec)
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from texts;
Field 1: `tiny`
Catalog: `def`
Database: `test`
Table: `texts`
Org_table: `texts`
Type: BLOB
Collation: utf8_general_ci (33)
Length: 765
Max_length: 0
Decimals: 0
Flags: BLOB
Field 2: `normal`
Catalog: `def`
Database: `test`
Table: `texts`
Org_table: `texts`
Type: BLOB
Collation: utf8_general_ci (33)
Length: 196605
Max_length: 0
Decimals: 0
Flags: BLOB
Field 3: `medium`
Catalog: `def`
Database: `test`
Table: `texts`
Org_table: `texts`
Type: BLOB
Collation: utf8_general_ci (33)
Length: 50331645
Max_length: 0
Decimals: 0
Flags: BLOB
Field 4: `longt`
Catalog: `def`
Database: `test`
Table: `texts`
Org_table: `texts`
Type: BLOB
Collation: utf8_general_ci (33)
Length: 4294967295
Max_length: 0
Decimals: 0
Flags: BLOB
0 rows in set (0.00 sec)
As you can see. After the character_set_result (with SET NAMES UTF8) to UTF8, the length field changed values for all but the LONGTEXT column, where it stays the same. So, metadata is incorrect, inconsistent. Reason is that for length only 4 bytes are reserved in the protocol. Originally MySQL had no charsets and 4 bytes were just enough. But after 4.1 no more. So, when detecting the real types, one need to use the char_maxlen and divide Length with it, but only if the Length is not 4294967295, which leads to bugs, because this is an edge case.