ODBC connection problem :(

[RoyceyBaby]

Help!

I have installed postgreSQL 7.3.2 from source for my LFS4.0 linux system.

I have downloaded psqlodbc. When I setup a linked table in access, it works briefly but then it will have connection problems after a short while, 60 seconds?

The ODBC errors I am getting are the following:

- could not connect to remote socket (#101)
- no response from backend (#27)
- could not send query (connection dead) (#27)
- unexpected protocol character during authentication, error while reading from socket (#210)


eventually I can recreate the linked table, but then I fall foul of the same problems.

Can anybody point me in the right direction.


Thanks,

Royce

 

[WonderCow]

Hi Royce,

I'm having exactly these problems. I can't figure out why, and I've tried everything.

Did you ever get a solution, if so I appreciate that it was a while ago, but do you know what to do?

Thank you Very much

WonderCow

 

[gwinn7]

I am running Postgres v8 and the latest psqlODBC driver. Everything is working great for us. You stated you are running v7.3. That version of PostgreSQL is significantly older.

What happens when you use psql on the Linux machine? Is the postmaster still running? Can you query the database on the server?

Have you thought about upgrading to v7.4, or 8.0?

Gary
gwinn7

 

[TrojanWarBlade]

Silly question but is this a firewall issue?


Trojan.

 

[flugh]

Can you run a 'netstat -pat' and see what addressort postgres is listening on? TrojanWarBlade's firewall comment is right on too (assuming the process is actually listening on a port). You may need to edit your postgresql.conf and uncomment the tcp_socket = true line. Or add -i to your startup commandline.

gwinn7 has a good question to think about though. If you're rolling your own, get the newest postgres source (8.0.3 as of right now I believe). A lot of new features and bugs worked out of the code. I am using postgres 7.4.7 (stock with my unbuntu 5.04 distro), and the newest pgodbc drivers as of a couple weeks ago, with Access as the front end, no problems. So it can be done

----
JBR

 

[WonderCow]

Thanks for responding, but the problem is a little more odd than it looks:

We have been using this combination for over a year at least, and have not had this problem. The problem started a month ago, and we have changed nothing that we can think of.

Sadly, the users didn't bother to tell us for over a week, and we have lost track of any obscure things that may have affected us.

I have checked the firewall and all was good.

I then restarted postgres (and we are using 7.4, not 7.3 like Royce, but the problem is identical), which yielded no results - it was all fine.

tcp_socket=true is uncommented, and the netstat -pat command gives everything I would expect.

also, just to make things a bit more confusing, these error messages are not consistent - they happen on random machines (can't find a pattern to that) in random parts of the application, and not all the time.

Cheers for the suggestions guys, I do appreciate them - I have been trying to solve this problem for 2 weeks now, and it's getting a little frustrating.

WonderCow

 

[TrojanWarBlade]

I bet it is.
Can you get the users to keep a log of when the dropouts occur and what they were doing at the time?
Maybe you can see a pattern from there.
Maybe some changed some code that you are not aware of.
If we are talking about dropouts rather than failure to connect at all then I would suggest it sounds more like a client problem than a server problem (client as in application as opposed to user).



Trojan.

 

[WonderCow]

I have set postgres loggin for the client machines, but I am now convinced that it isn't thwe code, since someone else is getting these problems, and he's not using the app. if it is client side, I am leaning more toward odbc problems, although I don't really think that it is. I have been sifting through some of the logs, and can't come across an individual table/view/etc. that is involved across the board.

The other strange thing is that some users have started complaining about not being able to log in.

The plot grows thicker by the minute, and speaking of which, the errors are on the increase - initially I only had a couple of people with a problem involving an error message. After that it was a good few complaining about more than one error, and now it's got to the point where there are heaploads of errors happening on average every 5-10 mins.

I have tried to mimic this on my machine, and I have no problem. It isn't a timeout problem, since I've let my copy run unnatended overnight, and used it fine the next morning.

No code has been changed in the app either - I am the only person who can change anything, and I've not touched it for over 6 weeks due to other projects, etc. and just in case, I checked over a copy, and tried to find anything out of the ordinary, but the pages that are reporting errors have nothing in common.

bizarre, no?

 

[TrojanWarBlade]

Is there any other process that's running on the server that could be hogging a resource such as disk, memory or network?


Trojan.

 

[TrojanWarBlade]

Yep, bizarre.
I agree, with that info it does sound more server side, hence my current suggestions.


Trojan.

 

[WonderCow]

There are no major server processes - we are running 4cpus which are averaging:

1 99.4% idle
2 84.4% idle
3 95.0% idle
4 93.2% idle

So far, I have tried the following:

Checked ODBC timeout - OK
Check Driver version for ODBC Postgres - OK
Check ODBC Settings - OK
Check for DB Corruption - OK
Use all tables that the error has come from (anything it's touched) - OK
Check table and field names (in case someone else has managed to put something daft in) - OK
Check for unexpected data - OK
Sift through ODBC Trace for a handful of users who have the problem and find the difference with them and those who don't - OK (though not a reccomended hobby)
Compare data structure with last known working copy - OK
Add index to tables without and re-index those that do - OK
Vacuum DB - OK

These are just the server side bits, there are other things I tried that aren't related (frontend,ec.)


I'm unsure as to how to proceed to be honest, I have tired every avenue I can think of, and I must also confess that I know reasonably little about postgres.

Cheers

 

[TrojanWarBlade]

what about disk space and memory usage?


Trojan.

 

[WonderCow]

Actually, i've not checked disk/mem usage - I will do so now...

 

[WonderCow]

mem is 4497000k used : 138000k free
disk usage is 23% used (17G total disk size)

I'm unsure about the amount of mem - think it looks ok though, but the disk space seems Way within bounds.

 

[gwinn7]

This may seem really silly, but have you checked the NIC on your server?

The problems you are experiencing seem to point at a hardware, or even a cabling issue. I don't recall seeing any prior posts where this was even looked at.

I have had to replace NICs in the past because of similar network types of timeouts and errors etc.

Gary
gwinn7

 

[WonderCow]

Sorry about this, but there is so much info I have gathered over th last two weeks, I haven't given all the details - and one thing I said was a little misleading:

The machines affected were always the same ones, but they were random in the sense that there was no pattern to work out why some did and some didn't.

The hosting service is also remote (we pay for external hosting, not remote in another office). I will try to check this out as a possibility, but with there being some form of consistency of users who do/don't I'm not entirely sure this will be the cause. The users machines are all fine though.

We did install load balancers - although users are experiencing problems either going through the domain or through vpn.

Cheers for the idea - I'll try to have a look if I can.

WonderCow

 

[TrojanWarBlade]

The machines affected were always the same ones, but they were random in the sense that there was no pattern to work out why some did and some didn't.

The users machines are all fine though.

I find these to comments to be conficting.
If you have only certains machines exhibiting a problem then surely you should be looking for something that is common to them and not the others. Do they have something installed that the others don't or vice versa?
Is there a particular user that only ever uses these machines?
Are they all connected to the same phase of the mains?
Some crazy ideas there but just meant to try to show how i think you should be looking for something common between them.
Personally I would wary of stating that they are all fine.



Trojan.

 

[WonderCow]

The machines are of different Win versions (no pattern), different Office versions (no pattern), but otherwise have identical 'other' software on. They all have same activex versions, odbc, and there does not appear to be any correlation there.

There are also different levels of use for each of these users - as a generalisation, mostly the errors are more easily found on the heavier users (which kind of follows reason).

In answer to the questions, though:

1. No odd/different software
2. Yes, each user uses their own terminal, but we have tried switchnig a user with and a user without problems. The result was that the user without had problems on the offending machine, but so did the user with on the 'non-offending' machine (I hope that makes sense)
3. there is no pattern to the wiring/power supply.

I was more innacurate in my wording than my meaning, I've just noticed the mistake - I meant that the application was fine, rather than blanket stating the machines (ODBC is still in the running as well as other factors, although my suspicions are still more leaning toward the server side of things)

I initially thought that this was corruption - and that users were accessing the wrong (damaged) information. This is not the case. I have tried the application for curruption, and that yielded nothing either.

 

[TrojanWarBlade]

Can you try deliberately running a heavy load on one of the machines that has not failed before to see if you can cause it to fail?
That might give more confidence that it is not specific to a batch of machines and that it is, as you suggest, load dependant.


Trojan.

 

[WonderCow]

I will get a light user to start using it for long periods of time now.

Cheers