Inserting users directly into the database?

Issue I'm hitting - how/where does Jive store its auto-increment fields it uses for its table IDs?

For testing a clients configuration we want to do a data dump from our system and create the equivalent users in a test on prem Jive instance.

My solution is very dumb/straight forward...

Create each of the profile fields the client needs.

SELECT MAX(`fieldID`)+1 FROM `jiveProfileField` INTO @next_jiveProfileField_fieldID; SELECT MAX(`idx`)+1 FROM `jiveProfileField` INTO @next_jiveProfileField_idx; INSERT IGNORE INTO `jiveProfileField` (`fieldID`,`name`,`isRequired`,`isFilterable`,`isSearchable`,`isVisibleToUsers`,`isVisibleToGuests`,`isEditable`,`isShowSummaryLabel`,`summaryIndex`,`fieldType`,`idx`,`isDefault`,`extManaged`,`extMapping`,`isList`,`regIdx`,`defaultSecLvlID`,`pseudo`) VALUES (@next_jiveProfileField_fieldID,'Department','0','1','1','1','1','1','1','-1','9',@next_jiveProfileField_idx,'1','0',null,'0','-2147483648','1001','0'); SELECT `fieldID` FROM `jiveProfileField` WHERE `name` = 'Department' INTO @Department_fieldID; 

Insert each of the clients users.

SELECT MAX(`userID`)+1 FROM `jiveUser` INTO @next_jiveUser_userID; INSERT IGNORE INTO `jiveUser` (         `userID`,         `username`,         `passwordHash`,         `firstName`,         `lastName`,         `nameVisible`,         `email`,         `emailVisible`,         `userEnabled`,         `creationDate`,         `modificationDate` ) VALUES (         @next_jiveUser_userID,         '1a1joh@rna.com',         '$2a$08$yvMdeKrZvMceTnLFhUF7DOvwo/n.Dq7Iv3o4GTw7vvFj2n0bAdp76',         'Adam',         'Johnson',         '1',         '1a1joh@rna.com',         '1',         '1',         '1429100995635',         '1429100995635' )

Insert the profile fields for each user.

SELECT MAX(`userProfileID`)+1 FROM `jiveUserProfile` INTO @next_jiveUserProfile_userProfileID; INSERT IGNORE INTO `jiveUserProfile` (   `userProfileID`,   `userID`,   `fieldID`,   `levelID`,   `value`,   `primaryVal` ) VALUES (         @next_jiveUserProfile_userProfileID,         @next_jiveUser_userID,         @Department_fieldID,         NULL,         '',         '0' )

What I've done works!

- that is until you actually try to update the profile fields of any of the users I create.  Jive in sbs.log will then throw:

2015-12-17 15:54:31,026 [http-nio-127.0.0.1-9001-exec-7] [2039:al@rna.com:REGULAR] ERROR com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor - org.springframework.dao.DuplicateKeyException: PreparedStatementCallback; SQL [INSERT INTO jiveUserProfile (userProfileID, userID, fieldID, levelID, value, primaryVal) VALUES (?, ?, ?, ?, ?, ?)]; Duplicate entry '1327' for key 'PRIMARY'; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1327' for key 'PRIMARY' com.jivesoftware.base.database.dao.DAOException: org.springframework.dao.DuplicateKeyException: PreparedStatementCallback; SQL [INSERT INTO jiveUserProfile (userProfileID, userID, fieldID, levelID, value, primaryVal) VALUES (?, ?, ?, ?, ?, ?)]; Duplicate entry '1327' for key 'PRIMARY'; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1327' for key 'PRIMARY'

Any Ideas?  Restarting the Jive application server also didn't reset what ID it uses...

What I *think* is happening, since the userProfileID column is not auto-increment, is Jive must store this variable externally somewhere.  But where?

Ping Ryan Rutan and Markus Nagel