Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

According to the restrictions on stored routines and triggers, dynamic sql cannot be used (restriction lifted for stored procedures in version 5.0.13 and later). Why is this limitation in place? And why lift it for procedures, but not functions or triggers?

share|improve this question
    
adding a bounty to see if we can draw some more info out. –  Derek Downey Jan 14 '11 at 14:09
    
This is actually a good internals question that MySQL users should be aware of. +1 !!! –  RolandoMySQLDBA Jun 21 '11 at 16:39

3 Answers 3

up vote 5 down vote accepted

Just hearing the question makes me think of two aspects:

ASPECT #1 : Functions are supposed to be DETERMINISTIC

If this so, this implies that a function should present the same return data consistently for a given set of parameters, NO MATTER WHEN YOU CALL THE FUNCTION.

Now, imagine a function that produces a different answer because of gathering data at different times of the day based on static SQL in the function. In a sense, that can still be considered DETERMINISTIC if you query the same set of tables and columns every time, given the same set of parameters.

What if you could change the underlying tables of a function via Dynamic SQL? You be violating the definition of a DETERMINISTIC function.

Notice that MySQL added this option in /etc/my.cnf

log-bin-trust-function-creators

Although this may be an oversimplification to say, this allows functions to be allowed to write data into binary logs without strictly enforcing the DETERMINISTIC property.

ASPECT #2 : Triggers should be able to be rolled back

  • Could you imagine a trigger with all the same behaviors as a function and then introducing Dynamic SQL into the mix?
  • Could you imagine trying to apply MVCC (Multiversion Concurrecy Control) against Dynamic SQL after applying MVCC to the base table the trigger was meant for?

You would essentially have data that grows quadratically (even exponentially) just in MVCC alone. The process of managing the rollback of SQL with triggers that can be non-DETERMINISTIC would be ungodly complex, to say the least.

In light of these two aspects, I'm sure MySQL Developers thought of these things and quickly dismissed them by imposing restrictions.

So, why lift the restriction for Procedures? Simply put, there is no concern over DETERMINISTIC properties or Rollback.

share|improve this answer
2  
Can't be so "ungodly complex" if other DBMS can very well support MVCC and dynamic SQL in triggers. –  a_horse_with_no_name Apr 24 '12 at 20:38
1  
Actually, @a_horse_with_no_name, you are right. For Oracle and PostgreSQL, ungodly complex has been coded. +1 for your comment. MySQL has the handicap of dealing with multiple storage engines so rollback and determinism may require overlap of storage engine operations. That's a little scary. Maybe someone will have the guts to push "ungodly complex" into InnoDB in full. Even more desirable would be to create storage-engine specific trigger implementation in such way it does not allow mixing storage engines within the same query. –  RolandoMySQLDBA Apr 24 '12 at 21:03

Largely, it is due to security. The exception for procedures is because the dynamic SQL within procedure can be assigned the security context of the executing user. This means that even though the engine does not know what is going to be executed, it can make certain that the user is permitted to access the referenced object(s).

Beyond that, you can raise the ugly issues of what could happen, were it permissible.

share|improve this answer

This is a great question, but I don't know the answer. I imagine this is going to have to goto the internals team, but I don't know that they'll be big into this site. In the meantime, I can help you deduce some answers.

For starters I see this:

The trigger cache does not detect when metadata of the underlying objects has changed. If a trigger uses a table and the table has changed since the trigger was loaded into the cache, the trigger operates using the outdated metadata.

Which makes me think that that has something to do with it. It's not going to be recompiling SQL if it's not even monitoring the metadata. Which means it's an engine concern.

By the same token, when I read this block I think the same thing (engine):

To prevent problems of interaction between server threads, when a client issues a statement, the server uses a snapshot of routines and triggers available for execution of the statement. That is, the server calculates a list of procedures, functions, and triggers that may be used during execution of the statement, loads them, and then proceeds to execute the statement. This means that while the statement executes, it will not see changes to routines performed by other threads.

So all in all I'm not entirely sure why they don't allow it, but I can guess. Sorry that I can't help you more, I'm open to sussing this out some more. Best is to hope for some active MySQL devs once we leave private beta ;)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.