Inserting users directly into the database?
butch Dec 17, 2015 12:58 PMIssue 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