9352 - bugs.version varchar(16) vs 64 and reserved word conflict

Status: RESOLVED FIXED

(Bugzilla :: Bugzilla-General, defect, P2)

Description

[agrundma]

I manage Bugzilla for a project and when we do a new build, I add a new version
into Bugzilla for that build.  This is done with a manual sql command ( insert
into versions (value, program) values ("build whatever", "product"); ). I
discovered that any version that is over 16 characters reaks havoc with the
database, as that version name becomes truncated on the Enter Bug and
individual bug pages, but is not truncated on the Query page.  Deleting the
offending entry from the versions table (and deleting versioncache) doesn't
work, and the Add Bug page still shows the truncated version name.

An indefinite text limit for the versions table (and product table which I
noticed suffered from this same bug (#3725)) would be nice.  An option on the
Edit Components page (limited to 16 chars) to add new versions (and products)
would also work.

Comment 1

[Terry Weissman]

*** Bug 23515 has been marked as a duplicate of this bug. ***

Comment 2

[Terry Weissman]

*** Bug 23414 has been marked as a duplicate of this bug. ***

Comment 3

[Matthew Miller]

I worked around this by changing a line in checksetup.pl from 

  version varchar(16) not null,

to

  version varchar(32) not null,

and added

ChangeFieldType('bugs', 'version','varchar(32) not null');

near the end.

Comment 4

[Terry Weissman]

[e-mail] is the new owner of Bugzilla and Bonsai.  (For details,
see my posting in netscape.public.mozilla.webtools,
news://news.mozilla.org/38F5D90D.F40E8C1A%40geocast.com .)

Comment 5

[Tara Hernandez]

this has gotten fixed somewhere along the way.  All my tables have varchar(64)

Comment 6

[Matthew Tuck [:CodeMachine]]

Is there somewhere I can see a long version?

Also, will Bugzilla now stuff up if versions exceed 64 characters, ie are checks
in place?

Comment 7

[Andrew Gorski]

*** Bug 36737 has been marked as a duplicate of this bug. ***

Comment 8

[Dave Miller [:justdave]]

In search of accurate queries....  (sorry for the spam)

Comment 9

[Gervase Markham [:gerv]]

Do we need do document the 64 char limit, or do people who use more than that 
deserve everything they get for excessive verbosity?

Gerv

Comment 10

[GavinS]

Could someone have another look at this one, because I don't think this is
actually fixed in 2.12.

The checksetup.pl in the 2.12 tarball I downloaded seems to have the fielddef of
version in the bugs table as a varchar(16), and I can't see anywhere where it is
later changed to be anything else.

Comment 11

[Dave Miller [:justdave]]

doh.  confirmed.

Comment 12

[GavinS]

Should bug#16436 be a dupe of this? (It also thinks this is fixed :(

Comment 13

[Dave Miller [:justdave]]

that one reported this, but per comments, they only fixed the product column.  
This one was supposedly for the version column.  This WAS fixed at some point.  I 
remember seeing code in there to change it to varchar(64) at some point.  I'm not 
sure how or why it got backed out.

Comment 14

[Dave Miller [:justdave]]

There's also a problem with the version column during shadow database 
synchronization and when restoring backups made with mysqldump, because version() 
is a function and the key definition for version looks like the version() 
function in newer versions of mySQL.  Since both of these bugs will require 
changing the version column definition, I'm just making it part of this bug 
instead of making a new bug.  No point in changing it twice.

> ERROR 1064 at line 11: You have an error in your SQL syntax near
> 'version(version),
>   KEY component(component),
>   KEY resolution(resolution),
>   KE' at line 35

Comment 15

[Dave Miller [:justdave]]

Hmmm....  I just did a mySQL dump of our database at syndicomm and imported it 
into the mySQL on my laptop (both are running 3.23.38) and it worked fine.  What 
was the difference?  I compared the mysql dump to an earlier dump I'd done before 
we'd upgraded syndicomm's mySQL (which did trip up the import on my laptop) and 
the difference is that the key names in the mysqldump generated by 3.23.38 have a 
space between the key name and the open-paren for the column name.  Which sounds 
like the problem was really a bug in mysqldump in earlier versions of mySQL which 
has since been fixed, rather than a reserved word conflict...

Comment 16

[Christian Reis]

Myk has pointed out that http://www.mysql.com/doc/R/e/Reserved_words.html does
not contain version, and that the space separating version from the parenthesis
suffices to quell the problem.

A longer-term change could be changing version to bugversion - does anyone deem
this necessary?

Comment 17

[Christian Reis]

Attachment: Minor patch implementing Matthew's fix with a 64-bit field

Comment 18

[Christian Reis]

Reporter: is 64 bits good enough for you?

Does anyone think it should be done differently?

Comment 19

[Myk Melez [:myk] [@mykmelez]]

http://www.mysql.com/doc/R/e/Reserved_words.html

This page shows that "version" is not a reserved word and explains that function
names can be used as column/index names so long as you do not put whitespace
between the function name and its parenthesized parameter list *when calling it
as a function*. Thus, when using the name to refer to the column/index, make
sure to put whitespace after it so it is interpreted correctly.

This is apparently what the newer version of mysqldump does that the earlier
version erroneously didn't do. Or perhaps using function names as column/index
names was illegal in earlier versions of mysql. In any case, we should either
find out which version of mysql fixed this problem and make it a requirement for
Bugzilla to be using that version or later, release note the problems, and/or
explicitly declare the name of the INDEX on the "version" field in the "bugs"
table, i.e.:

INDEX theversion (version)

Comment 20

[Tara Hernandez]

This is undoubtedly aggravating to some, but not security related.  Bumping to
2.16 (though I'm fixing the field limit to 64 in passing as that's a regression.

Comment 21

[Christian Reis]

Attachment: Small fix to stop "not null" rechanging

Comment 22

[Christian Reis]

I'm adding a small patch to ChangeFieldType that will quell it's desire to ALTER
the table every time it sees a field followed by "not null" in the new field.
This is triggered by this bug and is so small it might not qualify as a bug
itself. Looks average.

Without this patch checksetup will try and alter the table every run.

Comment 23

[Myk Melez [:myk] [@mykmelez]]

When I apply the latest patch and run checksetup.pl multiple times, the script
updates bugs.version every time I run it instead of just the first time.  Or, at
least, the following message appears every time:

-----
Updating field type version in table bugs ...
old: varchar(64)
new: varchar(64) not null
-----

Comment 24

[Christian Reis]

Attachment: Updated patch (last one was broken) for ChangeFieldType()

Comment 25

[Christian Reis]

Dumb detail left out from last patch, fixed in this one. Should run fine.

Comment 26

[Myk Melez [:myk] [@mykmelez]]

checksetup.pl works again after I installed the latest patch.  Note that the
first patch on this bug, the one that fixes the actual problem for which the bug
was filed, was checked into the tree by Tara on Saturday, so I am resolving this
bug FIXED and opening a new one for the problem this fix exposes.

Comment 27

[Dave Miller [:justdave]]

fixed in 2.14...

Comment 28

[Dave Miller [:justdave]]

*** Bug 61382 has been marked as a duplicate of this bug. ***

Comment 29

[Dave Miller [:justdave]]

*** Bug 43291 has been marked as a duplicate of this bug. ***

Comment 30

[Dave Miller [:justdave]]

*** Bug 88621 has been marked as a duplicate of this bug. ***

Comment 31

[Dave Miller [:justdave]]

Moving to Bugzilla product

Comment 32

[Dave Miller [:justdave]]

*** Bug 108901 has been marked as a duplicate of this bug. ***

Comment 33

[Jacob Steenhagen]

*** Bug 112168 has been marked as a duplicate of this bug. ***