Mysql crash after foreach create

[nezzard]

Hi, i try to create posts inside foreach, but i think something wrong, bcs my mysql crash after that, can you tell me, how i can fix this?
Maybe something like delay?

result.forEach(function(item){
	wp.song().search( item['artist']+' - '+item['song'] ).then(function( posts ) {
		if(!posts[0]){

			var myRequests = [];
			myRequests.push(rp({uri: "http://ws.audioscrobbler.com/2.0/?method=artist.getinfo&artist="+encodeURIComponent(item['artist'])+"&api_key=[key]&format=json", json: true}));
			myRequests.push(rp({uri: "https://www.googleapis.com/youtube/v3/search?part=snippet&order=viewCount&q="+encodeURIComponent(item['artist']+'+'+item['song'])+"&type=video&key=[key]", json: true}));
			Promise.all(myRequests)
			  .then((arrayOfHtml) => {
			  	console.log(arrayOfHtml[0]['artist']['image'][2]['#text']);
			    console.log(arrayOfHtml[1]['items'][0]['id'].videoId);


					wp.posts().create({
					    title: item['artist']+' - '+item['song'],
					    content: 'Your post content',
					    status: 'publish'
					}).then(function( response ) {
					    console.log( response.id );
					})

			    
			  })
			  .catch(/* handle error */);


		}
	});
})

[kadamwhite]

@nezzard Apologies for the delayed response, I have been away from Github for the holidays.

The most likely issue you're running into is that too many requests are being made at once—if result is large, the forEach executes in a few milliseconds, so all the requests are dispatched at once and that overwhelms MySQL. The best way around this is to dispatch the requests in sequence, instead of in parallel, by chaining each new request onto the promise from the prior request: this is easily accomplished using reduce, and we can write a small helper to assist with batching.

Note: I've removed your API keys from your post, and they are represented below by the keys object. I recommend not sharing your own API keys in github posts, just so that other developers aren't able to use your account in their own applications.

var WPAPI = require('./wpapi');

var wp = new WPAPI({
  endpoint: 'http://wpapi.loc/wp-json',
  username: 'apiuser',
  password: 'password'
});

var result = [
  { artist: 'Bran Van 3000', song: 'Saltwater Cats' },
  { artist: 'Shonky', song: 'Olympia' },
  { artist: 'BT', song: 'The Only Constant Is Change' },
  { artist: 'The Avalanches', song: 'Since I Left You' }
];

// Using Axios for remote API requests
var axios = require('axios');

function createSong(item) {
  // The logic in this function is identical to the `forEach` function in your
  // question except for skipping the initial `wp.song()` query, which is not
  // relevant for demonstrating how to throttle the post creation requests
  var artist = encodeURIComponent(item.artist);
  var song = encodeURIComponent(item.song);

  // Kick off the API requests
  var myRequests = [
    axios.get(`http://ws.audioscrobbler.com/2.0/?method=artist.getinfo&artist=${artist}&api_key=${keys.audioscrobbler}&format=json`),
    axios.get(`https://www.googleapis.com/youtube/v3/search?part=snippet&order=viewCount&q=${artist}+${song}&type=video&key=${keys.google}`)
  ];

  // Once they return, create the song item
  return Promise.all(myRequests)
    // This step just simplifies the results because I'm using Axios for the requests
    .then((resultsArr) => resultsArr.map(result => result.data))
    // Now we digest the results and create our post
    .then((resultsArr) => {
      var artist = resultsArr[0].artist;
      var artistImage = artist.image.find(img => img.size === 'medium');
      var artistImageTag = artistImage ?
        `<img src="${artistImage['#text']}" alt="${artist.name}">` :
        '';

      var similarArtists = artist.similar.artist
        .map(similar => similar.name)
        .join(', ');

      var videoId = resultsArr[1].items[0].id.videoId;

      // I'm using wp.posts() since my sample site doesn't have a song CPT
      return wp.posts()
        .create({
          title: `${item.artist}-${item.song}`,
          content: `${artistImageTag}\n\nVideo ID: ${videoId}\n\nSimilar Artists: ${similarArtists}`,
          status: 'publish'
        })
        .then((createdPost) => {
          console.log(createdPost.id, createdPost.title.rendered);
        });
    });
}

result
  // Reduce over the items, creating a new one only when the last request
  // chain is complete
  .reduce((lastRequestDone, item) => {
    return lastRequestDone.then(() => createSong(item));
  }, Promise.resolve())
  // Error handling
  .catch(e => console.error(e));

The reducer function at the end could easily be adapted to batch the requests, making only 5 or so at once, which WP should be able to handle; that'll get you through a large set faster than going one by one. You can use _.chunk or a similar method of your own to split a large array into many arrays of five items:

// The above snippet has been tested, and works; this on the other hand is
// basically pseudocode but should point you in the right direction
_.chunk(result, 5)
  // Create posts within each chunk in parallel, and process chunks one
  // after another
  .reduce((lastRequestDone, batch) => {
    return Promise.all(batch.map(item => createSong(item)));
  }, Promise.resolve())
  // Error handling
  .catch(e => console.error(e));

Let me know if the above works, hope this is helpful!

[nezzard]

@kadamwhite Thanks. it's working for me. Reduce solved my problem.
Pls can you tell me, how i can limit cycles? For example 5 cycles _.chunk give me an error

Sorry, for my English

[kadamwhite]

No worries; what error are you receiving?

[nezzard]

@kadamwhite
_ is not defined

[kadamwhite]

Oh, got it! _ would be Lodash, which you'd need to load specifically:

var _ = require('lodash')

Or else you could use the lodash.chunk library itself

var chunk = require('lodash.chunk')

One or the other option would need to be installed. But chunk itself is an easy function to write; you just want to loop over one long array and break it into smaller arrays of a certain size. I can demonstrate when I'm at a computer later on.

[nezzard]

I have installed lodash and lodash.chunk, but again i got an error
TypeError: _.chunk is not a function

[kadamwhite]

Without seeing your code, I don't know how to diagnose that issue; if you've installed the NPM package into your project you should be able to require and import it into your code and use it without trouble. Not sure what could be going wrong.

Out of academic interest, here's roughly how you could write your own chunking function:

function chunk(arr, size) {
  return arr.reduce(function(chunks, item) {
    // Find the "active" chunk, which is always the last one in the array
    var idx = chunks.length - 1;
    if (chunks[idx].length === size) {
      // Last chunk is full: start the next
      chunks.push([item]);
    } else {
      // otherwise, continue filling the previous chunk
      chunks[idx].push(item);
    }
    return chunks;
  }, [ [] ]);
}

chunk([1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20], 3)
// [ [1,2,3], [4,5,6], [7,8,9], [10,11,12], [13,14,15], [16,17,18], [19,20] ]

Or, for a more concise (but less readable) implementation with ES:

const chunk = (arr, size) => arr.reduce((chunks, item) => (
  chunks[chunks.length - 1].length === size ?
    chunks.concat([
      [item]
    ]) :
    chunks.slice(0, chunks.length - 1).concat([
      chunks[chunks.length - 1].concat([item])
    ])
), [ [] ]);

[kadamwhite]

It occurs to me: What version of lodash are you using? Do you get the error if you use

var chunk = require('lodash.chunk')

chunk(arr, size)

without the _? You'd only need the _.chunk if you're using the full lodash package.

[nezzard]

@kadamwhite

_.chunk(result, 5).reduce((lastRequestDone, item) => {

	var songArray = new Array ({'key': item['id'], 'artist': item['artist'], 'song': item['song']});
	songArray = JSON.stringify(songArray);

  				return lastRequestDone.then(() => 
  				wp.song().search( item['artist']).then(function( posts ) {
  				//Берем миниатюру из первого поста, если артист найден
					return posts[0]['featured_media'];
  				}).then(function(data){  			
		  			return lastRequestDone.then(() => wp.song().search( item['artist']+' - '+item['song'] ).then(function( posts ) {
		  				
		  				//Ищем композицию по артисту и названию песни
		  						if(!posts[0]){
		  							 return lastRequestDone.then(() => postSong(songArray, myJson));
		  						}
		  						else{
									//Если посты найдены, пушим их в массив и отправляем клиенту
									console.log("Посты найдены ");

									if(posts[0]['featured_media'] === 0){
										//Отправляем клиенту массив БЕЗ миниатюрой 
										//myJson.push({key: result[i]['id'], post: posts[0], end: 0});
										return lastRequestDone.then(() => writet({key: item['id'], post: posts[0], end: 0}));
									
									}
					  				wp.media().id( posts[0]['featured_media'] ).then(function(media){
					  					//Отправляем клиенту массив с миниатюрой 
					  					//myJson.push({key: result[i]['id'], post: posts[0], end: media.guid.rendered});
					  					return lastRequestDone.then(() => writet({key: item['id'], post: posts[0], end: media.guid.rendered}));
									});			  							
		  						}										

				  		}));
  					}).catch(function( err ) {
					    return lastRequestDone.then(() => postSong(songArray, myJson));
						// Тут должна быть функция, если нету картинки в исполнителе который уже был postSong()			

					}));

  					//	writet(myJson);

 }, Promise.resolve());

[nezzard]

@kadamwhite Latest version

[kadamwhite]

@nezzard Can you share the code you are using to import _? Is this still an issue?