technically, you can DECLARE HANDLER to intercept this error. You can even wrap that in a nice procedure, like (not tested)

CREATE PROCEDURE START_TRANSACTION_IF_NOT_EXISTS()

BEGIN

  DECLARE EXIT HANDLER nop FOR MYSQL_ERROR_CODE 1422 BEGIN END;

  IF NOT @@in_transaction THEN

    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

    START TRANSACTION;

  END IF;

END

I think that the correct syntax is:

DECLARE CONTINUE HANDLER FOR 1422 BEGIN END;

But for some reason, the result is the same when you call the procedure from a trigger. I have no idea if it is a bug or not.

Just to be clear: an error occurs because even this snippet causes an error, if it is inside a trigger:

IF FALSE THEN

	START TRANSACTION;

END IF;

Okay. This fails because START TRANSACTION is not allowed inside a trigger. Not even if hidden inside a stored procedure. There's an early check (in the CALL statement itself) that the called procedure doesn't have START TRANSACTION. And there's also a run-time check inside START TRANSACTION itself. You suggest to work around it by introducing a new statement START TRANSACTION IF NOT EXISTS that is allowed inside a stored procedure called from a trigger.

But then, I think, an easy fix would be simply to allow START TRANSACTION directly and only keep the run-time check.

But here're two more thoughts:

• this logic can also be applied to a COMMIT and ROLLBACK. Really, it can be applied to a lot of statements, to most DDL-s too, as they cause an implicit commit.
• in most cases START TRANSACTION in a trigger is an error. Your usage with an IF is really a corner case. In vast majority of the use cases the early check is a good thing.

This means that logically we should disable early checks completely for all statements (as they all can be put under IF FALSE THEN, right?), but also in almost all cases the early checks are useful.

May be a solution to these all issues would be to allow you to disable this early check per procedure? Like, declare a procedure “safe to be invoked from a trigger”. Then CALL won't check anything and START TRANSACTION will fail when executed from a trigger. It'll be up to you to protect all these dangerous statements with IF-s to make sure they don't break a trigger.

I see your points. Sounds like a good idea. I suspect that other (corner?) cases would benefit from this change, when we have a trigger (or a function?) calling a stored procedure.