distal-attribute
distal-attribute
distal-attribute
distal-attribute

text in "varbinary" text fields

User, date Message
Written by m.ardito
4 years ago
Category: General
7 posts since Tue, 09 Dec 08
Hi,

i was debugging a mediawiki installation, and noted for the first time that many table "text" fields are in "varbinary" data type. the page.page_title field content appears like "0x506167696E615F7072696E636970616C65"

if you look at the same field with SQLyog, another mysql db editor, you see the real text...

tried latest 5beta... nothing changes

is there a way to see the text data there ? thx!

Marco
Written by ansgar
4 years ago
4793 posts since Fri, 07 Apr 06
page_title is obviously a BLOB column, right? That's why HeidiSQL displays it as Hex, in order to avoid data loss caused by non-displayable characters.

However, if you update your build you can click the "View binary data as text" button (white page with purple "0x" on it).
Written by m.ardito
4 years ago
7 posts since Tue, 09 Dec 08
no, "page_title" has a "varbinary" column...

here is a csdv copy from data view in heidisql

"Field";"Type";"Null";"Key";"Default";"Extra"
"page_id";"int(10) unsigned";"NO";"PRI";NULL;"auto_increment"
"page_namespace";"int(11)";"NO";"MUL";"";""
"page_title";"varbinary(255)";"NO";"";"";""
"page_restrictions";"tinyblob";"NO";"";"";""
"page_counter";"bigint(20) unsigned";"NO";"";"0";""
"page_is_redirect";"tinyint(3) unsigned";"NO";"";"0";""
"page_is_new";"tinyint(3) unsigned";"NO";"";"0";""
"page_random";"double unsigned";"NO";"MUL";"";""
"page_touched";"binary(14)";"NO";"";"";""
"page_latest";"int(10) unsigned";"NO";"";"";""
"page_len";"int(10) unsigned";"NO";"MUL";"";""

so you see that

"page_title";"varbinary(255)";"NO";"";"";""

however, if i look at the data (it is acctually the title of the page, a text value), in the data grid, what i see issome thing like (again csv export):

"page_id";"page_namespace";"HEX(page_title)";"HEX(page_restrictions)";"page_counter";"page_is_redirect";"page_is_new";"page_random";"HEX(page_touched)";"page_latest";"page_len"
"11";"0";"4163717569737469";"";"3";"0";"1";"0.800053150333";"3230303930343033313235343538";"41";"89"
"9";"0";"416D6D696E69737472617A696F6E65";"";"4";"0";"1";"0.334868541664";"3230303930343033313235343138";"39";"89"
"10";"0";"416E616772616669636865";"";"3";"0";"1";"0.899325140168";"3230303930343033313235343334";"40";"89"
"40";"2";"41726469746F";"";"1";"1";"1";"0.886099289867";"3230303931303238303930343239";"119";"36"
"38";"2";"41726469746F2F4C696272692F43565F4D6172636F5F41726469746F";"";"5";"0";"1";"0.532049420005";"3230303930343039313035393135";"117";"105"
"36";"2";"41726469746F2F4C696272692F5072696D6F";"";"2";"0";"1";"0.89982026075";"3230303930343038303934383430";"115";"76"
"39";"2";"41726469746F2F4C696272692F50726F7661";"";"6";"0";"1";"0.932184248732";"3230303930343039313532303030";"118";"227"
"35";"2";"41726469746F2F4C696272692F74657374";"";"1";"0";"1";"0.797072618191";"3230303930343038303832343532";"114";"103"
"49";"0";"4361726963616D656E746F5F61737365676E617A696F6E693A5F736368656D615F64656C6C655F6F706572617A696F6E69";"";"22";"0";"0";"0.434797637293";"3230303930353037303934333139";"201";"10426"
"48";"0";"4361726963616D656E746F5F61737365676E617A696F6E693A5F7574696C6974795F6361726963614F726541737365676E617465";"";"4";"0";"1";"0.820733557914";"3230303930353037303735383236";"183";"10948"
"42";"0";"4361726963616D656E746F5F61737365676E617A696F6E695F6461695F7665636368695F666F676C695F457863656C";"";"75";"0";"0";"0.425042834455";"3230303930353037303830333336";"182";"2051"
"50";"2";"436F6C756363692F4C696272692F4361726963616D656E746F5F61737365676E617A696F6E69";"";"2";"0";"1";"0.199692095478";"3230303930353037303933333533";"200";"277"

Written by m.ardito
4 years ago
7 posts since Tue, 09 Dec 08
i mean, if i look at the data in SQLyog, "page_title" has text values, and i can read them (eg: "this is the page title"), while in heidi i read something like "0x4163717569737469" instead of "this is the page title"
Written by ansgar
4 years ago
4793 posts since Fri, 07 Apr 06
Well, varbinary also has binary collation, so that's pretty the same as BLOB.

What would SQLyog do if there was some image file in your page_title field? Display text? Would be very wrong.
 

Please login to leave a reply, or register at first.