Link to home
Start Free TrialLog in
Avatar of projects
projects

asked on

mysql table field types

I want to run a bandwidth test on occasion from one of my test scripts. This will simply help me get some really basic info on how some of our networks are doing.

Script side;

function band_test()
{
        echo "Doing bandwidth Test"
        BAND_TIME="$(date +"%F %T")"
        CSTATS=`curl -w '%{speed_download}\t%{time_namelookup}\t%{time_total}\n' -o /dev/null -s http://server.com/file`
        SPEED=`echo $CSTATS | awk '{print $1}' | sed 's/\..*//'`
        DNS_TIME=`echo $CSTATS | awk '{print $2}'`
        TOTAL_TIME=`echo $CSTATS | awk '{print $3}'`
        $CURL -F function=band_test -F band_time="$BAND_TIME" -F speed="$SPEED" -F dns_time="$DNS_TIME" -F total_time="$TOTAL_TIME"

}


Php Side;

                        elseif ($_POST['function'] === 'bandwidth_monitor') {
                                mysql_query('INSERT INTO band_logs ( band_time, speed, dns_time, total_time, client_id ) VALUES ("' . $_POST['band_time'] . '","' . $_POST
['speed'] . '","' . $_POST['dns_time'] . '","' . $_POST['total_time'] . '","' . $clientid . '")' )
                or die('Query failed: ' . mysql_error());
                        }
                }

Mysql;

  	Field name    	Type    	Allow nulls?    	Key    	Default value    	Extras   
	id 	int(10) unsigned 	No 	Primary 	NULL 	auto_increment
	speed 	int(10) unsigned 	No 	None 	NULL 	
	dns_time 	float(10,4) 	No 	None 	NULL 	
	total_time 	float(10,4) 	No 	None 	NULL 	
	band_time 	datetime 	No 	None 	NULL

Open in new window


When I wasn't using these field types, I was testing using varchar and everything worked fine.
Now, nothing is being entered into the db but I've been starring at it so long that I am probably not seeing the obvious.

I also want to change 'speed' to 'bps' for bytes per second

Help!
Avatar of Gary
Gary
Flag of Ireland image

Add error reporting to your page, also before your query line

mysql_query('INSERT INTO ...

add
echo('INSERT INTO band_logs ( band_time, speed, dns_time, total_time, client_id ) VALUES ("' . $_POST['band_time'] . '","' . $_POST
 ['speed'] . '","' . $_POST['dns_time'] . '","' . $_POST['total_time'] . '","' . $clientid . '")' )

Open in new window

...so you can check what values are being inserted, it's probably a case of entering characters in a numbers field
Avatar of projects
projects

ASKER

Actually, I have some typos in my message but I can't edit it anymore.
There is a mismatch of table names which doesn't exist in the real code.
You mean client_id
No, if you look at the function names, there's a mismatch but it's not mismatched in my code.
Anyhow, nothing is getting into the table.
I tried adding your error reporting but that kept giving me errors. Can you just post the line exactly into the code I posted? Then I'll just cut and paste it with less chance of errors.

Also, just overlook the client_id, that's just another routine in the php code to know which script is sending stuff since I have a few.
Did you post your code exactly as you have it in your php page as there appears to be a space between $_POST and ['speed']

At the top of your php page after the opening <?php tag add

error_reporting(E_ALL);

elseif ($_POST['function'] === 'bandwidth_monitor') {
	echo('INSERT INTO band_logs ( band_time, speed, dns_time, total_time, client_id ) VALUES ("' . $_POST['band_time'] . '","' . $_POST['speed'] . '","' . $_POST['dns_time'] . '","' . $_POST['total_time'] . '","' . $clientid . '")' );
	mysql_query('INSERT INTO band_logs ( band_time, speed, dns_time, total_time, client_id ) VALUES ("' . $_POST['band_time'] . '","' . $_POST['speed'] . '","' . $_POST['dns_time'] . '","' . $_POST['total_time'] . '","' . $clientid . '")' )
	or die('Query failed: ' . mysql_error());
}

Open in new window

This is what it looks like;


function band_test()
{
        echo "Doing bandwidth Test"

        BAND_TIME="$(date +"%F %T")"
        CSTATS=`curl -w '%{speed_download}\t%{time_namelookup}\t%{time_total}\n' -o /dev/null -s http://servre.com/file`
        SPEED=`echo $CSTATS | awk '{print $1}' | sed 's/\..*//'`
        DNS_TIME=`echo $CSTATS | awk '{print $2}'`
        TOTAL_TIME=`echo $CSTATS | awk '{print $3}'`
        $CURL -F function=band_test -F band_time="$BAND_TIME" -F speed="$SPEED" -F dns_time="$DNS_TIME" -F total_time="$TOTAL_TIME"
}

Open in new window


                        elseif ($_POST['function'] === 'band_test') {
                                 mysql_query('INSERT INTO bandwidth_monitor ( band_time, speed, dns_time, total_time, client_id ) VALUES ("' . $_POST['band_time'] . '","'
 . $_POST['speed'] . '","' . $_POST['dns_time'] . '","' . $_POST['total_time'] . '","' . $clientid . '")' )
                or die('Query failed: ' . mysql_error());
                        }
                }

Open in new window

Did you make the amendments above?
Post the resulting sql that should have been printed on screen.
it's just a php page so the only way to see the output would be to send it to a file.

However, I seem to have gotten it working now. There was a field missing in the database table.
It's all working now so I'm not sure how to award. Can I award just because you were good enough to try and help? :)
This is storing in bytes per second right?
Also, can you tell me how to add a timeout to curl while we're at this?
When dns doesn't respond in time, I don't want the script stalled, I just want curl to time out and try again later.
This morning, the data in the database looks different than as I left it last night.

 

band_time 	                bytesps     dns_time   total_time
2014-08-20 01:34:16 	720686 	  0.0030 	     14.5500
2014-08-20 01:34:30 	1111732    0.0030         9.4320
2014-08-20 01:34:43 	2425414    0.0120         4.3230
2014-08-20 03:41:50 	758 	          0.0020 	       0.2780     <changes here...
2014-08-20 03:45:45 	849 	          0.0020 	       0.2480
2014-08-20 04:23:31 	1531 	  0.0020 	       0.1380
2014-08-20 04:29:36 	867 	          0.0020 	       0.2430

Open in new window

And how should it look
Well, it doesn't look right to me...

Look at where it changes overnight... goes from 2425414 bytes per second to 758 and in LESS time!
That's not possible :). It's a 10MB file I am using for testing.

2014-08-20 01:34:43       2425414    0.0120         4.3230
2014-08-20 03:41:50       758                 0.0020              0.2780
Are you sure there was a connection/file available as the total time is 0.2 seconds
Yes, the file is there. I did have dns issues yesterday when one of the fibre storage units died but I got that back up and dns is fine now so it's not that.
Ooops, found the problem. A variable calling the file name (for different sizes) was messed up so the file was unreachable.

Thanks for the lead :)

If you think you can answer my previous question, I'll be happy to award you this solution.

This is storing in bytes per second right?

Also, can you tell me how to add a timeout to curl while we're at this?
When dns doesn't respond in time, I don't want the script stalled, I just want curl to time out and try again later.
ASKER CERTIFIED SOLUTION
Avatar of Gary
Gary
Flag of Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
curl --max-time 5 -w '%{speed_download}\t%{time_namelookup}\t%{time_total}\n' -o /dev/null -s http://server.com/file

Ok, bits per second then, thanks.
So, for example, 3934921 bits is my result so 480.3KB is the actual speed.
Correct, I actually get 491.86kb though