Uploaded image for project: 'Bamboo Data Center'
  1. Bamboo Data Center
  2. BAM-10090

Indexing problem in table USER_COMMIT because of column COMMIT_REVISION text type

    • Icon: Bug Bug
    • Resolution: Fixed
    • Icon: Low Low
    • 3.4 M2
    • 3.3.2
    • None
    • None

      This error happens because the column COMMIT_REVISION in the table USER_COMMIT is set to TEXT,

      2011-10-24 12:06:05,071 ERROR [main] [SchemaUpdate] Unsuccessful: create index commit_rev_idx on USER_COMMIT (COMMIT_REVISION)
      2011-10-24 12:06:05,071 ERROR [main] [SchemaUpdate] BLOB/TEXT column 'COMMIT_REVISION' used in key specification without a key length
      

      There is a limitation on indexing TEXT type fields. As this post states, we can have VARCHAR type with maximum 65,532 bytes after MySQL 5.0.3.

      We should change the column type from TEXT to VARCHAR.

            [BAM-10090] Indexing problem in table USER_COMMIT because of column COMMIT_REVISION text type

            FWIW, I tested out a restart of Bamboo 3.4 after an initial upgrade and the index was created successfully as you suggested.

            David Corley added a comment - FWIW, I tested out a restart of Bamboo 3.4 after an initial upgrade and the index was created successfully as you suggested.

            Looks all good to me!

            David Corley added a comment - Looks all good to me!

            PiotrA added a comment -

            upgrade notes updated: http://confluence.atlassian.com/display/BAMBOO/Bamboo+3.4+Upgrade+Guide
            better logs: BAM-11070

            That's covering everything raised here, am I right? Or should we fix/track something else too? BTW: thanks for helping!

            PiotrA added a comment - upgrade notes updated: http://confluence.atlassian.com/display/BAMBOO/Bamboo+3.4+Upgrade+Guide better logs: BAM-11070 That's covering everything raised here, am I right? Or should we fix/track something else too? BTW: thanks for helping!

            Definitely in the upgrade notes for 3.4 as a gotcha for people using MySQL.

            If you can also add something to 3.4.5 so that it appears in one or more of the following locations:

            • Logs at the end of the upgrade in a pretty obvious message
            • Admin screen if Bamboo can detect the non-existence of the index

            ,that would be great.
            I suspect the addition of the index will probably improve the My Bamboo screen, from what I understand, and I know that screen can be painfully slow for us, so it's a performance win.

            David Corley added a comment - Definitely in the upgrade notes for 3.4 as a gotcha for people using MySQL. If you can also add something to 3.4.5 so that it appears in one or more of the following locations: Logs at the end of the upgrade in a pretty obvious message Admin screen if Bamboo can detect the non-existence of the index ,that would be great. I suspect the addition of the index will probably improve the My Bamboo screen, from what I understand, and I know that screen can be painfully slow for us, so it's a performance win.

            PiotrA added a comment -

            Good idea, but where?

            Where you would expect - as the user of Bamboo - to see such information?

            PiotrA added a comment - Good idea, but where? Where you would expect - as the user of Bamboo - to see such information?

            But the VARCHAR type is only used from 3.4 on, so there's no history of the 4000 limit in older versions as they used the TEXT datatype for that column.

            I didn't realize that the existence of the indices is checked every time Bamboo starts. If that is the case, then it should fix the problem. I guess a lot of users who have large,busy Bamboo instances like ours don't like restarting Bamboo if we can avoid it, so it may be worth noting somewhere that users should manually create the index if they wish to avoid a restart after upgrading to 3.4.x when using MySQL.

            David Corley added a comment - But the VARCHAR type is only used from 3.4 on, so there's no history of the 4000 limit in older versions as they used the TEXT datatype for that column. I didn't realize that the existence of the indices is checked every time Bamboo starts. If that is the case, then it should fix the problem. I guess a lot of users who have large,busy Bamboo instances like ours don't like restarting Bamboo if we can avoid it, so it may be worth noting somewhere that users should manually create the index if they wish to avoid a restart after upgrading to 3.4.x when using MySQL.

            PiotrA added a comment -

            David,

            Also FWIW, I'm not sure why the value of 4000 was set?

            AFAIK the only reason for that is "because it was always 4000". I agree with you that revisions in modern SCM's are rather shorter than 4000 chars. But from I understand the 4000 value was set in bamboo db schema long long time ago. Not that it make a sense now, especially after taking into consideration your findings about MySQL index implementation details.

            Regarding the upgrading issues:
            I believe the Bamboo code (including upgrade tasks) is OK. You're right that the upgrade task 2806 doesn't add the index after the conversion, but this index would be created after next Bamboo restart (if it's not already in the DB). I agree this isn't the best solution, but it works. Doesn't it?

            regards,
            Piotr Stefaniak

            PiotrA added a comment - David, Also FWIW, I'm not sure why the value of 4000 was set? AFAIK the only reason for that is "because it was always 4000". I agree with you that revisions in modern SCM's are rather shorter than 4000 chars. But from I understand the 4000 value was set in bamboo db schema long long time ago. Not that it make a sense now, especially after taking into consideration your findings about MySQL index implementation details. Regarding the upgrading issues: I believe the Bamboo code (including upgrade tasks) is OK. You're right that the upgrade task 2806 doesn't add the index after the conversion, but this index would be created after next Bamboo restart (if it's not already in the DB). I agree this isn't the best solution, but it works. Doesn't it? regards, Piotr Stefaniak

            Also FWIW, I'm not sure why the value of 4000 was set?
            Given that it appears to be the revision ID that's stored, then the max value of that column should be the string representation of the upper limit of the underlying VCS:
            Subversion (32-bit) - "2147483647" (10 char) (if unsigned 4294967295, also 10 char)
            Subversion (64-bit) - "9223372036854775807" (19 char) (if unsigned 18446744073709551615, 20 char)
            Git/Mercurial (SHA-1) - 160 bit output represented by 40 char string (excl spaces)

            I could be completely wrong here, but it appears the COMMIT_REVISION column could be VARCHAR(40) instead of VARCHAR(4000).
            It probably doesn't need to be changed for the index anyway, as we're still within the 255-char limit imposed by the index.

            David Corley added a comment - Also FWIW, I'm not sure why the value of 4000 was set? Given that it appears to be the revision ID that's stored, then the max value of that column should be the string representation of the upper limit of the underlying VCS: Subversion (32-bit) - "2147483647" (10 char) (if unsigned 4294967295, also 10 char) Subversion (64-bit) - "9223372036854775807" (19 char) (if unsigned 18446744073709551615, 20 char) Git/Mercurial (SHA-1) - 160 bit output represented by 40 char string (excl spaces) I could be completely wrong here, but it appears the COMMIT_REVISION column could be VARCHAR(40) instead of VARCHAR(4000). It probably doesn't need to be changed for the index anyway, as we're still within the 255-char limit imposed by the index.

            I just opened BSP-6626 about this. I suspect you may not have fixed the issue completely.
            The original intent here was to create an index on the COMMIT_REVISION column in the USER_COMMIT table.
            However the following still occurs:

            • The schema update still throws the error above when upgrading from 3.3.3 -> 3.4.4.
            • Upgrade task 2806 carries out the conversion of column type from TEXT to VARCHAR
            • Upgrade task 2806 doesn't appear to try and create the index after the conversion.
              • This was verified in BSP-6626 by listing the indices that exist after the upgrade completes.

            I believe a new upgrade task need to be added to create the index that runs some time after 2806.
            Furthermore if the existing syntax for the index creation is used, a prefix of the default VARCHAR size of 255 will be used for the index, even though the column is specified by Bamboo to be VARCHAR(4000).
            You might want to consider reducing the VARCHAR column size based on the following from the MySQL documentation

            By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix. See Section 12.1.13, “CREATE INDEX Syntax”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a UTF-8 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, this length limit is raised to 3072 bytes, for InnoDB tables that use the DYNAMIC and COMPRESSED row formats.When you attempt to specify an index prefix length longer than allowed, the length is silently reduced to the maximum length. This configuration option changes the error handling for some combinations of row format and prefix length longer than the maximum allowed. See innodb_large_prefix for details.

            David Corley added a comment - I just opened BSP-6626 about this. I suspect you may not have fixed the issue completely. The original intent here was to create an index on the COMMIT_REVISION column in the USER_COMMIT table. However the following still occurs: The schema update still throws the error above when upgrading from 3.3.3 -> 3.4.4. Upgrade task 2806 carries out the conversion of column type from TEXT to VARCHAR Upgrade task 2806 doesn't appear to try and create the index after the conversion. This was verified in BSP-6626 by listing the indices that exist after the upgrade completes. I believe a new upgrade task need to be added to create the index that runs some time after 2806. Furthermore if the existing syntax for the index creation is used, a prefix of the default VARCHAR size of 255 will be used for the index, even though the column is specified by Bamboo to be VARCHAR(4000). You might want to consider reducing the VARCHAR column size based on the following from the MySQL documentation By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix. See Section 12.1.13, “CREATE INDEX Syntax”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a UTF-8 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, this length limit is raised to 3072 bytes, for InnoDB tables that use the DYNAMIC and COMPRESSED row formats.When you attempt to specify an index prefix length longer than allowed, the length is silently reduced to the maximum length. This configuration option changes the error handling for some combinations of row format and prefix length longer than the maximum allowed. See innodb_large_prefix for details.

            migrate column type to VARCHAR + upgrade task
            database tests

            Marek Went (Inactive) added a comment - migrate column type to VARCHAR + upgrade task database tests

              Unassigned Unassigned
              akhachatryan ArmenA
              Affected customers:
              2 This affects my team
              Watchers:
              4 Start watching this issue

                Created:
                Updated:
                Resolved:

                  Estimated:
                  Original Estimate - 5m
                  5m
                  Remaining:
                  Remaining Estimate - 5m
                  5m
                  Logged:
                  Time Spent - Not Specified
                  Not Specified