MySQL: Circular Foreign Key Causes "Resource Is Not Open" Exception in Frictionless

[ipimpat]

I encountered an issue when trying to open a resource in Frictionless that has a circular foreign key relationship with another resource.

The operation fails immediately with a FrictionlessException: [error] resource is not open.

Database Structure:

The database consists of two tables, foo and bar, which reference each other through foreign keys:

CREATE TABLE IF NOT EXISTS `bar` (
  `id` int(11) NOT NULL,
  `foo_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `foo` (
  `id` int(11) NOT NULL,
  `bar_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `bar`
  ADD PRIMARY KEY (`id`),
  ADD KEY `foo_id` (`foo_id`);

ALTER TABLE `foo`
  ADD PRIMARY KEY (`id`),
  ADD KEY `bar_id` (`bar_id`);

ALTER TABLE `bar`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `foo`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `bar`
  ADD CONSTRAINT `bar_fk_1` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`);

ALTER TABLE `foo`
  ADD CONSTRAINT `foo_fk_1` FOREIGN KEY (`bar_id`) REFERENCES `bar` (`id`);

Test package.yaml File:

resources:
  - name: foo
    type: table
    path: mysql://username:password@host/database
    scheme: mysql
    format: sql
    dialect:
      sql:
        table: foo
  - name: bar
    type: table
    path: mysql://username:password@host/database
    scheme: mysql
    format: sql
    dialect:
      sql:
        table: bar

Test Code:

from frictionless import Package

package = Package("package.yaml")
package.get_resource("foo").open()

Steps to Reproduce:

1. Create the database structure as defined above.
2. Use the provided package.yaml file to configure the Frictionless package.
3. Run the test code to open the foo resource.
4. Observe that the operation fails immediately with the following traceback:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File ".../venv/lib/python3.12/site-packages/frictionless/resources/table.py", line 171, in open
    self.__open_lookup()
  File ".../venv/lib/python3.12/site-packages/frictionless/resources/table.py", line 263, in __open_lookup
    with source_res:
  File ".../venv/lib/python3.12/site-packages/frictionless/resource/resource.py", line 261, in __enter__
    self.open()
  File ".../venv/lib/python3.12/site-packages/frictionless/resources/table.py", line 171, in open
    self.__open_lookup()
  File ".../venv/lib/python3.12/site-packages/frictionless/resources/table.py", line 264, in __open_lookup
    for row in source_res.row_stream:  # type: ignore
               ^^^^^^^^^^^^^^^^^^^^^
  File ".../venv/lib/python3.12/site-packages/frictionless/resources/table.py", line 138, in row_stream
    raise FrictionlessException("resource is not open")
frictionless.exception.FrictionlessException: [error] resource is not open 

Expected Behavior:

The resource should open successfully, even when it has circular foreign key references.

Observed Behavior:

An exception is raised immediately when trying to open the resource, indicating that the resource is not open due to the circular reference.

Possible Cause:

The issue may relate to how Frictionless manages resource lifecycles when dealing with circular foreign key references. Specifically, it appears that the resource cannot open itself properly when it references another resource that refers back to it.

Environment:

• Frictionless version: 5.18.0