Chris, Hans,
Hans, first, thanks for your link to:
http://www.nabble.com/MySQL-connection-problem-tf2486297.html#a9092103
FYI to all. I just tested the fix. Doesn't work. I still need the XAPoolDataSource source codes to
fix this.
Did Katherine Morgan post anything else? The fix was suggested by her.
For those of you using MySQL, if you wanna test this out, edit your my.cnf file, maybe at
/etc/my.cnf. Under [mysqld], enter "wait_timeout=60". When OFBiz is fully started up, wait for 1
minute, then try to do a login.
Don't set it to 10 seconds, or you might never get it to work even if the DBCP functions
correctly! OFBiz processes may take out a DB connection for more than 10 seconds, you never know.
Jonathon
Jonathon -- Improov wrote:
Chris,
You mean go from MySQL to Xindice to PostgreSQL? Yeah, I know, data
migration can be a pain, even without data-mapping efforts to go with it
(ie, same structure migrated to same structure).
> My limited reading through mailing lists on mysql and postgres show
that the
> timeout handling should be handled by the client (ofbiz) and that mysql
> decided to provided an additional safe gaurd by moving an excessive,
> configurable, "catch-all" timeout to the server.
If you're saying that a DBCP should assume that a DB connection can
never time out (always good), I rest my case. :) Personally, I think
MySQL could've provided the option to drop the time out feature.
FYI to all. If Hans is right, it appears OFBiz's DBCP XAPoolDataSource
does handle connection time out after all! I'll check it out.
Anyway, the fix, as mentioned by Hans, can be done in
MinervaConnectionFactory. I'm gonna try just that. Problem solved. On
with real work. You're right, OFBiz does abstract tons of infrastructure
details away from application developers.
Would be good if we can get our hands on the XAPoolDataSource source
codes, just to confirm that the issue is fixed.
Jonathon
Chris Howe wrote:
Most of the data migration issues should be solvable by reading the
migration data files (entity-engine-xml) into a Xindice XML database
and running ofbiz services from there. This will allow XPath queries
for the hierarchical stuff, and XUpdate of children elements for error
messages and thus group error handling using XPath. I hope to be able
to get some of this together shortly as we've been using OFBiz to
slowly bring our legacy data in line with the OFBiz data model and the
legacy data is still in MySQL (and much more critical and can't really
take the risk that I "think" I changed the right data).
If you don't have data, there shouldn't be any issue. Simply run the
ant run-install with a delegator calling the postgres datasource. The
impression I get is that most of the community in production is using
postgres unless they've purchased a license to a mssql or oracle.
My limited reading through mailing lists on mysql and postgres show
that the timeout handling should be handled by the client (ofbiz) and
that mysql decided to provided an additional safe gaurd by moving an
excessive, configurable, "catch-all" timeout to the server.
One of my favorite things about OFBiz is that you can get pretty far in
developing something useful without ever knowing or worrying about
database administration like this and can come back and address it when
you're fine tuning your deployment for bottlenecks before going live.
--- Jonathon -- Improov <[EMAIL PROTECTED]> wrote:
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
