MYSQL - Error code 1644

1

I have two databases, when a trigger is triggered it generates an insert in another database, it is a device to generate log:

The schema is this: local name database has a table called tab_manifest and an insert is made in it and the trigger fires an insert in the database local_log.log_tab_manifesto.

Below is the construction and an example of the error at the time of inserting.

USE local_log;

delimiter $$
DROP TABLE IF EXISTS 'local_log'.'log_tab_manifesto'$$ 
CREATE TABLE 'log_tab_manifesto' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'user_nome' varchar(30) NOT NULL,
  'data_hora' datetime NOT NULL,
  'host' varchar(45) NOT NULL,
  'operacao' varchar(2) NOT NULL COMMENT 'I-Insert, D-Delete, U-Update',
  'manifesto_id' int(11) DEFAULT NULL,
  'man_id' int(11) DEFAULT NULL,
  'uni_id' int(11) DEFAULT NULL,
  'filial_id' int(11) DEFAULT NULL COMMENT 'numero da carta frete de cada filial, exemplo foz_id',
  'mot_mot_id' int(11) DEFAULT NULL,
  'mot_est_id' int(11) DEFAULT NULL,
  'veic_id' int(11) DEFAULT NULL,
  'man_data' date DEFAULT NULL,
  'man_origem' varchar(45) DEFAULT NULL,
  'man_destino' varchar(45) DEFAULT NULL,
  'man_status' varchar(1) DEFAULT '0' COMMENT 'null  ou 0 = Viajando / 1 = chada / 2 = descarregando / 3 = finalizado',
  'man_ordem_chegada' datetime DEFAULT NULL COMMENT 'Ordena a Chegada dos Manifestos',
  'man_cancelado' varchar(1) DEFAULT NULL,
  'man_descarga' datetime DEFAULT NULL,
  PRIMARY KEY ('id')
)ENGINE=InnoDB DEFAULT CHARSET=utf8$$

    -- CRIANDO trigger PARA A TABELA - triggers SÃO CRIADAS NA DATABASE DE ***PRODUÇÃO***
DELIMITER $$

USE 'local'$$

DROP TRIGGER IF EXISTS 'local'.'tg_tab_manifesto_after_i'$$
CREATE
DEFINER='root'@'127.0.0.1'
TRIGGER 'local'.'tg_tab_manifesto_after_i'
AFTER INSERT ON 'local'.'tab_manifesto'
FOR EACH ROW
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION /*DECLARAR EXECEÇÃO*/ 
    RESIGNAL SQLSTATE '21S01' SET MESSAGE_TEXT = 'TRIGGER tg_tab_manifesto_after_i';/*RETORNA O NOME DA TRIGGER EM CASO DE ERRO*/
    /*LOG - INSERIR NOVO REGISTRO DE LOG */
    set @id = (select ifnull(max(id)+1,1) from local_log.log_tab_manifesto);    
    insert into local_log.log_tab_manifesto
        values(         
            @id,
            substring_index(session_user(),'@',1),
            now(),
            substring_index(session_user(),'@',-1),
            'I',
            NEW.'log_tab_manifesto'.'manifesto_id',
            NEW.'log_tab_manifesto'.'man_id',
            NEW.'log_tab_manifesto'.'uni_id',
            NEW.'log_tab_manifesto'.'filial_id',
            NEW.'log_tab_manifesto'.'mot_mot_id',
            NEW.'log_tab_manifesto'.'mot_est_id',
            NEW.'log_tab_manifesto'.'veic_id',
            NEW.'log_tab_manifesto'.'man_data',
            NEW.'log_tab_manifesto'.'man_origem',
            NEW.'log_tab_manifesto'.'man_destino',
            NEW.'log_tab_manifesto'.'man_status',
            NEW.'log_tab_manifesto'.'man_ordem_chegada',
            NEW.'log_tab_manifesto'.'man_cancelado',
            NEW.'log_tab_manifesto'.'man_descarga'
        );  
end$$

The error happens here in this insert below:

INSERT INTO 
tab_manifesto(
    uni_id,
    filial_id,
    mot_mot_id,
    mot_est_id,
    veic_id,
    man_origem,
    man_destino
) values (
    1,
    NULL,
    30,
    NULL,
    327,
    'FOZ',
    ' SAO PAULO'
);
  

Error Code: 1644. TRIGGER tg_tab_manifesto_after_i

Could someone help me fix this error?

    
asked by anonymous 28.01.2016 / 11:28

1 answer

0

I found this site that says that this error is set by user ... then I took a look again at the code to see where I wrote this and found the lines are those

DECLARE EXIT HANDLER FOR SQLEXCEPTION /*DECLARAR EXECEÇÃO*/ 
RESIGNAL SQLSTATE '21S01' SET MESSAGE_TEXT = 'TRIGGER tg_tab_manifesto_after_i';/*RETORNA O NOME DA TRIGGER EM CASO DE ERRO*/

I commented on these lines and circled the script again, so yes I got a more detailed error message:

  

08:34:09 INSERT INTO tab_manifest (uni_id, filial_id, mot_mot_id, mot_est_id, veic_id, man_origem, man_destino) values (1, NULL, 30, NULL, 327, 'FOZ', 'SAO PAULO') Error Code: 1054. Unknown column 'NEW.log_tab_manifesto.manifesto_id' in 'field list' 0.031 sec

There is no NEW log_tab_manifesto .manifesto_id the correct one is NEW. tab_manifesto .manifesto_id

Thank you all for the attention, lesson learned!

    
28.01.2016 / 11:58