Description:
In 5.1 and 5.5 'mysql` the metadata of a @user_variable returned identify the variable as BLOB (BLOB with an encoding = TEXT, I think). In 5.0 it was identified as a VAR_STRING.
How to repeat:
C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -p --column-type-info
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.23 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT NOW() INTO @a; SELECT @a AS 'TEST';
Query OK, 1 row affected (0.00 sec)
Field 1: `TEST`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: MEDIUM_BLOB
Collation: utf8_general_ci (33)
Length: 50331645
Max_length: 19
Decimals: 31
Flags:
+---------------------+
| TEST |
+---------------------+
| 2012-09-03 12:43:28 |
+---------------------+
1 row in set (0.00 sec)
mysql>
..
C:\Program Files\MySQL\MySQL Server 5.5\bin>cd \program files\mysql\mysql server
5.0\bin
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -p --debug-info
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.96-community-nt MySQL Community Edition (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT NOW() INTO @a; SELECT @a AS 'TEST';
Query OK, 1 row affected (0.00 sec)
Field 1: `TEST`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: binary (63)
Length: 8192
Max_length: 19
Decimals: 31
Flags: BINARY
+---------------------+
| TEST |
+---------------------+
| 2012-09-03 13:01:23 |
+---------------------+
1 row in set (0.00 sec)
mysql>
Suggested fix:
VAR_STRING is preferable IMHO - at least as long as the string returned is not too long. Scripts, application (GUI tools in particular) may invoke special code and interfaces to handle BLOBs/TEXTs.
But why 5.0 returns "BINARY" flag I do not understand! Anyway this is history now.
(I am setting the client category. If this is a server issue then please change)