Symfony2 / Doctrine2: Don't drop fulltext index on schema:update

Question

To support fulltext indexing in Symfony2 I use a MyISAM mirror table. We're regularly copying our production dataset to that table and created a SearchEntity that maps the table's structure and is associated with the real entities. So we can execute our search queries on our SearchRepository (using a custom MATCH AGAINST statement builder) and retrieve the found entities by resolving the associations.

Now, when I execute doctrine:schema:update Doctrine2 doesn't recognize the (manually added) indices on that table and wants to drop them. Unfortunately there is no advice annotation that says "but leave this index intact!".

I already tried to trick Doctrine using an @Index annotation with the same fields as in the fulltext index (prefixed ft_) and then execute some SQL manually to replace them with my FT index but that fails as well: when Doctrine initially creates the table with those dummy indices it fails because the index key length is larger than 1000 bytes (which is a hard limit for obvious reasons in MySQL)

Question is: can I advise Doctrine to leave the indices it finds on the table intact on a schema:update command? Is there a way to hack that into the framework? It's extremeley cumbersome to recreate the fulltext index after each schema update :(

SearchEntity:

/**
 * @ORM\Table(name="tmp_search2",options={"engine"="MyISAM"},
 *            uniqueConstraints={@ORM\UniqueConstraint(name="uq",columns={"language_id","product_offer_id","product_group_id","retailer_id"} )},
  *            indexes={@Index(name="price_idx", columns={"product_offer_price"}),
  *                     @Index(name="started_at_idx", columns={"product_offer_started_at"}),
  *                     @Index(name="ended_at_idx", columns={"product_offer_ended_at"}),
  *                     @Index(name="ft_products", columns={"product_name"}),
  *                     @Index(name="ft_product_group", columns={"product_group_name"}),
  *                     @Index(name="ft_product_retailer", columns={"retailer_name"})
  *            }
  * )
  * @ORM\Entity(repositoryClass="SearchRepository")
  */

class SearchEntity
{
    /**
     * This field is only here to satisfy doctrine's need for a non-composite primary key.
     * @ORM\Id
     * @ORM\Column(name="id", type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
     private $searchId;

   /**
    * @ORM\ManyToOne(targetEntity="ProductOffer")
    * @ORM\JoinColumn(name="product_offer_id", referencedColumnName="id")
    */
    private $productOffer;

   /**
     * @var integer
     *
     * @ORM\Column(name="product_offer_price", type="integer")
     */
    private $price;

The SQL to create the tmp_search indices (first drop what doctrine leaves there, then create ours)

DROP INDEX ft_products ON tmp_search2;
DROP INDEX ft_product_group ON tmp_search2;
DROP INDEX ft_product_retailer ON tmp_search2;

# import product data and then...

CREATE FULLTEXT INDEX ft_products ON tmp_search2 (product_name,product_short_text,product_long_text);
CREATE FULLTEXT INDEX ft_product_group ON tmp_search2 (product_group_name);
CREATE FULLTEXT INDEX ft_product_retailer ON tmp_search2 (retailer_name);

Answer 1

I was able to solve this using migrations then adding fake indices with the same name.

A migration added the actual fulltext index using raw SQL:

$this->addSql('ALTER TABLE content ADD FULLTEXT fulltext_content(title, description)');

Then I added the index to the Entity definition:

@ORM\Table(name="content", indexes={@ORM\Index(name="fulltext_content",columns={"title","description"})})

As long as you generate the fulltext indices first, Doctrine will no longer delete them.

Answer 2

Like someone else answered it's not a good idea to use doctrine:schema:update on production because any minor error in your code could potentially cause half your database to be dropped.

I work on a fairly large project and we use doctrine:schema:update --dump-sql to find queries that need to be executed and execute them manually.

Edit: The only other suggestion I have in addition is if you don't want to execute queries manually you could process the output of doctrine:schema:update --dump-sql filter the queries you don't want to execute and run the remaining ones on the database. Or create a command that creates your indexes after updating your schema, something like myproject:schema:createIndexes (or whatever)

Answer 3

1. You shouldn't use doctrine:schema:update on production. Use migrations instead.
2. As I remember Doctrine 2 doesn't have MyIsam full support, but you can create table manually and use it
3. Mysql Fulltext search is not the best choise for searching. Maybe Sphinx or Lucene are more appropriate solution for your task?