String data, right truncated: 1406 Data too long for column 'uri'

Based on human review of https://api.drupal.org/api/drupal/includes%21database%21database.inc/fun... the proposed snippet looks like correct code.

I realized there are indexes on the URI field, two of them. So I asked chatgpt about whether I need to consider that too.

My prompt

There are indexes on the uri field, do I need to change anything about them when I change the field?

Chatgpt response

When you modify the length of a field that has indexes in Drupal 7, you must temporarily drop the indexes, modify the field, and then recreate the indexes. This ensures that the database schema updates correctly without running into issues related to the indexes.

Here's how you can update the uri field to 255 characters while handling the indexes properly:

1. Identify the indexes on the uri field. If you have a custom index, you'll need to note its name.
2. Update the .install file with the proper steps to drop the index, change the field, and recreate the index.

Here's an updated version of the function to handle indexes:

/**
 * Update the length of the 'uri' field in the 'l10n_server_project' table to 255 characters.
 */
function l10n_server_update_7001() {
  // Drop indexes on the 'uri' field before modifying the field.
  $indexes = db_index_exists('l10n_server_project', 'uri');
  if ($indexes) {
    db_query("ALTER TABLE {l10n_server_project} DROP INDEX uri_index");
  }

  // Define the new schema for the 'uri' field.
  $schema = array(
    'fields' => array(
      'uri' => array(
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
    ),
  );

  // Update the 'uri' field to match the new schema.
  db_change_field('l10n_server_project', 'uri', 'uri', $schema['fields']['uri']);

  // Recreate the indexes on the 'uri' field.
  db_query("ALTER TABLE {l10n_server_project} ADD INDEX uri_index (uri)");

  // Indicate the update was successful.
  return t("Changed the length of the 'uri' field to 255 characters.");
}

Explanation:

• Dropping the index:
  • Before modifying the uri field, drop the index using db_query("ALTER TABLE {l10n_server_project} DROP INDEX uri_index");. Replace uri_index with the actual name of your index if it's different.
• Modifying the field:
  • The db_change_field function updates the field as described previously.
• Recreating the index:
  • After modifying the field, recreate the index using db_query("ALTER TABLE {l10n_server_project} ADD INDEX uri_index (uri)");. Again, replace uri_index with the correct index name.

Notes:

1. Ensure you replace uri_index with the actual name of the index on the uri field. If there are multiple indexes, you need to handle each one accordingly.
2. Backup your database before performing these updates to prevent data loss.

Run the database updates via /update.php or drush updatedb after adding this function to apply the changes.

The https://api.drupal.org/api/drupal/includes%21database%21database.inc/fun... docs confirm that, but also provide API functions to adding the key rather than altering directly as chatgpt said and also there is a built-in argument on db_change_field:

To maintain database portability, you have to explicitly recreate all indices and primary keys that are using the changed field.

That means that you have to drop all affected keys and indexes with db_drop_{primary_key,unique_key,index}() before calling db_change_field(). To recreate the keys and indices, pass the key definitions as the optional $keys_new argument directly to db_change_field().

[...]
You could use db_add_{primary_key,unique_key,index}() in all cases unless you are converting a field to be type serial. You can use the $keys_new argument in all cases.

Proposed an update function based on the above info. I don't have a way to test this, so this is as far as I could bring it.

Moving to major since it blocks parsing any translatable string on localize.drupal.org.

I assume the uri was capped at 50 to help limit index sizes. Not sure how allowing longer will change that, so maybe we should up to 100 or something more conservative, but I don't have enough deep database experience to tell how the performance would be affected either way.

Re indexes - I didn't catch the comments here before simplifying the update function in the MR. For this one, I’m not too concerned about database portability. It works for localize.drupal.org, and if anyone happens to be running this stack in D7 on Postgres and needs the extra key handling, followups are welcome.

I think this is ready to deploy now.

This is deployed and new releases are being caught up. I fully expect this needs forward-porting.

https://git.drupalcode.org/project/l10n_server/-/blob/3.0.x/l10n_server/... is also limited to 50 yeah, so that needs work as well. I don't know if we need an update function that depends on how the D10 site is run.

Created a branch in the issue fork for 3.x but it does not allow to update the fork on the gitlab UI, there are conflicts. I would love if someone could take it over, @TeeBeeCoder maybe?

I have a look at it today Gabor!

@goba & @drumm, I made the requested change without hook_update_N as it is a installation.

Thanks everyone!

Automatically closed - issue fixed for 2 weeks with no activity.