2

How can I determine the maximum $query parameter received by function mysqli_multi_query (or mysqli_query), in PHP? I have a php program which generates a large string made of UPDATE sql commands, separated by ';' The Problem is that if that string exceeds a certain length mysqli_query generates an error like 'MySQL server has gone away'. I notice that that length seems to be around 1MB, but how can I probe-it so that I can make sure that I never exceed that length?

The script needs to run about 7000 updates, on 25 or so fields. Executing one update at a time proved very slow, Concatenating multiple updates runs much faster.

Any possibility to run multiple queries even faster?

Thanck you for any advice!

Costel Socianu
  • 149
  • 1
  • 2
  • 10

3 Answers3

5

You should take a look at MySQL error logs.

If you dont have access to machine (hosting etc) you may ask your administrator or helpdesk for that log.

MySQL supports very big queries. Im not sure if there is any limit, but when you are using network - you may have problem with packet size.

You may check --max_allowed_packet in MySQL configuration, and try to set bigger packet size. Im not sure about default configuration, but it may be 1MB which may be too small value to get query with 7000 updates at once.

MySQL may need more RAM to process query like this.

If you cant reconfigure MySQL you have to split your big query to smaller queries somehow.

You may also read this for more information:

devshed - MySQL server has gone away

You asked:

Any possibility to run multiple queries even faster?

there is no simple answer for that question. It depends on query, database schema etc.

Increasing MySQL cache size in configuration file may help a lot in most cases related with big simple updates with not much computing, because database engine will operate on RAM memory, not on hard disk. When big cache is used - sometimes first big query may be slower, because data is not yet loaded into RAM, but when it finally loads - queries that need a lot of read/write operations will work much faster.

Added later: I assume your data processing needs php deserialize() function which may be hard to implement in pure SQL and you have to do it in PHP :) If you have access to server console you may create cron (linux sheduler) job, that call PHP script from shell during night.

Added later later After discussion in comments i have one more idea. You can make full database or one table backup from phpmyadmin, download it, restore data on home computer (on Windows you may use XAMPP, WAMP server). On your home computer you can run mysql.exe and process data locally.

Kamil
  • 13,363
  • 24
  • 88
  • 183
  • I want to log accesses to the site and I use a table with the first fields like ID, get, post, server, cookies. I store the serialized variabiles in thosed field, at access times. When I access the log, I deserialize (at this point I run those 7000 UPDATEs) those fields and write them in auxiliary fields, like server_HTTP_REFERER, server_REMOTE_IP, or post_page. I want to export the table and analyze it in a spreadsheet – Costel Socianu Oct 27 '12 at 16:51
  • I see. Can you reconfigure that server? – Kamil Oct 27 '12 at 16:59
  • I was thinking trying to save the updates in one big SQL file and run it with mysql command but I don't think i can run command line from php. – Costel Socianu Oct 27 '12 at 17:07
  • ... and i assume - you cant process that data during insert, because that may ruin site performance, right? – Kamil Oct 27 '12 at 17:09
  • I think you should group them in 10-50 query groups and run them one after one in some loop. It will take some time, but log analysis is not frequent operation and you can run it before sleep :) Other option is database backup and restore on home computer (as i wrote in my answer). – Kamil Oct 27 '12 at 17:17
  • Thanks for all the opinions and guidance. I decided to process only part of data on every refresh so that it mysql server doesn't complain about too large packet and php server about exceeding maximum execution time. 5 refreshes and then I will be able to export the data. – Costel Socianu Oct 27 '12 at 18:22
  • For your information: maximum execution time is set in PHP configuration on server (if im not wrong - in php.ini file). – Kamil Oct 27 '12 at 18:33
0

I found a limit of 16 field/value pairs on an INSERT statement. Beyond that number I got a "Forbidden" error. My total INSERT statement length on the working statement was 392 characters.

Martin Hughes
  • 16
  • 1
0

Use a for loop to do any massive work and just use regular mysqli_query. I got over 16000 queries to go in like that. Some things have to be changed in the php.ini file also. post mb size needs to change. Make it as big as you can if your sending a lot of characters. var input max should be changed also if your sending a lot of different variables. Make php memory size bigger. Make sure your not running out of system memory when running when running the queries. If you do everything right you could send over 20000 queries. Set php memory to at least 256mb. You might need to increase the timeout times from 30 and 60 to 200 or higher sometimes if your sending really large amounts of queries. If your don't change any settings you will have your post fail even if everything is true. PHP will make the script conditions false if your going beyond any php.ini setting limits/maxes. You don't have to change any mysql settings doing it one by one. It will take some time if your inserting or updating anything over a 1000 queries.

Josh
  • 1
  • 1