mySQL database changes when I open a field in phpmyadmin, but I don't make any change

Question

I have a wordpress site, and a template with demo-data installed. When I open some fields up in phpmyadmin, even if I make no changes and select some other field, it does an update to the field. No problem, it updates nothing, because I made zero changes. What happens then is the wordpress site's front end goes haywire, because SOMETHING changed in the field I was simply examining. The field in question has a large chunk of HTML code in it, and quite a few ; and " characters in it...

I think when I open the field and close it, the update command parses what's in the field, and it see's the ; and " all over the place... I could be wrong. Anyway when I look at the data and copy it, either by mysql shell query or by phpmyadmin, and update it EXACTLY back into the DB like it was before, SOMETHING is changing, but even using a notepad++ compare plugin and looking at the data every way I can, there is no change to it...

Does this make any sense? It's driving me nuts!

This is a wordpress site using a rocket theme, with the gantry framework in case anyone is familiar...

Here is a video demonstration of this issue

https://www.youtube.com/watch?v=ljXTDKKmUUw

Ok, the answer seems to be, that if data is stored serialized in an array inside a field, it must be unserialized with php first to be read and serialized again with php to be written.

Apparently this is a common problem with wordpress, and causes allot of frustration when people try to do database migrations that change url lengths.

I learned allot more about this problem here: http://wpgarage.com/tips/data-portability-and-data-serialization-in-wordpress/

Answer 1

The thing is that the database entries in question do not just contain "text with a bunch of colons", this is PHP serialized data. True, it is text, but it must be regarded as sort of binary. When you make arbitrary changes to serialized data, e.g. changing a word to longer or shorter word, you break the data structure. When the associated code tries to deserialize the data afterwards, it fails. Moral: Do not edit serialized data carelessly, or you will break stuff.

Citing from the PHP documentation: Serialized strings shouldn't be even stored in TEXT fields.

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.

Anyways, there are ways to edit PHP serialized data if you adhere to the rules of the serialized values or adjust the corresponding structural information. There are even some specialized editors or online tools for this (I have not tested them).
To learn more about the structure of serialized data check the top comment of egingell in the linked function documentation on php.net, which describes the anatomy of a serialize()'ed value:

String -> s:size:value;

Integer -> i:value;

Boolean -> b:value; (does not store "true" or "false", does store '1' or '0')

Null -> N;

Array -> a:size:{key definition;value definition;(repeated per element)}

Object -> O:strlen(object name):object name:object size:{s:strlen(property name):property name:property definition;(repeated per property)}

String values are always in double quotes
Array keys are always integers or strings
    "null => 'value'" equates to 's:0:"";s:5:"value";',
    "true => 'value'" equates to 'i:1;s:5:"value";',
    "false => 'value'" equates to 'i:0;s:5:"value";',
    "array(whatever the contents) => 'value'" equates to an "illegal offset type" warning because you can't use an array as a key;
however, if you use a variable containing an array as a key, it will equate to 's:5:"Array";s:5:"value";', and attempting to use an object as a key will result in the same behavior as using an array will.

Answer 2

From the looks of the video it seems like you are using a pretty old PMA version. I would start updating PMA, PHP and MySQL to see if there was a problem with that specific version.