lol_dba tries to create indexes on columns that don't exist in the table

[cgat]

Here's the output of bundle exec rake db:find_indexes when run on my rails 3.2.12 app:

   class AddMissingIndexes < ActiveRecord::Migration
      def change
        add_index :keyword_visit_associations, :visit_id
        add_index :keyword_visit_associations, :keyword_id
        add_index :keyword_visit_associations, [:keyword_id, :visit_id]
        add_index :hiking_parties, :visit_id
        add_index :hiking_parties, :participant_id
        add_index :hiking_parties, [:participant_id, :visit_id]
        add_index :images, [:id, :type]
        add_index :images, :camera_id
        add_index :images, :lens_id
     ->add_index :locations, [:capture_id, :station_id]
        add_index :locations, :visit_id
     ->add_index :locations, [:location_image_id, :visit_id]
     ->add_index :locations, [:capture_id, :visit_id]
     ->add_index :visits, [:capture_id, :station_id]
     ->add_index :visits, [:location_image_id, :station_id]
        add_index :visits, :station_id
        add_index :survey_seasons, :survey_id
        add_index :captures, :camera_id
        add_index :captures, :lens_id
        add_index :comparison_indices, [:capture_id, :historic_capture_id]
        add_index :comparison_indices, :capture_id
        add_index :comparison_indices, :historic_capture_id
        add_index :photographers_visits, [:photographer_id, :participant_id]
        add_index :photographers_visits, [:visits_photographed_id, :visit_id]
        add_index :fn_authors_visits, [:fn_author_id, :participant_id]
        add_index :fn_authors_visits, [:visits_authored_id, :visit_id]
        add_index :historic_visits, :station_id
        add_index :surveys, :surveyor_id
        add_index :historic_captures, :camera_id
        add_index :historic_captures, :lens_id
        add_index :historic_captures, [:hcapture_owner_id, :hcapture_owner_type]
        add_index :metadata_files, [:metadata_owner_id, :metadata_owner_type]
      end
    end

And you can see my schema here: https://gist.github.com/cgat/5873205

I've put an arrow next to a few of the failing indexes (the table does not contain the column). It seems to be creating indices for has_many and has_many through relationships (that's my best guess).

Here are the relationships from my visit model:

  belongs_to :station, inverse_of: :visits
  has_and_belongs_to_many :photographers, join_table: :photographers_visits, class_name: 'Participant', order: :last_name
  has_and_belongs_to_many :fn_authors, join_table: :fn_authors_visits, class_name: 'Participant', order: :last_name
  has_many :locations, inverse_of: :visit, dependent: :destroy, order: :location_identity
  has_many :sorted_location_photos, through: :locations, source: :location_photos, order: :image
  has_many :located_captures, through: :locations, source: :captures
  has_many :keyword_visit_associations, dependent: :destroy
  has_many :keywords, through: :keyword_visit_associations, order: :keyword
  has_many :hiking_parties, dependent: :destroy
  has_many :hiking_party_participants, through: :hiking_parties, source: :participant, order: :last_name
  has_many :field_notes, inverse_of: :visit, dependent: :destroy, order: :field_note_file
  has_many :unsorted_captures, as: :capture_owner, class_name: "Capture", dependent: :destroy
  has_many :unsorted_location_photos, as: :image_owner, class_name: "LocationImage", dependent: :destroy, order: :image

[plentz]

@cgat thanks for the bug report. It will take a few weeks so I can look further since I'm really busy lately - sorry for that. If you provide a pull request with the fix, I will find some time to review it and merge the changes.

[cgat]

@plentz if I get some free time in the next couple weeks I'll see what I can do. It's obviously not a big problem for me at the moment, but I wanted to raise the issue in case anyone else was experiencing the problem (perhaps my setup is obscure). For what it's worth, I'm using version 1.5.0.

[chuyihuang]

me too has the same issue and this gem even creates duplicated indexes

[PikachuEXE]

Not a big issue but still exists

[giedriusr]

For me it also creates duplicated indexes.

[tecnobrat]

@plentz any word on this? ... love the gem :)

[plentz]

@cgat @chuyihuang @PikachuEXE @giedriusr @tecnobrat hey guys, I just released v1.6.6 that should fix this problem. give it a try and let me know if you still have some problem

[plentz]

I've found that using different versions of rails generate different indexes. Looking at the output of our tests, using rails 4.2 seems to fix all the problems. But I want to fix it for every version. So I've created the branch issue_22, if someone wants to give it a try :)

[plentz]

Guys, I've just released 2.1 and I think it fixes this issue. If you still have a problem, please, open a new issue reporting what you're seeing using the latest version.