Unknown Collation Error Migrating Newer MySQL DB to Older MySQL DB

Today I will explain how I was able to fix the Unknown collation error when importing a MySQL database.

I was moving a completed WordPress site this weekend from my hosting account to a client’s hosting account and was surprised to receive an error upon importing the database into the new empty database.

The error was:

Unknown collation: ‘utf8mb4_unicode_520_ci’

Upon Googling it, I discovered that this is a typical error when importing a database from a newer version of MySQL to an older version of MySQL.

The solution was pretty simple. Export the database in a format that is compatible to both, which turned out to be MYSQL40.  I also chose that format when importing the database. Worked fine and I was back on track again.

Changing WordPress Max File Upload Size

I was Googling around, looking for an easier way to change the max file upload size when uploading files to the WordPress Media library and ran across this post on Brian’s Tips and Tricks for Web Developers website.

You can easily change PHP values by going to the Select PHP Options page under Select PHP version in the Software section of cPanel. When you’re on the Select PHP version page, choose Switch to PHP Options at the top right.

Click on the value, make a selection, Apply and Save.

Troubleshooting Reasons for an Unusually Large WordPress Database

My client informed me that when she viewed her website, it was displaying the “Error Establishing Database Connection” message.

When I went to wp-admin, it was displaying another similar message about the database. I logged into the Control Panel and was able to view the database in PHPMYADMIN. I checked to make sure the database name, username, and password matched. Called GoDaddy and found out that the database was over the limit allowed by the hosting plan and so the database user was disabled. We had one week to resolve the issue either by reducing the size of the large WordPress database or moving to a hosting plan that had unlimited database space.

I  browsed the database using PHPMyAdmin, and eventually found that there were over 1 million records in wp_posts! Next, I asked the client to move to a higher level hosting plan and got the site back online. There were only about 1,000 actual posts when viewing Posts from the Dashboard. I continued to browse wp_posts and finally something caught my eye. There were lots of posts of Post-type Http, and a field contained ‘core_control_http_log_item’. I Googled the character string and came up with one match! Googled translated the page from French and I found the answer. It was the Core Control plugin, which had been active for several months and had been logging activity in the database. I had been using it to debug Paypal connection problems and had not disabled it.

Next, I had to remove all posts of post-type http. I used the query:

DELETE a,b,c FROM wp_posts a LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id) WHERE a.post_type = ‘http’.

I hope this may help someone else who discovers that they have a large WordPress database that shouldn’t be so excessive in size and need to find the reason why.

How to Troubleshoot Contact Form 7 Configuration Error

Process of Elimination for Contact Form 7 Configuration Error

I use the Contact Form 7 plugin on almost all of the WordPress sites I create. Recently, I was creating a very long form, saving frequently, and then noticed there was a configuration error: “Multiple form controls are in a single label element. ”

I looked through the code and nothing jumped out at me that could have been wrong.

So I thought, how am I going to find the error? There were almost 50 input fields!

Answer: Process of elimination

I opened up Notepad, copied the entire form, and pasted it into Notepad.

Next I went back to the form, and deleted all but the top several fields. Saved it. No error.

This told me that the error was somewhere in the remaining part of the form.

I copied the next few fields from Notepad and pasted it at the bottom of the form.

Saved again. The error showed up! So now I knew the error was in the fields I had just pasted.

So, I used this process to isolate the part of the form with the error. It saved a lot of time that I would have spent going over the code repeatedly, looking for some problem.

I hope this tip will save you time. It can be applied in situations other than just html forms, too.

Turned out the error was a misplacement of the closing ‘label’ tag.

Troubleshooting Outlook Express Errors 0x800CCC0D and 0x008CCC0E

Every once in a while, Outlook has an error such as “Can’t find host – cannot Locate Server” or “Failed to Connect – Cannot Connect to Server”, error codes 0x800CCC0D and 0x008CCC0E. I found the notes below and thought posting it would be a great way to store the info in case I need it in the future.

I found this very helpful link for troubleshooting those errors and other Outlook errors such as Client Response Invalid.

Some other tips:

  • If outgoing port 25 is being blocked, try setting the port to 587.
  • To clear cache and flush DNS, go to DOS command prompt and enter ‘ipconfig/flushdns’
  • Try Control Panel > Network Connections > Local Area Connections. Right-click and choose Repair

Starting Outlook in SafeMode – Tools > Options > Other > Advanced – Add-in manager, clear checkboxes; restart Outlook


By |April 30th, 2017|DNS, email, outlook, WordPress|Comments Off on Troubleshooting Outlook Express Errors 0x800CCC0D and 0x008CCC0E

Not Receiving Emails from WordPress Contact Form

This happens frequently – you install Contact Form 7 or Fast Secure Contact Form plugin on your WordPress site and you’re not receiving emails sent through it — they just never arrive in the Inbox. Sometimes this can be due to the settings in the form, such as From: [your-name] <[your-email]>. Contact Form 7 has some good documentation on troubleshooting email delivery here.

Sometimes it can be due to an incorrect setting on the host. The cPanel MX Entry is set to Local Mail Exchanger by default but should be set to Remote Mail Exchanger. This means that if a form in your website uses email@yourdomain.com and it’s the same as your website, the web server will attempt to handle the email itself (because it also hosts your site). To solve the problem, change the setting in cPanel to Remote Mail Exchanger which will tell the server to use the MX records to route the email. This problem can occur with any host that uses cPanel including GoDaddy, HostGator, etc. In GoDaddy, this setting can be found under Hosting > Manage > Email > MX Entry. You may also need to add an MX record with priority 10 and remove, if any, an MX record with priority 0.

This happened again recently with a site hosted at GoDaddy but the email was hosted by Google GSuite. The five Google MX records were in place but no email was being received through the forms on the website. Setting under Control Panel is found under EMail then MX Entry and check off Remote Mail Exchanger instead of Local Mail Exchanger.

Allowed Memory Size Exhausted

I started getting the following errors on the frontend of a WordPress site:

Fatal error: Allowed memory size of 67108864 bytes exhausted (tried to allocate 12288 bytes) in /home/aginginoudoun/public_html/wp-admin/includes/theme.php on line 523
Fatal error: Allowed memory size of 67108864 bytes exhausted (tried to allocate 3072 bytes) in /home/aginginoudoun/public_html/wp-content/plugins/sabai/lib/Sabai/Addon/System/Model/Base/AddonGateway.php on line 81

A Google search indicated that the software was reaching a PHP memory limit, so I ran a phpinfo.php and saw that both local and master memory was 64M. Further reading advised creating a .user.ini file, put the following line in it: memory_limit = 256M, and place the file in the public_html folder. So I did this, and ran phpinfo again and saw that local memory was now 256M and master was still 64M. Supposedly the local overrides the master value.

Tested the website again, and no more fatal errors!

P.S. Don’t forget to remove your phpinfo.php file from the site.

Moving WordPress Development Site to ‘Live’ Site

I just recently moved a WordPress site I’ve been developing using a different domain name to its final destination as a ‘live’ site. I’ve done this several times before but this time I wanted to document the process for future reference.

The development site is sdspreview.com. The current site is named 3dogfarm.com and is an HTML site. The two sites are both on GoDaddy but under different accounts.

Here is the process I used:

On Development Site –

  • Bring up to date all plugins, etc.
  • Install the Go Live Update urls plugin
  • Go to Settings > general and change wordpress address and site address to 3dogfarm.com

Log in to hosting for sdspreview.com
Go to root directory for the site, select all files and Archive into zip file.

Download zip file to my PC

Go to PHPMYADMIN and export database to PC.

Go to hosting where WordPress site will be. Install WordPress in root folder.

Locate WordPress database that was just created. Import the database that was exported to PC.

Upload zip file to root folder.

Extract zipped file to root folder.

Go to Databases and find out the database name, username, password, and MySQL host name.

Go to root and edit wp-config.php – update the database name, username, password, and MySQL host name.

Log into the site – reset password if you’ve forgotten it.

Run the Go Live Update URLs plugin to change any lingering occurrences of the old url. Sometimes this plugin recommends against updating the tables for certain plugins. When that happens, I usually do skip those tables and then do a search and replace in PHPMyAdmin using the queries in this article: https://www.hostinger.com/tutorials/wordpress/how-to-change-wordpress-urls-in-mysql-database-using-phpmyadmin#gref

Review any widgets to ensure no old urls are there.

Enter Google Analytics code and set search engines to allow indexing.

Sometimes I also have to go to Permalinks and just click Update to fix any old URLs.



Migrating Membership Site from Drupal to WordPress PMPro

At first glance I thought this project would be straightforward. Just move the membership data from one database to another.

As I dug in deeper, I realized it would be quite challenging.

This post will document the process.

First, I analyzed the Drupal database to get an understanding of the user registration process and fields collected and stored. A second database was being maintained on the owner’s PC. The Drupal database contained the user’s registration data, but the second database was being used to manage annual payment and additional information about the user. The intent was to eventually combine the two so everything could be handled in one system.

After careful analysis, it was determined that the local (second) database had the most current information such as the user’s address plus it had all of the payment and registration information, which the Drupal database didn’t have.

So, now the project became a transfer of data from a MicroSoft Access database to a WordPress database. The idea of merging the Access database and Drupal database was considered, however, the only field common between the two was the email address. Even first name and last name differed for some users between the two databases. Additionally, there were no fields of any importance stored for the user in Drupal that were not in Access.

I chose Paid Memberships Pro because I had had some limited experience with it on another project. With the other project, it seems like a good plugin and seemed to have good support. I opted for the paid support since I needed access to the Support Forum and code examples. I could tell that there would be custom programming and that I would need the extra support.

I created a WordPress installation, added the PMPro plugin, and got to work. The first step was to determine the membership levels. There were going to be two main levels that users could choose from, and three levels that would be hidden from users, and used only by admins. For example, there is a Member-for-Life level that is free and can only be assigned by an admin. Once members achieve the requirements of that level, the admin changes their level.

The existing system for membership involved payment by check, and in a few rare cases, members paid by Paypal, but it was a manual process as far as keeping track of who paid and how much they paid.

The plan for the new system is to start out offering only payment by check, and then soon after, add online payment via Paypal.

There are two plugins for paying by check:

Pay by Check Addon – can set for each membership level whether that level accepts payment by check only, credit card only, or both; it marks the order as “pending” and users won’t have access until an admin changes the order status to “success”. However, they are still considered “members” in the system, and so they show up in the members list and some custom code, themes, and other addons may give them “access” to things since they are a member.

Check Payment Level – requires adding two versions of each membership level, ex:

  • Regular Member – pay by check
  • Regular Member – pay via Paypal

So, I chose the Pay by Check Addon since it didn’t require duplicate levels. I installed the plugin and completed a few additional setup steps for installation.

Next, looking at the Access database, I saw there were many user fields that we planned to keep besides the usual ones that WordPress stores for each user, such as Spouse’s Name, Type of Business, etc. I found out that in order to add these fields to the database, I would need to use an addon called Register Helper. There was mention in the forums of using other tools to add the fields, but by using Register Helper, there are benefits that the other tools didn’t provide.

I learned that I would need to specify for each field whether it would appear:

  • in the user’s profile
  • on the checkout page, meaning it would be populated as the user registers and pays

Additionally, I can specify whether the field is for admins only to view and edit, or for the user to view in their profile and edit. May need to add a code snippet in order to display the admin-only fields in the admin panel. Also need to add code to display the additional fields in the Member Listing.

Other aspects that I can set for each field include: read-only, field label, hint, and required. If you want the field to be included in an Export to CSV file, then be sure to specify that for each field (which I did, memberslistcsv=true). I also read about an add-on that makes life easier when adding members manually – Add Member Admin Addon.  The admin can create the user, fill in the membership settings, and complete the order in one step. If you want the fields you’re adding to be accessible to this addon, then specify that for each field (addmember=true).

To add the users to the database, I needed to create a CSV file containing the user data. Two plugins are used to import the user data using the CSV file: Import Users from CSV, and Paid Memberships Pro – Import Users from CSV Add On. There is a template to get you started that lists all of the fields required. The import process will assign a membership level to each user, and will maintain any existing subscription information for each user’s membership. For example, you can fill in the amount that their subscription costs, when their membership started, and whether they are an active member or not. There are also a set of fields containing billing information such as billing address.

Next I created a cheat sheet listing all the fields for the new database. I had to cross-reference the field names from the example CSV worksheet with the Access database. I realized I would need to do some data manipulation in order to get the old fields into the new fields, for example:

  • user_login, which is the username that the member logs in with – this data did not exist in the Access database, so I created a formula that concatenates three fields – first name, middle initial, last name into a single field – username
  • membership_id, which is a number that correlates to the membership level, so for example, a ‘Regular’ member, designated in the Access database as ‘Regular’ would have a membership_id of 1. A find/replace was all that was needed to change the word to a number.
  • membership_startdate, which is ‘Date Joined’ in the Access database, needed to be converted from YYYY/MM/DD to YYYY-MM-DD, which is the format that the CSV import requires; some dates contained just the year, so they were converted also to YYYY-01-01.

So, thinking ahead, the next steps would be:

  • Add the Register Helper code to the site so that new fields would be created
  • Get data for several users from the Access database (in Excel format)
  • Perform the data manipulations and change the field names to create the CSV for import.
  • Import the users.
  • Test.

So, I submitted my Register Helper code to the experts at PMPro to look over. I’m still wondering about last_name and first_name. They are WordPress core fields, but from what I read on the forums, if you add them via Register Helper, they will not become one with the core fields, they will be their own separate fields. So, you will end up with two copies of first name and last name. And it sounds difficult if not impossible to keep them synched, for example, if the user edits the first name field, it won’t update the other first name field. Same for the billing fields, called pmpro_bfirstname and pmpro_blastname. My decision for now is to go ahead and add first_name and last_name.

Through the support forum, I also learned the answer to one of my other questions which was: what if you use Register Helper to add fields, then find out you want to remove or add fields, or change the field type? Do you have to somehow remove the fields you decided you don’t want? Turns out that the definitions are read and compiled each time the page is loaded. So if you change a text field to a textarea field, the data in that field will stay, but if you change a textarea (300-character) field to a text field (40 characters), then any characters beyond the 40 will be lost.

Yet another question I had was about “profile”=>”admins” in the PMProRH_Field array. I had seen in the forums documentation stating “profile”=>”admins_only”, “profile”=>”admins”, and “profile”=>”admin”, so I wondered which one, or if all usages were correct. It turns out that as long as ‘admin’ is part of the string, it will work.

So, I posted to the forum and got one of the experts to review my pmpro-customizations.php file and got some good tips. I have two sections of code in my file. The first section creates the fields that are for admins only and go into the user’s profile. The second section are all the fields that are collected during checkout and are shown in the user profile and editable by the user. Each section requires a call to pmprorh_add_registration_field, however, the $fields array needs to be reset inbetween.

I uploaded my pmpro-customizations.php file to a folder I created called pmpro-customizations and activated the pluging. Error! It appears that the Register Helper Example plugin must be de-activated or it will clash with my customizations plugin since they both call my_pmprorh_init. So, I de-activated the Register Helper Example plugin, activated PMPro Customizations and it worked!

Next I checked my user profile (I’m the only user so far) and I see all of the new fields.

I did an Export to CSV but none of the new fields were there. I’ll come back to that later.

I have finished adding all of the membership_ columns to the spreadsheet of member data and I will export the first six rows of user data into a MSDOS CSV file. I decided to set the membership enddate to 12/31/2016, and I may just need to manually adjust the start dates. I don’t have anything for the membership_subscription_transaction_id column, which is supposed to be required in order to continue existing subscriptions. I changed the actual email addresses to fake ones just in case, to be sure those users wouldn’t get an email notification.

I added the bank_deposit field to my customizations field and FTP’d it over to the plugin directory. Do I need to deactivate and re-activate the plugin? So that’s what I tried, and sure enough, bank_deposit now appears in the user profile.

Make sure to remove any commas that are in the cells or else it will confuse the comma-separated fields.

I ran the import just by going to Users > Import from CSV. I got an error message that one user was skipped because the email address was the same as an existing email address, which happened to be my user record. I checked the user profiles and the data was all there. Membership Level, however, was ‘none’. I checked in phpmyadmin, and saw that the membership level looked correct there. Quick check of the CSV file and I found the culprit – an extra space at the end of the column label ‘membership_id’. Removed that, re-imported, and then the Membership Levels were filled in.

Next I checked Memberships > Member List and saw that the column ‘Joined’ was today’s date for every user I imported. After some reading on the forum, I found out that Joined is actually the date that the user registered on the website, which may or may not be the same date that they became a member.

Now I need to figure out how to add links that logged in users see to access their profile and billing info. Theme My Login seems to be the one recommended for this.

Worked on the Member Directory. Just took existing Directory page that was there and expanded the shortcode to include more fields. It does list a Pending member who paid by check but check hasn’t been received. Found some code on the forum but it didn’t work. This was eventually resolved. Jason patched the code I had in my customizations file since the member directory add-on for some reason that did have the correction hadn’t been published yet. He said I could safely update to the next version of the member directory add-on when it came out.

Next is the Business Directory, which should list only those members who indicated they wanted to be listed there. The field is called business_roster and will contain ‘yes’ if they want to be shown in the directory. After posting on the forum, I received code that I placed in /themes/affna/paid-memberships-pro/pmpro-member-directory/directory.php. It was placed under the themes folder so it won’t get overwritten when the pmpro software updates.

As far as preventing Pending members from accessing member-only content, I found some code on the support forum that creates a shortcode that works like the [membership] shortcode, but it additionally excludes any pending members.

I need to figure out how to put links in the sidebar for members only. I can do it using the shortcode mentioned above, and then put in the links in HTML, but that won’t work for the section of links that the administrative person will be maintaining. She won’t want to be copying/pasting HTML code into a small window. I need a way for her to build a list of links using a Custom Menu, but how do I keep that custom menu from being displayed to non-members? I tried using the Nav Menus Add-on, which worked great to build a member-only and non-member main navigation menu, but didn’t work in the sidebar. I ended up using the widget-logic plugin. Just added is_user_logged_in() to the widget. UPDATE: Had to change the widget sidebar custom menu because widget logic was not keeping the pending members out. Also pending members could see the Members main nav menu and could see the post topics but not the post content. So, ended up creating two new levels – Apply for Regular and Apply for Associate. These levels had a setting excluding them from nav content.

So I want to collect data that the user enters in upon checkout, but is not saved in the database. These fields are only used to determine whether to allow the person to have an account on the site. The fields will need to be collected and put into the registration email. It seems that in order to do this, I have to modify the checkout template and then also the email template. I just had a look at the checkout template and am thinking I don’t want to edit this. I saw a software update announcement that said to make sure if you’re using your own checkout template, to make sure it still works after applying the update. I just want to make this an easy-to-maintain site and don’t want to have to re-examine custom templates whenever there’s an upgrade. Since it’s only 5 or so fields, I think I will just store them in the database. So I added those fields to the customization plugin and found out that the checkout email that goes to the admin automatically has the user profile fields included! Except — it did not include the additional 5 or so fields because I had them as admin-only, so I changed them to user-profile=true, and now they are in the email. I don’t have to create and edit the email template. BTW, there is an add-on for email templates that lets you edit portions of the email. I was able to correct some text that is in the checkout email to the user.

I noticed that the user profile doesn’t show the billing fields (billing address, etc.). Found a code recipe that will show them in the user profile and so I added that to customizations plugin. For the user that I am testing with, who applied for a membership, there is no info in those billing fields. I want the billing fields to contain the home address info. This is fine for the users I’m importing, because I have those fields filled in. For users who register though, I want to ask for home address and have it go into billing fields. Right now I don’t ask for billing or home address during checkout.

Found code and added it to customizations plugin that removes old WordPress core user profile fields Jabber, AIM, Yahoo IM, and biographical data.

To keep user on the frontend, I used Theme-my-login plugin, and under User Links, you can remove the Dashboard link and Profile link (according to role). Now the Login Widget will not show those links. The member can edit their profile via a link in the sidebar to their Account page.

I gave up on finding a way to automate moving the blog posts from Drupal to WordPress and moved them manually. To assist in moving the photo galleries, I used the Add From Server plugin, which you can point to any folder on your server and tell it to import the files from there.

Actual import of real data: over 2,000 records; import stopped and resulted in a 404 error. I checked and there are only 800 users. Some users had no membership level. Found out that if the record has ‘inactive’, then the membership level is changed to 0. Removed the first 800 records from the CSV, saved the file, and ran the import again. This time over 600 users were imported. Repeated until all records were imported.








Restoring a Hacked WordPress Site

Recently a client contacted me about having difficulty logging in to the dashboard of their WordPress website. Sure enough, each time I attempted to log in, a ‘Connection was Reset’ message was displayed by the browser. The frontend of the site displayed correctly. Upon closer inspection of the WordPress files, it became obvious that some of them had been infiltrated. Upon editing the wp-config.php file, I could see code injected into the first line of the file. There were more files infected all through the site.

Looking at the last few backups, I could see that the files had already been infected before they were backed up. The best option would be to install the latest version of WordPress manually. First I removed files that were not part of WordPress, and then I uploaded the WordPress files. I was able to successfully log in to the backend.

Next steps:

Make sure WordPress and all plugins are updated.

Change all administrator passwords – use a combination of uppercase letters, lowercase letters, special characters, and numbers.

Make sure your website files AND database are being backed up daily or weekly.

Consider a security plugin.