0

I have the following code :

try
{
    if(!($stmt = $conn["DB"]->prepare('CALL `central`.`permissions_edit`(?,?,?,?,?);')))
    {
        $rtn["Errors"][] = "permissions_edit Prepare failed: (" . $conn["DB"]->errno . ") " . $conn["DB"]->error;
    }
    else{
        foreach ($data as $user => $areas) {
            foreach ($areas as $area => $access) {
                foreach ($access as $acc => $active) {
                    if($active != "U")
                    {
                        if(!($stmt->bind_param(
                            'siiis',
                            $key,
                            $user,
                            $area,
                            $acc,
                            $active
                        )))
                        {
                            $rtn["Error"][] = "permissions_edit Bind failed: (" . $conn["DB"]->errno . ") " . $conn["DB"]->error;
                        }
                        else if(!($stmt->execute()))
                        {
                            $rtn["Errors"][] = "permissions_edit Execute failed: (" . $conn["DB"]->errno . ") " . $conn["DB"]->error;
                        }
                        else if($res = $stmt->get_result())
                        {
                            if ($row = $res->fetch_assoc())
                            {

                                if(isset($row["Success"]) && $row["Success"] )
                                {
                                    $rtn["Results"][]= "user=$user areas=$area access=$acc active=$active Message=" . $row["Msg"];
                                }
                                else
                                {
                                    $rtn["Success"] = false;
                                    $rtn["Errors"][]= "user=$user areas=$area access=$acc active=$active Message=" . $row["Msg"];                   
                                }
                            }

                        }
                        else
                        {
                            $rtn["Errors"][] = "permissions_edit Get failed: (" . $conn["DB"]->errno . ") " . $conn["DB"]->error;
                        }

                    }
                    else
                        $rtn["Results"][]= "user=$user areas=$area access=$acc active=$active Message=Unchanged";
                }
            }
        }
    }


}
catch(Exception $e)
{
    $rtn["Errors"][] = "permissions_edit Error(" . $conn["DB"]->errno . "): " . $conn["DB"]->error;
}

using this code where the loop repeats 1-2 times works perfectly however using this code where the loop repeats more than twice causes the POST to PHP to terminated with no Response

and generates the following in the MySql log,

2017-03-03T16:47:49.284972Z 450 [Note] Aborted connection 450 to db: 'central' user: 'username' host: 'localhost' (Got an error reading communication packets)

2017-03-03T16:47:49.582165Z 451 [Note] Aborted connection 451 to db: 'central' user: 'username' host: 'localhost' (Got an error reading communication packets)

so the question is what have I done wrong?

Note i tried adding 

$res->free();
$stmt->close();

this then resulted in

Packets out of order. Expected 1 received 7. Packet size=7

mysqli_stmt::execute(): MySQL server has gone away

mysqli_stmt::execute(): Error reading result set's header

from info.php

PHP Version 5.6.27

mysqli
MysqlI Support  enabled
Client API library version  mysqlnd 5.0.11-dev - 20120503 - $Id: 76b08b24596e12d4553bd41fc93cccd5bac2fe7a $
Active Persistent Links     0
Inactive Persistent Links   0
Active Links    0
Directive   Local Value Master Value
mysqli.allow_local_infile   On  On
mysqli.allow_persistent On  On
mysqli.default_host no value    no value
mysqli.default_port 3306    3306
mysqli.default_pw   no value    no value
mysqli.default_socket   no value    no value
mysqli.default_user no value    no value
mysqli.max_links    Unlimited   Unlimited
mysqli.max_persistent   Unlimited   Unlimited
mysqli.reconnect    Off Off
mysqli.rollback_on_cached_plink Off Off
MikeT
  • 5,398
  • 3
  • 27
  • 43
  • i guess there are too many request made to server and that could be causing this...from edit i guess http://stackoverflow.com/questions/2232150/pdo-mysql-server-has-gone-away this thread could help – RohitS Mar 03 '17 at 17:51
  • as you can see from the errors its a localhost DB with only me using it, so there should only be this command currently accessing it and these should be sequential not simultaneous – MikeT Mar 03 '17 at 17:59
  • T yes...but there are some solutions within thread you can try..also you try enabling reconnect property.. – RohitS Mar 03 '17 at 18:09

2 Answers2

0

the Problem is that you need to call $conn["DB"]->next_result();

Here is a tidy version that works

$stmt = $conn["DB"]->prepare('CALL `central`.`permissions_edit`(?,?,?,?,?);');
$stmt->bind_param('siiis', $key, $user, $area, $acc, $active);

foreach ($data as $user => $areas) {
    foreach ($areas as $area => $access) {
        foreach ($access as $acc => $active) {
            if($active == "U")
            {
                $rtn["Results"][]= "user=$user areas=$area access=$acc active=$active Message=Unchanged";
                continue;
            }
            $stmt->execute();
            $row = $stmt->get_result()->fetch_assoc();
            if(!empty($row["Success"]))
            {
                 $rtn["Results"][]= "user=$user areas=$area access=$acc active=$active Message=" . $row["Msg"];
            }
            else
            {
                 $rtn["Success"] = false;
                 $rtn["Errors"][]= "user=$user areas=$area access=$acc active=$active Message=" . $row["Msg"];
            }
            $conn["DB"]->next_result();
        }
    }
}
MikeT
  • 5,398
  • 3
  • 27
  • 43
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • too much code is never the problem only wrong code, can you point out which part was wrong as most of what you seem to have done is remove all the error handling – MikeT Mar 03 '17 at 18:17
  • What you have fancy to call "error handling" doesn't actually *handle* anything. Yet it makes this code awfully hard to read and getting to the point. Now it's quite clear and readable without the need to scroll a whole screen horizontally. – Your Common Sense Mar 03 '17 at 18:21
  • as i said easier to read doesn't change behaviour, something you have done has changed the behaviour, after all if i don't know why this fixes it how can i find the problem the next time i make what ever mistake it was? – MikeT Mar 03 '17 at 18:24
  • PHP is rather talkative about errors - so you won't miss them. – Your Common Sense Mar 03 '17 at 18:26
  • going through your code the only functional difference i can see is `conn["DB"]->next_result();` was that what i was missing? – MikeT Mar 03 '17 at 18:30
  • That's right. You see, the less code you write, the easier you get to the meaningful parts :) – Your Common Sense Mar 03 '17 at 18:31
  • no not at all and your condensing attitude doesn't help the least in any regard – MikeT Mar 03 '17 at 18:34
-2

You can see what cuse this error from https://dev.mysql.com/doc/refman/5.7/en/communication-errors.html

If a client successfully connects but later disconnects improperly or is terminated, the server increments the Aborted_clients status variable, and logs an Aborted connection message to the error log. The cause can be any of the following:

  • The client program did not call mysql_close() before exiting.

  • The client had been sleeping more than wait_timeout or interactive_timeout seconds without issuing any requests to the server.

  • The client program ended abruptly in the middle of a data transfer.

However, I believe that it's not good practices to use bind_param inside loop, since it should used only one time one before the loop. The second notice is to make sure to close connection even if there error accrued.

Ali Yusuf
  • 337
  • 4
  • 6