Exactness of the Tensor Functor This might turn out to be a very stupid question, so I apologize in advance, but it is confusing me a little bit.
I know in general that if $$M'\rightarrow M\rightarrow M''\rightarrow 0$$ is an exact sequence of $A-$modules, then $$M'\otimes_AN\rightarrow M\otimes_AN \rightarrow M''\otimes_AN \rightarrow 0$$ is an exact sequence for any $A-$module $N$.
If instead we are given an exact sequence of the form: 
$$M'''\rightarrow M'\rightarrow M\rightarrow M''\rightarrow 0$$ where $M'''$ is not the zero module (because I know that tensoring with a module doesn't necessarily take injective maps to injective maps), then I guess my question is why isn't the following an exact sequence as well?
$$M'''\otimes_A N\rightarrow M'\otimes_AN\rightarrow M\otimes_AN \rightarrow M''\otimes_AN \rightarrow 0$$ 
What is going morally wrong here? I guess i'm wondering where exactly is the proof of the first case failing to show that the second sequence is exact at $M'\otimes_AN$?
I would appreciate very much if anyone could clarify my confusion. I am probably just being stupid about this :/
 A: Ok so we have an exact sequence 
$$
M''' \to M' \to M \to M'' \to 0
$$
Let's break it up a bit. I'm going to denote by $\alpha$ the map $M' \to M$ and by $\beta$ the map $M \to M''$. Then we can break up the exact sequence above as two exact sequences
$$
M''' \to M' \to \operatorname{im} \alpha \to 0
$$
and 
$$
0 \to \ker \beta \to M \to M'' \to 0.
$$
Tensoring, we see that the first becomes the exact sequence
$$
M''' \otimes N \to M'\otimes N \to \operatorname{im} \alpha \otimes N \to 0 \enspace \enspace \enspace (*)
$$
and the second becomes the exact sequence
$$
\ker \beta \otimes N \to M \otimes N \to M'' \otimes N \to 0 \enspace \enspace \enspace (**).
$$
From exactness of the original sequence we know that $\operatorname{im} \alpha = \ker \beta$ so that $\operatorname{im} \alpha \otimes N = \ker \beta \otimes N$. However, it is no longer necessarily true that this is a submodule of $M\otimes N$ as the tensor product with $N$ is not necessarily left exact. 
By exactness of $(*)$, $\operatorname{im}(M''' \otimes N \to M' \otimes N) = \ker(M'\otimes N \to \operatorname{im}\alpha \otimes N = \ker \beta \otimes N)$. However, now the map $\ker \beta \otimes N \to M \otimes N$ might not be an inclusion, there might be a nontrivial kernel. Thus, $\ker(M'\otimes N \to \operatorname{im}\alpha \otimes N = \ker \beta \otimes N)$ might not necessarily be equal to the kernel of the composition to $M \otimes N$, $\ker(M' \otimes N \to M \otimes N)$. Therefore, the longer sequence might not be exact even though the two short exact sequences are. 
This is the general idea even if we try to extend it. A long exact sequence is really just a bunch of short exact sequences at each step and tensoring won't always preserve the inclusion of that kernel and so it won't always preserve the long exact sequences. 
A more concise way to say this is that tensoring does not preserve kernels, $\ker \beta \otimes N \neq \ker (\beta \otimes \operatorname{id}_N)$ in general so in general it can't preserve longer exact sequences where we need something mapping to a kernel to still stay exact.
A: Some Counterexamples
There are many counterexamples to this statement - clearly, there has to be, for otherwise flat modules would be everywhere. And perhaps the best way to see why it fails is to examine some examples. Here is a class of examples that have two advantages: they are easy to compute with, and show something a little different than working over principal ideal domains (it's safe to ignore the parenthetical remarks):
Consider $\mathbb{Z}[x]$-the integer polynomial ring. Here is an example of an exact sequence of $\mathbb{Z}[x]$-modules:
$$
0\to \mathbb{Z}[x]\xrightarrow{\alpha} \mathbb{Z}[x]^2\xrightarrow{\beta}\mathbb{Z}[x]\xrightarrow{\gamma} \mathbb{Z}/2\to 0
$$
The maps are defined by


*

*$\alpha(f) = (2f,xf)$ 

*$\beta(f,g) = 2g - xf$ 

*$\gamma$ the quotient map via the ideal $(2,x)$.


(This is just a special example of something called the Koszul complex given by the regular sequence $(2,x)$ - any two relatively prime elements generating a proper ideal will work, since $\mathbb{Z}[x]$ is a GCD domain.) Here are two examples to show why tensoring does not keep this exact:
Example 1
One can apply the functor $\mathbb{Z}/2\otimes_{\mathbb{Z}[x]}-$ to get the complex
$$
0\to \mathbb{Z}/2\xrightarrow{\alpha} \mathbb{Z}/2\oplus \mathbb{Z}/2\xrightarrow{\beta}\mathbb{Z}/2\to\mathbb{Z}/2\to 0
$$
Here, $\mathbb{Z}/2$ is a $\mathbb{Z}[x]$ module via the quotient map $\mathbb{Z}[x]\to \mathbb{Z}/2$, so $2$ and $x$ act trivially, and hence $\ker\beta = \mathbb{Z}/2\oplus\mathbb{Z}/2$ whereas $\operatorname{Im}\alpha = 0$. In other words, $\operatorname{Tor}_1^{\mathbb{Z}[x]}(\mathbb{Z}/2,\mathbb{Z}/2) = \mathbb{Z}/2\oplus\mathbb{Z}/2$. Note, to compute derived functors we "should have" chopped off the $\mathbb{Z}/2$ but that doens't matter since we are not computing the zeroth homology.
Example 2
Here is a more interesting example, still working with $\mathbb{Z}[x]$-modules: Consider the following $\mathbb{Z}[x]$-module: the underyling abelian group structure is given by $V = \mathbb{Z}/4\times\mathbb{Z}/4$. There is an endomorphism of $V$ as abelian groups given by the matrix
$$T=
\begin{pmatrix}
2 & 2\\
0 & 1
\end{pmatrix}
$$
Letting $x$ act via $T$ makes $V$ into a $\mathbb{Z}[x]$-module. Applying $V\otimes_{\mathbb{Z}[x]}-$ to the exact sequence gives a chain complex
$$
V\xrightarrow{\alpha} V^2\xrightarrow{\beta} V\to V\otimes_{\mathbb{Z}[x]}\mathbb{Z}/2\to 0
$$
Now $\beta([(1,0),(1,0)]) = (2,0) - (2,0) = 0$. However, $\alpha(a,b) = [ (2a,2b), (2a + 2b,b)]$. So the complex is not exact at $V^2$.
To Produce Your Own...
...find rings that have non-flat modules and modules with free resolutions of length at least two. Most of the time you should get a winner.
A: If I understand the question correctly, you already knew that, when 
$$
M'''\to M'\to M\to M''\to 0
$$
is exact,
$$
M'''\otimes_AN\to M'\otimes_AN\to M\otimes_AN\to M''\otimes_AN\to 0
$$
could fail to be exact when $M'''=0$, and you tried to avoid that difficulty by assuming that $M'''\neq0$.  Unfortunately, that doesn't really avoid the problem. If you have a counterexample with $M'''=0$, then you can manufacture a counterexample another with any non-zero $M'''$ that you want. Just put your desired $M'''$ in place of the $0$ and define the map $M'''\to M'$ to be the zero map.  
OK, so probably the next thing to try is to assume that not only the module $M'''$ but also the map $M'''\to M$ has to be non-zero.  That avoids the construction I just described, but it still doesn't avoid the problem.  Take your counterexample for the case where $M'''=0$, and modify it as follows.  Put any desired non-zero $M'''$ in place of the $0$, and also replace $M'$ with $M'\oplus M'''$.  Then define the map from $M'''$ to (the new) $M'$ to be the inclusion of the second summand.  EDIT: I should have also said that the map from the new $M'$ to $M$ is the composition of the projection from the new $M'$ to the old with the original map from the old $M'$ to $M$.
In effect, what I've done here is to splice together your original short exact sequence with the (even shorter) exact sequence $M'''\to M'''\to 0$.  That's the simplest case of (the reverse of) what Dori Bejleri explained about breaking longer exact sequences into several short exact sequences.
