Configuring phpmyadmin to show Drupal 7 BLOB data

MichaelCole's picture

Hi,

This took me a bit to figure out and I thought I'd share it. Hopefully someone else finds it useful.

Drupal 7 serializes php data into long-blob fields, instead of just saving strings like in D6. This is great, because now D7 can store and retrieve arrays, not just strings. Hurray!

Unfortunately, out-of-the-box phpMyAdmin does not show this data as text, it just shows [BLOB - 14B]. Meh!

This is especially annoying for the "variables" table, where I'd like to be able to browse the actual data, not just how big it is. Fortunately, phpMyAdmin can be coaxed into showing this blob data in a nice way.

I work on a pre-made development environment called Drupal Quickstart, and wanted to include a solution to this problem. I'm posting it here as it may be useful to people who don't want to use Quickstart.

Anyways here's how to do it - add these lines to the bottom of your phpMyAdmin config file:

# Show 1000 rows instead of 30 by default
$cfg['MaxRows'] = 1000;
# Show BLOB data as a string not hex.
$cfg['DisplayBinaryAsHex'] = false;
# Show BLOB data in row detail pages.
$cfg['ProtectBinary'] = false;
# Show BLOB data on table browse pages.  Hack to hardcode all requests.
$_REQUEST['display_blob'] = true;

On Unix, you'll likely want to edit /etc/phpmyadmin/config.inc.php.
On Windows, you'll want to install Drupal Quickstart :-D

The first line (maxrows) tweaks the default number of rows shown in the "table browse" pager.

The second line tells phpMyAdmin to show the BLOB data as strings. This may cause problems for other BLOB data, but works for serialized php data.

The third line shows the BLOB data on the row detail pages. It's editable, but be careful editing - you could wedge your site.

The fourth line is the tricky part. There's no config parameter for showing BLOBS on the "table browse" page. We can hack it by hacking the web request itself. This hardcodes "display_blob" for every request - making the setting in the "options" menu above the records useless. (Did you know there was a little options menu above the records? I'd never noticed it before this!)

Anyways, that's how to do it. If you'd like it done already, here's some Unix bash-fu to configure this automatically:

echo "
# Show 1000 rows instead of 30 by default
\$cfg['MaxRows'] = 1000;
# Show BLOB data as a string not hex.
\$cfg['DisplayBinaryAsHex'] = false;
# Show BLOB data in row detail pages.
\$cfg['ProtectBinary'] = false;
# Show BLOB data on table browse pages.  Hack to hardcode all requests.
\$_REQUEST['display_blob'] = true;
" | sudo tee -a /etc/phpmyadmin/config.inc.php

This works for phpMyAdmin 3.3.7deb5build0.10.10.1.

Fortunately, the $_REQUEST part came to me in a dream.
Unfortunately, that dream was the day after I released Quickstart 0.9.1.
This will be included in Quickstart 1.0. If you'd like it in your current version (0.9.1 or below), just open a terminal, and paste in the bash-fu above.

Have any other suggestions?

Cheers,

Mike

Originally posted here.

Comments

Clever. Thanks for

texas-bronius's picture

Clever. Thanks for sharing!
Of course phpMyAdmin is a great way to go if you've got it and nothing else, but I am always surprised to see so many people using it when there are desktop gui tools for mysql like Sequel Pro (mac) and (at least there used to be?) one right from dev.Mysql itself.

In addition to it not being

Elijah Lynn's picture

In addition to it not being available on GNU/Linux systems (Ubuntu). It is also not open source. That is probably a pretty big reason.

Hmm, appears I am wrong, it has a MIT license => http://www.sequelpro.com/legal

MySQL Workbench

rlnorthcutt's picture

Good point, Bronius!

I have really been enjoying MySQL Workbench lately. Its available for all major OSes and allows you to connect to remote databases as well as store the login for each... very handy. Its saved me a few times where PHPMyAdmin wasn't available and my only other option was command line mysql (ouch).

http://wb.mysql.com/

Its worth checking out, especially for Windows users out there...

ron

Custom Drupal Website

Peace, Compassion, Prosperity

I use Workbench a lot,

Jamie Holly's picture

I use Workbench a lot, especially for modelling new schemas. It's a powerful tool and gives me that familiar interface of doing database design in other apps, like Access, Foxpro, Alpha, etc.

I think a lot of people keep PHPMyAdmin around just because it is quick and easy. I run it on my test and development servers simply because I can quickly get in there and change something without having to wait for WB to start up. PHPMyAdmin has also been adding some really nice features. One really sweet feature that's coming in 3.5 (it's in beta now) is synchronization. You can have PHPMyAdmin connect to two databases and synchronize the whole database or selected tables. I've been using this for a couple of months now between my development and testing servers and it is a really awesome time saver.


HollyIT - Grab the Netbeans Drupal Development Tool at GitHub.

I tried MySQL Workbench years

ergophobe's picture

I tried MySQL Workbench years ago and it kept crashing my system and looked elsewhere. I discovered SQLYog, which has both a community and a paid version. I've been using it ever since, though there are some limitations in terms of http tunneling (paid version only I think).

http://code.google.com/p/sqlyog/

It definitely shows serialized data in the variables table as editable text, not binary.

Downloading Workbench now to take it for a spin

This did not work out for me...

redhatmatt's picture

Probably my PhpMyAdmin version (3.3.9.2) that came with MAMP / MAMP Pro (2.03.)

Went with Sequel Pro instead - which does great! Mysql Workbench has messed with my system stability in the past.

Thanks!

Editing serialized data

aangel's picture

This tool might be handy:
http://unserialize.net/serialize

It converts between the text blob form (hard to read, have to modify string lengths manually) to PHP Array style. Thus:

a:5:{s:5:"title";a:1:{s:5:"value";s:11:"[site:name]";}s:9:"canonical";a:1:{s:5:"value";s:10:"[site:url]";}s:11:"description";a:1:...

becomes:

array (
  'title' =>
  array (
    'value' => '[site:name]',
  ),
  'canonical' =>
  array (
    'value' => '[site:url]',
  ),
...

Also handles json and yaml.


Andre Angelantoni
Founder, PostPeakLiving.com

Re: Editing serialized data

tsssystems's picture

http://unserialize.net/serialize seems to be for sale. In any case, it's not a functional site anymore. I was able to find the same functionality at:
http://i-tools.org/unserialize/exec

Using this site in conjunction with MySQL Workbench allowed me to change serialized data.

Navicat Lite

knalstaaf's picture

I was able to edit BLOBs using Navicat Lite.

Just click the BLOB in the row (you have to click the actual BLOB, not just the row) and hit the "Text" icon. There you go (screen).

Just in case it could help anyone...

Search and replace mass data

druvision's picture

Instead of converting data one by one, it's usefull to put it all in a SQL command, if you need to do mass search and replace.

To convert a blob to text, use the MySql convert function. Then you have to save it into the database and convert it again to blob - but this is handled automatically by MySQL. So the following query would do the trick:

UPDATE metatag
SET data = replace(CONVERT(data USING utf8), 's:11:"[site:name]"', 's:20:"[current-page:title]"');

Hope this helps

I am trying to get my site out of maintenance mode

TheoRichel's picture

but I need to do that through the database, just change a 1 into a 0 in the Variable table. But this is a Blob and since this is shared hosting I cannot access the cfg-file as outlined above.
Question: can I safely change this blob field to a text field, edit it and then change it back to blob?

Many thanks

The answer is

TheoRichel's picture

yes I can. I just did so and my site is out of maintenance mode.

Bookmarklet that shows [BLOB - %] link contents

Leksat's picture

Here is a bookmarklet I use:

/* helper stuff is omitted */
$('td > a:contains("[BLOB")', window.frames["frame_content"].document).each(function() {
  var $link = $(this);
  var url = $link.attr('href');
  $.get(url, function(result) {
    $link.replaceWith($('<pre/>').text(JSON.stringify(unserialize(result), null, 2)));
  });
});

Just drag-n-drop the bookmark from here: http://jsfiddle.net/CYxvq/
(Works for me in Chrome, PMA 3.4.10)