I read several articles on this including CBC. I thought this was the best, but others note that

Italy and Greece were also countries opposing the invitation to associate status - whatever that really means. Germany and France also opposed it with Germany calling the deal a "red line" that they would not cross.

The only other case in the EU of a partial status is Ukraine - the member countries all voted on bringing them in however which is clearly not going to happen here for Canada, at least at this time. von der Leyen overextended her powers by making the offer, and several of the bigger countries of the EU did not appreciate being strong armed.

I agree with you that in the long run, Carney's failure here is for the better for Canada whose best deal lies with the US - even now though it will be less beneficial than before carney started to grand stand as well as try to meddle in American politics.