Bug #98428 FTS AUX Tables are never encrypted
Submitted: 29 Jan 2020 12:48 Modified: 15 Jul 16:30
Reporter: Satya Bodapati (OCA) Email Updates:
Status: Closed Impact on me:
None 
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:48] Satya Bodapati
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.
[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 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 16:30] Satya Bodapati
Am closing this.. See above