Your Read.FRM file Is About To Stop Being Relevant

Comments · 457 Views

For dynamic storage engine plugins, DROP TABLE, TRUNCATE TABLE, and RENAME TABLE did not work due to incorrectly determining the engine from the read .frm file.

  • PersistentInnoDBtablespaces now include transactional storage forSerialized Dictionary Information (SDI), which is dictionary object data in serialized form. Along with the disappearance of the Read.frm file and trigger metadata files, mentioned previously, you might notice the appearance of.SDIfiles. These are serialized dictionary information files. SDI transactional storage is reserved for an in-progress feature not yet fully implemented.
  • A new command-line utility,ibd2sdi, is used to extract serialized dictionary information (SDI) from persistentInnoDBtablespaces. SDI data is not present in persistentInnoDBtablespaces in this release. Theibd2sdiutility is reserved for future use.
  • InnoDBstartup code was refactored to support MySQL initialization changes related to the MySQL data dictionary feature.

The introduction of the data dictionary enables several INFORMATION_SCHEMAproblems to be addressed:

  • Queries onINFORMATION_SCHEMA.STATISTICScould return different results depending on the order of columns in the select list.
  • SomeINFORMATION_SCHEMA tables had suboptimal column types and sizes. Such tables that are now viewed on data dictionary tables in themysqlsystem database have more appropriate column definitions.
  • Queries onINFORMATION_SCHEMAtables that resulted in directory scans to determine database or file names no longer do so, but instead read database and table names from the data dictionary.
  • Queries onINFORMATION_SCHEMAtables that opened.frmfiles to obtain table metadata no longer do so, but instead read this information from the data dictionary.
  • For comparisons of database or table names inINFORMATION_SCHEMAqueries, usingCOLLATE to force a given collation to work only if applied to theINFORMATION_SCHEMAtable column, but not if applied to the comparison value.
  • For dynamic storage engine plugins,DROP TABLE,TRUNCATE TABLE, andRENAME TABLEdid not work due to incorrectly determining the engine from the.frmfile. (Bug #74277, Bug #19902868)
  • When upgrading an SQL node to NDB 8.0 from a previous release series, the.frm file whose contents are read and then installed in the data dictionary does not contain any information about foreign keys. This meant that foreign key information was not installed in the SQL node's data dictionary. This is fixed by using the foreign key information available in the NDB data dictionary to update the local MySQL data dictionary during the table metadata upgrade. (Bug #30071043)
  • When the underlying data dictionary tables store values previously obtained by directory scans (for example, to enumerate database names or table names within databases) or file-opening operations (for example, to read information from.frmfiles),INFORMATION_SCHEMAqueries for those values now use table lookups instead. (Additionally, even for a non-viewINFORMATION_SCHEMA table values such as database and table names are retrieved by lookups from the data dictionary and do not require a directory or file scans.)
  • All MyISAM tables and indexes.
  • Tables are managed by other storage engines.
  • Other files underneath the MySQL data directory, such as the.frmfiles that record the structure of each table.
  • Any other files in the database subdirectories under the server's data directory.
  • Althoughmysqlbackupbacks up InnoDB tables without interrupting database use, the final stage that copies non-InnoDB files (such as MyISAM tables and.frmfiles) temporarily puts the database into a read-only state, using the statementFLUSH TABLES WITH READ LOCK. For best backup performance and minimal impact on database processing:
    1. Do not run longSELECTqueries or other SQL statements at the time of the backup run.
    2. Keep your MyISAM tables relatively small and primarily for read-only or read-mostly work.

    Read Blog: https://techqy.com/how-to-access-or-read-frm-file-explain-frm-file-in-mysql/

  • Then the locked phase at the end of amysqlbackuprun is short (maybe a few seconds), and does not disturb the normal processing ofmysqldmuch. If the preceding conditions are not met in your database application, use the--only-innodboption to back up only InnoDB tables, or use the--no-lockingoption to back up non-InnoDB files. Note that MyISAM,.frm, and other files copied under the--no-locking setting cannot be guaranteed to be consistent if they are updated during this final phase of the backup.

  • Althoughmysqlbackupbacks up InnoDB tables without interrupting database use, the final stage that copies non-InnoDB files (such as MyISAM tables and.frmfiles) temporarily puts the database into a read-only state, using the statementFLUSH TABLES WITH READ LOCK. For best backup performance and minimal impact on database processing:
    1. Do not run longSELECTqueries or other SQL statements at the time of the backup run.
    2. Keep your MyISAM tables relatively small and primarily for read-only or read-mostly work.

    Then the locked phase at the end of amysqlbackuprun is short (maybe a few seconds), and does not disturb the normal processing ofmysqldmuch. If the preceding conditions are not met in your database application, use the--only-innodbor--only-innodb-with-frmoption to back up only InnoDB tables, or use the--no-lockingoption to back up non-InnoDB files. Note that MyISAM,.frm, and other files copied under the--no-locking setting cannot be guaranteed to be consistent if they are updated during this final phase of the backup.

  • For a large database, a backup run might take a long time. Always check thatmysqlbackuphas completed successfully, either by verifying thatmysqlbackup returned exit code 0 or by observing thatmysqlbackuphas printed the textmysqlbackup completed OK!.
  • Restoring an NDB backup to a later version of NDB Cluster:
    • When restoring the metadata from a backup (--restore-metaoption),ndb_restorenormally attempts to reproduce the captured table schema exactly as it was when the backup was taken.

      Tables created in versions of NDB prior to 8.0 use.frmfiles for their metadata. These files can be read by themysqldin NDB 8.0, which can use the information contained therein to create the.sdifiles used by the MySQL data dictionary in later versions.

  • When restoring an older backup to a newer version of NDB, it may not be possible to take advantage of newer features such as hashmap partitioning, greater number of hashmap buckets, read backup, and different partitioning layouts. For this reason, it may be preferable to restore older schemas usingmysqldumpand themysqlclient, which allows NDB to make use of the new schema features.

Comments