Get ORA-25263 while dequeue with message ID #201

eugene-vodyanko · 2021-12-03T09:02:45Z

Describe the bug
Get ORA-25263 (no message in queue) when try dequeue message with message ID dequeue option.

To Reproduce

package main

import (
	"context"
	"database/sql"
	"fmt"
	"github.com/godror/godror"
)

func main() {
	var (
		dbQueue = "TEST_EVENT"
	)

	db, err := sql.Open("godror", `user="user" password="password" connectString="db.db" libDir="D:/app/user/product/11.2.0/client_4"`)
	if err != nil {
		panic(err)
	}

	ctx := context.Background()
	err = transaction(ctx, db, dbQueue)
	if err != nil {
		panic(err)
	}
}

func transaction(ctx context.Context, db *sql.DB, dbQueue string) error {
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}

	q, err := godror.NewQueue(ctx, tx, dbQueue, "SYS.AQ$_JMS_MESSAGE")
	if err != nil {
		return err
	}

	opts, _ := q.DeqOptions()
	opts.Mode = godror.DeqBrowse
	opts.MsgID = "7BA3FC69817C6F60E0540208202FCAE4"
	err = q.SetDeqOptions(opts)

	if err != nil {
		return err
	}

	msgs := make([]godror.Message, 1)
	n, err := q.Dequeue(msgs)

	if err != nil {
		return err
	}

	for _, m := range msgs[:n] {
		fmt.Printf("Got message %+v\n", m.MsgID)

		err = m.Object.Close()
		if err != nil {
			return err
		}
	}

	return tx.Commit()
}

Expected behavior
Get existing message with same identifier.

Error output

panic: dequeue: ORA-25263 no message in queue TEST_EVENT with message ID
0000000000000000000000000000000000000000000000000000000043414534

goroutine 1 [running]:
main.main()
D:/work/go/app-go/cmd/app-go/main.go:23 +0x7f

Process finished with the exit code 2

Your oracle client version
11.2.0.4

Your godror version
1.14

Your go version
1.17

Machine (please complete the following information):

OS: win
Architecture: x86_64
Version: 10

Maybe, you have recommendations or instructions?

Thanks!

The text was updated successfully, but these errors were encountered:

sudarshan12s · 2021-12-03T12:30:39Z

Can you also share the enqueue code details to see if any setting could cause this issue ? ex: ``` inventoryqueue, err := godror.NewQueue ... sendmsg := godror.Message{Object: obj} sendmsg.Expiration = 10000 inventoryqueue.Enqueue(sendmsgs) ... ```

…

On Fri, Dec 3, 2021 at 2:32 PM eugene-vodyanko ***@***.***> wrote: *Describe the bug* Get ORA-25263 when try dequeue message with message ID dequeue option. *To Reproduce* package main import ( "context" "database/sql" "fmt" "github.com/godror/godror" ) func main() { var ( dbQueue = "TEST_EVENT" ) db, err := sql.Open("godror", `user="user" password="password" connectString="db.db" libDir="D:/app/user/product/11.2.0/client_4"`) if err != nil { panic(err) } ctx := context.Background() err = transaction(ctx, db, dbQueue) if err != nil { panic(err) } } func transaction(ctx context.Context, db *sql.DB, dbQueue string) error { tx, err := db.BeginTx(ctx, nil) if err != nil { return err } q, err := godror.NewQueue(ctx, tx, dbQueue, "SYS.AQ$_JMS_MESSAGE") if err != nil { return err } opts, _ := q.DeqOptions() opts.Mode = godror.DeqBrowse opts.MsgID = "7BA3FC69817C6F60E0540208202FCAE4" err = q.SetDeqOptions(opts) if err != nil { return err } msgs := make([]godror.Message, 1) n, err := q.Dequeue(msgs) if err != nil { return err } for _, m := range msgs[:n] { fmt.Printf("Got message %+v\n", m.MsgID) err = m.Object.Close() if err != nil { return err } } return tx.Commit() } *Expected behavior* Get existing message with same identifier. *Error output* panic: dequeue: ORA-25263 no message in queue TEST_EVENT with message ID 0000000000000000000000000000000000000000000000000000000043414534 goroutine 1 [running]: main.main() D:/work/go/app-go/cmd/app-go/main.go:23 +0x7f Process finished with the exit code 2 *Your oracle client version* 11.2.0.4 *Your godror version* 1.14 *Your go version* 1.17 *Machine (please complete the following information):* - OS: win - Architecture: x86_64 - Version: 10 Maybe, you have recommendations or instructions? Thanks! — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#201>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ALRPMOWQVJ5LMN5ZU46YLGDUPCBUFANCNFSM5JJFTDWA> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

eugene-vodyanko · 2021-12-03T12:50:40Z

Can you also share the enqueue code details to see if any setting
Hi!
The enqueue work is carried out by another application on PL/SQL via API by package DBMS_AQ (dbms_aq.enqueue).
If it helps I can give an example of this on Pl/SQL...

sudarshan12s · 2021-12-03T14:06:09Z

Yes it would help if you can share details or if it could be reproduced in standalone go example. ORA-25263 is mostly because the message is not present the queue with message id which is provided in dequeue options. Also are you able to read it from DB through sqlplus client (select query from user.queuetable)?

…

On Fri, Dec 3, 2021 at 6:20 PM eugene-vodyanko ***@***.***> wrote: *Can you also share the enqueue code details to see if any setting* Hi! The enqueue work is carried out by another application on PL/SQL via API by package DBMS_AQ (dbms_aq.enqueue). If it helps I can give an example of this on Pl/SQL... — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#201 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ALRPMOXYPRMSJ64B5BR4SELUPC4KZANCNFSM5JJFTDWA> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

eugene-vodyanko · 2021-12-03T14:39:43Z

Below is PL/SQL block for enqueue and "select 1" to verify the existence of:

declare
  jmsMessage sys.aq$_jms_message;
  msgId raw(16);

  function newJmsMessage return sys.aq$_jms_message
  is
  begin
    return sys.aq$_jms_message.construct(sys.dbms_aq.JMS_TEXT_MESSAGE);
  end; /* newJmsMessage */
  
  procedure enqueue(
    p_object in sys.aq$_jms_message
  , p_queue in varchar2 default 'TEST_EVENT'
  , p_correlation in varchar2 default null
  , r_msg_id out raw
  )
  is
    enqueue_options     dbms_aq.enqueue_options_t;
    message_properties  dbms_aq.message_properties_t;
    message_handle      raw(16);
  begin
    if p_correlation is not null then
      message_properties.correlation := p_correlation;
    end if;

    dbms_aq.enqueue(
        queue_name          => p_queue
      , enqueue_options     => enqueue_options
      , message_properties  => message_properties
      , payload             => p_object
      , msgid               => message_handle
    );

    r_msg_id := message_handle;
  end; /* enqueue */

  procedure dequeue(
    p_queue in varchar2 default 'TEST_EVENT'
  , r_object out nocopy sys.aq$_jms_message
  , p_msg_id in raw default null
  )
  is
    dequeue_options     dbms_aq.dequeue_options_t;
    message_properties  dbms_aq.message_properties_t;
    message_handle      raw(16);
  begin
    dequeue_options.navigation   := dbms_aq.NEXT_MESSAGE;
    dequeue_options.visibility   := dbms_aq.ON_COMMIT;
    dequeue_options.dequeue_mode := dbms_aq.BROWSE;

    if p_msg_id is not null then
      dequeue_options.msgId := p_msg_id;
    end if;

    dbms_aq.dequeue(
      queue_name          => p_queue
    , dequeue_options     => dequeue_options
    , message_properties  => message_properties
    , payload             => r_object
    , msgid               => message_handle
    );

  end; /* dequeue */
begin
  -- Enqueue
  jmsMessage := newJmsMessage();
  enqueue(p_object => jmsMessage, r_msg_id => msgId);
  dbms_output.put_line('msgId: '||msgId);
  
  -- Dequeue  
  /*
  dequeue(r_object => jmsMessage, p_msg_id => msgId);
  */

end;

commit
/

-- Select row with msgid: 'D23FA1B22FB936ACE05400144FFA9B52'
select 1 from jms_test_event tab
where tab.msgid = hextoraw('D23FA1B22FB936ACE05400144FFA9B52')
/

But in go program:

Set opts.MsgID:  D23FA1B22FB936ACE05400144FFA9B52
panic: dequeue: ORA-25263: в очередиTEST_EVENT нет сообщений с идентификатором 0000000000000000000000000000000000000000000000000000000039423532

goroutine 1 [running]:
main.main()
	D:/work/go/app-go/cmd/app-go/main.go:23 +0x7f

Process finished with the exit code 2

I modestly assume that the problem is in the area of converting a string to RAW...
Perhaps the message identifier in Go should be represented in symbolic (string) form differently than in HEX?

tgulacsi · 2021-12-04T08:17:51Z

I don't know - ODPI-C says only

value [IN] – a pointer to the bytes making up the message identifier, or NULL if no specific message is to be dequeued.

which does not say whether this is a RAW or sth else.

I've tried opts.MsgID = hex.DecodeString(), without success - see 6d3a9b3 TestQueue/notexist

tgulacsi · 2021-12-04T10:09:00Z

96b2c5e says "ORA-25263: no message in queue TEST.TEST_Q wit
h message ID 00 (dpiQueue_deqOne / dequeue message)" (the same), though I Set & Get & Compare deqOptions.MsgID, and ODPI-C seems to set/get the proper ATTR, so I don't know.

eugene-vodyanko · 2021-12-04T10:13:33Z

Thanks for your answers.
I understand correctly that the error may be on the side ODPI-C?

tgulacsi · 2021-12-04T10:16:39Z

I don't know.
I see no smoking gun anywhere.
Maybe the use of OCI in ODPI is wrong, but I don't know.

Without msgID, the dequeueing works.

eugene-vodyanko · 2021-12-04T10:24:09Z

Nevertheless, thank you for your time.

P.S. This problem is a bit upsetting. Since this is a fairly common way of working with a queue - when we read messages in a selector with a special selection condition without deleting and then we send a message with a specific identifier for processing.
It is clear that this can be circumvented by stored procedures, it would be preferable to use a common consistent API (AQ).

sudarshan12s · 2021-12-07T18:14:07Z

Below is PL/SQL block for enqueue and "select 1" to verify the existence of:

declare
  jmsMessage sys.aq$_jms_message;
  msgId raw(16);

  function newJmsMessage return sys.aq$_jms_message
  is
  begin
    return sys.aq$_jms_message.construct(sys.dbms_aq.JMS_TEXT_MESSAGE);
  end; /* newJmsMessage */
  
  procedure enqueue(
    p_object in sys.aq$_jms_message
  , p_queue in varchar2 default 'TEST_EVENT'
  , p_correlation in varchar2 default null
  , r_msg_id out raw
  )
  is
    enqueue_options     dbms_aq.enqueue_options_t;
    message_properties  dbms_aq.message_properties_t;
    message_handle      raw(16);
  begin
    if p_correlation is not null then
      message_properties.correlation := p_correlation;
    end if;

    dbms_aq.enqueue(
        queue_name          => p_queue
      , enqueue_options     => enqueue_options
      , message_properties  => message_properties
      , payload             => p_object
      , msgid               => message_handle
    );

    r_msg_id := message_handle;
  end; /* enqueue */

  procedure dequeue(
    p_queue in varchar2 default 'TEST_EVENT'
  , r_object out nocopy sys.aq$_jms_message
  , p_msg_id in raw default null
  )
  is
    dequeue_options     dbms_aq.dequeue_options_t;
    message_properties  dbms_aq.message_properties_t;
    message_handle      raw(16);
  begin
    dequeue_options.navigation   := dbms_aq.NEXT_MESSAGE;
    dequeue_options.visibility   := dbms_aq.ON_COMMIT;
    dequeue_options.dequeue_mode := dbms_aq.BROWSE;

    if p_msg_id is not null then
      dequeue_options.msgId := p_msg_id;
    end if;

    dbms_aq.dequeue(
      queue_name          => p_queue
    , dequeue_options     => dequeue_options
    , message_properties  => message_properties
    , payload             => r_object
    , msgid               => message_handle
    );

  end; /* dequeue */
begin
  -- Enqueue
  jmsMessage := newJmsMessage();
  enqueue(p_object => jmsMessage, r_msg_id => msgId);
  dbms_output.put_line('msgId: '||msgId);
  
  -- Dequeue  
  /*
  dequeue(r_object => jmsMessage, p_msg_id => msgId);
  */

end;

commit
/

-- Select row with msgid: 'D23FA1B22FB936ACE05400144FFA9B52'
select 1 from jms_test_event tab
where tab.msgid = hextoraw('D23FA1B22FB936ACE05400144FFA9B52')
/

But in go program:

Set opts.MsgID:  D23FA1B22FB936ACE05400144FFA9B52
panic: dequeue: ORA-25263: в очередиTEST_EVENT нет сообщений с идентификатором 0000000000000000000000000000000000000000000000000000000039423532

goroutine 1 [running]:
main.main()
	D:/work/go/app-go/cmd/app-go/main.go:23 +0x7f

Process finished with the exit code 2

I modestly assume that the problem is in the area of converting a string to RAW... Perhaps the message identifier in Go should be represented in symbolic (string) form differently than in HEX?

Thanks for sharing details. we are checking it.

tgulacsi pushed a commit that referenced this issue Dec 4, 2021

Add test for #201

6d3a9b3

eugene-vodyanko mentioned this issue Dec 7, 2021

Is there a reference example dequeue message with message ID dequeue option oracle/odpi#167

Closed

tgulacsi closed this as completed in 3d7b254 Jan 19, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Get ORA-25263 while dequeue with message ID #201

Get ORA-25263 while dequeue with message ID #201

eugene-vodyanko commented Dec 3, 2021 •

edited

sudarshan12s commented Dec 3, 2021 via email •

edited

eugene-vodyanko commented Dec 3, 2021

sudarshan12s commented Dec 3, 2021 via email •

edited

eugene-vodyanko commented Dec 3, 2021 •

edited

tgulacsi commented Dec 4, 2021

tgulacsi commented Dec 4, 2021

eugene-vodyanko commented Dec 4, 2021

tgulacsi commented Dec 4, 2021 •

edited

eugene-vodyanko commented Dec 4, 2021

sudarshan12s commented Dec 7, 2021

Get ORA-25263 while dequeue with message ID #201

Get ORA-25263 while dequeue with message ID #201

Comments

eugene-vodyanko commented Dec 3, 2021 • edited

sudarshan12s commented Dec 3, 2021 via email • edited

eugene-vodyanko commented Dec 3, 2021

sudarshan12s commented Dec 3, 2021 via email • edited

eugene-vodyanko commented Dec 3, 2021 • edited

tgulacsi commented Dec 4, 2021

tgulacsi commented Dec 4, 2021

eugene-vodyanko commented Dec 4, 2021

tgulacsi commented Dec 4, 2021 • edited

eugene-vodyanko commented Dec 4, 2021

sudarshan12s commented Dec 7, 2021

eugene-vodyanko commented Dec 3, 2021 •

edited

sudarshan12s commented Dec 3, 2021 via email •

edited

sudarshan12s commented Dec 3, 2021 via email •

edited

eugene-vodyanko commented Dec 3, 2021 •

edited

tgulacsi commented Dec 4, 2021 •

edited