heat-manage db_sync crashes on migration 033_software_config because of MySQL error 1709

Bug #1316646 reported by Claudio Forlivesi
32
This bug affects 5 people
Affects Status Importance Assigned to Milestone
OpenStack Heat
Fix Released
High
Clint Byrum
Icehouse
Fix Released
High
Unmesh Gurjar

Bug Description

I am running heat-manage db_sync against MySQL 5.6.17, and I get the following errors:

1) if the database was created using:

DROP DATABASE IF EXISTS heat;
CREATE DATABASE heat;
GRANT ALL PRIVILEGES ON heat.* TO 'heat'@'localhost' IDENTIFIED BY 'HEAT_DBPASS';
GRANT ALL PRIVILEGES ON heat.* TO 'heat'@'%' IDENTIFIED BY 'HEAT_DBPASS';
FLUSH PRIVILEGES;

I get the following error:

# heat-manage db_sync
No handlers could be found for logger "heat.common.config"
2014-05-06 14:06:00.630 2059 WARNING heat.openstack.common.db.sqlalchemy.session [-] This application has not enabled MySQL traditional mode, which means silent data corruption may occur. Please encourage the application developers to enable this mode.
2014-05-06 14:06:00.781 2059 INFO migrate.versioning.api [-] 14 -> 15...
2014-05-06 14:06:01.183 2059 INFO migrate.versioning.api [-] done
2014-05-06 14:06:01.184 2059 INFO migrate.versioning.api [-] 15 -> 16...
2014-05-06 14:06:01.286 2059 INFO migrate.versioning.api [-] done
2014-05-06 14:06:01.286 2059 INFO migrate.versioning.api [-] 16 -> 17...
2014-05-06 14:06:01.411 2059 INFO migrate.versioning.api [-] done
2014-05-06 14:06:01.412 2059 INFO migrate.versioning.api [-] 17 -> 18...
2014-05-06 14:06:01.563 2059 INFO migrate.versioning.api [-] done
2014-05-06 14:06:01.564 2059 INFO migrate.versioning.api [-] 18 -> 19...
2014-05-06 14:06:01.730 2059 INFO migrate.versioning.api [-] done
2014-05-06 14:06:01.731 2059 INFO migrate.versioning.api [-] 19 -> 20...
2014-05-06 14:06:01.862 2059 INFO migrate.versioning.api [-] done
2014-05-06 14:06:01.862 2059 INFO migrate.versioning.api [-] 20 -> 21...
2014-05-06 14:06:01.943 2059 INFO migrate.versioning.api [-] done
2014-05-06 14:06:01.944 2059 INFO migrate.versioning.api [-] 21 -> 22...
2014-05-06 14:06:02.051 2059 INFO migrate.versioning.api [-] done
2014-05-06 14:06:02.051 2059 INFO migrate.versioning.api [-] 22 -> 23...
2014-05-06 14:06:02.156 2059 INFO migrate.versioning.api [-] done
2014-05-06 14:06:02.156 2059 INFO migrate.versioning.api [-] 23 -> 24...
2014-05-06 14:06:02.205 2059 INFO migrate.versioning.api [-] done
2014-05-06 14:06:02.205 2059 INFO migrate.versioning.api [-] 24 -> 25...
2014-05-06 14:06:02.366 2059 INFO migrate.versioning.api [-] done
2014-05-06 14:06:02.366 2059 INFO migrate.versioning.api [-] 25 -> 26...
2014-05-06 14:06:02.506 2059 INFO migrate.versioning.api [-] done
2014-05-06 14:06:02.507 2059 INFO migrate.versioning.api [-] 26 -> 27...
2014-05-06 14:06:02.650 2059 INFO migrate.versioning.api [-] done
2014-05-06 14:06:02.650 2059 INFO migrate.versioning.api [-] 27 -> 28...
2014-05-06 14:06:03.081 2059 INFO migrate.versioning.api [-] done
2014-05-06 14:06:03.081 2059 INFO migrate.versioning.api [-] 28 -> 29...
2014-05-06 14:06:03.212 2059 INFO migrate.versioning.api [-] done
2014-05-06 14:06:03.212 2059 INFO migrate.versioning.api [-] 29 -> 30...
2014-05-06 14:06:03.286 2059 INFO migrate.versioning.api [-] done
2014-05-06 14:06:03.287 2059 INFO migrate.versioning.api [-] 30 -> 31...
2014-05-06 14:06:03.350 2059 INFO migrate.versioning.api [-] done
2014-05-06 14:06:03.351 2059 INFO migrate.versioning.api [-] 31 -> 32...
2014-05-06 14:06:03.536 2059 INFO migrate.versioning.api [-] done
2014-05-06 14:06:03.537 2059 INFO migrate.versioning.api [-] 32 -> 33...
ERROR: (OperationalError) (1709, 'Index column size too large. The maximum column size is 767 bytes.') 'CREATE INDEX ix_software_config_tenant ON software_config (tenant)' ()

I re-tried using the following DB definition:

DROP DATABASE IF EXISTS heat;
SET @@global.innodb_file_per_table=true;
SET @@global.innodb_file_format=barracuda;
SET @@global.innodb_large_prefix=true;
CREATE DATABASE heat CHARACTER SET utf8 COLLATE utf8_general_ci;
GRANT ALL PRIVILEGES ON heat.* TO 'heat'@'localhost' IDENTIFIED BY 'HEAT_DBPASS';
GRANT ALL PRIVILEGES ON heat.* TO 'heat'@'%' IDENTIFIED BY 'HEAT_DBPASS';
FLUSH PRIVILEGES;

But I get exactly the same issue. Do I have to use any special flag or configuration trick on MySQL?

Revision history for this message
Clint Byrum (clint-fewbar) wrote :

This may have gone undetected in the past because people had latin1 databases, but we do a lot to push people toward UTF-8 which makes index size 3 bytes per "char". The answer is either to reduce it to String(255) or change it to a VARBINARY. The former is preferred, as 256 byte varchars become TINYTEXT which is less efficient as it is stored basically as a separate row.

Changed in heat:
status: New → Triaged
importance: Undecided → Critical
Revision history for this message
Clint Byrum (clint-fewbar) wrote :

In reasearching this, I cannot reproduce the error on MySQL 5.5. 5.6 is definitely coming, so this is a priority, but most Linux distros still ship 5.5.

Anyway, I have posted several fixes to reduce the column size to 64. That will make it work on 5.6.

Changed in heat:
importance: Critical → High
Changed in heat:
assignee: nobody → Clint Byrum (clint-fewbar)
status: Triaged → In Progress
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Related fix proposed to heat (master)

Related fix proposed to branch: master
Review: https://review.openstack.org/100475

Revision history for this message
Clint Byrum (clint-fewbar) wrote :

Bot must have missed this patch:

https://review.openstack.org/#/c/92421/

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to heat (master)

Reviewed: https://review.openstack.org/92421
Committed: https://git.openstack.org/cgit/openstack/heat/commit/?id=db0a7fe91d3eddc6d337028332fbd496bb000df2
Submitter: Jenkins
Branch: master

commit db0a7fe91d3eddc6d337028332fbd496bb000df2
Author: Clint Byrum <email address hidden>
Date: Tue May 6 10:48:36 2014 -0700

    Patch 033 migration to work w/ MySQL 5.6

    With MySQL 5.6 setup to use UTF-8 for everything, the 033 migration
    will fail. This is because a VARCHAR(256) cannot be indexed in UTF-8
    databases. In versions of MySQL before 5.6, this produced a warning and
    a truncated, less-useful index. In 5.6 it was changed to an error.

    We will reduce these columns further to 64 bytes in the model in a
    subsequent fix. For now, we will just create the table with 64 character
    columns on new systems, which will allow this migration to work properly
    on mysql-5.6. There is no danger of overflow, as 64 chars is what Keystone
    uses for them anyway.

    Change-Id: I27b9c61828301ba8c392b3d316f656641af753bc
    Closes-Bug: #1316646

Changed in heat:
status: In Progress → Fix Committed
Changed in mos:
milestone: none → 5.1
assignee: nobody → MOS Heat (mos-heat)
importance: Undecided → Critical
status: New → Triaged
no longer affects: mos
Changed in heat:
milestone: none → juno-2
status: Fix Committed → Fix Released
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Related fix merged to heat (master)

Reviewed: https://review.openstack.org/100475
Committed: https://git.openstack.org/cgit/openstack/heat/commit/?id=dcfac2ec67e167990a49e82d713f01344f0664cd
Submitter: Jenkins
Branch: master

commit dcfac2ec67e167990a49e82d713f01344f0664cd
Author: Bernhard M. Wiedemann <email address hidden>
Date: Mon Jun 16 12:05:31 2014 +0000

    Patch 044 migration to work w/ MySQL 5.6

    With MySQL 5.6 setup to use UTF-8 for everything, the 044 migration
    will fail. This is because a VARCHAR(256) cannot be indexed in UTF-8
    databases. In versions of MySQL before 5.6, this produced a warning and
    a truncated, less-useful index. In 5.6 it was changed to an error.

    We will reduce these columns further to 64 bytes in the model in a
    subsequent fix. For now, we will just create the table with 64 character
    columns on new systems, which will allow this migration to work properly
    on mysql-5.6. There is no danger of overflow, as 64 chars is what Keystone
    uses for them anyway.

    Change-Id: I73932f69ff662ef5bb030b6516bd3d32507de658
    Related-Bug: #1316646

Thierry Carrez (ttx)
Changed in heat:
milestone: juno-2 → 2014.2
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to heat (stable/icehouse)

Fix proposed to branch: stable/icehouse
Review: https://review.openstack.org/142431

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to heat (master)

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

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Change abandoned on heat (master)

Change abandoned by Clint 'SpamapS' Byrum (<email address hidden>) on branch: master
Review: https://review.openstack.org/92406
Reason: I don't have time to work on Heat anymore.

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to heat (stable/icehouse)

Reviewed: https://review.openstack.org/142431
Committed: https://git.openstack.org/cgit/openstack/heat/commit/?id=314437f2323ed90ec2338fc7dae78b3be70c2beb
Submitter: Jenkins
Branch: stable/icehouse

commit 314437f2323ed90ec2338fc7dae78b3be70c2beb
Author: Clint Byrum <email address hidden>
Date: Tue May 6 10:48:36 2014 -0700

    Patch 033 migration to work w/ MySQL 5.6

    With MySQL 5.6 setup to use UTF-8 for everything, the 033 migration
    will fail. This is because a VARCHAR(256) cannot be indexed in UTF-8
    databases. In versions of MySQL before 5.6, this produced a warning and
    a truncated, less-useful index. In 5.6 it was changed to an error.

    We will reduce these columns further to 64 bytes in the model in a
    subsequent fix. For now, we will just create the table with 64 character
    columns on new systems, which will allow this migration to work properly
    on mysql-5.6. There is no danger of overflow, as 64 chars is what Keystone
    uses for them anyway.

    Closes-Bug: #1316646
    (cherry picked from commit db0a7fe91d3eddc6d337028332fbd496bb000df2)

    Change-Id: I27b9c61828301ba8c392b3d316f656641af753bc

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Duplicates of this bug

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.