4gb: doesn't hurt - requires more ram on db server [i.e. 1-2bytes/record] - although in some cases, data is stored within the table row up to a certain size, and then stored outside the row is a pointer after that.
 
Remember, we also agreed to move away from adodb and replace the db layer due to the problems it's given us. Hence writing a pdo replacement.
 
So now's a good time to work out what the correct schema should be, because it may be that the 'broken' adodb schema's we've generated over the years are incorrect anyway. [I know in the case of pgsql/mssql this was definitely the case - I thought that in the case of MySQL we were fairly good and hadn't be hit by any issues - you seem to be proving otherwise].
 
There are already several 'tidy up schema' updates in the 2.x branch, so adding a few more doesn't feel like an issue. I'd rather do that then have issues going forwards on old data sets due to some old adodb bug.


On Mon, Sep 23, 2013 at 9:30 AM, Damien Regad <dregad@mantisbt.org> wrote:
Thanks for your feedback.

You've missed what is probably the most important point

 >     - columns of VARCHAR type that are expected to be INTEGER (or some
 >     variant of it)

I take your points for the rest, and agree that in most cases the
differences have little consequence, but as we have standardized on a
tool to define and maintain the schema, then our own database should be
aligned to that definition.

As for allowing 4GB, true it's completely overkill but at the same time
it does not hurt, does it ?

 > ^ 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.

So you think this should be addressed as part of install.php ?

D


On 2013-09-16 22:16, Paul Richards wrote:
> 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.
>


------------------------------------------------------------------------------
LIMITED TIME SALE - Full Year of Microsoft Training For Just $49.99!
1,500+ hours of tutorials including VisualStudio 2012, Windows 8, SharePoint
2013, SQL 2012, MVC 4, more. BEST VALUE: New Multi-Library Power Pack includes
Mobile, Cloud, Java, and UX Design. Lowest price ever! Ends 9/20/13.
http://pubads.g.doubleclick.net/gampad/clk?id=58041151&iu=/4140/ostg.clktrk
_______________________________________________
mantisbt-dev mailing list
mantisbt-dev@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/mantisbt-dev