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

[Wolfgang Rohregger]

Hi,

sorry for the late answer.

The database has been at 2.9.x-b07 version after Tomcat startup with auto.ddl enabled.

Cheers,

Wolfgang

-----Ursprüngliche Nachricht-----
Von: Sam Ottenhoff [mailto:ottenhoff at longsight.com] 
Gesendet: Donnerstag, 01. November 2012 23:10
An: Wolfgang Rohregger
Cc: sakai-dev at collab.sakaiproject.org
Betreff: Re: [Building Sakai] SAK-21739 conversion fails at removing duplicates

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"
>
>