It may sometimes happen that collections that existed a while ago, or till yesterday, are suddenly not visible to anyone with whom the collections were shared.
There could be several ways in which a shared collection may disappear from the view of people with whom it was shared. One of the reasons in TeleScope is when the user row representing a user(who created all the now missing shared collections) in the users' MS-SQL table is deleted. This triggers a cascade delete operation on the table that stores the collections information(m_lightboxes) and the table that holds the collection contents information(m_lb_items).
The following observations confirm the cause of this problem:
- Was any user row deleted from the users table recently and before this problem started occurring?
- Do all the missing collections belong to users who were deleted?
- Are the collections not visible to all the users, and even administrators?
The answer will be Yes for all the above questions if user information and all information about their created collections are deleted from the database. The only way to fix this is to restore the rows in the affected tables.
SQL Server Management Studio (SSMS)
Steps To Fix
To fix this problem we need to restore the deleted rows(in tables user, m_lightboxes & m_lb_items), which can be achieved with a recent backup of the database. OnDemand customers should contact their Account Manager to check if backups are available for their TeleScope installation, and proceed with the rest of the restoration if backups are configured.
<supportagent>Agents should create a task of type SaaS request on PLNP JIRA to have the following steps done for OnDemand customers.</supportagent>
Please follow the steps below to restore the shared collections:
- Obtain the SQL backups of users, m_lightboxes, m_lb_items tables from as recent as possible and when the deleted user row of the user and most of their collections existed in the database.
- Copy the SQL INSERT statement for the deleted user. The user can be found by searching for either the username or first name and last name in the user backup file.
- Copy the SQL INSERT statements for the collections created by the deleted user. This can be found in the m_lightboxes backup file whose owner field is set to the username of the deleted user.
- Copy the SQL INSERT statements for the assets in these deleted collections. These can be found in the m_lb_items backup file. This step needs the most work as you need to compile the collection IDs from the above step(Step 3) of all the deleted collections belonging to the deleted user. Once you have the list, locate all the INSERT statements for all the asset references in m_lb_items whose lb_id belongs to the set of collection IDs.
- Run the SQL queries generated from the preceding step to restore the user, their collections, and collection contents.
Refresh views of the affected users to check if the collections that went missing are visible again once the restoration work is complete on the affected tables.
Please sign in to leave a comment.