mysql engine InnoDB not being used on centos or redhat 6.5

[Mark Vanderwiel]

when using centos or redhat 6.5, it has mysql 5.1, with default engine of myISAM. Openstack projects require InnoDB. I see some code, neutron/db/model_base.py#L25, that looks to try to set the engine type for create tables. But after an install, tables are instead created with default engine myISAM.
Where seeing this on neutron and ceilometer.

[Darren Birkett (darren-birkett)]

This appears to affect the ovs_tunnel_endpoints table and the extradhcpopts table.

Seems like the top level __table_args__ (which define the engine as innoDB), are not getting inherited properly when these tables are created.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/78649

[John Eckersberg (jeckersb)]

I think this might be a more widespread problem.

There was a bug in alembic which prevented it from rendering table keyword arguments in migrations. Here is the issue:

https://bitbucket.org/zzzeek/alembic/issue/110/

It seems like most of the migrations in neutron were made with the buggy version of alembic, and thus do not have the mysql_engine table arguments passed during the create_table calls:

$ git grep mysql_engine -- neutron/db/migration/alembic_migrations/versions/ | wc -l
5

I would expect each call to create_table to contain the correct mysql_engine line, so there should be roughly:

$ git grep create_table -- neutron/db/migration/alembic_migrations/versions/ | wc -l
185

I am no expert on sqlalchemy/alembic so maybe I'm missing something, please correct me if so.

[Darren Birkett (darren-birkett)]

Hi John,

All neutron tables do actually get created with the innodb mysql_engine as inherited from the parent class, except the two detailed. This is because when they are created, they attempt to set some additional __table_args__ and the __table_args__ from the parent class are not explicitly inherited.

The attached/proposed fix resolves this issue for those tables.

Darren

[John Eckersberg (jeckersb)]

I see that bit, but that only affects what gets rendered into the migration scripts. The problem is the bug in alembic I posted. Even though __table_args__ is set correctly, it was ignored when the migration scripts were created. The models aren't consulted when the migrations are run with neutron-db-manage. If alembic rendered them wrong and the incorrect script was checked in (which seems to be what happened), then the migrations themselves are broken until they are manually corrected.

To show this, I've run devstack with neutron on RHEL 6.5 following the basic setup here:
https://wiki.openstack.org/wiki/NeutronDevstack

Devstack explicitly configures MySQL to use the InnoDB engine as the default in lib/databases/mysql. This masks the problem, so I commented out that section to leave MyISAM as the default.

Running stack.sh failed with the following error, which is the first hint that InnoDB is *not* being used:

sqlalchemy.exc.OperationalError: (OperationalError) (1071, 'Specified key was too long; max key length is 1000 bytes') '\nCREATE TABLE cisco_ml2_credentials (\n\tcredential_id VARCHAR(255), \n\ttenant_id VARCHAR(255) NOT NULL, \n\tcredential_name VARCHAR(255) NOT NULL, \n\tuser_name VARCHAR(255), \n\tpassword VARCHAR(255), \n\tPRIMARY KEY (tenant_id, credential_name)\n)\n\n' ()

Checking the storage engine used by the tables that were created prior to the fatal error confirms they are using MyISAM and not InnoDB as intended:

mysql> select engine, count(*) from information_schema.tables where table_schema = 'neutron_ml2' group by engine\G
*************************** 1. row ***************************
  engine: MyISAM
count(*): 30

I will put together a patch for review that adds the mysql_engine option to all of the create_table calls in the neutron migrations.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/81334

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/78649
Committed: https://git.openstack.org/cgit/openstack/neutron/commit/?id=72e37ddcd067432675543c8f4d327fe766bc8044
Submitter: Jenkins
Branch: master

commit 72e37ddcd067432675543c8f4d327fe766bc8044
Author: Darren Birkett <email address hidden>
Date: Thu Mar 6 16:17:03 2014 +0000

    Correctly inherit __table_args__ from parent class

    Where unique constraints are added to certain tables by populating
    __table_args__, we need to ensure we inherit the top level
    __table_args__ that define the default storage engine to use when
    creating tables.

    Change-Id: Ib886b84922830b60282b4f882591e43f5041e713
    Fixes: bug 1288358

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: milestone-proposed
Review: https://review.openstack.org/86149

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/86149
Committed: https://git.openstack.org/cgit/openstack/neutron/commit/?id=3547788a22f2008a062ccf0ddcec04f834b097c8
Submitter: Jenkins
Branch: milestone-proposed

commit 3547788a22f2008a062ccf0ddcec04f834b097c8
Author: Darren Birkett <email address hidden>
Date: Thu Mar 6 16:17:03 2014 +0000

    Correctly inherit __table_args__ from parent class

    Where unique constraints are added to certain tables by populating
    __table_args__, we need to ensure we inherit the top level
    __table_args__ that define the default storage engine to use when
    creating tables.

    Change-Id: Ib886b84922830b60282b4f882591e43f5041e713
    Fixes: bug 1288358
    (cherry picked from commit 72e37ddcd067432675543c8f4d327fe766bc8044)

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/81334
Committed: https://git.openstack.org/cgit/openstack/neutron/commit/?id=466e89970f11918a809aafe8a048d138d4664299
Submitter: Jenkins
Branch: master

commit 466e89970f11918a809aafe8a048d138d4664299
Author: Jakub Libosvar <email address hidden>
Date: Tue Jul 22 18:50:07 2014 +0200

    Use storage engine when creating tables in migrations

    Although __table_args__ is set correctly in the base model, existing
    migration scripts were not generated with the mysql_engine option in
    the alembic output, likely due to:

    https://bitbucket.org/zzzeek/alembic/issue/110/

    This adds the mysql_engine option to each table creation operation.

    Change-Id: I990e8d64bcadf2be4e1e319319bc23225123b304
    Closes-Bug: #1288358