How to convert longblob to readable format

Question

I'm using ara for my ansible project to stock playbook output into database (Mysql). Some Tables are not readable i would like to know how to convert that in order to develop a php page to display thos values:

here's my table description :

mysql> desc data;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | varchar(36)  | NO   | PRI | NULL    |       |
| playbook_id | varchar(36)  | YES  | MUL | NULL    |       |
| key         | varchar(255) | YES  |     | NULL    |       |
| value       | longblob     | YES  |     | NULL    |       |
| type        | varchar(255) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

as you see the value column is longblob so the output is not clear:

mysql> select value from data;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| value                                                                                                                                                                                                                                         |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| xœmŽË
ƒ0å"¸kMÄG;àÊU«@±5b &!7
ýû&R
¥Åp3Ì$§'fåc’Â!{©” ¸x™ÁQ¢Í"¦ÐíùB©`€‹ãš
                                           b%so­päjTëÌb÷j½9c<×ð_yÑ”»2øaó¢Ipíg;âOºd¬Û€~˜†xÆi~_À¡Ï¿[M“u¼`‘ó*´îáWòìI=N                                                                                                                                                     |
| xœmŽË
ƒ0å"¸³&â£f_påªU Ø1““R
¥Åp3Ì$Çæ0
˜ä}–Â!©” 8{™ÃA¢Í#¦Ð©`€«ãšŒb#Ë`­päbTçÌjwj»:c<×ð_EÙTY|ŸUÁË6µ_ì„?銱þôÃ4Äã0ÎËûŽCñÝjë˜lšà%‹\Ô¡u
                                                                                                  ¿’'ìÂ=O   

i try to convert those data to use UTF-8 but it gives me null:

SELECT CONVERT(value USING utf8) FROM data;
+---------------------------+
| CONVERT(value USING utf8) |
+---------------------------+
| NULL                      |
| NULL                      |
| NULL                      |
| NULL                      |
| NULL                      |
| NULL                      |
| NULL                      |
| NULL                      |
| NULL                      |
| NULL                      |
| NULL                      |
| NULL                      |
| NULL                      |
| NULL                      |
| NULL                      |
| NULL                      |
| NULL                      |
+---------------------------+
17 rows in set, 18 warnings (0,00 sec)

Answer 1

I helped design some of those models :) (although I am no longer an active developer on the project).

Have you considered just using the Ara web interface as the UI for this data? It's generally a bad idea to poke directly at the database like this because it typically hasn't been designed as a stable API: there's an excellent chance that some future update will break your code, because the assumption is that only ARA is accessing the database.

In any case:

In order to save space, many of the values stored in the database are compressed using Python's zlib.compress method. This is handled by the CompressedData type, which looks like this:

class CompressedData(types.TypeDecorator):
    """
    Implements a new sqlalchemy column type that automatically serializes
    and compresses data when writing it to the database and decompresses
    the data when reading it.
    http://docs.sqlalchemy.org/en/latest/core/custom_types.html
    """
    impl = types.LargeBinary

    def process_bind_param(self, value, dialect):
        return zlib.compress(encodeutils.to_utf8(jsonutils.dumps(value)))

    def process_result_value(self, value, dialect):
        if value is not None:
            return jsonutils.loads(zlib.decompress(value))
        else:
            return value

    def copy(self, **kwargs):
        return CompressedData(self.impl.length)

You will need to use the zlib.decompress method -- or the php equivalent -- to read those values. I am not a PHP developer, but it looks as if PHP as a zlib module.