LicensePoolManagement module install issues

4
I have installed the new LicensePoolManager module on my application in the Modeler. I had to make a couple of changes to my entities to accommodate this for my existing application: My original people objects (where --> indicates a specialization of) were: StaffMember --> Contact --> System.User To install the new module I have had to change this to: StaffMember --> Contact --> LicensePoolManager.Account --> System.User This converted OK in the Modeler (apart from having to recreate all my logins), but when I try to put this change on a production server I get the following error in the Service Console when it tries to update the database structure: M2EE: An error occurred while executing action 'Execute DDL commands'. com.mendix.m2ee.api.AdminException: Executing DDL commands failed. at com.mendix.core.MxRuntime.c(SourceFile:499) Caused by: ac: Error (SQL State: S0002, Error Code: 8155) on executing: INSERT INTO [licensepoolmanager$account] ([id], [submetaobjectname]) SELECT * FROM (SELECT [id], [submetaobjectname] FROM [message$contact]) AS U1 UNION SELECT * FROM (SELECT [id], 'StaffTeams.StaffMember' FROM [staffteams$staffmember]) AS U1; I think SQL error 8155 indicates a column is not identified uniquely. How do I overcome this issue? Also, is there a way to make this type of specialization change while retaining the existing data? Additional info: If I copy the queries that the Console is trying to run and execute them directly against the database I get the following message: Msg 8155, Level 16, State 2, Line 1 No column was specified for column 2 of 'U1'. The second column of the StaffMember object when viewed in the database is a deleted_firstname column. Could this be the cause of the issue? Additional info: No that was not the issue. I removed all the 'Deleted_...' columns from the database and received the same error during database restructuring/synching Update: After manually running the amended SQL script suggested by Jonathan all seemed to be well, but closer inspection reveals a few issues still when compared to a new project: 1) Accounts overview form - shows no data in the 'Blocked', 'Web Service User' and 'Is anonymous' columns. 2) Active Sessions - there is no data shown in the upgraded project, but this works in a new project. Why? Response to Sjoerd 17 Feb: Thanks for the suggestion Sjoerd. It does look like it could be permissions, but the permissions seem to be the same between my upgraded project and a new project... plus you can't change the permissions on the System.User object anyway. I don't understand how these permissions work. Looking at System.User, a user only has Read permissions to Name and Roles, and Read-Write to Password and Language. They have no rights to Is anonymous, Blocked etc. The LicensePoolManager.Account entity inherits from User, but in the new project, you can see the data in these 'no permission' columns in the Account_Overview form. How? Where did the rights to see this data come from? In my upgraded project, I cannot see data in these 'no permissions' columns, but in the new test project I can. I don't understand. But the most important missing data is the list of Active Sessions... Update: Well, it looks like the LastOnline date is not getting stored in the database. This field is empty for all StaffMember and Contact records
asked
5 answers
2

I just uploaded a new version of the LicensePoolManager to the AppStore which solves the problem with ActiveSessions and the use of specializations of Account.

answered
6

When the tables [message$contact] and [staffteams$staffmember] do not contain required data, you can remove these tables and restart (and synchronize) the project again.

Otherwise, you can safely remove a little part of the SQL queries and execute them directly against the database. Replace the following part:

INSERT INTO [licensepoolmanager$account] ([id], 
[submetaobjectname])
SELECT * FROM (SELECT [id], 
[submetaobjectname]
 FROM [message$contact]) AS U1
UNION
SELECT * FROM (SELECT [id], 
'StaffTeams.StaffMember'
 FROM [staffteams$staffmember]) AS U1;

by:

INSERT INTO [licensepoolmanager$account] ([id], 
[submetaobjectname])
SELECT * FROM (SELECT [id], 
[submetaobjectname]
 FROM [message$contact]) AS U1
;

You see that the second part of the query has to be removed. Now SQL Server accepts the SQL script.

answered
4

Regarding your issues with the active sessions window, if it does work for you in another (new) project, it sounds to me like there might be a security issue preventing you from seeing the Account objects. Did you update the access rules for the Contact and StaffMember specializations after installing the LPM?

Similar for the missing information in the Account Overview portion, I suspect. I just checked my simple project containing nothing but the LPM where those attributes do get shown, so I suspect there must be some sort of security issue preventing you from seeing them right now.

In response to David's security update on 17 Feb System.User is indeed a bit of a weird case of security rules, since you can't explicitly define access rules for its attributes like you can for 'normal' entities.

One thing you could check for is the rights which your different user roles have to manage users of other ranks. I just did some experimenting around in my own version, and most of the attributes in the Account Overview became empty when I removed rights to manage user roles. So maybe for that issue check your settings for the different user roles to see if they can manage the roles of the users who have 'missing' attributes.

Regarding the Active Sessions window, that window shows Account objects, so even if there's any issues with System.User security, that should only result in the username column being empty, whereas Fullname and the LicensePool should still be visible.

As a result I can see two issues which might be causing the window to stay empty:

  • Security on the specialized objects you are using (Contact and StaffMember) not being setup correctly to allow for reading the Account attributes. (The absence of the username would be caused by the same System.User issues as above) This might result in a seemingly empty grid filled with objects of which you are not allowed to see any of the attributes which are setup in the grid. (I believe you should be able to see a return of objects from this query in firebug then, despite the list being empty.) Could check access rules to these after using 'Update Security' if you did not do so yet.

  • Somehow the LastOnline attribute is not set correctly by the java action (When you open Active Sessions a JavaAction is called which updates the LastOnline attribute of all online accounts to the current date time, after which the grid is constrained to users for which the LastOnline attribute is within 15 seconds of the current date time. What you could check:

    • If this 15 second window is too short for your project. You could try this by extending the 15 second window in the xpath constraint on the active sessions grid.
    • See if the Java action is properly setting the LastOnline attribute. Several options for this, such as (temporarily) adding it to the Account Overview grid to see if it's being set, checking it in the database, setting up logging, etc. Still seems unlikely to me though that the java action is causing issues when it works correctly for you in a new project.
answered
1

I did some googling, it appears that SQL error 8155 indicates the lack of a supplied alias for a column. (See also http://technet.microsoft.com/en-us/library/aa226404%28SQL.80%29.aspx)

I'm not an expert on databases, but from this I guess it might be worthwhile to double check if the database query is completely valid.

answered
1

To investigate further, I have taken a new simple project that seems to be working OK - you can see the active sessions. I added a new entity Contact and made it a specialization of licencepoolmanager/account. New records created through the Contact form do not show up under Active Sessions when they are logged in. I will file a bug report and add this project to it

answered