421064 - [Oracle] whine.pl crashes and doesn't work (fix sql_interval and DB::Oracle::st inheritance)

Status: RESOLVED FIXED

(Bugzilla :: Database, defect)

Description

[Heiko Seitz]

User-Agent:       Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.1.12) Gecko/20080203 SUSE/2.0.0.12-0.1 Firefox/2.0.0.12
Build Identifier: 

My company plans to setup a new bug tracker. I've downloaded and installed the latest development version 3.1.3 to do some testing concerning the new support of Oracle databases. Everything was set up in a virtual machine running Novell openSUSE 10.3 with Oracle 9 client installed. The database runs on an Oracle 10 server on a different machine. I added the scripts to crontab according to the information given in the official installation guide (PDF). But the behaviour can be reproduced by calling the from the shell.

collectstats.pl seems to have a problem with an operator:

DBD::Oracle::db selectall_hashref failed: ORA-00920: invalid relational operator (DBD ERROR: error possibly near <*> indicator at char 90 in 'SELECT series_id, query, creator FROM series WHERE frequency != 0 AND (13943 + series_id) <*>% frequency = 0') [for Statement "SELECT series_id, query, creator FROM series WHERE frequency != 0 AND (13943 + series_id) % frequency = 0"] at Bugzilla/DB/Oracle.pm line 386
        Bugzilla::DB::Oracle::selectall_hashref('undef', 'SELECT series_id, query, creator FROM series WHERE frequency ...', 'series_id') called at ./collectstats.pl line 561
        main::CollectSeriesData() called at ./collectstats.pl line 131

whine.pl simply terminates with a segmentation fault. Couldn't find any hints or details so far.

Both scripts run smoothly on the original mySQL database (which was copied by contrib/bzdbcopy.pl into Oracle), so both problems seem Oracle related to me.

Here some additional Information about the environment of the Bugzilla installation:

* This is Bugzilla 3.1.3 on perl 5.8.8
* Running on Linux 2.6.22.17-0.1-default #1 SMP 2008/02/10 20:01:04 UTC

Checking for                 DBI (v1.41)   ok: found v1.58
Checking for           DBD-mysql (v4.00)   ok: found v4.006
Checking for          DBD-Oracle (v1.19)   ok: found v1.20
Checking for           Oracle (v10.01.0)   ok: found v10.02.0300


Reproducible: Always

Steps to Reproduce:
1.
2.
3.

Comment 1

[timeless]

http://www.kasabsoftware.com.au/databases/cheatSheet2.php?pg=200302

Function  	Modulus 	
MySQL  		Uses modulus operator (%) or MOD() function
Oracle  	Uses MOD() function
SQL Server  	Uses modulus operator (%)
Microsoft Access	Uses mod operator

I'm not sure we guaranteed this would work in that release. But it's definitely buggy and needs to be fixed.

Comment 2

[Frédéric Buclin]

    # (days_since_epoch + series_id) % frequency = 0. So they'll run every
    # <frequency> days, but the start date depends on the series_id.

Modulo is only used here to randomize the list of queries to run. We could easily filter them using Perl, i.e. get them all, and then grep() the list.

Comment 3

[Xiaoou]

For collectstats.pl:
    Since all DBs support the MOD function,  we'd better change this '%' 
    to MOD instead.

For whine.pl:

    This bug is caused by the Oracle DBI, which doesn't know how to bind_param
    with the sql_interval with placeholders. Shall we change all these
    sql_interval()s to no use placeholder?

Comment 4

[Max Kanat-Alexander]

(In reply to comment #3)
>     This bug is caused by the Oracle DBI, which doesn't know how to bind_param
>     with the sql_interval with placeholders. Shall we change all these
>     sql_interval()s to no use placeholder?

  No. What do you mean the Oracle DBD doesn't know how to bind_param with sql_interval? Is there some bug in the DBD, or is it a bug in our implementation?

Comment 5

[Xiaoou]

>   No. What do you mean the Oracle DBD doesn't know how to bind_param with
> sql_interval? Is there some bug in the DBD, or is it a bug in our
> implementation?

For example:

 $dbh->selectrow_array("SELECT NOW() + INTERVAL ? MINUTE FROM  
                            DUAL",undef,8);

will not be correctly executed, because Oracle DBD will change it to
"SELECT NOW() + INTERVAL 8 MINUTE FROM DUAL;", but not 
"SELECT NOW() + INTERVAL '8' MINUTE FROM DUAL;"

 

Comment 6

[Max Kanat-Alexander]

(In reply to comment #5)
> "SELECT NOW() + INTERVAL 8 MINUTE FROM DUAL;", but not 
> "SELECT NOW() + INTERVAL '8' MINUTE FROM DUAL;"

  That sounds like a bug in the DBD to me--all the other DBDs handle it correctly.

Comment 7

[Xiaoou]

whine.pl:
......
sub get_next_event {
    my $event = {};

    # Loop until there's something to return
    until (scalar keys %{$event}) {
        $dbh->bz_start_transaction();

        # Get the event ID for the first pending schedule
        $sth_next_scheduled_event->execute;
        my $fetched = $sth_next_scheduled_event->fetch;
        $sth_next_scheduled_event->finish;
        return undef unless $fetched;  <----
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
        Why it always "Segmentation fault" here 
        when I run whine.pl by ./whine.pl? I ran this code 
        in a test program, and it was fine.

btw, I've scheduled a search with Whining, and it can send me mails 

Comment 8

[Xiaoou]

This "Segmentation fault" is more like a warning that can be ignored, because it happens when(after) the whine.pl finish

Comment 9

[Xiaoou]

Attachment: v1

Comment 10

[Max Kanat-Alexander]

Comment on attachment 322525 [details] [diff] [review]
v1

Hmm, for the whine thing would it help if you did a cast on the units? Maybe that would help DBD::Oracle (or Oracle) figure it out?

Also, split out the collectstats.pl bit into another bug, it's much simpler and can probably just go in by itself.

Comment 11

[Xiaoou]

Attachment: v2

>>>Hmm, for the whine thing would it help if you did a cast on the units? Maybe
>>>that would help DBD::Oracle (or Oracle) figure it out?
1. Yeah, DBD::Oracle can't know how to bind param with the INTERVAL things, but it know how to bind param with function, and this cast will work.
2. The crash thing is caused by using the wrong base class.

Comment 12

[Max Kanat-Alexander]

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

Yes, but isn't there a simpler CAST function or something in Oracle that would allow us to not have to write that function?

Comment 13

[Xiaoou]

Maybe no, and it is not the cast thing, the functions added return a date format result instead of using "INTERVAL" directly.

Comment 14

[Max Kanat-Alexander]

(In reply to comment #13)
> Maybe no, and it is not the cast thing, the functions added return a date
> format result instead of using "INTERVAL" directly.

  Just do "CAST $units AS NUMBER" is what I'm saying. That can convert a varchar. That seems a lot simpler.

Comment 15

[Xiaoou]

Attachment: v3

You are so /strong ! :-)
Two self functions help with this.

Comment 16

[Max Kanat-Alexander]

Comment on attachment 327063 [details] [diff] [review]
v3

Hmm, I'm concerned about the February 29 bug with this:

http://www.orafaq.com/maillist/oracle-l/2007/08/30/0949.htm

Comment 17

[Max Kanat-Alexander]

Comment on attachment 327063 [details] [diff] [review]
v3

Yeah, set your system date to June 29 and then try:

ELECT NOW() - NUMTOYMINTERVAL(16, 'MONTH') FROM DUAL;

And you'll get:

ORA-01839: date not valid for month specified

Comment 18

[Max Kanat-Alexander]

Comment on attachment 327063 [details] [diff] [review]
v3

Okay, I don't see any way around the February 29 bug, so we'll just have to live with this.

I think DB::Oracle::st should probably be doing some fancy DBI inheritance thing instead of inheriting from DBI::st, but if this works, I'm okay with it for now. The only thing that's actually in DBD::Oracle::st that I know if is execute_for_fetch and some Oraperl compatibility functions in the XS.

Comment 19

[Max Kanat-Alexander]

tip:

Checking in Bugzilla/DB/Oracle.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Oracle.pm,v  <--  Oracle.pm
new revision: 1.9; previous revision: 1.8
done

3.2:

Checking in Bugzilla/DB/Oracle.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Oracle.pm,v  <--  Oracle.pm
new revision: 1.6.2.1; previous revision: 1.6
done