[JDEV] Changing database structure in xdb_sql

[raditha dissanayake]

Hi,
there is another odity in jabber+xdb_sql (i am not complaining here 
xdb_sql is a wonderful piece of software) that results in mutliple xdb 
set queries being sent very very occaisionaly. I have seen it happen but 
haven't been able to track down the code that results in the bug. Most 
of the time it occurs when the first query takes a bit of time to 
execute. Please go through your dump and try to see if you get two such 
queries.

Since you are using mysql you can dig down to the next layer  and add 
your debug info into xdbmysql_query() function in xdb_sql_mysql.c this 
obviously is where the query is actualy being executed. Once again 
please keep an eye out for two queries popping out. Particular piece of 
code i suggested logs to error stream so you can still see it if you 
start the server without the -D option. That makes it easier to sift 
through the messages.



Bruce Heller wrote:

>Well when I log the query I get :
>
>20020923T11:21:34: [alert] (xdb_sql_backend.c:92): the query is: UPDATE
>users SET password = 'azerty' WHERE jid =
>'cvxnjknkj at chablis.terravirtual.net'
>
>The query is right, if I copy/paste it to MySQL (thru phpMyAdmin for
>example), it does work perfectly... But it still puts a "NULL" value to the
>password... :/
>
>
>--------------------------------
>Bruce Heller
>Ligne Directe: 01.47.66.55.64
>Fax: 01.47.66.55.54
>Standard: 01.47.66.55.50
>--------------------------------
>TERRA VIRTUAL
>73, rue de Saussure
>75017 PARIS
>-------------------------------
>----- Original Message -----
>From: "raditha dissanayake" <jabber at raditha.com>
>To: <jdev at jabber.org>
>Sent: Saturday, September 21, 2002 11:41 PM
>Subject: Re: [JDEV] Changing database structure in xdb_sql
>
>
>  
>
>>certainly does not look as if there is an error in your xml
>>what you can do is to add something like this
>>
>><code>
>>    log_error(ZONE,"the query is: %s",query);
>></code>
>>
>>to your sqldb_query() function in xdb_sql_backend.c that will tell you
>>what is the exact query that is being passed to the databse.
>>
>>As for your second question you will be better off using a database that
>>supports stored procedures rather than trying to use two separate queries.
>>
>>
>>Justin Georgeson wrote:
>>
>>    
>>
>>>You created your database with jid instead of username, right? Did you
>>>create it manually or edit one of the sample configs and create off
>>>      
>>>
>that?
>  
>
>>>Bruce Heller wrote:
>>>
>>>      
>>>
>>>>Hello. I wanted to use a slighty different database architecture with
>>>>xdb_sql, I modified the xdb_sql.xml according to the new layout of the
>>>>tables. But it doesn't work as it should.
>>>>For example the password changing was :
>>>><!-- Authentication set query -->
>>>>      <querydef name="auth-set">
>>>>        <text>UPDATE users SET password = '$$p$$' WHERE username =
>>>>'$$username$$'</text>
>>>>        <bindvar name="user">$$username$$</bindvar>
>>>>        <bindvar name="password">$$p$$</bindvar>
>>>>      </querydef>
>>>>
>>>>And is now :
>>>><!-- Authentication set query -->
>>>>      <querydef name="auth-set">
>>>>        <text>UPDATE users SET password = '$$p$$' WHERE jid =
>>>>'$$username$$'</text>
>>>>        <bindvar name="user">$$username$$</bindvar>
>>>>        <bindvar name="password">$$p$$</bindvar>
>>>>      </querydef>
>>>>
>>>>The only change here is that the username is now called "jid" in the
>>>>database... But it won't change the password right, it onyl returns
>>>>"NULL"
>>>>into the database...
>>>>
>>>>Also, I would like to know if it's possible to excecute 2 SQL query
>>>>for a
>>>>single querydef ?
>>>>
>>>>Thanks in advance !
>>>>
>>>>--------------------------------
>>>>Bruce Heller
>>>>Ligne Directe: 01.47.66.55.64
>>>>Fax: 01.47.66.55.54
>>>>Standard: 01.47.66.55.50
>>>>--------------------------------
>>>>TERRA VIRTUAL
>>>>73, rue de Saussure
>>>>75017 PARIS
>>>>-------------------------------
>>>>
>>>>_______________________________________________
>>>>jdev mailing list
>>>>jdev at jabber.org
>>>>http://mailman.jabber.org/listinfo/jdev
>>>>        
>>>>
>>>      
>>>
>>_______________________________________________
>>jdev mailing list
>>jdev at jabber.org
>>http://mailman.jabber.org/listinfo/jdev
>>    
>>
>
>_______________________________________________
>jdev mailing list
>jdev at jabber.org
>http://mailman.jabber.org/listinfo/jdev
>  
>