SUMMARY
This article is for Flix administrators. It gives a few examples of how queries can be made directly to MySQL to gather information about Flix.
MORE INFORMATION
Flix keeps information about all of its shows, sequences, and panels in a MySQL database. Advanced Flix administrators can pull this information directly from MySQL. It can be useful in extracting specific information and troubleshooting Flix.
You can use the built-in mysql command-line tool or a 3rd party tool such as MySQL Workbench or Sequel Pro to interface with MySQL.
In the examples below you will need to replace the "<?>" characters with the required information in order for the queries to be valid.
- Get the Show ID using its Name
- Get the Sequence ID from its Name
- Find the File paths for Panel Assets
- Display a List of Users (Excluding the system user)
- Find Panel IDs that use a particular Filename
- Get a human-readable server list
- Find Flix 5 metadata for a panel ID
- Find Panel Revision from Flix 5 metadata
Get the Show ID, using its Name
In this example, you can get the show id for a project by using the name of the show.
mysql> SELECT
`shows`.`show_id`,
`shows`.`title`
FROM
`shows`
WHERE
`shows`.`title` LIKE '%<?>%';
+---------+-----------------+
| show_id | title |
+---------+-----------------+
| 2 | the little bird |
+---------+-----------------+
Get the Sequence ID from its Name
In this example, you can get the sequence ID by using the sequence name.
mysql> SELECT
`sequence`.`id`,
`sequence`.`description`
FROM
`sequence`
WHERE
`sequence`.`description` LIKE '%<?>%';
+----+-------------+
| id | description |
+----+-------------+
| 2 | a place |
+----+-------------+
Find the File paths for Panel Assets
In this example, you can find the file paths on the storage device for a particular asset.
mysql> SELECT
`vPanel_asset_ref`.`panel_id` AS PanelID,
`vPanel_asset_ref`.`vPanel_id` AS PanelRevision,
`media_object`.`ref` AS Ref,
CONCAT('<asset_dir>/', `media_object`.`id`, '_', `media_object`.`filename`) AS FilePath
FROM
`media_object`
LEFT JOIN
`asset` ON `asset`.`asset_id` = `media_object`.`asset_id`
LEFT JOIN
`vPanel_asset_ref` ON `vPanel_asset_ref`.`asset_id` = `asset`.`asset_id`
WHERE
`vPanel_asset_ref`.`show_id` = <?> and
`vPanel_asset_ref`.`sequence_id` = <?> and
`vPanel_asset_ref`.`panel_id` = <?>;
+---------+---------------+-----------+------------------------------+
| PanelID | PanelRevision | Ref | FilePath |
+---------+---------------+-----------+------------------------------+
| 1 | 1 | artwork | <asset_dir>/3_test.0491.jpeg |
| 1 | 1 | thumbnail | <asset_dir>/16_079043250.png |
| 1 | 1 | scaled | <asset_dir>/20_019628328.png |
| 1 | 1 | fullres | <asset_dir>/25_552125904.png |
+---------+---------------+-----------+------------------------------+
Display a List of Users (Excluding the system user)
In this example, you can get a list of users who can use Flix. If you are using LDAP or OAuth authentication, the list of people who have access to Flix might not be complete. In this case, you will get a list of users who have signed into Flix earlier.
mysql> SELECT
`user`.`id`,
`user`.`username`,
`user`.`is_admin`,
`user`.`type`,
`user`.`email`,
`user`.`deleted`
FROM
`user`
WHERE
`user`.`is_system` = 0;
+----+----------+----------+------+------------------------+---------+
| id | username | is_admin | type | email | deleted |
+----+----------+----------+------+------------------------+---------+
| 1 | admin | 1 | flix | flix-admin@foundry.com | 0 |
+----+----------+----------+------+------------------------+---------+
Find Panel IDs that use a particular Filename
In this example, you can find what is the Panel ID for a specific file.
mysql> SELECT
`shows`.`title` AS ShowTitle,
`sequence`.`description` AS SequenceTitle,
`panel`.`panel_id` AS PanelID
FROM
`media_object`
LEFT JOIN
`asset` ON `asset`.`asset_id` = `media_object`.`asset_id`
LEFT JOIN
`shows` ON `shows`.`show_id` = `asset`.`show_id`
LEFT JOIN
`vPanel_asset_ref` ON `vPanel_asset_ref`.`asset_id` = `asset`.`asset_id`
LEFT JOIN
`vPanel` ON `vPanel`.`show_id` = `vPanel_asset_ref`.`show_id` AND
`vPanel`.`sequence_id` = `vPanel_asset_ref`.`sequence_id` AND
`vPanel`.`panel_id` = `vPanel_asset_ref`.`panel_id`
LEFT JOIN
`sequence` ON `sequence`.`id` = `vPanel`.`sequence_id` AND
`sequence`.`id` = `vPanel`.`sequence_id`
LEFT JOIN
`panel` ON `panel`.`panel_id` = `vPanel`.`panel_id`
WHERE
`media_object`.`id` = SUBSTRING_INDEX('<?>', '_', 1) AND
`media_object`.`filename` = SUBSTRING_INDEX('<?>', '_', -1)
GROUP BY
ShowTitle, SequenceTitle, PanelID;
+------------+---------------+---------+
| ShowTitle | SequenceTitle | PanelID |
+------------+---------------+---------+
| show1 | seq_qwery | 1 |
| other show | asdf | 1 |
+------------+---------------+---------+
Get a human-readable server list
In this example, you can get a list of all Flix servers. It is also available in the Flix client under Management Console -> Servers.
mysql> SELECT
HEX(`server`.`server_id`) AS serverIdent,
INET_NTOA(`server`.`host_ip`) AS IP,
`server`.`port`,
`server`.`rpc_port`,
`server`.`running`,
`server`.`start_date`,
`server`.`hostname`
FROM
`server`;
+----------------------------------+--------------+------+----------+---------+---------------------+-----------------+
| serverIdent | IP | port | rpc_port | running | start_date | hostname |
+----------------------------------+--------------+------+----------+---------+---------------------+-----------------+
| F22A1072B6754BCDB78477EDCD81F8FD | 192.168.1.67 | 8080 | 9876 | 1 | 2021-02-01 13:18:29 | flx.foundry.com |
+----------------------------------+--------------+------+----------+---------+---------------------+-----------------+
Find Flix 5 metadata for a panel ID
In this example, you can find what data Flix is keeping for panels migrated from Flix 5
mysql> SELECT
`vpanel`.`panel_id`,
`vpanel`.`data`
FROM
`vpanel`
WHERE
`show_id` = <?> AND
`sequence_id` = <?> AND
`vpanel`.`panel_id` = <?>;
+----------+----------------------------------------------------------+
| panel_id | data |
+----------+----------------------------------------------------------+
| 1 | {"flix5_panel_id": "31", "flix5_panel_revision": "1"} |
| 1 | {"flix5_panel_id": "31", "flix5_panel_revision": "2"} |
| 1 | {"flix5_panel_id": "31", "flix5_panel_revision": "3"} |
+----------+----------------------------------------------------------+
Find Panel Revision from Flix5 metadata
This search is helpful when wanting to find the Flix counterpart of a Flix 5 panel
mysql> SELECT
`vPanel`.`show_id`,
`vPanel`.`sequence_id`,
`vPanel`.`panel_id`,
`vPanel`.`panel_revision`
FROM
`vPanel`
JOIN
`sequence` ON `sequence`.`id` = `vPanel`.`sequence_id`
WHERE
`vPanel`.`data`->"$.flix5_panel_id" = "<?>" AND
`vPanel`.`data`->"$.flix5_panel_revision" = "<?>" AND
`sequence`.`tracking_code` = "<?>";
+---------+-------------+----------+----------------+
| show_id | sequence_id | panel_id | panel_revision |
+---------+-------------+----------+----------------+
| 7| 97 | 236 | 1 |
+---------+-------------+----------+----------------+
FURTHER READING
If this isn't what you were looking for, and wanted to find out how to install MySQL, you can refer to the article below:
Q100551: Installing MySQL 8 on Red Hat/CentOS 7 or Rocky 9
If instead, you were trying to find best practices to backup/restore Flix database data, please check the two articles below:
You can learn how to get the show_id and sequence_id from the following article:
Q100619: How to retrieve show_id and/or sequence_id from Flix Client
Finally, for recommendations on how to set Flix up in the most efficient way, you can read this article:
Q100593: Optimum Flix servers setup
We're sorry to hear that
Please tell us why