Merge error "Unknown column 'spdataset.data' in 'field list'"

[bronwyncombs]

Describe the bug
OperationalError on merge

To Reproduce
Steps to reproduce the behavior:

1. Merge accession agents using instructions from this comment
2. Click Merge
3. See error

Screenshots

Desktop:

• macOS, Chrome, v7.9-dev
  Database Name: archireef

Additional context
Specify 7 Crash Report - 2023-07-31T18_31_53.402Z.txt

[melton-jason]

This issue should only be specific to the archireef database.

Looking at the server, their database has a malformed spdataset table:

MariaDB [archireef]> desc spdataset;
+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra          |
+--------------------+--------------+------+-----+---------+----------------+
| id                 | int(11)      | NO   | PRI | NULL    | auto_increment |
| name               | varchar(256) | NO   |     | NULL    |                |
| columns            | longtext     | NO   |     | NULL    |                |
| uploadplan         | longtext     | YES  |     | NULL    |                |
| uploadresult       | longtext     | YES  |     | NULL    |                |
| collection_id      | int(11)      | NO   | MUL | NULL    |                |
| specifyuser_id     | int(11)      | NO   | MUL | NULL    |                |
| visualorder        | longtext     | YES  |     | NULL    |                |
| importedfilename   | longtext     | YES  |     | NULL    |                |
| remarks            | longtext     | YES  |     | NULL    |                |
| timestampcreated   | datetime(6)  | NO   |     | NULL    |                |
| timestampmodified  | datetime(6)  | NO   |     | NULL    |                |
| createdbyagent_id  | int(11)      | YES  | MUL | NULL    |                |
| modifiedbyagent_id | int(11)      | YES  | MUL | NULL    |                |
+--------------------+--------------+------+-----+---------+----------------+
14 rows in set (0.002 sec)

Compare this to the spdataset table on the fwri database:

MariaDB [fwri]> desc spdataset;
+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra          |
+--------------------+--------------+------+-----+---------+----------------+
| id                 | int(11)      | NO   | PRI | NULL    | auto_increment |
| name               | varchar(256) | NO   |     | NULL    |                |
| columns            | longtext     | NO   |     | NULL    |                |
| data               | longtext     | NO   |     | NULL    |                |
| uploadplan         | longtext     | YES  |     | NULL    |                |
| uploaderstatus     | longtext     | YES  |     | NULL    |                |
| uploadresult       | longtext     | YES  |     | NULL    |                |
| rowresults         | longtext     | YES  |     | NULL    |                |
| collection_id      | int(11)      | NO   | MUL | NULL    |                |
| specifyuser_id     | int(11)      | NO   | MUL | NULL    |                |
| visualorder        | longtext     | YES  |     | NULL    |                |
| importedfilename   | longtext     | YES  |     | NULL    |                |
| remarks            | longtext     | YES  |     | NULL    |                |
| timestampcreated   | datetime(6)  | NO   |     | NULL    |                |
| timestampmodified  | datetime(6)  | NO   |     | NULL    |                |
| createdbyagent_id  | int(11)      | YES  | MUL | NULL    |                |
| modifiedbyagent_id | int(11)      | YES  | MUL | NULL    |                |
+--------------------+--------------+------+-----+---------+----------------+
17 rows in set (0.001 sec)

archireef is missing three very important columns in the spdataset table: data, uploaderstatus, and rowresults.

Even accessing the datasets via the Workbench button from the Navigation Menu results in an error

I made sure this was not a problem on databases that were configured as Specify expects.
To test this you can:

• Create a WorkBench dataset (Any Base Table)
• (should be optional) upload the dataset
• Try and merge the agent who created the Dataset

[bronwyncombs]

@melton-jason thank you for looking into this!!