Blog functions don't return any content

Hi Drew,

Below is the debug output:

Debug Message - Perch 3.1.2
[14] SELECT DISTINCT settingID, settingValue FROM perch3_settings WHERE userID=0
[1] SELECT * FROM perch3_pages WHERE pagePath='/blog/index.php' LIMIT 1
Using template: /templates/pages/attributes/default.html
Using sub-template: /templates/pages/attributes/seo.html
[4] SELECT * FROM perch3_pages WHERE pageNew=0 AND pageHidden=0 AND pageDepth >=0 AND pageDepth<=1 ORDER BY pageTreePosition ASC
[1] SELECT pageTreePosition FROM perch3_pages WHERE pagePath='/blog/index.php' LIMIT 1
[1] SELECT pageID FROM perch3_pages WHERE pageTreePosition IN ('000-004', '000') ORDER BY pageTreePosition DESC
[4] Using template: /templates/navigation/item.html
[nil] SELECT SQL_CALC_FOUND_ROWS DISTINCT tbl.* FROM ( SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM perch3_blog_index idx JOIN perch3_blog_posts main ON idx.itemID=main.postID AND idx.itemKey='postID' JOIN perch3_blog_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='postID' AND idx2.indexKey='postDateTime' WHERE 1=1 AND idx.itemID=idx2.itemID AND idx.itemKey=idx2.itemKey GROUP BY idx.itemID, idx2.indexValue, postID ) as tbl WHERE (postStatus='Published' AND postDateTime<='2018-09-24 08:09:00' ) GROUP BY itemID, sortval ORDER BY sortval DESC LIMIT 0, 10
[1] SELECT FOUND_ROWS() AS `count`
Using template: /templates/blog/post_in_list.html

Thanks.

Is it possible that somehow paths are not recognised on my Perch installation on the server?

Hi Drew,

Thanks for replying.

There was one published post. I deleted it and created a new post (published). I still can't see this blog post. However, the following line appears in the post debug message:

Invalid query: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0' for key 'PRIMARY'

I've also noticed I can't add any new post and no error message appears in the debug message.

I was first developing the site locally. It's all working fine with my local database. Then I copied over the files to one hosting provider, created a new database there and imported the local database. I think I only updated db host, user, password and license on the production environment.

The problem started happening then but because I wasn't writing blog posts and was planning to move to a new hosting provider, I didn't look into that issue.

However, I moved the site to a new server over the weekend (also created a new database and moved my content) and the issue came back. I would like to change the profile of the site so it's quite important to make the Blog app work.

It would be great if you could help, Drew. I'm not an SQL expert so I don't really know at what is causing the problem.

Thanks.

It happens when I try to save changes to an existing post (content changes are saved).

Here is the debug message (I've removed content bits so it's not too long):

Debug Message - Perch 3.1.2
[1] SELECT u.*, r.* FROM perch3_users u, perch3_user_roles r WHERE u.roleID=r.roleID AND u.userEnabled=1 AND u.userID=1 AND u.userHash='f1a5e6f66c287b7fed45a6db969ac905' LIMIT 1
UPDATE perch3_users SET userHash='0a91a10c885559aa3e21c03095a231d8' WHERE userID='1'
[14] SELECT settingID, settingValue, userID FROM perch3_settings WHERE userID=1 OR userID=0 ORDER BY userID ASC
[7] SELECT itemValue FROM perch3_menu_items WHERE itemType='app'
[0] SELECT COUNT(*) FROM perch3_blog_comments WHERE commentStatus='PENDING'
[1] SELECT * FROM perch3_blog_authors WHERE authorEmail='MYEMAIL@magdawebdesign.com' LIMIT 1
[1] SELECT * FROM perch3_blog_posts WHERE postID=0
[1] SELECT * FROM perch3_blog_sections ORDER BY sectionTitle ASC
[1] SELECT * FROM perch3_blogs ORDER BY blogTitle ASC
[1] SELECT * FROM perch3_blogs WHERE blogID=1 LIMIT 1
[1] SELECT * FROM perch3_blog_sections WHERE blogID='1' ORDER BY sectionTitle ASC
Using template: /templates/blog/post.html
[1] SELECT * FROM perch3_resources WHERE resourceID='32' LIMIT 1
[1] SELECT * FROM perch3_resources WHERE resourceAWOL=0 AND resourceParentID='32' AND resourceKey='thumb'
[1] SELECT * FROM perch3_resources WHERE resourceAWOL=0 AND resourceParentID='32' AND resourceTargetWidth=50 AND resourceTargetHeight=50 AND resourceCrop=1 AND resourceDensity=1
Logging resources for perch_blog
DELETE FROM perch3_resource_log WHERE appID='perch_blog' AND itemFK='postID' AND itemRowID=0
INSERT IGNORE INTO perch3_resource_log(`appID`, `itemFK`, `itemRowID`, `resourceID`) VALUES('perch_blog','postID',0,32),('perch_blog','postID',0,33),('perch_blog','postID',0,34)
UPDATE perch3_blog_posts SET postStatus='Published', postTemplate='post.html', postDescRaw='CONTENT', postTitle='TITLE', postDateTime='2018-04-05 09:10:00', postDynamicFields='{\"excerpt\":{\"_flang\":\"markdown\",\"raw\":\"EXCERPT",\"processed\":\"EXCERPT"},\"image\":{\"assetID\":\"32\",\"title\":\"IMAGE",\"_default\":\"\\/perch\\/resources\\/IMAGE\",\"bucket\":\"default\",\"path\":\"IMAGE\",\"size\":57682,\"w\":1200,\"h\":786,\"mime\":\"image\\/jpeg\",\"sizes\":{\"thumb\":{\"w\":\"150\",\"h\":\"98\",\"target_w\":150,\"target_h\":150,\"density\":2,\"path\":\"IMAGE\",\"size\":8968,\"mime\":\"image\\/jpeg\",\"assetID\":\"33\"},\"w50h50c1\":{\"w\":\"50\",\"h\":\"50\",\"target_w\":\"50\",\"target_h\":\"50\",\"crop\":\"true\",\"density\":\"1\",\"path\":\"IMAGE",\"size\":1594,\"mime\":\"\",\"assetID\":\"34\"}}}}' WHERE postID='0'
No ids to log.
UPDATE perch3_blog_posts SET postStatus='Published', postTemplate='post.html', postDescRaw='CONTENT', postTitle='TITLE', postDateTime='2018-04-05 09:10:00', postDynamicFields='{\"excerpt\":{\"_flang\":\"markdown\",\"raw\":\"EXCERPT\",\"processed\":\"EXCERPT\"},\"image\":{\"assetID\":\"32\",\"title\":\"IMAGE\",\"_default\":\"\\/perch\\/resources\\/IMAGE\",\"bucket\":\"default\",\"path\":\"IMAGE\",\"size\":57682,\"w\":1200,\"h\":786,\"mime\":\"image\\/jpeg\",\"sizes\":{\"thumb\":{\"w\":\"150\",\"h\":\"98\",\"target_w\":150,\"target_h\":150,\"density\":2,\"path\":\"IMAGE\",\"size\":8968,\"mime\":\"image\\/jpeg\",\"assetID\":\"33\"},\"w50h50c1\":{\"w\":\"50\",\"h\":\"50\",\"target_w\":\"50\",\"target_h\":\"50\",\"crop\":\"true\",\"density\":\"1\",\"path\":\"IMAGE\",\"size\":1594,\"mime\":\"\",\"assetID\":\"34\"}}}}', postSlug='SLUG' WHERE postID='0'
No ids to log.
DELETE FROM perch3_blog_index WHERE itemKey='postID' AND itemID='0'
INSERT INTO perch3_blog_index (itemKey, itemID, indexKey, indexValue) VALUES ('postID',0,'excerpt','EXCERPT'),('postID',0,'image','/IMAGE'),('postID',0,'postID','0'),('postID',0,'blogID','1'),('postID',0,'postTitle','TITLE'),('postID',0,'postSlug','SLUG'),('postID',0,'postDateTime','2018-04-05 09:10:00'),('postID',0,'postDescRaw','DESC'),('postID',0,'postTags',''),('postID',0,'postStatus','Published'),('postID',0,'authorID','1'),('postID',0,'sectionID','1'),('postID',0,'postCommentCount','0'),('postID',0,'postImportID',''),('postID',0,'postLegacyURL',''),('postID',0,'postAllowComments','1'),('postID',0,'postTemplate','post.html'),('postID',0,'postMetaTemplate','post_meta.html'),('postID',0,'postIsPublished','0'),('postID',0,'itemID',''),('postID',0,'itemRowID',''),('postID',0,'postURL','URL'),('postID',0,'postURLFull','URL'),('postID',0,'_id',0)
Invalid query: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0' for key 'PRIMARY'
[1] OPTIMIZE TABLE perch3_blog_index
[nil] SELECT COUNT(*) AS qty, c.catID FROM perch3_blog_index i, perch3_categories c, perch3_blog_posts p WHERE i.indexValue=c.catPath AND i.indexKey='_category' AND i.itemKey='postID' AND i.itemID=p.postID AND p.postStatus='Published' AND p.postDateTime<='2018-09-25 09:49:00' GROUP BY i.indexValue, c.catID
DELETE FROM perch3_category_counts WHERE countType='blog.post'
[1] SELECT authorID, COUNT(*) AS qty FROM perch3_blog_posts WHERE postStatus='Published' AND postDateTime<='2018-09-25 09:49:00' GROUP BY authorID
UPDATE perch3_blog_authors SET authorPostCount=0
UPDATE perch3_blog_authors SET authorPostCount='1' WHERE authorID=1 LIMIT 1
[1] SELECT c.sectionID, COUNT(p.postID) AS qty FROM perch3_blog_sections c, perch3_blog_posts p WHERE c.sectionID=p.sectionID AND p.postStatus='Published' AND p.postDateTime<='2018-09-25 09:49:00' GROUP BY c.sectionID ORDER BY c.sectionTitle ASC
UPDATE perch3_blog_sections SET sectionPostCount=0
UPDATE perch3_blog_sections SET sectionPostCount='1' WHERE sectionID='1' LIMIT 1
Edit mode: edit
[1] SELECT mi.*, p.privKey FROM perch3_menu_items mi LEFT JOIN perch3_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=0 ORDER BY itemOrder ASC LIMIT 0, 1
[2] SELECT mi.*, p.privKey FROM perch3_menu_items mi LEFT JOIN perch3_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=1 ORDER BY mi.itemOrder ASC
[1] SELECT itemTitle FROM perch3_menu_items WHERE itemType='app' AND itemValue='perch_blog' LIMIT 1
[1] SELECT mi.*, p.privKey FROM perch3_menu_items mi LEFT JOIN perch3_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=0 ORDER BY itemOrder ASC LIMIT 1, 10
[2] SELECT mi.*, p.privKey FROM perch3_menu_items mi LEFT JOIN perch3_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=2 ORDER BY mi.itemOrder ASC
File: /addons/apps/perch_blog/modes/edit.post.php
[1] SELECT * FROM perch3_resources WHERE resourceID='32' LIMIT 1
Queries: 44
Memory: 6.4182

Actually no. When I click 'Save', I'm taken to the blank Post page and no new post appears in the list of posts in the admin area.

I've just asked my hosting provider. I'll come back with this shortly.

Hi Drew,

The hosting provider said there weren't any specific errors, just a few about blog image not loading. Is it helpful?

They also said: "As for the SQLSTATE error you received, this seems to be trying to import content to the SQL database to a location where content already exists. Unfortunately, it doesn't say specifically which table this is in either. It might be worth clearing the contents of the database and manually re-uploading."

If I delete database content, will I need to run Perch setup?

Thanks, Magda

I'll do that.

It worked :) I'm so happy! Thanks Drew.

https://mobdro.ooo/

Drew McLellan said:

Which tables are you looking to delete? You could try removing the perch3_blog tables and then just letting the Blog app recreate them.

I also faced same issue but now it solved for me. Thank you for the help.