Start Free Trial

asked on

ODBC Error

Hello,
As our internal web service, we are running Apache 1.3.26 (Win32) with PHP 4.3.5.
Windows 7 32 bit.
We have hundreds of .php files used for various functions.
Part of our system is used as a time clock.

About three weeks ago, we started to have an issue with the time clock functions.
Although it works properly most of the time, we sometimes get the following error message.

--------------------
Warning: odbc_connect(): SQL error: [Microsoft][ODBC Microsoft Access Driver] System resource exceeded., SQL state S1001 in SQLConnect in C:\modules\adodb\drivers\adodb-odbc.inc.php on line 167
-------------------

When I close Apache and restart it, the issue is temporarily resolved.
There is no common factor to when, the error will occur.

Time_Clock.mdb is in Access 2000 File Format.
There are 7 tables in Time_Clock.mdb.
One of the tables is clock which stores all the work hours, clock in times, and clock out times.
This is the table being accessed when we have the issue.

I have disabled the Anti-Virus.
I have run Compact and Repair on Time_Clock.mdb multiple times.
I deleted ten years of records from the clock table to make the table smaller.
I moved Time_Clock.mdb to the local C:\ drive. It had been on another server.
I copied the system image to a different PC, with different hardware.

The error still occurs.

I am the lone IT person in our company.
My knowledge of databases and programming is minimal.
Can someone help me find the answer?

Thank you,
Doug Koser

Lukasz Zielinski

"System resource exceeded" I would check disk and memory usage

ziolko.

slightwv (䄆 Netminder)

See if you can turn on connection pooling:
https://www.ibm.com/support/pages/error-s1001microsoftodbc-microsoft-access-driver-system-resource-exceeded-native-error-1011-sci83514

Make sure the code is releasing/closing connections properly. If you aren't familiar with the code, you may need some outside consulting for this one.

Lukasz Zielinski

...also if you use wrapper.jar/.exe to run Apache as service - check wrapper.conf for memory settings, maybe your Apache does not have enough memory to run.

ziolko.

ASKER

Thank you for the suggestions.
Disk and Memory usage are ok.

I will turn on Connection Pooling for the driver.
It sometimes doesn't have the issue for a day or two.
I'll let you know how it goes.
Thanks again.

you definitively need to go to 64.bit windows os and useAccess 64-bit as well. if using external routines they have to be turned to 64-bit as well.

note, on 32-bit windows your programs can't use virtual memory beyond 2GB. if stopping any other memory-consuming on the computer you may get a few time longer but that will not last for long.

Sara

John Tsioumpris

Since it started suddenly 3 weeks ago there is a chance that some update or another application is clashing with Access and therefore you have troubles.
Check your recent update and if you installed something.

ASKER

Thanks John.
All updates have been turned off for years, except the anti virus, which I have turned off until I resolve the issue.
There have been no changes to the server in the past five years.
I copied the image to another computer to eliminate any hardware issue.

How many people can access that program at once? Too many could cause the resource problem.

ASKER

Thanks for the thought Dave.
It's just a time clock and vacation request function.
Very small data transfers.
At most there could be three people trying to clock in / out at the same time.
But it happens during odd times of the day too, not just the normal shift start or end times.

It's about 24 hours since I turned on Connection Pooling for the driver. It's holding so far, but I wouldn't call it fixed unless it goes a week without failing.

I'll keep my postings updated.

Thank you all.

ASKER

I guess I should clarify that.
This is our intranet server. A lot of people could be doing various functions on the server, but the only issue is with the time clock function, which is very small data transfers. The time clock is the only function that uses the database the error refers to.

slightwv (䄆 Netminder)

My thought process:
It is how the app does connections to the database. Just because other areas don't explicitly access the time function, doesn't mean the app(s) don't create the ODBC connection.

ASKER

Thank you for clarifying.
I understand.
Thank you

ASKER

It just errored out again.
Connection Polling for the driver didn't fix it.

ASKER

The next suggestion in this chain is
"Make sure the code is releasing/closing connections properly."
If the code controlling that hasn't been modified, could this still be the problem?

And how would I check that and correct it?

slightwv (䄆 Netminder)

>>Connection Polling for the driver didn't fix it.

Many theories fail.. :(

>>If the code controlling that hasn't been modified, could this still be the problem?

It could be if usage of the app has increased.

Based on the error message "something" is exhausting some resource.

>>And how would I check that and correct it?

I'm not a PHP person. Make sure there is a close for every open:
https://www.php.net/manual/en/function.odbc-close.php

In the .Net world, there is a garbage collector that would try to protect systems from "bad" developers and would clean up dead things even if the developers didn't explicitly do it.

I don't know if PHP/Apache has something similar but the fact that restarting Apache fixes things temporarily makes me think there is something in the code not being cleaned up.

ASKER

Thank you. I'll look into that.

"very small data transfers" is very misleading. The runtime code for PHP and the driver have to be loaded (normally for every request of any kind). Apache is probably spawning a new process for each request. Any data on disk has to be loaded each time.

I would take a look at Windows Task Manager (Ctrl-Alt-Delete) to see what is running and how much memory each process is using. You can sort by CPU percentage or Memory usage to get different views.

ASKER

Apache is using about 110MB of memory
Overall memory usage is 34%

I'll look into the database closing in php

Thank you

Average usage isn't the problem. I'd go to the Performance tab in Task Manager and let it run for a while to see if there are peaks in either CPU or Memory usage. Peaks are when you run out of resources.

ASKER

Hello,
Sorry I haven't replied sooner. I am the only IT person, and a few emergencies came up.

I happened to be watching memory usage when the issue ocured today, and there was no spike in usage.
There was a minor increase in CPU Usage, but nothing major.

Does that point toward something other that not releasing connections?

I did look at the time clock php files.
I'm seeing where needed.
mysql_close();
$db->Close();
fclose();

All information I've found shows the proper close to
$conn = &ADONewConnection('access');
would be
ADOConnection_close();

I don't find
ADOConnection_close();
anywhere, but anywhere I tried to enter it, showed as an error when the code ran.

This is PHP4.3.5. All the files were written more than 15 years ago.

Today I moved this server to a different computer, to see if it's any different.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thank you for getting back to me.
I've tried installing everything on a 64 bit operating system. The time clock would set up ok, but other parts of our intranet would not. That is mostly due to my lack of database management experience.

But I do not see any evidence of memory being maxed out. I've been running performance monitors all day.
Please explain why having a 64 bit O/S would help.
Thank you

ASKER

I haven't seen any spikes in memory usage. Hard Faults /sec have been at zero all day.
All metrix I can see are well within normal usage.
Disk, CPU, Memory, Network.

Lukasz Zielinski

But I do not see any evidence of memory being maxed out
you mean it did not max out on all available resources?
again, if you use wrapper.jar to run apache as service, there is file wrapper.conf which includes setting for max memory availabe for apache.
so you may still have available resources on your server but maybe apache used all memory allowed by wrapper.conf.

ASKER

I don't use wrapper.jar, I just have Apache, PHP, and MySql installed.
Or do I not understand what you mean?

There could be a memory setting in Apache to set memory usage. I'll look for that.

ASKER

In the php config file, httpd.conf, I don't see any setting for memory usage.

Lukasz Zielinski

Doug, my idea was based on wrapper.conf settings.
So if you don't use it - I'm out of ideas

ASKER

Thanks you.
I appreciate your thoughts.

PHP memory limits are found in the 'php.ini' file that is for that installation.

The correct Microsoft ODBC Driver for SQL Server is also required for the 'sqlsrv' driver to work.. https://docs.microsoft.com/en-us/sql/connect/php/system-requirements-for-the-php-sql-driver?view=sql-server-ver15

ASKER

Thank you Dave. I left work for the day. I'll look at that tomorrow.
Thank you

ASKER

I connected from home to check it out. Here are the settings I found in php.ini. What do you suggest?
Thank you,

;;;;;;;;;;;;;;;;;;;
; Resource Limits ;
;;;;;;;;;;;;;;;;;;;

max_execution_time = 480 ; Maximum execution time of each script, in seconds
max_input_time = 90 ; Maximum amount of time each script may spend parsing request data
memory_limit = 50M ; Maximum amount of memory a script may consume (8MB)

xxxxxxxxxxxxxxxxxxxxxx

; Set maximum length of log_errors. In error_log information about the source is
; added. The default is 1024 and 0 allows to not apply any maximum length at all.
log_errors_max_len = 1024
xxxxxxxxxxxxxxxxxxxxxxxx

; Maximum size of POST data that PHP will accept.
post_max_size = 8M
xxxxxxxxxxxxxxxxxxxxxxx

; Maximum allowed size for uploaded files.
upload_max_filesize = 2M
xxxxxxxxxxxxxxxxxxxxxxx

; Handling of LONG fields. Returns number of bytes to variables. 0 means
; passthru.
odbc.defaultlrl = 4096
xxxxxxxxxxxxxxxxxxxxxxxxxx

Since none of the other PHP pages seem to cause errors, I don't see a problem. Is there a setting for the 'php_errors.log' file? If so check that file to see what's there.

One other thought. Windows 7 is not a server OS so only 10 or 20 connections can be made to it from other computers. I don't remember at the moment how that is determined. But I think I read in another question that computers can make multiple connection and use up that number of connections fairly quickly.

Another page suggests that the error can be cause by a query that returns too many records. ??

if you are searching for the message

Warning: odbc_connect(): SQL error: [Microsoft][ODBC Microsoft Access Driver] System resource exceeded., SQL state S1001

you mostly will get as a possible reason that the limited virtual memory provided at a 32 bit OS is a problem. moreover, MS Access is a file-based DBMS. hence it must operate with page locks (not record locks) what definitively is a bottle-neck on a 32-bit system that has a maximum of 2 GB physical memory for its applications.

second would be the number of opened connections same time which requires a multi-core cpu and an OS which could handle multiple connections and associated threads and one physical database file without exhausting its resources.

Sara

ASKER

Thank you.
I greatly appreciate all thoughts and comments.
I'll be trying one thing at a time, so I know what finally solves it.

This system is being used the same as it's been used for the past 15 years.
It started on Windows XP. For the past 5 years, it's been on Windows 7.
It is a quad core processor 3.0Ghz.

Can you start a debug version of your application from Visual Studio IDE?

if yes, can you reproduce the ODBC error there? if yes, examine the debug output widow which may show additional error information such as which resource is exhausted.

alternatively, you may programmatically call SQLError (or its successor SQLGetDiagRec) function after SQLConnect returns SQL_SUCCESS_WITH_INFO.

SQLError/SQLGetDiagRec should be called multiple times in order to get all information of an ODBC error event. see sample code in c++.

void ShowSQLErrorInfo(
      const std::string & label,
      SQLHANDLE handle,
      SQLSMALLINT type)
  {
    SQLINTEGER i = 0;
    SQLINTEGER native = 0;
    SQLCHAR state[ 7 ] = { 0 };
    SQLCHAR text[256] = { 0 };
    SQLSMALLINT len = 0;
    SQLRETURN ret ) = 0;
    std::cout <<  "\n The driver reported the following error info " << label << \n\n";
    do
    {
      ret = SQLGetDiagRec(type, handle, ++i, state, &native, text,
      sizeof(text), &len );
      if (SQL_SUCCEEDED(ret)) {
           std::cout <<  state << ":" << i << "," << native << ":" << text << "\n"; }
    }
    while( ret == SQL_SUCCESS );
  }

Open in new window

the state is a five character SQLSTATE code. the first two characters indicate the class and the next three indicate the subclass. SQLSTATEs provide detailed information about the cause of a warning or error.

the native error code is a code specific to the data source. it may useful for locating an internal error or state.

The message text is the text of the diagnostic.

Sara

ASKER

Thank you Sara.
That is beyond my knowledge. I would have to learn how to do what you're suggesting.

I have looked for an error log for php, as Dave suggested. I didn't find one.

Here are some settings I found in the php.ini file for error log.
To me, it reads like display_errors would have to be turned off to use log_errors
Is that correct, or can both be set to On?
And then set error_log= to a folder of my choice?

But if the log file would only show the error that has been displaying, we already know what that is.
------------

log_errors = Off

; Print out errors (as a part of the output). For production web sites,
; you're strongly encouraged to turn this feature off, and use error logging
; instead (see below). Keeping display_errors enabled on a production web site
; may reveal security information to end users, such as file paths on your Web
; server, your database schema or other information.
display_errors = On

; Log errors into a log file (server-specific log, stderr, or error_log (below))
; As stated above, you're strongly advised to use error logging in place of
; error displaying on production web sites.
log_errors = Off

; Log errors to specified file.
;error_log = filename

I believe that the error you're seeing is not coming from PHP but the operating system. It is a 'standard' error message for many SQL related problems with other languages that use the ODBC functions.

The PHP error log on IIS is in 'wwwroot' on my systems. It has to be in a place that is writeable by a web page function. I suggest enabling the PHP error log to see if there is something related to the error that you already know about.

ASKER

Thank you Dave.
I'll look into that.

ASKER

I set up error log. It hasn't had the error since the log was set up. Since the business is closed until Monday, I won't see any errors before that.

Here's some info I got from mysql.

mysql> status
--------------
C:\mysql\bin\mysql.exe Ver 12.22 Distrib 4.0.20a, for Win95/Win98 (i32)

Connection id: 169
Current database:
Current user: ODBC@localhost
SSL: Not in use
Server version: 4.0.20a-nt-max
Protocol version: 10
Connection: localhost via TCP/IP
Client characterset: latin1
Server characterset: latin1
TCP port: 3306
Uptime: 2 days 19 hours 13 min 47 sec

Threads: 9 Questions: 11058 Slow queries: 0 Opens: 1190 Flush tables: 1 Ope
n tables: 0 Queries per second avg: 0.046

ASKER

I have error log turned on and a location set up in the php.ini.
The error occurred about an hour ago. It did not create a log.

Where did you set up the error_log? It has to be in a location that is write-able by the web server. It is usually in 'webroot'.

ASKER

I had set it up to a folder on C:\.
Logged in and run as an administrator, I thought that would work.
I'll change it to the wwwroot folder.
I'll let you know if that works.

Thank you

IIS is very restrictive and in my experience doesn't care about admin privileges. The location has to be accessible to the IIS user (IUSR?).

ASKER

We had the issue about an hour ago, and no error log is showing.

Here are the settings in the php.ini for error logging.

; Log errors into a log file (server-specific log, stderr, or error_log (below))
; As stated above, you're strongly advised to use error logging in place of
; error displaying on production web sites.
log_errors = On

; Log errors to specified file.
error_log = C:\Inetpub\wwwroot

ASKER

display_errors = On

display errors is also on.
Does the log_errors paragraph suggest this NEEDS to be off if using log_errors?
Or is that just a recommendation?

ASKER

As for the mysql side, I found the error log for that.
The only entry is one that has been repeating since 2012, so I don't see that as relating to the current issue.

200414 11:02:12 Aborted connection 331 to db: 'intranet' user: 'webcalendar' host: `localhost' (Got an error reading communication packets)

121003 18:15:33 Aborted connection 1 to db: 'intranet' user: 'webcalendar' host: `localhost' (Got an error reading communication packets)

This refers to a calensar database we use. It's not related to the time clock, except that when approving vacation days, it enters a name in the calendar on the date of the vacation day. This has been working ok.

That is not what is being done when the error occurs.

The 'display_errors' is just a recommendation. Mysql shouldn't have anything to do with the MBD file or driver.

ASKER

No issue today.

ASKER

Hello,
The error occurred 4/14 at 11:00 and then did not occur again until 4/15 at 22:21.
More than 35 hours.
It ran ok through the heaviest usage times of the day, then at a time of very little usage, the error occurred again.
The latest error still did not create an error log in wwwroot, as set up in the php,ini.

I did found an Apache error log, which shows the normal error message
[Tue Apr 14 11:01:02 2020] PHP Warning: odbc_connect(): SQL error: [Microsoft][ODBC Microsoft Access Driver] System resource exceeded., SQL state S1001 in SQLConnect in C:\modules\adodb\drivers\adodb-odbc.inc.php on line 167

On 4/14 when I discovered the system showing the normal error, I tried a different function of the time clock other than clocking in or out.
It produced the following error.
[Tue Apr 14 11:01:02 2020] PHP Warning: odbc_exec(): supplied argument is not a valid ODBC-Link resource in C:\modules\adodb\drivers\adodb-odbc.inc.php on line 504

Does this help at all?

Considering that the time stamp is identical, that looks like part of the same error to me. The first error caused the second one.

ASKER

I haven't seen the error in a couple days, and I think it's because I've restarted Apache every day.
Because of that, as a test, I set up a scheduled task to stop and the start Apache every day at 3AM.
We'll see if that keeps it from having the error.

ASKER

That didn't work. I still get the error.

ASKER

Thinking now of the 10 connection limit you mentioned.
I'm thinking of moving the Time Clock functions to a computer separate from the rest of our intranet.

Do you get the error at the same time of day each time?

ASKER

No. I haven't seen any common factor to it.
Time of day.
Heavy or light usage.

ASKER

Hello,
I made some changes in the php.ini.

memory_limit = 50M
--changed to 256M

post_max_size = 8M
--changed to 128M

upload_max_filesize = 2M
--changed to 128M

It's running for about 40 hours without the error.

ASKER

Those changes did not help.
I got the error again.

ASKER

I did manage to get a 64 bit computer set up that looks like it may run the old Apache, php, mysql & perl that we use.
I might try that in the next few days.

ASKER

still testing

ASKER

I noticed there was a scheduled task to update the anti-virus every 4 hours.
I turned that off.
Ran great for five days, then the problem started again.

I now moved the server to a 64 bit Windows 8.1 Pro computer.

ASKER

5 days with no errors, since I moved it to a different PC with a 64 bit O/S

ASKER

Thank you all for the suggestions and comments you've all made.
After moving to 64 bit Wndows 8.1 Pro, it's been running for a week without the error, so I'll call that issue resolved.
Of course there is a different issue now. I'll start a new thread for that.
Again, thank you all for your help.

ASKER

How do I properly close this out?

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial