853070 - reps_dev_allizom_org database was causing replication failures

Status: RESOLVED FIXED

(Data & BI Services Team :: DB: MySQL, task)

Description

[Brandon Johnson [:cyborgshadow]]

Last night we noticed some issues with the reps_dev_allizom_org database giving replication errors on foreign key checks.

I recognized this specifically on a "test by Ioana" that Ioana made today. I'm CCing her on the bug in case she knows who the devs are who made the database to forward this information to them.

I found that the referenced table, events_event, was MyISAM. Adjusting the table to InnoDB solved the issue.

Here's the query I used to fix the issue:

ALTER TABLE reps_dev_allizom_org.events_event ENGINE=InnoDB;

Query OK, 28 rows affected (0.13 sec)
Records: 28  Duplicates: 0  Warnings: 0

Comment 1

[Giorgos Logiotatidis [:giorgos]]

Thanks for taking care of this Brandon. 

Strange that there is a MyISAM table there. Do we have other table MyISAM tables in 'remo'?

Can you verify that django database configuration, under settings/local.py looks similar to the following snippet? This sets storage_engine to InnoDB and I believe it should be there from the very start. 

"""
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'remo',
        'USER': 'root',
        'PASSWORD': '',
        'HOST': '',
        'PORT': '',
        'OPTIONS': {
            'init_command': 'SET storage_engine=InnoDB',
            'charset' : 'utf8',
            'use_unicode' : True,
        },
        'TEST_CHARSET': 'utf8',
        'TEST_COLLATION': 'utf8_general_ci',
    },
    # 'slave': {
    #     ...
    # },
}

"""

Comment 2

[Sheeri Cabral [:sheeri]]

Giorgos - 

This happened again today, but on reps_allizom_org (see bug 853395). I applied the same fix that Brandon did yesterday.

Here's what I see in the local.py file, with the password elided (on engagementadm.private.phx1.mozilla.com, in  /data/engagement/www/reps.mozilla.org/remo/remo/settings/local.py)


DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'reps_mozilla_org',
        'USER': 'reps',
        'PASSWORD': 'ELIDED',
        'HOST': 'db-engagement-rw-zeus.phx1.mozilla.com',
        'PORT': '3306',
        'OPTIONS': {
            'init_command': 'SET storage_engine=InnoDB',
            'charset' : 'utf8',
            'use_unicode' : True,
        },
        'TEST_CHARSET': 'utf8',
        'TEST_COLLATION': 'utf8_general_ci',
    },
    # 'slave': {
    #     ...
    # },
}

So indeed, the storage engine should be being set appropriately.

Is events_event a new table? or could it be that it's been MyISAM for a long time but only now foreign keys are being put in place and being used? 2 weeks ago this wasn't an issue, but we've seen it several times in the past few days. Did anything change last week?

Comment 3

[Sheeri Cabral [:sheeri]]

Note also that this db cluster has a master and 3 slaves, but only 2 of the slaves had this problem, even though they're all set to have the default storage engine be InnoDB.

Comment 4

[Sheeri Cabral [:sheeri]]

There are 2 tables on all the machines that are MySIAM, temp.mm and temp.mi.

On the 2 slaves, these additional tables are MyISAM:

 reps_allizom_org      django_admin_log    
 reps_allizom_org      events_attendance   
 reps_allizom_org      events_metric
 reps_allizom_org      profiles_useravatar 
 reps_allizom_org      tastypie_apiaccess  
 reps_allizom_org      tastypie_apikey   
  
 reps_dev_allizom_org  django_admin_log    
 reps_dev_allizom_org  events_attendance   
 reps_dev_allizom_org  events_metric       
 reps_dev_allizom_org  profiles_useravatar 
 reps_dev_allizom_org  tastypie_apiaccess  
 reps_dev_allizom_org  tastypie_apikey   

But the master and other slave don't have that problem, those tables are InnoDB. 
Those tables were all created between June 6th and July 27th of last year, so it's possible they were created before the default for innodb was put into place.

Would you like us to convert the remaining tables to InnoDB?

Comment 5

[Giorgos Logiotatidis [:giorgos]]

I don't know when the 'SET storage_engine=InnoDB' was put in place in the server environment, but this was suggested in local.py-dist from the first push (back in Feb 2012). 

So if the option was where from the first moment, it's strange that we ended up with some MyISAM tables, let alone that these tables exist only in same servers.

Mozillians had a similar issue with a MyISAM table a month or two ago, which we couldn't tell where it came from :/ Thoughts?

In any case, please convert the remaining tables to InnoDB.

Comment 6

[Sheeri Cabral [:sheeri]]

Were these all pushed using the ORM? Or is it possible someone copied and pasted a table definition from a local environment that wasn't set up the same?

I think we set the default storage engine as InnoDB on the servers either in March or in June 2012, so it could be because of that.

Comment 7

[Sheeri Cabral [:sheeri]]

OK, we're all clear on the reps databases, on the master and all slaves:

mysql>  select table_schema,table_name from information_schema.tables where engine='myisam' and table_schema not in ('information_Schema','mysql');
Empty set (0.01 sec)

Comment 8

[Giorgos Logiotatidis [:giorgos]]

(In reply to Sheeri Cabral [:sheeri] from comment #6)
> Were these all pushed using the ORM? Or is it possible someone copied and
> pasted a table definition from a local environment that wasn't set up the
> same?

Yes, all pushed using the ORM.

Thanks for fixing this Sheeri!