Mysql rows getting deleted after an unrelated insertion action happens in another table

Question

I'll try to explain this as simple as possible, since it involves an ecommerce music site.

So I have an app website based on CakePHP which is an ecommerce site, we sell online Music.

The problem I'm having is that after someone buys an Album, the Genre rows of that album get Deleted, and then the Album does not contain any rows detailing what Music Genre it belongs to.

In simple view:

1. Album of id=65 contains Genres of 5,7 and 10. This is how the table detailing the genres looks like:

   album_genre (table)

     fk_genre   fk_album
        12         87
        9          87
        5          65
        7          65
        10         65
   

After the user buys the Album of id=65, the album_genre table rows containing that fk_album id get deleted.

      fk_genre   fk_album
         12         87
         9          87

What happens when a user buys an Album?

1. User pays with card or instore credit

2. Payment if accepted, then proceeds to create an MusicItem row:

   2.1 MusicItem row contains:
          fk_album, fk_receipt, fk_user, downloads
   

3. Proceed to increment sales number for album in Album table

There is no code close by or executed that edits/deals with the album_genre table when someone buys an album, or any other place except when you're creating an Album in the backend to add to the store...

I suspect it might be a problem with the database maybe? I can't seem to find the problem, either in logs or debug logs or anything =/ need some help in ideas to resolve this bug.

Update:

• Did a SHOW TRIGGERS and came back empty.

• Found that album_genre has 3 constraints, even though it has only 2 columns:

    - Columns are: fk_album, fk_genre
    - Constraints are `PRIMARY`, `FOREIGN KEY` and `FOREIGN KEY`
  

Update 2:

• Showing fragment of code from step 3

  public function incrementSales($idAlbum)
  {
      $parameters = array(
                  'fields'=>array('Album.album_sales'),
                  'recursive'=>-1,
                  'conditions'=>array('Album.id_album'=>$idAlbum)
      );
  
      $album = $this->find('first',$parameters);
      $newQuantity = $album['Album']['album_sales'] + 1;
      $this->read(null,$idAlbum);
      $this->set('album_sales',$newQuantity);
  
      if($this->save())
          return true;
      else
          return false;
   }
  

Answer 1

I finally figured it out,

Top fix the problem I recreated the table. The old (problematic) table looked like this:

  fk_genre   fk_album
     5          37
     9          37
     12         89
     3          89
     67         89      

The new (working) table structure looks like this:

  id   fk_genre   fk_album
   1      5          37
   2      9          37
   3      12         89
   4      3          89
   5      67         89

The problem had to do with some confusion in the table with having no INDEX set up and just having two PRIMARY keys fk_genre and fk_album.

I figured if I added an index it would surely help in data structuring and order, leaving the job of differentiating each row with the INDEX id, instead of the combination of fk_genre and fk_album, and it worked!

Edit:

Forgot to mention that the Model class of AlbumGenre.php that deals with that table did not had specified a Primary Key set up, so i'm guessing also that CakePHP got confused as to what to use as Primary Key and probably used any of the two.

 public  $primaryKey = 'id';