Data is not being inserted, query execute doesn't seem to run

user_3541003 0
Viewed 155

I have a function that collects data and puts it into an object. It then converts this object into a json string, and places it into a table. It takes about 30 seconds to do it.

Now, this json string is fairly big (roughly 36mb or so).

But when I execute the query, nothing gets put into the table, and I don't get any errors.

So I did some debugging, and the code now looks like this:

function __construct($total, $allActions, $employees, $branches, $companies, $departments, $lastUpdated, $update = false)
    {
        echo "Made it to constructor. Update: ".json_encode($update);
        global $conn;
        $this->Total = $total;
        $this->AllActions = $allActions;
        $this->Employees = $employees;
        $this->Branches = $branches;
        $this->Companies = $companies;
        $this->Departments = $departments;
        $this->LastUpdated = $lastUpdated;
        if($update) {
            try {
                $query = $conn->prepare("INSERT INTO actionplansummarydata_new (json, last_updated) VALUES (?, ?)");
                echo "Prepared query.";
                if($query->bind_param('ss', json_encode($this), $this->LastUpdated->format("Y-m-d H:i:s"))) {
                    echo "Bound parameters.";
                    if ($query->execute()) {
                        echo "Executed query.";
                    } else {
                        echo "Error inserting summary: " . $query->error;
                    }
                } else {
                    echo "Error binding query: " . $query->error;
                }
            } catch(Exception $ex) {
                echo "Exception: ".$ex->getMessage();
            }
        }
    }

Now, when this is executed, this is the response I get on the page:

Made it to constructor. Update: truePrepared query.Bound parameters.

But I have an if statement here:

            if ($query->execute()) {
                echo "Executed query.";
            } else {
                echo "Error inserting summary: " . $query->error;
            }

Neither of these gets echoed to the page, nor do I get any exceptions.

I'm completely baffled.

The server is MariaDB 10.4, the json field in the database is longtext, so it should be able to store it.

mysql php
4 Answers

If your error reporting is at E_ALL, you should not be facing a silent fail with the query execution... In any case, first of all, some steps in troubleshooting the point of failure.

Move json_encode($this) out of the bind, assign to a variable, and var_dump to see what you're actually feeding the binder. So too with $this->LastUpdated->format; presuming it's a DateTime object, but who knows. Easier to debug when fail-points are outside conditional statements.

Can you confirm whether it works as expected if you insert strings of dummy data instead? E.g.:

if($query->bind_param('ss', '{}', '2020-05-19 01:01:01')); {

One problem here is with the fact that bind_param binds variables by reference, not by value. Therefore, you can't feed it with functions. You have to assign the values to variables and only then bind them (because a function doesn't return a usable reference). Meaning, change to:

$json = json_encode($this);
$updated = $this->LastUpdated->format("Y-m-d H:i:s");
if($query->bind_param('ss', $json, $updated)) {

You can assign the bind result (true/false) into a variable, then var_dump. (We'd rather not var_dump from inside the conditional statement, right?) The bind would fail === false for reasons above. Likewise, var_dump the prepared query to ensure it succeeds, tracing each step.

A fatal error might be triggered by the database engine acting up, quite possible with the size of your insert query. You may want to look up MariaDB error logs for more clues, in case you're hitting a limit somewhere. I'd guess with max_allowed_packet, default 16MB, you're well over it. (Also see mysqli_stmt::send_long_data for sending long data in blocks.)

In other issues, json is a reserved word in MySQL 8.0 (not listed as such for Maria 10.4?!). You may want to rename the column to ensure it doesn't become a problem (even if it works here).

As far as how this scenario is possible, what you describe (no output from the if/else) only makes sense if there's a fatal error triggered by the $query->execute() that's never displayed. Unless there's a bug in PHP error reporting with certain MySQL failures. Do triple-check your PHP error reporting configuration, including anywhere in your code/.htaccess that might override php.ini.

user_46303250

Check the setting of max_allowed_packet; this may be stopping you. Set it to 1G.

If that does not work, there may be timeout issues, or some other size limitation.

For large text strings, I sometimes like to compress them, and stored the string into a MEDIUMBLOB.

user_17668310

what data type that you are using to store the 36MB of JSON string ? you probably need to change the data type to LONGTEXT that can store up to 4GB of data or JSON data type that can store up to 1GB of data

or

you can store it individually as columns in a table and change it back to JSON on your code when needed

user_117634110

Try to check if prepared

 if($query = $conn->prepare("INSERT INTO actionplansummarydata_new (json, last_updated) VALUES (?, ?)")){
        $query->bind_param('ss', json_encode($this), $this->LastUpdated->format("Y-m-d H:i:s"));
        if($query->execute()){
            // code here
        }

}
user_134899120
Related