[MDEV-7997] galera cluster crashed upon trigger Created: 2015-04-15  Updated: 2020-12-07  Resolved: 2020-12-07

Status: Closed
Project: MariaDB Server
Component/s: Galera
Affects Version/s: 10.0.17-galera
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Umarzuki Assignee: Seppo Jaakola
Resolution: Won't Fix Votes: 0
Labels: None
Environment:

Ubuntu 14.04 amd64

galera-3
25.3.9-trusty

mariadb-10.0
10.0.17+maria-1~trusty

Attachments: File garbd.log-2015-04-15     File my.cnf-2015-04-15-db1     File my.cnf-2015-04-15-db2     File mysql.log-2015-04-15-db1     File mysql.log-2015-04-15-db2     File mysql.log-2015-04-17-db2     File mysql.log-2015-04-22-m51     File mysql.log-2015-04-22-m52    

 Description   

When a trigger ran, cluster will fail. I believe the trigger is in one of mysql.log attached.

 Comments   
Comment by Daniel Black [ 2015-04-15 ] 

12:59 < umarzuki> nirbhay: one of delete trigger definition https://paste.ee/p/5Irml
 
 
 
BEGIN
 
DELETE FROM EVENT_AGENT_CLONE WHERE event= old.ev_id;
 
INSERT INTO EVENT_AGENT_CLONE(event, transactionType) values (old.ev_id, 'Cancelled');
 
END

What would help is the trigger type and the table definition of EVENT_AGENT_CLONE.

What is the table schema on which this trigger happens.

See my comment that perhaps this is just:

BEGIN
 
REPLACE INTO EVENT_AGENT_CLONE(event, transactionType) values (old.ev_id, 'Cancelled');
 
END

Your error log contained the following.

150415 11:47:58 [Warning] WSREP: RBR event 1 Query apply warning: 1, 12193
 
150415 11:47:58 [Warning] WSREP: Ignoring error for TO isolated action: source: 9a0a95d2-e31f-11e4-aa21-736d6b84b38c version: 3 local: 0 state: APPLYING flags: 65 conn_id: 7069 trx_id: -1 seqnos (l: 2217, g: 12193, s: 12192, d: 12192, ts: 676552141153475)
 
150415 11:48:52 [ERROR] Slave SQL: Error 'Unknown column 'evdet_id' in 'NEW'' on query. Default database: 'comp2012db'. Query: 'CREATE DEFINER=`webjedi`@`10.224.202.171` TRIGGER `CREATE_` AFTER INSERT ON `agent` FOR EACH ROW BEGIN
 
DECLARE counter int;
 
 
 
DECLARE connect_federated_fail CONDITION FOR 1296;
 
DECLARE error_reading_packet CONDITION FOR 1158;
 
DECLARE connect_federated_timeout CONDITION FOR 1159;
 
DECLARE write_packet_timeout CONDITION FOR 1160;
 
DECLARE CONTINUE HANDLER FOR connect_federated_fail, connect_federated_timeout,write_packet_timeout,error_reading_packet 
  BEGIN
 
		IF counter=0 THEN
 
            		DELETE FROM MY_AGENT_FAILED WHERE event= new.event;
 
    			INSERT INTO MY_AGENT_FAILED(event, transactionType) values (new.evdet_id, new.transactionType);
 
   		END IF;
 
 
 
  END;
 
    SET counter = 0;
 
    DELETE FROM MY_AGENT_CLONE WHERE event= new.event;
 
    INSERT INTO MY_AGENT_CLONE (event, transactionType) values (new.evdet_id, new.transactionType);
 
    SET counter=1;
 
END', Internal MariaDB error code:

evdet_id -> event_id perhaps

Comment by Umarzuki [ 2015-04-15 ]

below is table schema for effected table.

MariaDB [(none)]> describe compdb.JEVENTS_VW;
 
+--------------+--------------+------+-----+---------------------+-------+
 
| Field        | Type         | Null | Key | Default             | Extra |
 
+--------------+--------------+------+-----+---------------------+-------+
 
| id           | int(12)      | NO   |     | 0                   |       |
 
| title        | longtext     | NO   |     | NULL                |       |
 
| postedBy     | varchar(255) | NO   |     |                     |       |
 
| categoryId   | int(11)      | NO   |     | 1                   |       |
 
| category     | varchar(255) | NO   |     | NULL                |       |
 
| content      | longtext     | NO   |     | NULL                |       |
 
| startDate    | datetime     | NO   |     | 0000-00-00 00:00:00 |       |
 
| endDate      | datetime     | NO   |     | 0000-00-00 00:00:00 |       |
 
| dateCreated  | datetime     | NO   |     | 0000-00-00 00:00:00 |       |
 
| dateModified | datetime     | NO   |     | 0000-00-00 00:00:00 |       |
 
+--------------+--------------+------+-----+---------------------+-------+

Comment by Daniel Black [ 2015-04-15 ]

https://mariadb.com/kb/en/mariadb/mariadb-galera-cluster-known-limitations/ having a primary key is also a requirement. Fixing that and your trigger syntax error may actually solve your problem.

Still is a bug. it shouldn't segfault based on this.

Comment by Umarzuki [ 2015-04-15 ]

I was informed that this issue did not happen when table engine changed to InnoDB (MyISAM previously) but will confirm back after I restored all databases with table engine innodb.

Comment by Umarzuki [ 2015-04-17 ]

after reinstall, converting tables to innodb from myisam, only second node (10.1.1.101) got crashed after 4 successful entry (and triggers).

Comment by Umarzuki [ 2015-04-22 ]

I tested on mariadb 5.5 galera cluster with same error. Please see attached log.

Comment by Nirbhay Choubey (Inactive) [ 2015-04-25 ]

umarzuki: What's the structure of 'EVENT_AGENT_CLONE' table?
SHOW CREATE TABLE EVENT_AGENT_CLONE

Comment by Umarzuki [ 2015-04-25 ] 

mysql> SHOW CREATE TABLE EVENT_AGENT_CLONE;
 
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table             | Create Table                                                                                                                                                                                                                                                                                     |
 
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| EVENT_AGENT_CLONE | CREATE TABLE `EVENT_AGENT_CLONE` (
 
  `event` int(11) NOT NULL,
 
  `transactionType` varchar(20) NOT NULL,
 
  `dateCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 CONNECTION='mysql://dbUser:user@x.x.x.x/testCompBaru/EVENT_AGENT' |
 
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Comment by Nirbhay Choubey (Inactive) [ 2015-04-26 ]

umarzuki: hmm.. Any idea where is 'evdet_id' column coming from? What's the definition of `CREATE_` trigger?

Comment by Umarzuki [ 2015-04-26 ] 

mysql> show triggers;
 
+------------------------------+--------+-------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+-----------------------+-------------------+----------------------+----------------------+--------------------+
 
| Trigger                      | Event  | Table                   | Statement                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | Timing | Created | sql_mode              | Definer           | character_set_client | collation_connection | Database Collation |
 
+------------------------------+--------+-------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+-----------------------+-------------------+----------------------+----------------------+--------------------+
 
| CREATE_                      | INSERT | uni12_jevents_vevdetail | BEGIN
 
DECLARE counter int;
 
 
 
DECLARE connect_federated_fail CONDITION FOR 1296;
 
DECLARE error_reading_packet CONDITION FOR 1158;
 
DECLARE connect_federated_timeout CONDITION FOR 1159;
 
DECLARE write_packet_timeout CONDITION FOR 1160;
 
DECLARE CONTINUE HANDLER FOR connect_federated_fail, connect_federated_timeout,write_packet_timeout,error_reading_packet 
  BEGIN
 
		IF counter=0 THEN
 
            		DELETE FROM EVENT_AGENT_FAILED WHERE event= new.evdet_id;
 
    			INSERT INTO EVENT_AGENT_FAILED(event, transactionType) values (new.evdet_id, 'New');
 
   		END IF;
 
 
 
  END;
 
    SET counter = 0;
 
    DELETE FROM EVENT_AGENT_CLONE WHERE event= new.evdet_id;
 
    INSERT INTO EVENT_AGENT_CLONE (event, transactionType) values (new.evdet_id, 'New');
 
    SET counter=1;
 
END                                                                                                                                                                                                                                                                          | AFTER  | NULL    | NO_AUTO_VALUE_ON_ZERO | webjedi@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
 
| DELETE_                      | DELETE | uni12_jevents_vevdetail | BEGIN
 
DECLARE counter int;
 
DECLARE connect_federated_fail CONDITION FOR 1296;
 
DECLARE error_reading_packet CONDITION FOR 1158;
 
DECLARE connect_federated_timeout CONDITION FOR 1159;
 
DECLARE write_packet_timeout CONDITION FOR 1160;
 
DECLARE CONTINUE HANDLER FOR connect_federated_fail, connect_federated_timeout,write_packet_timeout,error_reading_packet 
  BEGIN
 
	IF counter=0 THEN
 
		DELETE FROM EVENT_AGENT_FAILED WHERE event= old.evdet_id;
 
    		INSERT INTO EVENT_AGENT_FAILED(event, transactionType) values (old.evdet_id, 'Cancelled');
 
	END IF;
 
  END;
 
 
 
SET counter=0;
 
DELETE FROM EVENT_AGENT_CLONE WHERE event= old.evdet_id;
 
INSERT INTO EVENT_AGENT_CLONE(event, transactionType) values (old.evdet_id, 'Cancelled');
 
SET counter=1;
 
 
 
END                                                                                                                                                                                                                                                                                                   | AFTER  | NULL    | NO_AUTO_VALUE_ON_ZERO | webjedi@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
 
| UPDATE_EVENT_AGENT_CLONE_CAT | UPDATE | uni12_jevents_vevent    | BEGIN
 
DECLARE counter int;
 
DECLARE connect_federated_fail CONDITION FOR 1296;
 
DECLARE error_reading_packet CONDITION FOR 1158;
 
DECLARE connect_federated_timeout CONDITION FOR 1159;
 
DECLARE write_packet_timeout CONDITION FOR 1160;
 
DECLARE CONTINUE HANDLER FOR connect_federated_fail, connect_federated_timeout,write_packet_timeout,error_reading_packet
 
  BEGIN
 
	IF counter=0 && new.catid<>186 THEN	
		IF new.state<> old.state AND new.state=0 THEN
 
            DELETE FROM EVENT_AGENT_FAILED WHERE event= old.ev_id;
 
            INSERT INTO EVENT_AGENT_FAILED(event, transactionType) values (new.ev_id, 'Cancelled');
 
        ELSE
 
            DELETE FROM EVENT_AGENT_FAILED WHERE event= old.ev_id;
 
            INSERT INTO EVENT_AGENT_FAILED(event, transactionType) values (new.ev_id, 'Updated');
 
        END IF;
 
 
 
	END IF;  
END;
 
 
 
SET counter=0;
 
IF new.catid<>186 THEN
 
    IF new.state<> old.state AND new.state=0 THEN
 
        DELETE FROM EVENT_AGENT_CLONE WHERE event= old.ev_id;
 
        INSERT INTO EVENT_AGENT_CLONE(event, transactionType) values (new.ev_id, 'Cancelled');
 
	    SET counter=1;
 
    ELSE
 
        DELETE FROM EVENT_AGENT_CLONE WHERE event= old.ev_id;
 
        INSERT INTO EVENT_AGENT_CLONE(event, transactionType) values (new.ev_id, 'Updated');
 
        SET counter=1;
 
    END IF;
 
END IF;
 
 
 
END | AFTER  | NULL    |                       | webjedi@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
 
| DELETE_EVENT_AGENT_CLONE_CAT | DELETE | uni12_jevents_vevent    | BEGIN
 
DECLARE counter int;
 
DECLARE connect_federated_fail CONDITION FOR 1296;
 
DECLARE error_reading_packet CONDITION FOR 1158;
 
DECLARE connect_federated_timeout CONDITION FOR 1159;
 
DECLARE write_packet_timeout CONDITION FOR 1160;
 
DECLARE CONTINUE HANDLER FOR connect_federated_fail, connect_federated_timeout,write_packet_timeout,error_reading_packet 
  BEGIN
 
	IF counter=0 THEN
 
		DELETE FROM EVENT_AGENT_FAILED WHERE event= old.ev_id;
 
    		INSERT INTO EVENT_AGENT_FAILED(event, transactionType) values (old.ev_id, 'Cancelled');
 
	END IF;
 
  END;
 
 
 
SET counter=0;
 
DELETE FROM EVENT_AGENT_CLONE WHERE event= old.ev_id;
 
INSERT INTO EVENT_AGENT_CLONE(event, transactionType) values (old.ev_id, 'Cancelled');
 
SET counter=1;
 
 
 
END                                                                                                                                                                                                                                                                                                               | AFTER  | NULL    |                       | webjedi@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
 
+------------------------------+--------+-------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+-----------------------+-------------------+----------------------+----------------------+--------------------+

I will check evdet_id tomorrow

Comment by Nirbhay Choubey (Inactive) [ 2015-04-27 ]

Ok, from the trigger definition 'evdet_id' should be in EVENT_AGENT_CLONE and EVENT_AGENT_FAILED tables.

Comment by Nirbhay Choubey (Inactive) [ 2015-10-08 ]

From the attached logs :

150415 7:58:47 [ERROR] WSREP: FSM: no such a transition APPLYING -> REPLICATING
150415 7:58:47 [ERROR] mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.

To report this bug, see http://kb.askmonty.org/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

Server version: 10.0.17-MariaDB-1~trusty-wsrep-log
key_buffer_size=25165824
read_buffer_size=131072
max_used_connections=10
max_threads=1002
thread_count=14
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2226066 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0x7f7b730ab008
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x7f8ead646df0 thread_stack 0x48000
/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x7f8ebe5d138e]
/usr/sbin/mysqld(handle_fatal_signal+0x457)[0x7f8ebe108fc7]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x10340)[0x7f8ebc8b4340]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x39)[0x7f8ebbf0bcc9]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x148)[0x7f8ebbf0f0d8]
/usr/lib/galera/libgalera_smm.so(ZN6galera3FSMINS_9TrxHandle5StateENS1_10TransitionENS_10EmptyGuardENS_11EmptyActionEE8shift_toES2+0x17c)[0x7f8eb9d5284c]
/usr/lib/galera/libgalera_smm.so(_ZN6galera13ReplicatorSMM9replicateEPNS_9TrxHandleEP14wsrep_trx_meta+0x19a)[0x7f8eb9d48f4a]
/usr/lib/galera/libgalera_smm.so(galera_to_execute_start+0x226)[0x7f8eb9d5ba16]
/usr/sbin/mysqld(_Z24wsrep_to_isolation_beginP3THDPcS1_PK10TABLE_LIST+0xdea)[0x7f8ebe0a805a]
/usr/sbin/mysqld(_Z11open_tablesP3THDPP10TABLE_LISTPjjP19Prelocking_strategy+0x1555)[0x7f8ebdf38275]
/usr/sbin/mysqld(_Z20open_and_lock_tablesP3THDP10TABLE_LISTbjP19Prelocking_strategy+0x34)[0x7f8ebdf38404]
/usr/sbin/mysqld(_Z12mysql_insertP3THDP10TABLE_LISTR4ListI4ItemERS3_IS5_ES6_S6_15enum_duplicatesb+0xf2)[0x7f8ebdf63192]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0xc16)[0x7f8ebdf7e466]
/usr/sbin/mysqld(+0x40f497)[0x7f8ebdf87497]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1ebb)[0x7f8ebdf899fb]
/usr/sbin/mysqld(_Z10do_commandP3THD+0x20f)[0x7f8ebdf8a69f]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x1fb)[0x7f8ebe058b5b]
/usr/sbin/mysqld(handle_one_connection+0x40)[0x7f8ebe058d40]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x8182)[0x7f8ebc8ac182]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f8ebbfcf47d]

Trying to get some variables

Generated at Thu Feb 08 07:23:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.