MySQL error on rename when dropping column with a constraint

a nasty MySQL bug

Recently I wanted to remove a column from phpmyadmin that is covered with a constraint. Impossible to do so, MySQL throws following error:

ERROR 1025 (HY000): Error on rename of './optiner_prestadmin/#sql-6bc_3e6' to './optiner_prestadmin/order_history' (errno: 150)

You may get above error when executing a query removing index, e.g.

ALTER TABLE order_history DROP INDEX user_id;

Fortunately, there is a solution, not very easy to find in the internet (I used comments from this article).

execute the following in MySQL:

SHOW CREATE TABLE order_history;

You shall get something like:

order_history | CREATE TABLE `order_history` (
  `order_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `status_id` bigint(20) NOT NULL,
  `informed` tinyint(1) DEFAULT '0',
  `comment` text,
  `created_at` datetime NOT NULL,
  `created_by` bigint(20) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `order_id_idx` (`order_id`),
  KEY `user_id_idx` (`user_id`),
  KEY `status_id_idx` (`status_id`),
  KEY `created_by_idx` (`created_by`),
  CONSTRAINT `order_history_created_by_sf_guard_user_id`
    FOREIGN KEY (`created_by`) REFERENCES `sf_guard_user` (`id`)
      ON DELETE CASCADE,
  CONSTRAINT `order_history_order_id_order_info_id`
    FOREIGN KEY (`order_id`) REFERENCES `order_info` (`id`),
  CONSTRAINT `order_history_status_id_order_status_id`
    FOREIGN KEY (`status_id`) REFERENCES `order_status` (`id`),
  CONSTRAINT `order_history_user_id_sf_guard_user_id`
    FOREIGN KEY (`user_id`) REFERENCES `sf_guard_user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28305 DEFAULT CHARSET=utf8

we want to delete the whole column user_id (with all corresponding indices, constraints, etc.) - need to drop the FOREIGN KEY first:
```
ALTER TABLE order_history
  DROP FOREIGN KEY `order_history_user_id_sf_guard_user_id`;
```
you'll find the name of the foreign key in the last constraint clause.

finally, drop the column:

ALTER TABLE `order_history` DROP `user_id`;

and that's it!

Query OK, 28065 rows affected (3,99 sec)
Records: 28065  Duplicates: 0  Warnings: 0

symfony & Doctrine behavior review

Doctrine behaviors

The main idea of this article is to create a short summary on Doctrine behaviors (really powerful and useful tools) which speed up development significantly. I tried to mention the most important behaviors, providing brief description and links. It's quite common to reinvent the wheel, being just unconscious that someone has already implemented a feature you are just in need of. Moreover, I hope that both beginners and advanced symfony developers will find this article a useful source of brief information about Doctrine behaviors for everyday use.

Basically, a behavior lets you share some relations, algorithms and other features among your project's model. They are (or at least should be) highly configurable: providing you a mechanism that can be easily customized to fit in different projects. Customization usually includes: enabling/disabling some of the behavior features (you may use only some of them), renaming additional schema columns, configuring them (like adding 'not null'), etc. You can use many Doctrine behaviors in your projects: core behaviors, Doctrine extensions, symfony plugins - or if you've got nothing better to do late at nights - you can create your own behaviors ;)

Core behaviors

The Doctrine ORM is bundled with several behaviors (calles core behaviors), these are:

Versionable - add an entire XxxVersion table to store your Xxx model object versions as either only version numbers or numbers versions along with ALL column data - to see object data changes through time,
Timestampable - probably the most popular of all behaviors, adds created_at and updated_at timestamp columns to your model, automatically saving datetime when a record is created or updated, respecticely,
Sluggable - adds a slug column that stores a slug which is a unique index automatically and can be used along with sfDoctrineRoute class to refer to an object without passing its ID as a parameter; by default the update is disabled, since this may harm your search engines rating,
I18n - add an entire XxxTranslation table to provide Internationalization (I18n) for your Xxx model, essential when developing a multi-language project,
NestedSet - adds root_id, lft, rgt and level columns to your model to develop an advanced hierarchical data structure (such as product categories), nested sets is an alternative to adjacency model, more details on hierarchical data here,
Searchable - choose model columns you want to index and add an entire database table, speeding up a basic search engine development, more info about searching here,
Geographical - adds longitude and latitude columns storing geographical coordinates. Ever needed to use gmaps in your project, along with sfEasyGMapPlugin? Not only this behavior suits the data structure you need, but also provides you with getDistance() method to calculate distance between two Geographical objects,
SoftDelete - adds a deleted_at column which defines if a record has been marked as deleted (and if so, when). Useful when designing a highly complicated system where data consistency is important and even if some data should be invisible in the backend, it should still remain in the database.

extension behaviors

You may also use Doctrine extensions:

Blameable - adds an additional level of auditing capabilities to models, blameable allows you to track who created or last updated any model in an environment with many users, blameable is a great companion to Timestampable behavior,
EventLoggable, readme - logs any Doctrine Events (pre/post Delete/Insert/...) fired upon a record, log type may be chosen (e.g. file) and events may be limited to the chosen ones only,
Localizable - gives you functionality to convert units of measurement (e.g. kilometers to miles, etc.) or any other conversions desirable,
Locatable - gives you functionality using the Google Maps API to automatically populate your model with latitude and longitide information using Google, a fantastic tool to use along with sfEasyGMapPlugin,
Sortable - gives you sortable functionality to your models, enabling you to easily fetch next/previous objects, moving them up/down in a sorted list or swapping with another object,
Taggable - adds Tagging capabilities, creates a TaggableTag table and a XxxTaggableTag table for each taggable model, using refClass m:n db table relation, provides easy tag management: add, remove, remove all, set, etc, finally, gives you the possibility to get the most popular tags (for a tag cloud, for example) fetched either from chosen models or from all of them,

plugin behaviors

Finally, there are also symfony plugins providing custom behaviors:

sfDoctrineActAsSignablePlugin - a well developed alternative to blameable Doctrine extension,
sfDoctrineActAsTaggablePlugin - provides tag management (alternative to taggable Doctrine extension), provides a tag cloud generation,
csDoctrineActAsAttachablePlugin - associate various uploads with multiple models,
sfDoctrineJCroppablePlugin - add user-croppable images to your models with a javascript image cropper,
sfDoctrineNestedSetPlugin - a form and a validator providing easy NestedSet behavior management,
csDoctrineActAsCategorizablePlugin - categorize your models, mix, match or separate multiple models into multiple nested/flat categories,
sfDoctrineActAsRattablePlugin - rate Doctrine objects,
vjCommentPlugin - provides commentable behavior to comment news page, articles, etc,
ajDoctrineLuceneablePlugin - provides luceneable behavior with Lucene full text integration,

useful links about Doctrine behaviors:

notes

If you find any information missing, please, let me know. Hopefully, you'll find it useful, otherwise I'll lose the motivation to do such thing next time ;)

update record changes with sfDoctrineActAsSignablePlugin: Signable behavior

sfDoctrineActAsSignablePlugin

The plugin is fairly simple, it contains only a Doctrine temlate and a template event listener (basic stuff to implement a new Doctrine Behavior). It was initially released by Vitaliy Tverdokhlib.

Signable Doctrine Behavior

The plugin provides a Signable behavior ready to be used in your models:

Model:
  actAs:
    Signable: ~

It works very similarly to Timestampable behavior: created_at and updated_at timestamp columns are added, storing information about the datetime that a record was created and last updated. Signable adds created_by and updated_by columns storing information about the user that created or last updated a record.

The plugin may be used along with sfDoctrineGuardPlugin. This gives you a possibility to refer to the sf_guard_user table. No additional configuration is needed, the plugin automatically checks if sfDoctrineGuardPlugin is installed.

configuration

The most important thing you can configure is to choose the type of the user field: it can be either string or integer:

Model:
  actAs:
    Signable:
      created:
        type: string
      updated:
        type: integer

If string type is chosen, you can simply display the name of the user (e.g. in frontend blog article display page), but can do nothing further. If you choose integer, you store ID of the user, which gives you a possibility to refer to the user in the user database table (which gives you all possibilities of using user Doctrine model).

You can configure the behavior to meet your needs. Let's take a look at some examples:

The created_by and updated_by column names are the default ones. You may change them:

Model:
  actAs:
    Signable:
      created:
        name: creator_id
        type: integer
      updated:
        name: update_username
        type: string

You want to know which user has created a record only (meaning that the user who updated the record is not important). In this case, disable udpated option:
```
Model:
  actAs:
    Signable:
      updated:
        disabled: true
```
You want to add the Signable behavior to models of an existing project that already maintaines lots of data. And you want the created_by/updated_by user to be not null, because there's always someone who creates or updates a record. In a big project (a CRM, ERP, etc.) it's a good solution to create a system user (with id=1 for example) who represents all system actions (a daemon creating/updating objects from cron tasks):
```
Model:
  actAs:
    Signable:
      created:
        options:
          notnull: true
          default: 1
      updated:
        options:
          notnull: true
          default: 1
```
In this case, adding created_by/updated_by database columns will fill them initially with 1 (system user) and you can refer to the user (e.g. using getUser() method), being sure that there's always a user referenced. And all further modifications will store real user ids.

real world examples

Blog articles are being added by application users. You add Signable behavior (with type: string) and you may use:
```
echo $article->getCreatedBy();
```
in the app frontend to display who submitted the article.
An E-commerce applications management system: users (employees) log in to the system and create orders that customers submit. You add Signable behavior (with type: integer). Whenever an order is created, the created_by is filled with the user ID. The application can generate statistics on which users added the most orders.

No matter if your project is a complex management system with extended backend - or a small company website with mainly frontend developed - you'll find this plugin useful!

Symfony world