Problems with AT and multiple users

We have just move the Archivists Toolkit into production. When that happened things slowed to a crawl and at least one critical feature stopped working. The main difference between what we started with and where we are now is that we have 3-4 people attempting to use this for serious work. A couple of people are merging names and subjects to correct problems from the old system, others are entering accessions.

But the merge is now taking 5-10 minutes. We are getting intermittant errors trying to open specific records. Finally we can no longer do a list all on accessions. This last is critical. Every time we try we get "Error finding all:failed to find all" with the dump:

org.archiviststoolkit.mydomain.LookupException: failed to find all
at org.archiviststoolkit.mydomain.DomainAccessObjectImpl.findAllCommon(DomainAccessObjectImpl.java:708)
at org.archiviststoolkit.mydomain.DomainAccessObjectImpl.findAll(DomainAccessObjectImpl.java:654)
at org.archiviststoolkit.mydomain.DomainTableWorkSurface$7.run(DomainTableWorkSurface.java:1151)
at java.lang.Thread.run(Thread.java:662)
Caused by: org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [org.archiviststoolkit.model.Repositories#345905]
at org.hibernate.persister.entity.AbstractEntityPersister.check(AbstractEntityPersister.java:1765)
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2407)
at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:2307)
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2607)
at org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:92)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:250)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:234)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:142)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
at org.archiviststoolkit.mydomain.DomainAccessObjectImpl.findAllCommon(DomainAccessObjectImpl.java:700)
... 3 more

At this point the product is unusable.

We are running this on Oracle 10g.

Thank you
Rich

Migrating from Oracle to MySQL

I can't comment on this reponse, but our DBAs and administrators could not find any issues on their side. Based on that, and the Harvard experience (posted on the listserv) we decided that we could not live with Oracle. Whether the problem is with the implementation or AT it wasn't going to be fixed. So we determined to move to MySQL.

Unfortunately we had customized the UI and added several thousand locations, so we were reluctant to start over from scratch. Instead we decided to migrate the database to MySQL and move ahead from there.

This had some issues including:

encryption issues
database capabilities
untransferred table attributes

The actual migration (using Navicat) took 15 minutes. While everything looked good we could not log in. This turned out to be due to a difference in how Oracle and MySQL encrypt passwords. The user ids and passwords were migrated, but AT could not read them.

Overcoming this took some effort. After several abortive attempts we discovered a developers bypass in the AT source code. This got us past the login, but we still could not move forward. At reported a logging problem. At first glance we thought it was a permissions issue and just commented out the call to logSession. (More on this later.) This got us in and we were able to reset all passwords.

However when others tried to start the program they too got the logging problem which made it clear that it was not a permission problem on one PC. Following the thread dump information we discovered that AT was unable to create new Session records because it could not generate the key.

We dug into the database on Oracle and MySQL and realized that the Session records, among others, needed autoincrement added to their keys. In order to ensure that we got this right we ended up creating and initializing a new MySQL database with AT. This told us which tables neede the autoincrement. Once that was added we were able to log on and do most tasks.

However, we could not import EADs or merge Resources. The thread dump mentioned not finding an instance_sequence table. There is no instance_sequence table in Oracle, so this threw us for a while. But comparing the dummy database we had created with our AT database we found several sequence tables that were not in Oracle. It appears that AT takes advantage of built in Oracle sequences that are not supported in MySQL, thus they were not migrated.

We created the tables, modeled on the dummy database, and initialized them in the same way AT did. (This was found in the function determineSequenceNumbers in UpgradeDatabaseBackgroundResultCreator.java.)

At this point everything seems to work.

It would be helpful if anyone here is aware of anything that we missed. Everything looks good, but considering how we got here, I would not be surprised to learn that we missed something that will come back to bite us in the future.

(One last minor question. The other sequences get intialized to the maximum value of their associated keys, but structured_data_sequence is initialized to 10000. Why is that?)

Thank you
Rich

Structured data sequence

Hi This value gets initialized to 10000 in order to fix a bug that was causing all sorts of issues when multiple users were on the system.

AT Problems Expanding

We are now unable to log in. The program hangs on "loading repositories"

Rich

Hi, Rich, The problems are

Hi, Rich,

The problems are related to the setup of your network/database. Hopefully by working with your DBA and network techinicans you can find a solution.

Regards,
Annie