Jump to content
TNG Community
Sign in to follow this  
bwalton

Census Plus delete Error question

Recommended Posts

bwalton

When I try to delete a transcript I added in error I get the following error

Transcript ID is dbid=6061&iid=4385037_00247 and cpbaseID is 2079 and census year is 1920

An error has occurred in the TNG software. This could be due to a setup issue, an incomplete upgrade or a program bug. If you are the site owner, you may contact TNG support for help with this problem. Please copy the query below and paste it into your message.

Query: SELECT personID, tng_medialinks.eventID FROM tng_medialinks INNER JOIN tng_events ON tng_medialinks.eventID=tng_events.eventID LEFT OUTER JOIN cp_link ON cp_link.cpeventID=tng_medialinks.eventID WHERE cp_link.cpbaseID = '2079' AND tng_medialinks.personID IN (SELECT cp_link.cpersonID FROM cp_link WHERE cp_link.cpersonID=tng_medialinks.personID )

The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

There is no one linked to the file, I am using TNG 11.1.2 and CP 11.0.2.6b

My guess is the database size is to big but how do I get around that ?

Thanks

Bill

 

 

 

Share this post


Link to post
Share on other sites
Ken Roy

Bill,

What screen are you trying to delete the transcript from?   There are a lot of scripts involved with this mod and from your above description, I cannot tell what program I need to look at.

Share this post


Link to post
Share on other sites
bwalton

Thanks Ken;

from the  Delete tab, "Delete Census Plus Transcript Records"

Bill

Share this post


Link to post
Share on other sites
Ken Roy

Bill,

Are you entering anything other than dbid=6061&iid=4385037_00247 in the input field?  

Granted I don't have that transcript on my site, but the query generated is

Quote

Query: SELECT transcriptID, cpbaseID, censusYear FROM cp_base WHERE (transcriptID = 'dbid=6061&iid=4385037_00247') OR (cpbaseID = '')

which is very different than what you are showing. 

Share this post


Link to post
Share on other sites
Ken Roy

OK, I found the query but am not too sure how to fix it.  You must have a big event table.

Share this post


Link to post
Share on other sites
bwalton

 

No nothing extra,

 sc1.jpg

Yes its a big file, I guess not sure what the average would be :)

sc2.jpg

All the Best

Bill

Share this post


Link to post
Share on other sites
Ken Roy

Bill,

Did you ever create the CPI INDEXes as part of a previous upgrade?

You could try clicking the Create CPI INDEXes button in the Mod Manager.  If the indexes were previously created it will fail.  The Indexes were added in some previous version to handle JOINS that cause the error you are seeing.

It could also mean that we need other indexes

Share this post


Link to post
Share on other sites
bwalton

Create Indices for Census Plus International mod tables

ALTER TABLE `cp_data` ADD INDEX `BaseID` ( `cpbaseID` ) ... An error has occurred in the TNG software. This could be due to a setup issue, an incomplete upgrade or a program bug. If you are the site owner, you may contact TNG support for help with this problem. Please copy the query below and paste it into your message.

Query: ALTER TABLE `cp_data` ADD INDEX `BaseID` ( `cpbaseID` )

Duplicate key name 'BaseID'

 

Already done

Bill

Share this post


Link to post
Share on other sites
Ken Roy

Thanks Bill,

I am not a DBA and did not write this particular part of the Census Plus International code, so it may take me a while to figure out how best to solve your issue.

If there are no persons or events linked to this census, you might be able to delete the records from phpMyAdmin using the cpbaseID is 2079 for the search in the following tables:

  • cp_base
  • cp_data
  • cp_link
  • cp_summary
  • cp_veteran

Note that there is likely no entries for the last 3 tables shown above

Share this post


Link to post
Share on other sites
bwalton

Should I go into Edit Census Transcript - Remove Unwanted Rows and delete all the rows 1st?

Could there be some sort of limit being placed on the file size or whatever by my hosting company?

I seem to recall that I use to be able to go into find and get a list of all the pages entered and click through them 1, 2, 3, and so on and it would show me total lines I must have reached a threshold at some point because that no longer works it will find with the base ID but errors out with the transcript even if I put the name in.

An error has occurred in the TNG software. This could be due to a setup issue, an incomplete upgrade or a program bug. If you are the site owner, you may contact TNG support for help with this problem. Please copy the query below and paste it into your message.

Query: SELECT p.surname AS lastname, p.givenname AS firstname, p.birthdate, STR_TO_DATE(p.birthdate,'%Y/%m/%d') AS birthdatetr, p.birthplace, p.relation, p.sex AS gender, p.censusYear, STR_TO_DATE(p.censusYear,'%Y/%m/%d') AS censusyeartr, p.location AS censusplace, p.cplineno, p.cpbaseID, p.cpdataID, p.transcriptID, cp_link.cplinkID, cp_link.cpersonID, cp_link.cpeventID, cp_link.gedcom, cp_link.eventtypeID, cp_base.imageurl,tng_medialinks.eventID,tng_medialinks.mediaID,tng_medialinks.medialinkID, tng_trees.treename, tng_people.personID, COALESCE(p.personID, cp_link.cpersonID, tng_people.personID, 'No personID' ) as personID FROM cp_data AS p LEFT JOIN cp_link ON cp_link.cpdataID = p.cpdataID LEFT JOIN cp_base ON cp_base.cpbaseID = p.cpbaseID LEFT OUTER JOIN tng_trees on tng_trees.gedcom = cp_link.gedcom LEFT OUTER JOIN tng_medialinks ON tng_medialinks.eventID = cp_link.cpeventID LEFT OUTER JOIN tng_people ON tng_people.personID = cp_link.cpersonID AND tng_people.gedcom = cp_link.gedcom GROUP BY p.cpbaseID, cp_link.cpdataID, p.surname, p.givenname, cp_link.cplinkID ORDER BY lastname, firstname, birthdate LIMIT 50

The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

It gives the same last line, this is a find on just the TID

I am going to check I can likely restore from the Feb 5 backup I don't think I added much new in the last week to CPI I may have to reenter a couple of people. That said it might be a good idea to have someone looking at this because it will likely come up again.

Thanks for all the help

All the Best

Bill

 

All the Best

Bill

Share this post


Link to post
Share on other sites
Ken Roy

Bill,

The issue is that you are trying to JOIN too many table columns together.  I fixed a similar problem a couple years ago by adding INDEXes.   I don't think it has anything to do with the number of rows in a census record.  It has to do with trying to find all events in your database that need to be deleted that might be linked to this census. 

If you use phpMyAdmin, and do a search for cpbaseID = 2079 I think you should be able to delete the records that way.

 

Share this post


Link to post
Share on other sites
bwalton

I went in to phpadmin and deleted 2079 and it seems to be gone, the page & people, do you recommend I optimize the cp tables before I add any new pages or would it not be necessary ?

Thanks

Bill

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×