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

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'),
        ),
    ),
);

Reply