DB: 'output' column of jobs table isn't big enough

106 views

Skip to first unread message

bra...@branflakes.net

unread,

Jul 15, 2019, 5:11:04 PM7/15/19

to AtoM Users

Hi, AtoM community.

Earlier today, we ran into a problem on our AtoM 2.5.0 instance where the AtoM job worker would die while processing a job, leaving this error logging behind:

Jul 15 15:56:05 libatomdev02 php[3584]: 2019-07-15 15:56:05 > Job finished.
Jul 15 15:56:05 libatomdev02 php[3584]:
Jul 15 15:56:05 libatomdev02 php[3584]: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'output' at row 1
Jul 15 15:56:05 libatomdev02 php[3584]:
Jul 15 15:56:06 libatomdev02 php[3584]: 22001

It appears to be a problem similar to that reported in a thread earlier this year (https://groups.google.com/d/topic/ica-atom-users/UnGGmWC8YTo/discussion), relating in that instance to the culture column of some AtoM objects. Essentially, our job (an arUpdateEsIoDocumentsJob which was updating 1900+ archival descriptions) was producing more output than the output column of the jobs table could hold.

mysql> DESCRIBE job;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| id            | int(11)      | NO   | PRI | NULL    |       |
| name          | varchar(255) | YES  |     | NULL    |       |
| download_path | text         | YES  |     | NULL    |       |
| status_id     | int(11)      | YES  | MUL | NULL    |       |
| completed_at  | datetime     | YES  |     | NULL    |       |
| user_id       | int(11)      | YES  | MUL | NULL    |       |
| object_id     | int(11)      | YES  | MUL | NULL    |       |
| output        | text         | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

The text column type is limited to 64K in length [1]. I was able to resolve the problem in our local instance by changing the column type to MEDIUMTEXT, which has a size limit of 16M, using this one-liner.

mysql> ALTER TABLE job MODIFY output MEDIUMTEXT;

I don't have access to Redmine to create a bug report for this. If it would be helpful, I can create an SQL migration for this (very similar to this one), and issue a PR for it in Git. It might also be problematic in ways I don't see yet, or so trivial, it's just easier for someone at Artefactual to apply themselves.

Let me know how I can best help.

Brandon

José Raddaoui

unread,

Jul 23, 2019, 2:15:15 PM7/23/19

to AtoM Users

Hi Brandon,

Thank you very much for reporting this issue and its possible solution! We recently found the same one with long PDF contents, so I've added a note in that Redmine ticket:

https://projects.artefactual.com/issues/13095

We'll include a fix in the 2.5.2 release. In the meantime, changing the column type as you did shouldn't be a problem when running the next upgrade.