unit_create_new.sql failed: error=Error 1062: Duplicate entry '32767' for key 'PRIMARY'

[kaihendry]

Looks like we have hit the smallint limit on Units in the dev environment.

[kaihendry]

https://github.com/unee-t/unit/blob/master/sql/unit_create_new.sql for reference on how we call the SQL procedure.

This is blocking Unit creation in the development environment, making @PicoCreator's UIlicious tests fail and @nbiton from further load testing / performance work.

[franck-boullier]

This is a NON trivial fix ---> BZ periodically restore the core DB schema for BZ to what it think should be. This happens each time the .checksetup.pl script is triggered.

There is a good chance that any change in the DB schema will be overridden at some unpredictable time in the future if we do not make sure that we understand how to make the .checksetup.pl script behave the way we want it to do (i.e keep the core BZ DB schema changes that we need to make).

Until this is taken care of we should delete some of the existing units in the DEV environment to make room for the automated Uilicious tests.

There is a script to remove a unit and all the objects associated to this unit. Input needed is the BZ unit ID and it will take care of the BZ side of things.

[franck-boullier]

@nbiton can you use the script to remove a unit and all the objects associated to this unit and cleanup some of the existing units in Unee-T so we have a spare 100 units that we could create as part of normal tests?

[nbiton]

@franck-boullier you suggest I do it one at a time to clean 100 units?

[kaihendry]

I removed some units, however it would appear there are ids at 32767 on tables flagtypes & groups.

[hendry@t480s db]$ echo "describe flagtypes;" | ./connect.sh 2>/dev/null | grep -m 1 id
id      smallint(6)     NO      PRI     NULL    auto_increment
[hendry@t480s db]$ echo "describe groups;" | ./connect.sh 2>/dev/null | grep -m 1 id
id      mediumint(9)    NO      PRI     NULL    auto_increment

It would appear flagtypes is full?

[dylanwh]

So we're going to make the flagtype id bigger in https://github.com/bugzilla/bugzilla/pull/75.

[dylanwh]

So assuming the code in bugzilla/bugzilla#75, here's what you need to know, operationally:

1. I'll make another new-style release, 5.0.6. It will actually include that as teh version number :-)
2. You run checksetup.pl against your database. Please do this against a dev or testing one
3. You'll want to note how long it runs. Schema changes like this can cause table locks. Though I bet it would happen pretty fast.
4. @kaihendry asked IRC what would happen if you then ran an older version of bugzilla after this change -- to be clear if you did that it wouldn't necessarily be harmful. If you ran the old version and ran checksetup it may revert the schema change which would be bad if you then had flagtypes outside of the bounds of the smallint type.

[franck-boullier]

Awesome! @dylanwh thanks

[kaihendry]

Merged @dylanwh's work in https://github.com/bugzilla/bugzilla/pull/75 and tested in {dev,demo}. We should not see this issue again. 🤞