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

Created on 10 July 2024, 9 days ago
Updated 11 July 2024, 7 days ago

Problem/Motivation

Localize.drupal.org has been not syncing releases since May 20 because the private_file_download_statistics_and_export_the_data project showed up on drupal.org which is longer than the allowed 50 chars in the URI field. This was the error:

PDOException: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'uri' at row 1: INSERT INTO {l10n_server_project} (uri, title, last_parsed, home_link, connector_module, status) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5); Array ( [:db_insert_placeholder_0] => private_file_download_statistics_and_export_the_data [:db_insert_placeholder_1] => Private file download track [:db_insert_placeholder_2] => 1720621321 [:db_insert_placeholder_3] => http://drupal.org/project/private_file_download_statistics_and_export_the_data [:db_insert_placeholder_4] => l10n_drupal_rest_restapi [:db_insert_placeholder_5] => 1 ) in l10n_drupal_rest_refresh_project_list() (line 70 of /var/www/localize.drupal.org/htdocs/sites/all/modules/l10n_server/connectors/l10n_drupal_rest/l10n_drupal_rest.rest.inc).

Steps to reproduce

Proposed resolution

The 50 chars is an arbitrary limit I think and we can adjust it. We need to update it to 255 chars. I asked chatgpt to write me an update function to cut down on looking this up in the manual. I rarely do this in Drupal 7 :D Copying the whole prompt and response for posterity.

My prompt

Write a database update function for the Drupal 7 l10n_server module to change the varchar field named uri in the l10n_server_project table to 255 characters long.

Chatgpt response

To update the uri field in the l10n_server_project table to a length of 255 characters in Drupal 7, you will need to write an update function in the module's .install file. In Drupal 7, the hook_update_N function is used for database schema updates.

Here's the function to update the uri field to 255 characters long:

Open the l10n_server.install file in the l10n_server module directory. Add the following update function:

/**
 * Update the length of the 'uri' field in the 'l10n_server_project' table to 255 characters.
 */
function l10n_server_update_7001() {
  // Define the new schema for the 'l10n_server_project' table.
  $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']);

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

Explanation

  • l10n_server_update_7001(): This function follows the naming convention for Drupal update functions, where 7001 indicates that this is the first update for the module version 7.x.
  • $schema: Defines the new schema for the uri field, setting its type to varchar and its length to 255.
  • db_change_field(): This function changes the field in the database. The parameters are:
  • 'l10n_server_project': The table name.
  • 'uri': The existing field name.
  • 'uri': The new field name (in this case, it's the same).
  • $schema['fields']['uri']: The new field definition.

After adding this function, you need to run the database updates to apply the changes. This can be done through the Update Manager at /update.php or using Drush with the command drush updatedb.

Make sure to backup your database before running any updates.

Remaining tasks

User interface changes

API changes

Data model changes

🐛 Bug report
Status

Fixed

Version

3.0

Component

Code

Created by

🇭🇺Hungary Gábor Hojtsy Hungary

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Merge Requests

Comments & Activities

  • Issue created by @Gábor Hojtsy
  • 🇭🇺Hungary Gábor Hojtsy Hungary

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

  • 🇭🇺Hungary Gábor Hojtsy Hungary

    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.

  • 🇭🇺Hungary Gábor Hojtsy Hungary

    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.

  • Merge request !60Proposed db update function → (Merged) created by Gábor Hojtsy
  • Status changed to Needs review 8 days ago
  • 🇭🇺Hungary Gábor Hojtsy Hungary

    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.

  • First commit to issue fork.
  • Status changed to RTBC 8 days ago
  • 🇺🇸United States drumm NY, US

    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.

  • Pipeline finished with Skipped
    8 days ago
    #221166
  • Status changed to Active 8 days ago
  • 🇺🇸United States drumm NY, US

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

  • 🇭🇺Hungary Gábor Hojtsy Hungary

    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.

  • 🇭🇺Hungary Gábor Hojtsy Hungary

    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.

  • Status changed to Needs review 7 days ago
    • TeeBeeCoder committed 9f98054e on 3.0.x
      Issue #3460571 by TeeBeeCoder: Increase max URI length to 255 characters...
  • Status changed to Fixed 7 days ago
  • 🇫🇷France FMB Perpinyà, Catalonia, EU
  • 🇫🇷France FMB Perpinyà, Catalonia, EU

    Thanks everyone!

Production build 0.69.0 2024