Fix the new summary index on MySQL.

Review Request #6513 — Created Oct. 27, 2014 and submitted

Information

Review Board
release-2.0.x
c1bbe6a...

Reviewers

MySQL has a quirk (many say a bug) where the max index length is based
on the actual number of allowed bytes, rather than characters, in a
VARCHAR. On InnoDB, this max length is 767 bytes. Each Unicode character
in MySQL takes 3 bytes, though. If you're running InnoDB with utf-8 and
create an index on a column with a max length over 255 characters (or
765 bytes, you'll either get an index safely capped to 255 (if running
MySQL < 5.6) or an error (if running MySQL 5.6+).

Note that this is not a problem for Postgres or SQLite. It's also, in
this case, not a problem for MySQL with MyISAM, since the max length
there is 1000 bytes.

Our options for the summary field were to either truncate from 300
characters to 255 (destroying bits of history), to remove the index
entirely, or to cap the index ourselves. The first two options are
terrible, and the third is not directly supported by Django.

Turns out what we can do is create per-engine SQL initial data files
that will create the appropriate indexes. This will only work for new
installations, though, but covers that case.

We can then create an evolution that, depending on the database type,
invokes custom SQL for creating the index. On MySQL, that custom SQL
will contain a key length cap, and for everything else, there will be no
cap (since they don't support such a concept).

Created a database with this change on SQLite, MySQL (InnoDB), and Postgres,
testing the initial data SQL files. In each case, I saw that the index was
properly applied. For MySQL, the information on the index showed that the cap
was set to 255 characters.

Created a database without this change on SQLite, MySQL, and Postgres to test
the upgrade case. I ran an evolution with this change. I then saw the indexes.
Again, on MySQL, it showed a key length of 255.

reviewbot
  1. Tool: Pyflakes
    Processed Files:
        reviewboard/reviews/evolutions/__init__.py
        reviewboard/reviews/evolutions/review_request_summary_index_manual.py
        reviewboard/reviews/models/review_request.py
    
    Ignored Files:
        reviewboard/reviews/models/sql/reviewrequest.postgresql_psycopg2.sql
        reviewboard/reviews/models/sql/reviewrequest.sqlite3.sql
        reviewboard/reviews/models/sql/reviewrequest.mysql.sql
    
    
    
    Tool: PEP8 Style Checker
    Processed Files:
        reviewboard/reviews/evolutions/__init__.py
        reviewboard/reviews/evolutions/review_request_summary_index_manual.py
        reviewboard/reviews/models/review_request.py
    
    Ignored Files:
        reviewboard/reviews/models/sql/reviewrequest.postgresql_psycopg2.sql
        reviewboard/reviews/models/sql/reviewrequest.sqlite3.sql
        reviewboard/reviews/models/sql/reviewrequest.mysql.sql
    
    
  2. 
      
david
  1. Ship It!

  2. 
      
chipx86
Review request changed

Status: Closed (submitted)

Change Summary:

Pushed to release-2.0.x (52f7090)
Loading...