The osmajor_install_options table was introduced in Beaker 0.11, and it has never had a UNIQUE constraint covering (osmajor_id, arch_id) although the code assumes that there is only one row per OS major-arch combination. The problem is that arch_id is NULLable so a UNIQUE constraint on (osmajor_id, arch_id) would not have the desired effect.

Up until Beaker 0.15, the lazy_create method assumed a UNIQUE constraint existed on whatever columns were being used to create the row, in this case osmajor_id and arch_id. (In Beaker 0.15 we changed lazy_create to the conditional insert approach we have now, which doesn't rely on a UNIQUE constraint.)

So in the period of Beaker 0.11 to 0.15, editing OS major install options would create duplicate osmajor_install_options rows.

Steps to reproduce:
1. Install Beaker 0.14.4 or run from a git checkout.
2. If necessary, import a distro or manually create an OS version for testing.
insert into osmajor (osmajor) values ('RedHatEnterpriseLinux6');
insert into osversion (osmajor_id, osminor) select id, '0' from osmajor;
3. In Beaker's web UI, log in as admin and select Admin -> OS Versions from the menu.
4. Click an item in the OS Major column to edit its install options.
5. Enter "testone" into kernel options and click Save Changes.
6. Change kernel options to "testone testtwo" and click Save Changes.

Actual results:
mysql> select * from osmajor_install_options;
+----+------------+---------+---------+-----------------+---------------------+
| id | osmajor_id | arch_id | ks_meta | kernel_options  | kernel_options_post |
+----+------------+---------+---------+-----------------+---------------------+
|  1 |          1 |    NULL |         | testone         |                     |
|  2 |          1 |    NULL |         | testone testtwo |                     |
+----+------------+---------+---------+-----------------+---------------------+
2 rows in set (0.00 sec)

Expected results:
+----+------------+---------+---------+-----------------+---------------------+
| id | osmajor_id | arch_id | ks_meta | kernel_options  | kernel_options_post |
+----+------------+---------+---------+-----------------+---------------------+
|  2 |          1 |    NULL |         | testone testtwo |                     |
+----+------------+---------+---------+-----------------+---------------------+

Given that the duplicate rows are not being created anymore (since Beaker 0.15 fixed lazy_create not to rely on a UNIQUE constraint) all we need to do at this point is craft a SQL migration to clean up old duplicate rows which were left behind by old versions.

The only problem is, which row is the right one to keep in case of duplicates...

Ideally we want to preserve the row which contains the install options that Beaker is actually using, since that will be the least surprising to Beaker admins (it won't suddenly change the effective install options for jobs).

The scheduler looks up install options using OSMajor.install_options_by_arch which is an attribute-mapped collection, mapped on arch. SQLAlchemy populates it by querying osmajor_install_options, with no specific ORDER BY clause. In my testing, it appears to take the value from the *last* row in the result set in case of duplicates.

Strictly speaking, the database can be returning the osmajor_install_options rows in arbitrary order because there is no ORDER BY clause, but realistically in this case MySQL will be returning the most recently inserted row, that is with the highest id. So we will need to preserve that one and delete earlier ones in the cleanup script.

http://gerrit.beaker-project.org/4361

The SQL migration to fix this issue is included in beaker-server-22.0-0.git.23.f619759 which is currently available for download here:

https://beaker-project.org/nightlies/develop/

This fix was verified using the steps in comment 2 in a throwaway environment. Also the patch includes an accurate reproducer in the test suite.

Beaker 22.0 has been released.