PHP Max amount of inserts in one SQL query

Question

I have a pretty simple question. I am inserting a lot of records at once in a MySQL table. It works for about 2000 records (actually a bit more). But say I want to insert 3000 records, than it doesn't do anything.

I'm working through AS3 sending an array containing all the records via AMFPHP to a simple PHP script to parse and insert the array.

Is this normal, or should I look into it?

Currently I'm slicing my array in parts of 2000 records, and sending a couple AMFPHP requests instead of just 1.

Selfeducated

Answer 1

PHP's queries are limited by the "max_allowed_packet" configuration option. It defines the absolute length limit, in characters, that a query string can be. Note that this isn't just the total size of the data being inserted, it's the entire query string. SQL commands, punctuation, spaces, etc...

Check how long your 3000 record version is vs. the 2000 one, and then get your server's packet length limit:

SHOW VARIABLES WHERE Variable_name LIKE  '%max_allowed_packet%'

If your 3000-record version is longer than this limit, the query will defnitely fail because it'll be chopped off somewhere part-way

Wareing

Thank you! Also thanks to the other comments. I didn't know this so thanks for the info. – Selfeducated

It shows 16777216. What does it means bytes, kb or characters length? – Livingston

Answer 2

I don't think there is really a limit in the number of inserts in one query.

Instead, there is a limit in the size of the query you can send to MySQL
See :

• max_allowed_packet
• Packet too large

So, basically, this depends on the amount of data you have in each insert.

Thorlay

Answer 3

I would ensure max_allowed_packet is larger than your PHP SQL query.

http://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html

Gilolo

Answer 4

I think PHP doesn't limit the amount of inserted query at one, instead its limit the amount of the memory usage that can be taken by script, and max time of the execution.

Kwa