How to affect last_insert_id() from trigger (MySQL/MariaDB)?

Question

MySQL

There is a table like this (InnoDB is required):

CREATE TABLE `tbl` (
  `id1` int(11) NOT NULL AUTO_INCREMENT,
  `id2` int(11) NOT NULL,
  PRIMARY KEY (`id1`,`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I want to achieve my own numbering for each id2 group, as here for MyISAM.
Added a trigger for this:

CREATE DEFINER=`root`@`localhost` TRIGGER `db`.`tbl_BEFORE_INSERT` BEFORE INSERT ON `tbl` FOR EACH ROW BEGIN
    SET NEW.id1 = (SELECT COALESCE(MAX(id1) + 1, 1) FROM `tbl` WHERE id2 = NEW.id2);
END

Everything works great, but last_insert_id() doesn't return NEW.id1 because the increment failed.
Actually how do I get last_insert_id() to give the value I want?
I tried not to assign NEW.id1 in the trigger, but to change the INCREMENT of the table:

SET @id = NULL;
SELECT COALESCE(MAX(id1) + 1, 1) INTO @id FROM `tbl` WHERE id2 = NEW.id2;
ALTER TABLE `tbl` AUTO_INCREMENT = @id;

And it didn't work because ALTER TABLE ends the transaction, which is not allowed in triggers.
Instead of ALTER TABLE, I tried SET @@[email protected], but it (as I understand it) has an effect when creating a table.

Answer 1

Let's start with the fact that AUTO_INCREMENT works after insertion, so you need to change it AFTER INSERT. In passing, I note that in AFTER UPDATE it is impossible to change the field value.
Further, an update in AFTER INSERT is also prohibited .
In total , practically the only solution in this case will be a storage that will change the necessary columns, wrapped in a transaction, for the "atomicity" of the operation.
But the main question is, why the hell do you set AUTO_INCREMENT if you yourself know better than the server what value the newly inserted record should have?