Trigger not working with longtext field

Question

I have 2 MySQL table name uploads and uploads_log.

uploads table has a field name json_values (datatype: longtext)
uploads_log table has 2 fields old_value, new_value (both datatype: longtext)

On After UPDATE of uploads table I have written a trigger which just put the whole content of uploads.json_values into uploads_log table's old_value, new_value.

trigger is

BEGIN
    IF (NEW.json_values != OLD.json_values) THEN
        INSERT INTO uploads_log (`file_id`, `user_id`, `field_name`, `old_value`, `new_value`, `ip`, `created_at`)
        VALUES (OLD.`file_id`,
                OLD.`user_id`,
                'json_values',
                OLD.json_values,
                NEW.json_values,
                NEW.user_ip,
                NOW());
    END IF;
END

My issue is: When I'm editing small string in uploads.json_values my trigger is working fine, but when Im editting some realy long string like 378369 characters long. I'm getting the following error.

SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

I try to debug the issue I removed the trigger and EDITED uploads.json_values with long string it workes fine, and I manually INSERTED that long string into uploads_log.old_value then also it works fine, So the issue is with the trigger.

Is trigger has some limitataion of length?

Both the table uses Storage Engine: InnoDB and MySQL Version is 5.6.21.

uploads table Structure

CREATE TABLE `uploads` (
 `file_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` int(11) unsigned NOT NULL,
 `json_values` longtext COLLATE utf8_unicode_ci NOT NULL,
 `read_values` longtext COLLATE utf8_unicode_ci,
 `user_ip` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 PRIMARY KEY (`file_id`),
 KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=34444 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

uploads_log table Structure

CREATE TABLE `uploads_log` (
 `action_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `file_id` int(11) unsigned DEFAULT NULL,
 `user_id` int(11) unsigned DEFAULT NULL,
 `field_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `old_value` longtext COLLATE utf8_unicode_ci,
 `new_value` longtext COLLATE utf8_unicode_ci,
 `ip` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
 `created_at` datetime DEFAULT NULL,
 PRIMARY KEY (`action_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I found this question and this one, but it is not related to UPDATE trigger.

Any help/suggestion will be very much appreciated.

Thanks.

Check out [this answer](http://stackoverflow.com/a/25578580/1541403) — Vatev, Nov 29 '16 at 12:18
@e4c5: I have updated my question with table structure. and Vatev I'm also reading this answer but having an hard time to digest it. so googling it. — Raunak Gupta, Nov 29 '16 at 12:30
@e4c5: Trigger is written in `uploads` table, and it is getting fired after table update. — Raunak Gupta, Nov 29 '16 at 12:50
and you did try changing the row format to compressed as suggested in the error message right? — e4c5, Nov 29 '16 at 13:03
phpMyAdmin **does not** has any option to change `ROW_FORMAT` to `compressed`, So I cannot able to do it. — Raunak Gupta, Nov 29 '16 at 13:10

score 1 · Accepted Answer · edited Apr 13 '17 at 12:42

1

I also had some similar issue but not with trigger this question helped me out.

Change innodb_log_file_size value located in C:\xampp\mysql\bin\my.ini (if you are using XAMPP) to something higher than 5M.

or as pointed out by @Vatev you can set innodb_log_file_size = 128M

You can use this MySQL command to get the innodb_log_file_size value, it will give you the result in Byte.

edited Apr 13 '17 at 12:42

Community

1
1

answered Nov 29 '16 at 12:53

Rana Ghosh

4,514
5
23
40

thanks, it did work for me on local/dev server, but is there any other way to achieve this, because I don't know I'll have any option to change MySQL configuration on shared hosting. – Raunak Gupta Nov 29 '16 at 13:13

Trigger not working with longtext field

1 Answers1