Query for lost auctions, and other DB sundries...

Subscribe to Query for lost auctions, and other DB sundries... 2 post(s), 1 voice

 
Avatar OneSounder 2 post(s)

Hi Morgan et al,

For quite some time, there’s been an issue starting up jbidwatcher. On my initial installation, it used to take anywhere up to 30 minutes or even more, pinning the CPU in the process. I’ve seen others on the forums make mention of this…

Figuring that I’ve been using this thing for years now and thought I’d give a little something back, I think I’ve tracked down the problem. Specifically, the qurey in findLostAuctions() in AuctionInfo. If you have a rather large auctions table (mine seems to have about a 1.4m rows), the existing query creates one heck of a cartesian product. Try it interactively.

So… To fix this, I propose using this query instead.

SELECT * FROM auctions a WHERE NOT EXISTS ( SELECT 1 from entries e where ( a.identifier = e.identifier ))

which seems to work quite nicely and still returns the same result. Tested with both Derby and MySQL.

And… Startup time is now “normal” once again.

Oh, and while I’m at it, I’ve looked at the DB creation script (jbidwatcher.sql) which fails for MySQL/MariaDB…

Specifically, when you create the devices table. That’s not a valid syntax for that under those DBs (and others).

The corrected syntax for those, would be:

CREATE TABLE devices (
id INTEGER NOT NULL AUTO_INCREMENT,
device_id VARCHAR DEFAULT NULL,
security_key VARCHAR DEFAULT NULL,
PRIMARY KEY (id)
)

ALTER TABLE devices AUTO_INCREMENT = 1

The alter table is sort of “redundant” but since the original statement specified a starting value…

I’ve tested both of these with the latest source from github…

So, I propose that perhaps you could fire off a separate script when initializing different DBs?

And also, making reloading DB from the auctions.xml file work again, would be kind of nice… It seems to be broken (also mentioned on the forums). I’d be willing to take a look at that as well.

Hope that helps out a bit and thanks!

 
Avatar OneSounder 2 post(s)

Also, I’d like to add, don’t use the mysql-connector-java-5.1.7. There’s a problem with it that prevents the garbage collector from releasing resources that it allocates. After about 10 minutes of running, you’ll start getting out of heap space errors.

Use the most recent one, or at least 5.1.36.