Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only
Expand all | Collapse all

Odd -255 Errors

  • 1.  Odd -255 Errors

    Posted Thu June 16, 2022 09:51 AM

    All:

    While tracking down another issue, I noticed several sessions reporting error -255 (Not In Transaction). These seemed out of place, because the sessions I saw showing that error were not doing COMMIT or ROLLBACK, which as far as I can tell should be the only statements that would generate that error.

    I put a trap on -255 with onmode -I and got _hundreds_ of hits. I saw INSERT statements, UPDATE statements and even SELECT statements. Never a COMMIT or ROLLBACK.

    The tables in question don't have triggers on them, so there wouldn't be a COMMIT/ROLLBACK in there.So I'm stumped. I'm left to think that perhaps the engine is reporting the wrong error.

    Has anyone else seen this?

    We're on 14.10.FC7W1 on CentOS 7. I'm seeing the errors coming from both JDBC clients and legacy ODBC clients (the latter running a VERY old 32-bit SDK) alike.

    I'm gathering information to open a ticket but wanted to check to see if anyone else had encountered this.

    TIA:

    - TJG



    ------------------------------
    TOM GIRSCH
    ------------------------------

    #Informix


  • 2.  RE: Odd -255 Errors

    Posted Thu June 16, 2022 10:30 AM
    Agreed. You should only see a -255 error for the following statements:

    • COMMIT WORK
    • ROLLBACK WORK
    • SAVEPOINT
    • RELEASE SAVEPOINT
    • ROLLBACK WORK TO SAVEPOINT
    I cannot think of any others.

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: Odd -255 Errors

    Posted Thu June 16, 2022 10:32 AM
    Do you have logging enabled on the db? Maybe running in ANSI Mode?

    Marcus 





  • 4.  RE: Odd -255 Errors

    Posted Thu June 16, 2022 10:55 AM
    From what you are telling us, the application side is working correctly ?
    So the sqlerror would be somewhere in the background, maybe not respected
    or not even forwarded to the application.

    Did you try to switch on sqlexplain for a specific session
    onmode -Y <sessionId> 2 (or 1)
    Check the resulting sqlexplain out file what really happens. Any query done 
    by a trigger or whatever reason should be listed there.

    Maybe this can give you some hints.

    MARCUS HAARMANN







  • 5.  RE: Odd -255 Errors

    Posted Thu June 16, 2022 01:25 PM
    I actually dumped the contents of syssqltrace for each session and saw no clues there. I see the error codes listed there, so it seems like the error IS being reported. I think the back-end apps trap around errors and retry.





  • 6.  RE: Odd -255 Errors

    Posted Thu June 16, 2022 01:26 PM
    No ANSI mode here.

    name            aeglobal
    partnum         14681280
    owner           informix
    created         01/31/2017
    is_logging      1
    is_buff_log     0
    is_ansi         0
    is_nls          0
    is_case_insens  0
    flags           -12287






  • 7.  RE: Odd -255 Errors

    Posted Fri June 17, 2022 04:15 AM
    This might well be some internal 'commit' being executed 'just in case', with its success, or any error, being ignored on purpose, so nothing related to the success of an actual statement, and nothing reported back to the client.
    Whether such error code should make it into sql traces, or if this could be avoided in all cases, might be an interesting question, but as long as such error doesn't reach the application, I'd not consider it as reported.  Also in onstat (-g ses <sid>) it's not uncommon to see SQL/ISAM codes that nobody else would notice, and there's a lot of such just-in-case operations as well as deliberate testing for certain conditions, by means of provoking an error, internally that also would never reach any surface ... unless someone starts trapping these errors, getting a lot of false positives and ending up more confused than (s)he was.

    ------------------------------
    Andreas Legner
    ------------------------------



  • 8.  RE: Odd -255 Errors

    Posted Fri June 17, 2022 04:44 AM
    I have encountered the same issue with the DBI perl driver a couple of years back. When it established a connection, it would do a query to retrieve some server info and at the end, it would always issue a "ROLLBACK WORK" ( I was told it was because of ANSI databases ). If it got a -255, then it was not an ANSI database, and would proceed in accordance. The application never saw the -255 error.

    Luis Marques

    ------------------------------
    Luis Marques
    ------------------------------



  • 9.  RE: Odd -255 Errors

    Posted Fri June 17, 2022 07:06 AM
    Hmm, Luis may have it. You might just be seeing code that is using a COMMIT or ROLLBACK to determine whether or not the database is in ANSI mode or non-logged. In a non-logged database you would get -256 instead of -255 and no error in ANSI mode database. Better to just check the sqlca.sqlerrd fields after connecting, but it works.

    Art

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 10.  RE: Odd -255 Errors

    Posted Fri June 17, 2022 08:54 AM
    But in that case, wouldn't "ROLLBACK WORK" show as the current statement?





  • 11.  RE: Odd -255 Errors

    Posted Fri June 17, 2022 10:08 AM
    OH SURE! Make a reasonable deduction and burst all our bubbles! 

    B^}

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 12.  RE: Odd -255 Errors

    Posted Fri June 17, 2022 05:58 AM

    I found to run the dbexport dbname also show the "-255 - Not in transaction." error message when the database with log mode. My Informix version is 14.10.FC7W1 on AIX 7.2

    I also test the dbexport on 14.10.FC6 and 14.10.FC8 on AIX 7.2. It is not show the "-255 - Not in transaction." error message.



    ------------------------------
    Philip Yeh
    ------------------------------



  • 13.  RE: Odd -255 Errors

    Posted Tue June 21, 2022 09:33 PM
    As far as I know, the problem only happens in 14.10.FC7W1, it does not reproduce in 14.10.FC8 and earlier versions.

    Most likely a cosmetic error, should not affect the dbexport output.

    IT40507: DBEXPORT ENDS WITH -255 WHEN EXPORTING DATABASE WITH LOGGING

    ------------------------------
    KHON CHIEK TAN
    ------------------------------