Re: [mantisbt-dev] DB schema on mantisbt.org/bugs not standard 1.2

[Paul R. <pa...@ma...>]

Right,

The number(7) vs number(10) is caused the our migration to adodb.

Basically, historically we set INT(7) as data type, with an auto/zero fill
so the number returned from db was 000023 or similar. When we then looked
at other databases, that filling was moved into the PHP code I believe.

At the same time, with the move to adodb, we stopped specifying the length
field e.g. (7)/(10), hence the difference.

In terms of mysql, the (7)/(10) represents the display width of a column if
zero fill is enabled, and does not alter the size of the data field - it's
4 bytes in both case. Therefore this doesn't actually make any difference,
and we can just ignore

In terms of the text vs longtext, I'm half inclined to think that our
database ( the non-12 one) probably has it correct for some fields at least:

a) text = 75KB
b) mediumtext = 16MB
c) longtext = 4GB

Allowing 4GB for a token for instance seems rather silly. For a bug
description, I could imagine you *could* hit the 75KB limit, but....

For the remaining two issues:

- incorrect default values
^ I'm inclined to look back at this with a view to adding a database check
for mysql only to fix this on old datasets. As we can probably add a fix
for this fairly easily.

- missing unique index on mantis_user_table.username
^ I'd say that's worth manually running against the database - the index
should be there as you say, and that's in the script. If I had to make a
guess, there is/was a duplicate username, therefore the index part of the
upgrade wouldn't apply, and instead of someone fixing that, they've just
skipped the index.

Paul


>  - however, I'd be inclined
>> to be against manually running a script. Our bug tracker is oldest
>> tracker we have that's been updated through all releases.. Therefore, if
>>
>> that doesn't match, we need to investigate why for each instance where
>> it is different.
>>
>
> Fair enough, however that is not a justification for keeping a potentially
> incorrect schema around. We can always keep a backup of the current
> database for historical purposes before the fix.
>
> I was not around in the pre 1.0 days, but I believe upgrades were executed
> by manually running SQL scripts. I would therefore argue that differences
> such as:
>
> - columns of VARCHAR type that are expected to be INTEGER (or some variant
> of it)
> - columns of smaller size, e.g. number(7) vs number(10), text vs longtext
> - incorrect default values
> - missing unique index on mantis_user_table.username
>
> are the result of missing, wrong or improperly executed upgrade steps,
> which could potentially lead to bugs.
>
>