World Data, long execution time

Anterrabae

Guest
Hey all,

I'm trying to develop some tools for my alliance (if it works out well, and I get real hosting, I'll make it public), and my first step was to download some town data. I used the example I found, but kept getting errors with execution time, so I incrementally increased it with ini_set(). I tried 5 minutes, 10 minutes, 20 minutes, but at 30 minutes it finally finishes and the data is there.

Can anyone tell me if there is an easier way to do it, that will take less time? I was wanting to update hourly, but if its going to take 30 minutes to execute, it would seem more logical to do it ever 2-4 hours. Can anyone give me a pointer towards updating a DB more quickly?

Code:
$dbh = mysql_connect ("localhost", "grepo_claims",
"fakepass") or die('Cannot connect to the database because: ' . mysql_error());
mysql_select_db ("grepo_claims");
ini_set("memory_limit","100M");
ini_set('max_execution_time', 1800);
mysql_query("Truncate Table towns");
$datafile = gzfile('http://en30.grepolis.com/data/towns.txt.gz');
if(!is_array($datafile)) die("File could not be opened");

foreach($datafile as $datum){
	list($id, $player_id, $name, $island_x, $island_y, $number_on_island, $points) = explode(',', $datum);

	If (!isset($id)){ $id=0; }
	If (!isset($player_id)){ $player_id=0; }
	If (!isset($name)){ $name=0; }
	If (!isset($points)){ $points=0; }
	$INSERT = "INSERT INTO `towns` (`id`, `player_id`, `name`, `island_x`, `island_y`, `number_on_island`, `points`) VALUES ('$id', '$player_id', '$name', '$island_x', '$island_y', '$number_on_island', '$points')";

	$result = mysql_query("$INSERT");
	if (!$result) {
	print"$id, $player_id, $name, $island_x, $island_y, $number_on_island, $points <br>";
		print"$INSERT <br>";
		die('Invalid query: ' . mysql_error());
	}

}
print "DB Updated Successfully";
mysql_close($dbh);
 

Ac04

Strategos
Hey all,

I'm trying to develop some tools for my alliance (if it works out well, and I get real hosting, I'll make it public), and my first step was to download some town data. I used the example I found, but kept getting errors with execution time, so I incrementally increased it with ini_set(). I tried 5 minutes, 10 minutes, 20 minutes, but at 30 minutes it finally finishes and the data is there.

Can anyone tell me if there is an easier way to do it, that will take less time? I was wanting to update hourly, but if its going to take 30 minutes to execute, it would seem more logical to do it ever 2-4 hours. Can anyone give me a pointer towards updating a DB more quickly?

Code:
$dbh = mysql_connect ("localhost", "grepo_claims",
"fakepass") or die('Cannot connect to the database because: ' . mysql_error());
mysql_select_db ("grepo_claims");
ini_set("memory_limit","100M");
ini_set('max_execution_time', 1800);
mysql_query("Truncate Table towns");
$datafile = gzfile('http://en30.grepolis.com/data/towns.txt.gz');
if(!is_array($datafile)) die("File could not be opened");

foreach($datafile as $datum){
	list($id, $player_id, $name, $island_x, $island_y, $number_on_island, $points) = explode(',', $datum);

	If (!isset($id)){ $id=0; }
	If (!isset($player_id)){ $player_id=0; }
	If (!isset($name)){ $name=0; }
	If (!isset($points)){ $points=0; }
	$INSERT = "INSERT INTO `towns` (`id`, `player_id`, `name`, `island_x`, `island_y`, `number_on_island`, `points`) VALUES ('$id', '$player_id', '$name', '$island_x', '$island_y', '$number_on_island', '$points')";

	$result = mysql_query("$INSERT");
	if (!$result) {
	print"$id, $player_id, $name, $island_x, $island_y, $number_on_island, $points <br>";
		print"$INSERT <br>";
		die('Invalid query: ' . mysql_error());
	}

}
print "DB Updated Successfully";
mysql_close($dbh);

Bulk inserts should do the job, like the following. Please note that you may have to use array_chunk for this, since on one of my servers, we have a limit on how much can be inserted at a time.
PHP:
<?php
	$dbh = mysql_select_db("grepo_claims", mysql_connect ("localhost", "grepo_claims","fakepass")) or die('Cannot connect to the database because: ' . mysql_error());
	ini_set("memory_limit","100M"); 
	ini_set('max_execution_time', 1800); // should now be unnecessary - should take no longer than 20 seconds per world
	mysql_query("Truncate Table towns");
	$datafile = gzfile('http://en30.grepolis.com/data/towns.txt.gz');
	if(!is_array($datafile)) die("File could not be opened");

	$INSERT = "INSERT INTO `towns` (`id`, `player_id`, `name`, `island_x`, `island_y`, `number_on_island`, `points`) VALUES ";
	foreach($datafile as $datum){
		list($id, $player_id, $name, $island_x, $island_y, $number_on_island, $points) = explode(',', $datum);

		if (!isset($id)){ $id=0; }
		if (!isset($player_id)){ $player_id=0; }
		if (!isset($name)){ $name=0; }
		if (!isset($points)){ $points=0; }
		$INSERT = "('$id', '$player_id', '$name', '$island_x', '$island_y', '$number_on_island', '$points'),";
	}
	$result = mysql_query("$INSERT");
	if (!$result) {
		die('Invalid query: ' . mysql_error());
	}
	print "DB Updated Successfully";
	mysql_close($dbh);
?>
Also, please note that previously you were printing a line for each query you were executing. This is time consuming, and will be a part of what has been making things take ages.

Let me know if this helps, and ask any questions if you're not sure what's going on.
 
Last edited:

Anterrabae

Guest
Thanks! Was using the example, I was under the impression the print would only occur on an error, but it really wasn't necessary anyway.

I'll test it and let you know if I run into any issues.
 

Ac04

Strategos
Oh, my bad... yeah, I didn't read the code properly - doing too many things at the same time.

Yeah, the print would only have been on an error, which begs the question of why it was taking so long.

What server is it running on?

Either way, bulk inserts should make it faster.
 

Anterrabae

Guest
Its a WAMP server on my PC for testing atm, I figured it'd be a bit slow, but its tons worse than I expected. Also, what seems strange is php.exe isn't running while the page is stalled, mysqld is using minimal resources as is httpd. I'll try running the script from command line to see if its the web server causing issues.
 

Ac04

Strategos
Is it still running slowly? If so, could you give me some timings, so I can further look for a better solution for you.
 

Anterrabae

Guest
I ran the script via command prompt, it ran for around 10 seconds, then ended. I checked the table and it had a few thousand rows. I refreshed, and it had a bit more. It took around 25 minutes or so for it to finish. I suppose the queries are being printed out, and then sent to mysql for processing. My confusion is that when run via the web server, it eventually comes back with the error about time (assuming I kept the default max_execution_time of 30 seconds) and it only processes ~300 rows. If when I run the script from the command prompt and it sends all of the queries to mysql and it sits in queue, why doesn't it do the same when run via the web server?

As for your request, what do you mean by timings? The how long it is taking to run?
 

Ac04

Strategos
The reason that command line didn't time out is because php cli (command line interface) uses a different php.ini file to normal php, and there is no limit set on the max_execution_time for cli.

Yes, by timings, I mean how long it's taking to run.

These timings you've given me earlier today, are they for the batch inserts or for the original script?
 

Anterrabae

Guest
Well, the modified script you gave me gives out an error because you were overriding the first half of the $INSERT query (Insert into...) with the values in the foreach loop. If I try to separate them it only produces 1 row.

I think I understand what you're trying to do, create one very long query that looks like:

INSERT into Table (column1, column2...) VALUES ('value1', 'value2'), ('value3', 'value4'), ...

Right?

I've been able to get it into 1 large query (I only need a few columns, so I figured I'd take out the extras since I don't need them)

PHP:
    $dbh = mysql_select_db("grepo_claims", mysql_connect ("localhost", "grepo_claims","fakepass")) or die('Cannot connect to the database because: ' . mysql_error());
    ini_set("memory_limit","100M"); 
    ini_set('max_execution_time', 1800); // should now be unnecessary - should take no longer than 20 seconds per world
    mysql_query("Truncate Table towns");
    $datafile = gzfile('http://en30.grepolis.com/data/towns.txt.gz');
    if(!is_array($datafile)) die("File could not be opened");
	 $INSERT = "INSERT INTO `towns` (`id`, `player_id`, `name`, `points`) VALUES ";
	 $VALUES = "";
   foreach($datafile as $datum){
        list($id, $player_id, $name, $points) = explode(',', $datum);

        if (!isset($id)){ $id=0; }
        if (!isset($player_id)){ $player_id=0; }
        if (!isset($name)){ $name=0; }
        if (!isset($points)){ $points=0; }
        $VALUES .= "('$id', '$player_id', '$name', '$points'), ";
    }
    $result = mysql_query("$INSERT" . "$VALUES");
    if (!$result) {
        die('Invalid query: ' . mysql_error() . $INSERT . $VALUES);
    }
    print "DB Updated Successfully";
    mysql_close($dbh);
The problem is, I received an error stating the max_allowed_packets was too small, so I increased it to 32M in mysql. Now it runs, but I receive an error that my MySQL query has an error. The only thing I could think of is the trailing comma at the very end.

So, I updated my script to this:

PHP:
 $dbh = mysql_select_db("grepo_claims", mysql_connect ("localhost", "grepo_claims","fakepass")) or die('Cannot connect to the database because: ' . mysql_error());
    ini_set("memory_limit","100M"); 
    ini_set('max_execution_time', 1800); // should now be unnecessary - should take no longer than 20 seconds per world
    mysql_query("Truncate Table towns");
    $datafile = gzfile('http://en30.grepolis.com/data/towns.txt.gz');
    if(!is_array($datafile)) die("File could not be opened");
	 $INSERT = "INSERT INTO `towns` (`id`, `player_id`, `name`, `points`) VALUES ";
	 $VALUES = "";
    foreach($datafile as $datum){
        list($id, $player_id, $name, $points) = explode(',', $datum);

        if (!isset($id)){ $id=0; }
        if (!isset($player_id)){ $player_id=0; }
        if (!isset($name)){ $name=0; }
        if (!isset($points)){ $points=0; }
	if ($VALUES != ""){
	    $VALUES .= ", ";
	}
        $VALUES .= "('$id', '$player_id', '$name', '$points')";
    }
    $result = mysql_query("$INSERT" . "$VALUES");
    if (!$result) {
        die('Invalid query: ' . mysql_error() . $INSERT . $VALUES);
    }
    print "DB Updated Successfully";
    mysql_close($dbh);
It works! The script returns successful, and my DB updates. However, I now get this warning, any idea on what went wrong?

Code:
Warning: mysql_close() expects parameter 1 to be resource, boolean given
If the query returned an error, it should've come back as an error. So why is the handle a boolean?
 
Last edited by a moderator:

Ac04

Strategos
Sorry, yes I meant to concatenate onto $INSERT, rather than overwrite it.

Also, to remove the trailing comma, you could have done the following before inserting into the db.

$INSERT = substr($INSERT, 0, -1);

This will remove the final character from the end of the string.

Also, the reason for the handle being a boolean is because I've combined the mysql_select_db() and mysql_connect() lines into one. mysql_select_db() returns a boolean (http://php.net/manual/en/function.mysql-select-db.php). You'll have to separate these two lines out again if you want to call a mysql_close(). Otherwise you can call mysql_close() with no parameters - it will close the last opened connection.

However, since you're not using a persistant connection, you don't need to call mysql_close() (http://www.php.net/manual/en/function.mysql-close.php)

Does this script run faster than the previous one you were trying?
 

Anterrabae

Guest
Yes, it only takes a few seconds to run, similar behavior to when I ran it via command line. Which is awesome!

I added the change for removing the comma (much better than running an if every row), and removed the close, and it works with 0 errors, and the DB is full.

Thanks a TON for your help. For anyone looking for the finished script, its here:

PHP:
$dbh = mysql_select_db("DATABASE", mysql_connect ("SERVER", "USER","PASSWORD")) or die('Cannot connect to the database because: ' . mysql_error());
    ini_set("memory_limit","100M");
    mysql_query("Truncate Table towns");
    $datafile = gzfile('http://en30.grepolis.com/data/towns.txt.gz');
    if(!is_array($datafile)) die("File could not be opened");
	 $INSERT = "INSERT INTO `towns` (`id`, `player_id`, `name`, `points`) VALUES ";
	 $VALUES = "";
    foreach($datafile as $datum){
        list($id, $player_id, $name, $points) = explode(',', $datum);

        if (!isset($id)){ $id=0; }
        if (!isset($player_id)){ $player_id=0; }
        if (!isset($name)){ $name=0; }
        if (!isset($points)){ $points=0; }
	$VALUES .= "('$id', '$player_id', '$name', '$points'), ";
	 }
	$VALUES = substr($VALUES, 0, -2);
    $result = mysql_query("$INSERT" . "$VALUES");
    if (!$result) {
        die('Invalid query: ' . mysql_error());
    }
    print "DB Updated Successfully";
 

Ac04

Strategos
Yes, it only takes a few seconds to run, similar behavior to when I ran it via command line. Which is awesome!

I added the change for removing the comma (much better than running an if every row), and removed the close, and it works with 0 errors, and the DB is full.

Thanks a TON for your help. For anyone looking for the finished script, its here:

PHP:
$dbh = mysql_select_db("DATABASE", mysql_connect ("SERVER", "USER","PASSWORD")) or die('Cannot connect to the database because: ' . mysql_error());
    ini_set("memory_limit","100M");
    mysql_query("Truncate Table towns");
    $datafile = gzfile('http://en30.grepolis.com/data/towns.txt.gz');
    if(!is_array($datafile)) die("File could not be opened");
	 $INSERT = "INSERT INTO `towns` (`id`, `player_id`, `name`, `points`) VALUES ";
	 $VALUES = "";
    foreach($datafile as $datum){
        list($id, $player_id, $name, $points) = explode(',', $datum);

        if (!isset($id)){ $id=0; }
        if (!isset($player_id)){ $player_id=0; }
        if (!isset($name)){ $name=0; }
        if (!isset($points)){ $points=0; }
	$VALUES .= "('$id', '$player_id', '$name', '$points'), ";
	 }
	$VALUES = substr($VALUES, 0, -2);
    $result = mysql_query("$INSERT" . "$VALUES");
    if (!$result) {
        die('Invalid query: ' . mysql_error());
    }
    print "DB Updated Successfully";
My pleasure :) If you need any more help with stuff like this, let me know.

some more lines you can remove:

PHP:
if (!isset($id)){ $id=0; }
if (!isset($player_id)){ $player_id=0; }
if (!isset($name)){ $name=0; }
if (!isset($points)){ $points=0; }
Just set default values in the database.
 

Anterrabae

Guest
Yeah, that could work. I was thinking it would keep the query from dying due to no values, but because of the single quotes in the query around the values, it won't matter.

Once I've gotten everything working, I'll pass it along. I'm working on making a claims system for my alliance. Instead of using the forums, they simply login to the system, type in the town id or town name, it finds the data from the DB (AJAX), and then they go on to take out their target. The system keeps track of how long the claim has existed, and updates it with warnings for admins, so they can extend the claim, or allow it to expire. It will also mark the claim as successful if it finds the city is now owned by the player who claimed it, or if it was taken by another player, it marks it as failed. The user who claimed it can see their expired/failed claims and then click a button to request an extension, which an admin can then approve/disapprove.

I'm sure something like this exists, but I've gotten rusty with my PHP and I like a challenge :)
 

SmokeyCosmy

Guest
The quickest method to load game information into a table is by using LOAD DATA INFILE. For this you will need to temporary save the data files somewhere on your host.

Btw.. I recomand updating via a simple bash script, rather than php, it's much easier and even faster..