| Bug #11974 | TEXT column with binary collation appears to be a BLOB | ||
|---|---|---|---|
| Submitted: | 15 Jul 2005 18:18 | Modified: | 28 Jul 2005 16:57 |
| Reporter: | Dean Ellis | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S4 (Feature request) |
| Version: | 4.1.13 BK | OS: | Windows (Windows/Linux) |
| Assigned to: | Alexander Barkov | CPU Architecture: | Any |
[15 Jul 2005 20:10]
MySQL Verification Team
On Windows: C:\temp>bug11974 Server version: 4.1.13-nt Name: a FIELD_TYPE: 252 BLOB_FLAG: 1 BINARY_FLAG 0 Name: b FIELD_TYPE: 252 BLOB_FLAG: 1 BINARY_FLAG 1 Name: c FIELD_TYPE: 252 BLOB_FLAG: 1 BINARY_FLAG 1 On Linux; miguel@hegel:~/dbs/4.1$ ./bug11974 Server version: 4.1.13-debug-log Name: a FIELD_TYPE: 252 BLOB_FLAG: 1 BINARY_FLAG 0 Name: b FIELD_TYPE: 252 BLOB_FLAG: 1 BINARY_FLAG 1 Name: c FIELD_TYPE: 252 BLOB_FLAG: 1 BINARY_FLAG 1
[23 Aug 2005 10:43]
Sergei Golubchik
They are not completely the same, collation of the blob is 'binary', while collation of the text column is not.
[28 Jun 2007 11:37]
Andrea Valassi
I understood from the manual that it is now possible to distinguish TEXT BINARY and BLOB via the charsetnr variable: "To distinguish between binary and non-binary data for string data types, check whether the charsetnr value is 63. If so, the character set is binary, which indicates binary rather than non-binary data. This is how to distinguish between BINARY and CHAR, VARBINARY and VARCHAR, and BLOB and TEXT." (http://dev.mysql.com/doc/refman/5.0/en/c-api-datatypes.html). I tried this in a similar case to the one reported in this bug, and it seems to work. Is this the recommended solution? Cheers, Andrea
[29 Jun 2007 5:59]
Alexander Barkov
Yes, this is a recommended solution. For example, Java and ODBC connectors use this approach.
[29 Jun 2007 17:04]
Mark Matthews
Yes, that's what Connector/J and Connector/ODBC do, but only because we don't expose a concrete type for TEXT like we should, and it causes all kinds of interesting corner cases related to the output of functions and temporary tables. The real answer is to have a _real_ type in the protocol for TEXT types.
Description: Protocol apparently returns identical information for TEXT column with utf8_bin collation as for a BLOB column (FIELD_TYPE_BLOB, with flags BLOB_FLAG and BINARY_FLOG), making it impossible for a client (via C API or Connector/J, etc) to determine whether the column is actually TEXT or BLOB. How to repeat: USE test; DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( a TEXT CHARACTER SET utf8 COLLATE utf8_general_ci, b TEXT CHARACTER SET utf8 COLLATE utf8_bin, c BLOB ); #include <stdlib.h> #include <stdio.h> #include "mysql.h" int main() { MYSQL* m; MYSQL_RES* r; MYSQL_FIELD* f; int i, nf; m= mysql_init(NULL); mysql_real_connect(m,"localhost","root","","test",0,NULL,0); mysql_query(m,"SELECT * FROM t1"); r= mysql_store_result(m); f= mysql_fetch_fields(r); nf= mysql_num_fields(r); for (i= 0; i < nf; i++ ) { printf("Name: %s FIELD_TYPE: %d BLOB_FLAG: %d BINARY_FLAG %d\n", f[i].name, f[i].type, (f[i].flags & BLOB_FLAG) == BLOB_FLAG, (f[i].flags & BINARY_FLAG) == BINARY_FLAG ); } mysql_free_result(r); mysql_close(m); return 0; } Suggested fix: Probably should have a distinct field type for TEXT columns, but perhaps could avoid setting BLOB_FLAG with column is not in fact a BLOB column.