Reason: I was instructed to run several scripts that promote the site, this is a fantasy football site, and there are several moments of the site located in different domains. Some of them have more than 80 thousand users, and each user must have a team consisting of 15 players. Therefore, some tables have the rows Nousers x No.players.
However, sometimes the script fails and the result is corrupted, so I have to backup 10 tables before which I run the script. However, I still need to reserve tables in order to keep a historical record of user actions. Because football matches can last more than 50 games.
Task: To duplicate db tables with php script. When I started, I used to backup the table using sqlyog . It works, but it takes a lot of time, since I have to wait until each table is duplicated. In addition, for large tables, sqlyog crashes while duplicating large tables, which can be very annoying.
Current solution: I created a simple application with an interface that does this work, and it works great. It consists of three files, one for connecting to db, 2nd for manipulating db, 3rd for the user interface and for using the second file code. The fact is that sometimes it gets stuck in the middle of the process of duplicating tables.
Purpose:. To create an application that will be used by the administrator to facilitate backup of the database using mysql + php.
My question is:. How to make sure that the duplicate script will fully reserve the table completely without server hang or script interruption .
Below, I will include my code for duplicating a function, but basically these are two important lines that I think are in them:
//duplicate tables structure $query = "CREATE TABLE $this->dbName.`$newTableName` LIKE $this->dbName.`$oldTable`"; //duplicate tables data $query = "INSERT INTO $this->dbName.`$newTableName` SELECT * FROM $this->dbName.`$oldTable`";
The rest of the code is for verification purposes only if an error occurs. If you want to take a look at the whole code, be my guest. Here's the function:
private function duplicateTable($oldTable, $newTableName) { if ($this->isExistingTable($oldTable)) { $this->printLogger("Original Table is valid -table exists- : $oldTable "); } else { $this->printrR("Original Table is invalid -table does not exist- : $oldTable "); return false; } if (!$this->isExistingTable($newTableName))// make sure new table does not exist alrady { $this->printLogger("Distination Table name is valid -no table with this name- : $newTableName"); $query = "CREATE TABLE $this->dbName.`$newTableName` LIKE $this->dbName.`$oldTable`"; $result = mysql_query($query) or $this->printrR("Error in query. Query:\n $query\n Error: " . mysql_error()); } else { $this->printrR("Distination Table is invalid. -table already exists- $newTableName"); $this->printr("Now checking if tables actually match,: $oldTable => $newTableName \n"); $varifyStatus = $this->varifyDuplicatedTables($oldTable, $newTableName); if ($varifyStatus >= 0) { $this->printrG("Tables match, it seems they were duplicated before $oldTable => $newTableName"); } else { $this->printrR("The duplicate table exists, yet, doesn't match the original! $oldTable => $newTableName"); } return false; } if ($result) { $this->printLogger("Query executed 1/2"); } else { $this->printrR("Something went wrong duplicateTable\nQuery: $query\n\n\nMySql_Error: " . mysql_error()); return false; } if (!$this->isExistingTable($newTableName))//validate table has been created { $this->printrR("Attemp to duplicate table structure failed $newTableName table was not found after creating!"); return false; } else { $this->printLogger("Table created successfully: $newTableName"); //Now checking table structure $this->printLogger("Now comparing indexes ... "); $autoInc = $this->checkAutoInc($oldTable, $newTableName); if ($autoInc == 1) { $this->printLogger("Auto inc seems ok"); } elseif ($autoInc == 0) { $this->printLogger("No inc key for both tables. Continue anyways"); } elseif ($autoInc == -1) { $this->printLogger("No match inc key!"); } $time = $oldTable == 'team_details' ? 5 : 2; $msg = $oldTable == 'team_details' ? "This may take a while for team_details. Please wait." : "Please wait."; $this->printLogger("Sleep for $time ...\n"); sleep($time); $this->printLogger("Preparing for copying data ...\n"); $query = "INSERT INTO $this->dbName.`$newTableName` SELECT * FROM $this->dbName.`$oldTable`"; $this->printLogger("Processing copyign data query.$msg...\n\n\n"); $result = mysql_query($query) or $this->printrR("Error in query. Query:\n $query\n Error: " . mysql_error()); // ERROR usually happens here if large tables sleep($time); //to make db process current requeste. $this->printLogger("Query executed 2/2"); sleep($time); //to make db process current requeste. if ($result) { $this->printLogger("Table created ($newTableName) and data has been copied!"); $this->printLogger("Confirming number of rows ... "); ///////////////////////////////// // start checking count $numRows = $this->checkCountRows($oldTable, $newTableName); if ($numRows) { $this->printLogger("Table duplicated successfully "); return true; } else { $this->printLogger("Table duplicated, but, please check num rows $newTableName"); return -3; } // end of checking count ///////////////////////////////// }//end of if(!$result) query 2/2 else { $this->printrR("Something went wrong duplicate Table\nINSERT INTO $oldTable -> $newTableName\n\n$query\n mysql_error() \n " . mysql_error()); return false; } } }
As you noticed, the function is intended only for duplication of one table, so there is another function that takes an array of tables from the user and passes an array of table names one by one to duplicate Table (). Any other feature should be enabled for this question, please let me know.
One solution comes to my mind, duplicates the tables in parts, adds any improvements, I'm not sure how to embed in the work, but maybe if I could insert, let them say 25% at a time when this can help ?