Error starting ODK in Tomcat with MySQL 5.1.73

We have installed ODK Aggregate on a RHEL 6 box and running MySQL 5.1.73. According to the installation notes, ODK has been tested on 5.1 (https://opendatakit.org/use/aggregate/tomcat-install/). I am familiar with MySQL but am unsure about this error. On application initialization, it is attempting to create the _granted_authority_hierarchy table but throwing an error because apparently the DATETIME(6) data type is not recognized.

the error is below. I have executed the statement without the 2 datetime fields and the statement works.

Anyone know how to correct this before I pull my hair out? I have scanned the MySQL 5.1 docs and don't see mention of this - http://download.nust.na/pub6/mysql/doc/refman/5.1/en/datetime.html

Thank you in advance.

Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE TABLE webappdb._granted_authority_hierarchy ( _URI VARCHAR(80) CHARACTER SET utf8 NOT NULL , _CREATOR_URI_USER VARCHAR(80) CHARACTER SET utf8 NOT NULL , _CREATION_DATE DATETIME(6) NOT NULL , _LAST_UPDATE_URI_USER VARCHAR(80) CHARACTER SET utf8 NULL , _LAST_UPDATE_DATE DATETIME(6) NOT NULL , DOMINATING_GRANTED_AUTHORITY VARCHAR(80) CHARACTER SET utf8 NOT NULL , SUBORDINATE_GRANTED_AUTHORITY VARCHAR(80) CHARACTER SET utf8 NOT NULL , INDEX(_URI ) USING HASH , INDEX(_LAST_UPDATE_DATE ) , INDEX(DOMINATING_GRANTED_AUTHORITY ) USING HASH ) ]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(6) NOT NULL , _LAST_UPDATE_URI_USER VARCHAR(80) CHARACTER SET utf8 NULL , `_L' at line 1

Hi there,

The release notes say you need a minimum of MySQL 5.7. Where are you seeing 5.1?

Are any of the other Aggregate tables created when you look at the DB?
If no, then you might have a JDBC or permissions problem.

Yaw

··· On Wed, Jan 25, 2017 at 6:31 AM, wrote: > We have installed ODK Aggregate on a RHEL 6 box and running MySQL 5.1.73. According to the installation notes, ODK has been tested on 5.1 (https://opendatakit.org/use/aggregate/tomcat-install/). I am familiar with MySQL but am unsure about this error. On application initialization, it is attempting to create the _granted_authority_hierarchy table but throwing an error because apparently the DATETIME(6) data type is not recognized. > > the error is below. I have executed the statement without the 2 datetime fields and the statement works. > > Anyone know how to correct this before I pull my hair out? I have scanned the MySQL 5.1 docs and don't see mention of this - http://download.nust.na/pub6/mysql/doc/refman/5.1/en/datetime.html > > Thank you in advance. > > Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE TABLE `webappdb`.`_granted_authority_hierarchy` ( `_URI` VARCHAR(80) CHARACTER SET utf8 NOT NULL , `_CREATOR_URI_USER` VARCHAR(80) CHARACTER SET utf8 NOT NULL , `_CREATION_DATE` DATETIME(6) NOT NULL , `_LAST_UPDATE_URI_USER` VARCHAR(80) CHARACTER SET utf8 NULL , `_LAST_UPDATE_DATE` DATETIME(6) NOT NULL , `DOMINATING_GRANTED_AUTHORITY` VARCHAR(80) CHARACTER SET utf8 NOT NULL , `SUBORDINATE_GRANTED_AUTHORITY` VARCHAR(80) CHARACTER SET utf8 NOT NULL , INDEX(`_URI` ) USING HASH , INDEX(`_LAST_UPDATE_DATE` ) , INDEX(`DOMINATING_GRANTED_AUTHORITY` ) USING HASH ) ]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(6) NOT NULL , `_LAST_UPDATE_URI_USER` VARCHAR(80) CHARACTER SET utf8 NULL , `_L' at line 1 > > -- > -- > 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.

Thank you Yaw and Mitch, I found the reference to 5.1 on an installation documentation page but now cannot see it - may have been my mind playing tricks on me...

I unfortunately cannot upgrade at this point so will have to install an older version.

For some reason i didn't get notified you had replied.

··· On Tuesday, January 24, 2017 at 3:31:58 PM UTC-8, cord....@gmail.com wrote: > We have installed ODK Aggregate on a RHEL 6 box and running MySQL 5.1.73. According to the installation notes, ODK has been tested on 5.1 (https://opendatakit.org/use/aggregate/tomcat-install/). I am familiar with MySQL but am unsure about this error. On application initialization, it is attempting to create the _granted_authority_hierarchy table but throwing an error because apparently the DATETIME(6) data type is not recognized. > > the error is below. I have executed the statement without the 2 datetime fields and the statement works. > > Anyone know how to correct this before I pull my hair out? I have scanned the MySQL 5.1 docs and don't see mention of this - http://download.nust.na/pub6/mysql/doc/refman/5.1/en/datetime.html > > Thank you in advance. > > Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE TABLE `webappdb`.`_granted_authority_hierarchy` ( `_URI` VARCHAR(80) CHARACTER SET utf8 NOT NULL , `_CREATOR_URI_USER` VARCHAR(80) CHARACTER SET utf8 NOT NULL , `_CREATION_DATE` DATETIME(6) NOT NULL , `_LAST_UPDATE_URI_USER` VARCHAR(80) CHARACTER SET utf8 NULL , `_LAST_UPDATE_DATE` DATETIME(6) NOT NULL , `DOMINATING_GRANTED_AUTHORITY` VARCHAR(80) CHARACTER SET utf8 NOT NULL , `SUBORDINATE_GRANTED_AUTHORITY` VARCHAR(80) CHARACTER SET utf8 NOT NULL , INDEX(`_URI` ) USING HASH , INDEX(`_LAST_UPDATE_DATE` ) , INDEX(`DOMINATING_GRANTED_AUTHORITY` ) USING HASH ) ]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(6) NOT NULL , `_LAST_UPDATE_URI_USER` VARCHAR(80) CHARACTER SET utf8 NULL , `_L' at line 1

As Yaw notes, the newer ODK Aggregate 1.4.13 release REQUIRES the newer
version. Prior releases and the earlier documentation specified MySQL 5.1
and newer.

In this particular case, with 1.4.13, we use new syntax to create DATETIME
columns that retain milliseconds (actually, microseconds, but the data from
ODK Collect is only down to the millisecond). The older MySQL databases
only retained values to the nearest second.

··· On Tue, Jan 24, 2017 at 6:52 PM, Yaw Anokwa wrote:

Hi there,

The release notes say you need a minimum of MySQL 5.7. Where are you
seeing 5.1?

Are any of the other Aggregate tables created when you look at the DB?
If no, then you might have a JDBC or permissions problem.

Yaw

On Wed, Jan 25, 2017 at 6:31 AM, cord.thomas@gmail.com wrote:

We have installed ODK Aggregate on a RHEL 6 box and running MySQL
5.1.73. According to the installation notes, ODK has been tested on 5.1 (
https://opendatakit.org/use/aggregate/tomcat-install/). I am familiar
with MySQL but am unsure about this error. On application initialization,
it is attempting to create the _granted_authority_hierarchy table but
throwing an error because apparently the DATETIME(6) data type is not
recognized.

the error is below. I have executed the statement without the 2
datetime fields and the statement works.

Anyone know how to correct this before I pull my hair out? I have
scanned the MySQL 5.1 docs and don't see mention of this -
http://download.nust.na/pub6/mysql/doc/refman/5.1/en/datetime.html

Thank you in advance.

Caused by: org.springframework.jdbc.BadSqlGrammarException:
StatementCallback; bad SQL grammar [CREATE TABLE webappdb._granted_authority_hierarchy
( _URI VARCHAR(80) CHARACTER SET utf8 NOT NULL , _CREATOR_URI_USER
VARCHAR(80) CHARACTER SET utf8 NOT NULL , _CREATION_DATE DATETIME(6) NOT
NULL , _LAST_UPDATE_URI_USER VARCHAR(80) CHARACTER SET utf8 NULL ,
_LAST_UPDATE_DATE DATETIME(6) NOT NULL , DOMINATING_GRANTED_AUTHORITY
VARCHAR(80) CHARACTER SET utf8 NOT NULL , SUBORDINATE_GRANTED_AUTHORITY
VARCHAR(80) CHARACTER SET utf8 NOT NULL , INDEX(_URI ) USING HASH ,
INDEX(_LAST_UPDATE_DATE ) , INDEX(DOMINATING_GRANTED_AUTHORITY )
USING HASH ) ]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '(6) NOT NULL ,
_LAST_UPDATE_URI_USER VARCHAR(80) CHARACTER SET utf8 NULL , `_L' at line 1

--

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.

--

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