Increase size of source field to hold long URLs

1000 characters seems too arbitrary considering in the wild implementations don't have this limit. I would suggest switching to text type.

The following updates returned messages

redirect module

Update #7100
Failed: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes: ALTER TABLE {redirect} CHANGE `source` `source` VARCHAR(1000) NOT NULL COMMENT 'The source path to redirect from.', ADD INDEX `source_language` (`source`, `language`); Array ( ) in db_change_field() (line 3017 of /var/www/dvg/includes/database/database.inc).

I had to lower the limit to 900 to be able to install this update on our servers ...

I would like database columns source and redirect both to be able to handle much longer urls (1000 characters would work).
Also, would like the overlay form input box you enter the source and redirect urls into to accept much longer urls (1000 characters would work).

Thanks,

Michael

We haven't had any problems with the length of redirect source field (yet) but ran into the "PDOException: SQLSTATE" error when trying to enter more than the the 255 DB character limit.

Ie, following test URL:

go/TGezh0aB7wkV8xx8TqrpKVwMclUPMtUWOKpvM1KKc1qsyjqUzlk65acnDxwos5QOtiwXsXAPYwvtyvqntqfyPrlItC1IWkA5wYno08yxrjRXyg14YSMsyFLMK5T7IDSQaXC0Yz95628fT0iaU1xw4UtleIPAISEbmfXxQOP0SByq7vfFgctvWiDW6wilBtei3HD3omP8vBUg1elK4PBW6hkTn9hly5yT5KAFL54HISNlumcmm0zTB85vlSam5A8H

inserted into the admin/config/search/redirect/add form creates error:

messagePDOException: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'source' at row 1: INSERT INTO {redirect} (hash, type, source, source_options, redirect, redirect_options, language, status_code, count, access) 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, :db_insert_placeholder_6, :db_insert_placeholder_7, :db_insert_placeholder_8, :db_insert_placeholder_9); Array ( [:db_insert_placeholder_0] => MHVGlY7ipqw3Izr0Qb5cvcJ_jQq7wnITSoZbfWsWomY [:db_insert_placeholder_1] => redirect [:db_insert_placeholder_2] => go/TGezh0aB7wkV8xx8TqrpKVwMclUPMtUWOKpvM1KKc1qsyjqUzlk65acnDxwos5QOtiwXsXAPYwvtyvqntqfyPrlItC1IWkA5wYno08yxrjRXyg14YSMsyFLMK5T7IDSQaXC0Yz95628fT0iaU1xw4UtleIPAISEbmfXxQOP0SByq7vfFgctvWiDW6wilBtei3HD3omP8vBUg1elK4PBW6hkTn9hly5yT5KAFL54HISNlumcmm0zTB85vlSam5A8H [:db_insert_placeholder_3] => a:0:{} [:db_insert_placeholder_4] => node/9719 [:db_insert_placeholder_5] => a:0:{} [:db_insert_placeholder_6] => en [:db_insert_placeholder_7] => 0 [:db_insert_placeholder_8] => 0 [:db_insert_placeholder_9] => 0 ) in drupal_write_record() (line 7246 of /var/www/drupal-staging/includes/common.inc).

Be nice to have had some more validation on this as well as increase the character length as others have requested.

MySQL doesn't care how long a VARCHAR is, it can be 30k, that's fine. The index/key can't be more than 3k though, so that might be a problem. Only 1k if you want to support 'old' MySQLs.

I'd go with @MrHaroldA's solution and make the total index/key < 1k. 900 chars for source should be enough 99.9% of the time.

Right, the 255 character limit for VARCHAR ended with MySQL 5.0.3 and D7 requires 5.0.15, so let's increase this. I used 900 characters since going over that seemed to cause problems for some folks. While we're at it, we should increase the redirect field too, since that can point to any arbitrary URL.

I also noticed that the form sets both these fields' limits at 560 chars. I have no idea why that didn't match the limit in the schema, so I changed that too.

Works like a charm!

Unfortunately, the update crashes on my production server:

> SQLSTATE42000: Syntax error or access violation: 1071 Specified keyerror was too long; max key length is 767 bytes

I'm told by my sysadmin that can be worked around by some config setting, but it doesn't work OOTB.

The only way I can see for this is to remove the index that uses the source column. The problem with doing that is that we rely on the index for looking for redirects in hook_init().

I do wonder though -- why aren't we using redirect_load_by_hash() in hook_init()?

Hmm, I see. The larger InnoDB key size of 3072 bytes was made a (default) option in MySQL 5.5, and made mandatory in 5.7.7: http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_inn...

D7 supports MySQL 5.0.15 so that won't work for us and we'll have to make the table shorter. I think removing the index and loading by hash would cause very serious performance problems for large sites, so let's definitely not do that. At least D8 requires MySQL 5.5 so we could use 900 characters there.

joachim, would you mind doing some tests and figuring out how large a value you can use for the source column? (Perhaps this is where the value of 560 bytes came from.) We should be able to leave the redirect column at 900 characters since there's no index on it. The actual length of the index would depend on the character set used, if you have time to experiment with that as well. Also, what version of MySQL are you running?

And all of this is to accomodate a single source value of 260 characters on my client's site :)

My local dev site has MySQL 5.5.38, but the production server I tried this on which crashed has XtraDB 5.6.25-73.1.

I'm afraid I'm not able to tinker with the production server to see what it'll tolerate!

> I think removing the index and loading by hash would cause very serious performance problems for large sites, so let's definitely not do that.

I'm rather confused what the loading by hash functionality is for. I assumed it actually *was* for speed, but it's not used in hook_init(), so I've probably misunderstood its purpose.

> We should be able to leave the redirect column at 900 characters since there's no index on it.

Bad news on that front: #2194099: Create database index on the redirect column.

> And all of this is to accomodate a single source value of 260 characters on my client's site :)

Ha, I know the feeling! I had a CSV of redirects to migrate in, and some had source URLs of about 700 characters!!

Is it possible to create a schema and update that set the index size to 'max', being platform dependent? Is there a way to get the current db's max index size?

joachim, I just realized I had access to a MySQL 5.1.73 environment to test this. I can create the index without any problems, plus create the index for the redirect column. If I manually try to create a UNIQUE index this fails with the same error you saw, but the BTREE index created by this update hook runs without errors by indexing just 255 characters of the text columns.

Percona XtraDB seems to be doing something different here, and I don't see a way in DatabaseSchema_mysql::addIndex() to force it to index 255 characters. Can you try to just create a test table with a varchar(900) text column, using charset utf8, and see if you can run the following?

alter table test add index my_long_index (my_long_column)

For me that works and when I run show create table test I see KEY `my_long_index` (`my_long_column`(255))

Instead of increasing the field size of varchar, we can have the LONGTEXT type to the source field.
So, in this case source url size can be saved upto 2000 chars.

What's the benefit of using LONGTEXT? The problem here as I understand it isn't the length of the VARCHAR, but the size of the field used in the index.

A few other problems:

1. +++ b/redirect.install
   @@ -104,7 +104,6 @@ function redirect_schema() {
   -        'source',
   

   You've removed 'source' from the index, so that hook_update_N() needs to make that change too.

2. +++ b/redirect.install
   @@ -117,6 +116,9 @@ function redirect_schema() {
   +  if(db_field_exists('redirect','source')) {
   +    db_query('ALTER TABLE {redirect} CHANGE  source  source LONGTEXT');
   +  }
   

   Why would the field exist when the module is installed? That shouldn't be possible.

3. +++ b/redirect.install
   @@ -432,3 +434,7 @@ function _redirect_migrate_path_redirect_variables() {
   +function redirect_update_7103(&$sandbox) {
   

   Missing a docblock.

Since we want to 'Increase size of source field to hold long URLs', we shall use the LONGTEXT.
And yes their is a problem with the index size of the source field, so we shall drop and add the index of source field, in this case data loss will not be their.

> so we shall drop and add the index of source field, in this case data loss will not be their.

Won't that mean a big drop in performance? hook_init() does a query of redirects with a condition on the source field.

> Won't that mean a big drop in performance?

https://api.drupal.org/api/drupal/modules%21system%21system.api.php/func...
yes, hook_init() runs on every page load. This will have impact on server response time performance for every page access.

but our module upgrade runs only once which re-indexes the redirect table.
So i believe after this, the performance will be the same as in the earlier state.

heykarthikwithu, i agree with joachim that using a fulltext field would be a performance killer. afaict you can't create an index combining fulltext columns with other types so looking up whether a redirect exists is going to be very slow with your change. yes, hypothetically a url could be arbitrarily long but i don't think we need to support such massive values in practice.

my proposed change seems to work fine on all supported versions of mysql, we just need to figure out what's up with Percona XtraDB here (since that's officially supported too) to accept this change.

LONGTEXT is crazy insanely long (like 2GB). VARCHAR can be up to 65k, which is waaaay longer than any URL. Exactly what joachim says in #16.

This is getting ridiculous. Let's please fix the field size to a length we know ALL databases will accept as index size. It's definitely more than 255, which probably fixes this issue for many many people.

Keep in mind that this patch must work both on MySQL, Postgres and any other DBMS supported by Drupal.
So, native sql queries are not recommended. You should work instead DB API to drop or create sql indexes.

Concerning changing type of "source", I would recommended to create a temporary column a copy data.

I still need to investigate further, but on Acquia (Server version: 5.5.24-55-log Percona Server (GPL), Release 26.0) I observed problems increasing the size of these database fields - it wasn't possible to set the source to anything larger than 255 (once the index had been added in redirect_update_7101):

mysql> ALTER TABLE redirect MODIFY source VARCHAR(256);
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
mysql> ALTER TABLE redirect MODIFY source VARCHAR(255);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Possibly related - #2815099: exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 3072 bytes' in

As discussed, using ALTER TABLE is MySQL specific and LONGTEXT is *far* too large. My patch in #8 still applies cleanly to the 7.x-1.x and 7.x-2.x branches, so let's see if the tests still pass. Plus, it updates the form API settings to allow people to actually use the longer values, without which this patch is somewhat pointless. As mentioned above, this works for me with MySQL 5.1.73. The only outstanding issue is one report of problems with Percona XtraDB from joachim, for which I'd like more information, as mentioned in #14.

OTOH, given malcomio's report that Acquia won't allow increasing these fields from 255 to 256, perhaps any attempt at a patch here is dead in the water unless it's decided we don't need to support Acquia's hosting platform and/or Percona Server's default settings.

Oops, forgot to trigger the testbot.

I also forgot to set the version to 7.x-2.x, which is where I assume the active development is happening. The relevant code doesn't seem to have changed between those branches, although if/when #2194099: Create database index on the redirect column lands this patch will need a minor change to manage the new index on the redirect column.

> As mentioned above, this works for me with MySQL 5.1.73. The only outstanding issue is one report of problems with Percona XtraDB from joachim, for which I'd like more information, as mentioned in #14.

I'm sorry, but this was so long ago that I don't even remember which project I was working on at the time!

Does the limitations on Acquia's environment still hold true?

I would like to fix this issue too, from what I have found, some people have created new modules to edit the existing DB using hook_schema_alter, and then basically just adding their field below it. Has anyone here tried that?

#24 failed to apply on 1.x-dev. On update db there was an error:

Failed: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes: ALTER TABLE {redirect} CHANGE `source` `source` VARCHAR(900) NOT NULL COMMENT 'The source path to redirect from.', ADD INDEX `status_source_language` (`status`, `source`, `language`); Array ( ) в функции db_change_field() (строка 3076 в файле /home/httpd/vhosts/estee-design.ru/subdomains/dev/httpdocs/includes/database/database.inc).

So I had to remove from #24 patch everything exept the following:

diff --git a/redirect.admin.inc b/redirect.admin.inc
index 990a22a..7eaa42f 100644
--- a/redirect.admin.inc
+++ b/redirect.admin.inc
@@ -351,7 +351,7 @@ function redirect_edit_form($form, &$form_state, $redirect = NULL) {
     '#type' => 'textfield',
     '#title' => t('From'),
     '#description' => t("Enter an internal Drupal path or path alias to redirect (e.g. %example1 or %example2). Fragment anchors (e.g. %anchor) are <strong>not</strong> allowed.", array('%example1' => 'node/123', '%example2' => 'taxonomy/term/123', '%anchor' => '#anchor')),
-    '#maxlength' => 560,
+    '#maxlength' => 900,
     '#default_value' => $redirect->rid || $redirect->source ? redirect_url($redirect->source, $redirect->source_options + array('alter' => FALSE)) : '',
     '#required' => TRUE,
     '#field_prefix' => $GLOBALS['base_url'] . '/' . (variable_get('clean_url', 0) ? '' : '?q='),
@@ -365,7 +365,7 @@ function redirect_edit_form($form, &$form_state, $redirect = NULL) {
   $form['redirect'] = array(
     '#type' => 'textfield',
     '#title' => t('To'),
-    '#maxlength' => 560,
+    '#maxlength' => 900,
     '#default_value' => $redirect->rid || $redirect->redirect ? redirect_url($redirect->redirect, $redirect->redirect_options, TRUE) : '',
     '#required' => TRUE,
     '#description' => t('Enter an internal Drupal path, path alias, or complete external URL (like http://example.com/) to redirect to. Use %front to redirect to the front page.', array('%front' => '<front>')),

Then I patched the module with modified patch and run the following in mysql cli:

ALTER TABLE redirect DROP INDEX status_source_language;
ALTER TABLE redirect MODIFY source VARCHAR(900);
ALTER TABLE redirect MODIFY redirect VARCHAR(900);
ALTER TABLE redirect ADD INDEX status_source_language (status, source, language);

My Mysql server version is:

mysql> STATUS;
Server version:		5.6.32-78.0-log Percona Server (GPL), Release 78.0, Revision 8a8e016

causes major issue:
https://www.drupal.org/node/2815099

Hello,

I meet also the PDO Exception mentioned by @vbard.

Same repro steps but with a MySQL 5.6.38.

Note that without the patch, I do not have the issue.

EDIT:

I managed to redirect working with column sizes of 900 by following this steps:

1. Removing the definition of the "status_source_language" index in the code base;
2. Running it via a mysql cli like this:ALTER TABLE redirect ADD INDEX status_source_language (status, source, language);

I tried to translate this into code as follow:

/**
 * Implements hook_schema().
 *
 * The schema definition is split in 2 steps: The table definition that happens
 * normally here, and the indexes definition that will happen in the install
 * hook.
 *
 * This implementation avoid the failure of the "redirect" table creation due to a
 * PDOException caused the DB index limitations of some databases.
 *
 * @see redirect_install()
 */
function redirect_schema() {
  $schema['redirect'] = array(
    'description' => 'Stores information on redirects.',
    'fields' => array(
      'rid'  => array(
        'type' => 'serial',
        'not null' => TRUE,
        'description' => 'Primary Key: Unique redirect ID.',
      ),
      'hash' => array(
        'type' => 'varchar',
        'length' => 64,
        'not null' => TRUE,
        'description' => 'A unique hash based on source, source_options, and language.',
      ),
      'type' => array(
        'type' => 'varchar',
        'length' => 64,
        'not null' => TRUE,
        'default' => '',
        'description' => "The redirect type; if value is 'redirect' it is a normal redirect handled by the module.",
      ),
      'uid' => array(
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
        'description' => 'The {users}.uid of the user who created the redirect.',
      ),
      'source' => array(
        'type' => 'varchar',
        'length' => 900,
        'not null' => TRUE,
        'description' => 'The source path to redirect from.',
      ),
      'source_options' => array(
        'type' => 'text',
        'not null' => TRUE,
        'serialize' => TRUE,
        'description' => 'A serialized array of source options.',
      ),
      'redirect' => array(
        'type' => 'varchar',
        'length' => 900,
        'not null' => TRUE,
        'description' => 'The destination path to redirect to.',
      ),
      'redirect_options' => array(
        'type' => 'text',
        'not null' => TRUE,
        'serialize' => TRUE,
        'description' => 'A serialized array of redirect options.',
      ),
      'language' => array(
        'description' => 'The language this redirect is for; if blank, the alias will be used for unknown languages.',
        'type' => 'varchar',
        'length' => 12,
        'not null' => TRUE,
        'default' => 'und',
      ),
      'status_code' => array(
        'type' => 'int',
        'size' => 'small',
        'not null' => TRUE,
        'description' => 'The HTTP status code to use for the redirect.',
      ),
      'count' => array(
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
        'description' => 'The number of times the redirect has been used.',
      ),
      'access' => array(
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
        'description' => 'The timestamp of when the redirect was last accessed.',
      ),
      'status' => array(
        'type' => 'int',
        'size' => 'small',
        'not null' => TRUE,
        'default' => 1,
        'description' => 'Boolean indicating whether the redirect is enabled (visible to non-administrators).',
      ),
    ),
    'primary key' => array('rid'),
    'unique keys' => array(
      'hash' => array('hash'),
    ),
  );

  return $schema;
}

/**
 * Implements hook_install().
 */
function redirect_install() {
  // Finalize the schema definition by defining the indexes.
  // "db_add_index" cannot be called in schema hook.
  // This step is not done during the schema definition in order to avoid any
  // PDOException that could happen at this time.
  // PDOException are due to the DB index limitations of some databases.
  db_add_index('redirect', 'expires', array(
    'type',
    'access',
  ));
  db_add_index('redirect', 'status_source_language', array(
    'status',
    'source',
    'language',
  ));

  // If the path redirect table exists, then set the schema to run the
  // migration update function.
  if (db_table_exists('path_redirect')) {
    drupal_set_installed_schema_version('redirect', 6999);
  }
}

But That does not work, I still have the PDOException: exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes'

I hope that can helps.

I managed to install "redirect" with the the column size set 900.

For this, I created a new patch with the help I found in this issue: #2549049: Data too long for 'mailto' and its patch 2549049-10.patch.

I set to 255 the number of characters that the index must take into account for the field "source":

...

    'indexes' => array(
      'expires' => array('type', 'access'),
      'status_source_language' => array(
        'status',
        // Limit the number of characters used by the index.
        // That allows avoiding the risk to have a PDOException
        // caused the DB index limitations of some databases.
        // see https://www.drupal.org/project/redirect/issues/2057615.
        array('source', 255),
        'language',
      ),
    ),
..

.

The part I do not get is that according to the MySQL documentation, if the number of characters is not set, MySQL sets it to 255 by default.
So, the patch #24 should work and the number of character should be 255 but it does not while executing the SQL cli "ALTER TABLE redirect ADD INDEX status_source_language (status, source, language);" does not generate an error.
See screenshot " 2057615-index-db-screenshot.png".

Anyway, I tested the patch with MySQL 5.6 and SQLite and I did not meet any issue with it.

Feedbacks are welcomed.

I updated the patch to include in the field help information regarding the number of characters that can be used

Patch #33 worked as expected for me

@enriquelacoma,

I aligned the "redirect" field description on the "source" one.
Otherwise, everything is ok.

@every one
I think that we address with this last patch all points reported in the issue.

Could we consider it as RTBC?

Looks good.

I think we also have to add this code in update_7101.

/**
 * Add status field.
 */
function redirect_update_7101() {
  db_add_field('redirect', 'status', array(
    'type' => 'int',
    'size' => 'small',
    'not null' => TRUE,
    'default' => 1,
    'description' => 'Boolean indicating whether the redirect is enabled (visible to non-administrators).',
  ));
  db_drop_index('redirect', 'source_language');
  db_add_index('redirect', 'status_source_language', array(
    'status',
    // Limit the number of characters used by the index.
    // That allows avoiding the risk to have a PDOException
    // caused the DB index limitations of some databases.
    // see https://www.drupal.org/project/redirect/issues/2057615.
    array('source', 255),
    'language',
  ));
}

Its hard to follow all the comments and different patches on this issue and whether the support for older Mysql versions was actually added, but I can confirm that on latest 7.x-dev version, installing this on a site with Mysql 5.6.34 fails horribly.

The module is partially installed, but the redirect table is not built, I still get the error message mentioned above:

$ drush en redirect
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes  

I had to leave the module disabled for now. Drush even had problems disabling the module the first time, but the second attempt worked.

$ drush pm-disable redirect -y
The following extensions will be disabled: redirect
There was a problem disabling redirect.  

$ drush pm-disable redirect -y
The following extensions will be disabled: redirect
redirect was disabled successfully.  

$ drush pm-uninstall redirect -y
The following modules will be uninstalled: redirect
redirect was successfully uninstalled.

I've created the patch in similar issue
It should solve last issue.

Uh... Hate to dig up an old issue but...

Shouldn't there be a hook_update_n for this?

I just updated to from rc3 to rc4 and did the database updates (7103, 7104) and noticed that my field lengths were still 255 bytes. Personally I wouldn't much care how long they are, but the UI now claims a 900 char limit. I suspect this disparity may cause issues for some people.

With ulethjay #46

I wasn't sure whether or not I should create a new ticket for this but I think it's mostly related to this one.

After upgrading from 7.x-1.0-rc3 to 7.x-1.0-rc4 I get the following notices from the Schema module:

column source - difference on: length
    declared: array('description' => 'TODO: please describe this field!', 'type' => 'varchar', 'length' => 900, 'not null' => TRUE)
    actual: array('description' => 'The source path to redirect from.', 'type' => 'varchar', 'length' => '255', 'not null' => TRUE)

column source_options - difference on: not null
    declared: array('description' => 'TODO: please describe this field!', 'type' => 'blob', 'not null' => TRUE)
    actual: array('description' => 'TODO: please describe this field!', 'type' => 'blob', 'not null' => FALSE)

column redirect - difference on: length
    declared: array('description' => 'TODO: please describe this field!', 'type' => 'varchar', 'length' => 900, 'not null' => TRUE)
    actual: array('description' => 'The destination path to redirect to.', 'type' => 'varchar', 'length' => '255', 'not null' => TRUE)

column redirect_options - difference on: not null
    declared: array('description' => 'TODO: please describe this field!', 'type' => 'blob', 'not null' => TRUE)
    actual: array('description' => 'TODO: please describe this field!', 'type' => 'blob', 'not null' => FALSE)

indexes status_source_language:
    declared: array('status', array('source', 255), 'language')
    actual: array('status', 'source', 'language')

indexes redirect:
    declared: array(array('redirect', 255))
    actual: array('redirect')

Is there any way to easily fix these?