[SOLVED] MariaDB crashed 10.1.32-MariaDB occasionally when do any insert/update/truncate operation

MariaDB crashed 10.1.32-MariaDB occasionally when do any insert/update/truncate operation on specific table, Let me explain clearly It happen only when Db have high traffic. With the help of other team we have recovered the database, anyway lost that particular table and that's not issue since we are using that table for temporary purpose. However we are unable to create table in same name. we have created the same table with another name hrms_flat_file_temp1. Let me come to the point I wish to find the root cause of the crash so that I will take necessary action for future. Below is the mysql errro log

2023-02-27 17:39:41 1b84 InnoDB: Assertion failure in thread 7044 in file pars0pars.cc line 865 InnoDB: Failing assertion: sym_node->table != NULL InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to https://jira.mariadb.org/ InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 230227 17:39:41 [ERROR] mysqld got exception 0x80000003 ; 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 https://mariadb.com/kb/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.1.32-MariaDB key_buffer_size=4244635648 read_buffer_size=131072 max_used_connections=5001 max_threads=1001 thread_count=2047 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1218585 K bytes of memory Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0 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... mysqld.exe!my_parameter_handler()[my_init.c:258] mysqld.exe!raise()[signal.cpp:516] mysqld.exe!abort()[abort.cpp:71] mysqld.exe!pars_retrieve_table_def()[pars0pars.cc:865] mysqld.exe!pars_retrieve_table_list_defs()[pars0pars.cc:890] mysqld.exe!pars_select_statement()[pars0pars.cc:1019] mysqld.exe!yyparse()[pars0grm.y:369] mysqld.exe!pars_sql()[pars0pars.cc:2247] mysqld.exe!fts_parse_sql()[fts0sql.cc:214] mysqld.exe!fts_cmp_set_sync_doc_id()[fts0fts.cc:2755] mysqld.exe!fts_sync_commit()[fts0fts.cc:4435] mysqld.exe!fts_sync()[fts0fts.cc:4640] mysqld.exe!fts_optimize_sync_table()[fts0opt.cc:2981] mysqld.exe!fts_optimize_thread()[fts0opt.cc:3115] KERNEL32.DLL!BaseThreadInitThunk() ntdll.dll!RtlUserThreadStart() The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. InnoDB: Warning: a long semaphore wait: --Thread 4056 has waited at row0ins.cc line 1853 for 505.00 seconds the semaphore: S-lock on RW-latch at 00007FF7C7589FE0 '&dict_operation_lock' a writer (thread id 14868) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0ins.cc line 1853 Last time write locked in file row0mysql.cc line 3418 Holder thread 0 file not yet reserved line 0 InnoDB: Warning: a long semaphore wait: --Thread 17772 has waited at row0ins.cc line 1853 for 505.00 seconds the semaphore: S-lock on RW-latch at 00007FF7C7589FE0 '&dict_operation_lock' a writer (thread id 14868) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0ins.cc line 1853 Last time write locked in file row0mysql.cc line 3418 Holder thread 0 file not yet reserved line 0

Here is table DDL script :

CREATE TABLE `hrms_flat_file_temp` (
  `refno` INT(11) NOT NULL AUTO_INCREMENT,
  `employee_id` VARCHAR(50) NOT NULL,
  `associate_name` VARCHAR(100) NOT NULL,
  `email_id` VARCHAR(100) NOT NULL,
  `doj` DATE NOT NULL,
  `designation` VARCHAR(100) NOT NULL,
  `location` VARCHAR(25) NOT NULL,
  `shift` VARCHAR(255) DEFAULT NULL,
  `manager_employee_id` VARCHAR(25) NOT NULL,
  `buh` VARCHAR(100) DEFAULT NULL,
  `employee_type` INT(1) DEFAULT NULL,
  `department` VARCHAR(50) DEFAULT NULL,
  `dob` DATE DEFAULT NULL,
  `employee_status` VARCHAR(100) DEFAULT NULL,
  `gender` VARCHAR(12) DEFAULT NULL,
  `project_name` VARCHAR(255) DEFAULT NULL,
  `buh_employee_id` VARCHAR(50) DEFAULT NULL,
  `tl_employee_id` VARCHAR(50) DEFAULT NULL,
  `name_of_the_interviewer` VARCHAR(100) DEFAULT NULL,
  `designation_id` INT(11) DEFAULT NULL,
  `location_id` INT(11) DEFAULT NULL,
  `shift_id` INT(11) DEFAULT NULL,
  `vfs_id` INT(11) DEFAULT NULL,
  `team_lead_id` INT(11) DEFAULT NULL,
  `manager_id` INT(11) DEFAULT NULL,
  
  PRIMARY KEY (`refno`),
  UNIQUE KEY `UK2_mas_users_email_id` (`email_id`),
  UNIQUE KEY `UK3_mas_users_employee_id` (`employee_id`),
  KEY `FK_hrms_uplby` (`uploaded_by`),
  FULLTEXT KEY `employee_id` (`employee_id`),
  FULLTEXT KEY `designation` (`designation`),
  FULLTEXT KEY `location` (`location`),
  FULLTEXT KEY `shift` (`shift`),
  FULLTEXT KEY `manager_employee_id` (`manager_employee_id`),
  CONSTRAINT `FK_hrms_uplby` FOREIGN KEY (`uploaded_by`) REFERENCES `mas_users` (`vfs_id`)
)

Here is Deadlock log

LATEST DETECTED DEADLOCK

2023-02-27 17:01:47 1254 *** (1) TRANSACTION: TRANSACTION 1511610850, ACTIVE 0 sec updating or deleting mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1 MySQL thread id 361084063, OS thread handle 0x3ed8, query id 1426113636 10.140.1.6 hcc_vfs updating UPDATE vfs_hcc_coding.flat_file AS ffile SET ffile.assigned_to = NAME_CONST('vfs_id',15715), ffile.assigned_by = NAME_CONST('vfs_id',15715), ffile.assigned_date_time = CURRENT_TIMESTAMP WHERE ffile.refno = @refno *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2223128 page no 786 n bits 112 index PRIMARY of table vfs_mas.mas_users trx table locks 2 total table locks 3 trx id 1511610850 lock mode S locks rec but not gap waiting lock hold time 0 wait time before grant 0 *** (2) TRANSACTION: TRANSACTION 1511566082, ACTIVE 232 sec fetching rows, thread declared inside InnoDB 3172 mysql tables in use 2, locked 2 1780 lock struct(s), heap size 210472, 75368 row lock(s), undo log entries 48516 MySQL thread id 360949095, OS thread handle 0x1254, query id 1426114182 10.140.1.6 hcc_vfs Sending data UPDATE vfs_mas.mas_users AS mu SET mu.status_id=2, mu.updated_by= NAME_CONST('UserId',468), mu.updated_time_stamp= NAME_CONST('CurrDate',_latin1'2023-02-27 16:57:54' COLLATE 'latin1_swedish_ci') WHERE mu.status_id=1 AND NOT EXISTS (SELECT 1 FROM vfs_mas.hrms_flat_file_temp AS hfft WHERE mu.employee_id = hfft.employee_id) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 2223128 page no 786 n bits 112 index PRIMARY of table vfs_mas.mas_users trx table locks 11 total table locks 3 trx id 1511566082 lock_mode X locks rec but not gap lock hold time 0 wait time before grant 0 *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2223128 page no 786 n bits 112 index PRIMARY of table vfs_mas.mas_users trx table locks 11 total table locks 3 trx id 1511566082 lock_mode X waiting lock hold time 0 wait time before grant 0 *** WE ROLL BACK TRANSACTION (1)

I saw few of blogs that says having too many fulltext index will cause issue in mariadb.
I'm just running some Internal API to get users from that and then I will truncate this hrms_flat_file_temp1 table then I will insert the records in that table. after that I will insert all other tables using this master table.
I'm using begin trancation commit from PHP script.
My guess is truncate table script will do meta lock so in high traffic it's causing issue but I'm not sure.

If anyone have idea on this, Please help me, thanks in advance!