No, that's precisely the situation where markets work best: when resources are scarce. A single gallon of water isn't worth much to a data center or a farmer; you can't water a field with just one gallon. But it's potentially worth a lot to someone who's thirsty. Prices will go up, demand will drop, and supply will increase to meet demand, unless you kneecap that process by imposing artificial price controls - then you'll have shortages.
> you can have people who need it but are too poor to pay what others will pay for it
You're right, markets are not charities; they're only concerned with efficiency. Caring for people who can't take care of themselves isn't efficient, but it is the right thing to do. Even in this situation though you're better off with markets than without them, because it's way easier for people, organizations, and governments with excess resources to provide for the needy when they're operating in an efficient environment than in an inefficient one.
> If someone [...] buys all the water
That's called a monopoly and I agree that's not good, because if one person owns/controls everything it's no longer a free market; you're essentially back to central planning. Individuals and data centers should be able to buy their water in a competitive market, not one dominated by a single supplier.
> It also doesn't work when the product is common use, like a road or a park
Markets are actually great at allocating things like road space. NYC's congestion pricing is doing wonders for the efficiency of their road system right now, and tolls have been a thing basically forever. But I agree in principle it's hard for markets to allocate resources that there's no practical mechanism of charging for. Thankfully, water generally doesn't fall into that category.
