[Building Sakai] SAK-21739 conversion fails at removing duplicates

[Wolfgang Rohregger]

Hi,

we just tried to apply SAK-21739 conversion script statements to our local test instance (MySQL). The script comments state that it "will remove duplicates" and fails in doing so:

-- SAK-21739 Enforce uniqueness on template key + locale
-- The following lines will remove duplicates from your database by filtering first on date and then on max id (tie break).
CREATE TABLE EMAIL_TEMPLATE_ITEM_TEMP (ID INTEGER);
INSERT INTO EMAIL_TEMPLATE_ITEM_TEMP SELECT ID from EMAIL_TEMPLATE_ITEM
  where LAST_MODIFIED not in (select MAX(LAST_MODIFIED) from EMAIL_TEMPLATE_ITEM GROUP BY template_key, template_locale);
DELETE FROM EMAIL_TEMPLATE_ITEM WHERE ID IN (SELECT ID FROM EMAIL_TEMPLATE_ITEM_TEMP);
DELETE FROM EMAIL_TEMPLATE_ITEM_TEMP;
INSERT INTO EMAIL_TEMPLATE_ITEM_TEMP SELECT MAX(ID) FROM EMAIL_TEMPLATE_ITEM GROUP BY UPPER(TEMPLATE_KEY), UPPER(TEMPLATE_LOCALE);
DELETE FROM EMAIL_TEMPLATE_ITEM WHERE ID NOT IN (SELECT ID FROM EMAIL_TEMPLATE_ITEM_TEMP);
DROP TABLE EMAIL_TEMPLATE_ITEM_TEMP;

alter table EMAIL_TEMPLATE_ITEM add unique key EMAIL_TEMPLATE_ITEM_KEY_LOCALE_KEY (TEMPLATE_KEY,TEMPLATE_LOCALE);
-- end of SAK-21739

-- SAK-22223 don't use null as a template key
update EMAIL_TEMPLATE_ITEM set TEMPLATE_LOCALE = 'default' where TEMPLATE_LOCALE is null or TEMPLATE_LOCALE = '';

The error output is:

update EMAIL_TEMPLATE_ITEM set TEMPLATE_LOCALE = 'default' where TEMPLATE_LOCALE is null or TEMPLATE_LOCALE = ''          Error Code: 1062. Duplicate entry 'polls.notifyDeletedOption-default' for key 'EMAIL_TEMPLATE_ITEM_KEY_LOCALE_KEY'


>From reading the SAK-21739 JIRA issue comments I understand that in certain conditions no automatic making of uniqueness is possible. If that assumption is true, shouldn't the comments in the conversion script file be appended with something like "this operation can fail under certain circumstances. In this case you have to delete the wrong duplicates for yourself."?

Cheers,

Wolfgang
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20121030/884af949/attachment.html