script

I recently had a problem with a couple of websites that randomly threw the following error

Out of resources when opening file '/var/www/somelocation' (Errcode: 400 - Too many open files)

1	Out of resources when opening file '/var/www/somelocation' (Errcode: 400 - Too many open files)

The culprit being the MySQL server behind the webserver, hitting the ‘open_files_limit’ -limit.

You can get the limit seen by MySQL by querying SHOW VARIABLES like ‘open_files_limit’; and the current status from SHOW STATUS LIKE ‘Open_files’

I’m not going to go into how you change this value (mysql conf obviously and possibly system conf), nor what it should be set to – as both those things depends heavily on your specific environment.

What I will focus on is how you can log the usage from PHP. Here goes.

First we’ll connect to the MySQL server in question:

$servername = "server.example.com";
$username = "myusername";
$password = "mypassword";
$db = "mydatabase";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $db);
// Check connection
if (!$conn){
    die("Connection failed: " . mysqli_connect_error());
}

$servername = "server.example.com";

$username = "myusername";

$password = "mypassword";

$db = "mydatabase";

// Create connection

$conn = mysqli_connect($servername, $username, $password, $db);

// Check connection

if (!$conn){

die("Connection failed: " . mysqli_connect_error());

}

Once those formalities are out of the way, lets get the two datapoints we’re after. Namely 1) how many files are currently open and 2) What’s the upper limit. And the lets log that – with a timestamp obviously – to a text file, for analyzing.

We’ll create a variable to contain the log data, called $logData. Then we’ll run the two queries: SHOW STATUS LIKE ‘Open_files’ and SELECT @@open_files_limit; (which is the shorthand way to get the system variable) and save those results on variables of their own ($getFilelimit + $getCurrentOpenFiles). Additionally we’ll get the timestamp and join it all together into a comma separated value in $logEvent.

$logData = "";

$getFilelimit= mysqli_query($conn, "SHOW STATUS LIKE 'Open_files'");
while($valueArr1 = mysqli_fetch_array($getFilelimit)){
    $logData = $valueArr1&#91;1];
}

$getCurrentOpenFiles= mysqli_query($conn, "SELECT @@open_files_limit;");
while($valueArr2 = mysqli_fetch_array($getCurrentOpenFiles)){
    $logData = $logData . "/" . $valueArr2&#91;0];
}

$logEvent = date("Y-m-d H:i:s",time()) . ";" . $logData;

$logData = "";

$getFilelimit= mysqli_query($conn, "SHOW STATUS LIKE 'Open_files'");

while($valueArr1 = mysqli_fetch_array($getFilelimit)){

$logData = $valueArr1[1];

}

$getCurrentOpenFiles= mysqli_query($conn, "SELECT @@open_files_limit;");

while($valueArr2 = mysqli_fetch_array($getCurrentOpenFiles)){

$logData = $logData . "/" . $valueArr2[0];

}

$logEvent = date("Y-m-d H:i:s",time()) . ";" . $logData;

Then we’ll write the event to a log using a custom function writeTolog().

writeToLog($logEvent);

1	writeToLog($logEvent);

Which we’ll create as follows

function writeToLog($data){
	$file = '/full/path/to/log.txt';
	
	// Open the file to get existing content
	$current = file_get_contents($file);

	// Append data
	$data = "$data\n" . $current;

	// Write the contents back to the file
	file_put_contents($file, $data);
}

function writeToLog($data){

$file = '/full/path/to/log.txt';

// Open the file to get existing content

$current = file_get_contents($file);

// Append data

$data = "$data\n" . $current;

// Write the contents back to the file

file_put_contents($file, $data);

}

And finally we’ll close our MySQL connection and output the result for good measure, in case we’ll want to browse it manually.

mysqli_free_result($getFilelimit);
mysqli_free_result($getCurrentOpenFiles);

mysqli_close($conn);

echo ("Logged: " . $logEvent);

mysqli_free_result($getFilelimit);

mysqli_free_result($getCurrentOpenFiles);

mysqli_close($conn);

echo ("Logged: " . $logEvent);

The complete script

That leaves us with a script that looks like this

&lt;?php

// ############################
// Function: WRITE TO LOG
// ############################

function writeToLog($data){
	$file = '/full/path/to/log.txt';

	// Open the file to get existing content
	$current = file_get_contents($file);

	// Append data
	$data = "$data\n" . $current;

	// Write the contents back to the file
	file_put_contents($file, $data);
}

// ############################
// CONNECT TO MYSQL
// ############################

$servername = "server.example.com";
$username = "myusername";
$password = "mypassword";
$db = "mydatabase";
	// Create connection
	$conn = mysqli_connect($servername, $username, $password, $db);
	// Check connection
	if (!$conn){
		die("Connection failed: " . mysqli_connect_error());
	}

// ############################
// Collect datapoints
// ############################

$logData = "";

$getFilelimit= mysqli_query($conn, "SHOW STATUS LIKE 'Open_files'");
while($valueArr1 = mysqli_fetch_array($getFilelimit)){
	$logData = $valueArr1&#91;1];
}

$getCurrentOpenFiles= mysqli_query($conn, "SELECT @@open_files_limit;");
while($valueArr2 = mysqli_fetch_array($getCurrentOpenFiles)){
	$logData = $logData . "/" . $valueArr2&#91;0];
}

$logEvent = date("Y-m-d H:i:s",time()) . ";" . $logData;

// ############################
// Log
// ############################

writeToLog($logEvent);

// ############################
// Close connection and output
// ############################

mysqli_free_result($getFilelimit);
mysqli_free_result($getCurrentOpenFiles);

mysqli_close($conn);

echo ("Logged: " . $logEvent);
	
?>

<?php

// ############################

// Function: WRITE TO LOG

// ############################

function writeToLog($data){

$file = '/full/path/to/log.txt';

// Open the file to get existing content

$current = file_get_contents($file);

// Append data

$data = "$data\n" . $current;

// Write the contents back to the file

file_put_contents($file, $data);

}

// ############################

// CONNECT TO MYSQL

// ############################

$servername = "server.example.com";

$username = "myusername";

$password = "mypassword";

$db = "mydatabase";

// Create connection

$conn = mysqli_connect($servername, $username, $password, $db);

// Check connection

if (!$conn){

die("Connection failed: " . mysqli_connect_error());

}

// ############################

// Collect datapoints

// ############################

$logData = "";

$getFilelimit= mysqli_query($conn, "SHOW STATUS LIKE 'Open_files'");

while($valueArr1 = mysqli_fetch_array($getFilelimit)){

$logData = $valueArr1[1];

}

$getCurrentOpenFiles= mysqli_query($conn, "SELECT @@open_files_limit;");

while($valueArr2 = mysqli_fetch_array($getCurrentOpenFiles)){

$logData = $logData . "/" . $valueArr2[0];

}

$logEvent = date("Y-m-d H:i:s",time()) . ";" . $logData;

// ############################

// Log

// ############################

writeToLog($logEvent);

// ############################

// Close connection and output

// ############################

mysqli_free_result($getFilelimit);

mysqli_free_result($getCurrentOpenFiles);

mysqli_close($conn);

echo ("Logged: " . $logEvent);

We can then browse this manually to get the currect state, or simply run it automatically as a Cron job – perhaps every 10 seconds or so. That gives us a dataset similar to this…

2020-03-03 15:59:11;1086/10000
2020-03-03 15:59:01;1073/10000
2020-03-03 15:58:51;1073/10000
2020-03-03 15:58:41;1073/10000
2020-03-03 15:58:31;1073/10000
2020-03-03 15:58:21;1075/10000
2020-03-03 15:58:11;1085/10000
2020-03-03 15:58:01;1087/10000
2020-03-03 15:57:51;1087/10000
2020-03-03 15:57:41;1087/10000
2020-03-03 15:57:31;1116/10000
2020-03-03 15:57:21;1115/10000
2020-03-03 15:57:11;1115/10000
2020-03-03 15:57:01;1115/10000
2020-03-03 15:56:51;1115/10000
2020-03-03 15:56:41;1107/10000
2020-03-03 15:56:31;1107/10000
2020-03-03 15:56:21;1107/10000
2020-03-03 15:56:11;1105/10000
2020-03-03 15:56:01;1105/10000
2020-03-03 15:55:51;1105/10000
2020-03-03 15:55:41;1105/10000
2020-03-03 15:55:31;1120/10000
2020-03-03 15:55:21;1120/10000
2020-03-03 15:55:11;1131/10000
2020-03-03 15:55:01;1146/10000
2020-03-03 15:54:51;1150/10000
2020-03-03 15:54:41;1161/10000
2020-03-03 15:54:31;1161/10000
2020-03-03 15:54:21;1159/10000
2020-03-03 15:54:11;1159/10000
2020-03-03 15:54:01;1161/10000
2020-03-03 15:53:51;1161/10000
2020-03-03 15:53:41;1161/10000
2020-03-03 15:53:31;1157/10000
2020-03-03 15:53:21;1157/10000
2020-03-03 15:53:11;1157/10000
2020-03-03 15:53:01;1157/10000
2020-03-03 15:52:51;1157/10000
2020-03-03 15:52:41;1157/10000
2020-03-03 15:52:31;1157/10000
2020-03-03 15:52:21;1161/10000
2020-03-03 15:52:11;1163/10000
2020-03-03 15:52:01;1165/10000
2020-03-03 15:51:51;1167/10000
2020-03-03 15:51:41;1169/10000
2020-03-03 15:51:31;1169/10000
2020-03-03 15:51:21;1170/10000
2020-03-03 15:51:11;1170/10000
2020-03-03 15:51:01;1171/10000
2020-03-03 15:50:51;1171/10000
2020-03-03 15:50:41;1173/10000
2020-03-03 15:50:31;1173/10000
2020-03-03 15:50:21;1173/10000
2020-03-03 15:50:11;1195/10000
2020-03-03 15:50:01;1195/10000
2020-03-03 15:49:51;1197/10000
2020-03-03 15:49:41;1197/10000
2020-03-03 15:49:31;1201/10000
2020-03-03 15:49:21;1221/10000
2020-03-03 15:49:11;1231/10000
2020-03-03 15:49:01;1232/10000
2020-03-03 15:48:51;1231/10000
2020-03-03 15:48:41;1303/10000
2020-03-03 15:48:31;1305/10000
2020-03-03 15:48:21;1335/10000
2020-03-03 15:48:11;1336/10000
2020-03-03 15:48:01;1335/10000
2020-03-03 15:47:52;1335/10000
2020-03-03 15:47:42;1335/10000
2020-03-03 15:47:32;1345/10000
2020-03-03 15:47:22;1345/10000
2020-03-03 15:47:12;1345/10000
2020-03-03 15:47:02;1347/10000
2020-03-03 15:46:51;1347/10000
2020-03-03 15:46:41;1351/10000
2020-03-03 15:46:31;1377/10000
2020-03-03 15:46:21;1397/10000
2020-03-03 15:46:11;1369/10000
2020-03-03 15:46:01;1346/10000
2020-03-03 15:45:51;1364/10000
2020-03-03 15:45:41;1364/10000
2020-03-03 15:45:31;1364/10000
2020-03-03 15:45:21;1368/10000
2020-03-03 15:45:11;1368/10000
2020-03-03 15:45:01;1368/10000
2020-03-03 15:44:51;1368/10000
2020-03-03 15:44:41;1368/10000
2020-03-03 15:44:31;1368/10000
2020-03-03 15:44:21;1368/10000
2020-03-03 15:44:11;1360/10000
2020-03-03 15:44:01;1352/10000
2020-03-03 15:43:51;1340/10000
2020-03-03 15:43:41;1345/10000
2020-03-03 15:43:31;1353/10000
2020-03-03 15:43:21;1357/10000
2020-03-03 15:43:11;1357/10000
2020-03-03 15:43:01;1350/10000
2020-03-03 15:42:51;1351/10000
2020-03-03 15:42:41;1342/10000
2020-03-03 15:42:31;1327/10000
2020-03-03 15:42:21;1327/10000
2020-03-03 15:42:11;1327/10000
2020-03-03 15:42:01;1295/10000
2020-03-03 15:41:51;1295/10000
2020-03-03 15:41:41;1295/10000
2020-03-03 15:41:31;1295/10000
2020-03-03 15:41:21;1295/10000
2020-03-03 15:41:11;1296/10000
2020-03-03 15:41:01;1309/10000
2020-03-03 15:40:51;1305/10000
2020-03-03 15:40:41;1305/10000
2020-03-03 15:40:31;1305/10000
2020-03-03 15:40:21;1305/10000
2020-03-03 15:40:11;1305/10000
2020-03-03 15:40:01;1305/10000
2020-03-03 15:39:51;1305/10000
2020-03-03 15:39:41;1305/10000
2020-03-03 15:39:31;1293/10000
2020-03-03 15:39:21;1293/10000
2020-03-03 15:39:11;1293/10000
2020-03-03 15:39:01;1313/10000
2020-03-03 15:38:51;1313/10000
2020-03-03 15:38:41;1313/10000
2020-03-03 15:38:31;1313/10000
2020-03-03 15:38:21;1313/10000
2020-03-03 15:38:11;1313/10000
2020-03-03 15:38:01;1312/10000
2020-03-03 15:37:51;1310/10000
2020-03-03 15:37:41;1298/10000
2020-03-03 15:37:31;1298/10000
2020-03-03 15:37:21;1298/10000
2020-03-03 15:37:11;1298/10000
2020-03-03 15:37:01;1298/10000
2020-03-03 15:36:51;1298/10000
2020-03-03 15:36:41;1321/10000
2020-03-03 15:36:31;1323/10000
2020-03-03 15:36:21;1331/10000
2020-03-03 15:36:11;1331/10000
2020-03-03 15:36:01;1331/10000
2020-03-03 15:35:51;1331/10000
2020-03-03 15:35:41;1335/10000
2020-03-03 15:35:31;1337/10000
2020-03-03 15:35:21;1335/10000
2020-03-03 15:35:11;1337/10000
2020-03-03 15:35:01;1337/10000
2020-03-03 15:34:51;1337/10000
2020-03-03 15:34:41;1337/10000
2020-03-03 15:34:31;1337/10000
2020-03-03 15:34:21;1337/10000
2020-03-03 15:34:11;1305/10000
2020-03-03 15:34:01;1305/10000
2020-03-03 15:33:52;1304/10000
2020-03-03 15:33:42;1268/10000
2020-03-03 15:33:32;1266/10000
2020-03-03 15:33:22;1267/10000
2020-03-03 15:33:12;1269/10000
2020-03-03 15:33:02;1267/10000
2020-03-03 15:32:51;1267/10000
2020-03-03 15:32:41;1267/10000
2020-03-03 15:32:31;1270/10000
2020-03-03 15:32:21;1270/10000
2020-03-03 15:32:11;1270/10000
2020-03-03 15:32:01;1271/10000
2020-03-03 15:31:51;1283/10000
2020-03-03 15:31:41;1301/10000
2020-03-03 15:31:31;1301/10000
2020-03-03 15:31:21;1272/10000
2020-03-03 15:31:11;1272/10000
2020-03-03 15:31:01;1272/10000
2020-03-03 15:30:51;1272/10000
2020-03-03 15:30:41;1272/10000
2020-03-03 15:30:31;1256/10000
2020-03-03 15:30:21;1256/10000
2020-03-03 15:30:11;1255/10000
2020-03-03 15:30:01;1254/10000
2020-03-03 15:29:51;1254/10000
2020-03-03 15:29:41;1254/10000
2020-03-03 15:29:31;1254/10000
2020-03-03 15:29:21;1254/10000
2020-03-03 15:29:11;1254/10000
2020-03-03 15:29:01;1262/10000
2020-03-03 15:28:51;1264/10000
2020-03-03 15:28:41;1264/10000
2020-03-03 15:28:31;1264/10000
2020-03-03 15:28:21;1264/10000
2020-03-03 15:28:11;1264/10000
2020-03-03 15:28:01;1264/10000
2020-03-03 15:27:51;1264/10000
2020-03-03 15:27:41;1266/10000
2020-03-03 15:27:31;1266/10000
2020-03-03 15:27:21;1266/10000
2020-03-03 15:27:11;1266/10000
2020-03-03 15:27:01;1266/10000
2020-03-03 15:26:51;1266/10000
2020-03-03 15:26:41;1265/10000
2020-03-03 15:26:31;1264/10000
2020-03-03 15:26:21;1264/10000
2020-03-03 15:26:11;1264/10000
2020-03-03 15:26:01;1264/10000
2020-03-03 15:25:51;1266/10000
2020-03-03 15:25:41;1266/10000
2020-03-03 15:25:31;1266/10000
2020-03-03 15:25:21;1266/10000
2020-03-03 15:25:11;1266/10000
2020-03-03 15:25:01;1268/10000
2020-03-03 15:24:51;1266/10000
2020-03-03 15:24:41;1180/10000
2020-03-03 15:24:31;987/10000
2020-03-03 15:24:21;987/10000
2020-03-03 15:24:11;992/10000
2020-03-03 15:24:01;992/10000
2020-03-03 15:23:51;992/10000

Which we can then present in the form we please. The simplest being imported into excel of course and then made into a chart.

You may encounter certain situation where Excel, Word or the interaction between the two, are causing some weird errors. One of the most generic being:

There was a problem sending the command to the program in MS Office

In my experience, this is often caused by corrupt formatting data in the registry or in the document template(s). Clearing all these settings, sometimes solve this problem.

The problems are with HKCU\Software\Microsoft\Office\<version>\<application>\Options as well as HKCU\Software\Microsoft\Office\<version>\<application>\Data – where version is the office version you have installed (i.e. 12.0 or 14.0) and the application is either word or excel.

Solving the problem

Note: Remember to close Word and Excel before you attempt this. And obviously, as always, you need to run this from an elevated command prompt.

In this example I’m just going to clear the settings for all office versions (since XP) in one go. May as well, as that just gives us a single script to run, no matter the version. And since it will just fail and move on, if the entry doesn’t exist, it doesn’t really matter.

echo Clearing Format and Data from registry...

REG DELETE "HKCU\Software\Microsoft\Office\10.0\Word\Options"
REG DELETE "HKCU\Software\Microsoft\Office\11.0\Word\Options"
REG DELETE "HKCU\Software\Microsoft\Office\12.0\Word\Options"
REG DELETE "HKCU\Software\Microsoft\Office\14.0\Word\Options"
REG DELETE "HKCU\Software\Microsoft\Office\15.0\Word\Options"
REG DELETE "HKCU\Software\Microsoft\Office\16.0\Word\Options"

REG DELETE "HKCU\Software\Microsoft\Office\10.0\Word\Data"
REG DELETE "HKCU\Software\Microsoft\Office\11.0\Word\Data"
REG DELETE "HKCU\Software\Microsoft\Office\12.0\Word\Data"
REG DELETE "HKCU\Software\Microsoft\Office\14.0\Word\Data"
REG DELETE "HKCU\Software\Microsoft\Office\15.0\Word\Data"
REG DELETE "HKCU\Software\Microsoft\Office\16.0\Word\Data"

REG DELETE "HKCU\Software\Microsoft\Office\10.0\Excel\Options"
REG DELETE "HKCU\Software\Microsoft\Office\11.0\Excel\Options"
REG DELETE "HKCU\Software\Microsoft\Office\12.0\Excel\Options"
REG DELETE "HKCU\Software\Microsoft\Office\14.0\Excel\Options"
REG DELETE "HKCU\Software\Microsoft\Office\15.0\Excel\Options"
REG DELETE "HKCU\Software\Microsoft\Office\16.0\Excel\Options"

REG DELETE "HKCU\Software\Microsoft\Office\10.0\Excel\Data"
REG DELETE "HKCU\Software\Microsoft\Office\11.0\Excel\Data"
REG DELETE "HKCU\Software\Microsoft\Office\12.0\Excel\Data"
REG DELETE "HKCU\Software\Microsoft\Office\14.0\Excel\Data"
REG DELETE "HKCU\Software\Microsoft\Office\15.0\Excel\Data"
REG DELETE "HKCU\Software\Microsoft\Office\16.0\Excel\Data"

echo Clearing Format and Data from registry...

REG DELETE "HKCU\Software\Microsoft\Office\10.0\Word\Options"

REG DELETE "HKCU\Software\Microsoft\Office\11.0\Word\Options"

REG DELETE "HKCU\Software\Microsoft\Office\12.0\Word\Options"

REG DELETE "HKCU\Software\Microsoft\Office\14.0\Word\Options"

REG DELETE "HKCU\Software\Microsoft\Office\15.0\Word\Options"

REG DELETE "HKCU\Software\Microsoft\Office\16.0\Word\Options"

REG DELETE "HKCU\Software\Microsoft\Office\10.0\Word\Data"

REG DELETE "HKCU\Software\Microsoft\Office\11.0\Word\Data"

REG DELETE "HKCU\Software\Microsoft\Office\12.0\Word\Data"

REG DELETE "HKCU\Software\Microsoft\Office\14.0\Word\Data"

REG DELETE "HKCU\Software\Microsoft\Office\15.0\Word\Data"

REG DELETE "HKCU\Software\Microsoft\Office\16.0\Word\Data"

REG DELETE "HKCU\Software\Microsoft\Office\10.0\Excel\Options"

REG DELETE "HKCU\Software\Microsoft\Office\11.0\Excel\Options"

REG DELETE "HKCU\Software\Microsoft\Office\12.0\Excel\Options"

REG DELETE "HKCU\Software\Microsoft\Office\14.0\Excel\Options"

REG DELETE "HKCU\Software\Microsoft\Office\15.0\Excel\Options"

REG DELETE "HKCU\Software\Microsoft\Office\16.0\Excel\Options"

REG DELETE "HKCU\Software\Microsoft\Office\10.0\Excel\Data"

REG DELETE "HKCU\Software\Microsoft\Office\11.0\Excel\Data"

REG DELETE "HKCU\Software\Microsoft\Office\12.0\Excel\Data"

REG DELETE "HKCU\Software\Microsoft\Office\14.0\Excel\Data"

REG DELETE "HKCU\Software\Microsoft\Office\15.0\Excel\Data"

REG DELETE "HKCU\Software\Microsoft\Office\16.0\Excel\Data"

Also deleting templates…

Deleting your document templates and any temporary cached versions of these, may also be a good idea.

del /F /Q %appData%\Microsoft\Templates\Normal.dotm
del /F /Q %appData%\Microsoft\Templates\~$Normal.dotm
del /F /Q %appData%\Microsoft\Templates\Normal1.dotm
del /F /Q %appData%\Microsoft\Templates\~$Normal1.dotm
del /F /Q %appData%\Microsoft\Templates\Normal11.dotm
del /F /Q %appData%\Microsoft\Templates\~$Normal11.dotm
del /F /Q %appData%\Microsoft\Word\Startup\Common.dotm
del /F /Q %appData%\Microsoft\Word\Startup\~$Common.dotm
del /F /Q %appData%\Microsoft\Word\Startup\common.dot
del /F /Q %appData%\Microsoft\Word\Startup\~$common.dot

del /F /Q %appData%\Microsoft\Templates\Normal.dotm

del /F /Q %appData%\Microsoft\Templates\~$Normal.dotm

del /F /Q %appData%\Microsoft\Templates\Normal1.dotm

del /F /Q %appData%\Microsoft\Templates\~$Normal1.dotm

del /F /Q %appData%\Microsoft\Templates\Normal11.dotm

del /F /Q %appData%\Microsoft\Templates\~$Normal11.dotm

del /F /Q %appData%\Microsoft\Word\Startup\Common.dotm

del /F /Q %appData%\Microsoft\Word\Startup\~$Common.dotm

del /F /Q %appData%\Microsoft\Word\Startup\common.dot

del /F /Q %appData%\Microsoft\Word\Startup\~$common.dot

Hopefully it helps.

Monitoring MySQL open_files_limit from PHP and logging usage over time

The complete script

There was a problem sending the command to the program in MS Office

Solving the problem

Also deleting templates…