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 2 years 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

Fullscreen
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?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,
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
<?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

0 Marko Vucak over 2 years 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

Fullscreen
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?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,
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
<?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'), ), ), );

Children

No Data