Getting out of a Jam Quickly

We’ll be putting some powerful Linux CLI code bits on this page. The intention is to learn how to manage various types of data and manipulate data quickly.

Outputting binary data from MySQL CLI safely and processing it

In this example, we have stored a compressed JSON string using LZ4 and need to uncompress it with some ninja code

mysql -u user -ppassword database -NB -e 'SELECT HEX(column) FROM table LIMIT 1' | xxd -r -p | perl -MCompress::LZ4 -e 'undef $/;print uncompress(<>)'

What’s happening here?

First, we use mysql’s batch mode (-B flag) along with suppressing column headers (-N flag). The -e flag is the start of the SQL command. The HEX function turns the output into hexadecimal. xxd -r -p converts the hex into binary and prints it. Finally, we use Perl to uncompress the LZ4 blob and print it. There are easier ways to do this for LZ4, but this is a proof of concept of how to safely handle binary data from MySQL CLI — which can be very difficult to do. Also, instead of using “undef $/”, you could use the Perl flag -0777 before MCompress but that is pretty esoteric. If you’re interested in learning more perl runtime flags, check out the Perl runtime document.

We could eliminate the step using xxd completely by using Perl’s pack function. This would be the preferable method because you could pipe more than one row into the decompressor.Observe:

mysql -u user -ppassword database -NB -e 'SELECT HEX(column) FROM table LIMIT 1' | perl -MCompress::LZ4 -lne 'print uncompress(pack("H*",$_))'