307804 - SQL Identifiers must be less than 30 characters long for Oracle

Status: RESOLVED WONTFIX

(Bugzilla :: Database, enhancement)

Description

[Lance Larsh]

User-Agent:       Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.3) Gecko/20041119
Build Identifier: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.3) Gecko/20041119

Oracle SQL limits the length of SQL identifiers to 30 charaters.  There are
currently four objects with names longer than 30 characters.  Namely:

   category_group_map_category_id_idx
   group_control_map_product_id_idx
   profile_setting_value_unique_idx
   profiles_activity_profiles_when_idx


Reproducible: Always

Steps to Reproduce:
(NOTE:  Testing obviously requires a database driver for the DB of interest, and
as of this moment the driver for Oracle isn't done yet since this bug blocks it.
 The steps below show how to test the code against any database for which a
driver is available.)
1. Run checksetup.pl on a clean installation.
2. Edit localconfig to set the db_* parameters to point to a fresh db with no
schema created yet.
3. Run checksetup.pl again (this time to create the schema).

Actual Results:  
Oracle returns the following error:
  DBD::Oracle::db do failed: ORA-00972: identifier is too long (DBD ERROR:
  error possibly near <*> indicator at char 20 in 'CREATE UNIQUE INDEX
    <*>category_group_map_category_id_idx ON category_group_map (category_id,
    group_id)')

Expected Results:  
All objects should be created without errors.

Comment 1

[Max Kanat-Alexander]

Arrrgh. This is a tricky one...

I wonder if we should just have the driver truncate the index name to 30
characters whenever it does an index operation. That would be simple enough, I
suppose. Although it would be hard to keep up with when we add new index
operation functions.

I suppose 30 characters is a reasonable limit.

If you want to rename the indexes, abbreviate the table names. So
"category_group_map_" would become something like "cgm_" -- though I'm concerned
slightly about the future-proofness of such short abbreviations.

Let me know if you have any clever ideas.

Comment 2

[Lance Larsh]

(In reply to comment #1)
> I wonder if we should just have the driver truncate the index name to 30
> characters whenever it does an index operation. That would be simple enough, I
> suppose. Although it would be hard to keep up with when we add new index
> operation functions.

Although having the driver truncate the name is the simplest code change, I
don't think we'd want the burden of requiring generic index operations to pass
the index name to the driver for truncation.

> If you want to rename the indexes, abbreviate the table names. So
> "category_group_map_" would become something like "cgm_" -- though I'm
> concerned slightly about the future-proofness of such short abbreviations.

I think shortening the name for all DBs is the best approach.  We can use less
extreme abbreviations, like:

   cat_group_map_cat_id_idx
   group_control_map_prod_id_idx
   prof_setting_value_unique_idx
   prof_activity_prof_when_idx

Up until now it looks like all the index names are of the form
"${table}_${column}_idx".  I assume there's no code that requires that to be
true (i.e., so we don't need to change the table names to match).

Comment 3

[Max Kanat-Alexander]

(In reply to comment #2)
> I think shortening the name for all DBs is the best approach.  We can use less
> extreme abbreviations, like:

  That sounds good.

> I assume there's no code that requires that to be
> true (i.e., so we don't need to change the table names to match).

  There *was* code that required it to be true, but you don't have to worry
about it. :-)

Comment 4

[Frédéric Buclin]

Max, xiaoou says that his patch from bug 310718 worked around this problem in _get_create_index_ddl() thanks to
  $index_name = "idx_" . substr(md5_hex($index_name),0,20);

This means we can now close this bug, isn't it? Maybe as FIXED and depending on 310718 with [fixed by blocker].

Comment 5

[Max Kanat-Alexander]

Hrm, I'd rather say that instead, we don't need to fix this because we worked around it in the Oracle driver.