[jira] [Commented] (ROL-1953) mysql roller_permission insert statements not defined correctly in 400-to-500-migration.vm and 400-to-500-migration.sql file

["Glen Mazza (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/ROL-1953?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13541100#comment-13541100 ] 

Glen Mazza commented on ROL-1953:
---------------------------------

Hi Harsh, thanks for letting us know about this, what I did was add in an explanation in the .vm file for those in your shoes who might have the same problem:

+-- Note: if getting error that roller_permission.id column is not wide enough 
+-- due to concatenation of w.id and u.username below, may be able to rely on
+-- w.id alone *if* no single blog is shared by multiple users. 

I think that's the best that can be done within reasonable effort.  The solution you've proposed, AFAICT, wouldn't work if two people have write access to the same blog.  For example, if Tom grants Bob access to Tom's Blog, presently, the migration script would create two new keys "tomsblogtom" and "tomsblogbob".  The proposed solution would create two keys with the same primary key ID "tomsblog".  If I tried to truncate the length of the concatenation so you don't get a column width error, that's a stronger solution, although it can also fail as you sometimes need the full length of the name and blog to fully get uniqueness.  Problem is, our .vm --> each database.sql AFAICT presently doesn't have a generic "substring" capability that I could use.

                
> mysql roller_permission insert statements not defined correctly in 400-to-500-migration.vm and 400-to-500-migration.sql file
> ----------------------------------------------------------------------------------------------------------------------------
>
>                 Key: ROL-1953
>                 URL: https://issues.apache.org/jira/browse/ROL-1953
>             Project: Roller
>          Issue Type: Bug
>          Components: Installation
>    Affects Versions: 5.0
>         Environment: any
>            Reporter: Harsh Gupta
>            Assignee: Roller Unassigned
>
> Here select statement is a combination of w.id and w.username whereas the column where it has to insert is roller_permission's id column that doesn't have enough length to fit in.
> concat function is incorrectly used here according to me and if i just use select w.id , upgrade works for me
> insert into roller_permission (id,username,actions,objectid,objecttype,pending,datecreated) 
>    select   concat(w.id, u.username)  , u.username, 'edit_draft', w.handle, 'Weblog', 0, current_timestamp
>       from rolleruser as u, website as w, roller_user_permissions as p
>       where p.user_id = u.id and p.website_id = w.id and permission_mask = 1;
> insert into roller_permission (id,username,actions,objectid,objecttype,pending,datecreated) 
>    select   concat(w.id, u.username)  , u.username, 'author', w.handle, 'Weblog', 0, current_timestamp
>       from rolleruser as u, website as w, roller_user_permissions as p
>       where p.user_id = u.id and p.website_id = w.id and permission_mask = 2;
> insert into roller_permission (id,username,actions,objectid,objecttype,pending,datecreated) 
>    select   concat(w.id, u.username)  , u.username, 'admin', w.handle, 'Weblog', 0, current_timestamp
>       from rolleruser as u, website as w, roller_user_permissions as p
>       where p.user_id = u.id and p.website_id = w.id and permission_mask = 3;

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira