0

I am trying to insert a very large amount of data into a MySQL database using this basic code:

$query = "INSERT INTO PublicNoticesTable (url, county, paperco, date, notice, id) VALUES " . $queryString;
$result = mysqli_query($connect, $query);

The $queryString is generated beforehand and contains thousands of lines of information in the format of:

('a', 'b', 'c'),('a', 'b', 'c');

That way I can insert multiple rows of data in one mysql call and to reduce the processing time. I have the ability to throttle the amount of information that goes into $queryString and when I have a smaller amount of information the query succeeds. YAY! When I put a little bit too much information the query fails. If anyone could provide a solution for me it would be greatly appreciated. I am using GoDaddy and PHPMyAdmin, if that's important.

Thank you!

Pixelknight1398
  • 537
  • 2
  • 10
  • 33
  • 1
    What error do you get when the query fails? – BeetleJuice Jul 29 '17 at 03:08
  • 1
    please show the query that is failing with the error message to debug – Dhaval Chheda Jul 29 '17 at 03:08
  • There is no error message? If there is I am not sure how to retrieve it. All I know is that `$result` returns false – Pixelknight1398 Jul 29 '17 at 03:09
  • atleast write the query which is failing because we cannot understand what you mean by little too much information .. I mean just a sample query with hard coded values – Dhaval Chheda Jul 29 '17 at 03:11
  • if it returns false, then you need to use `mysqli_error($connect)` on the query, see if that yields anything. – Funk Forty Niner Jul 29 '17 at 03:12
  • I inserted `mysqli_error($connect)` into the fail statement and I get a white screen of death, the actual query doesn't run anymore even with small amounts of data. I can't share the actual data contained in the query as it is sensitive information. I can say that it is properly formatted because obviously the query works with small amounts, and doesn't with large amounts. – Pixelknight1398 Jul 29 '17 at 03:16
  • Add mysqli error reporting. It's widely documented – Strawberry Jul 29 '17 at 07:12

2 Answers2

2

If you are trying to insert the values using INSERT ... VALUES pattern then you have the limit of max_allowed_packet.When I put a little bit too much information the query fails then why not now debug mysqli_error($connect)

From Docs :

To optimize insert speed, combine many small operations into a single large operation. Ideally, you make a single connection, send the data for many new rows at once, and delay all index updates and consistency checking until the very end.

MySQL client or the mysqld server receives a packet bigger than max_allowed_packet bytes, it issues a Packet too large error and closes the connection.

Try to view what is the max size of packet

show variables like 'max_allowed_packet';

The SQL length is limited by 1M default, you can change max_allowed_packet parameter to support more bigger single insert.

A l w a y s S u n n y
  • 36,497
  • 8
  • 60
  • 103
  • How would I go about locating this 'max_allowed_packet' value? – Pixelknight1398 Jul 29 '17 at 03:58
  • @Pixelknight1398 see here https://stackoverflow.com/questions/8062496/how-to-change-max-allowed-packet-size?answertab=active#tab-top and https://stackoverflow.com/questions/5688403/how-to-check-and-set-max-allowed-packet-mysql-variable?answertab=active#tab-top – A l w a y s S u n n y Jul 29 '17 at 04:10
0

You could make sure that the maximum length for your columns is enough to incorporate your data. If your data is really long (more than about 10,000 characters in each column), you could try using a blob.

Programmer
  • 1