Web based family history software

Question Error after deleting Unused places

More
1 month 3 weeks ago - 1 month 3 weeks ago #1 by RickM
Error after deleting Unused places was created by RickM
Hi there

My friend Martin and I have been working on one of his sites to complete and massive tidy up of Data.. Lets say a huge number of issues have been solved and Martin is now working through places to get them right.

Whilst deleting Unused place names through the MAP >> Geographic Data Option in the control panel the following Error occurs.. We are not sure if this is related to some screw ups in place names introduced by us that would look similar to "Derby, Yorkshire, England,Derby, Yorkshire, EnglandDerby, Yorkshire, England" introduced by us accidentally or another reason.. Or is this an undocumented feature  <Smile>..

 

If You D/Load the file you should be able to read the Error properly.. Sorry right now there is no empty addresses to try and recreate the error.. I don't do addresses as I am never sure of English places etc..

Further digging has unearthed these two lines in the database.. Not sure how to proceed to resolve now!

 


 

Rick M
New Zealand - Nu Tirani
alineofmalkins.com/wt_malkins
Webtrees v2.1.19
Last edit: 1 month 3 weeks ago by RickM. Reason: Further Info added

Please Log in or Create an account to join the conversation.

More
1 month 3 weeks ago #2 by Franz Frese

Please Log in or Create an account to join the conversation.

More
1 month 3 weeks ago - 1 month 3 weeks ago #3 by RickM
Replied by RickM on topic Error after deleting Unused places

unreadable

 
Franz, I did suggest Downloading the Image to read it properly, however
Hopefully this is better
 
 

Rick M
New Zealand - Nu Tirani
alineofmalkins.com/wt_malkins
Webtrees v2.1.19
Last edit: 1 month 3 weeks ago by RickM.

Please Log in or Create an account to join the conversation.

More
1 month 3 weeks ago - 1 month 3 weeks ago #4 by Franz Frese
Replied by Franz Frese on topic Error after deleting Unused places
even download was not better, else I would not have written.
About the error message: You've got me stumped.
But Greg sure knows more about the error message: "foreign key cascading depth is more than 15" and why this can happen.

searching the net seems you have some self referencing (within the places for England).
I would try to export the places (csv) and perhaps clearing the table and reimport.
Last edit: 1 month 3 weeks ago by Franz Frese.

Please Log in or Create an account to join the conversation.

More
1 month 3 weeks ago #5 by RickM
Replied by RickM on topic Error after deleting Unused places
Sorry, Understood..

Hmm Your suggestions sound rugged/scary, I will wait and see if Greg chimes in.. If Not I will have a bash at your suggestion..

Thanks.

Rick M
New Zealand - Nu Tirani
alineofmalkins.com/wt_malkins
Webtrees v2.1.19

Please Log in or Create an account to join the conversation.

More
1 month 3 weeks ago #6 by fisharebest
Replied by fisharebest on topic Error after deleting Unused places
This is a limit in MySQL that I didn't know about until now.

The place-locations table has a hierarchy. Each child place is linked to a parent place.

When you delete a parent, the children are deleted automatically - and the grandchildren and great-grandchildren, etc.

The MySQL limit says that this "cascading deletion" can only work to 15 levels deep.

So I guess you have a place-hierarchy with more than 15 levels?

If you can find this location, then perhaps delete the middle of the hierarcy (less than 15 levels from the bottom).

Greg Roach - greg@subaqua.co.uk - @fisharebest@phpc.social - fisharebest.webtrees.net

Please Log in or Create an account to join the conversation.

More
1 month 3 weeks ago #7 by RickM
Replied by RickM on topic Error after deleting Unused places

So I guess you have a place-hierarchy with more than 15 levels?

If you can find this location, then perhaps delete the middle of the hierarcy (less than 15 levels from the bottom).
 
Greg,

I did wonder about this, as some errors such as you described did crop up after working with places etc.. Seems multiple place names got copied into one a number of times.. I thought I had found all Damn there are some 15000 places to search through.. I will give it a bash..

Do you have any ideas that 'might' speed up the search?? <Smile>>
Thanks for chiming in..
 

Rick M
New Zealand - Nu Tirani
alineofmalkins.com/wt_malkins
Webtrees v2.1.19

Please Log in or Create an account to join the conversation.

More
1 month 3 weeks ago #8 by fisharebest
Replied by fisharebest on topic Error after deleting Unused places
> Do you have any ideas that 'might' speed up the search??

WITH RECURSIVE x AS (SELECT *, 1 AS depth FROM wt_place_location WHERE parent_id IS NULL UNION ALL SELECT y.*, 1 + x.depth FROM wt_place_location AS y, x WHERE y.parent_id = x.id) SELECT * FROM x ORDER BY depth;

Greg Roach - greg@subaqua.co.uk - @fisharebest@phpc.social - fisharebest.webtrees.net

Please Log in or Create an account to join the conversation.

More
1 month 3 weeks ago #9 by RickM
Replied by RickM on topic Error after deleting Unused places
Thanks Greg

The Query does run but I don't get the results expected..

When entering the query into the phpMyAdmin query screen it shows to have an error

'The name of the CTE was expected. (near x)'.

And the actual SQL appears randomly.

And it shows all 13279 rows in the table

Hmmm.. <Smile>

Rick M
New Zealand - Nu Tirani
alineofmalkins.com/wt_malkins
Webtrees v2.1.19

Please Log in or Create an account to join the conversation.

More
1 month 3 weeks ago #10 by fisharebest
Replied by fisharebest on topic Error after deleting Unused places
Then a query similar to that will be what you want.

Sorry - busy at work...

Greg Roach - greg@subaqua.co.uk - @fisharebest@phpc.social - fisharebest.webtrees.net

Please Log in or Create an account to join the conversation.

More
1 month 3 weeks ago #11 by RickM
Replied by RickM on topic Error after deleting Unused places
No worries Greg, Appreciate you helping anyways..

Enjoy Work! <Smile>

Rick M
New Zealand - Nu Tirani
alineofmalkins.com/wt_malkins
Webtrees v2.1.19

Please Log in or Create an account to join the conversation.

More
1 month 3 weeks ago - 1 month 3 weeks ago #12 by fisharebest
Replied by fisharebest on topic Error after deleting Unused places
Try this:

WITH RECURSIVE x AS (SELECT id, cast(place as char(32767)) AS hierarchy, 1 AS depth FROM wt_place_location WHERE parent_id IS NULL UNION ALL SELECT y.id, concat(x.hierarchy, ', ', y.place) AS hierarchy, 1 + x.depth FROM wt_place_location AS y join x on y.parent_id = x.id) SELECT * FROM x WHERE depth > 14 ORDER BY depth;

If you are using phpMyAdmin instead of the command line, then replace "order by depth" with "order by depth desc" to show the longest path at the top of the list instead of the bottom.

Greg Roach - greg@subaqua.co.uk - @fisharebest@phpc.social - fisharebest.webtrees.net
Last edit: 1 month 3 weeks ago by fisharebest.

Please Log in or Create an account to join the conversation.

More
1 month 3 weeks ago #13 by RickM
Replied by RickM on topic Error after deleting Unused places
Greg

That did the trick and OMG 24 Levels Deep.. 1 Really screwed up place..

I will be honest and say that it may be the random logging out that might have contributed to this problem.
One gets logged out in the middle of editing and all.. We wait patiently for the update and def no pressure.. If the error comes up again we have a solution for now..

Many Many Thanks..

Rick M
New Zealand - Nu Tirani
alineofmalkins.com/wt_malkins
Webtrees v2.1.19

Please Log in or Create an account to join the conversation.

Powered by Kunena Forum
}