DB Error When Accessing 'SEO' Settings
By Brandon Scivolette on Friday, 08 April 2016
Posted in Technical Issues
Replies 42
Likes 0
Views 848
Votes 0
Good Afternoon,

I'm new to EasyBlog and, so far, everything is working well except one error. When attempting to access the 'SEO' menu (standalone, not under 'General' settings) I receive a DB error. The URL I am trying to access is: /administrator/index.php?option=com_easyblog&view=metas

The error doesn't appear to affect the component or any other configuration.

I've copied the full error message below and also attached a screenshot.

## Error Message ##
1267 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation 'ifnull' SQL=SELECT * FROM ( (SELECT m.id, m.type, m.content_id, m.keywords, m.description, m.indexing, if(m.title != '', ifnull(m.title, p.title), p.title) AS title FROM `#__easyblog_meta` AS m INNER JOIN `#__easyblog_post` AS p ON m.content_id = p.id AND p.`published`!='9' WHERE m.`type` = 'post') UNION (SELECT m.id, m.type, m.content_id, m.keywords, m.description, m.indexing, if(m.title != '', ifnull(m.title, p.title), p.title) AS title FROM `#__easyblog_meta` AS m LEFT JOIN `#__easyblog_team` AS p ON m.content_id = p.id WHERE m.`type` = 'team') UNION (SELECT m.id, m.type, m.content_id, m.keywords, m.description, m.indexing, if(m.title != '', ifnull(m.title, p.name), p.name) AS title FROM `#__easyblog_meta` AS m LEFT JOIN `#__users` AS p ON m.content_id = p.id WHERE m.`type` = 'blogger') UNION (SELECT m.* FROM `#__easyblog_meta` AS m WHERE m.`type` = 'view') UNION (SELECT m.id, m.type, m.content_id, m.keywords, m.description, m.indexing, if(m.title != '', ifnull(m.title, p.title), p.title) AS title FROM `#__easyblog_meta` AS m LEFT JOIN `#__easyblog_category` AS p ON m.content_id = p.id WHERE m.`type` = 'category')) AS x LIMIT 0, 20

NOTE: Post edited to include FTP and domain details for the test server.
Hey Brandon,

Can you please download the attached file and upload it into /administrator/components/com_easyblog/models/
Mark
·
Friday, 08 April 2016 12:58
·
0 Likes
·
0 Votes
·
0 Comments
·
Good Afternoon,

File uploaded as requested. No change in the error, though.
Brandon Scivolette
·
Saturday, 09 April 2016 00:23
·
0 Likes
·
0 Votes
·
0 Comments
·
Hey there,

I am really sorry for the delay of this reply as it is a weekend for us here.

Is it possible for you to provide us with the back end and FTP access to your site to check on this issue?

By the way, in the future you can just include your site's access once at http://stackideas.com/dashboard/site rather than needing to keep adding them in your replies.
Arlex Wong
·
Saturday, 09 April 2016 12:27
·
0 Likes
·
0 Votes
·
0 Comments
·
No problem with the reply, it's the weekend here as well.

I've added the login details to the site URL you mentioned. However, we use IP whitelisting so my guess is that I will need to whitelist your IP before you will be able to access the site. Feel free to try it, but if it doesn't work let me know what IP you are logging in from.
Brandon Scivolette
·
Sunday, 10 April 2016 00:55
·
0 Likes
·
0 Votes
·
0 Comments
·
Hey Brandon,

Arlex and I work at home during the weekend and our ip addresses are different. Our office ip address is different too because it is not on static ip address. Anyway, this is my current ip address, 115.134.196.36

By the way, can you also please provide us with the ftp access as well?
Mark
·
Sunday, 10 April 2016 01:30
·
0 Likes
·
0 Votes
·
0 Comments
·
Hi Mark,

I added your IP to the whitelist. I don't have access to the FTP setup, but I can request it first thing Monday morning. Our site is actually on a Windows server, the same location as our software. So I just need to request FTP configuration from our development team.

In the meantime you should be able to login to the Joomla admin now that the IP is whitelisted.
Brandon Scivolette
·
Sunday, 10 April 2016 01:53
·
0 Likes
·
0 Votes
·
0 Comments
·
Hey Brandon,

I am still hitting errors when trying to access the admin area, http://screencast.com/t/A0dzZbzZ0 . Would it be possible for you to get the ftp access for us as well?
Mark
·
Sunday, 10 April 2016 02:30
·
0 Likes
·
0 Votes
·
0 Comments
·
I didn't forget about this, just waiting on the FTP configuration from my development team. I will get you that info as soon as I have it.
Brandon Scivolette
·
Tuesday, 12 April 2016 02:15
·
0 Likes
·
0 Votes
·
0 Comments
·
Hi Brandon,

Thanks for the heads up. We will wait for your update
Muhammad Fadhli
·
Tuesday, 12 April 2016 10:12
·
0 Likes
·
0 Votes
·
0 Comments
·
Hi Guys,

So I still don't have an FTP account for this particular install, but I DO have FTP for a separate server that I use for friends/family and testing. It just so happens that I installed a full backup of the site that has this issue on this test server yesterday. I confirmed that the same issue exists here as well. Also, the main site with this problem is a live site, so it makes the most sense to resolve this on the test server. Whatever the solution is I can implement directly to the live site.

Can you message me directly so I can provide FTP info for the test server?
Brandon Scivolette
·
Friday, 15 April 2016 23:33
·
0 Likes
·
0 Votes
·
0 Comments
·
Hey Brandon,

Is it possible for you to edit your first post above to include the site access for the back up site?
Mark
·
Friday, 15 April 2016 23:57
·
0 Likes
·
0 Votes
·
0 Comments
·
Done.
Brandon Scivolette
·
Saturday, 16 April 2016 00:06
·
0 Likes
·
0 Votes
·
0 Comments
·
This is all I see when I login to the ftp server, http://screencast.com/t/eqsLJ1Jyeuz
Mark
·
Saturday, 16 April 2016 00:24
·
0 Likes
·
0 Votes
·
0 Comments
·
I think I fixed it, sorry about that. I had set the FTP path to tight. Try again with the same credentials.
Brandon Scivolette
·
Saturday, 16 April 2016 00:29
·
0 Likes
·
0 Votes
·
0 Comments
·
Thanks! It works now! By the way, can I have the phpmyadmin please?
Mark
·
Saturday, 16 April 2016 00:49
·
0 Likes
·
0 Votes
·
0 Comments
·
Credentials added to the original post.
Brandon Scivolette
·
Saturday, 16 April 2016 00:57
·
0 Likes
·
0 Votes
·
0 Comments
·
Updated original post again with cpanel credentials.
Brandon Scivolette
·
Saturday, 16 April 2016 01:28
·
0 Likes
·
0 Votes
·
0 Comments
·
Hey there,

I am really sorry for the delay of this reply as it is a weekend for us here.

I've checked in your backend, it seems your current Joomla notified your database is not updated, you can refer on my attached screenshot below.

Then I click "FIX" button, I believe all Joomla table collation changed from `utf8_unicode_ci` to utf8mb4_unicode_ci.

Now hitting this error from the SEO page :
1253 COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4' SQL=SELECT * FROM ( (SELECT m.id, m.type, m.content_id, m.keywords, m.description, m.indexing, if(m.title != '', ifnull(m.title, p.title), p.title) AS title FROM `#__easyblog_meta` AS m INNER JOIN `#__easyblog_post` AS p ON m.content_id = p.id AND p.`published`!='9' WHERE m.`type` = 'post') UNION (SELECT m.id, m.type, m.content_id, m.keywords, m.description, m.indexing, if(m.title != '', ifnull(m.title, p.title), p.title) AS title FROM `#__easyblog_meta` AS m LEFT JOIN `#__easyblog_team` AS p ON m.content_id = p.id WHERE m.`type` = 'team') UNION (SELECT m.id, m.type, m.content_id, m.keywords, m.description, m.indexing, if(m.title != '', ifnull(m.title, p.name), p.name) COLLATE utf8_general_ci AS title FROM `#__easyblog_meta` AS m LEFT JOIN `#__users` AS p ON m.content_id = p.id WHERE m.`type` = 'blogger') UNION (SELECT m.id, m.type, m.content_id, m.keywords, m.description, m.indexing, m.title FROM `#__easyblog_meta` AS m WHERE m.`type` = 'view') UNION (SELECT m.id, m.type, m.content_id, m.keywords, m.description, m.indexing, if(m.title != '', ifnull(m.title, p.title), p.title) AS title FROM `#__easyblog_meta` AS m LEFT JOIN `#__easyblog_category` AS p ON m.content_id = p.id WHERE m.`type` = 'category')) AS x LIMIT 0, 20


Can you consult with your webhosting provider regarding this is it have hitting this error '1253 COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4'' before?

Try run this following sql check the VARIABLE sql :


SHOW VARIABLES LIKE  '%char%'


Can you update `latin1` value to `utf8` then restart your SQL server and see how it goes?


character_set_database - utf8

character_set_server - utf8
Arlex Wong
·
Saturday, 16 April 2016 13:16
·
0 Likes
·
0 Votes
·
0 Comments
·
Hi Arlex,

I will take a look at the queries/fixes you've proposed and see what I can learn. As for the DB error, I was aware of this already as it occurred when upgrading Joomla 3.5 to 3.5.1 (and then 3.5.2). My research found that it is a bogus error and the consensus among Joomla dev folks was to ignore it.
Brandon Scivolette
·
Tuesday, 19 April 2016 01:27
·
0 Likes
·
0 Votes
·
0 Comments
·
Hey Brandon,

Sure, let us know how this goes.
Mark
·
Tuesday, 19 April 2016 01:32
·
0 Likes
·
0 Votes
·
0 Comments
·
Looks like I figured it out. Since I have this install on a test server I decided to utilize tools that I otherwise wouldn't use on a live server. Notably, the features provided by Admin Tools. I first tried using their "Change Database Collation" tool. They recommend 'UTF-8 (multibyte)' so I tried that first, which didn't resolve anything. I then tried changing to 'UTF-8 (Joomla default)' and the SEO tools began to work properly.

I haven't fully tested the entire site to make sure nothing broke during this process, but, so far so good.
Brandon Scivolette
·
Tuesday, 19 April 2016 05:10
·
0 Likes
·
0 Votes
·
0 Comments
·
Thanks for getting back to us your issue resolved.

By the way, keep us update if you still hitting some similar sql error from Easyblog.
Arlex Wong
·
Tuesday, 19 April 2016 11:26
·
0 Likes
·
0 Votes
·
0 Comments
·
So, unfortunately, the fix I mentioned previously on my test server did NOT work on my production server. The database shows as 'up-to-date', the DB collation was changed (as described above) yet the database error 1253 persists.

Further, when I tried to migrate Joomla articles the process was successful for 10 posts but then hung. I was able to successfully migrate all Joomla articles on my test server with the exact same site, so I'm not sure what the issue is.
Brandon Scivolette
·
Friday, 22 April 2016 00:24
·
0 Likes
·
0 Votes
·
0 Comments
·
Try download my attached few files and replace into these few file in your site and see how it goes?

JoomlaFolder\administrator\components\com_easyblog\includes\easyblog.php
JoomlaFolder\administrator\components\com_easyblog\includes\notification\notification.php
JoomlaFolder\administrator\components\com_easyblog\models\metas.php
Arlex Wong
·
Friday, 22 April 2016 00:39
·
0 Likes
·
0 Votes
·
0 Comments
·
I haven't attempted the article migration yet, but the SEO page is now working. Well done
Brandon Scivolette
·
Friday, 22 April 2016 01:27
·
0 Likes
·
0 Votes
·
0 Comments
·
Regarding article migration, the issue persists. Nothing is happening now. I loaded the console and I am seeing this 500 error (attached)
Brandon Scivolette
·
Friday, 22 April 2016 02:25
·
0 Likes
·
0 Votes
·
0 Comments
·
I am sorry for the delay of this reply,

Can you temporary backup all of your data from your database because I would like to try replicate this error from your site?

By the way, it seems like your current template already override the php error message to "Unable to load rendered class", so it can't show the exact what php error you hitting.

Also When I tried to access in your Joomla backend, I hitting following error :
403 - Forbidden: Access is denied.

You do not have permission to view this directory or page using the credentials that you supplied.


This is my current IP address : 175.139.130.20
Arlex Wong
·
Friday, 22 April 2016 11:55
·
0 Likes
·
0 Votes
·
0 Comments
·
Alright, I finally got the firewall issue resolved. Can you send me a current IP so I can whitelist? I see the IP for Arlex, but that was from 4/22, not sure if it's still current.
Brandon Scivolette
·
Thursday, 28 April 2016 02:54
·
0 Likes
·
0 Votes
·
0 Comments
·
Thanks for getting back to us Brandon

This is my current IP address : 175.139.129.120 , keep us update one you have done
Arlex Wong
·
Thursday, 28 April 2016 10:19
·
0 Likes
·
0 Votes
·
0 Comments
·
Hi Arlex,

I have whitelisted your IP across all firewalls, so you should be good for admin access. Here is the current situation:

My post originally began with the DB error for the 'SEO' page. However, on 4/22 you provided me with three files to overwrite the originals. I did that and the fix worked, the SEO page is accessible. During all of this I found another issue, which is the Joomla article migration tool. Per my posts on 4/22 the process stalls without any visible errors. On 4/22 I provided a screenshot of the 500 error I saw on my Chrome console which I presume is related to the issue with article migration.

So, as we sit today, my issue is just that I am unable to successfully migrate the articles I need into EB. I've attached a screenshot of the selections I made for migration, which represents the only category of articles that I need to migrate.

Also, I want to make it clear that this is a production site with significant visitor traffic. My experience with the migrator suggests that it would be unlikely anything could affect the front end or visitors, but I did want to make that clear. I did run a backup just a few moments ago in case any issues are encountered.
Brandon Scivolette
·
Friday, 29 April 2016 01:59
·
0 Likes
·
0 Votes
·
0 Comments
·
Hey Brandon,

I am sorry for the delay of this reply,

It seems like my current IP address already changed to 219.92.242.212 , hm may i know is it possible to you get back to me before 7pm Malaysia time? So that we can check this issue before we leave office.

Because my IP address is dynamic one, once I leave our office, that IP address will get changed.

Looking forward of your response.
Arlex Wong
·
Friday, 29 April 2016 13:41
·
0 Likes
·
0 Votes
·
0 Comments
·
Good Morning,

Unfortunately you are a full 12 hours ahead of us in the Eastern US. Sorry I couldn't respond sooner.
Brandon Scivolette
·
Friday, 29 April 2016 22:26
·
0 Likes
·
0 Votes
·
0 Comments
·
Hey Brandon,

I am really sorry for the delay of this reply as it is a weekend for us here. Can you please update to 5.0.35 and see if you are still having issues with this?

I am currently at home right now and this is my ip address, 210.186.221.147

It would be greatly appreciated if you could temporarily disable this restriction and once we get this sorted out, re-enable the restrictions again
Mark
·
Sunday, 01 May 2016 16:21
·
0 Likes
·
0 Votes
·
0 Comments
·
I would love to disable the firewall but, unfortunately, I don't have control over it. Our software is also on this server and the development team keeps it locked down. I will add the IP you provided now, but I know it likely will change when you log in at work in the morning. You are 12 hours ahead, so it should be 3am there now. I will likely be available as late as 10pm ET today, which would be 10am your time tomorrow. If you can message me when you get in with your current IP I can add it before I go to bed.

Sorry about all this.
Brandon Scivolette
·
Tuesday, 03 May 2016 03:00
·
0 Likes
·
0 Votes
·
0 Comments
·
Hey Brandon,

This is my current IP address : 219.92.242.212 , keep us update once you have done.
Arlex Wong
·
Tuesday, 03 May 2016 11:08
·
0 Likes
·
0 Votes
·
0 Comments
·
Sorry Arlex, I didn't get your response until after 11pm last night. Seems like we're going to have a problem with this method considering the large gap in time zone and your dynamic IP.

Do you have any thoughts or suggestions for how to resolve the issue without accessing the backend? Knowing what the error is that is likely causing the issue does anything come to mind for possible causes? I will also say that I have found, when I try to test the migrator, only one article migrates successfully. Oddly, it's not the newest or oldest, so I'm not sure why that one article is successful.

Anyway, I'll try any solutions or run any tests you can think of.
Brandon Scivolette
·
Wednesday, 04 May 2016 02:58
·
0 Likes
·
0 Votes
·
0 Comments
·
hey Brandon,

Hm, when he generated 500 error is actually somewhere override that real php error when you trying to run the migration, so we unable to give any suggestion to check on this issue in currently.

Try follow my instruction below and see is it can temporary disable that error override feature first?
1. Try switch your frontend and backend template to use back Joomla default template.
2. Enable error reporting to maximum from backend > system > global configuration
3. Try run the migration again and see is it got any sql error show out.

If no, you have to try login to your FTP server, temporary rename your plugin folder to 'plugins_bak' from your Joomla root folder.
4. After you renamed, try refresh the page from backend migrator page then run again and see is it got any error show out.

At the same time, can you try if you can unblock my current IP address 175.139.130.27 ?
Arlex Wong
·
Wednesday, 04 May 2016 10:29
·
0 Likes
·
0 Votes
·
0 Comments
·
Hi Arlex,

I just happened to be up this late, so I was able to whitelist the IP you just provided. You're welcome to try the backend with my saved site credentials.

One thing though, In your previous post you mentioned changing the front end template to the Joomla default. That's something I definitely don't want to do since it's a high traffic site. It's getting later here in the US but we still can have considerable traffic. I don't mind if you change the back end template, just please not the front end.
Brandon Scivolette
·
Wednesday, 04 May 2016 11:25
·
0 Likes
·
0 Votes
·
0 Comments
·
That was great !! I can access your backend now, I will investigate now regarding this issue.
Arlex Wong
·
Wednesday, 04 May 2016 11:31
·
0 Likes
·
0 Votes
·
0 Comments
·
Hm, I have a little lost here since this thread also goes so long, may i know this issue is it only happen on this site `movinglabor`? because I tested this site `floridagrandma`, it seems work fine.

If that is the thing, I've tried to find where Joomla root folder under this site `movinglabor` through FTP server, but it seems like you only provided that FTP access for this site `floridagrandma` only.

If you are still available, can you provide us with this site `movinglabor` FTP access so we can troubleshoot on this?
Arlex Wong
·
Wednesday, 04 May 2016 12:07
·
0 Likes
·
0 Votes
·
0 Comments
·
Hey,

Yeah, this thread has gotten very long. The domain 'floridagrandma.com' is just something I use for testing. It's on a completely separate Linux server from the production domain. I know that the migration was successful on the Linux server.

Unfortunately I don't have FTP access for the production site. It is on a Windows server and locked down tightly because we have our software there as well.

Anyway, I am going to review your suggestions for troubleshooting and see what I can find out on my own. Worst case I will manually copy over the Joomla articles to EB. It will be a hassle, but at least I can get it done and move on to using EB in production.

Thanks for your time and help.
Brandon Scivolette
·
Thursday, 05 May 2016 02:32
·
0 Likes
·
0 Votes
·
0 Comments
·
Hey Brandon,

Keep us updated the result.

I would like recommended you start a new thread regarding this result, because if I am not around, our other moderator still can follow up this issue.
Arlex Wong
·
Thursday, 05 May 2016 10:39
·
0 Likes
·
0 Votes
·
0 Comments
·
View Full Post