Etherpad-lite: Migrate data from dirtydb to mysql Anton Keks, 19 Oct 2011

Recently I've been playing with etherpad-lite, which is a port of Etherpad (a collaborative editor a'la Google Wave acquired by Google and then open-sourced) to node.js platform.

As my node.js project is getting ready for production use, I have decided to migrate from the default dirtydb to mysql. Dirtydb is a fun little database written in JavaScript that just appends key/value pairs to a json-formatted text files and then basically reads in backwards to find a matching key if you need to query it. Super-simple and works nicely. The author claims it can handle up to 1M records.

I would happily stay with dirtydb , but etherpad-lite adds new revisions of the pad on every character typed, so 1M records can be reached pretty quickly with it, so I have decided to move to mysql as a backend.

Etherpad-lite uses a DB abstraction library ueberDB (the name probably should have been überDB , but the author didn't have the 'ü' on his keyboard? ;-)), which basically mimics the dirtydb interface, but can use other backends, like mysql or sqlite for actual storage.

You configure etherpad-lite/ueberDB to use mysql in the settings.json file:   

"dbType" : "mysql",   
"dbSettings" :  {
    "user": "etherpad",
    "host": "localhost",
    "password": "etherpad",
    "database": "etherpad" 
   },
}

This already works provided you have created the necessary mysql database and user:

- mysqladmin create etherpad -u root -p
- grant all privileges on etherpad.\* to etherpad identified by 'etherpad';

But if you have been using the app with dirtydb for some time, you probably want to migrate the data from dirtydb to mysql , here is a simple script to do just that:

  var dirty = require("dirty")('../var/dirty.db');
  var db = require("./db/DB");
  db.init(function() {
      db = db.db;
      dirty.on("load", function() {
          dirty.forEach(function(key, value) {
              db.set(key, value);
          });
      });
  });

Save it as {etherpad-root}/node/dirty2mysql.js, then

- cd node
- node dirty2mysql.js

If it doesn't do anything, try installing the dirtydb module explicitly (not as a dependency of ueberDB):

- npm install dirty

And you're done!

Archived comments

grapsus 2012-02-07T10:45:10.864Z

Thank you ! The migration worked like a charm.

Sascha 2012-07-01T08:20:40.296Z

Hi there.I just tried to do this, but i got the ...

Hi there.I just tried to do this, but i got the error that die DB module is not present.My db Folder looks like drwxr-xr-x 2 root root 4.0K Jul 1 10:08 docroot-rw-r--r-- 1 root root 25 Mar 17 02:53 ds.json-rw-r--r-- 1 root root 695 Mar 17 02:53 index.jsdrwxr-xr-x 5 root root 4.0K Jul 1 10:08 node_modules-rw-r--r-- 1 root root 736 Jul 1 10:08 package.json-rw-r--r-- 1 root root 345 Mar 17 02:53 READMEHave you an idea?

Sascha Urbansky 2012-07-01T11:44:06.647Z

Hi there.Got it running. But at the end it goes ...

Hi there.Got it running. But at the end it goes to a mysql Error.[2012-07-01 13:41:20.527] [INFO] ueberDB - Flushed 103831 values/var/lib/nodejs/node-apps/etherpad-lite/src/node_modules/ueberDB/CacheAndBufferLayer.js:225 if(err) throw err; ^Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''{\"changeset\":\"Z:fbt>2|q=1yc=9y*2+2$ei\",\"meta\":{\"author\":\"a.OvafnujGQrg' at line 1 at Function._packetToUserObject (/var/lib/nodejs/node-apps/etherpad-lite/src/node_modules/ueberDB/node_modules/mysql/lib/client.js:387:11) at Query._handlePacket (/var/lib/nodejs/node-apps/etherpad-lite/src/node_modules/ueberDB/node_modules/mysql/lib/query.js:33:33) at Client._handlePacket (/var/lib/nodejs/node-apps/etherpad-lite/src/node_modules/ueberDB/node_modules/mysql/lib/client.js:312:14) at Parser. (native) at Parser.emit (events.js:67:17) at /var/lib/nodejs/node-apps/etherpad-lite/src/node_modules/ueberDB/node_modules/mysql/lib/parser.js:71:14 at Parser.write (/var/lib/nodejs/node-apps/etherpad-lite/src/node_modules/ueberDB/node_modules/mysql/lib/parser.js:576:7) at Socket. (native) at Socket.emit (events.js:67:17) at TCP.onread (net.js:367:14)Do you have any idea why?

John McLear 2012-10-25T16:54:34.562Z

Heya, I just put this into core :) It works slightly different but it's available in the bin/ folder and is executed as node bin/migrateDirtyDBtoMySQL.js