On Thu, Jan 21, 2010 at 10:52 AM, Marvin Addison <[email protected]>wrote:
> > We are running on mysql. I am getting frequent (every few minutes) > constraint violations. Here is the exception: > > Exception in thread "pool-1-thread-70998" > org.springframework.dao.DataIntegrityViolationException: > PreparedStatementCallback; SQL [Insert into COM_STATISTICS(STAT_SERVER_IP, > STAT_DATE, APPLIC_CD, STAT_PRECISION, STAT_COUNT, STAT_NAME) VALUES(?, ?, ?, > ?, 1, ?)]; Duplicate entry > 'unknown-2010-01-15-CAS-MINUTE-GRANT_SERVICE_TICKET-1' for key 1; nested > exception is > com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: > Duplicate entry 'unknown-2010-01-15-CAS-MINUTE-GRANT_SERVICE_TICKET-1' for > key 1 > > My hunch is that the MySQL DATE data type doesn't have enough > granularity to store HH:MM:SS needed to make each entry unique. I > would recommend updating the table schema to the TIMESTAMP datatype, > or MySQL's version of same, and see if it goes away. > > M > > For my birthday Scott attached the mysql data definitions to the wiki. I tried it out and it worked just fine. http://www.ja-sig.org/wiki/pages/viewpageattachments.action?pageId=22940014 CREATE TABLE `COM_AUDIT_TRAIL` ( `AUD_USER` varchar(100) NOT NULL , `AUD_CLIENT_IP` VARCHAR(15) NOT NULL , `AUD_SERVER_IP` VARCHAR(15) NOT NULL , `AUD_RESOURCE` varchar(100) NOT NULL , `AUD_ACTION` varchar(100) NOT NULL , `APPLIC_CD` varchar(5) NOT NULL , `AUD_DATE` TIMESTAMP NOT NULL ); ALTER TABLE `COM_AUDIT_TRAIL` ADD CONSTRAINT `COM_AUDIT_TRAIL_PK` PRIMARY KEY ( `AUD_USER`, `AUD_CLIENT_IP`, `AUD_SERVER_IP`, `AUD_RESOURCE`, `AUD_ACTION`, `APPLIC_CD`, `AUD_DATE` ) ; CREATE TABLE `COM_STATISTICS` ( `STAT_SERVER_IP` varchar(15) NOT NULL , `STAT_DATE` datetime NOT NULL , - Hide quoted text - `APPLIC_CD` varchar(5) NOT NULL , `STAT_PRECISION` varchar(6) NOT NULL , `STAT_COUNT` numeric NOT NULL , `STAT_NAME` varchar(100) ); ALTER TABLE `COM_STATISTICS` ADD CONSTRAINT `COM_STATISTICS_PK` PRIMARY KEY ( `STAT_SERVER_IP`, `STAT_DATE`, `APPLIC_CD`, `STAT_PRECISION`, `STAT_NAME` ) ; CREATE INDEX `COM_AUDIT_TRAIL_DATE_I` ON `COM_AUDIT_TRAIL` (`AUD_DATE`); CREATE INDEX `COM_AUDIT_TRAIL_CLIENT_DATE_ I` ON `COM_AUDIT_TRAIL` (`AUD_CLIENT_IP`, `AUD_DATE`); CREATE INDEX `COM_AUDIT_TRAIL_USER_DATE_I` ON `COM_AUDIT_TRAIL` (`AUD_USER`, `AUD_DATE`); CREATE INDEX `COM_AUDIT_TRAIL_ACTION_DATE_I` ON `COM_AUDIT_TRAIL` (`AUD_ACTION`, `AUD_DATE`); CREATE INDEX `COM_STATISTICS_DATE_I` ON `COM_STATISTICS` (`STAT_DATE`); CREATE INDEX `COM_STATISTICS_NAME_DATE_I` ON `COM_STATISTICS` (`STAT_NAME`, `STAT_DATE`); -- You are currently subscribed to [email protected] as: [email protected] To unsubscribe, change settings or access archives, see http://www.ja-sig.org/wiki/display/JSG/cas-user
