450379 - Searching SQL for candidates to truncate short_desc (> 255 chars) should use CHAR_LENGTH

Status: RESOLVED FIXED

(Bugzilla :: Database, defect)

Description

[A. Shimono [:himorin]]

Attachment: patch v.1

On >= 3.1.x, Bugzilla limits its bugs.short_desc up to 255 charactors.
checksetup.pl checks this limit with Bugzilla/Install/DB.pm, but this modules uses LENGTH instead of CHAR_LENGTH in the SQL.

cf. On our test server,,
+--------+--------------------+-------------------------+
| bug_id | length(short_desc) | char_length(short_desc) |
+--------+--------------------+-------------------------+
|    395 |                342 |                     146 |
|   3761 |                301 |                     129 |
|   3848 |                286 |                     136 |

Comment 1

[Max Kanat-Alexander]

Comment on attachment 333527 [details] [diff] [review]
patch v.1

Is CHAR_LENGTH an ANSI-standard function that works on all the DBs we support?

Also, LENGTH actually should work correctly if the DB has been converted to UTF-8, which it should have been before this point.

Comment 2

[Xiaoou]

No need for Oracle. because oracle fields are in *CHAR* when using utf8.

Comment 3

[A. Shimono [:himorin]]

(In reply to comment #1)
> Is CHAR_LENGTH an ANSI-standard function that works on all the DBs we support?

MySQL and PostgreSQL, yes. I don't know about Oracle.
http://dev.mysql.com/doc/refman/4.1/en/string-functions.html
http://www.postgresql.org/docs/7.4/interactive/functions-string.html

CHAR_LENGTH might be SQL-92 and -99 standard.

> Also, LENGTH actually should work correctly if the DB has been converted to
> UTF-8, which it should have been before this point.

For MySQL, no.
LENGTH() : Return the length of a string in bytes

Comment 4

[Xiaoou]

If there is no other way to solve it, Oracle can create a function CHAR_LENGTH as synonym of LENGTH.

Also, according to rfc2822 http://tools.ietf.org/html/rfc2822#section-2.1.1, the mail subject length is limited to "SHOULD be no more than 78 characters".
So, is this 255 limit of short_desc to big?

Comment 5

[A. Shimono [:himorin]]

> Also, according to rfc2822 http://tools.ietf.org/html/rfc2822#section-2.1.1,
> the mail subject length is limited to "SHOULD be no more than 78 characters".
> So, is this 255 limit of short_desc to big?

You can insert "<CR><LF><SP>" or "<CR><LF><TAB>" to make new lines in Subject: header.
If cannot, the limit for b64-ed 3-byte UTF-8 might be 14 chars or so.
# (78 - 'Subject: ' - '=?UTF-8?B?' - '?=') / 4

Comment 6

[Max Kanat-Alexander]

Okay, CHAR_LENGTH is in the SQL standard, so we should use it. We should also be using it in _check_content length.

xiaoou--we'll need an alias for Oracle.

Comment 7

[Xiaoou]

Attachment: CHAR_LENGTH for Oracle

Create function CHAR_LENGTH for Oracle.

Comment 8

[A. Shimono [:himorin]]

(In reply to comment #6)
> Okay, CHAR_LENGTH is in the SQL standard, so we should use it. We should also
> be using it in _check_content length.

Yeah, i have the same problem on the short_desc (?) of attachments.
But i'm not sure we can *simply* replace it in _check_content, b/c i've not checked from where the method is used whole the code.

Comment 9

[Max Kanat-Alexander]

(In reply to comment #8)
> Yeah, i have the same problem on the short_desc (?) of attachments.
> But i'm not sure we can *simply* replace it in _check_content, b/c i've not
> checked from where the method is used whole the code.

  You can, it's safe.

Comment 10

[Max Kanat-Alexander]

Comment on attachment 333527 [details] [diff] [review]
patch v.1

Also needs to fix the other LENGTH() call.

Comment 11

[A. Shimono [:himorin]]

Attachment: patch v.2

revised by comment #10

Comment 12

[Max Kanat-Alexander]

Comment on attachment 335653 [details] [diff] [review]
patch v.2

Looks good.

For what it's worth, only MySQL has this idiotic behavior.

Comment 13

[A. Shimono [:himorin]]

Attachment: merged two r+ patch (final, carrying forward r+)

merge attachment 335006 [details] [diff] [review] and attachment 335653 [details] [diff] [review]
(only merging, so carrying forward r+)

Comment 14

[Frédéric Buclin]

tip:

Checking in Bugzilla/DB/Oracle.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Oracle.pm,v  <--  Oracle.pm
new revision: 1.15; previous revision: 1.14
done
Checking in Bugzilla/Install/DB.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/Install/DB.pm,v  <--  DB.pm
new revision: 1.55; previous revision: 1.54
done


3.2rc1:

Checking in Bugzilla/DB/Oracle.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Oracle.pm,v  <--  Oracle.pm
new revision: 1.6.2.6; previous revision: 1.6.2.5
done
Checking in Bugzilla/Install/DB.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/Install/DB.pm,v  <--  DB.pm
new revision: 1.51.2.2; previous revision: 1.51.2.1
done