Saving block translation with large amount of text causes PDOException: Data too long for column 'translation'

Question

I have a Drupal 7 project where I am using a Panel to patch together some Drupal blocks. These blocks have to be translated and I just added some data to the current French translation and apparently there is a limit to how much translated content you can have in a block because after I add the extra french content to the block and try to save I get an error that says: "The website encountered an unexpected error. Please try again later."

and going to the reports log I see:

PDOException: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'translation' at row 1: UPDATE {locales_target} SET translation=:db_update_placeholder_0 WHERE ( (lid = :db_condition_placeholder_0) AND (language = :db_condition_placeholder_1) ); Array ( [:db_update_placeholder_0] => 

[LOTS OF HTML MARKUP/TRANSLATION DATA HERE THAT I LEFT OUT FOR BREVITY]

[:db_condition_placeholder_0] => 16474 [:db_condition_placeholder_1] => fr ) in i18n_string_textgroup_default->save_translation() (line 747 of /home/master/public_html/sites/all/modules/i18n/i18n_string/i18n_string.inc).

The block contains HTML mixed with text and its pretty large, 72080 characters. I need it to be large because its a lot of text and I have HTML markup in it that works with some custom javascript I created.

Any idea how to get over this limit for the maximum amount you can translate in a block?

Here is the table that is being referenced and it looks like the field "language" is already a blob:

Answer 1

You could change that database column and let other modules know about those changes with hook_schema_alter(), but this could cause problems with existing modules. If you are going through this, be sure to test it on the development site, before doing the same changes on the live site. In particular, be sure the code you are using doesn't conflict with another module that alters the same column; in that eventuality, you need to accordingly change your code.

To make that field larger, it's sufficient to run the following code, for example when installing a custom module.

function mymodule_install() {
  if (db_field_exists('locales_target', 'translation')) {
    $field = array(
      'type' => 'text',
      'mysql_type' => 'blob',
      'size' => 'big',
      'not null' => TRUE,
      'description' => 'Translation string value in this language.',
    );

    db_change_field('locales_target', 'translation', $field);
  }
}

The following hook implementation would make the other modules aware of the column changes.

function mymodule_schema_alter(&$schema) {
  if (isset($schema['locales_target']['translation'])) {
    $schema['locales_target']['translation'] = array(
      'type' => 'text',
      'mysql_type' => 'blob',
      'size' => 'big',
      'not null' => TRUE,
      'description' => 'Translation string value in this language.',
    );
  }
}

It's necessary to use a module since, without implementing hook_schema_alter(), other modules won't know the database column has been changed.

Answer 2

I fixed this issue with the code below. This code is similar to what the user @apderno suggested but his function call was missing 1 signature and didn't work or I would have rated his suggestion as the best answer.

To fix this I created a new custom module with a .module and .install file in the module. For my .module page:

 function make_translations_longer_schema_alter(&$schema) {

 if (isset($schema['locales_target']['translation'])) {
    
    $schema['locales_target']['translation'] = array(
      'type' => 'blob',
      'size' => 'big',
      'not null' => TRUE,
      'description' => 'Translation string value in this language.',
    );   
  }
}

For my .install page:

function make_translations_longer_install() {

  db_change_field('locales_target', 'translation', 'translation', array(
    'description' => "Translation string value in this language.",
    'type' => 'blob',
    'not null' => FALSE,
    'size' => 'big',
  ));
}

I was then able to create the longer French translation. Thanks to @apderno!!!