Smallint(6) number are limiting us and causing errors in Unee-T

[franck-boullier]

The problem:

The BZ native schema uses the following formats for keys:

• SMALLINT(6)
• MEDIUMINT(9)
• INT(11)

This creates a limit on the number of objects we can create: Maximum value for SMALLINT is 65535 (see https://stackoverflow.com/questions/1678296/whats-the-maximum-value-of-smallint6-unsigned-in-mysql) MySQL documentation specifies that "For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767" (see https://dev.mysql.com/doc/refman/5.6/en/numeric-type-attributes.html) This is because by default SMALLINT (and MEDIUMINT and INT) are set to Unsigned = false ---> it is possible to enter negative values.

IRL this creates some hard to fix issue like https://github.com/unee-t/lambda2sns/issues/9#issuecomment-478229369

The solution:

We need to:

• Make sure that we set Unsigned = true for all the fields that are SMALLINT(6), MEDIUMINT(9), INT(11). This will NOT be a problem since these keys are "Autoincremented" ---> not possible to have negative values.
• Convert the SMALLINT(6) to MEDIUMINT(9) for all the fields that are likely to store a high number of records (products, series, etc...)

[franck-boullier]

Next Steps:

• Check that all the UiLicious tests are passed as expected in the DEMO.
• If no issue: update the DB schema in the PROD environment to BZ Db schema v4.33
• Merge PR #122
• Close this issue

[kaihendry]

RE "IRL this creates some hard to fix issue like unee-t/lambda2sns#9 (comment)", the particular failure Error 1213: Deadlock found when trying to get lock; try restarting transaction actually comes from https://github.com/unee-t/unit via MEFE btw.

So perhaps we need some tests upon https://github.com/unee-t/unit to ensure units can be created without issue?

[franck-boullier]

Error 1213: Deadlock found when trying to get lock; try restarting transaction

My guess is that this is a timeout issue still: it take a looong time to create a unit on the BZ side (almost 20 seconds for each units based on anecdotal evidences from how fast the system replies). @kaihendry could it be that the lambda is not patient enough as it needs to wait for the lock to be released from the current unit creation before it can create another unit?

[franck-boullier]

DB schema updated to v4.33 in DEV/Staging, DEMO and PROD. This should fix the problem