Chris,
Wow, thanks. You sound like you've really been through it (the migration).
Seems like quite a pain migrating to PostgreSQL from MySQL. But what if I don't have data to
migrate? What if I just start over with PostgreSQL? Any problems? I'll take your advice regarding
the migration gotchas. Thanks.
Actually, about DBCPs. For RDBMSes, it's correct for database connections to time out after a set
interval of inactivity. That's just prudence. When connections are used inside of DBCPs, it is the
DBCP's responsibility to refresh timed out connections in the pool. I did my very first very own
DBCP more than a decade ago, and that was one of the must-have functionalities for a DBCP. I was
beaten up real bad for missing that out. :)
The fix for this, in OFBiz, is in OFBiz's DBCP --- XAPoolDataSource. I posted another message
asking for the source codes to ofbiz-minerva.jar.
Jonathon
Chris Howe wrote:
This does not occur in PostgreSQL. It is a "feature" of MySQL and they
(mysql) will smugly say that OFBiz doesn't handle the connection pool
correctly. I don't know and don't really care to know if it's true or
not. I switched over about 2 months ago and have had smooth sailing
since (even seemingly eliminated that UserLoginHistory bug that you're
aware of).
Be warned, it's a bit of a pain to convert from MySQL to Postgres.
Most of the issues seem to be of how lax MySQL with data and how
stringent PostgreSQL is(at least the default installation). These were
some of the issues I came across with my data using the export/import
in webtools
1. the createdBy fields in the various entities weren't in the correct
case (i believe this has been solved in OFBiz, I just had data that
predated the fix)
2. UserLogin and Party entites end up with a circular dependency based
on the partyId admin if the UserLogin admin created parties. Either
load the single Party record for partyId before loading the UserLogin
entity or remove the createdBy data from the Party entity
3. Heirarchial parent->child relationships. This occurs with the *Type
entities. They simply need to be loaded in the correct order. There
is a JIRA issue which solves this problem for about the *Type entities
where the child is childId and the parent is parentChildId (e.g.
partyTypeId -> parentPartyTypeId)
There may have been other referential integrity issues, but I think
they were self created and not created by OFBiz.
--- Jonathon -- Improov <[EMAIL PROTECTED]> wrote:
MySQL connections in the database connection pool time out (usually
set to 8 hours). Possible
workarounds(?):
1. Increase the time out value and hope that someone will connect to
OFBiz
before the connections time out.
2. Fix OFBiz to allow to a "validateQuery" mechanism.
3. Use PostgreSQL.
In Tomcat, we usually use the "validateQuery" so the DBCP will test
each connection before giving
it to the application. If all connections in the pool has timed out
(say no one has accessed OFBiz
in 8 hours), the DBCP creates new connections for the pool.
If someone will tell me that this doesn't happen for PostgreSQL, I'll
simply make the switch to
PostgreSQL rather than fix things in OFBiz for MySQL.
Thanks.
Jonathon
