Error updating table: vcals: Query Failed - vcals 'content' too big for field type TEXT #4743

New issue

Open

opened 2026-02-20 16:23:09 -05:00 by deekerman · 2 comments

deekerman commented

2026-02-20 16:23:09 -05:00

Owner

Originally created by @vaudoin on GitHub (Mar 3, 2022).

Issue

For some users who have a lot of meetings on there calendar, a database error is raised each time they want to add a meeting.
vcals content seems too big for column type TEXT.

Notice that : vCals periodicity is configured to 3 months sync.

Error updating table: vcals: Query Failed: (see query below): MySQL error 1406: Data too long for column 'content' at row 1

For some users, indeed, the content is greater than 65000 caracters ('content' field TEXT limit).

Expected Behavior

There shouldn't be any error while saving vcals content.

Actual Behavior

Each time a user (who have a lot of meetings) tries to add a meeting he got a "Error while saving".
The meeting is recorded anyway. On the logs, we can see the database error while saving vcals.

suitecrm.log
Extract of SQL that raised the error :

UPDATE vcals SET `date_modified`='2022-03-01 07:01:58',`user_id`='03042ad3-a0b0-42f7-8153-ae7a822d3315',`type`='vfb',`source`='sugar',`content`='BEGIN:VCALENDAR\nVERSION:2.0\nPRODID:-//SugarCRM//SugarCRM Calendar//EN\nBEGIN:VFREEBUSY\nORGANIZER;CN=SomeOne:VFREEBUSY\nDTSTART:2022-02-27 23:00:00\nDTEND:2022-05-27 22:00:00\nFREEBUSY:20220419T100000Z/20220419T103000Z\nX-FREEBUSY-ID:ef25b6a1-50f0-049e-2091-61af6a9203e8\nX-FREEBUSY-TYPE:Meeting\nFREEBUSY:20220310T160000Z/20220310T163000Z\nX-FREEBUSY-ID:eb03c11d-93e9-627d-13b8-600d3d202a6c\nX-FREEBUSY-TYPE:Meeting\nFREEBUSY:20220301T170000Z/20220301T180000Z\nX-FREEBUSY-ID:661906bd-c049-f29c-49af-600d36d45ff8\nX-FREEBUSY-TYPE:Meeting\nFREEBUSY:20220408T063000Z/20220408T103000Z\nX-FREEBUSY-ID:db43f84b-0d94-1c10-8f55-620a1bd283c0\nX-FREEBUSY-TYPE:Meeting\nFREEBUSY:20220401T120000Z/20220401T133000Z\nX-FREEBUSY-ID:c7ac65fc-1a2a-accb-dfce-61fa8c789a00\nX-FREEBUSY-TYPE:Meeting\nFREEBUSY:20220420T070000Z/20220420T074500Z\nX-FREEBUSY-ID:a5510d41-b497-f367-d420-600d1c7833fc\nX-FREEBUSY-TYPE:Meeting\nFREEBUSY:20220308T074500Z/20220308T080000Z\nX-FREEBUSY-ID:a68a3bf7-1971-5457-2d13-61e7c3290314\nX-FREEBUSY-TYPE:Meeting\nFREEBUSY:20220517T103000Z/20220517T113000Z\nX-FREEBUSY-ID:59b70d21-1694-d648-3beb-600d354ed9a9\nX-FREEBUSY-TYPE:Meeting\nFREE
[...]
FREEBUSY:20220407T070000Z/20220407T073000Z\nX-FREEBUSY-ID:1e12cbbc-9faf-f6ca-2a87-62172208dd11\nX-FREEBUSY-TYPE:Call\nFREEBUSY:20220421T070000Z/20220421T073000Z\nX-FREEBUSY-ID:30ebf87a-f13a-eb02-17e3-621722eaf2c6\nX-FREEBUSY-TYPE:Call\nFREEBUSY:20220505T070000Z/20220505T073000Z\nX-FREEBUSY-ID:469ae4f1-d233-462e-d14d-6217222d46e5\nX-FREEBUSY-TYPE:Call\nFREEBUSY:20220519T070000Z/20220519T073000Z\nX-FREEBUSY-ID:51571672-aac4-e37f-25a1-6217224f5a9a\nX-FREEBUSY-TYPE:Call\nFREEBUSY:20220303T140000Z/20220303T143000Z\nX-FREEBUSY-ID:8ee777ed-c111-073f-7948-621cc31491c1\nX-FREEBUSY-TYPE:Call\nFREEBUSY:20220310T080000Z/20220310T083000Z\nX-FREEBUSY-ID:e7503fc0-46cf-5eaf-b506-621722555e27\nX-FREEBUSY-TYPE:Call\nDTSTAMP:2022-03-01 07:01:58\nEND:VFREEBUSY\nEND:VCALENDAR\n'
WHERE  vcals.id = 'e16ab702-9ff0-da72-db8b-505970910eba' AND deleted=0

Possible Fix

As a quick fix (non upgrade safe), I moved content field from TEXT to LONGTEXT.
modules/vCals/vardefs.php

'content' =>
  array(
    'name' => 'content',
      'type' => 'longtext', // Quickfix long to longtext
  ),

Steps to Reproduce

No that easy to reproduce, as you need a lot of data on meetings.
For instance, for a specific user, he has 810 meetings lines of meetings during the same period retrieved in vcals. (684 are recurring ones).

SELECT * FROM meetings_users mu LEFT JOIN meetings m ON m.id = mu.meeting_id WHERE `user_id`='03042ad3-a0b0-42f7-8153-ae7a822d3315' AND m.date_start > ('2022-02-27') AND m.date_end < ('2022-05-27')

vcals, content field is 98151 caracters:

SELECT length(content) FROM vcals WHERE `user_id`='03042ad3-a0b0-42f7-8153-ae7a822d3315'

Context

The question could also be : how come we have so many content for vcals for only 3 months ? Even if there is a lot of meetings, we have for some users with vcals content that reaches arround 100000 caracters... Could this be related to recurring meeting (we use them a lot)?

Your Environment

SuiteCRM Version used: 7.10.9 but we are about to migrate to 7.12.4 (devlopment environment) and this still a problem.
Environment name and version (e.g. MySQL, PHP 7): MariaDB Server 10.3.31, PHP 7.3
Operating System and version (e.g Ubuntu 16.04): Debian 10

Originally created by @vaudoin on GitHub (Mar 3, 2022). #### Issue For some users who have a lot of meetings on there calendar, a database error is raised each time they want to add a meeting. vcals content seems too big for column type TEXT. Notice that : vCals periodicity is configured to 3 months sync. Error updating table: vcals: Query Failed: (see query below) : MySQL error 1406: Data too long for column 'content' at row 1 For some users, indeed, the content is greater than 65000 caracters ('content' field TEXT limit). #### Expected Behavior  There shouldn't be any error while saving vcals content. #### Actual Behavior Each time a user (who have a lot of meetings) tries to add a meeting he got a "Error while saving". The meeting is recorded anyway. On the logs, we can see the database error while saving vcals. * suitecrm.log Extract of SQL that raised the error : ``` UPDATE vcals SET `date_modified`='2022-03-01 07:01:58',`user_id`='03042ad3-a0b0-42f7-8153-ae7a822d3315',`type`='vfb',`source`='sugar',`content`='BEGIN:VCALENDAR\nVERSION:2.0\nPRODID:-//SugarCRM//SugarCRM Calendar//EN\nBEGIN:VFREEBUSY\nORGANIZER;CN=SomeOne:VFREEBUSY\nDTSTART:2022-02-27 23:00:00\nDTEND:2022-05-27 22:00:00\nFREEBUSY:20220419T100000Z/20220419T103000Z\nX-FREEBUSY-ID:ef25b6a1-50f0-049e-2091-61af6a9203e8\nX-FREEBUSY-TYPE:Meeting\nFREEBUSY:20220310T160000Z/20220310T163000Z\nX-FREEBUSY-ID:eb03c11d-93e9-627d-13b8-600d3d202a6c\nX-FREEBUSY-TYPE:Meeting\nFREEBUSY:20220301T170000Z/20220301T180000Z\nX-FREEBUSY-ID:661906bd-c049-f29c-49af-600d36d45ff8\nX-FREEBUSY-TYPE:Meeting\nFREEBUSY:20220408T063000Z/20220408T103000Z\nX-FREEBUSY-ID:db43f84b-0d94-1c10-8f55-620a1bd283c0\nX-FREEBUSY-TYPE:Meeting\nFREEBUSY:20220401T120000Z/20220401T133000Z\nX-FREEBUSY-ID:c7ac65fc-1a2a-accb-dfce-61fa8c789a00\nX-FREEBUSY-TYPE:Meeting\nFREEBUSY:20220420T070000Z/20220420T074500Z\nX-FREEBUSY-ID:a5510d41-b497-f367-d420-600d1c7833fc\nX-FREEBUSY-TYPE:Meeting\nFREEBUSY:20220308T074500Z/20220308T080000Z\nX-FREEBUSY-ID:a68a3bf7-1971-5457-2d13-61e7c3290314\nX-FREEBUSY-TYPE:Meeting\nFREEBUSY:20220517T103000Z/20220517T113000Z\nX-FREEBUSY-ID:59b70d21-1694-d648-3beb-600d354ed9a9\nX-FREEBUSY-TYPE:Meeting\nFREE [...] FREEBUSY:20220407T070000Z/20220407T073000Z\nX-FREEBUSY-ID:1e12cbbc-9faf-f6ca-2a87-62172208dd11\nX-FREEBUSY-TYPE:Call\nFREEBUSY:20220421T070000Z/20220421T073000Z\nX-FREEBUSY-ID:30ebf87a-f13a-eb02-17e3-621722eaf2c6\nX-FREEBUSY-TYPE:Call\nFREEBUSY:20220505T070000Z/20220505T073000Z\nX-FREEBUSY-ID:469ae4f1-d233-462e-d14d-6217222d46e5\nX-FREEBUSY-TYPE:Call\nFREEBUSY:20220519T070000Z/20220519T073000Z\nX-FREEBUSY-ID:51571672-aac4-e37f-25a1-6217224f5a9a\nX-FREEBUSY-TYPE:Call\nFREEBUSY:20220303T140000Z/20220303T143000Z\nX-FREEBUSY-ID:8ee777ed-c111-073f-7948-621cc31491c1\nX-FREEBUSY-TYPE:Call\nFREEBUSY:20220310T080000Z/20220310T083000Z\nX-FREEBUSY-ID:e7503fc0-46cf-5eaf-b506-621722555e27\nX-FREEBUSY-TYPE:Call\nDTSTAMP:2022-03-01 07:01:58\nEND:VFREEBUSY\nEND:VCALENDAR\n' WHERE vcals.id = 'e16ab702-9ff0-da72-db8b-505970910eba' AND deleted=0 ``` #### Possible Fix As a quick fix (non upgrade safe), I moved content field from TEXT to LONGTEXT. modules/vCals/vardefs.php ``` 'content' => array( 'name' => 'content', 'type' => 'longtext', // Quickfix long to longtext ), ``` #### Steps to Reproduce No that easy to reproduce, as you need a lot of data on meetings. For instance, for a specific user, he has 810 meetings lines of meetings during the same period retrieved in vcals. (684 are recurring ones). ``` SELECT * FROM meetings_users mu LEFT JOIN meetings m ON m.id = mu.meeting_id WHERE `user_id`='03042ad3-a0b0-42f7-8153-ae7a822d3315' AND m.date_start > ('2022-02-27') AND m.date_end < ('2022-05-27') ``` vcals, content field is 98151 caracters: ``` SELECT length(content) FROM vcals WHERE `user_id`='03042ad3-a0b0-42f7-8153-ae7a822d3315' ``` #### Context The question could also be : how come we have so many content for vcals for only 3 months ? Even if there is a lot of meetings, we have for some users with vcals content that reaches arround 100000 caracters... Could this be related to recurring meeting (we use them a lot)? #### Your Environment * SuiteCRM Version used: 7.10.9 but we are about to migrate to 7.12.4 (devlopment environment) and this still a problem. * Environment name and version (e.g. MySQL, PHP 7): MariaDB Server 10.3.31, PHP 7.3 * Operating System and version (e.g Ubuntu 16.04): Debian 10

deekerman added the

Type: Bug

Area: Clean Up: Performance

labels

2026-02-20 16:23:09 -05:00

deekerman commented

2026-02-20 16:23:10 -05:00

Author

Owner

@samus-aran commented on GitHub (Jul 6, 2022):

Interesting problem. Need to investigate more on the structure of vcal and confirm if the structure is causing this issue.

@samus-aran commented on GitHub (Jul 6, 2022): Interesting problem. Need to investigate more on the structure of vcal and confirm if the structure is causing this issue.

deekerman commented

2026-02-20 16:23:10 -05:00

Author

Owner

@pgorod commented on GitHub (Jul 6, 2022):

Since these strings should be highly repeated, and thus highly compressable, maybe we could try a simple PHP gzdeflate before saving the field, and then a gzinflate when reading from the DB, this could make some difference.

Although probably the key point is whether the recurring meetings are getting split into individual instances, and whether that makes sense for some reason.

@pgorod commented on GitHub (Jul 6, 2022): Since these strings should be highly repeated, and thus highly compressable, maybe we could try a simple PHP gzdeflate before saving the field, and then a gzinflate when reading from the DB, this could make some difference. Although probably the key point is whether the recurring meetings are getting split into individual instances, and whether that makes sense for some reason.