Jump to content
piernas

Help with addon for migrating data and max_execution_time

Recommended Posts

I'm lazy to create custom fields and move all the data from my 2.2 live shop each time I test a new version of oscommerce in my local server. Doing it by phpadmin is boring so I finally made a tool to extract the data from the live shop and place it on the test shop tables. I plan to upload the tool to the addons section but I have a problem.

 

The php script I wrote has a function that reads data from old tables and inserts the results on the test server corresponding table. But now I'm facing the problem that the script takes too long to process bigger tables and I end up with a Maximum execution time error at the middle of the process. In my case I may have a few thousand rows in the bigger table, but I' m sure other shops will have even bigger databases.

 

Do you know any solution (apart of increasing max_execution_time) to avoid the problem?

 

Here's the function in case you want to take a look:

function translada ($tabla, $campos) {

	$sql_insert  = "INSERT INTO " . $tabla;
	$sql_insert .= " (".implode(", ", $campos).") ";
	
 // Abre dos conexiones a las bases de datos de origen y destino
	$conn = new mysqli(DESTINATION_SERVER, DESTINATION_USER, DESTINATION_PASS, DESTINATION_DB); // destino
	$conn2 = new mysqli(SOURCE_SERVER, SOURCE_USER, SOURCE_PASS, SOURCE_DB); // origen

	$customers_query = "SELECT * from " . $tabla;
	$origen = $conn2->query($customers_query);

	$contador =0;

	if ($origen->num_rows > 0) {

		while($row = $origen->fetch_assoc()) {
		$sql="";
		$sql_values ="";
			$sql_values .= " VALUES (";

		foreach ($campos as $valor) {
			$sql_values .= "'" . addslashes ($row[strval($valor)]). "'," ;
		}

		$sql_values = rtrim($sql_values, ',');
		$sql_values .= ")";
		$sql = $sql_insert . $sql_values;

		if ($conn->query($sql) === TRUE) {
			$contador++;
		} else {
			echo "Error: " . $conn->error . "<br>";
		}
	}
	echo $contador . " registros procesados en la tabla " . $tabla . ".<br>";
	}
	$conn->close();
	$conn2->close();
}

Edited by piernas

Share this post


Link to post
Share on other sites

Whoops! I'm sorry found I posted in the wrong forum. i think it will fit better in the add-ons forum. Please any moderator feel free to move the topic.

Share this post


Link to post
Share on other sites

perhaps you can consider only adding the indexes after the table is filled up, that would shave some time off


KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Share this post


Link to post
Share on other sites

Thanks, I'll do it and compare response times. Anyway I think I'll need something else; it takes minutes to load.

 

I think I've seen some tricks before on another util for importing data, but I can't remember where or how it did it. Maybe javascript?

Share this post


Link to post
Share on other sites

You could load it in sections. Maybe check a timer, or just load a fixed number of rows, then reload the page. Pass the last row loaded as a parameter when you reload so the next one knows where to start.

 

Or you could use mysqldumper since it already does that. It's a nice, open-source database backup and copy tool. i use it for the same thing you're doing on sites with over 50,000 products, and it handles the database size easily.

 

Regards

Jim


See my profile for a list of my addons and ways to get support.

Share this post


Link to post
Share on other sites

I'll try your first solution, it sounds good. Will try mysqldumper too, it will be useful for sure, but I want to make an one-click tool because I think it's good for people who doesn't know how to use other tools. I think something similar into installation is a must for people upgrading their shops to newer versions.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×