300452 - checksetup.pl should have a way to check real DB schema vs desired one (not only against bz_schema)

Status: NEW

(Bugzilla :: Installation & Upgrading, enhancement)

Description

[u197037]

User-Agent:       Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8b) Gecko/20050217
Build Identifier: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8b) Gecko/20050217

Currently checksetup.pl rely on bz_schema table when check DB schema
consistence. This should be re-worked to allow check real DB schema against
desired (and possibly eliminate ba_schema table at all).
This should be done as overridable, per-DB mechanism, somewhat like DB access
routines are currently done.

Reproducible: Always

Steps to Reproduce:
1. Modify manually any column definition in any table
2. run checksetup.pl
3. Column neither won't change, nor even reported as not schema consistent

Actual Results:  
nothing, any comments from checksetup.pl

Expected Results:  
DB column should be changed according to schema definition, or, at least,
mentioned as non consistent

Comment 1

[Max Kanat-Alexander]

bz_schema cannot be eliminated. Perhaps you do not understand how cross-DB
checksetup works. Read bug 285111 for details.

Comment 2

[u197037]

Removing bug dependencies as agreed with Max (mkanat@)

Comment 3

[A. Karl Kornel]

Suggest either WORKSFORME or DUPLICATE of bug 291548.

It sounds to me like the reporter wants a way to let Bugzilla know about DB
changes made as part of (insert feature or patch here).  That sounds like bug
291548 to me.

Comment 4

[Max Kanat-Alexander]

No, not exactly. What he wants to do is something that I also wanted to have
checksetup do. It's EXTREMELY non-trivial for databases other than MySQL, though.

Comment 5

[u197037]

Also, you might find these packages useful (not tested, I've done just CPAN search):
http://search.cpan.org/~brian/RDBAL-1.2/RDBAL/Schema.pm
http://search.cpan.org/~kclark/SQL-Translator-0.07/lib/SQL/Translator/Schema.pm

Comment 6

[Tim Abell]

this would have saved me some considerable head scratching.

a few times when i've upgraded bugzilla (with no customisations to the schema) I've ended up with incorrect data types in the upgraded schema. (eg char(0) where it should be char(1).) I have no idea why, but having checksetup notice the mismatch would have been very helpful. Particularly of the symptoms of the incorrect schema tend to be features failing to work but producing no error.

the latest was that for some reason flagtypes.status ended up as char(0), meaning that i could add a flag but it wouldn't show in the ui due to missing the type 'b' in the database.

i've voted for this but it's beyond me to produce a patch at the moment i'm afraid (much as i'd like to).

Comment 7

[Mark Clements]

I also have just been bitten by this, and spent about half an hour trying to figure out why checksetup.pl was failing with an error when installing the AgileTools extension:

  Adding new column 'committed' to the 'agile_sprint' table...
  DBD::mysql::db do failed: Table 'db_mdb_bugzilla.agile_sprint' doesn't exist 

It turns out that the reason for this is that I installed an old version of the extension some time ago, and then uninstalled it, manually deleting the tables.

When I came to reinstall it, check_setup.pl thinks the tables still exist due to the bz_schema table, and then tries to add the new columns that have been added since I originally installed it, with the above error.

Basically, as far as I can see, checksetup.pl should always rebuild the bz_schema table otherwise things break, and in very non-obvious ways.