Recover MySQL from files

This is how I recovered my databases from a HD crash using Mac OS X. These included both MyISAM and InnoDB tables.

  1. Get the files, typically from a backup or booting into your machine/server in recovery mode, usually located @ /var/lib/mysql
  2. Read (if you own this file) mysql_upgrade_info, so you see which mysql version created the files.
  3. Install the same version in your machine. If needed, uninstall whatever exists (if you don’t need it, of course):
brew remove mysql
  1. Install the closest mysql version to that you found in mysql_upgrade_info (or whatever you guess is the version). See the available formulas and do it:
brew search mysql
brew install mysql@5.5
  1. I installed myself version 5.5, which is the one I needed.
  2. Now locate the place where the local files reside, typically @ /usr/local/var/mysql
  3. Zip this folder as a backup measure and remove all of its contents.
  4. Move inside all the files and folders you got in step 1.
  5. Launch mysql:
/usr/local/opt/mysql@5.5/bin/mysql.server start
  1. Connect and mysqldump whatever you need.

Hints to manipulate Apple’s Photos programmatically

Today we have many tools to handle data in the cloud, but there is no tool in the market able to do many things you can think of, like having a centralized photo DB in your computer coming from different devices (ie, merging your iPhone photos with those of your wife’s).

So, some hints are coming… (just hints, no final code, just read and adapt to your needs/circumstances). These are some useful locations:

/Users/<USER>/Pictures/Photos Library.photoslibrary/database/photos.db

And these for shared albums:

/Users/<USER>/Library/Containers/ Support/

/Users/<USER>/Library/Containers/ Support/

In the surrounding folders there are the original pictures and resampled versions (thumbnails and so on).

The databases are SQLite and you can manipulate them easilly. There are libraries in almost any programming language and you have a built-in sqlite3 command-line tool you can invoke from any scripting language, such as AppleScript, like this:

do shell script ¬
    "sqlite3 FILE.DB 'SELECT * FROM table;'"

This would work to get out binary data:

do shell script ¬
    "sqlite3 FILE.DB 'SELECT quote(blobfield) FROM table;'" & ¬
    " | cut -d \"'\" -f2 | xxd -r -p > /tmp/out.gz'"

Photos stores many things in binary plists, so the previous code will come in handy to extract that binary fields, which you can later uncompress and parse as regular XML data.

If you can’t read the DB files, don’t panic. Most probably they are locked (opened by another process). You can check it like this:

lsof | grep -e 'FILE.DB'

Most probably there are some sync services running and thus locking the databases. You can quit temporarily this process, do your stuff, and it will be relaunched after that. Ie, if the “offending” process is photolibraryd, you can:

kill -3 `ps xww | grep photolibraryd | grep -v grep`; sqlite3 FILE.DB 'SELECT * FROM table;'

More tips tomorrow!

HTML5 today: 1 code

After many years out of Flash and inside HTML5, I can dictate: nightmare.

It’s a world of possibilities but, regarding developers, a totally *ucking nightmare.

100% of customers think they need support for desktop computers and mobiles devices. This means a minimum of Chrome, IE, Firefox and Safari, in different environments (desktop, tablet, mobile) and OS’s (Win, Mac, iOS, Android, including Win 7 thru 10, Mac 10 thru 12, iOS 8 thru 11…), and usually as well the customer’s frontend app, which uses to be a Cordova-based app (also available for iOS, Android and desktop Win and Mac).

Regarding audio and video, you stick with mp3/ogg and mp4/ogv. Regarding programming, you rely mostly in general-use libraries such as jQuery. For the rest of functions, you create your own libraries, aimed to support around 40-50 different browsers/versions.

In the end, you spend in development a similar amount of time than you spent previously in Flash (mostly because Flash is still available for impossible tasks out of Flash, such as RAD —standing for Rapid Animation Development, instead of Application—, now with HTML5 output thanks to CreateJS).

But some times you spend around 100 thru 200% of the time with debugging tasks. The last time I had a bug in the customer’s iOS app. The code worked fine in the other 40-50 browsers, but not here. I had to re-make at home the app and simulate the customer’s code in order to replicate and debug the issue. This converted a simple 2-hours job into a complex 4-days job.

My advice to all my customers all this time is the following: force your users to use your own app. This way you develop and mantain 1 code.

Otherwise, take care of your problems. No! Your supported browser’s problems! The transition from IE10 to IE11 caused an issue which affected to almost 1000+ interactives to one of my customers. This customer had the luck to have my company as the responsible of such interactives, as we managed to resolve the issue quickly. However, this issue caused a bunch of negative comments in the different app stores. This is because my customer lets the browsers decide which work and which doesn’t, how and when.

Flash movies worked the same all around the browsers and operative systems: 1 code, 1 behavior.

This is just the first round. I wanna tell you more about this!

ASCII to compressed ASCII

Here is a funny experiment with javascript. It takes normal text as input and returns encoded ASCII text (from 0x20 thru 0xFF) with a 30% reduction in length, which can be decoded later to the original, with some exceptions/rules. It preserves only alphanumeric characters (except for some of them which are transliterated specially, like 1 = I or Z = 2).

This could be funny as a workaround in some situations, like circumventing the classical Twitter limit of 140 characters (would need the decoder on the other side, of course).

A few websites

A few websites I developed or helped to develop in the past few years: (dead) (dead) (dead)

And hundreds of web applications I can’t show because they reside in private areas 😉

I hope to resume my activities in this blog, as I have many things to share…

ACK Media in collaboration with Pescados

We are proud to announce that ACK Media (Amar Chitra Katha) will collaborate with Pescados in the production of Qilania.

They will use their proved expertise to bring to the net this unique virtual world for childs called Qilania.

Appart from being the leading company in India regarding entertainment and education for young people, they have published already a MMORPG called The Legend of Katha and a special website for children, called TinkleOnline.

We are sure we will make a very strong team 😉