• 
      

    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:
    Completed
    Change Summary:
    Pushed to release-2.0.x (52f7090)