Question 41564 in PHP Question posted in
Official manual can be found here.

Using PHP multi_query to install a large SQL file

I have created a CMS. When installing the CMS, I must install its database. I use PHP multi_query() to istall the database without opening phpMyAdmin. When the SQL file is as small as 624KB, the database is installed successfully. However, when the SQL file is as large as 6.32MB or more, the database does not install. Here is the code I use to install the database via PHP

            $sParamSqlFile = 'database.sql';
            if(file_exists($sParamSqlFile)){
            
                $fSql = file_get_contents($sParamSqlFile);

                /* execute multi query */
                if ($oDbConn->multi_query($fSql)){
                    do {
                        /* store first result set */
                        if ($oResult = $oDbConn->store_result()) {
                            while ($aRow = $oResult->fetch_row()) {
                                //
                            }
                            $oResult->free();
                        };
                        /* print divider */
                        if ($oDbConn->more_results()){
                            //
                        }
                    } while ($oDbConn->next_result());
                }
                else{
                    return 'false';
                }
                
                /* close connection */
                $oDbConn->close();
                return 'true';
            }
            return $sParamSqlFile . ' does not exist';

Edit: I have encounted this error while trying to install the database "Warning: mysqli::multi_query(): Error while reading SET_OPTION’s response packet. PID=8660"

Could you help with a solution so that I could install the database when the SQL file is large?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I referred to this answer https://stackoverflow.com/a/5688506/12963244 to solve the problem.

    I ran this SQL statement in phpMyAdmin to show the current max_allowed_packet

    SHOW VARIABLES LIKE 'max_allowed_packet';

    The max_allowed_packet was 1048576. I changed it to 16777216 by running the following SQL statement in phpMyAdmin

    SET GLOBAL max_allowed_packet=16777216;

    After changing the max_allowed_packet to 16777216, I was able to install my database from a SQL file of about 6.32MB successfully using PHP's multi_query().

    I did this in Xampp. I am told this solution may not work in shared hosting where you may not be allowed by your web hosting company to change the max_allowed_packet this way


  2. 0

    The code simple returns false

    You should be checking what the error is when a query fails.

    Most likely the packet size is too large.

    Try parsing the data – mysqldump will produce something with a well defined structure that is easy to split into individual statements (you just need to ensure that any occurrence of ‘;’ NOT within single quotes marks the end of statement – then run the statements one at a time.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search