Error: Data too long for column 'route' at row 1

[pabloarias]

Steps to reproduce the issue

Indexing for Smart Search with CLI with command:

php -d memory_limit=512M joomla.php finder:index -vvv

I obtain an error: Data too long for column 'route' at row 1

Expected result

All indexed correctly without errors.

Actual result

If alias is big, near to 400 characters: I obtain the error.

It is because the route field, in finder_links table, has a 400 chars limit.

[rdeutz]

@Hackwar why we have the alias made to 400 chars?

[brianteeman]

The alias is generated automatically from the title BUT if you have set unicode alias to no then the alias can be longer than the title

[Hackwar]

This was unified at one point and for some reason it was decided on 400 chars. I changed the last few columns over to 400 chars when I stumbled upon this. But there is no real reason for this. This is also a general issue. However, the alias is not meant to be a short story, so 400 chars is pretty excessive, even if we use unicode...

[schultz-it-solutions]

I ran into this issue "Error: Data too long for column 'alias' at row 1" from a slightly different angle. I have a "custom field" (textarea) which I like to be indexed as well. Indexing alone seems to work, but when I activate the option (in the custom field's configuration) to "index and add to taxonomy", then I seem to run into the mentioned error: "Error: Data too long for column 'alias' at row 1"

The "value" of that textfield (it is connected to the articles) can indeed be longer than 400 chars. However, custom fields do not have an alias, and creating the alias from the field title for indexing does not make a whole lot of sense (since the title is the same for all instances of that field).

So my guess is, the alias for the search taxonomy is rendered from the "value" of the custom field - and that can (at least for the field type "textarea") easily exceed 400 chars. If that is the case, then maybe a different approach for the alias of custom fields is needed...

also: some additional information in the error message would be helpful in identifying which entry is actually causing the error.

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/43545.}

[Hackwar]

Why do you set this to "index and add to taxonomy" when it is over 400 chars long? This would result in a select list of gigantic entries with all the text of the field. I agree in a way that we should catch the errors when indexing. My solution would be to truncate the data properly when adding the taxonomy to the database. Would you be willing to create a PR for that?

[schultz-it-solutions]

I seem to be unable to create a PR for this, I am getting the following error message: Pull request creation failed. Validation failed: must be a collaborator

However I do have a fix prepared for this issue...

[Quy]

@schultz-it-solutions If the changes are minimal, please post code changes here and I can do the pull request. Thanks.

[schultz-it-solutions]

yeah, it is actually only a few lines of code:

in [JOOMLAROOT]/administrator/components/com_finder/indexer/Helper.php in method "addCustomFields"

            // We want to add this field as a taxonomy; but only to a maximum of 40 words, less than 255 characters
            if (
                ($searchindex == self::CUSTOMFIELDS_ADD_TO_TAXONOMY || $searchindex == self::CUSTOMFIELDS_ADD_TO_BOTH)
                && $field->value
            ) {
                $fieldValue = $field->value;                
                if (strlen($fieldValue) > 255) {                
                    $fieldValue = implode(' ',array_slice(array_filter(explode(' ', $fieldValue)), 0, 40));
                }
                $fieldTitle =   $field->title;
                if (strlen($fieldTitle) > 255) {                
                    $fieldTitle = implode(' ',array_slice(array_filter(explode(' ', $fieldTitle)), 0, 40));
                }
                $item->addTaxonomy($fieldTitle, $fieldValue, $field->state, $field->access, $field->language);
            }