[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

mysql table field types

Posted on 2014-08-19
20
Medium Priority
?
249 Views
Last Modified: 2014-08-20
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!
0
Comment
Question by:projects
  • 12
  • 8
20 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 40271399
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
0
 

Author Comment

by:projects
ID: 40271405
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.
0
 
LVL 58

Expert Comment

by:Gary
ID: 40271417
You mean client_id
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:projects
ID: 40271418
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.
0
 

Author Comment

by:projects
ID: 40271424
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.
0
 
LVL 58

Expert Comment

by:Gary
ID: 40271430
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

0
 

Author Comment

by:projects
ID: 40271547
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

0
 
LVL 58

Expert Comment

by:Gary
ID: 40271552
Did you make the amendments above?
Post the resulting sql that should have been printed on screen.
0
 

Author Comment

by:projects
ID: 40271553
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.
0
 

Author Comment

by:projects
ID: 40271555
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? :)
0
 

Author Comment

by:projects
ID: 40271558
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.
0
 

Author Comment

by:projects
ID: 40273113
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

0
 
LVL 58

Expert Comment

by:Gary
ID: 40273402
And how should it look
0
 

Author Comment

by:projects
ID: 40273438
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
0
 
LVL 58

Expert Comment

by:Gary
ID: 40273588
Are you sure there was a connection/file available as the total time is 0.2 seconds
0
 

Author Comment

by:projects
ID: 40273761
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.
0
 

Author Comment

by:projects
ID: 40273777
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.
0
 
LVL 58

Accepted Solution

by:
Gary earned 2000 total points
ID: 40273792
You can set a max time for the connection to last

--max-time


or a connection timeout

--connect-timeout

Append the number of seconds

If the example db entries above are for a 10mb file then the speed is bps.
0
 

Author Comment

by:projects
ID: 40274220
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.
0
 
LVL 58

Expert Comment

by:Gary
ID: 40274227
Correct, I actually get 491.86kb though
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month18 days, 20 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question