The problem isn't exactly lack of mystery. It's lack of risk. Current magic in RPGs is 100% safe and has no drawbacks to speak off. You can use and abuse it all you want and nothing will ever go wrong. Which is complely contrary to ancient myths were magic demanded big sacrifices and/either carried big risks.
But alas that's something completely unadmissible to modern day players.
Actually, not exactly, Mythic magic spans an enormous span of time, of which only a short period included risky/costly magic. This being a legacy of the church's stance on supernatural powers which are not derived from God. This in turn was a legacy of the suppression of other rival religions, which indeed had claims of powers other than God at work, combining them all into the work of an Adversary.
Earlier magic stems from several primary sources, superstitions which worked, an intrinsic quality and the intervention of an interested supernatural entity.
The first is generally sympathetic magic or lore passed down. This is essentially 'scientific' magic, the power to make a better sword or charm by including certain rituals into the production process, without necessarily understanding why or how it works. It suffers from trial and error, with a hefty load of confirmation bias. It preserves mystery by obfuscating the relation between action and result. It is integral with the world(though RPGs put a resource mechanic in the way), and can be quite feasibly implemented as circumstantial magic, a collection of rituals that can be performed, given the preconditions, to achieve an outcome. Unfortunately, it's ridiculously involved to design, to come up with rituals for the right situations, broad enough to be useful, narrow enough to avoid universal applicability.
The second is inherent magic, the magic of being really strong or driven, the power of superior ore, genetics or conviction. This is the power of being the Other, all the magic that derives from the descendants of gods, relics of saints and heroes, the power of self improvement. When you are stronger, faster, tougher, smarter or more charismatic than others. This is kind of in most leveled RPGs already, even ignoring the explicit implementations like sorcerors, whenever a character survives the raw damage from a terminal velocity fall, leaps superhuman distances, or even pulls off some kind of absurd bluff, you are performing the inherent magic. Taken to the extreme, it's the power of being unique.
Finally, you have supernatural intervention. This is where the talk of sacrifices come in. All the song and dance, the offerings, the pomp and ceremony, it's aimed at attracting and directing the attentions of something more powerful, to do you a favor. It is dangerous because the Power has it's own will and motivations, which might not gel with your own, but, given their attention, none of the rest is necessary. Whether it's God or a horde of djinni to do something for you. Sacrifices are great because you have to stand out to get the attention of the more powerful, and only that. But this isn't really game magic so much as plot magic, involving intensive NPC intervention.
Now, to round it off, players don't like permanent costs for transient actions. GMs don't either. It gets in the way of the game, because if you have to pay out your ass each and every time you do something special, then the game grinds to a standstill, abilities unused. And unused abilities are a waste of game time and sheet space, unlike reality and myths, which have a distinctly limited special effects budget. Risk could be a factor, if not for the tendency of people to go for low hanging fruits of drama and comedy, even for the best of us. With game magic, you need to have a fair idea of what you are paying(in most cases, a spell slot), and what you are getting(a contained spell effect).
There is little room for uncertain character abilities, but this does not mean you cannot have uncertain magic. Just not
character intrinsic magic.
There is, of course, plenty of room for more freeform magic. There is even room for magic with horrific costs...provided they are not core parts of your character. In such cases the magic is a publicly available plot device, available for anyone with the appropriate criterion, knowledge and willingness to pay/take the risk. Anyone here knows Glorantha? Some of that can help relate.
