team_sets_modules table usage?

We have team_sets_modules table size over 5GB and is growing rapidly. How can we reduce size of that table? Are all those records necessary?

By documentation it says:

This table is used to manage team sets and keep track of which modules have records that are or were associated to a particular team set.

When i run queries on that table:

/* How many records with team_set_id are? i get about 4000 */

SELECT * from team_sets_modules where deleted=0 and team_set_id <>"";

/* How many records without team_set_id are? i get all other records, millions of them */

SELECT * from team_sets_modules where deleted=0 and team_set_id IS NULL;

I see that majority of records in that table only have id and module name, team_set_id is empty. Can we remove records without id?

Parents

0 Marko Vucak over 1 year ago

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();
		}
	}

Reply

0 Marko Vucak over 1 year ago

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();
		}
	}

Children

No Data