Re: Migration from Dirty.db to MySQL

[Peter Martischka]

Sounds like an error that is really really painful to debug. Try to
only migrate the first 100 lines of your file, then the next 100 and
so on. It would be great if you could nail it down to own specific
line

On 1 July 2012 12:56, Sascha Urbansky <chick...@googlemail.com> wrote:
> Hi there,
>
> i tried to Migrate Dirty.db to mysql using
> http://blog.codeborne.com/2011/10/etherpad-lite-migrate-data-from-dirtydb.html
>
> But i got the following 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.<anonymous> (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.<anonymous> (native)
> at Socket.emit (events.js:67:17)
> at TCP.onread (net.js:367:14)
>
>
> Can you help me??

[John McLear]

You could add this to the Wiki on Github :)

On Sat, Apr 27, 2013 at 7:50 AM, olibou <oli...@gmail.com> wrote:

I run also into troubles .... probably my fault since I've keeped my dirtydb too long and forgot to switch to mysql ... probably due to success of etherpad .... nevertheless

I had to migrate to mysql ... and after trying to use migrateDirtyDBtoMySQL.js without success I've decided to do it in perl. 

So, here is my small perl script :

#!/usr/bin/perl

use strict;

use DBI;

my $dbh = DBI->connect("DBI:mysql:database=etherpad;host=localhost", "mymysqlid", "mymysqlpasswd",) or die;

$dbh->prepare("TRUNCATE TABLE store")->execute();

$dbh->prepare("SET CHARACTER SET utf8")->execute();

open(F,"var/dirty.db") or die;

while (<F>) {

    if (m|\{\"key\":\"(.*)\",\"val\":(.*)\}|) {

my ($k,$v) = ($1,$2);

my $sth = $dbh->prepare("SELECT `key` FROM store WHERE `key` = ?");

$sth->execute($k) or die;

my @a = $sth->fetchrow();

if ($a[0]) {

   $sth = $dbh->prepare("UPDATE store set `value` = ? WHERE `key` = ?");

   $sth->execute($v,$k) or die;

} else {

   $sth = $dbh->prepare("INSERT INTO store (`key`,`value`) VALUES (?,?)");

   $sth->execute($k,$v) or die;

}

    } else {

die "Err!\n";

    }

}

close F;

Le dimanche 1 juillet 2012 13:56:40 UTC+2, Sascha Urbansky a écrit :

Hi there,

i tried to Migrate Dirty.db to mysql using http://blog.codeborne.com/2011/10/etherpad-lite-migrate-data-from-dirtydb.html

But i got the following 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.<anonymous> (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.<anonymous> (native)
    at Socket.emit (events.js:67:17)
    at TCP.onread (net.js:367:14)


Can you help me??

--
You received this message because you are subscribed to the Google Groups "Etherpad Open Source Discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to etherpad-open-source...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Bryce Lynch]

On Sat, Apr 27, 2013 at 6:42 AM, John McLear <john...@gmail.com> wrote:

You could add this to the Wiki on Github :)

...or fork the repository, add the Perl script, and issue a pull request.

--
The Doctor [412/724/301/703] [ZS]
https://drwho.virtadpt.net/
"I am everywhere."

[Gautam Somani]

I tried your script to migrate dirty.db from a linux machine into mysql on windows machine,  and it game me no errors, but when I started etherpad, it game me following error:

C:\etherpad\node_modules\ep_etherpad-lite\node_modules\ueberDB\mysql_db.js:73
            throw err;
                  ^
Error: Duplicate entry 'pad2readonly:Dil-PB' for key 'PRIMARY'
    at Function.Client._packetToUserObject (C:\etherpad\node_modules\ep_etherpad-lite\node_modules\ueberDB\node_modules\mysql\lib\client.js:387:11)
    at Query._handlePacket (C:\etherpad\node_modules\ep_etherpad-lite\node_modules\ueberDB\node_modules\mysql\lib\query.js:33:33)
    at Client._handlePacket (C:\etherpad\node_modules\ep_etherpad-lite\node_modules\ueberDB\node_modules\mysql\lib\client.js:312:14)
    at Parser.EventEmitter.emit (events.js:88:17)
    at Parser.write.emitPacket (C:\etherpad\node_modules\ep_etherpad-lite\node_modules\ueberDB\node_modules\mysql\lib\parser.js:71:14)
    at Parser.write (C:\\etherpad\node_modules\ep_etherpad-lite\node_modules\ueberDB\node_modules\mysql\lib\parser.js:576:7)
    at Socket.EventEmitter.emit (events.js:88:17)
    at TCP.onread (net.js:403:14)
Press any key to continue . . .


Any clue why this came? Can there really be a duplicate entry related to primary key?

[Artur Marud]

One simple insert:

INSERT INTO etherpad.store VALUES ('MYSQL_MIGRATION_LEVEL','1');

That will fix your problem.