search

3dcitydb / importer-exporter

3D City Database client for high-performance import and export of 3D city model data
Apache License 2.0
126 stars 54 forks source link

Hotfix export timeout issue #261

Closed yaozhihang closed 2 years ago

yaozhihang commented 2 years ago

Fix the issue #257.

It seems that the issue is caused by the very long where clause in the root select statement. So, putting the long where clause in a subquery solves the issue.

BWibo commented 2 years ago

@yaozhihang I just created a fresh build and tested this with the workaround described in https://github.com/3dcitydb/importer-exporter/issues/257#issuecomment-1191038324 enabled and disabled and can confirm this works! Thx for the quick fix.

clausnagel commented 2 years ago

@yaozhihang, thanks for proposing this hotfix to solve #257. However, I'm not sure whether this is really a "fix". Imho, the query optimizer should deal with "long" where clauses automatically, not the application code. Rearranging the query in the application code as proposed might help in the context of #257, but might have no (or even a bad) impact in other scenarios. For instance, the getNumberMatched method in DBSplitter still uses the unchanged query and yet performs good.

And does your change affect exports from Oracle?

@BWibo, have you already tried and "vacuumed" your database? Maybe this already helps?

clausnagel commented 2 years ago

@BWibo, thanks for vacuuming the database. Unfortunately, I again ran into the issue in my export test. So, it seems that vacuuming might help but does not solve the issue.

clausnagel commented 2 years ago

When adding DatabaseConnectionPool.getInstance().purge(); as line 591 to the class org.citydb.core.operation.exporter.controller.Exporter (so, as last line of the finally block), the export also completes without issues in my tests.

This seems to support that re-arranging the query is not necessary. As far as I understand, purge() closes all idle connections. This is of course not what we want in the first place, because we use a pool to avoid having to physically open connections all the time. But still it shows that there seems to be an issue with pool maintenance and handling of idle connections (maybe even a Tomcat JDBC pool bug?).

yaozhihang commented 2 years ago

clausnagel's solution looks better. It ensures that the pool produces clean connections for each export.

clausnagel commented 2 years ago

It seems that PR #264 also solves the issue #257 and thus makes this PR obsolete. @yaozhihang and @BWibo, can you please test and confirm?

clausnagel commented 2 years ago

Discarding this PR due to #263