Project

General

Profile

MySQL managment

Pascal S
Added about 5 years ago

Hello

Here is not an issue but more a question. I successfully installed and run the latest public version of Tigase and I was wondering how to add multiple user to the Tigase database. Let me be more precise. Currently Tigase is running using a MySQL database and I tried to add user using the TigAddUser() stored Procedure.

My problem occurs their because this procedure puts a new entry into the tig_users table but does not update the tig_pairs table. I wanted to manually add the needed entries to the tig_pairs table but I'm unable to determine the value of row nid.

Any thoughts on that matter would be great, thanks in advance.


Replies (13)

Avatar?id=6023&size=32x32

Added by Artur Hefczyc TigaseTeam about 5 years ago

You should use TigAddUserPlainPw() stored procedure instead, because it takes care of a correct password encoding, depending on your settings.

What kind of data you want to put into tig_pairs table? Normally nothing else is needed for the user to login, unless you also want to generate some contact list for each user, for tests.

Added by Pascal S about 5 years ago

Well in fact I would like to generate a contact list for various new users, the idea is when adding someone to the server he should have a default roster (for example all the company executive).

I looked into ad-hoc commands that allows to add an address to one roster but if I could script that directly in base (for multiple users addition) it would be great.

Added by Wojciech Kapcia TigaseTeam about 5 years ago

I think that what you are looking for is Dynamic Roster (v. DynamicRosterIfc.java interface for more details)

Added by Pascal S about 5 years ago

I used the TigAddUserPlainPw() storred procedure, it updated the tig_users with the expected value but then I wasn't able to conect using the credential added to the database. I used Psi+ to monitor the stanza exchange and here is what I get:

<?xml version="1.0"?>


<stream:stream xmlns:stream="http://etherx.jabber.org/streams" version="1.0" xmlns="jabber:client" to="example.com" xml:lang="fr" xmlns:xml="http://www.w3.org/XML/1998/namespace">


<?xml version='1.0'?><stream:stream xmlns='jabber:client' xmlns:stream='http://etherx.jabber.org/streams' from='example.com' id='04fd87b9-7a9e-4cc0-9c49-08e26d701fe5' version='1.0' xml:lang='en'>


<stream:features>
<ver xmlns="urn:xmpp:features:rosterver"/>
<starttls xmlns="urn:ietf:params:xml:ns:xmpp-tls"/>
<mechanisms xmlns="urn:ietf:params:xml:ns:xmpp-sasl">
<mechanism>PLAIN</mechanism>
<mechanism>ANONYMOUS</mechanism>
</mechanisms>
<register xmlns="http://jabber.org/features/iq-register"/>
<auth xmlns="http://jabber.org/features/iq-auth"/>
</stream:features>


<starttls xmlns="urn:ietf:params:xml:ns:xmpp-tls"/>


<proceed xmlns="urn:ietf:params:xml:ns:xmpp-tls"/>


<?xml version="1.0"?>


<stream:stream xmlns:stream="http://etherx.jabber.org/streams" version="1.0" xmlns="jabber:client" to="example.com" xml:lang="fr" xmlns:xml="http://www.w3.org/XML/1998/namespace">


<?xml version='1.0'?><stream:stream xmlns='jabber:client' xmlns:stream='http://etherx.jabber.org/streams' from='example.com' id='04fd87b9-7a9e-4cc0-9c49-08e26d701fe5' version='1.0' xml:lang='en'>


<stream:features>
<ver xmlns="urn:xmpp:features:rosterver"/>
<mechanisms xmlns="urn:ietf:params:xml:ns:xmpp-sasl">
<mechanism>PLAIN</mechanism>
<mechanism>ANONYMOUS</mechanism>
</mechanisms>
<register xmlns="http://jabber.org/features/iq-register"/>
<auth xmlns="http://jabber.org/features/iq-auth"/>
</stream:features>


<auth xmlns="urn:ietf:params:xml:ns:xmpp-sasl" mechanism="PLAIN">AHRlc3RhZGQAdGVzdGFkZDU2</auth>


<failure xmlns="urn:ietf:params:xml:ns:xmpp-sasl">
<not-authorized/>
<text xml:lang="en">Password not verified</text>
</failure>

I made sure the input credential were the right ones but still I wasn't able to log in.

I talked earlier of the tig_pairs table because for me it contain the password of my users (in tig_users the user_pw is empty expect for the administrator and for the user added using TigAddUserPlainPw()).

Find attached my configuration file and let me know if you need anything else.

Added by Wojciech Kapcia TigaseTeam about 5 years ago

You are using --auth-db = mysql which has different way of storing credentials in the database. Mentioned above ways with using stored procedures are meant for @--auth-db = tigase-custom@, which is default since version 5.1.0

Added by Pascal S about 5 years ago

Okay, so my issue are coming from using mysql as an auth-db.

As it could be important for my implementation to separate the authentication part of the server from the rest (in particular from the tig_ma_msgs table) could you explain me how to add a new user in this configuration?

I noticed that the password was stored into the tig_pairs table but I'm unable to understant the nid row...

Added by Pascal S about 5 years ago

So I made a fresh installation (using no --auth-db = mysql) my problem is in that configuration the message_off-line functionality is not working properly. I noticed that neither the row last_logout nor the online_status are updated when a user disconnects.

Then off-line message are not treated as such and are stored directly as archive message and never show on the recipient side.

My init.properities is the same as before minus the two --auth-db lines.

Avatar?id=6023&size=32x32

Added by Artur Hefczyc TigaseTeam about 5 years ago

Pascal Sygnet wrote:

Okay, so my issue are coming from using mysql as an auth-db.

As it could be important for my implementation to separate the authentication part of the server from the rest (in particular from the tig_ma_msgs table) could you explain me how to add a new user in this configuration?

I noticed that the password was stored into the tig_pairs table but I'm unable to understant the nid row...

Please have a look at the document: http://www.tigase.org/content/tigase-db-schema-explained

Avatar?id=6023&size=32x32

Added by Artur Hefczyc TigaseTeam about 5 years ago

Pascal Sygnet wrote:

I noticed that neither the row last_logout nor the online_status are updated when a user disconnects.

These DB fields are not guaranteed to show accurate information. They need a special configuration to be updated in the first place, and even then, they are never guaranteed to be updated, so you should never rely on them. This is kind of a leftover from our early experiments to provide user online status through DB to third-party systems.

So I made a fresh installation (using no --auth-db = mysql) my problem is in that configuration the message_off-line functionality is not working properly.

Then off-line message are not treated as such and are stored directly as archive message and never show on the recipient side.

This has nothing to do with the DB fields: the last_logout or the online_status showing incorrect information.

My init.properities is the same as before minus the two [...] lines.

Yes, you have the msgoffline plugin turned off in your configuration, hence it is not working:

--sm-plugins = [...],-msgoffline,[...]

Added by Pascal S almost 5 years ago

Thank you for the database schema explanation everything is clearer.

For my message off-line problem I didn't make myself clear (sorry my question must have looked dumb ;-).

Either using +msgoffline or -msgoffline I encounter the same issue.

  1. reboot the server

  2. user A logs in (using Psi+) and send message to user B (off-line)

  3. user B logs in (using Psi+) and receives off-line message from user A

  4. from their off-line message stop working altogether.

    -> messages send to off-line users appear in the tig_ma_msgs table

    -> no entries are added to the msg_history table (unlike first connection when off-line messages were effectively delivered).

To add something I should say that the problem does not occur when using other clients than Psi+ (for example Pidgim and my custom bosh client). If you need any log file to investigate just let me know.

It is not a real problem for me as I use a custom application but it concerns me if clients want to connect using their own software.

Added by Wojciech Kapcia TigaseTeam almost 5 years ago

OK, a bit of clarification - in your configuration you have following entries:

--sm-plugins = +message-archive-xep-0136,(…),+amp,-msgoffline,(…)

--comp-class-6 = tigase.archive.MessageArchiveComponent
--comp-name-6 = message-archive

So you are using:

  • AMP - this is a message plugin - it allows more powerful message processing as well as provides functionality of storing offline messages and those are stored to msg_history table;

  • MessageArchive XEP-0136 - this plugin is also responsible for storing messages but this is actually for providing complete archive functionality and (if enabled) it stores messages to tig_ma_msgs table;

Now, there is a bug in Psi which doesn't correctly disconnects user session when going offline hence Tigase "thinks" that the user is still connected (at least for a while - there are a few mechanisms that occasionally checks and remove old sessions) hence when the messages comes Tigase doesn't store it in the offline messages table but tries to send it to client over socket connection that should be closed by Psi.

Added by Pascal S almost 5 years ago

Okay it is a lot more clear for me (I was convinced something wa wrong with Psi log-out mechanism).

So if I understand correctly I could choose a configuration like:

--sm-plugins = +message-archive-xep-0136,(…),-amp,+msgoffline,(…)

--comp-class-6 = tigase.archive.MessageArchiveComponent
--comp-name-6 = message-archive

if I wanted just message archive and off-line messages.

Is their a way to trigger more frequent "checks and remove old sessions" mechanisms? (if not don't bother responding)

Thank you for taking the time.

Added by Wojciech Kapcia TigaseTeam almost 5 years ago

You should also enable message plugin:

--sm-plugins = +message-archive-xep-0136,(…),-amp,+message,+msgoffline,(…)

As for more frequent checking - you can either change kernel tcp_keepalive setting or change settings of Watchdog mechanism:

    (1-13/13)