Bad entries in Hall D CCDB?

[sdobbs]

While tracking down an issue related to the SQLite files used in Hall D (JeffersonLab/halld_sim#178 (comment)), I noticed that there is some weird issue in getting assignments from a couple of tables. Basically, I am trying to loop through all the assignments for each table to track down problematic comment field data.

In two cases, I get the following error:
checking /FDC/fdc_drift ...
Traceback (most recent call last):
File "search.py", line 65, in
main()
File "search.py", line 57, in main
assigns = ccdb1.get_assignments(table.path)
File "/group/halld/Software/builds/Linux_CentOS7.7-x86_64-gcc4.8.5/ccdb/ccdb_1.06.06/python/ccdb/provider.py", line 1123, in get_assignments
table = self.get_type_table(path_or_table)
File "/group/halld/Software/builds/Linux_CentOS7.7-x86_64-gcc4.8.5/ccdb/ccdb_1.06.06/python/ccdb/provider.py", line 481, in get_type_table
table = query.one()
File "/group/halld/Users/sdobbs/rcdb/python/sqlalchemy/orm/query.py", line 2817, in one
"Multiple rows were found for one()")
sqlalchemy.orm.exc.MultipleResultsFound: Multiple rows were found for one()

Any idea what could be causing this? I have attached a script that can reproduce the problem: search.py.txt

[markito3]

Note that query.py is coming from the rcdb library. Doesn't seem that it should work like that...

[sdobbs]

good point, I missed that... that is super weird...

[sdobbs]

Taking out the RCDB library, I get:

checking /FDC/fdc_drift ...
Traceback (most recent call last):
File "search.py", line 65, in
main()
File "search.py", line 57, in main
assigns = ccdb1.get_assignments(table.path)
File "/group/halld/Software/builds/Linux_CentOS7.7-x86_64-gcc4.8.5/ccdb/ccdb_1.06.06/python/ccdb/provider.py", line 1123, in get_assignments
table = self.get_type_table(path_or_table)
File "/group/halld/Software/builds/Linux_CentOS7.7-x86_64-gcc4.8.5/ccdb/ccdb_1.06.06/python/ccdb/provider.py", line 481, in get_type_table
table = query.one()
File "/group/halld/Software/builds/Linux_CentOS7.7-x86_64-gcc4.8.5/ccdb/ccdb_1.06.06/python/sqlalchemy/orm/query.py", line 2537, in one
"Multiple rows were found for one()")
sqlalchemy.orm.exc.MultipleResultsFound: Multiple rows were found for one()

In the end, both cases are calling the same (?) sqlalchemy library, so maybe this is not so surprising...

[DraTeots]

Hi!
query comes from SQLAlchemy - an external library that is used both in RCDB and CCDB.

query.one - requires exactly one data record from a database while multiple rows has been returned. That triggers this exception.

This relates to this query:

query = self.session.query(TypeTable)
                                .filter(TypeTable.name == table_name,
                                        TypeTable.parent_dir_id == parent_dir.id)

So somehow there are two /FDC/fdc_drift tables.

[DraTeots]

But you should see it with ls command

[sdobbs]

Oh, you're right. That's not good... how in the world did that happen? I'll have to look at the logs...

Any suggestions on how to cleanly get rid of one of these? For what it's worth, it looks like this table is not actually used in the current halld_recon.

[DraTeots]

There is also get_type_tables method. You can quickly use it to check what is happening

tables = db.get_type_tables('/FDC/')
for table in tables:
    print(table.name, table.path)
    for constant_set in table.constant_sets:
        print(constant_set.assignment)
        print(constant_set.assignment.run_range.min

(!) Be aware that the code above is slow as each table.constant_set.assignment.run_range will trigger a separate DB query. But is OK for debug

You can look at all properties here
https://github.com/JeffersonLab/ccdb/blob/master/python/ccdb/model.py

[markito3]

MariaDB [ccdb]> select * from typeTables where name = "fdc_drift";
+----+---------------------+---------------------+-------------+-----------+-------+----------+--------------+----------+---------+
| id | created | modified | directoryId | name | nRows | nColumns | nAssignments | authorId | comment |
+----+---------------------+---------------------+-------------+-----------+-------+----------+--------------+----------+---------+
| 11 | 2013-01-17 19:02:29 | 2013-01-17 19:02:29 | 8 | fdc_drift | 140 | 1 | 0 | 1 | |
| 14 | 2013-01-17 19:02:30 | 2013-01-17 19:02:30 | 8 | fdc_drift | 139 | 1 | 0 | 1 | |
+----+---------------------+---------------------+-------------+-----------+-------+----------+--------------+----------+---------+
2 rows in set (0.022 sec)

[DraTeots]

If there is no assignments just delete one. It might be cumbersome with the commands, but API is

db.delete_type_table(table)

tables = db.get_type_tables('/FDC/')
for table in tables:
    if table.name == 'fdc_drift':
        db.delete_type_table(table)

[markito3]

MariaDB [ccdb]> select * from constantSets where constantTypeId = 11;
Empty set (0.023 sec)

MariaDB [ccdb]> select * from constantSets where constantTypeId = 14;
Empty set (0.023 sec)

[markito3]

looks like they are not used. Dmitry, do you agree?

[DraTeots]

Yep. The question is how that happened

[markito3]

Since there are no constants assigned for fdc_drift(id = 11) nor for fdc_drift(id = 14) we could delete either or both directly from the database table with no harm done, no?

[DraTeots]

No harm

[markito3]

I'll touch base with Lubomir and Keigo...