So I know this became a more pressing issue in these cities as population density increased, and that there have been events like the 'Great Stink' in London that acted as a trigger, but what I'm curious is how the political discussion was at that time. Who was against it? What were their arguments? Were the consequences of open sewers for public health common knowledge or initially more of a fringe opinion?
The "Great Stink" in the summer of 1858 was indeed the trigger for accepting Joseph Bazalgette's proposed sewerage system; during those months pretty much all governmental opposition to a new sewer system vanished. At that point, there were about 350 sewers in London, and hundreds of thousands of cesspits, which would be emptied periodically by "night soil collectors". The sewers were generally plumbed to newly-built, middle-class homes, which by the mid 19th century were perhaps more likely than not to have a flush toilet. Regardless of how it got there, though, pretty much all of the waste eventually ended up in the Thames. This included not only human excrement, but other waste: food scraps, bones, dead animal carcasses, and god knows what else.
The Thames is also where most of the city's drinking water came from. Today, the problem with that is glaringly obvious; back then, before germ theory was widely accepted, it was acknowledged as a problem for a different reason. Miasma theory, which was the predominant theory about the cause of disease back then, held that "bad air" (also sometimes referred to as "night air) was the cause of disease, and stinky, rotting organic matter (such as sewage and rotting dog corpses and big piles of food scraps and such) generated this "bad air". Miasma wasn't something in the air, but something which changed the air itself. John Snow and his famous map of cholera cases was radical specifically because he posited that cholera came from drinking water contaminated with something bad, rather than something in the air. The prevailing theory was that if you could keep the stink away from you, you'd be fine.
Regardless of your belief on the cause of disease, it was abundantly clear that by the mid-1800s, the Thames was a very big problem. It was disgusting on a level that those of us in the first world today probably can't imagine. Even holding to miasma theory, the Thames was a huge health risk, because it absolutely reeked of rot and excrement, a sure sign of miasma (and obviously, if you spent much time in or around the water, you were likely to get sick, which was used to support miasma theory). They didn't necessarily have the right specific cause identified, but the public health consequences of open sewers, leaky/overflowing cesspits, and the horrific state of the Thames were widely known and acknowledged.
Various solutions had been proposed for years, and of course there was opposition! Why? I'll let the BBC answer that:
Joseph Bazalgette...and his team built 82 miles of intercepting sewers parallel to the River Thames, and 1,100 miles of street sewers at a cost of £4.2 million.
Money. The cost was the primary concern. London was already a big city and was growing fast, and everyone knew that getting all that sewage (and its toxic miasma) out of there was going to be a big, complicated, and expensive project. Prior to the Great Stink, some people in power argued that dumping effluent in the Thames whisked it away, despite clear evidence to the contrary: the flow of the river simply wasn't enough to deal with the volume of waste, and the Thames is a tidal river. Stuff got a few miles downstream...and then was promptly washed back upstream when the tide came in.
Keep in mind this was also a time period when economic stratification was extremely pronounced. Yes, London was pretty gross for everyone (by modern standards) -- but the people living in the most crowded areas, the people whose only toilet was a decades-old leaky communal cesspit were not the ones in Parliament. Those living in Parliament may well have had a flush toilet in their home. They had country homes well beyond the city and its stench. They most certainly weren't hauling away night soil or sifting through waste looking for valuables on the shores of the river. As is all too common, there was a definite degree of remove between the day common situation and the people in power. It's worth considering one of the main reasons why the Great Stink was such a turning point: the Houses of Parliament are right on the river, and they were forced to confront the situation that many people had been living with for decades.
Sources:
https://www.bbc.co.uk/england/sevenwonders/london/sewers_mm/index.shtml
London: The Biography by Peter Ackroyd Thames: The Biography by Peter Ackroyd (Both of these are excellent reads if you're interested in the history of London)
https://www.museumoflondon.org.uk/discover/how-bazalgette-built-londons-first-super-sewer