I am using PHP on a free hosting service to Insert rows into a MySQL table, but can only seem to add 10,000 rows at a time, rather than the full ~17,000 rows that my code expects. It is very odd, because in the same foreach loop that is populating the MySQL table, a JSON file is also being populated, and the JSON file has all ~17,000 values, while the MySQL table just stops at 10,000.
I was concerned because of my free hosting solution that the table could only have a maximum of 10,000 rows, but that is not the case. If I run the PHP code twice, I will get a table with 20,000 rows, so it seems to be an issue with the fact that I can only set 10,000 rows at a time.
Why could this be? I checked the 10,000th value and there is nothing odd in it that should terminate the connection or violate a character limit. It also shouldn't have reached any size limit that I am aware of, seeing as I can have the table reach 20,000 rows, but just in two sets of 10,000.
Anyway, I am very confused but maybe you can help. Here is the relevant part of my php code:
foreach ( $cc as $c ) {
$y++;
if($y < $count)
{
fputs($jsonfile2,"{".'"name"'.": ".'"'.$c.'"'.",".'"updatetimetotalminutes"'.": ".'"'.$updatetimetotalminutes.'"'.",".'"updatetime"'.": ".'"'.$updatetime.'"'.",".'"updatetimetotal"'.": ".'"'.$updatetimetotal.'"'.",".'"updateordernumber"'.": ".'"'.$y.'"'.",".'"numberofnations"'.": ".'"'.$numnations.'"'.",".'"founderlessstatus"'.": ".'"'.$founderlessstatus.'"'.",".'"delegatevotes"'.": ".'"'.$delegatevotes.'"'."},");
mysqli_query($con,"INSERT INTO `regions`(`Update Order Number`, `Name`, `Nations`, `Targetable?`, `Time to Update`, `Seconds into the Update`, `Minutes into the Update`, `Delegate Votes`) VALUES ('$y', '$c', '$numnations', '$founderlessstatus', '$updatetime', '$updatetimetotal', '$updatetimetotalminutes', '$delegatevotes')");
}
else
{
fputs($jsonfile2,"{".'"name"'.": ".'"'.$c.'"'.",".'"updatetimetotalminutes"'.": ".'"'.$updatetimetotalminutes.'"'.",".'"updatetime"'.": ".'"'.$updatetime.'"'.",".'"updatetimetotal"'.": ".'"'.$updatetimetotal.'"'.",".'"updateordernumber"'.": ".'"'.$y.'"'.",".'"numberofnations"'.": ".'"'.$numnations.'"'.",".'"founderlessstatus"'.": ".'"'.$founderlessstatus.'"'.",".'"delegatevotes"'.": ".'"'.$delegatevotes.'"'."}"."]");
mysqli_query($con,"INSERT INTO `regions`(`Update Order Number`, `Name`, `Nations`, `Targetable?`, `Time to Update`, `Seconds into the Update`, `Minutes into the Update`, `Delegate Votes`) VALUES ('$y', '$c', '$numnations', '$founderlessstatus', '$updatetime', '$updatetimetotal', '$updatetimetotalminutes', '$delegatevotes')");
}
}
$con is the mysqli_connect() value.
Any help on this would be appreciated. I will provide any additional information as I can if it would help diagnose the problem, but my access to certain settings may be limited due to my hosting solution.
Thank you!
If you're hitting the max_updates limit you could refresh the connection after a given number of updates, something like:
$row = $con->query("SHOW VARIABLES LIKE 'max_updates'")->fetch_assoc();
$max_updates = $row === FALSE ? 10000 : $row['Value'];
$current_update = 0;
foreach($cc as $c ) {
if (++$current_update >= $max_updates) {
$conn->close();
$conn->real_connect();// Reopen connection here, you'll need your details
$current_update = 1;
}
// ...
}
It also shouldn't have reached any size limit that I am aware of, seeing as I can have the table reach 20,000 rows, but just in two sets of 10,000.
Are you sure? Your database is not the only possible problem (but might still be one) when you are going this way. Your PHP configuration has a few more for you, for example example max_execution_time. When you are not displaying or logging PHP's error messages you are pretty much fighting a lost cause.
So a few advices:
Always turn on error reporting while you are developing
Don't only rely on PHP's error messages only, but take care of extensions and drivers, and how to handle their errors, too. (e.g mysqli_error())
If you can, use a proper Debugger (e.g. PHPDBG)
Hosting should not be based on magic, but on science! So properly inform yourself about what your hoster can and what it cannot do. Switch if it doesn't fit your needs.
Your webservers error logs (and your database logs in your case) are your best friends while developing
The max_updates resource limit counts each "statement".
Since you are exceeding the max_updates setting, try concatenating the inserts into a single query (INSERT statement). Here's an example from MySQL docs of an insert that inserts multiple rows:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
Another benefit of inserting multiple rows in a single statement is that it's more efficient.
Your only limit then will be the size of max_allowed_packet.