As an anonymous user, you can only add new data. If you would like to also modify existing data, please create an account and indicate your languages on your user page.

User:Malafaya/Removed options causing problems

From OmegaWiki
Jump to: navigation, search

It happens frequently that one obtains a SQL error when accessing specific expressions. I found out that these are usually due to option items being removed from options for a particular language. Usually, these are in the part of speech option. I.e., if we decide to remove the option item verb from the part of speech option/annotation for English, then we will be unable to load any expression which is annotated as a verb. This is because the annotation value is not marked as deleted (in table uw_option_attribute_values) when the corresponding item is deleted (in table uw_option_attribute_options).

To fix this, one just marks the value as having been deleted in the same transaction as the option item itself. Suppose we are working on option_id 420235, which was deleted at some point:

SELECT remove_transaction_id FROM uw_option_attribute_options where option_id=420235

This returns remove_transaction_id 317785. Then,

update uw_option_attribute_values set remove_transaction_id=317785 where option_id=420235;

fixes all objects still using this deleted option item.

Quickly checking for existing problems[edit]

SELECT * FROM uw_option_attribute_values where remove_transaction_id is null and option_id in (select option_id from uw_option_attribute_options where remove_transaction_id is not null);

How to get to option_id[edit]

It's common to know what specific expression is causing trouble rather than a specific option_id. Let's see how to get there from a known troubled expression.

Example: harbour

SELECT * FROM uw_expression where spelling='harbour';

This returns 2 expressions. P.S. Sometimes, the "main" expression is not the problem but some other linked to the same DM. In that case, execute this for all related expressions. So, expression_id in (45722, 472462). Let's consult the uw_syntrans table now:

SELECT * FROM uw_syntrans where expression_id in (45722, 472462);

This yields 3 rows, but 1 is deleted (remove_transacion_id has a value). So, syntrans_sid in (472463, 638800). It's time for the uw_objects table:

SELECT * FROM uw_objects where object_id in (427463, 638800) AND uw_objects.table='uw_syntrans';

This will return all SynTrans objects that are annotated with other objects. Objects are a generic relationable entity. They can be other entities besides SynTranses. We are currently working on SynTranses so we filter by that table. There is indeed one object 638800 in table uw_syntrans. The other object 427463 is not a syntrans as it was not returned.

OK, so we are left with one object. Let's see what options are assigned (which ones have values) to this SynTrans objects:

SELECT * FROM uw_option_attribute_values where object_id=638800;

We got an option value. option_id is 420235 and this value is not deleted (remove_transaction_id is null). We have discovered the potential problematic option_id. Potential, because we still have to check if this has been effectively deleted and this is causing the problem. Let's check the uw_option_attribute_options:

SELECT * FROM uw_option_attribute_options where option_id=420235

Here we see that remove_transaction_id is not null, confirming that it has been deleted. This returns remove_transaction_id 317785. Then,

update uw_option_attribute_values set remove_transaction_id=317785 where option_id=420235;

fixes all objects still using this deleted option item.

TODO: I don't know at this stage how to find what real-world option is associated with this option_id. What item was removed from the option? And what option has been affected (usually, part of speech).