Tomcat 8 and JDBC 4

Hi Mitch et al.,

Recent Aggregate release notes included the following:

Updated to expect Tomcat 8 and JDBC 4 libraries. This extensively impacted
the datastore layer.

Since many of us have existing Aggregate (or Aggregate-derived)
installations running Tomcat 6, I wonder if anybody has any high-level
advice on the upgrade path?

We've experimented occasionally with Tomcat 7 and 8, and we haven't noticed
any major issues -- but then we've always been afraid to update production
servers for fear of unknowns. Were all of the big updates to do with JDBC 4
rather than Tomcat 8? Any advice to those migrating existing installations?

Thanks very much,

Chris

The update to Tomcat 8 didn't significantly affect the software.

The primary change was the addition of a META-INF/context.xml for the
application. That disables scanning of the jars for container extensions
and sets the http-only flag to false (for GWT). It therefore eliminates the
extra config that was previously required on servlet 3.x containers and the
scanning flags should reduce the spin-up time for the server.

Otherwise, we are still (and only) relying on Servlet 2.5 functionality
(i.e., that present in Tomcat 6).

Thus, the WAR files should work in any web container supporting Servlet 2.5
or higher functionality.

The update also changes the database connection pool to the apache dbcp2
library (and dozens of other jars). This probably impacts Survey CTO
somewhat. That change shifted the connection verification to occur before
borrowing a connection. This might alter performance characteristics. Also,
the size of the idle connection pool is significantly reduced.

The update to JDBC4 was significant in many ways. The code now explicitly
manages data types for value binds. Before, it let the system apply the
bind. This caused issues on the upgrade because some numeric types seemed
to be erroneously treated as strings after the upgrade. By explicitly
managing the binds, the proper data typing was preserved.

Also with this update, the MySQL storage of dates has been changed to
request a higher precision timestamp when creating data tables. Previously,
times were truncated to seconds. With newly created tables, times can be
stored down to the millisecond accuracy captured within ODK Collect (this
is specific to MySQL). This change requires MySQL 5.6 or higher. On a
clean database, this should slightly improve task lock performance when a
server is heavily stressed.

··· ------------------ w.r.t. upgrades

Obviously, back up your database first.

Take an outage and migrate your database to the newer versions (the new
software requires higher minimum db versions).
Note that the PostgreSQL create_db_and_user.sql script is missing an alter
statement on the schema (see release notes).
On MySQL, drop the _task_lock table (this will be recreated and does not
need to be preserved across outages).

Once you've upgraded your database, updating to Tomcat 8 and the new WAR
should be fine.

I don't think it is worth the time to do this for typical studies, but, if
you are using MySQL, pulling the dataset into ODK Briefcase before the
upgrade and pushing it up into a empty database has the advantage of
creating the data tables with the higher-precision timestamp fields. You
could also issue alter table modify column commands to accomplish the same
thing. ODK Aggregate will use whatever accuracy the database table provides.

On Wed, Apr 5, 2017 at 4:53 AM, Christopher Robert chrislrobert@gmail.com wrote:

Hi Mitch et al.,

Recent Aggregate release notes included the following:

Updated to expect Tomcat 8 and JDBC 4 libraries. This extensively
impacted the datastore layer.

Since many of us have existing Aggregate (or Aggregate-derived)
installations running Tomcat 6, I wonder if anybody has any high-level
advice on the upgrade path?

We've experimented occasionally with Tomcat 7 and 8, and we haven't
noticed any major issues -- but then we've always been afraid to update
production servers for fear of unknowns. Were all of the big updates to do
with JDBC 4 rather than Tomcat 8? Any advice to those migrating existing
installations?

Thanks very much,

Chris

--
You received this message because you are subscribed to the Google Groups
"ODK Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to opendatakit-developers+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Mitch Sundt
Software Engineer
University of Washington
mitchellsundt@gmail.com

Thanks so much for this, Mitch!

In SurveyCTO, we store data as flat XML, and we had switched to C3P0
connection pools -- so some of these changes don't likely affect us. But
the reduced spin-up time is fascinating, and in fact right now we're
working on optimizing both launch and un-launch time for virtual servers.

The Tomcat 8 news is encouraging and perhaps we'll start experimenting with
some production servers. We'll report back if we find any leaks or other
run-time issues with Tomcat 8.

Thanks again!

Chris

··· On Thu, Apr 6, 2017 at 12:41 PM, Mitch Sundt wrote:

The update to Tomcat 8 didn't significantly affect the software.

The primary change was the addition of a META-INF/context.xml for the
application. That disables scanning of the jars for container extensions
and sets the http-only flag to false (for GWT). It therefore eliminates the
extra config that was previously required on servlet 3.x containers and the
scanning flags should reduce the spin-up time for the server.

Otherwise, we are still (and only) relying on Servlet 2.5 functionality
(i.e., that present in Tomcat 6).

Thus, the WAR files should work in any web container supporting Servlet
2.5 or higher functionality.

The update also changes the database connection pool to the apache dbcp2
library (and dozens of other jars). This probably impacts Survey CTO
somewhat. That change shifted the connection verification to occur before
borrowing a connection. This might alter performance characteristics. Also,
the size of the idle connection pool is significantly reduced.

The update to JDBC4 was significant in many ways. The code now explicitly
manages data types for value binds. Before, it let the system apply the
bind. This caused issues on the upgrade because some numeric types seemed
to be erroneously treated as strings after the upgrade. By explicitly
managing the binds, the proper data typing was preserved.

Also with this update, the MySQL storage of dates has been changed to
request a higher precision timestamp when creating data tables. Previously,
times were truncated to seconds. With newly created tables, times can be
stored down to the millisecond accuracy captured within ODK Collect (this
is specific to MySQL). This change requires MySQL 5.6 or higher. On a
clean database, this should slightly improve task lock performance when a
server is heavily stressed.


w.r.t. upgrades

Obviously, back up your database first.

Take an outage and migrate your database to the newer versions (the new
software requires higher minimum db versions).
Note that the PostgreSQL create_db_and_user.sql script is missing an alter
statement on the schema (see release notes).
On MySQL, drop the _task_lock table (this will be recreated and does not
need to be preserved across outages).

Once you've upgraded your database, updating to Tomcat 8 and the new WAR
should be fine.

I don't think it is worth the time to do this for typical studies, but, if
you are using MySQL, pulling the dataset into ODK Briefcase before the
upgrade and pushing it up into a empty database has the advantage of
creating the data tables with the higher-precision timestamp fields. You
could also issue alter table modify column commands to accomplish the same
thing. ODK Aggregate will use whatever accuracy the database table provides.

On Wed, Apr 5, 2017 at 4:53 AM, Christopher Robert <chrislrobert@gmail.com wrote:

Hi Mitch et al.,

Recent Aggregate release notes included the following:

Updated to expect Tomcat 8 and JDBC 4 libraries. This extensively
impacted the datastore layer.

Since many of us have existing Aggregate (or Aggregate-derived)
installations running Tomcat 6, I wonder if anybody has any high-level
advice on the upgrade path?

We've experimented occasionally with Tomcat 7 and 8, and we haven't
noticed any major issues -- but then we've always been afraid to update
production servers for fear of unknowns. Were all of the big updates to do
with JDBC 4 rather than Tomcat 8? Any advice to those migrating existing
installations?

Thanks very much,

Chris

--
You received this message because you are subscribed to the Google Groups
"ODK Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to opendatakit-developers+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Mitch Sundt
Software Engineer
University of Washington
mitchellsundt@gmail.com

--
You received this message because you are subscribed to the Google Groups
"ODK Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to opendatakit-developers+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.