| Bug #98428 | FTS AUX Tables are never encrypted | ||
|---|---|---|---|
| Submitted: | 29 Jan 2020 12:48 | Modified: | 15 Jul 2022 16:30 |
| Reporter: | Satya Bodapati (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 5.7, 8.0, 5.7.29, 8.0.19 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | encryption, fts, innodb | ||
[29 Jan 2020 12:54]
Satya Bodapati
added tags
[30 Jan 2020 9:09]
MySQL Verification Team
Hello Satya, Thank you for the report and feedback! regards, Umesh
[30 Jan 2020 9:22]
MySQL Verification Team
Test results - 8.0.19, 5.7.29
Attachment: 98428_5.7.29_8.0.19.results (application/octet-stream, text), 6.55 KiB.
[15 Jul 2022 15:00]
Satya Bodapati
Fixed in 8.0.20 https://github.com/mysql/mysql-server/commit/0ffc43332348e7083e994e674fea4b0ff329a0cd BUG##30787535 : FULLTEXT INDEX TABLES CREATED IN ENCRYPTED SCHEMA ARE… … NOT ENCRYPTED Issue: When in an encrypted table, an FTS index is created, there are some aux tables (thus tablespaces with ibd files) are created for FTS. If the table is encrypted, then the FTS tablespaces are supposed to be encrypted too, which were not. Cause: When FTS aux tables are created, for those tables, flags are borrowed from main table. While copying those flags, encryption flag was not copied, thus aux tables were always created as not encrypted. Fix: Made sure that when the table flags are being copied for aux tables for FTS, encryption flag is also copied. RB : 23906 Reviewed-by : Rahul Agarkar <RAHUL.AGARKAR@ORACLE.COM>
[15 Jul 2022 16:30]
Satya Bodapati
Am closing this.. See above
Description: When a table has FTS index, the following IBDs are created: ls -1 FTS_0000000000000026_0000000000000033_INDEX_1.ibd FTS_0000000000000026_0000000000000033_INDEX_2.ibd FTS_0000000000000026_0000000000000033_INDEX_3.ibd FTS_0000000000000026_0000000000000033_INDEX_4.ibd FTS_0000000000000026_0000000000000033_INDEX_5.ibd FTS_0000000000000026_0000000000000033_INDEX_6.ibd FTS_0000000000000026_BEING_DELETED_CACHE.ibd FTS_0000000000000026_BEING_DELETED.ibd FTS_0000000000000026_CONFIG.ibd FTS_0000000000000026_DELETED_CACHE.ibd FTS_0000000000000026_DELETED.ibd t1.frm t1.ibd When you create encrypted table: CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)) ENGINE = InnoDB CHARACTER SET utf8mb4 ENCRYPTION='Y'; Only t1.ibd is encrypted How to repeat: CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)) ENGINE = InnoDB CHARACTER SET utf8mb4 ENCRYPTION='Y checks strings output on all IBD files. Suggested fix: /** Extract only the required flags from table->flags2 for FTS Aux tables. @param[in] flags2 Table flags2 @return extracted flags2 for FTS aux tables */ static inline uint32_t fts_get_table_flags2_for_aux_tables(uint32_t flags2) { /* Extract the file_per_table flag & temporary file flag from the main FTS table flags2 */ return ((flags2 & DICT_TF2_USE_FILE_PER_TABLE) | (flags2 & DICT_TF2_TEMPORARY) | DICT_TF2_AUX); } This should also handle ENCRYPTION flag.