John, I was fighting with the same problem some time ago: http://tech.groups.yahoo.com/group/jena-dev/message/46541 and I managed to solve it, as far as I remember.
But my advice would be -- save yourself the trouble and start using TDB. TDB is actively developed, and performance on SDB using more complex queries quickly becomes unacceptable. Martynas http://graphity.org On Fri, Jan 27, 2012 at 7:25 PM, John Fereira <[email protected]> wrote: >>On 24/01/12 15:16, John Fereira wrote: >>> After getting fuseki with SDB working yesterday I check it this >>> morning and got a 500 Server Error, with a SQLException in executing >>> SQL statement message. >>> >>> This appears to be the same problem I was trying to fix by migrating >>> from Joseki to Fuseki. After a bit of searching I came across this >>> thread (http://tech.groups.yahoo.com/group/jena-dev/message/35766) >>> which describes the same problem when using Joseki. The link I've >>> included shows a class for doing database connection pooling but it >>> appears to be a standalone class and there isn't any information about >>> where it might be hooked into SDB. I've been using database >>> connection pooling for a long time and have had better success with >>> the C3p0 db connection pooling library and have it wired into a Tomcat >>> servlet container but it seems to me a ConnectionPooling class could >>> be wired into SDB, but it would be nice if it pulled in the database >>> connection values from the config file instead of hardcoding them in >>> the java class as the URL shown above does. >>> >>> I've seen numerous references to connection management and connection >>> pooling with SDB but haven't seen any sort of recipe for how to put it >>> all together. >>> >> >>If it's overnight, is this the MySQL timeout idle connections issue? >>MySQL, by default, kills connections after (IIRC) 8 hours of idleness. > > Yes, the connection closed overnight (and yes the idle value is 8 hours, > based on the wait_timeout or interactive_timeout mysql variables). I guess > my question is "why is a connection being held open for 8 hours?". > >> >>Connection pooling is a good solution to this, the crude way is >>?autoReconnect=true or adjust the server settings to some very, very long >>period. >> > > I thought I'd read something that setting ?autoReconnect=true didn't seem to > fix the problem. It seems that adjusting the idle timeout to a very long > period would just result in running out of database connections eventually. > Connection pooling *would* be a good solution as long as the connection is > returned to the pool once the transaction is complete. > >>Adding connection pooling support to SDB would be good. There's nothing >>there except an assembler setting to set the number and a comment in >>SDBConnection about where it might hook in ... but that's as far as it goes > > There is a "pool" comment in SDBConnection but it looks like the place to add > pooling would be in the SDBConnectionFactory (which also has a pooling > comment). It currently obtains a java.sql.Connection using > JDBC.createConnection. I think I can figure out where to inject connection > pooling but I'm not quite sure what its' doing when sdb:jdbcUrl is not > specified in the desc file. > >> >>Not sure of the status of c3po ... one preview release in 2010-05 but before >>that 2007-05. Maybe Apache Commons DBCP is a better bet. > > I'm certainly not married to c3p0...it's just what I found to be a bit more > reliable a few years ago when I was implementing connection pooling in one of > my web applications. >
