What is status of the task? If not completed, provide work estimates.

Updated estimate

We need this for the coming 7.1.0 version.

I've made the changes described above - this entailed bumping schema version to 7.1; I've changed the schellscripts to utilize DBSchemaLoader class so everything will work the consistently; I've created update scripts for all databases and given the change we need to create new page describing the process similar to the one we have for version 5.1 - proposed URI would be:

http://docs.tigase.org/tigase-server/7.1.0/Administration_Guide/html/#_tigase_7_1_database_schema_upgrade - %daniel could you include that and make a note in the release notes?

The update procedure requires running *-schema-upgrade-to-7-1.sql file appropriate to the database engine used against created database. Following (with the matching credentials) can be used:

java -cp "jars/*" tigase.util.DBSchemaLoader -dbHostname ${HOSTNAME} -dbType ${DB_TYPE} -rootUser ${ROOT_USER} -dbPass ${DB_USER_PASS} -dbName ${DB_NAME} -schemaVersion ${DB_VERSION} -rootPass ${ROOT_USER_PASS} -dbUser ${DB_USER}  -adminJID "admin@atlantiscity" -adminJIDpass admin  -logLevel ALL -file database/${DB_TYPE}-schema-upgrade-to-7-1.sql

Tigase will check the version and prompt the user to do the update, new schema files are included in the IzPack installer and WebInstaller will pick them automatically (it uses same DBSchemaLoader underneath). Izpack installer should be able to upgrade existing schema to version 7.1 but I haven't tested this route as we are phasing it out.

Documentation has been created and added for the next build. I've included it with the PubSub Schema change page, the address for documentation will be

http://docs.tigase.org/tigase-server/7.1.0/Administration_Guide/html/#tigaseServer71

I've noticed the commit went to master branch - please cherry-pick it to release (with all previous documentation commits as well) so it will be generated.

Links fixed and will be available in new build.

documentation available http://docs.tigase.org/tigase-server/snapshot/Administration_Guide/html/#tigaseServer71

until v7.1.0 is released, then above link will work.

using uid-nid-pkey for primary key won't work.

I took another look at the issue after recent report from the problems during upgrade.

Unfortunately my initial change went against Tigase API (there is tigase.db.UserRepository.addDataList(BareJID, String, String, String[]) and tigase.db.UserRepository.setDataList(BareJID, String, String, String[])@) thus it was wrong to go with Primary Key on (uid,nid,pkey) as there is possible to have multiple items like that (most noticeably - @messages pkey from old msgoffline plugin).

In the end I've created additional id, auto-increment PK column.

Small comment related to the whole ticket (and the related one) - root cause for the experienced problem was due to lack of primary key (granted) but the reason it wasn't experience previously was that it only manifested itself in the particular environment - Percona MySQL implementation and its Galera clustering solution (v. InnoDB scalability issues due to tables without primary keys.)

However, by the rule of thumb it's a good idea to have Primary Key in the table, hence the addition of the column.