Why is some data stored as a blob?

[AndyB]

When I use phpmyadmin to look at the data_registry table, I see the data_value field the information is stored as a blob. I have two questions:

1) Why is this data stored as a blob?
2) How can I view this data?

 

[Jeremy]

http://stackoverflow.com/questions/7071662/mysql-text-vs-blob-vs-clob

 

[AndyB]

Hi King Kovifor,

The link you provided doesn't seam to address my specific questions. If I'm not mistaken the data in the data_registry table is text based. So not sure why blob storage was used.

 

[Jeremy]

data_registry is serialized data. You don't want to encode, hence the blob (you handle encoding software side)

http://stackoverflow.com/questions/10224699/blob-vs-text-in-forum

 

[AndyB]

Thank you once again for the link.

But it still has me scratching my head. From what I can tell the data in the data_registry table is simple text. Why is it being stored as a blob?

Also it would be nice to know how to look at this blob data using phpmyadmin.

 

[Jeremy]

Its being stored as text as its serialized data (coming from an array or object in PHP). You don't want to mess with the encoding of serialized data (as stated above). You have to run it through unserialize() and var_dump() in PHP to easily be able to read / use the data.

 

[AlexT]

But it still has me scratching my head. From what I can tell the data in the data_registry table is simple text. Why is it being stored as a blob?

Hi Andy, as King Kovifor said, it is serialized content you're storing in the data_registry table. Content returned from the PHP serialize() function should be considered a byte-stream. It's not so much about encoding (although, if for some reason your serialized content contains bytes that are outside your encoding it would also affect the chosen table format), but about the fact that it could contain null bytes, which would, if you were using a TEXT field, cause text content to be truncated. Have a look here:

Return Values

Returns a string containing a byte-stream representation of value that can be stored anywhere.

Note that this is a binary string which may include null bytes, and needs to be stored and handled as such. For example, serialize() output should generally be stored in a BLOB field in a database, rather than a CHAR or TEXT field.

 

[Jeremy]

Didn't realize it could contain null values, but that works too. Its been quite some time since I actually had to use serialize().

 

[CTXMedia]

Okay, great - but for me to understand the different I need to know what serialized data is? Is this a long string of data - containing the content of many fields - that is separated out into it's individual components when it is de-serialised? Sort of like storing an array in its native state?

So instead of having a table with 30 fields you can store the same data in a serialised BLOB?

Like maybe having all the Custom user profile fields in one place - one BLOB?

If so, how do you get at individual parts of a BLOB?

 

[Jeremy]

http://php.net/manual/en/function.serialize.php

Basically it creates a state of data (except resource-type). Upon unserialization, you get an identical copy of what you serialized. Its useful for complex data (that'd take a long time to compute), or variable data sets (custom profile fields are serialized, IIRC) that you can't predict the names or amount, or objects where you update instance variables.

If you want to get individual parts of a serialized array you need to unserialize it for use in your code. BLOB is just a storage method and considered a binary string.

 

[AndyB]

Thank you AlexT and King Kovifor for your explanations.

 

[AndyB]

So instead of having a table with 30 fields you can store the same data in a serialised BLOB?

Like maybe having all the Custom user profile fields in one place - one BLOB?

I think this is exactly the reason why blob's are used in the xf_data_registry table. In addition you can store the variable as an array.

 

[feldon30]

I'm still curious what the advantage of a *binary* format was for textual data. It's frustrating that JSON or another human-readable format could not have been used. There are some transforms I'd like to do with Custom User Fields that I wanted to do without running the data through PHP.

 

[Jeremy]

The data being stored there is an array, and not a string, instead of having to do table manipulations to give each custom field a column.