Well, we have AuditLog which has support for storing auth and logout events and is able to tell us when session started and stopped, even can track multiple concurrent sessions. However, having last_login/last_logout is useful in case we want to know if account is still in use.. maybe last_used or last_accesses field would be better fit for that.

last_used definitely sounds better in this case.

@wojtek Should we replace last_login with last_used and drop last_login and last_logout?

I'd say yes, replace last_login with last_used though I'm pondering what would be the best course of action and if we really need it as sometimes we were hitting some issues with performance during logging in due to updating records in the DB. Maybe handle this in the separate plugin (that could be disabled without the need to change SP)? (this is just an idea, thinking out loud).

There is also somewhat related #issue #1141…

@wojtek I adding a new plugin will force use to add a separate stored procedure, a separate call which will increase memory consumption and will impact performance (another query to execute). I wonder (if we really want that configurable) to use just some table which would hold this settings value.

Another thing is, that we have index on this field which impacts performance of the update quite a lot. It would be "better" to just drop the index to improve performance. The penalty would be a query time when we would need to check when user last logged in, but it is not very often executed query (in the app we query auditlog repository for that if I'm correct).

Agreed on the index (hence the mentioned ticket). I think that without indexes and with simplified SP it should be faster so we shouldn't worry about complicated separation - agreed?

@wojtek Yes, so we would drop indexes, delete fields and create a new one. Correct?

Maybe, for simplicity sake, just rename last_login with last_used (should be faster, we retain most sensible data) and drop last_logout, online_status and indexes?

Yes, we can try that. I'm just going to start working on that.

I've made changes to the database schema but they needs to be validated on each database, so I'm waiting for TTS-NG run to confirm that everything works.

As outlined in #tigaseim-95 - we should:

• move upgrade from -schema to -sp
• split "upgrading" to distinct SP/queries.

@wojtek Moving and splitting is not so simple. What we have to consider is:

• bulk change in database schema (modifying a few columns at once is faster than one by one)
• some database "check" if stored procedures are using only existing fields, so we need to keep proper ordering

As we are at it, I have an idea/request. Let's split "core" server schema to separate files for:

• core (user repo, auth repo)
• offline messages
• any other repos?

and then lets flatten Tigase XMPP Server schema to just one file per version. 9.0.0 seams like a good candidate for that..

• bulk change in database schema (modifying a few columns at once is faster than one by one)

I'm not so sure if https://github.com/tigase/tigase-server/blob/41353367a19f539a3867ee83e876687310a36ddf/src/main/database/mysql-issue #8.2.0-schema.sql#L26-L26 this results in "at once". They are in one SP but you have 4 distinct queries and from what I remember processlist showed individual queries. Splitting TigServerUpgrade to 4 separate could cause more atomic operations and dropping indexes should not impact here anythig.

• some database "check" if stored procedures are using only existing fields, so we need to keep proper ordering

Agreed, but placing it at the end of -sp should be ok - all SP that were using it would have been updated already?

As we are at it, I have an idea/request. Let's split "core" server schema to separate files for: and then lets flatten Tigase XMPP Server schema to just one file per version. 9.0.0 seams like a good candidate for that..

Well, surprise - we already have a task for it: #issue #1180 ;-) But indeed 9.0.0 feels more appropriate.

I've splitted SP for MySQL.