292718 - LOCATE is not ANSI SQL (prevents user account creation)

Status: RESOLVED FIXED

(Bugzilla :: Administration, task, P1)

Description

[Olivier Sirven]

User-Agent:       Mozilla/5.0 (X11; U; Linux i686; fr; rv:1.7.6) Gecko/20050406 Firefox/1.0.2 (Debian package 1.0.2-3)
Build Identifier: Mozilla/5.0 (X11; U; Linux i686; fr; rv:1.7.6) Gecko/20050406 Firefox/1.0.2 (Debian package 1.0.2-3)

A query crash the process while creating a new user if the edituser.cgi script.

Reproducible: Always

Steps to Reproduce:
1. Select "add a user" in the administration of bugzilla
2. Fill-in the form and leave "Disable text" field blank
3. Submit the form

Actual Results:  
Bugzilla crashes with following message:

DBD::Pg::st execute failed: ERROR:  function locate("unknown", character
varying) does not exist
HINT:  No function matches the given name and argument types. You may need to
add explicit type casts.
 [for Statement "SELECT eventdata FROM tokens WHERE tokentype = 'emailold'
        AND SUBSTRING(eventdata, 1, (LOCATE(':', eventdata) - 1)) = ?
        OR SUBSTRING(eventdata, (LOCATE(':', eventdata) + 1)) = ?" with
ParamValues: 2='[e-mail]', 1='[e-mail]'] at
Bugzilla/User.pm line 1142
	Bugzilla::User::is_available_username('[e-mail]') called at
/home/httpd/htdocs/bugzilla/editusers.cgi line 175

Comment 1

[Max Kanat-Alexander]

Hrm, I wonder if that's specific to 8.0, or if LOCATE is not ANSI.

Comment 2

[Max Kanat-Alexander]

OK, yeah, we should be using POSITION (or really, sql_position, I think) instead
of LOCATE. That's a pretty easy fix.

Comment 3

[Max Kanat-Alexander]

Attachment: Fix User Account Creation

OK, there was one other problem, which was that refreshed_when is NOT NULL and
was missing from the INSERT.

Comment 4

[Olivier Sirven]

(In reply to comment #3)
> Created an attachment (id=182830) [edit]
> Fix User Account Creation
> 
> OK, there was one other problem, which was that refreshed_when is NOT NULL and
> was missing from the INSERT.

I have tried your patch and it works just fine for me.

Comment 5

[Joel Peshkin]

Comment on attachment 182830 [details] [diff] [review]
Fix User Account Creation

refreshed_when should not be now() unless you derived groups.
Users should start out in need of refresh.

Comment 6

[Max Kanat-Alexander]

Attachment: v2

OK, I changed it to be January, 1901 by default.

In another patch, we should probably give refreshed_when a default value. I
think that would be a cleaner long-term solution. (Something for 2.22.)

Comment 7

[Joel Peshkin]

Comment on attachment 183786 [details] [diff] [review]
v2

r=joel if you've tested it
It does seem like there should have been a simpler way to do this in the first
place.

Comment 8

[Max Kanat-Alexander]

Yep, I just tested it, and it works. :-)

Comment 9

[Max Kanat-Alexander]

Checking in Bugzilla/User.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/User.pm,v  <--  User.pm
new revision: 1.55; previous revision: 1.54
done

Comment 10

[Max Kanat-Alexander]

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

Comment 11

[Max Kanat-Alexander]

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