Grouper Users - Open Discussion List

[Baron Fujimoto <>]

I'm attempted an upgrade of v2.2.2 to 2.4.0 using the grouperInstaller. I 
encounter an error when attempting to upgrade the DB registry though. After 
grinding along for more than four(!) days of elapsed time at this point[*], 
it crashes trying to add a foreign key constraint.

[*] This seems like a really long time to execute, is this typical? We're 
going to have to try to find some way to mitigate this for production 
systems. It seemed to spend most of its time in the ADD CONSTRAINT set of 
statements.

=====
##################################
Checking API database version with command: 
/home/grouper/grouper/grouper.apiBinary-2.2.2-upgrade-to-2.4/bin/gsh.sh 
-registry -check -noprompt

...
stdout: Detected Grouper directory structure 'api' (valid is api or webapp)
Using GROUPER_HOME:           
/home/grouper/grouper/grouper.apiBinary-2.2.2-upgrade-to-2.4
Using GROUPER_CONF:           
/home/grouper/grouper/grouper.apiBinary-2.2.2-upgrade-to-2.4/conf
Using JAVA:                   /usr/java/latest/bin/java
Using CLASSPATH:              
/home/grouper/grouper/grouper.apiBinary-2.2.2-upgrade-to-2.4/conf:/home/grouper/grouper/grouper.apiBinary-2.2.2-upgrade-to-2.4/dist/lib/grouper.jar:/home/grouper/grouper/grouper.apiBinary-2.2.2-upgrade-to-2.4/lib/grouper/*:/home/grouper/grouper/grouper.apiBinary-2.2.2-upgrade-to-2.4/lib/custom/*:/home/grouper/grouper/grouper.apiBinary-2.2.2-upgrade-to-2.4/lib/jdbcSamples/*:/home/grouper/grouper/grouper.apiBinary-2.2.2-upgrade-to-2.4/lib/ant/*:/home/grouper/grouper/grouper.apiBinary-2.2.2-upgrade-to-2.4/lib/test/*:/home/grouper/grouper/grouper.apiBinary-2.2.2-upgrade-to-2.4/dist/lib/test/*:/home/grouper/grouper/grouper.apiBinary-2.2.2-upgrade-to-2.4/src/resources
using MEMORY:                 64m-750m
Grouper starting up: version: 2.4.0, build date: null, env: grouper-dev
grouperPatchStatus read from: 
/home/grouper/grouper/grouper.apiBinary-2.2.2-upgrade-to-2.4/grouperPatchStatus.properties
api patches installed:        0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 
14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 
33, 34, 35, 36, 37, 38, 39, 40
grouper.properties read from: 
/home/grouper/grouper/grouper.apiBinary-2.2.2-upgrade-to-2.4/conf/grouper.properties
Grouper current directory is: 
/home/grouper/grouper/grouper.apiBinary-2.2.2-upgrade-to-2.4/bin
log4j.properties read from:   
/home/grouper/grouper/grouper.apiBinary-2.2.2-upgrade-to-2.4/conf/log4j.properties
Grouper is logging to file:   
/home/grouper/grouper/logs/grouper-api_error.log, at min level WARN for 
package: edu.internet2.middleware.grouper, based on log4j.properties
grouper.hibernate.properties: 
/home/grouper/grouper/grouper.apiBinary-2.2.2-upgrade-to-2.4/conf/grouper.hibernate.properties
grouper.hibernate.properties: 
user@jdbc:mysql://foo.hawaii.edu:3306/grprdev?CharSet=utf8&useUnicode=true&characterEncoding=utf8
subject.properties read from: 
/home/grouper/grouper/grouper.apiBinary-2.2.2-upgrade-to-2.4/conf/subject.properties
sources configured in:        subject.properties
subject.properties groupersource id: g:gsa
subject.properties groupersource id: grouperEntities
subject.properties ldap source id:   LDAP: null

[...]

##################################
Upgrading database with command: 
/home/grouper/grouper/grouper.apiBinary-2.2.2-upgrade-to-2.4/bin/gsh.sh 
-registry -noprompt -runsqlfile 
/home/grouper/grouper/grouper.apiBinary-2.2.2-upgrade-to-2.4/ddlScripts/grouperDdl_20190423_15_49_23_405.sql

........................................
[...]
........................................

Apr 26, 2019 3:11:55 PM 
edu.internet2.middleware.grouperInstaller.util.GrouperInstallerUtils 
execCommandHelper
SEVERE: Process exit status=1: out: Detected Grouper directory structure 
'api' (valid is api or webapp)
[...]
Error running script: 
/home/grouper/grouper/grouper.apiBinary-2.2.2-upgrade-to-2.4/ddlScripts/grouperDdl_20190423_15_49_23_405.sql,
 java.sql.SQLException: Cannot add foreign key constraint
[...]
=====

I think this error was encountered attempting to execute,

ALTER TABLE grouper_message
    ADD CONSTRAINT fk_message_from_member_id FOREIGN KEY (from_member_id) 
REFERENCES grouper_members (id);

Our DBA extracted the following from their end:

2019-04-26 15:11:46 0x7f3aafa37700 Error in foreign key constraint of table 
grprdev/#sql-d27_1c146:
 FOREIGN KEY (from_member_id) REFERENCES grouper_members (id):
Cannot find an index in the referenced table where the referenced columns 
appear as the first columns, or column types in the table and the referenced 
table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in tables created 
with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by 
such columns in new tables.
Please refer to 
http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html 
for correct foreign key definition.

What's the best way to resume or recover from this to proceed?

-- 
Baron Fujimoto <> :: UH Information Technology Services
minutas cantorum, minutas balorum, minutas carboratum desendus pantorum