[This topic is migrated from our old forums. The original author name has been removed] [Attachment has been removed.]
ERROR
Here's the error I got for the last script in a series of 8 I tried to run overnight. This set of scripts was usually taking 1.5 hrs to complete. This morning around 6:58am I stopped the execution that was going on for over 6hrs and I got the following error for the last script only. The rest of them had completed successfully.
[Error Code: 0, SQL State: 08S01] Software caused connection abort: recv failed
I'm attaching a few jpgs to expedite an advice from the community and the dbVis tech support group regarding this issue.
QUESTION
All I need to know is:
1) what created this incident
2) ideas why it happened and what app'n caused it
3) how to prevent it from happening again
4) any link to perf. tuning sql scripts exec. time that ties to dbVis from your experience ?
SUPPORTING Docs
dbVisSWAbort.jpg shows the last script and the 6:58am error
dbVisDebugWindow shows the Debug Window log at 6:58am but to be on the safe side I took a copy of the entire Debug Window contents and attached to this thread as a txt file.
The driver jpg shows the Teiid driver I'm using for JDBC connectivity with JBOSS EAP.
ENV'NT
dbVis JBOSS EAP/Data Virtualization Virtual Database via JDBC conn. Aster Teradata cluster
All 3 items in the line above are sitting on different servers in different VLANs separated by firewalls. Connectivity from dbVis to JBOSS EAP/DV via port 31000 and from JBOSS EAP/DV via port 2406. All ports are opened on the appropriate firewalls. We stood this env'nt up about 3 months ago and I've been using ever since.
I have full control on JBOSS, partial on the Aster Teradata cluster where I can only recommend changes but not immediately implement them and no control on dbVis other than configuring it with drivers, connections and run scripts. I do not have any control on the machine it's running on. As a matter of fact dbVis runs on a Citrix Image,
JBOSS EAP/DV runs on a Windows 2008R2 EE VMware virtual machine and Aster cluster on a set of physical Windows servers.
dBVIS Version
Product: DbVisualizer Pro 9.1.10
Build: #2295 (2014-08-08 18:38:31)
Java VM: Java HotSpot(TM) 64-Bit Server VM
Java Version: 1.7.0_60
Java Vendor: Oracle Corporation
OS Name: Windows Server 2008 R2
OS Arch: amd64
OS Version: 6.1
Any other info you may need please let me know and I'll respond as soon as possible.
thanks in advance.
stavrosk
[This reply is migrated from our old forums. The original author name has been removed]
Re: Script failed
Hello Roger,
Q.: When the log show the "FROM clause" is the GUI still showing the "in progress" information?
Answer: That's an affirmative. It still shows the "in progress" information.
Q.: Have you had a chance trying the beta version?
Answer: Have not installed it yet on our env'nt since only full supported versions are allowed. I'll install it on a personal computer where I'm free to test at will.
BTW, the "FROM clause" msg has never manifested again since I first mentioned it on this thread. I concur with your recommendation to move forward with the new version, even on beta, but it looks like I have to wait until it matures to become a fully supported one.
Feel free to put this thread on pause and I'll make an attempt to re-open if a similar dbVis incident comes around.
Thanks as always.
Stavros K.
Roger Bjärevall
said
almost 10 years ago
[This reply is migrated from our old forums.]
Re: Script failed
Hi Stavros,
> {quote:title=stavroskalamat wrote:}{quote}
> I understand your comment about the "FROM clause" dbVis msg so I'd like to ask why the very last script doesn't complete its execution ?
> dbVis shows the script is running however, when I open the Debug Window I see the "FROM clause" msg issued at a past time, which makes me think that execution has stopped since that time.
>
> The Q. is: is the script still going OR it really stopped when the msg was issued ?
That is (should) be displayed at the very end of the script execution. When the log show the "FROM clause" is the GUI still showing the "in progress" information?
Have you had a chance trying the beta version?
Regards
Roger
a
anonymous
said
almost 10 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Script failed
Hello Roger,
Q. Is this script is executed periodically outside of DbVisualizer?
Answer: The short script I'm talking about runs from JBOSS EAP admin console and it's integrated into the product's config. In other words, JBOSS EAP offers this setting (and a couple of others along the same lines) to maintain a keep-alive in order to ensure the admin that JBOSS connectivity with its underlying data source persists at all times.
As I mentioned before, so far, that seems to help dbVis maintain a good connection to JBOSS's data source as well, because what was probably happening before I used the script, was JBOSS connection to its source was occasionally breaking and that problem was manifesting as a weak connection from dbVis.
I'm glad the new version carries a keep-alive feature. Its timing couldn't be better. I hope it'll be available for JBOSS teiid and teradata sources.
Comment:
This is just a debug output declaring the reason why result set editing will be disabled in DbVisualizer. This usually happens if referring multiple tables in the FROM clause, using functions in the column list and so on. This is nothing to worry about.
I understand your comment about the "FROM clause" dbVis msg so I'd like to ask why the very last script doesn't complete its execution ?
dbVis shows the script is running however, when I open the Debug Window I see the "FROM clause" msg issued at a past time, which makes me think that execution has stopped since that time.
The Q. is: is the script still going OR it really stopped when the msg was issued ?
Thanks for your feedback.
Stavros K.
Edited by: stavroskalamat on Nov 6, 2014 8:26 PM
Roger Bjärevall
said
almost 10 years ago
[This reply is migrated from our old forums.]
Re: Script failed
Hi Stavros,
> {quote:title=stavroskalamat wrote:}{quote}
> The issue on my previous post (Posted: Nov 3, 2014 7:05 PM) I'm trying to resolve by running a short script with quick execution time against Aster from JDV in order to maintain a continuous non-interrupted connection. It's working so far (dbVis maintains stable connection to JDV) however, any ideas about it, why it's happening for example, are welcome.
Is this script is executed periodically outside of DbVisualizer? If that is the case it seems there is a network timeout somewhere. Your script prevents the timeout from happening resulting in the connection being kept and DbVisualizer is happy.
This is just a guess.
In the upcoming 9.2 version for DbVisualizer there is a builtin keep-alive feature that runs in the background. It executes a dummy SQL at a specified interval and will prevent timeouts from interrupting a physical connection with the database.
9.2 is currently in beta testing and you may give it a try. http://www.dbvis.com/download/beta/
Please note however that the current beta version (92.2320) don't work with any database. In the next beta planned for release tomorrow it will be possible to configure keep-alive for any database such as Aster. I suggest you give it a try and verify if it helps.
> Here's another error I got this morning I'd like your advice.
>
> Scenario:I'm running a set of scripts and the very last one can't complete. Once again, dbVis connects to JBOSS Data Virtualization (JDV) via a teiid JDBC driver. JDV in turn, connects to the Aster Teradata cluster.
>
> I added a txt excerpt from the bottom of the debug window that shows the log from the execution of the last 2 scripts. The very last script has a notion at the end of the file saying
>
> 07:59:29 [DEBUG ExecutorRunner-pool-4-thread-143 F.?] Edit not allowed: More than one table is referenced in the FROM clause
>
> Why is that an issue ?
This is just a debug output declaring the reason why result set editing will be disabled in DbVisualizer. This usually happens if referring multiple tables in the FROM clause, using functions in the column list and so on. This is nothing to worry about.
Regards
Roger
a
anonymous
said
almost 10 years ago
[This reply is migrated from our old forums. The original author name has been removed] [Attachment has been removed.]
Re: Script failed
The issue on my previous post (Posted: Nov 3, 2014 7:05 PM) I'm trying to resolve by running a short script with quick execution time against Aster from JDV in order to maintain a continuous non-interrupted connection. It's working so far (dbVis maintains stable connection to JDV) however, any ideas about it, why it's happening for example, are welcome.
Here's another error I got this morning I'd like your advice.
Scenario:I'm running a set of scripts and the very last one can't complete. Once again, dbVis connects to JBOSS Data Virtualization (JDV) via a teiid JDBC driver. JDV in turn, connects to the Aster Teradata cluster.
I added a txt excerpt from the bottom of the debug window that shows the log from the execution of the last 2 scripts. The very last script has a notion at the end of the file saying
07:59:29 [DEBUG ExecutorRunner-pool-4-thread-143 F.?] Edit not allowed: More than one table is referenced in the FROM clause
Why is that an issue ?
Any other info yo may need please request and I'll respond as soon as possible.
thanks in advance for your assistance
stavros
a
anonymous
said
almost 10 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Script failed
Hello Roger,
Thanks for replying.
Q.: Does this sudden hang (or interrupt) happen every time you execute the script?
Answer: I have run a lot of scripts from dbVis against JBoss Data Virtualization (JDV), that in turn connects to Aster, and I have observed getting similar disconnect errors sporadically and inconsistently throughout the last month and a half.
The disconnects are not associated with any script in particular. In an effort to strengthen the reliability of the connection between JDV and Aster (I thought that conn. was the culprit) I made some config. changes on JDV's end to validate the connection periodically and keep it up at all times. One of them was to run a sql statement regularly. The keepAlive script I use executes in less than 1 sec. and runs every minute.
I had dismissed the idea the duo dbVis -JDV have issues keeping up their connection continuous because I have continuous ping run as a windows service on the JDV VM to ping the nutanix based citrix image and the recordings show no interruption at all. The JDBC connection in this case goes through port 31000 and a ping won't catch that. Perhaps a wireshark trap will.
Let me know if my thoughts are correct. I'll create a wireshark capture anyways and keep an eye while scripts running. Any other thought or troubleshooting comment is welcome.
Q.: Are there any time-outs on the server side that may be triggered? (Even if it sounds strange that a time-out can happen while an ongoing execution it is worth checking it out).
Answer: I haven't observed any. Usually when a script fails I can re-highlight it, reconnect to JDV and it starts running again.
Any ideas/recommendations how I can chk whether the dbVis-JDV connection fails ?
When a script fails the server log on the JDV VM records an error that looks like this:
... Physical database connection acquired for: MainDB_VDB
11:54:40 [SELECT - 0 row(s), 0.000 secs] [Error Code: 30504, SQL State: 50000] TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 HSDW_Prod_DSrce: 34 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0. fld1_dim_key AS c_0 FROM DBASE.dbSchema.tbl1 AS g_0 ORDER BY 1]
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]
JBoss.org community prompted me to the JDV server logs but there's not anything more than the error above. I can increase the level of logging but the log file inflates very quickly and by the time a script fails it may already be huge.
If that's the only alternative then I'm willing to work on it.
Your thoughts are welcome.
Thanks as always.
sk
Roger Bjärevall
said
almost 10 years ago
[This reply is migrated from our old forums.]
Re: Script failed
Stavros,
Thanks for the detailed post.
At a first glance it looks as if the connection with the database has been interrupted while the SELECT was executing. DbVisualizer recognize these situations only if the driver respond back with an error once it happen. In this case it seems the connection is gone and there is no notification to DbVisualizer.
A few questions:
- Does this sudden hang (or interrupt) happen every time you execute the script?
- Are there any time-outs on the server side that may be triggered? (Even if it sounds strange that a time-out can happen while an ongoing execution it is worth checking it out).
Regards
Roger
anonymous
[Attachment has been removed.]