Help with error messages
We wrote/support a .NET app that has been in use for ~6 years now. We use it in-house against our 11.1FC3/RHEL 5 database and is our primary application w/~60 users. We also share it with 3 other organizations that are using 11.1FC?/AIX? Servers (not sure what exactly they are, but we have identical data tables/indices/etc for the database and the 3 partners are all on exact same IDS version/OS as each other). 1 of our partners receives an error that we have never seen and gets another error several times a week that we have received only ~10 times in life of app. We use an ODBC connection using the Informix client SDK. Currently 3.7, but have used 3.0 and 2.81 in the past. Our partners are using 3.0. Error we have never received is: ERROR [HY000] [Informix][Informix ODBC Driver][Informix]Could not insert new row into the table. ERROR [42S22] [Informix][Informix ODBC Driver][Informix]Column (moduser) not found in any table in the query (or SLV is undefined) This one is most puzzling as we don't insert into that field, it has a default value defined (it is part of a username/datetime stamp pair used in almost every table to track record updates. It is also part of a ) (verified that it is on partners DB's as well). Error partner gets much more than us or other partners: ERROR [HY000] [Informix][Informix ODBC Driver][Informix]Could not do a physical-order read to fetch next row. sqlerrm(informix.TABLENAME) TABLENAME is usually one of 3 different tables, but primarily one primary to the app table. We had just been attributing it to the DB being heavily accessed at that moment and since we had only received it so few times, it never really bothered us. However, our partner is now experiencing this much more frequently, and getting the first error, and wanting us to 'fix' it, but we don't know where to look as we can't reproduce the first error and get the 2nd so infrequently. Is there something we should be looking for, setting, configuring in our app that alleviate these errors? We have ZERO access/control over their database/server and very limited success in having their DBA assist us with any index additions we have asked for. Any and all suggestions welcome. TIA, Randy
The second error is most likely a contention and lockout problem. Does your application execute SET LOCK MODE TO WAIT <nsec>; after opening the database? Setting this, or increasing the timeout value, should relieve this problem. The other one may be coming from the trigger that updates moduser when the row is updated. It may be that the query they are running (perhaps an update to a multi-table view?) is including more than one table with a column named moduser and the trigger is not specifying which table to update. Something like that. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Mar 31, 2011 at 7:32 PM, Kennedy, Randy <RKennedy@scottsdaleaz.gov>wrote: > We wrote/support a .NET app that has been in use for ~6 years now. We > use it in-house against our 11.1FC3/RHEL 5 database and is our primary > application w/~60 users. We also share it with 3 other organizations > that are using 11.1FC?/AIX? Servers (not sure what exactly they are, but > we have identical data tables/indices/etc for the database and the 3 > partners are all on exact same IDS version/OS as each other). 1 of our > partners receives an error that we have never seen and gets another > error several times a week that we have received only ~10 times in life > of app. > > We use an ODBC connection using the Informix client SDK. Currently 3.7, > but have used 3.0 and 2.81 in the past. Our partners are using 3.0. > > Error we have never received is: > ERROR [HY000] [Informix][Informix ODBC Driver][Informix]Could not insert > new row into the table. > ERROR [42S22] [Informix][Informix ODBC Driver][Informix]Column (moduser) > not found in any table in the query (or SLV is undefined) > > This one is most puzzling as we don't insert into that field, it has a > default value defined (it is part of a username/datetime stamp pair used > in almost every table to track record updates. It is also part of a ) > (verified that it is on partners DB's as well). > > Error partner gets much more than us or other partners: > ERROR [HY000] [Informix][Informix ODBC Driver][Informix]Could not do a > physical-order read to fetch next row. sqlerrm(informix.TABLENAME) > > TABLENAME is usually one of 3 different tables, but primarily one > primary to the app table. We had just been attributing it to the DB > being heavily accessed at that moment and since we had only received it > so few times, it never really bothered us. > > However, our partner is now experiencing this much more frequently, and > getting the first error, and wanting us to 'fix' it, but we don't know > where to look as we can't reproduce the first error and get the 2nd so > infrequently. > > Is there something we should be looking for, setting, configuring in our > app that alleviate these errors? We have ZERO access/control over their > database/server and very limited success in having their DBA assist us > with any index additions we have asked for. Any and all suggestions > welcome. > > TIA, > Randy > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec501631f42059d049fd00e0b
Looks like I didn't finish my notes about the moduser field error. The field is one part of a pair that capture users updates of a record(moduser/moddatetime) and has a default value set for inserts and has a trigger for when that row is updated. Therefore, our app doesn't do anything with these fields and lets the server handle it. We also are very rarely ever updating a row, it is almost exclusively an insert and it is always a single table entry/update, not against a view. We have verified that the default values and trigger are in place on their server and it identical to ours. Don't know if that changes anything for your answer, but wanted to clarify. We will look at our timeout settings as well as try the 'set lock mode to wait xx' specifically for the connection. Thanks, Randy From: Art Kagel [mailto:art.kagel@gmail.com] Sent: Thursday, March 31, 2011 4:57 PM To: ids@iiug.org Cc: Kennedy, Randy Subject: Re: Help with error messages [23280] The second error is most likely a contention and lockout problem. Does your application execute SET LOCK MODE TO WAIT <nsec>; after opening the database? Setting this, or increasing the timeout value, should relieve this problem. The other one may be coming from the trigger that updates moduser when the row is updated. It may be that the query they are running (perhaps an update to a multi-table view?) is including more than one table with a column named moduser and the trigger is not specifying which table to update. Something like that. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Mar 31, 2011 at 7:32 PM, Kennedy, Randy <RKennedy@scottsdaleaz.gov> wrote: We wrote/support a .NET app that has been in use for ~6 years now. We use it in-house against our 11.1FC3/RHEL 5 database and is our primary application w/~60 users. We also share it with 3 other organizations that are using 11.1FC?/AIX? Servers (not sure what exactly they are, but we have identical data tables/indices/etc for the database and the 3 partners are all on exact same IDS version/OS as each other). 1 of our partners receives an error that we have never seen and gets another error several times a week that we have received only ~10 times in life of app. We use an ODBC connection using the Informix client SDK. Currently 3.7, but have used 3.0 and 2.81 in the past. Our partners are using 3.0. Error we have never received is: ERROR [HY000] [Informix][Informix ODBC Driver][Informix]Could not insert new row into the table. ERROR [42S22] [Informix][Informix ODBC Driver][Informix]Column (moduser) not found in any table in the query (or SLV is undefined) This one is most puzzling as we don't insert into that field, it has a default value defined (it is part of a username/datetime stamp pair used in almost every table to track record updates. It is also part of a ) (verified that it is on partners DB's as well). Error partner gets much more than us or other partners: ERROR [HY000] [Informix][Informix ODBC Driver][Informix]Could not do a physical-order read to fetch next row. sqlerrm(informix.TABLENAME) TABLENAME is usually one of 3 different tables, but primarily one primary to the app table. We had just been attributing it to the DB being heavily accessed at that moment and since we had only received it so few times, it never really bothered us. However, our partner is now experiencing this much more frequently, and getting the first error, and wanting us to 'fix' it, but we don't know where to look as we can't reproduce the first error and get the 2nd so infrequently. Is there something we should be looking for, setting, configuring in our app that alleviate these errors? We have ZERO access/control over their database/server and very limited success in having their DBA assist us with any index additions we have asked for. Any and all suggestions welcome. TIA, Randy ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Oh, also, the "Physical position" part of the error is indicating that the query is not using an index against the named table but performing a sequential scan of the table, which will increase the incidence of lockout errors - and slow down queries obviously. Look to see if that site's data distributions are up-to-date and up-to-standards. That may be one reason they are hitting the lock errors more frequently than other sites (the lack of a lock wait mode setting is the reason anyone hits it at all). Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Mar 31, 2011 at 8:07 PM, Kennedy, Randy <RKennedy@scottsdaleaz.gov>wrote: > Looks like I didn't finish my notes about the moduser field error. > > The field is one part of a pair that capture users updates of a > record(moduser/moddatetime) and has a default value set for inserts and has > a trigger for when that row is updated. Therefore, our app doesn't do > anything with these fields and lets the server handle it. We also are very > rarely ever updating a row, it is almost exclusively an insert and it is > always a single table entry/update, not against a view. We have verified > that the default values and trigger are in place on their server and it > identical to ours. > > Don't know if that changes anything for your answer, but wanted to clarify. > We will look at our timeout settings as well as try the 'set lock mode to > wait xx' specifically for the connection. > > Thanks, > Randy > > From: Art Kagel [mailto:art.kagel@gmail.com] > Sent: Thursday, March 31, 2011 4:57 PM > To: ids@iiug.org > Cc: Kennedy, Randy > Subject: Re: Help with error messages [23280] > > The second error is most likely a contention and lockout problem. Does > your application execute SET LOCK MODE TO WAIT <nsec>; after opening the > database? Setting this, or increasing the timeout value, should relieve > this problem. The other one may be coming from the trigger that updates > moduser when the row is updated. It may be that the query they are running > (perhaps an update to a multi-table view?) is including more than one table > with a column named moduser and the trigger is not specifying which table to > update. Something like that. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, implicitly, > or by inference. Neither do those opinions reflect those of other > individuals affiliated with any entity with which I am affiliated nor those > of the entities themselves. > > > On Thu, Mar 31, 2011 at 7:32 PM, Kennedy, Randy <RKennedy@scottsdaleaz.gov> > wrote: > We wrote/support a .NET app that has been in use for ~6 years now. We > use it in-house against our 11.1FC3/RHEL 5 database and is our primary > application w/~60 users. We also share it with 3 other organizations > that are using 11.1FC?/AIX? Servers (not sure what exactly they are, but > we have identical data tables/indices/etc for the database and the 3 > partners are all on exact same IDS version/OS as each other). 1 of our > partners receives an error that we have never seen and gets another > error several times a week that we have received only ~10 times in life > of app. > > We use an ODBC connection using the Informix client SDK. Currently 3.7, > but have used 3.0 and 2.81 in the past. Our partners are using 3.0. > > Error we have never received is: > ERROR [HY000] [Informix][Informix ODBC Driver][Informix]Could not insert > new row into the table. > ERROR [42S22] [Informix][Informix ODBC Driver][Informix]Column (moduser) > not found in any table in the query (or SLV is undefined) > > This one is most puzzling as we don't insert into that field, it has a > default value defined (it is part of a username/datetime stamp pair used > in almost every table to track record updates. It is also part of a ) > (verified that it is on partners DB's as well). > > Error partner gets much more than us or other partners: > ERROR [HY000] [Informix][Informix ODBC Driver][Informix]Could not do a > physical-order read to fetch next row. sqlerrm(informix.TABLENAME) > > TABLENAME is usually one of 3 different tables, but primarily one > primary to the app table. We had just been attributing it to the DB > being heavily accessed at that moment and since we had only received it > so few times, it never really bothered us. > > However, our partner is now experiencing this much more frequently, and > getting the first error, and wanting us to 'fix' it, but we don't know > where to look as we can't reproduce the first error and get the 2nd so > infrequently. > > Is there something we should be looking for, setting, configuring in our > app that alleviate these errors? We have ZERO access/control over their > database/server and very limited success in having their DBA assist us > with any index additions we have asked for. Any and all suggestions > welcome. > > TIA, > Randy > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf3054aa49657a46049fd04516