[ejabberd] Ejabberd mysql migration

Michael Ossareh michael at heysan.com
Mon Jun 23 22:37:57 MSD 2008

On Mon, Jun 23, 2008 at 6:45 AM, Sean Dilda <sean at duke.edu> wrote:
> Michael Ossareh wrote:
>> Hi guys,
>> We're migrating from mnesia to mysql. We get the following error when
>> running the migration process:
>> #42000You have an error in your SQL syntax; check the manual that
>> corresponds to your MySQL server version for the right syntax to use
>> near ';delete from users where username='1431905';insert into
>> users(username, password' at line
>> Does anyone have any idea's why? It looks to me as though the
>> submisssion of the back to back sql statements is making the server
>> unhappy. I know this can be done on the mysql CLI but I guess its down
>> to the client libs to see the >1 query and know to submit them
>> seperately. Could it be that the the erlang/odbc/mysql lib/module
>> isn't doing this correctly?
> It doesn't look like its the back to back as much as the second
> statement isn't complete.    What is the process you're going through to
> do this migration?

Hi, it was a complete statement. Mysql's error location is pretty hit
and miss. The problem was the queries being run back to back. ejd2odbc
exports mnesia tables into SQL. We were using its option to migrate
the data directly into mysql. However that failed, as discussed above.
There is an option to output it to a flat sql file and run it. That
worked for us.

mysql ejabberd < ejabberd.sql

and it worked. So it definitely looks like, to my relatively untrained
eye, that the erlang mysql/odbc lib doesn't process these statements

A note about this tool - it is super inefficient. For every insert
statement it creates it also creates two delete statements before that
(delete from rosterusers and delete from rostergroups). In our case
this was a fresh db so the need to parse, compile and run these two
queries meant, essentially, 3x more work than was needed.

Also, mysql imports benefit hugely using the bulk insert format.

insert into table values (x,y,z),(x2,y2,z2)...(xn,yn,zn);

Generally I only insert about 250, oracle has a hard limit of 256 I
believe, and I'll wrap two or three of those in begin/end transaction.
If I knew erlang I'd help out :( I hope someone with the coding skills
can take this knowledge and improve this process.

> _______________________________________________
> ejabberd mailing list
> ejabberd at jabber.ru
> http://lists.jabber.ru/mailman/listinfo/ejabberd

"Don't go through life, grow through life."
- Eric Butterworth

More information about the ejabberd mailing list