Help with addon for migrating data and max_execution_time

[piernas]

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 January 12, 2015 by piernas

[piernas]

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.

[♥bruyndoncx]

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

[piernas]

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?

[♥kymation]

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

[piernas]

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.