Data too long for column 'title' at row 1

miniol February 8, 2015

Hi,
This is a strange error that happens on some servers only.
In Omeka 2.2.2 (and also 2.1.4), trying to add an item with the long title returns the following error (in debug mode):

Zend_Db_Statement_Mysqli_Exception

Mysqli statement execute error : Data too long for column 'title' at row 1

I was investigating a bit the problem and my findings are:

  • when the error happens, the item is added to the database correctly
  • it happens only when the Title field contains big number of characters: 250 is OK, 260 gives error (no HTML formatting)

I looked into the database and i see that 'search_text' table contains columns 'title' and 'text' where the 'title' is defined as TINYTEXT. This means that the column cannot take more than 255 bytes.

Nevertheless it doesn't explain why the error occurs on selected servers. Each time the item is written to the database and the 'search_text' table is updated (however the long text is cut down to 255 characters).

Can anyone explain?

John Flatness February 9, 2015

I think the difference here is MySQL "strict" mode is enabled on the servers that get the error, but not the others.

The default MySQL behavior for the *TEXT types is to truncate long values with a warning. With strict mode on, values that are too long for the column result in an error.

miniol February 9, 2015

Thank's John. This really could be the problem.

The server that returns error has the following response for the both

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

The second server returns empty table so no modes are set.

I tried to run:
SET SESSION sql_mode = '';
but it doesn't change the session mode (the response is the same).

Unfortunately I have no admin rights to the first server (the bad one). So what more can I do to get rid of the error messages?

Is it possible to make Omeka to ignore such errors?

BTW - why Omeka don't use TEXT in the 'title' column of the search_texts table. Couldn't it be common to the element_texts table (using TEXT) that contain also item titles?

Maybe it is not the most typical use case, but when a user gives quite a long title for an item and Omeka lets him to do it without a warning, some part of the title will not be indexed. Am I correct?

Shouldn't it be specified maximum characters limit for the title field?

John Flatness February 9, 2015

Omeka can't ignore those errors: they prevented the row from being inserted, so they're actual errors and we need to fail there.

I'll probably just change the title for search to be in line with the element_texts text column size.