Reinstall data from MySQL data backup

katherine.gardiner · June 30, 2015, 6:19pm

We have been collecting data via tablets in South Africa, and uploading it to ODK Aggregate based in the UK on a tomcat server underpinned by MySQL. We had been taking regular extractions via briefcase but there were some issues with these. The VM hosting the data then crashed and the data on it were lost. We have been able to rebuild most of the dataset from the briefcase extractions but three weeks of data are missing.

We have recovered the MySQL database (in text files) from a back-up,and are wondering whether it is possible to re-import the data to ODK or to repoint ODK at the rebuilt database?

Mitch_S · June 30, 2015, 7:25pm

If you succeed in restoring your database, you should be able to re-run the
ODK Aggregate installer and specify the location of the new database to ODK
Aggregate.

You may or may not need to run the create_db_and_user.sql script that the
installer created to enable ODK Aggregate to access the database. If you
log into the database as that user from your ODK Aggregate server, you
should be able to see all of the restored database tables. If you cannot,
you will need to log in as the database root user and issue commands like
those in this script file to grant that user access.

Once you have done that, the new ODK Aggregate war should be copied to your
Tomcat server and should start up and connect to your database.

···

On Tue, Jun 30, 2015 at 11:19 AM, katherine.gardiner@spi.ox.ac.uk < katherine.robson@gmail.com> wrote:

We have been collecting data via tablets in South Africa, and uploading it
to ODK Aggregate based in the UK on a tomcat server underpinned by MySQL.
We had been taking regular extractions via briefcase but there were some
issues with these. The VM hosting the data then crashed and the data on it
were lost. We have been able to rebuild most of the dataset from the
briefcase extractions but three weeks of data are missing.

We have recovered the MySQL database (in text files) from a back-up,and
are wondering whether it is possible to re-import the data to ODK or to
repoint ODK at the rebuilt database?

--

Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en

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

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

katherine.gardiner · July 1, 2015, 10:09am

Thank you very much for your advice - we shall give that a go!

yanokwa · June 30, 2015, 7:27pm

I can confirm that this strategy works. We just did it a few weeks back
for a client with a corrupt MySQL database.

Yaw

···

-- Need ODK services? http://nafundi.com provides form design, server setup, professional support, and software development for ODK.

On Tue, Jun 30, 2015 at 2:25 PM, Mitch Sundt mitchellsundt@gmail.com wrote:

If you succeed in restoring your database, you should be able to re-run the
ODK Aggregate installer and specify the location of the new database to ODK
Aggregate.

You may or may not need to run the create_db_and_user.sql script that the
installer created to enable ODK Aggregate to access the database. If you
log into the database as that user from your ODK Aggregate server, you
should be able to see all of the restored database tables. If you cannot,
you will need to log in as the database root user and issue commands like
those in this script file to grant that user access.

Once you have done that, the new ODK Aggregate war should be copied to your
Tomcat server and should start up and connect to your database.

On Tue, Jun 30, 2015 at 11:19 AM, katherine.gardiner@spi.ox.ac.uk katherine.robson@gmail.com wrote:

We have been collecting data via tablets in South Africa, and uploading it
to ODK Aggregate based in the UK on a tomcat server underpinned by MySQL. We
had been taking regular extractions via briefcase but there were some issues
with these. The VM hosting the data then crashed and the data on it were
lost. We have been able to rebuild most of the dataset from the briefcase
extractions but three weeks of data are missing.

We have recovered the MySQL database (in text files) from a back-up,and
are wondering whether it is possible to re-import the data to ODK or to
repoint ODK at the rebuilt database?

--

Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en

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

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

--

Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en

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

Fergus_Gallagher · July 3, 2015, 8:02am

https://lh3.googleusercontent.com/-9XZ_oLHOKps/VZY-8ZkoNDI/AAAAAAAAB74/wD4bngGCnOE/s1600/ss1.png
I have done this:

I started with a completely clean system (hand-built Ubuntu VM in my
case)
Installed ODK, tomcat, etc as per instructions. I have ensured that the
old and new ODK versions are the same.
All I have are the .sql from a dump from the old (failed) ODK
Installed DB:

$ cat create_db_and_user.sql > mysql -u root -p
$ cat *.sql > mysql -u odk_user -p odk_prod # NO ERRORS

Copied the WAR to tomcat/webapps/ROOT.war
started tomcat

So far, so good. I can now login. But all my forms on the home page show
no data. I can see the header line, but no data

But quite a few errors on catalina.out like

INFO: Loading XML bean definitions from class path resource
[org/springframework/jdbc/support/sql-error-codes.xml]
Jul 03, 2015 8:45:07 AM
org.springframework.jdbc.support.SQLErrorCodesFactory
INFO: SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL,
Oracle, PostgreSQL, Sybase]
org.springframework.dao.DataAccessResourceFailureException:
PreparedStatementCallback; SQL [SELECT _URI, _CREATOR_URI_USER,
_CREATION_DATE, _LAST_UPDATE_URI_USER, _LAST_UPDATE_DATE, KEY,
VALUE FROM odk_prod._server_preferences_properties WHERE KEY = ?
ORDER BY _LAST_UPDATE_DATE DESC ;]; No operations allowed after
connection closed.; nested exception is
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No
operations allowed after connection closed.
at
org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:92)
...

All of the errors have that tell-tale "No operations allowed after
connection closed."

I am not an ODK user, just a techie asked to try and recover the system.
What steps have I missed - do I need more than just the SQL to recover?

Thanks,

Fergus

···

On Tuesday, 30 June 2015 20:25:03 UTC+1, Mitch Sundt wrote: > > If you succeed in restoring your database, you should be able to re-run > the ODK Aggregate installer and specify the location of the new database to > ODK Aggregate. > > You may or may not need to run the *create_db_and_user.sql* script that > the installer created to enable ODK Aggregate to access the database. If > you log into the database as that user from your ODK Aggregate server, you > should be able to see all of the restored database tables. If you cannot, > you will need to log in as the database root user and issue commands like > those in this script file to grant that user access. > > Once you have done that, the new ODK Aggregate war should be copied to > your Tomcat server and should start up and connect to your database. > > >

Fergus_Gallagher · July 3, 2015, 8:32am

I'm thinking this may be a red-herring, perhaps a race condition at tomcat
startup. I don't see it any more, even when loading the same pages.

···

On Friday, 3 July 2015 09:02:37 UTC+1, Fergus Gallagher wrote: > > > But quite a few errors on catalina.out like > > INFO: Loading XML bean definitions from class path resource > [org/springframework/jdbc/support/sql-error-codes.xml] > Jul 03, 2015 8:45:07 AM > org.springframework.jdbc.support.SQLErrorCodesFactory > INFO: SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, > MySQL, Oracle, PostgreSQL, Sybase] > org.springframework.dao.DataAccessResourceFailureException: > PreparedStatementCallback; SQL [SELECT `_URI`, `_CREATOR_URI_USER`, > `_CREATION_DATE`, `_LAST_UPDATE_URI_USER`, `_LAST_UPDATE_DATE`, `KEY`, > `VALUE` FROM `odk_prod`.`_server_preferences_properties` WHERE `KEY` = ? > ORDER BY `_LAST_UPDATE_DATE` DESC ;]; No operations allowed after > connection closed.; nested exception is > com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No > operations allowed after connection closed. > at > org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:92) > ... > > > All of the errors have that tell-tale "No operations allowed after > connection closed." >

yanokwa · July 4, 2015, 4:10pm

Do any of your database tables have good data?
https://code.google.com/p/opendatakit/wiki/AggregateDatabaseStructure
should help identifiy the tables.

Yaw

···

-- Need ODK services? http://nafundi.com provides form design, server setup, professional support, and software development for ODK.

On Fri, Jul 3, 2015 at 3:32 AM, Fergus Gallagher fergus.gallagher@gmail.com wrote:

I'm thinking this may be a red-herring, perhaps a race condition at tomcat
startup. I don't see it any more, even when loading the same pages.

On Friday, 3 July 2015 09:02:37 UTC+1, Fergus Gallagher wrote:

But quite a few errors on catalina.out like

INFO: Loading XML bean definitions from class path resource
[org/springframework/jdbc/support/sql-error-codes.xml]
Jul 03, 2015 8:45:07 AM
org.springframework.jdbc.support.SQLErrorCodesFactory
INFO: SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL,
MySQL, Oracle, PostgreSQL, Sybase]
org.springframework.dao.DataAccessResourceFailureException:
PreparedStatementCallback; SQL [SELECT _URI, _CREATOR_URI_USER,
_CREATION_DATE, _LAST_UPDATE_URI_USER, _LAST_UPDATE_DATE, KEY,
VALUE FROM odk_prod._server_preferences_properties WHERE KEY = ?
ORDER BY _LAST_UPDATE_DATE DESC ;]; No operations allowed after connection
closed.; nested exception is
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No
operations allowed after connection closed.
at
org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:92)
...

All of the errors have that tell-tale "No operations allowed after
connection closed."

--

Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en

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

Fergus_Gallagher · July 5, 2015, 10:54am

Yes, I think - so lots of _ref, _blb and _bn (not _bin as in that link).
Some quite big (mostly the _blb)

If the data is indeed there, can you suggest any reason why the data
doesn't show?

Help....deadline approaching...

···

On Saturday, 4 July 2015 17:10:47 UTC+1, Yaw Anokwa wrote: > > Do any of your database tables have good data? > https://code.google.com/p/opendatakit/wiki/AggregateDatabaseStructure > should help identifiy the tables. > > Yaw > -- > >

Fergus_Gallagher · July 5, 2015, 11:50am

We have COREx files too.

···

On Sunday, 5 July 2015 11:54:23 UTC+1, Fergus Gallagher wrote: > > Yes, I think - so lots of _ref, _blb and _bn (not _bin as in that link). > Some quite big (mostly the _blb) > > If the data is indeed there, can you suggest any reason why the data > doesn't show? >

Mitch_S · July 6, 2015, 7:00pm

This looks like a problem with the JDBC connection pool configuration.

What version of ODK Aggregate are you using?
And what version did you have running before that?

Beginning with ODK Aggregate 1.4.6, we switched connection pool
implementations to the more-standard c3p0.

If you had moved the database connection pool configuration up out of the
WAR into the webserver, you will need to update it (the previous connection
pool used bonecp).

···

On Sun, Jul 5, 2015 at 4:50 AM, Fergus Gallagher <fergus.gallagher@gmail.com wrote:

We have COREx files too.

On Sunday, 5 July 2015 11:54:23 UTC+1, Fergus Gallagher wrote:

Yes, I think - so lots of _ref, _blb and _bn (not _bin as in that
link). Some quite big (mostly the _blb)

If the data is indeed there, can you suggest any reason why the data
doesn't show?

--

Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en

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

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

Fergus_Gallagher · July 7, 2015, 8:14am

Thanks for the reply.

My ODK is a "fresh out of the box" 1.4.5, purely vanilla install. I have
done no customization at all - by the books as per the installation
instructions (apart from the SQL import, of course)

I originally tried the most recent 1.4.7 but downgraded when I couldn't see
any data (I completely blitzed the MySQL and reinstalled). V 1.4.5 was
what was on the old (now dead) VM.

For java versions I have tried OpenJDK 1.7.0_45, Oracle jdk1.7.0_79,
Oracle jdk1.8.0_40

···

On Monday, 6 July 2015 20:00:33 UTC+1, Mitch Sundt wrote: > > This looks like a problem with the JDBC connection pool configuration. > > What version of ODK Aggregate are you using? > And what version did you have running before that? > > Beginning with ODK Aggregate 1.4.6, we switched connection pool > implementations to the more-standard c3p0. > > If you had moved the database connection pool configuration up out of the > WAR into the webserver, you will need to update it (the previous connection > pool used bonecp). > > >

Mitch_S · July 7, 2015, 7:55pm

You should be using a Java 7 install.
We do not support Java 8 (1.8.xxx).
We test and build with the Oracle JDK.

To confirm: you downloaded and installed the MySQL Connector/J jar?

In a vanilla install, errors should be logged to:

tomcat6-stdout...
tomcat6-stderr...

What do you see in those?

···

On Tue, Jul 7, 2015 at 1:14 AM, Fergus Gallagher <fergus.gallagher@gmail.com wrote:

Thanks for the reply.

My ODK is a "fresh out of the box" 1.4.5, purely vanilla install. I have
done no customization at all - by the books as per the installation
instructions (apart from the SQL import, of course)

I originally tried the most recent 1.4.7 but downgraded when I couldn't
see any data (I completely blitzed the MySQL and reinstalled). V 1.4.5 was
what was on the old (now dead) VM.

For java versions I have tried OpenJDK 1.7.0_45, Oracle jdk1.7.0_79,
Oracle jdk1.8.0_40

On Monday, 6 July 2015 20:00:33 UTC+1, Mitch Sundt wrote:

This looks like a problem with the JDBC connection pool configuration.

What version of ODK Aggregate are you using?
And what version did you have running before that?

Beginning with ODK Aggregate 1.4.6, we switched connection pool
implementations to the more-standard c3p0.

If you had moved the database connection pool configuration up out of the
WAR into the webserver, you will need to update it (the previous connection
pool used bonecp).

--
--
Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en

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

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