Solved

xampp apache

Posted on 2012-04-12
26
605 Views
Last Modified: 2012-04-13
I have attached a php page which works perfectly in previous php / mysql installations but just refuses to work in xampp version 1.7.7
I have just migrated my website to a new 2008 windows server running xampp and the entire site works as it should except for this one page. When I access it the page just hangs for 30 seconds and I get the following message:

Fatal error: Maximum execution time of 30 seconds exceeded in C:\xampp\htdocs\OneCallOrders\1callorders\site\database\orders-reset-2weeks.php on line 14

It looks as if there is some markup error but I just cannot see it
Any ideas ?

Bill
orders-reset-2weeks.php
0
Comment
Question by:doctorbill
  • 11
  • 10
  • 5
26 Comments
 
LVL 4

Accepted Solution

by:
senseifedon earned 200 total points
ID: 37836925
max_execution_time there is an additional setting max_input_time which controls how long a php file can spend processing the request data.  When i set max_input_time to a higher value, the problem goes away. So, it appears that the error message is reporting the wrong configuration variable.

These settings are found in php.ini, however depending on your host you may be able to override them either in your script or in an .htaccess file.   After you update php.ini you may have to restart your https server process to reload the php settings.  (windows servers may need to recycle the application pool).
phpini.png
0
 

Author Comment

by:doctorbill
ID: 37836992
increasing the max_execution_time and max_input_time does not make any difference
0
 
LVL 4

Expert Comment

by:senseifedon
ID: 37837028
Did you restart apache service after changing the value? If you did, can you send the line 14?
0
 
LVL 4

Expert Comment

by:senseifedon
ID: 37837035
If you restart your apache service, you want to try this .htaccess file.
Please insert following lines to your .htaccess file.

# PHP 5, Apache 1 and 2.
<IfModule mod_php5.c>
  php_value magic_quotes_gpc                0
  php_value register_globals                0
  php_value session.auto_start              0
  php_value mbstring.http_input             pass
  php_value mbstring.http_output            pass
  php_value mbstring.encoding_translation   0
  php_value max_execution_time 120
  php_value memory_limit 128M
</IfModule>
0
 

Author Comment

by:doctorbill
ID: 37837069
Line 14:

} while ($row_orders = mysql_fetch_assoc($orders));
0
 

Author Comment

by:doctorbill
ID: 37837071
sorry - yes I did restart apache server
0
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 37837097
Hmmm - is your orders table only a temporary holding spot, or do your records stay there indefinitely? If they stay there and never get deleted, then it would make sense that this script would eventually get slower and slower and slower. You're trying to update every record in the orders table, and if you keep adding records without deleting any, then you eventually are going to run out of time. I would try this:

Change:
$query_orders = "SELECT * FROM orders";

To:
$query_orders = "SELECT * FROM orders WHERE date_unix IS NULL or date_unix = 0";

Also, make sure you have an index on the date_unix field.

You also have this section:

//get orders again since we've already cycled through them...
$query_orders = "SELECT * FROM orders";
$orders = mysql_query($query_orders, $Onecallorders) or die(mysql_error());
$row_orders = mysql_fetch_assoc($orders);
$totalRows_orders = mysql_num_rows($orders);

Open in new window


...but I don't see where any of this data is used later on in the script you attached, so I would suggest removing it unless there's a good reason for having it. Doing this:

SELECT * FROM anything

..without any WHERE clause at all to filter the results is usually a bad idea. In most cases, you don't need every single row from the table and you're going to put more load on the database for no reason.
0
 

Author Comment

by:doctorbill
ID: 37837099
If I remove this code the page works:

//first, add unix time to database
do {
$du = strtotime(str_replace("/","-",$row_orders[Date]))."<br>";
mysql_query("UPDATE orders SET date_unix = '".$du."' WHERE ID = '".$row_orders[ID]."'");
} while ($row_orders = mysql_fetch_assoc($orders));
//
0
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 37837122
doctorbill, can you paste the resulting rows from this query:

SELECT Date,date_unix,UNIX_TIMESTAMP(Date) FROM orders WHERE date_unix > 0 LIMIT 10;
0
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 37837132
Actually, also run this one and see if there's any difference:

SELECT Date,date_unix,UNIX_TIMESTAMP(REPLACE(Date,'/','-')) FROM orders WHERE date_unix > 0 LIMIT 10;
0
 

Author Comment

by:doctorbill
ID: 37837180
first query:
07/07/2010      1278453600      0
20/07/2010      1279576800      0
02/08/2010      1280700000      0
05/08/2010      1280959200      0
09/08/2010      1281304800      0
09/08/2010      1281304800      0
10/08/2010      1281391200      0
11/08/2010      1281477600      0
11/08/2010      1281477600      0
11/08/2010      1281477600      0

second query:
07/07/2010      1278453600      0
20/07/2010      1279576800      0
02/08/2010      1280700000      0
05/08/2010      1280959200      0
09/08/2010      1281304800      0
09/08/2010      1281304800      0
10/08/2010      1281391200      0
11/08/2010      1281477600      0
11/08/2010      1281477600      0
11/08/2010      1281477600      0
0
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 37837217
Okay, one more query to try:

SELECT Date,date_unix,UNIX_TIMESTAMP(CONCAT(SUBSTRING(Date,7,4),'-',SUBSTRING(Date,4,2),'-',SUBSTRING(Date,1,2))) FROM orders WHERE date_unix > 0 LIMIT 10;
0
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 37837332
Ultimately, what I'm trying to suggest is to use MySQL's own internal functions to update your UNIX timestamps. It will be a hundred times faster than trying to use PHP to pull each row, calculate the timestamp, and update the row again.  So you should be able to replace lines 5-14:
$query_orders = "SELECT * FROM orders";
$orders = mysql_query($query_orders, $Onecallorders) or die(mysql_error());
$row_orders = mysql_fetch_assoc($orders);
$totalRows_orders = mysql_num_rows($orders);

//first, add unix time to database
do {
$du = strtotime(str_replace("/","-",$row_orders[Date]))."<br>";
mysql_query("UPDATE orders SET date_unix = '".$du."' WHERE ID = '".$row_orders[ID]."'");
} while ($row_orders = mysql_fetch_assoc($orders));

Open in new window

...with one single query:
mysql_query("UPDATE orders SET date_unix=UNIX_TIMESTAMP(CONCAT(SUBSTRING(Date,7,4),'-',SUBSTRING(Date,4,2),'-',SUBSTRING(Date,1,2))) WHERE date_unix IS NULL or date_unix = 0");

Open in new window


Don't make that change yet, though! What I'm asking you to do is first run tests to make sure that we are converting the timestamp to the same value. So first we have to tell MySQL how to read the date (MySQL reads dates as YYYY-MM-DD, like "2010-04-12", so all those SUBSTRINGs are simply telling MySQL where to find the year, the month, and the day in your Date column), and then we run UNIX_TIMESTAMP to convert that date into the timestamp value.

We may need to make one more adjustment to the query to account for any timezone differences, but then you'll be able to replace that code with the final query and have it run A LOT faster.
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 4

Assisted Solution

by:senseifedon
senseifedon earned 200 total points
ID: 37837356
Please change the value in php.ini file as 0 (zero).

Max_input_time 0

Open in new window


After that you have to restart apache. I think it will work.
0
 

Author Comment

by:doctorbill
ID: 37837407
could the problem be that I have not as yet set a password for the mysql in the xampp security ?
0
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 37837500
@senseifedon -
No, no, no!!! Do NOT suggest changing the timeout settings to try to fix this! That's like trying to heal a gunshot wound by wearing a shirt so you can't see it anymore. The fact that it works when he removes the part about updating the UNIX timestamps (and the fact that it times out instead of throwing an error) goes to prove that it's a problem with too many queries/too much data.

Sorry, I do not normally try to downplay anyone else's suggestions, but that is a bad idea that will only cause more problems later on because it's ignoring the real problem.

@doctorhill -
While you SHOULD set a password for MySQL, it's not relevant to this problem right now. Right now, you have a section of very bad PHP code that is doing a lot of unnecessary updates. If you have 100,000 rows in your orders table, the PHP is updating 100,000 rows every single time you access this page. And you probably don't NEED to update 99% of those rows because they have already been updated the last time you accessed the page. So all you need to do is fix this bad code and I have been leading you down that path. You are almost at the final solution - stay with me and try that last query I sent over:

SELECT Date,date_unix,UNIX_TIMESTAMP(CONCAT(SUBSTRING(Date,7,4),'-',SUBSTRING(Date,4,2),'-',SUBSTRING(Date,1,2))) FROM orders WHERE date_unix > 0 LIMIT 10;
0
 

Author Comment

by:doctorbill
ID: 37837646
Result of query:

07/07/2010      1278453600      1278457200
20/07/2010      1279576800      1279580400
02/08/2010      1280700000      1280703600
05/08/2010      1280959200      1280962800
09/08/2010      1281304800      1281308400
09/08/2010      1281304800      1281308400
10/08/2010      1281391200      1281394800
11/08/2010      1281477600      1281481200
11/08/2010      1281477600      1281481200
11/08/2010      1281477600      1281481200

The frustrating thing is that the same database, tables, rows etc work on the old server with a php / mysql and phpmyading installation not under xampp. These installs were separate. The problem page opens up in seconds even with 9000 rows in the orders table !!
0
 
LVL 34

Assisted Solution

by:gr8gonzo
gr8gonzo earned 300 total points
ID: 37838398
Was the old server's PHP connecting to a MySQL service running on that same server? Like mysql_connect("localhost","username","password")?

If the new server's PHP is still trying to connect to the old server's MySQL, then each of your queries is taking a lot longer by having to travel over the network (one trip to get the data to PHP and one trip back to MySQL).

So remove lines 5 through 14 and add this line in its place:

mysql_query("UPDATE orders SET date_unix=UNIX_TIMESTAMP(CONCAT(SUBSTRING(Date,7,4),'-',SUBSTRING(Date,4,2),'-',SUBSTRING(Date,1,2)))-3600 WHERE date_unix IS NULL or date_unix = 0");

Open in new window

0
 

Author Comment

by:doctorbill
ID: 37839038
This is the connection file:

<?php
# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_Onecallorders = "localhost";
$database_Onecallorders = "1callorders";
$username_Onecallorders = "root";
$password_Onecallorders = "";
$Onecallorders = mysql_pconnect($hostname_Onecallorders, $username_Onecallorders, $password_Onecallorders) or trigger_error(mysql_error(),E_USER_ERROR);
?>
0
 

Author Comment

by:doctorbill
ID: 37839172
Ooops - sorry:

I just noticed the "Max_input_time 0" comment you made above

It now works !!!
0
 
LVL 4

Expert Comment

by:senseifedon
ID: 37839248
Wow. I'm happy to solve your problem. :))
0
 
LVL 34

Assisted Solution

by:gr8gonzo
gr8gonzo earned 300 total points
ID: 37839385
@doctorbill,

I would strongly advise you to make the change I recommended so you are not putting additional, unnecessary strain on your database. If you "fix" it by changing the timeouts and time limits, you are not really fixing the core problem - you are just hiding it. Not only is it a simple fix, but it is scalable, so you don't have to worry about changing the script later.

I regularly tell people not to try to fix problems by changing time limits, because 95% of all scripts do not take 30 seconds or more to run. You could have also fixed it by adding set_time_limit(0) to the top instead of changing the ini file, but I did not recommend that because it's not fixing the problem - it is only hiding it. The script does not need to pull all those records and update them all (which also locks those records so they temporarily cannot be used by other pages).
0
 
LVL 34

Assisted Solution

by:gr8gonzo
gr8gonzo earned 300 total points
ID: 37839434
To explain it another way, imagine you have a fence made out of 100 sticks that you've painted white. Now let's say you extend that fence and add another 5 sticks, and now you want to paint them white, too. What your script is currently doing is pulling all 105 sticks out of the ground, and then painting all 105 (even though 100 were already painted and you only needed to paint 5), and then you're putting them back into the ground. And every time you run that script, it's pulling out all of the sticks again, painting them all over again (even if they're already painted), and putting them back. As the fence grows, it's going to take longer and longer to paint everything again.

What I'm trying to get you to do is change that code so that instead of pulling everything out, repainting all of it, and putting it all back in, all you're doing is looking ONLY for the 5 sticks that need to be painted, and then painting them without pulling them out and putting them back in again.

I hate to keep repeating this in different ways, but it's a major coding flaw, and it's going to cause problems in the future if you don't fix it. I'll shut up now and move on.
0
 

Author Closing Comment

by:doctorbill
ID: 37840324
Point taken - thanks
0
 

Author Comment

by:doctorbill
ID: 37841776
I just need to say a BIG THANK YOU for the following script:

mysql_query("UPDATE orders SET date_unix=UNIX_TIMESTAMP(CONCAT(SUBSTRING(Date,7,4),'-',SUBSTRING(Date,4,2),'-',SUBSTRING(Date,1,2))) WHERE date_unix IS NULL or date_unix = 0");

It works perfectly and takes seconds - amazing !!!!!!!
0
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 37841979
Hi doctorbill,

Just a quick note - per the query in my last comment (37838398), that query SHOULD have a -3600 right after the last parentheses:

...Date,4,2),'-',SUBSTRING(Date,1,2))) WHERE date_unix....
should be:
...Date,4,2),'-',SUBSTRING(Date,1,2)))-3600 WHERE date_unix....

Otherwise your new timestamps will be an hour off (which could cause push the timestamps into the next day).

I suggest running the below query ONCE to re-calculate all of the timestamps for orders since the beginning of April 1st, 2012 just to be safe and make sure all of your recent orders have the correct timestamp:
mysql_query("UPDATE orders SET date_unix=UNIX_TIMESTAMP(CONCAT(SUBSTRING(Date,7,4),'-',SUBSTRING(Date,4,2),'-',SUBSTRING(Date,1,2)))-3600 WHERE date_unix > 1333256400");

Open in new window

Just to explain the -3600 part, the MySQL server's timezone is basically one hour different from PHP's. It's just the way your system is set up. So the -3600 simply subtracts the 3600 seconds (1 hour) from the final timestamp in order to correct the final number.

If you look at those previous queries I had you run, you'll see this:

07/07/2010      1278453600      1278457200

So PHP turned 07/07/2010 into 1278453600 while MySQL turned it into 1278457200. In other words, MySQL added an extra 3600 seconds. To make it match, we just have MySQL subtract 3600 seconds, and the final number matches PHP's, and all the rest of your system will continue working perfectly.

After you run that above query once, take it out of the system. It won't harm anything if you run it again, but there's no reason to do it and you should never run queries that don't have a purpose. :)
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now