0

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?

programmer777
  • 31
  • 7
  • What is going wrong? You get any errors? You get an timeout? [ask] – DarkBee Nov 22 '22 at 16:40
  • (Why are you returning `true` and `false` as literals BTW?) – DarkBee Nov 22 '22 at 16:43
  • I dont get errors. I don't get timeout errors either. The code simple returns `false` – programmer777 Nov 22 '22 at 16:43
  • Well it seems you aren't testing for any errors either - see [here](https://stackoverflow.com/questions/14578243/turning-query-errors-to-exceptions-in-mysqli) to report errors as exceptions – DarkBee Nov 22 '22 at 16:45
  • Do not use `multi_query()`. Either execute all statements separately or use `mysql` command line tool to upload it. – Dharman Nov 22 '22 at 17:20
  • @Dharman There are several thousands of lines in the SQL file. It is impossible to determine each statement separately. The application is being developed so that customers without knowledge of command line tool can install it – programmer777 Nov 22 '22 at 17:25
  • The warning you are seeing looks more like a bug. I don't know which PHP version you are using, maybe you can try with PHP 8.1. This function hasn't really been designed to work with SQL files. For loading SQL files there is the [`LOAD DATA LOCAL`](https://dev.mysql.com/doc/refman/8.0/en/load-data-local-security.html) syntax, but even this should be used with care. If I were you I would just forget that `multi_query()` exists because it's such a bad function. Some bugs have been fixed in PHP 8.1 but it's still not something I recommend anyone to use. – Dharman Nov 22 '22 at 17:44
  • @Dharman If I should forget that `multi_query` exists, then what could be alternative to `multi_query` so I could use it to install the database from the file. It is surpising that there isn't a special function for installing database from file – programmer777 Nov 23 '22 at 12:24
  • See [Loading .sql files from within PHP](https://stackoverflow.com/a/150091/1839439) – Dharman Nov 23 '22 at 13:57

2 Answers2

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.

symcbean
  • 47,736
  • 6
  • 59
  • 94
  • I have encoutered this error which I am confident is the reason for the database not installing "Warning: mysqli::multi_query(): Error while reading SET_OPTION's response packet. PID=8660" – programmer777 Nov 22 '22 at 17:07
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

programmer777
  • 31
  • 7