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

[Sam Ottenhoff]

Hi Wolfgang,

Can you share your experiences with the 2.9 conversion scripts?  Did
the script in fact remove the duplicates?  Did you run the conversion
script before or after you started up Sakai 2.9's Tomcat?

--Sam

On Tue, Oct 30, 2012 at 2:17 PM, Sam Ottenhoff <ottenhoff at longsight.com> wrote:
> Hi Wolfgang,
>
> It appears that the script below did in fact remove the duplicates and then
> failed when trying to run a final update.  Can you confirm that duplicates
> were removed and the script created the unique key as expected?
>
> Did you run the conversion script before or after starting up Sakai 2.9?  If
> you ran the conversion script after starting the 2.9 Tomcat instance with
> auto.ddl enabled, I can understand the error on the update.  It should be
> safe to ignore.  If you ran the conversion script before running Tomcat, I
> would be curious how you had an item in your database with a locale set to
> 'default'.
>
> --Sam
>
> On Tue, Oct 30, 2012 at 11:19 AM, Wolfgang Rohregger
> <Wolfgang.Rohregger at mci.edu> wrote:
>>
>> 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
>>
>>
>> _______________________________________________
>> sakai-dev mailing list
>> sakai-dev at collab.sakaiproject.org
>> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>>
>> TO UNSUBSCRIBE: send email to
>> sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of
>> "unsubscribe"
>
>