Problem with Bamboo notifications

[Peter J. Kootsookos]

I am installing a new version of Bamboo and, after setting up a couple of plans, accidentally deleted the "notifications" table in the MySQL database (don't ask).  I didn't have a back up.

I used the create script here to recreate a blank version of the table.  This seemed to make notifications work again for the pre-existing plans, but now when I add a new plan I get:

org.springframework.dao.DataIntegrityViolationException: could not execute batch; SQL [insert into NOTIFICATIONS (RECIPIENT_TYPE, RECIPIENT, NOTIFICATION_SET, CONDITION_KEY, CONDITION_DATA, NOTIFICATION_RULE_ID) values (?, ?, ?, ?, ?, ?)]; constraint [RECIPIENT_TYPE_UNIQUE]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute batch

Followed by:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'com.atlassian.bamboo.plugins.bamboo-slack:recipient.slack' for key 'RECIPIENT_TYPE_UNIQUE'

I just wonder whether the recipient type really needs to be unique?  

Any pointers appreciated!

[Marcin Gardias · Accepted solution]

That CREATE table script is wrong, it creates 4 unique indices instead of one but over multiple columns. Drop them and run:

ALTER TABLE `bamboo`.`NOTIFICATIONS` 
ADD UNIQUE INDEX `notifications_unique` (`RECIPIENT_TYPE` ASC, `RECIPIENT` ASC, `NOTIFICATION_SET` ASC, `CONDITION_DATA` ASC, `CONDITION_KEY` ASC);

[Peter J. Kootsookos]

Worked like a charm! Thank-you!