Wywołanie procedury z Triggera

Napisałem procedurę która rozdziela rekordy do dwu tabel z datą przed i po godzinie 15.00.
Ponieważ czynność ta potrzebna jest dwa razy dziennie chciałem uruchomić procedurę po zdarzeniu after insert,update lub delete.
Procedura i trigger działają, niestety próba wywołania procedury (call procedura) po zdarzeniach jw w bazie matce kończą się błędem.

Error Code: 1422. Explicit or implicit commit is not allowed in stored function or trigger.

procedura :

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_update_test`()
    MODIFIES SQL DATA
BEGIN
TRUNCATE game_14;
INSERT INTO game_14 (Select NULL, Id,Date,L1,L2,L3,L4,L5 FROM game_mm WHERE time(Date)<'15:00:00');
TRUNCATE game_21;
INSERT INTO game_21 (Select NULL, Id,Date,L1,L2,L3,L4,L5 FROM game_mm WHERE time(Date)>'15:00:00');
SET @id=0;
UPDATE ms.game_14 
SET 
    id = (SELECT (@id:=@id + 1))
WHERE
    1;
SET @id=0;
UPDATE ms.game_21 
SET 
    id = (SELECT (@id:=@id + 1))
WHERE
    1;
END

trigger onAfter Insert :

CREATE DEFINER=`root`@`localhost` TRIGGER `ms`.`game_mm_AFTER_INSERT` AFTER INSERT ON `game_mm` FOR EACH ROW
BEGIN
call ms.sp_update_test();
END

jeszcze struktura (wszystkie trzy tabele maja tę samą strukturę :

CREATE TABLE `game_mm` (
  `RecordId` int(11) NOT NULL AUTO_INCREMENT,
  `Id` int(11) DEFAULT NULL,
  `DATE` datetime DEFAULT NULL,
  `L1` smallint(6) DEFAULT NULL,
  `L2` smallint(6) DEFAULT NULL,
  `L3` smallint(6) DEFAULT NULL,
  `L4` smallint(6) DEFAULT NULL,
  `L5` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`RecordId`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

Trigger :

CREATE DEFINER=`root`@`localhost` TRIGGER `ms`.`game_mm_AFTER_INSERT` AFTER INSERT ON `game_mm` FOR EACH ROW
BEGIN
call ms.sp_update_test();
END

typowy INSERT wywołujący trigger:

INSERT INTO ms.game_mm VALUES
(NULL,9,'2018-12-05 14:00:00',5,14,23,24,25),
(NULL,10,'2018-12-05 21:00:00',5,21,33,34,35);

Alternnatywnym rozwiązaniem jest wywołanie procedury w konsoli MYSQL wołane z Crona ale Trigger wydawałł się bardziej eleganckim. W konsoli i z crona działa z triggera niestety nie.
pozdr
AK

1. Truncate robi niejawnego commita.
2. Spróbuj sobie zrobić commita w triggerze.

yarel napisał(a):

1. Truncate robi niejawnego commita.
2. Spróbuj sobie zrobić commita w triggerze.

dzięki też poczytałem o tym i pewnie się nie da bo zakaz dotyczy jawnych i nie jawnych wystąpień commita. (Explicit or implicit commit). Szkoda bo ten trigger byłby bardziej przydatny Ale dla pewności jutro sprawdzę i spróbuje.
pozdr
AK

Jaki jest cel w rozdzielaniu rekordów na 2 tabele, względem daty wstawienia? Tę funkcjonalność możesz zrealizować za pomocą partycjonowania.
https://dev.mysql.com/doc/refman/8.0/en/partitioning-types.html

yarel napisał(a):

Jaki jest cel w rozdzielaniu rekordów na 2 tabele, względem daty wstawienia? Tę funkcjonalność możesz zrealizować za pomocą partycjonowania.
https://dev.mysql.com/doc/refman/8.0/en/partitioning-types.html

Dzięki, nie mogę wykorzystać partycjonowania ponieważ nie mogę zmienić starej aplikacji która "poszła w świat" i w tej postaci wymaga dwu tabel. Baza nie jest aż tak duża i tak obciążona i mógę ją rozdzielać to w prostym zapytaniu ale projekt stary uniemożliwia modyfikacje tabel i zapytań wiec trzeba mu dostarczyć to co lubi :). Moje działania więc muszą być i są ograniczone są do serwera, dlatego chcę fizycznie rozdzielać tabele na dwie części(tabele) o tej samej strukturze. Poradziłem sobie skryptem PHP. wykonuje szybko i bezbłędnie. A zdarzenie wywoływane jest w skrypcie który modyfikuje tabelę matkę. Moja ciekawość wynikała z tego że ponieważ już jest mechanizm (trigger) czuwający nad zdarzeniami w tabeli to czemu jest tak ograniczony i nie można go wykorzystać w sposób ciut zmodyfikowany ???.

Dziękuję i pozdrawiam ...
AK

Tylko dlaczego nie zrealizowac tematu jako tabela partycjonowana a aplikacji udostepnic dwa widoki z odpowiednimi partycjami?

Ufff, zadałeś mi roboty. Jakoś to jednak przebrnąłem przy okazji poznałem co to partycjonowanie w MySQL. Jednym słowem laika można powiedzieć że to takie grupowanie taka jakby defragmentacja dysku twardego.
A dlatego Ufff, bo się zaparło, bo nie udało mi się partycjonować jak w zapytaniu funkcją TIME(), chyba ze względu na zapis wzorca typ (string) ('1500') ale udało się z funkcją HOUR() tu wzorzec to typ integer (15) i tak to udało się.

CREATE TABLE `ms`.`g_part` (
  `RecordId` int(11) NOT NULL AUTO_INCREMENT,
  `Id` int(11),
  `DATE` datetime,
  `L1` smallint(6) DEFAULT NULL,
  `L2` smallint(6) DEFAULT NULL,
  `L3` smallint(6) DEFAULT NULL,
  `L4` smallint(6) DEFAULT NULL,
  `L5` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`RecordId`,`Date`)
) 
PARTITION BY RANGE (hour(Date))(
PARTITION p0 values less than (15), -- wszystkie do godziny 15:00
partition p1 values less than (maxvalue) -- pozostałe
);

Po wrzuceniu danych zostały poukładane w partycje.

auto, id, datetime,            L1,  L2,  L3,  L4,  L5,
-----------------------------------------------
1	1	2018-12-01 14:00:00	1	2	3	4	5
3	3	2018-12-02 14:00:00	21	22	23	24	25
5	5	2018-12-03 14:00:00	1	2	3	4	5
7	7	2018-12-04 14:00:00	21	22	23	24	25
15	9	2018-12-05 14:00:00	5	14	23	24	25
-----------------------------------------------
2	2	2018-12-01 21:00:00	11	12	13	14	15
4	4	2018-12-02 21:00:00	31	32	33	34	35
6	6	2018-12-03 21:00:00	11	12	13	14	15
8	8	2018-12-04 21:00:00	31	32	33	34	35
16	10	2018-12-05 21:00:00	5	21	33	34	35

Z tego łatwo zrobić dwa widoki :

CREATE VIEW `ms`.`vg14` AS (SELECT * FROM `ms`.`g_14` PARTITION (`p0`))
CREATE VIEW `ms`.`vg21` AS (SELECT * FROM `ms`.`g_14` PARTITION (`p1`))

Do widoków są automatycznie wstawiane dane z tabeli matki i wszystko wydawało się OK.
Ale dla mnie znów wynikła przeszkoda nie do przejscia, widoków nie można modyfikować a mnie potrzebne indeksy ponumerowane kolejno od (1) w obu tabelach(widokach).
Dzięki za sugestie, przydadzą się w przyszłości tymczasem załatwię to skryptem z własnym wyzwalczem (zdarzeniem) przy okazji modyfikacji (insertu danych do tabeli matki).

pozdrawiam AK