team_sets_modules table usage?

I believe, but I may stand corrected, that every time you put multiple teams on a record it will create a team set that includes all those teams if one does not already exist.

So you may have a lot of different combinations of allowable teams on your records.

In the Repair under Admin you will see Repair Team Sets, among other things, this will clean up any Team Sets that are no longer in use.

https://support.sugarcrm.com/Documentation/Sugar_Versions/12.0/Ent/Administration_Guide/System/Repair/#Repair_Teams

FrancescaS

Hi, thx for your answer.

I run Repair Teams and "Clean up unused combinations of teams" but that only deletes records that have team_set_id related to teams that are not in use.

For me it's weird that there are records without team_set_id. What is purpose of them as can't see how they are connected to anything?

Hi Marko Vucak,

It is not natural for there to be records in the team_sets_modules table with a NULL team_set_id. It seems likely that there is an errant customization causing this behavior. Before doing anything with the records in this table, I recommend some investigation first.

Check a few of the modules in question to see if they have records with NULL team_set_id. For example, you have multiple entries in this table for the opportunities module, so run the following query to see a sample of what records may have generated the NULL entries:

SELECT * FROM opportunities WHERE team_set_id IS NULL AND deleted = '0' ORDER BY date_entered DESC LIMIT 0,10;

This should give you an idea of how pervasive the problem is. Based on your description of the table size growing rapidly, I'm assuming this is occurring with recently created and/or modified records. Under normal conditions, all records in these tables should have a valid team_id and team_set_id. The most common scenario is that both values are identical when there is only a single team on the record. 

Assuming that query returns results, you may want to check any customizations that are attempting to manipulate team assignments on a record. Let me know what you find.

Chris

Hi Chris

Thank you for answer.

I run that query on opp table and it returned 1 record, must be some old, not used, test record as it doesn't have name.

Same query on products table returns 0 records.

When i check how many we have in team_sets_module it returns 185762 related to opportunities.

SELECT count(id) from team_sets_modules where deleted=0 AND module_table_name = "opportunities" and team_set_id IS NULL;

Hi Marko,

If you're working in an isolated development environment, can you try creating and modifying opportunity records to see if either of those actions add records to the team_sets_modules table? Are you aware of any customizations that make changes to team visibility?

Chris

Hi Chris

Thx. There are no changes to team visibility. But project has a lot of customisations and it's using Sugar 9. I was trying to optimise it before upgrade to 12.

I changed one field on Opportunity and it added 4 records, then changed again and added 4 more.

I deleted all logic hooks in Opportunities module and it happens the same. I don't even use forecast module but there are a lot of PD's created.

I deleted all that has team_set_id NULL and it works fine so i will do that to clear data, probably update instance and see how it behaves in sugar 12.

I managed to track where this fails and is in class TeamSetLink (modules/Teams/TeamSetLink.php)

In this line acl_team_set_id is "" 
TeamSetManager::saveTeamSetModule($this->focus->acl_team_set_id, $this->focus->table_name);


It creates bean with empty acl_team_set_id and when it saves record in class TeamSetModule

public function save($check_notify = false)
    {
        $query = "SELECT id
            FROM $this->table_name
            WHERE team_set_id = ?
                AND module_table_name = ?";
        $row = $this->db->getConnection()->fetchColumn(
            $query,
            [$this->team_set_id, $this->module_table_name]
        );
        if (!$row){
            // insert the record by means of plain SQL in order to not trigger all other logic in SugarBean::save(),
            // since this method is manually called from SugarBean::save()
            $this->db->insertParams(
                $this->table_name,
                $this->getFieldDefinitions(),
                [
                    'id' => create_guid(),
                    'team_set_id' => $this->team_set_id,
                    'module_table_name' => $this->module_table_name,
                    'deleted' => 0,
                ]
            );
        }
    }

This query returns false always as there is no record with empty team set, in db when inserting it has value NULL.
So on every save it just add records in.

I see we can disable this saving at all by setting this config value to 0 is this recommended?
public static function saveTeamSetModule($teamSetId, $tableName){
		//if this entry is set in the config file, then store the set
		//and modules in the team_set_modules table
        if (!isset($GLOBALS['sugar_config']['enable_team_module_save'])
            || !empty($GLOBALS['sugar_config']['enable_team_module_save'])) {
			$teamSetModule = BeanFactory::newBean('TeamSetModules');
			$teamSetModule->team_set_id = $teamSetId;
			$teamSetModule->module_table_name = $tableName;
			$teamSetModule->save();
		}
	}

By further investigation i found out that table looks like this, not sure if it was ever customised or this is Sugar 9 default table:

CREATE TABLE `team_sets_modules` (
`id` char(36) NOT NULL,
`team_set_id` char(36) DEFAULT NULL,
`module_table_name` varchar(128) DEFAULT NULL,
`deleted` tinyint(1) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_team_sets_modules` (`team_set_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

If i alter table to look like in Sugar10 (12 also):

CREATE TABLE `team_sets_modules` (
`id` char(36) NOT NULL,
`team_set_id` char(36) NOT NULL,
`module_table_name` varchar(128) NOT NULL,
`deleted` tinyint(1) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_team_sets_modules` (`team_set_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Then everything works fine. I don't have original Sugar 9 database anywhere so not sure if someone has ever modified that table (not sure why will someone do that) or is just a bug.

So my solution will be to change table and then issue is fixed.

EDIT: Actually I finished with modifying db through custom vardef:

sugar/custom/Extension/modules/Teams/Ext/Vardefs/custom_team_sets_modules.php

<?php
$dictionary['TeamSetModule'] = array(
    'table' => 'team_sets_modules',
    'fields' => array(
        'id' => array(
            'name' => 'id',
            'type' => 'id',
            'required' => true,
        ),
        'team_set_id' => array(
            'name' => 'team_set_id',
            'type' => 'id',
            'isnull' => false,
            'required' => true,
        ),
        'module_table_name' => array(
            'name' => 'module_table_name',
            'vname' => 'LBL_NAME',
            'type' => 'name',
            'dbType' => 'varchar',
            'len' => 128,
            'isnull' => false,
            'required' => true,
        ),
        'deleted' => array(
            'name' => 'deleted',
            'type' => 'bool',
            'len' => 1,
            'default' => 0,
        ),
    ),
    'acls' => array(
        'SugarACLAdminOnly' => array(
            'adminFor' => 'Users',
            'allowUserRead' => true,
        ),
    ),
    'indices' => array(
        array(
            'name' => 'team_sets_modulespk',
            'type' => 'primary',
            'fields' => array('id'),
        ),
        array(
            'name' => 'idx_team_sets_modules',
            'type' => 'index',
            'fields' => array('team_set_id'),
        ),
    ),
);