Recovering auto-saved files in MySQL Workbench

Posted on

MySQL workbench is an open source tool for designing databases. As version 6.0.8, it is one of those programs where you need to save often, because the window you are working in will vanish every couple of hours.

Bug #1: Can’t recover files that weren’t saved

I was unlucky enough to have forgotten to save my work when it crashed today, and found this nasty flaw in the auto-recover feature:

Auto-save model interval: An open model that has not been saved will automatically be saved after this period. On loading a model file, MySQL Workbench will notify the user if the file was not previously saved correctly, due to a crash or power failure. MySQL Workbench can then attempt to recover the last auto-saved version. For automatic recovery to be available for a new file, it will have to have been saved at least once by the user.

Uh oh! The file hadn’t been saved yet, so it’s gone right? According to wb_model_file.cpp, this is not the case. The auto-save file is always written, but the recovery process wont be started until you try to use it again (which will never happen if you don’t have an old saved version):

/* Auto-saving
 *
 * Auto-saving works by saving the model document file (the XML) to the expanded document folder
 * from time to time, named as document-autosave.mwb.xml. The expanded document folder is
 * automatically deleted when it is closed normally.
 * When a document is opened, it will check if there already is a document folder for that file
 * and if so, the recovery function will kick in, using the autosave XML file.
 */

So under ~/.mysql/workbench/, I found a newmodel.mwbd folder. Workbench files are .zip files in disguise, so I compared it to a test file. It had all the same content, but with a document-autosave.xml, rather than a document.xml (see test file below):

Test archive

Cool, so I’d just rename the file, compress the whole lot and make it a .mwb? No such luck.

Bug #2: File Roller can’t compress ‘@’ files

Possibly because of the -@ command-line option in the zip command, File Roller refused to work with these files.

File Roller bug

Luckily, the document.mwb.xml file alone is enough for the file to be recognised and recovered from the auto-saved files:

File is recovered

The take-away from this? Save your work. In 2014, you still can’t count on auto-save to do this!

Transforming between SQL dialects

Posted on

I recently found myself Googling for some data voodoo. I have a web app which I want to work with locally, and the RDBMS requirements are a little bit incompatible.

Unfortunately, neither this impressive sed script nor this eloquent mix of sed, ruby and perl could do this with <100 syntax errors in the output, so I had to get creative. Here is what I have learned:

  • Simplify the problem. I decided to convert the structure to SQLite manually, as it is not likely to change. The parts you will need to convert often (thousands of INSERT statements) are the parts which are more important to have a script for. The extra mysqldump options for getting the data only, without nasty `backticks` were: 
    --compatible=ansi --skip-extended-insert --compact --no-create-info
  • Use sed to fix the escaping. MySQL escapes single quotes with ‘, and double quotes with ” but SQLite uses ” and “. This one-liner made the conversion: 
    sed -e "s/\'/''/g" -e 's/\"/"/g' db.sql > db.sqlite

The resulting file could have the structure cat‘d on to the start and imported into SQLite.

Loading Asterisk CDR into a database

Posted on

In the interests of accurate accounting, Asterisk creates a Master.csv, logging all calls and a few things about them.

This page on wiki.asterisk.org has a good breakdown of what all the fields mean.

I put together this MySQL table so that the data can be processed for data-analysis, accounting, or whatever it is that the data is needed for.

-- Code to create MySQL table for Asterisk CDR data
-- Field descriptions from:
--   https://wiki.asterisk.org/wiki/display/AST/CDR+Fields
CREATE TABLE cdr (
    accountcode VARCHAR(256) COMMENT 'What account number to use, (string, 20 characters)',
    src VARCHAR(80) COMMENT 'CallerID number',
    dst VARCHAR(80) COMMENT 'Destination extension',
    dcontext VARCHAR(80) COMMENT 'Destination context',
    clid VARCHAR(80) COMMENT 'CallerID with text',
    channel VARCHAR(80) COMMENT 'Channel used',
    dstchannel VARCHAR(80) COMMENT 'Destination channel if appropriate',
    lastapp VARCHAR(80) COMMENT 'Last application if appropriate',
    lastdata VARCHAR(80) COMMENT 'Last application data (arguments)',
    tsstart DATETIME COMMENT 'Start of call (date/time)',
    tsanswer DATETIME COMMENT 'Answer of call (date/time)',
    tsend DATETIME COMMENT 'End of call (date/time)',
    duration INT(11) COMMENT 'Total time in system, in seconds (integer), from dial to hangup',
    billsec INT(11) COMMENT 'Total time call is up, in seconds (integer), from answer to hangup',
    disposition ENUM('ANSWERED', 'NO ANSWER', 'BUSY') COMMENT 'What happened to the call',
    amaflags ENUM('DOCUMENTATION', 'BILL', 'IGNORE') COMMENT 'What flags to use, specified on a per channel basis like accountcode.',
    uniqueid VARCHAR(32) COMMENT 'Unique Channel Identifier',
    userfield VARCHAR(256) COMMENT 'user field: A user-defined field, maximum 255 characters'
) ;

The second-last field is not present on all installations, but it is on mine. The version I’m on is:

asterisk:~# asterisk -V
Asterisk 1.8.10.1~dfsg-1ubuntu1