Insert only adds upto 1000 records and ignoresall records after that

i have a large database where the client stores personal messages and fire email notifications [if allowed by the users]. certain users have the option of sending messages to their entire network of friends. some users have over 5000 friends in their network so if they select the whole network they'll be sending messages to over 5000 friends and system will store all the messages into a table. the problem is this that it does not insert more than 1000 records and ignores all inserts after the first 1000. i have increased the packet size, bulk_insert_buffer_size but still no luck. since the system stores some of the info in another table for reports, every insert returns its new message id. due to this i can not use the "insert into table (column1,column2) values (value1,value2) , (value1,value2)....etc."

table engine is innodb, mysql version is 5.1.3 and is hosted on amazon web services. all i want is to fix this issue of inserting more than 1000 records at a time. as mentioned earlier, it works fine but only up to 1000 records and simply ignores all the records after that.

i'm using php foreach(){} to insert message for each friend and if email is available, send notification to the user. this foreach(){} also inserts the same record in another table [with only 3 开发者_高级运维columns] for generating reports.

Have you tried to split the query into more sub-queries? Maybe inside a single transaction (since you use InnoDB)?

I'm not sure if Amazon limits the number of insertions for each query, but as soon as they don't limit the number of SQL queries you can do more INSERTs and should be able to fix.

By the way, are you sure that the plaintext query inserts all 5000 elements? Could it be your code returning a plaintext query with up to 1000 rows? Do you have any LIMIT statements in your SELECT queries that I suppose you perform when reading data?

there is unnecessary to perform insertion via looping

you can assign a unique transaction ID like below

create sent_message 
(
  user_id int(10), 
  friend_id int(10), 
  batch_id int(10), 
  message text
);

/* user 1001 sent message to all his 5000 friends */
$timestamp = time();

insert into sent_message 
  select 1001, friend_id, $timestamp, friend_id, '$message'
  from user_friend where user_id=1001;

/* email notification */
select email 
from 
  user_profile
inner join sent_message
on user_profile.user_id=sent_message.friend_id
where 
  sent_message.uid=1001 and 
  batch_id=$timestamp and
  user_profile.email!='';

/* loop the result and send the email notification
   or to attach all the emails into an email alias, 
   then send via assigned email alias */