master/ejabberd — Issue #1: mod_roster_odbc  issue
Author: interactiveobject
Created: 2011-10-28 10:28:13+00:00
State: open

Hi,
I currently porting your mongosql implementation to ejabberd--2.1.8-2.el6, everything seems work fin except when I use mod_roster_odbc.

I'm getting a **SQL transaction restarts exceeded** exception when **ejabberd_odbc** send query to mongodb, here is the logs :

```
=INFO REPORT==== 28-Oct-2011::12:02:11 ===
D(<0.298.0>:ejabberd_odbc:444) : MongoDB, Send query
["select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='",
"test1","' and jid='","test@localhost","';"]

=INFO REPORT==== 28-Oct-2011::12:02:11 ===
D(<0.298.0>:ejabberd_odbc:446) : MongoDB, Received result
{selected,["username","jid","nick","subscription","ask","askmessage","server",
"subscribe","type"],
[]}

=INFO REPORT==== 28-Oct-2011::12:02:11 ===
D(<0.298.0>:ejabberd_odbc:444) : MongoDB, Send query
["update ","rosterusers"," set ",
["username='test1'",
[[", ","jid='test@localhost'"],
[", ","nick=''"],
[", ","subscription='N'"],
[", ","ask='I'"],
[", ","askmessage=''"],
[", ","server='N'"],
[", ","subscribe=''"],
[", ","type='item'"]]],
" where ",
["username='","test1","' and jid='","test@localhost","'"],
";"]

=INFO REPORT==== 28-Oct-2011::12:02:11 ===
D(<0.298.0>:ejabberd_odbc:446) : MongoDB, Received result
{error,null}

=ERROR REPORT==== 28-Oct-2011::12:02:11 ===
E(<0.298.0>:ejabberd_odbc:399) : SQL transaction restarts exceeded
** Restarts: 10
** Last abort reason: null
** Stacktrace: [{ejabberd_odbc,sql_query_t,1},
{odbc_queries,update_t,4},
{mod_roster_odbc,'-process_subscription/6-fun-0-',8},
{ejabberd_odbc,outer_transaction,3},
{ejabberd_odbc,run_sql_cmd,4},
{p1_fsm,handle_msg,10},
{proc_lib,init_p_do_apply,3}]
** When State == {state,<0.383.0>,mongo,30000,"localhost",1000,
{0,{[],[]}}}

=INFO REPORT==== 28-Oct-2011::12:02:11 ===
D(<0.298.0>:ejabberd_odbc:444) : MongoDB, Send query
"rollback;"

=INFO REPORT==== 28-Oct-2011::12:02:11 ===
D(<0.298.0>:ejabberd_odbc:446) : MongoDB, Received result
{updated,0}

```

Registering a new user with ejabberd console seems to have to same issue :

```
ejabberdctl register test2 realtimewebapp.com test
User test2@realtimewebapp.com already registered at node ejabberd@s15821817
```

```
=INFO REPORT==== 28-Oct-2011::12:16:16 ===
D(<0.306.0>:ejabberd_odbc:444) : MongoDB, Send query
["select password from users where username='","test3","';"]

=INFO REPORT==== 28-Oct-2011::12:16:16 ===
D(<0.306.0>:ejabberd_odbc:446) : MongoDB, Received result
{selected,["password"],[]}

=INFO REPORT==== 28-Oct-2011::12:16:16 ===
D(<0.310.0>:ejabberd_odbc:444) : MongoDB, Send query
["insert into users(username, password) values ('","test3","', '","test", "');"]

=INFO REPORT==== 28-Oct-2011::12:16:16 ===
D(<0.310.0>:ejabberd_odbc:446) : MongoDB, Received result
{error,null}
```
Thank you in advance for you answer
Cheers
Y