search

CDLUC3 / mrt-doc

Documentation and Information regarding the Merritt repository
8 stars 4 forks source link

mysql 5.7 to 8.0.28 error - FTS_000 ... INDEX_2 - Consider restoring ... #1263

Open dloy opened 2 years ago

dloy commented 2 years ago

This issue

I'm putting out this issue to summarize some of the info that was discussed related to the FTS error related to the MySQL migration from version 5.7 to 8.0.28.

See Terry's stackoverflow - that documents and asks what actions need to be taken.

prepatchcompatability.log FTS errors


inv,FTS_0000000000000078_BEING_DELETED_CACHE - Consider restoring the DB instance from a snapshot creating before upgrading, and change the row_format of the tables to dynamic.
inv,inv_nodes - Consider restoring the DB instance from a snapshot creating before upgrading, and change the row_format of the tables to dynamic.
inv,FTS_0000000000000078_DELETED - Consider restoring the DB instance from a snapshot creating before upgrading, and change the row_format of the tables to dynamic.
inv,FTS_0000000000000078_00000000000000f2_INDEX_2 - Consider restoring the DB instance from a snapshot creating before upgrading, and change the row_format of the tables to dynamic.
inv,FTS_0000000000000078_00000000000000f2_INDEX_1 - Consider restoring the DB instance from a snapshot creating before upgrading, and change the row_format of the tables to dynamic.
inv,FTS_0000000000000078_CONFIG - Consider restoring the DB instance from a snapshot creating before upgrading, and change the row_format of the tables to dynamic.
inv,FTS_0000000000000078_00000000000000f2_INDEX_6 - Consider restoring the DB instance from a snapshot creating before upgrading, and change the row_format of the tables to dynamic.
inv,FTS_0000000000000078_00000000000000f2_INDEX_5 - Consider restoring the DB instance from a snapshot creating before upgrading, and change the row_format of the tables to dynamic.
inv,FTS_0000000000000078_00000000000000f2_INDEX_4 - Consider restoring the DB instance from a snapshot creating before upgrading, and change the row_format of the tables to dynamic.
inv,schema_migrations - Consider restoring the DB instance from a snapshot creating before upgrading, and change the row_format of the tables to dynamic.
inv,FTS_0000000000000078_00000000000000f2_INDEX_3 - Consider restoring the DB instance from a snapshot creating before upgrading, and change the row_format of the tables to dynamic.
inv,FTS_0000000000000078_DELETED_CACHE - Consider restoring the DB instance from a snapshot creating before upgrading, and change the row_format of the tables to dynamic.
inv,annita_tbl - Consider restoring the DB instance from a snapshot creating before upgrading, and change the row_format of the tables to dynamic.
inv,annita - Consider restoring the DB instance from a snapshot creating before upgrading, and change the row_format of the tables to dynamic.
inv,FTS_0000000000000078_BEING_DELETED - Consider restoring the DB instance from a snapshot creating before upgrading, and change the row_format of the tables to dynamic.

Some facts

Index type search

SELECT DISTINCT *
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'inv';

Triggers

The triggers are used to populate sha_dublinkernels from inv_dublinkernels, for creating a word searchable full text index.

See Triggers - https://github.com/CDLUC3/merritt-docker/blob/main/mrt-services/mysql/init.sql#L400-L439

Current:

# Triggers

DELIMITER //
DROP TRIGGER IF EXISTS insert_fulltext//
CREATE TRIGGER insert_fulltext
AFTER INSERT ON inv_dublinkernels
FOR EACH ROW
BEGIN
  IF NOT NEW.value='(:unas)' THEN
    INSERT INTO sha_dublinkernels
    VALUES (NEW.id, NEW.value);
  END IF;
END;
//
DROP TRIGGER IF EXISTS update_fulltext//
CREATE TRIGGER update_fulltext
AFTER UPDATE ON inv_dublinkernels
FOR EACH ROW
BEGIN
  IF NEW.value!='(:unas)' THEN
    UPDATE sha_dublinkernels
    SET value = NEW.value
    WHERE id = NEW.id;
  END IF;
END;
//
DROP TRIGGER IF EXISTS delete_fulltext//
CREATE TRIGGER delete_fulltext
AFTER DELETE ON inv_dublinkernels
FOR EACH ROW
BEGIN
  DELETE FROM sha_dublinkernels
  WHERE id = OLD.id;
END;
//
DELIMITER ;
insert into sha_dublinkernels 
select id, value from inv_dublinkernels 
where not inv_dublinkernels.element='where' 
and not inv_dublinkernels.value='(:unas)';

create definition sha_dublinkernels

CREATE TABLE `sha_dublinkernels` (
    `id` INT(10) UNSIGNED NOT NULL,
    `value` MEDIUMTEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
    PRIMARY KEY (`id`) USING BTREE,
    FULLTEXT INDEX `value` (`value`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
ROW_FORMAT=DYNAMIC
;

Perhaps related

See: https://dba.stackexchange.com/questions/293937/how-to-fix-file-format-in-tables-after-upgrade-of-mysql-5-6-mysql-8-0-23-in-a

This suggests problems of indexing: One way to fix the warning is to make sure there are no indexes on columns that may be longer than 767 bytes.

For our inv database the indexed column value is MEDIUMTEXT, which can be 16M. This error should not be showing up for a FULLTEXT index.

See: https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html This notes:

The problem with these comments is that the key prefix created in FULLTEXT should only be the length of any word in the source and not the entire length of the originating MEDIUMTEXT string.

Note that: innodb_ft_max_token_size=84

Hopefully Terry's stackoverflow will get this resolved.

dloy commented 1 year ago

I'm including the note from AWS regarding the Thanksgiving Failure:

Hi Martin, Thanks for your patience while we had been working on your case, I am Akil from AWS Premium support team and my team mate Monica had been assisting you with your case earlier and is currently off-shift. We do have some updates from the internal teams in regards to the issue that, your instance "rds-uc3-mrt-inv-prd" in region "US-WEST-2" was unable to failover as database crashed when going through the code path of the function btr_search_drop_page_hash_index 8: /rdsdbbin/mysql/bin/mysqld(my_print_stacktrace(unsigned char const, unsigned long)+0x2e) [0x20a0cfe] 9: /rdsdbbin/mysql/bin/mysqld(print_fatal_signal(int)+0x2bb) [0x101494b] 10: /rdsdbbin/mysql/bin/mysqld(handle_fatal_signal+0xa5) [0x10149f5] 11: /lib64/libpthread.so.0(+0x117e0) [0x14ab931097e0] 12: /rdsdbbin/mysql/bin/mysqld(ha_remove_all_nodes_to_page(hash_table_t, unsigned long, unsigned char const)+0x5f) [0x24ff63f] 13: /rdsdbbin/mysql/bin/mysqld(btr_search_drop_page_hash_index(buf_block_t)+0x6f2) [0x23ddc72] 14: /rdsdbbin/mysql/bin/mysqld(buf_LRU_free_page(buf_page_t, bool)+0x71f) [0x2414d6f] 15: /rdsdbbin/mysql/bin/mysqld(buf_flush_do_batch(buf_pool_t, buf_flush_t, unsigned long, unsigned long, unsigned long)+0xcb9) [0x2409c49] 16: /rdsdbbin/mysql/bin/mysqld() [0x240a2c8] 17: /rdsdbbin/mysql/bin/mysqld() [0x240a49d] 18: /rdsdbbin/mysql/bin/mysqld(std::thread::_State_impl<std::thread::_Invoker<std::tuple<Detached_thread, void ()()> > >::_M_run()+0xb0) [0x226ca60] 19: /rdsdbbin/mysql/bin/mysqld() [0x2b81000] 20: /lib64/libpthread.so.0(+0x740b) [0x14ab930ff40b] 21: /lib64/libc.so.6(clone+0x3f) [0x14ab928e409f] 22: The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains 23: information that should help you find out what is causing the crash. 24: 2022-11-23T20:37:05.208035Z 0 [Warning] [MY-011068] [Server] The syntax 'l There is a commit the 12th of Aug to fix that part of the code here but it hasn't made it to a minor release in mainline yet. This would be the cause of the initial crash. [+] https://github.com/mysql/mysql-server/commit/d7114b140509be567a5c63cd0c82757d59f0deef After the crash, the database was failing to start and erroring on this line of code [+] https://github.com/mysql/mysql-server/blob/mysql-8.0.28/storage/innobase/page/page0cur.cc#L1174 All this points to a potential logical corruption on the redo log but we do not have any data that allows us to identify the issue.

To Summarize : For the crash the problem is related to a bug in adaptive hash index For the database unable to recover the problem was redo log corruption. I hope the above information was helpful, and I was able to assist you on your issue. For any further assistance please reach out to us via a case correspondence, and we would be happy to help We value your feedback. Please share your experience by rating this and other correspondences in the AWS Support Center. You can rate a correspondence by selecting the stars in the top right corner of the correspondence.

Best regards, Akil M. Amazon Web Services