Data Export for Webportal / Specify 7.7 Upgrade

Get Help
1

Hello,

We are using the all-in-one docker and just upgraded to 7.7 (image: 6044e20a2548) and currently running into the following error

ProgrammingError at /context/user.json

(1146, "Table 'specify.agentidentifier' doesn't exist")

Request Method: 	GET
Request URL: 	http://dev-biodis22.ksulib.net/context/user.json
Django Version: 	2.2.10
Exception Type: 	ProgrammingError
Exception Value: 	

(1146, "Table 'specify.agentidentifier' doesn't exist")

Exception Location: 	/opt/specify7/ve/lib/python3.8/site-packages/MySQLdb/connections.py in query, line 276
Python Executable: 	/opt/specify7/ve/bin/python3.8
Python Version: 	3.8.0
Python Path: 	

['/opt/specify7',
 '/opt/specify7',
 '/opt/specify7/ve/bin',
 '/usr/lib/python38.zip',
 '/usr/lib/python3.8',
 '/usr/lib/python3.8/lib-dynload',
 '/opt/specify7/ve/lib/python3.8/site-packages',
 '/opt/specify7']

Server time: 	Wed, 22 Feb 2023 15:57:23 -0600

I did think oh, there might have been a new specify 6 release, so I went and downloaded the client, and connected to the database, and it says it is build v6.8.02 which is the same as the client, so I am assuming it is up to date?

Also, when trying to use the Data Exporter with the webportal Mapping, and “Build/Update”, I get the following error.

Screenshot 2023-02-22 at 9.50.07 AM
Screenshot 2023-02-22 at 9.50.07 AM2820×358 52.1 KB

Thanks!

2

Hi @tmisilo,

It looks like your Specify database does not have the Specify 6.8.02 schema changes applied or you have a misconfiguration in your docker compose file.

If the table agentidentifier does not exist in the database, it looks like the update was not applied or not applied correctly.

Can you log into the database with the Specify 6.8.02 main application (assuming it was previously used with Specify 6.8.01) so that the update can take place before using the DataExporter tool?

If you are able to log in and see no issue, please run the following command on your database:

select SpVersionID, AppVersion from spversion;

You should see the following:

SpVersionID AppVersion
1 6.8.02

If your AppVersion is still 6.8.01, it did not update correctly. If it is 6.8.02 and the agentidentifier table does not exist, I can take a closer look at your database to resolve this!

Docker composition instructions:

You will need to ensure that the Specify 6 image is specify6-service:6.8.02 in the docker-compose.yml file for your docker instance.

  specify6:
    image: specifyconsortium/specify6-service:6.8.02
    volumes:
      - "specify6:/volumes/Specify"
3

Can you log into the database with the Specify 6.8.02 main application (assuming it was previously used with Specify 6.8.01) so that the update can take place before using the DataExporter tool?

Yes, I can do that. We were running v6.8.01 (at least according to the previous docker-compose.yml I have)

MariaDB [specify]> select SpVersionID, AppVersion from spversion;
+-------------+------------+
| SpVersionID | AppVersion |
+-------------+------------+
|           1 | 6.8.02     |
+-------------+------------+

  specify6:
    image: specifyconsortium/specify6-service:6.8.02
    volumes:
      - "specify6:/volumes/Specify"

So it looks like we should be good on the version, but the table does not exist.

MariaDB [specify]> select * from agentidentifier;
ERROR 1146 (42S02): Table 'specify.agentidentifier' doesn't exist
4

It looks like the schema update did not take place somehow. You could manually change the AppVersion back to 6.8.01 and try to run the update again, though it may skip those tables again.

You can send us a copy of the database if you would like us to take a closer look. I should be able to kickstart the process so that the migration runs correctly from here!

If you’d prefer to get it working temporarily, you could just change the AppVersion to 6.8.01 and set the corresponding specify6 image back to 6.8.01 in the docker-compose.yml file. This should bring Specify 7 back to a functioning state in the meantime.

5

I tried this, and still no new table.

I have uploaded this as requested.

Changing it back to 6.8.01 did not seem to help allow us to get back into Specify 7.

6

Changing it back to 6.8.01 did not seem to help allow us to get back into Specify 7.

Did you make sure to modify the docker-compose to also point to the image for 6.8.01?

It also looks like none of the other schema migrations took place either, as the CREATE TABLE statements show that all of the double fields that became BigDecimal fields remain unchanged. Do you know how this update took place?

I have uploaded this as requested.

Unfortunately, every time I attempt to restore this SQL file, I encounter an issue with the syntax. I have spot-fixed one after another but I will not be able to restore it without some amount of clean-up.

See the associated error:

ERROR 1064 (42000) at line 42641: You have an error in your SQL syntax; 
check the manual that corresponds to your MariaDB server version for the right 
syntax to use near 'v(29082,'2007-05-01 01:35:34','2007-05-01 
01:37:20',1,'1890-05-24',1,NULL,NUL...' at line 154

It looks like this was made with MariaDB 10.9.3, and I am using 10.10.2. Do you know why the syntax may be invalid?

7

I’m not sure what you are asking? I updated this line to be the following:

  specify6:
    image: specifyconsortium/specify6-service:6.8.01
    volumes:
      - "specify6:/volumes/Specify"

Did I need to do something else?

It looks like this was made with MariaDB 10.9.3, and I am using 10.10.2. Do you know why the syntax may be invalid?

Nope no idea. I just upgraded MariaDB, and am uploading a new file.

8

That looks perfect! It shouldn’t be looking for the agentidentifier table with the Specify 6.8.01 files, so that is very bizarre.

The new MariaDB import was successful so I will begin investigating as soon as possible. Thank you!

9

Hi @tmisilo,

Great news. I was able to update the database successfully after changing both the AppVersion to and 6.8.01 and the SchemaVersion to 2.9.

This allowed me to enter the upgrade mode to make all of the changes successfully after entering my IT credentials.

I can back up my copy of your database after the updates and send it to you or you can try the above steps and run an upgrade using Specify 6.8.02.

10

Great news. I was able to update the database successfully after changing both the AppVersion to and 6.8.01 and the SchemaVersion to 2.9.

This worked thank you! I was able to do an export as well, now to work on getting the webportal working :slight_smile:

Thanks again!