Could it really take more than two weeks, a month or even longer for a transaction to be confirmed??? Wow. I thought they are “cancelled” at some point.
As long as they stay in the mempool of a miner, they will get picked up by a miner when they have no other more profitable tx to include in a block.
However, if your tx is using a too low fee rate, it will get purged since mempools have size limits. For example, in this screenshot you can see that the mempool.space node is currently using 1.6 GB for its mempool and that it purges tx with a fee rate below 18.2 sat/vB:
The 300MB is the default mempool size on a Bitcoin Core node:
In Bitcoin Core there is a memory pool limiter which can be configured with -maxmempool=<n>, where <n> is the size in MB (1000). The default value is 300.