So I had this very weird problem that seems to be presistent from php 7.2 through 8.1.12 where my sql queries seem to be randomly cut off. (while in string)
In my most recent script I tried to normalise the existing data within a database fully automatically (44 million rows)
During small scale testing the problem arises where php seems to be cutting down the query string (as example below).
This is a variable dump through XDebug:
"INSERT INTO `order` (`orderid`,`ordername`,`price`,`pricename`) VALUES (:id1, :orderid1, :price1, :pricename1),(:id2, :orderid2, :price2, :pricename2), </(shortend)> (:id22, :orderid22, :price22, :pricename22),(:id23, :orderid23, :"
Here is a copy of the sql code for your convenience:
-- --------------------------------------------------------
-- Host: 127.0.0.1
-- Server version: 8.0.30 - MySQL Community Server - GPL
-- Server OS: Win64
-- HeidiSQL version: 12.6.0.6765
-- --------------------------------------------------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
-- Structure of table stackoverflowtry.big_table is being written
CREATE TABLE IF NOT EXISTS `big_table` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`userid` int DEFAULT NULL,
`websitename` varchar(50) DEFAULT NULL,
`websiteid` int DEFAULT NULL,
`productid` int DEFAULT NULL,
`price` double DEFAULT NULL,
`pricename` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=454 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- Data exporting was deselected
-- Structure of table stackoverflowtry.order is being written
CREATE TABLE IF NOT EXISTS `order` (
`id` int DEFAULT NULL,
`productid` int DEFAULT NULL,
`price` double DEFAULT NULL,
`pricename` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- Data exporting was deselected
-- Structure of table stackoverflowtry.users is being written
CREATE TABLE IF NOT EXISTS `users` (
`id` int DEFAULT NULL,
`userid` int DEFAULT NULL,
`name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- Data exporting was deselected
-- Structure of table stackoverflowtry.website is being written
CREATE TABLE IF NOT EXISTS `website` (
`websiteid` int DEFAULT NULL,
`websitename` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- Data exporting was deselected
/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;
During the running of the script it is trying to assemble a "insert into" query using ints to change keys into unique values.
i made a simplified code example below, (it is very big i apologize but ram and size limitations apply):
<?php
<?php
function GetPDO(){
//if pdo object exsits, return it
// if(isset($_POST["PDO"])){
// return ($_POST["PDO"]);
// }
//declare pdo variables
$host = 'Localhost';
$db = 'stackoverflowtry';
$user = 'root';
$pass = '';
$charset = 'utf8mb4';
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
PDO ::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO ::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
// PDO ::MYSQL_ATTR_MAX_BUFFER_SIZE => 1024*1024*50,
];
//attempt to make pdo object
try{
$pdo = new PDO ($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
echo("Sql error! Contact support.");
exit();
}
$_POST["PDO"] = $pdo;
// return the pdo object
return $pdo;
}
function LastChanges($FileName){//finish php and sql tokenisation before sql execution
$execute = fopen($FileName,"a") or die ("Unable to open Cache_time!");
$stat = fstat($execute);
ftruncate($execute, $stat['size']-1);
unset($stat);
fwrite($execute,";");
fwrite($execute,"\"");
fwrite($execute,";");
fclose($execute);
}
require_once("overflow.php");//import id list
//setup nessesary variables
$user_params = [];
$user_batch_num = 1;
$website_batch_num = 1;
$order_batch_num = 1;
$BigDataList = [];
$amount = 0;
$num =0;
foreach ($big_table as $row_id){
$pdo = GetPDO();
//import all nessesary data
$sql ="SELECT * FROM `big_table` WHERE id = :id";
$params = [
'id' => $row_id["id"]
];
$stmt = $pdo->prepare($sql);
try{
$stmt->execute($params);
}catch(PDOException $e) {
$Error = true;
}
$DataList = $stmt->fetchAll();
// big table structured as follows:
// id name userid websitename websiteid productid price pricename
// int string int string int int int string
unset($stmt);
unset($sql);
$params = [];
//ensure the current array is 300 units long, otherwise you skip and try again
$BigDataList = array_merge($BigDataList,$DataList);
$amount += count($DataList);
if($amount > 300){
//split data into pieces of 300 if resultset is larger then 300
$PriceChunkData = array_chunk($BigDataList,300);
//enter prefixes into all the cache files
$User_write = fopen("Cache_User.php","w+") or die ("Unable to open Cache_User!");
fwrite($User_write,"<?php \$Cache_User = \"");
fclose($User_write);
$Website_write = fopen("Cache_Website.php","w+") or die ("Unable to open Cache_Website!");
fwrite($Website_write,"<?php \$Cache_Website = \"");
fclose($Website_write);
$order_write = fopen("Cache_order.php","w+") or die ("Unable to open Cache_order!");
fwrite($order_write,"<?php \$Cache_order = \"");
fclose($order_write);
//start chunked data loop
foreach ($PriceChunkData as $key => $PriceChunk) {
$ChunkAmount = count($PriceChunk);
//start normalisation per database row
foreach($PriceChunk as $value){
$num +=1;
//table one - user table
//make params array
$User_temp = [
'id'.$user_batch_num.'' => $num,
'userid'.$user_batch_num.'' => $value["userid"],
'name'.$user_batch_num.'' => $value["name"]
];
if($user_batch_num == 1){//on first pass of this 300 batch
$user_sql ="INSERT INTO `Users` (`id`,`userid`,`name`) VALUES ";
$user_params = [];
}
if($user_batch_num % 5 === 0){//on every 5th pass of this 300 batch
$user_write = fopen("Cache_User.php","a") or die ("Unable to open Cache_User!");
fwrite($user_write,$user_sql);
fclose($user_write);
$user_sql = "";
}
if($ChunkAmount > $user_batch_num){ //on every pass that is valid
$user_sql .= "(:id".$user_batch_num.", :userid".$user_batch_num.", :name".$user_batch_num."),";
$user_params = array_merge($user_params,$User_temp);
$user_batch_num += 1;
}
//table two - website table
$Website_temp = [
'websiteid'.$website_batch_num.'' => $num,
'websitename'.$website_batch_num.'' => $value["websitename"]
];
if($website_batch_num == 1){
$website_sql ="INSERT INTO `website` (`websiteid`,`websitename`) VALUES ";
$website_params = [];
}
if($website_batch_num % 5 === 0){
$Website_write = fopen("Cache_Website.php","a") or die ("Unable to open Cache_Website!");
fwrite($Website_write,$website_sql);
fclose($Website_write);
$website_sql = "";
}
if($ChunkAmount > $website_batch_num){
$website_sql .= "(:id".$website_batch_num.", :websiteid".$website_batch_num."),";
$website_params = array_merge($website_params,$Website_temp);
$website_batch_num += 1;
}
//table three - order table
$order_temp = [
'id'.$order_batch_num.'' => $num,
'productid'.$order_batch_num => $value["productid"],
'price'.$order_batch_num.'' => $value["price"],
'pricename'.$order_batch_num.''=> $value["pricename"]
];
if($order_batch_num == 1){
$order_sql ="INSERT INTO `order` (`orderid`,`ordername`,`price`,`pricename`) VALUES ";
$order_params = [];
}
if($order_batch_num % 5 === 0){
$order_write = fopen("Cache_order.php","a") or die ("Unable to open Cache_order!");
fwrite($order_write,$order_sql);
fclose($order_write);
$order_sql = "";
}
if($ChunkAmount > $order_batch_num){
$order_sql .= "(:id".$order_batch_num.", :orderid".$order_batch_num.", :price".$order_batch_num.", :pricename".$order_batch_num."),";
$order_params = array_merge($order_params,$order_temp);
$order_batch_num += 1;
}
}
//write last sql to file (it never ends on something multiplyable by 5)
$order_write = fopen("Cache_order.php","a") or die ("Unable to open Cache_order!");
fwrite($order_write,$order_sql);
fclose($order_write);
$order_sql = "";
$Website_write = fopen("Cache_Website.php","a") or die ("Unable to open Cache_Website!");
fwrite($Website_write,$website_sql);
fclose($Website_write);
$website_sql = "";
$user_write = fopen("Cache_User.php","a") or die ("Unable to open Cache_User!");
fwrite($user_write,$user_sql);
fclose($user_write);
$user_sql = "";
//during these imports php may (seemingly randomly) cut off the query string at any point
LastChanges("Cache_order.php");
//import order sql
require_once("Cache_order.php");
$stmt = $pdo->prepare($Cache_order); //SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
try{
$stmt->execute($order_params);
}catch(PDOException $e) {
echo($e->getMessage()); echo("</br>");
$Error = true;
}
$order_create = $stmt->fetch();
unset($stmt);
$order_params = [];
//import website sql
LastChanges("Cache_website.php");
require_once("Cache_Website.php");
$stmt = $pdo->prepare($Cache_Website);
try{
$stmt->execute($Website_params);
}catch(PDOException $e) {
echo($e->getMessage()); echo("</br>");
$Error = true;
}
$Website_create = $stmt->fetch();
unset($stmt);
$Website_params = [];
//import website sql
LastChanges("Cache_user.php");
require_once("Cache_user.php");
$stmt = $pdo->prepare($Cache_User);
try{
$stmt->execute($User_params);
}catch(PDOException $e) {
echo($e->getMessage()); echo("</br>");
$Error = true;
}
$User_create = $stmt->fetch();
unset($stmt);
$User_params = [];
}
}
}
note: require_once("overflow.php"); is a file that contains all the id's from big_table.sql
The problem I am having is that during the re-importation of the files it fails and creates the string as shown in the xdebug variable dump. I would expect it to import the file as it is written in storage and write it to the variable: "$Cache_order"
Of course, seeing as the query is incomplete I get the following error:
SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
I have tried just using string variables instead of file caching before this, however this resulted in it breaking at the point time.
Normaly one would consider ram limitations to be the issue, however php 'memory_get_usage' and 'memory_get_peak_usage' seem to be reporting otherwise.
(please note that it is currently breaking between 23-24th iteration of order_batch_num) the query sizes also never exceed 14kb
I had this issue last year and now again. I have tried a lot of diffrent possible solutions that i cannot remember to note down. However i did try to concatenate the query strings seperatly without all the other code (just pushing string on string) and that did work.
replies: xdebug is a debugging extention that is not likely to cause the issue seeing that the code has been run plenty of times with it disabled. An echo seems to give the same output as found in the xdebug variable dump. What Common Sense said was correct, however i cannot change the way my code provides parameters because php deletes data i set.
correction: the query isn't truncated, it is instead just cut off in the php variable.
englisch isn't my mother language so miss-comunications may apply.
Answers
It seems like you're encountering issues with string concatenation and memory usage in your PHP script, resulting in incomplete SQL queries being formed. Here are some suggestions to address this problem:
-
Optimize Memory Usage:
- Since you're dealing with a large dataset, memory management is crucial. Ensure that your PHP script is efficiently using memory.
- Use memory profiling tools like Xdebug or PHP's built-in memory usage functions (
memory_get_usage()andmemory_get_peak_usage()) to analyze memory consumption and identify potential areas for optimization. - Consider processing data in smaller batches to reduce memory usage. For example, instead of processing all 44 million rows at once, process them in chunks.
-
Concatenate SQL Queries Properly:
- Ensure that your SQL queries are concatenated correctly without any missing parts. Check the logic for building the queries to ensure that all parts are included.
- You mentioned that concatenating query strings separately worked. Try breaking down the process of building the queries into smaller, manageable steps to avoid memory issues.
-
Check for PHP Configuration Limits:
- Verify that PHP's
memory_limitconfiguration directive is set appropriately to accommodate the memory requirements of your script. If necessary, consider increasing this limit. - Similarly, check if there are any limits set on maximum execution time (
max_execution_time) or maximum input variables (max_input_vars) that could be affecting your script's execution.
- Verify that PHP's
-
Review File Writing and Reading:
- Double-check the file writing and reading processes to ensure that data is written and read correctly without any truncation or corruption.
- Consider using more robust file I/O operations and error handling mechanisms to prevent data loss or corruption during file operations.
-
Database Optimization:
- Optimize your database schema, indexes, and queries for better performance, especially when dealing with large datasets.
- Ensure that your database server has sufficient resources allocated to handle the workload efficiently.
-
Error Handling:
- Implement comprehensive error handling in your PHP script to catch and log any errors or exceptions that occur during execution. This will help in diagnosing and troubleshooting issues more effectively.
By addressing these points, you should be able to resolve the issues related to incomplete SQL queries and memory usage in your PHP script. Remember to test your changes thoroughly to ensure that they work as expected with your dataset.
