text in "varbinary" text fields

[expired user #3797]'s profile image [expired user #3797] posted 14 years ago in General Permalink
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
ansgar's profile image ansgar posted 14 years ago Permalink
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).
[expired user #3797]'s profile image [expired user #3797] posted 14 years ago Permalink
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"

[expired user #3797]'s profile image [expired user #3797] posted 14 years ago Permalink
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"
ansgar's profile image ansgar posted 14 years ago Permalink
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.