[MDEV-18827] Create utility to parse frm files and print their DDL (Full-time project - potential part-time) Created: 2019-03-06  Updated: 2023-11-08

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Create Table, Scripts & Clients
Fix Version/s: None

Type: Task Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Vicențiu Ciorbaru
Resolution: Unresolved Votes: 5
Labels: beginner-friendly, gsoc21, gsoc22

Issue Links:
Relates
relates to MDEV-15225 Can't import .ibd file with temporal ... Closed
relates to MDEV-26577 InnoDB: Failing assertion: dict_tf2_i... Closed
relates to MDEV-28727 ALTER TABLE ALGORITHM=NOCOPY does not... Closed
relates to MDEV-4637 TOOLS - FRM file parser / file inform... Open

 Description   

It would be useful if MariaDB had a utility that was able to parse frm files and print the DDL associated with the table.

For example, it would be useful for users who performed a partial backup with Mariabackup:

https://mariadb.com/kb/en/library/partial-backup-and-restore-with-mariabackup/

But they forgot to also backup the table DDL, so they can't restore the tables using the following process:

https://mariadb.com/kb/en/library/innodb-file-per-table-tablespaces/#importing-transportable-tablespaces-for-non-partitioned-tables

mysqlfrm is a tool that already exists that does similar things:

https://github.com/mysql/mysql-utilities/blob/master/scripts/mysqlfrm.py

But it doesn't seem very user-friendly. It needs to be able to contact the local MariaDB server, and it also needs to be able to spawn a server instance, and it seems to need to be able to create a bunch of files during this process. e.g.:

[ec2-user@ip-172-30-0-249 ~]$ cd /tmp
 
[ec2-user@ip-172-30-0-249 tmp]$ sudo mysqlfrm --server=root:@localhost:3306 /var/lib/mysql/db1/tab.frm --port=12345 --user=mysql
 
# Source on localhost: ... connected.
 
# Spawning server with --user=mysql.
 
# Starting the spawned server on port 12345 ... done.
 
# Reading .frm files
 
#
 
# Reading the tab.frm file.
 
#
 
# CREATE statement for /var/lib/mysql/db1/tab.frm:
 
#
 
 
 
CREATE TABLE `db1`.`tab` (
 
  `id` int(11) NOT NULL,
 
  `str` varchar(50) DEFAULT NULL,
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
 
 
#...done.



 Comments   
Comment by Oli Sennhauser [ 2021-03-26 ]

This would be a huge benefit for mariabackup partial table or schema restore. this tool could help to automatize and thus simplify partial table or schema restore with mariabackup!!! which is still quite complicated for normal admins. Ideally Vladislav Vaintroub and/or Vladislav Lesin should look into this???

Comment by Marko Mäkelä [ 2022-06-28 ]

The actual ROW_FORMAT used by InnoDB can differ from what is specified in the .frm file. See MDEV-26577 for a recent example.

Sometimes, there are many .frm file encodings that lead to the same SHOW CREATE TABLE output, even though internally there is some significant change. Examples include MDEV-15225 and MDEV-28727.

For partial restore of backups, I think that the correct solution would be to implement .frm file based IMPORT (MDEV-11658).

Comment by Antonio Bardazzi [ 2023-01-26 ]

As a partial workaround there is frmdump utility by DBSake:

Decode a MySQL .frm file and output a CREATE VIEW or CREATE TABLE statement.

This command does not require a MySQL server and interprets a .frm file according to rules similar to the MySQL server.

Comment by Marko Mäkelä [ 2023-11-08 ]

ALTER TABLE…IMPORT TABLESPACE based on the contents of the original .frm file was actually implemented in MDEV-26137.

Generated at Thu Feb 08 08:47:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.