When doing our upgrade to 3.18.8 I made the mistake of upgrading without a custom asset that we had created. I added this in after our install, and re-ran step_02 and compile_locale, but when viewing the custom assets it seems that all their attribute data is gone. Even the name and short name are gone, but strangely, the short name shows up correctly in the asset map. This leaves me to believe that the data might still be in the database, but I might just need to link it to the custom assets somehow?
Is there a script that I need to run to clean this up or fix? If there is no option to get this back, it won't be too bad, we only have about 70 of these assets. But, if I could get the data back it would be nice.
The short name is cached in the sq_ast table, so your assets are still there.
Grab the asset ID of one and do:
SELECT * FROM sq_ast_attr_value WHERE assetid = 'xxx';
If you see values, your data is still there but probably mapped to old (deleted) attribute IDs. You'll need to find the old and new IDs and run a DB query for each to remap the values.
The easiest way to do this is to create a new asset of your type, get its asset ID and run the query above. You should see attrid, assetid and custom_value.
Compare both the working and broken data sets and you should be able to see what the old attrid was and now what the new one is for each value.
Then just run, for each attrid:
UPDATE sq_ast_attr_value SET attrid = NEW-ID WHERE attrid = OLD-ID;
As you do each one, you should start seeing values appears in the backend interface.
Thanks.
So, was all of this caused by me not having the asset installed at the same time I did the upgrade?
[quote]Grab the asset ID of one and do:
SELECT * FROM sq_ast_attr_value WHERE assetid = ‘xxx’;[/quote]
When I run this query, I get (obviously adding my asset id):
ERROR: relation “sq_ast_attr_value” does not exist
EDIT: I realized the table is actually called sq_ast_attr_val, works now. :)’ /> And my data is still there. <img src=‘http://forums.matrix.squiz.net/public/style_emoticons/<#EMO_DIR#>/smile.gif’ class=‘bbc_emoticon’ alt=’
Ok, I tried UPDATE sq_ast_attr_val SET attrid = 2347 WHERE attrid = 2109; but got the following error:
ERROR: duplicate key violates unique constraint "ast_attr_val_pk"
Any ideas on that one?
Try adding "AND assetid = xxx" to the end and give it a go for one asset first.
One asset must have some duplicate values. Do a couple of assets at a time and if they work, we can think about doing:
DELETE FROM sq_ast_attr_val WHERE attrid = 2347;
to get rid of any new attributes values that will conflict with the update.
Might also be worth doing:
SELECT assetid FROM sq_ast_attr_val WHERE attrid = 2347;
to see which assets may be having problems.
[quote]Try adding "AND assetid = xxx" to the end and give it a go for one asset first.
One asset must have some duplicate values. Do a couple of assets at a time and if they work, we can think about doing:
DELETE FROM sq_ast_attr_val WHERE attrid = 2347;
to get rid of any new attributes values that will conflict with the update.
Might also be worth doing:
SELECT assetid FROM sq_ast_attr_val WHERE attrid = 2347;
to see which assets may be having problems.[/quote]
Yes, running UPDATE sq_ast_attr_val SET attrid = 2347 WHERE attrid = 2109 AND assetid = 33111; worked, and the attribute for that asset is now seen on the details screen.
Running SELECT assetid FROM sq_ast_attr_val WHERE attrid = 2347; returned 3 asset ID's. What does this mean?
So, running DELETE FROM sq_ast_attr_val WHERE attrid = 2347; will do, what, fix the duplicate key violation?
Thanks!
If you only got 3 assets from your first select, you can look at them one by one. They are assets that are working correctly, but must have entries for both the old and new attribute IDs.
Let's say one of them is asset 123.
SELECT * FROM sq_ast_attr_val WHERE assetid = '123';
Take a look at the custom_val column and see if there are two entries for each value. One will have the old attr ID, the other the new attr ID. Something like:
attrid,assetid,custom_val
2347,123,Value
2109,123,Value
If this is the case, you can go ahead and delete the new attrid values using the DELETE.
If you only get this:
attrid,assetid,custom_val
2347,123,Value
Then deleting those values will wipe out all data for this asset. What you'll need to do is not include this asset in your updates:
UPDATE sq_ast_attr_val SET attrid = 2347 WHERE attrid = 2109 AND assetid NOT IN ('123', '234', '345');
(change asset IDs to be the IDs of the assets with only the new attrids)
Thank you so much Greg! This worked perfectly using the format: UPDATE sq_ast_attr_val SET attrid = 2347 WHERE attrid = 2109 AND assetid NOT IN ('33111', '33123', '37771');
I really do appreciate your help, you saved me hours of manually going back to our old server and copying data manually, as well as calling our Media Services, who would have not been happy about loosing their audio data.
Thanks again!
No problem.