Clustering and Deleting Collections Issues

Apologies if this is less coherent than I would hope. It's not 100% consistent what is happening. I am going to include two databases (either here or will get them to you some other way):

Small database: DatabaseBackup20240702.sqlite DatabaseBackup20240702.zip
Large database: Milwaukee07_02_2024.sqlite <this file is too big so it is on a google drive, here>

When clustering (using k-clustering, default settings), the following issues are seen:

In a small database, clustering a small collection, it all works fine if one value of K is selected.
In a large database, clustering the whole set of data (>500k particles) rarely works, but has worked once. It goes through the process when one value of K is selected, you can see the particles being assigned to their cluster center, and you can sometimes even see the centroids listed in the "other" window (what do I call that?), but then it hangs on "About to execute INSERTs". In some cases, we have waited for hours and not seen it complete.
When a sub-collection of the large database is clustered, and one value of K is selected, it sometimes works but other times doesn't. I think (but am not 100% certain) that it stops working and then never works again. Sometimes it makes the collection in which the sub-collections for each cluster will be included, but no particles are assigned to the clusters, and sometimes it writes the cluster centers at the bottom, but this is more rare. Errors are not consistent, but here is one set of errors written to the error log:

10:59:44:614 : SQLite Error: Error retrieving the collection name for collectionID 121 10:59:44:618 : SQLite Error: Error retrieving the collection name for collectionID 122 10:59:44:621 : SQLite Error: Error retrieving the collection name for collectionID 123 10:59:45:746 : SQLite Error: Error retrieving the collection description for collectionID 121 10:59:46:475 : SQLite Error: Error retrieving the collection description for collectionID 122 10:59:47:370 : SQLite Error: Error retrieving the collection description for collectionID 123 11:00:16:728 : SQLite Error: SQL Exception executing batch atom adds and inserts. 11:00:19:268 : SQLite Error: SQL Exception inserting atom. Please check incoming data for correct format. 11:00:19:464 : SQLite Error: SQL Exception inserting atom. Please check incoming data for correct format. 11:03:12:846 : SQLite Error: Exception deleting collection.

When a sub-collection of the large database is clustered, and multiple values of K are selected (i.e. K = 3,5), it fails. Sometimes it just freezes and other times it shows information in the second window, for example:

edu.carleton.enchilada.errorframework.ExceptionAdapter: org.sqlite.SQLiteException: [SQLITE_CONSTRAINT_PRIMARYKEY] A PRIMARY KEY constraint failed (UNIQUE constraint failed: AtomMembership.CollectionID, AtomMembership.AtomID) at org.sqlite.core.DB.newSQLException(DB.java:1010) at org.sqlite.core.DB.newSQLException(DB.java:1022) at org.sqlite.core.DB.throwex(DB.java:987) at org.sqlite.core.DB.executeBatch(DB.java:812) at org.sqlite.core.CorePreparedStatement.executeBatch(CorePreparedStatement.java:72) at edu.carleton.enchilada.database.Database.bulkInsertExecute(Database.java:1971) at edu.carleton.enchilada.analysis.CollectionDivider.putInSubCollectionBulkExecute(CollectionDivider.java:338) at edu.carleton.enchilada.analysis.clustering.Cluster.assignAtomsToNearestCentroid(Cluster.java:503) at edu.carleton.enchilada.analysis.clustering.Cluster.assignAtomsToNearestCentroid(Cluster.java:388) at edu.carleton.enchilada.analysis.clustering.ClusterK.innerDivide(ClusterK.java:193) at edu.carleton.enchilada.analysis.clustering.ClusterK$3.construct(ClusterK.java:227) at edu.carleton.enchilada.externalswing.SwingWorker$2.run(SwingWorker.java:108) at java.base/java.lang.Thread.run(Unknown Source) Exception inserting particle. org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near ")": syntax error) at org.sqlite.core.DB.newSQLException(DB.java:1010) at org.sqlite.core.DB.newSQLException(DB.java:1022) at org.sqlite.core.DB.throwex(DB.java:987) at org.sqlite.core.NativeDB.prepare_utf8(Native Method) at org.sqlite.core.NativeDB.prepare(NativeDB.java:134) at org.sqlite.core.DB.prepare(DB.java:264) at org.sqlite.jdbc3.JDBC3Statement.execute(JDBC3Statement.java:52) at edu.carleton.enchilada.database.SQLiteDatabase.insertParticle(SQLiteDatabase.java:183) at edu.carleton.enchilada.database.Database.insertParticle(Database.java:1434) at edu.carleton.enchilada.database.Database.insertParticle(Database.java:1424) at edu.carleton.enchilada.analysis.clustering.Cluster.createCenterAtoms(Cluster.java:834) at edu.carleton.enchilada.analysis.clustering.Cluster.assignAtomsToNearestCentroid(Cluster.java:555) at edu.carleton.enchilada.analysis.clustering.Cluster.assignAtomsToNearestCentroid(Cluster.java:388) at edu.carleton.enchilada.analysis.clustering.ClusterK.innerDivide(ClusterK.java:193) at edu.carleton.enchilada.analysis.clustering.ClusterK$3.construct(ClusterK.java:227) at edu.carleton.enchilada.externalswing.SwingWorker$2.run(SwingWorker.java:108) at java.base/java.lang.Thread.run(Unknown Source) Exception in thread "Thread-8" edu.carleton.enchilada.errorframework.ExceptionAdapter: org.sqlite.SQLiteException: [SQLITE_CONSTRAINT] Abort due to constraint violation (UNIQUE constraint failed: CenterAtoms.AtomID) at org.sqlite.core.DB.newSQLException(DB.java:1010) at org.sqlite.core.DB.newSQLException(DB.java:1022) at org.sqlite.core.DB.execute(DB.java:861) at org.sqlite.core.CoreStatement.exec(CoreStatement.java:80) at org.sqlite.jdbc3.JDBC3Statement.execute(JDBC3Statement.java:53) at edu.carleton.enchilada.database.Database.addCenterAtom(Database.java:1848) at edu.carleton.enchilada.analysis.clustering.Cluster.createCenterAtoms(Cluster.java:839) at edu.carleton.enchilada.analysis.clustering.Cluster.assignAtomsToNearestCentroid(Cluster.java:555) at edu.carleton.enchilada.analysis.clustering.Cluster.assignAtomsToNearestCentroid(Cluster.java:388) at edu.carleton.enchilada.analysis.clustering.ClusterK.innerDivide(ClusterK.java:193) at edu.carleton.enchilada.analysis.clustering.ClusterK$3.construct(ClusterK.java:227) at edu.carleton.enchilada.externalswing.SwingWorker$2.run(SwingWorker.java:108) at java.base/java.lang.Thread.run(Unknown Source)

And another issue that might be related:

When trying to delete a sub-collection (either one created but not populated after a clustering run) or just any random sub-collection, the following has been seen (not always, it has worked when the database is "new" -- i.e. restored from the backed up version):

Exception deleting collection: org.sqlite.SQLiteException: [SQLITE_LOCKED] A table in the database is locked (database table is locked) at org.sqlite.core.DB.newSQLException(DB.java:1010) at org.sqlite.core.DB.newSQLException(DB.java:1022) at org.sqlite.core.DB.throwex(DB.java:987) at org.sqlite.core.NativeDB._exec_utf8(Native Method) at org.sqlite.core.NativeDB._exec(NativeDB.java:94) at org.sqlite.jdbc3.JDBC3Statement.executeUpdate(JDBC3Statement.java:109) at edu.carleton.enchilada.database.Database.recursiveDelete(Database.java:1687) at edu.carleton.enchilada.gui.MainFrame$5.construct(MainFrame.java:646) at edu.carleton.enchilada.externalswing.SwingWorker$2.run(SwingWorker.java:108) at java.base/java.lang.Thread.run(Unknown Source)

Addendum -- I set up a clean installation of Enchilada on a new laptop and installed the Milwaukee database and clustered it, and it completed, although it took >2 hours to do the last step (after the "About to execute INSERTs" statement). I am trying it again with a different single value of K. If it works again, I'll try two values of K and I will report back.

Don't worry about it. I've duplicated the problem, at least the slow inserting. I'm trying to nail down where in the code where it's stuck.

Great! That gives me hope.

I've fixed, I think, the first problem. There's a way of writing code that makes sqlite insertions faster. We didn't see this problem because the data wasn't big enough to make the slowdown bad enough to be a problem.

I haven't gotten to look at the subcollection one yet. Hopefully tomorrow.

I've made significant progress.

Here's the problem. Whenever we talk to the database to do ANYTHING, we are supposed to:

create a query/update/whatever
do our business
close it when we're done.

We do this in a lot of locations throughout the code. (I'm guessing around 100.) We were sloppy and didn't close our query most of the time. SQL Server didn't care. SQLite occasionally does. (It has the right to, these are real bugs, we just didn't know better, and SQL Server let us be sloppy.)

This is specifically the cause of the locking-related error messages that you saw.

The challenge in fixing it is that when you get the occasional error, like the locking error you saw, it means that a lock on the database was being held by a query somewhere else in the code, but you don't know where. So I had to go through the code base and check every one of them to make sure that they were closed. The development environment (Intellij) helped me find them, but it was still ultimately a job of cleaning up the code and using a smarter structure to help make sure everything closed.

So here's where we are.

I touched EVERYTHING in order to do this. Most of the fixes were braindead straightforward. Some of them required me to make some small changes to structure to pull it off. A few of them required me to be a bit more aggressive in the changes I made. I must have broken something somewhere in the process. All of the tests are passing, but they're all localized... and this problem occurs as a result of multiple connections interacting together. The only way to know is to try it and see how it goes.
There are a small number of cases where closing the connections is extra complicated to get right because the code is somewhat scattered. I've read them and I think they're ok, but the automated checker doesn't like them because they smell bad. It would be better to rewrite those pieces of code from scratch to make that manageable. I haven't done that.

Anyway, this explains much but not all of the error messages that you saw. I'm not seeing them now. Some of the other errors you saw might have been independent, or might have been an effect of this problem. I don't know. I'll have to get you a build, and you'll have to try it and see.

That's my next step; to do a release; hopefully later this morning, I've got to remember how to do it. I've also got to mix and match this with getting ready for SLAI and other items.

Thank you! If you can make a build, we can test it Monday. I do very much appreciate the time and effort that you put into this. It will make a difference.

Once we have a runnable version, we will keep you updated.

Thank you!! Deborah

https://www.carleton.edu/

Deborah S. Gross, Ph.D.

Charles "Jim" and Marjorie Kade Professor of the Sciences, Chemistry https://www.carleton.edu/chemistry/

Director of FOCUS https://www.carleton.edu/focus/

Evelyn M. Anderson Hall 240

(507) 222-5629

calendar https://calendar.google.com/calendar/u/0?cid=ZGdyb3NzQGNhcmxldG9uLmVkdQ | book an appointment https://calendar.app.google/5b3EZ5HPm1xPstAW7 | website https://www.carleton.edu/people/dgross/

On Fri, Jul 5, 2024 at 09:52 Dave Musicant @.***> wrote:

I've made significant progress.

Here's the problem. Whenever we talk to the database to do ANYTHING, we are supposed to:

create a query/update/whatever

do our business

close it when we're done.

We do this in a lot of locations throughout the code. (I'm guessing around 100.) We were sloppy and didn't close our query most of the time. SQL Server didn't care. SQLite occasionally does. (It has the right to, these are real bugs, we just didn't know better, and SQL Server let us be sloppy.)

This is specifically the cause of the locking-related error messages that you saw.

The challenge in fixing it is that when you get the occasional error, like the locking error you saw, it means that a lock on the database was being held by a query somewhere else in the code, but you don't know where. So I had to go through the code base and check every one of them to make sure that they were closed. The development environment (Intellij) helped me find them, but it was still ultimately a job of cleaning up the code and using a smarter structure to help make sure everything closed.

So here's where we are.

I touched EVERYTHING in order to do this. Most of the fixes were braindead straightforward. Some of them required me to make some small changes to structure to pull it off. A few of them required me to be a bit more aggressive in the changes I made. I must have broken something somewhere in the process. All of the tests are passing, but they're all localized... and this problem occurs as a result of multiple connections interacting together. The only way to know is to try it and see how it goes.

There are a small number of cases where closing the connections is extra complicated to get right because the code is somewhat scattered. I've read them and I think they're ok, but the automated checker doesn't like them because they smell bad. It would be better to rewrite those pieces of code from scratch to make that manageable. I haven't done that.

Anyway, this explains much but not all of the error messages that you saw. I'm not seeing them now. Some of the other errors you saw might have been independent, or might have been an effect of this problem. I don't know. I'll have to get you a build, and you'll have to try it and see.

That's my next step; to do a release; hopefully later this morning, I've got to remember how to do it. I've also got to mix and match this with getting ready for SLAI and other items.

— Reply to this email directly, view it on GitHub https://github.com/dmusican/enchilada/issues/16#issuecomment-2211016564, or unsubscribe https://github.com/notifications/unsubscribe-auth/AQGEJQJRCYPXT6CQE2636KLZK2XMHAVCNFSM6AAAAABKJ6LHPKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDEMJRGAYTMNJWGQ . You are receiving this because you authored the thread.Message ID: @.***>

I am keeping this in the same thread, but I can make a new thread if you prefer. I downloaded and tried the pre-release version

I was optimistic, and started with a clustering run of multiple K values (4 different values). It showed the error value decreasing in the tiny window, but it never printed anything into the text window (still not sure what to call it), so I couldn't see where it was along the way. After seeing it sit for a long time (>1 hour) without the error value changing, I killed it. It had completed the first value of K correctly, I think, and it had made the folders to put the particles from the second value of K, but I killed it before it populated them.

I then restarted Enchilada and clustered with only one value of K. It didn't complete. It did print information into the window whose name I don't know, and it gave this information after the clustering details: About to execute INSERTs. edu.carleton.enchilada.errorframework.ExceptionAdapter: org.sqlite.SQLiteException: [SQLITE_CONSTRAINT_PRIMARYKEY] A PRIMARY KEY constraint failed (UNIQUE constraint failed: AtomMembership.CollectionID, AtomMembership.AtomID) at org.sqlite.core.DB.newSQLException(DB.java:1179) at org.sqlite.core.DB.newSQLException(DB.java:1190) at org.sqlite.core.DB.throwex(DB.java:1150) at org.sqlite.core.DB.executeBatch(DB.java:951) at org.sqlite.core.DB.lambda$executeBatch$0(DB.java:916) at org.sqlite.core.SafeStmtPtr.safeRun(SafeStmtPtr.java:128) at org.sqlite.core.DB.executeBatch(DB.java:916) at org.sqlite.core.CorePreparedStatement.lambda$executeLargeBatch$1(CorePreparedStatement.java:76) at org.sqlite.jdbc3.JDBC3Statement.withConnectionTimeout(JDBC3Statement.java:454) at org.sqlite.core.CorePreparedStatement.executeLargeBatch(CorePreparedStatement.java:72) at org.sqlite.core.CorePreparedStatement.executeBatch(CorePreparedStatement.java:58) at edu.carleton.enchilada.database.Database.bulkInsertExecute(Database.java:1972) at edu.carleton.enchilada.analysis.CollectionDivider.putInSubCollectionBulkExecute(CollectionDivider.java:338) at edu.carleton.enchilada.analysis.clustering.Cluster.assignAtomsToNearestCentroid(Cluster.java:503) at edu.carleton.enchilada.analysis.clustering.Cluster.assignAtomsToNearestCentroid(Cluster.java:388) at edu.carleton.enchilada.analysis.clustering.ClusterK.innerDivide(ClusterK.java:193) at edu.carleton.enchilada.analysis.clustering.ClusterK$3.construct(ClusterK.java:227) at edu.carleton.enchilada.externalswing.SwingWorker$2.run(SwingWorker.java:108) at java.base/java.lang.Thread.run(Unknown Source) Exception inserting particle. org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near ")": syntax error) at org.sqlite.core.DB.newSQLException(DB.java:1179) at org.sqlite.core.DB.newSQLException(DB.java:1190) at org.sqlite.core.DB.throwex(DB.java:1150) at org.sqlite.core.NativeDB.prepare_utf8(Native Method) at org.sqlite.core.NativeDB.prepare(NativeDB.java:126) at org.sqlite.core.DB.prepare(DB.java:264) at org.sqlite.jdbc3.JDBC3Statement.lambda$execute$0(JDBC3Statement.java:51) at org.sqlite.jdbc3.JDBC3Statement.withConnectionTimeout(JDBC3Statement.java:454) at org.sqlite.jdbc3.JDBC3Statement.execute(JDBC3Statement.java:40) at edu.carleton.enchilada.database.SQLiteDatabase.insertParticle(SQLiteDatabase.java:181) at edu.carleton.enchilada.database.Database.insertParticle(Database.java:1435) at edu.carleton.enchilada.database.Database.insertParticle(Database.java:1425) at edu.carleton.enchilada.analysis.clustering.Cluster.createCenterAtoms(Cluster.java:834) at edu.carleton.enchilada.analysis.clustering.Cluster.assignAtomsToNearestCentroid(Cluster.java:555) at edu.carleton.enchilada.analysis.clustering.Cluster.assignAtomsToNearestCentroid(Cluster.java:388) at edu.carleton.enchilada.analysis.clustering.ClusterK.innerDivide(ClusterK.java:193) at edu.carleton.enchilada.analysis.clustering.ClusterK$3.construct(ClusterK.java:227) at edu.carleton.enchilada.externalswing.SwingWorker$2.run(SwingWorker.java:108) at java.base/java.lang.Thread.run(Unknown Source) Exception inserting particle. org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near ")": syntax error) at org.sqlite.core.DB.newSQLException(DB.java:1179) at org.sqlite.core.DB.newSQLException(DB.java:1190) at org.sqlite.core.DB.throwex(DB.java:1150) at org.sqlite.core.NativeDB.prepare_utf8(Native Method) at org.sqlite.core.NativeDB.prepare(NativeDB.java:126) at org.sqlite.core.DB.prepare(DB.java:264) at org.sqlite.jdbc3.JDBC3Statement.lambda$execute$0(JDBC3Statement.java:51) at org.sqlite.jdbc3.JDBC3Statement.withConnectionTimeout(JDBC3Statement.java:454) at org.sqlite.jdbc3.JDBC3Statement.execute(JDBC3Statement.java:40) at edu.carleton.enchilada.database.SQLiteDatabase.insertParticle(SQLiteDatabase.java:181) at edu.carleton.enchilada.database.Database.insertParticle(Database.java:1435) at edu.carleton.enchilada.database.Database.insertParticle(Database.java:1425) at edu.carleton.enchilada.analysis.clustering.Cluster.createCenterAtoms(Cluster.java:834) at edu.carleton.enchilada.analysis.clustering.Cluster.assignAtomsToNearestCentroid(Cluster.java:555) at edu.carleton.enchilada.analysis.clustering.Cluster.assignAtomsToNearestCentroid(Cluster.java:388) at edu.carleton.enchilada.analysis.clustering.ClusterK.innerDivide(ClusterK.java:193) at edu.carleton.enchilada.analysis.clustering.ClusterK$3.construct(ClusterK.java:227) at edu.carleton.enchilada.externalswing.SwingWorker$2.run(SwingWorker.java:108) at java.base/java.lang.Thread.run(Unknown Source) Exception in thread "Thread-1" edu.carleton.enchilada.errorframework.ExceptionAdapter: org.sqlite.SQLiteException: [SQLITE_CONSTRAINT_PRIMARYKEY] A PRIMARY KEY constraint failed (UNIQUE constraint failed: CenterAtoms.AtomID) at org.sqlite.core.DB.newSQLException(DB.java:1179) at org.sqlite.core.DB.newSQLException(DB.java:1190) at org.sqlite.core.DB.execute(DB.java:985) at org.sqlite.core.CoreStatement.exec(CoreStatement.java:79) at org.sqlite.jdbc3.JDBC3Statement.lambda$execute$0(JDBC3Statement.java:52) at org.sqlite.jdbc3.JDBC3Statement.withConnectionTimeout(JDBC3Statement.java:454) at org.sqlite.jdbc3.JDBC3Statement.execute(JDBC3Statement.java:40) at edu.carleton.enchilada.database.Database.addCenterAtom(Database.java:1848) at edu.carleton.enchilada.analysis.clustering.Cluster.createCenterAtoms(Cluster.java:839) at edu.carleton.enchilada.analysis.clustering.Cluster.assignAtomsToNearestCentroid(Cluster.java:555) at edu.carleton.enchilada.analysis.clustering.Cluster.assignAtomsToNearestCentroid(Cluster.java:388) at edu.carleton.enchilada.analysis.clustering.ClusterK.innerDivide(ClusterK.java:193) at edu.carleton.enchilada.analysis.clustering.ClusterK$3.construct(ClusterK.java:227) at edu.carleton.enchilada.externalswing.SwingWorker$2.run(SwingWorker.java:108) at java.base/java.lang.Thread.run(Unknown Source)

The full error log from today is: ErrorLog: started @ 2024-07-07 14:37:34:167 : SQLite Error: SQL Exception executing batch atom adds and inserts. 14:38:18:772 : SQLite Error: SQL Exception inserting atom. Please check incoming data for correct format. 14:38:19:008 : SQLite Error: SQL Exception inserting atom. Please check incoming data for correct format. 17:46:38:150 : SQLite Error: SQL Exception executing batch atom adds and inserts. 17:47:23:021 : SQLite Error: SQL Exception inserting atom. Please check incoming data for correct format. 17:47:23:272 : SQLite Error: SQL Exception inserting atom. Please check incoming data for correct format.

(the 14:xx times are the multiple K run and the 17:xx times are the single K run.)

Also, I have noticed that clicking on a collection, whether top-level or sub-collection, now takes a distinctly longer time to select and open it than it did previously. The cursor spins probably for 2 - 3 seconds.

Let me know if you need me to gather any additional information. Thank you for helping us with this.

Thanks, sorry you're having trouble. Here's some important stuff to try, which you might have already done, or might not, but I didn't think to suggest:

Rebuild the database (File, Rebuild Database) first so it's completely empty, then restore from the Milwaukee dump you provided. Then try to do the clustering you did above. If the data was corrupt from a previous bad run, that could be a problem. (Maybe you did this, and maybe it doesn't matter, but try it.)
Go into the advanced settings for clustering and set the error to something massive. 10000000000 or whatever. The results will won't be scientifically useful, but clustering will terminate faster, so it will help problems appear faster, hopefully.
I tried doing the above, clicking the whole dataset, and clustering for a range of 3-6. It got through 3, then started 4, and then I stopped it after it was going. Took a few minutes, I guess?
If you have something different going on than the above, get me a screencast? Do it in Zoom or whatever you like? Rebuild the database, import the data, and then let me see exactly what you're selecting.

On the matter of slower clicking: that's frustrating, understood. One problem at a time. Let's take the big one above.

Again, I'm in the classroom all day for the next three weeks, so my ability to work on this is limited, but I'll see what I can do.

dmusican / enchilada

Clustering and Deleting Collections Issues #16