Tag: database

Custom Columns Search: Not unique table/alias

After making Custom Sortable Columns everything was awesome, right?

Well… Mostly.

When I tried to sort a search, I got this:

WordPress database error: [Not unique table/alias: 'wp_postmeta']
SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.ID 
FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) 
LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id WHERE 1=1 
AND (((wp_posts.post_title LIKE '%kate%') OR ( (wp_postmeta.meta_key 
IN ( 'lezchars_actor', 'lezshows_worthit_details', 'lezshows_plots', 
'lezshows_episodes', 'lezshows_realness_details', 'lezshows_quality_details', 
'lezshows_screentime_details' ) ) AND (wp_postmeta.meta_value LIKE '%kate%') ) 
OR (wp_posts.post_excerpt LIKE '%kate%') OR (wp_posts.post_content LIKE '%kate%'))) 
AND ( wp_postmeta.meta_key = 'lezchars_type' ) AND wp_posts.post_type = 'post_type_characters' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'future' OR 
wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' OR 
wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY 
wp_postmeta.meta_value DESC LIMIT 0, 20

Not good.

And not actually (really) related to the sortability! No no, I did this to myself by adding in optimized post-meta search. You see, that runs on the front and the back end of WordPress.

I knew it had to be the search tweaks I’d made when I saw this:

OR ( (wp_postmeta.meta_key IN ( 'lezchars_actor', 'lezshows_worthit_details', 
'lezshows_plots', 'lezshows_episodes', 'lezshows_realness_details', 
'lezshows_quality_details', 'lezshows_screentime_details' ) )

Right away I realized it had to be my custom queries both calling from wp_postmeta and, in doing so, stemming all over each other. Not good at all.

The fix was relatively simple. All I had to do was move the filters to run only if we’re not in wp-admin.

/**
 * Only run if we're NOT in the admin screen!
 */

if ( ! is_admin() ) {
	add_filter( 'posts_join', 'lezwatch_search_join' );
	add_filter( 'posts_where', 'lezwatch_search_where' );
	add_filter( 'posts_distinct', 'lezwatch_search_distinct' );
}

This meant that the internal post search wouldn’t query those fields, but in the grand scheme of things, that’s okay. Generally when I’m looking for a post in the search, I’m looking for it by title. If I wanted to be more particular, I could have it not use my custom joins when I’m on a post listing page, but allow it if I’m editing a post (so adding a new link would search all that content too).

For now, this is what I need.

21 September, 2016

So Take an Upgrade, Maria!

A year ago I asked how do we solve a DB like Maria, and my friend James and I had a good laugh at the long list of issues.

It’s been a year.

I’ve upgraded to PHP 7.

It’s time.

What about Oracle vs Google?

Oh you heard about that? Google won a case against Oracle about GPL code in Android which I want to be really happy about, but it illustrates a major flaw in the judicial system. To whit: The player with the deepest pockets wins.

That case about Java though, not MySQL, which has been a long standing issue with the Open Source Community. The summary? Well it’s kind of like Prohibition. They can try to outlaw it, but we’re going to do our own thing. Which is where MariaDB comes in. It was a fork of MySQL, made in order to stay free and open. I strongly support open source software and so I change to MariaDB.

What Are The Issues?

There aren’t as many for MySQL 5.6 vs MariaDB 10.x as I’d thought. In looking at that, I determined it wouldn’t really impact my sites or setup. In fact, I was certain none of it would be an issue.

How to Upgrade?

I’m on WHM, so first I made a backup: /usr/local/cpanel/bin/backup --force

Then I went into WHM and said “Upgrade Maria!”

I picked the interactive upgrade since I wanted to see what it was doing. Step one went rather quickly and updated everything. It asked me to check my compiled software (which meant two non-WordPress sites that historically act odd when I upgrade). Once that was done, I clicked ‘next’ and was told I didn’t need to do anything else…

Now what?

I ran off to GTmetrix and checked my sites. I had two that were PHP 7 and they had no real change. I then changed another one to PHP 7 and Maria and checked it, and got bigger gains. Speed was up and everything, code wise, appeared to work as needed.

It was entirely a non-event.

10 June, 2016
Slow Site Troubleshooting: Database Edition
So your WordPress is slow and you’ve already done the needful. You’ve checked your plugins and themes, you’ve put caching in place, you’ve checked for hacks, but it’s still slow, especially on the back end of WordPress?

It may be your database.

More specifically it may be your wp_options table. When your options table gets very, very large, it gets very, very slow. WordPress regularly queries that when you’re logged in, and it’s not indexed. DB indexes are used to locate data fast, without searching every row in the tables. This sounds sensible in many ways, but we don’t

Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records. We don’t use ’em in the options table for a variety of reasons, but mostly that it slows things down.

So in lieu of making indexes on your own, what can and should you do to debug things?

Optimize Your Database

Got WP-CLI?
```
wp db optimize
```
Otherwise you can use phpMyAdmin to check tables with overhead and clean ’em up. I don’t use a plugin, I think asking WP to optimize itself is a little weird, but you certainly can. The point here is to keep it clean.

Cache that Database!

Caching makes things better. Right? Kind of right. Mostly right. I use memcacheD (which I often typo as memecached) and that plus an object-cache.php file can cache your DB calls so they’re faster, which is great. Unless your wp_optimize table is too big.

Your cache has two major issues. First, there’s going to be data you don’t want to cache (like private, sensitive information), but also when your cache is too big, or it’s trying to save data bigger than it is, it can make things slower by trying to cache, crashing, and repeating that over and over. That gets worse when we talk about the temp data generated by _transient entries in your database.

Check The Size

Your options table really shouldn’t be large. My biggest, busiest, site is only using 142K. It’s a site that’s old (8+ years now), it’s had many iterations and themes and plugins. You’d think it would be filthy with leftover code, because we all know plugins don’t always clean up. Nope. I did rebuild the DB once, in 2009, when I rebuilt the entire site from scratch, but that was 5 years ago and a lot has changed since then with plugins and themes. The next biggest site, a Multisite Network, has a wp_options of 100k. The biggest I’ve had is one of 500kb and that’s on a test site where I install and delete plugins daily.

You get the point I trust. These things should be small. At most, I’m going to have a lot of _transient entries. But that’s actually issue here.

Clean The Transients

There’s a story here about why WordPress doesn’t really clean your transients. Why? Well as the name implies, transient data is meant to be transient. It should be temporary data, saved and used briefly, and then it should go away. A lot of developers, as it happens, were storing it for long term caches. Like checking when the last upgrade ran, or when a cron kicked off. So while for one, glorious, month we did nuke them all on upgrade, now we only delete expired transients, which doesn’t help as much as it could. As Nacin said:

This leaves much to be desired, but we don’t want a core update to be blamed for breaking a site that incorrectly assumes transients aren’t transient.

Basically people doing things wrong in a way we couldn’t adjust for.

There are plugins like Delete Expired Transients and Transient Cleaner (which has the cutest header image). Those can be used to clean out your old transients.

If you want to go whole hog, there’s a command to clean the whole thing out with SQL:
```
DELETE FROM `wp_options` WHERE `option_name` LIKE ('%\_transient\_%')
```
Of course you want to run a db optimize afterwards to actually flush out the rows.

As always WP-CLI has features like wp transient delete-expired and wp transient delete-all.

What if that doesn’t help?

Then you should check the Database to see exactly what the biggest value is. In this case, I ran wp db cli to leap into the database and then this:
```
mysql> SELECT option_name , length (option_value) AS blah FROM wp_options ORDER BY blah DESC LIMIT 5;
+--------------------------------------------------+----------+
| option_name                                      | blah     |
+--------------------------------------------------+----------+
| cron                                             | 12194468 |
| _transient_feed_d117b5738fbd35bd8c0391cda1f2b5d9 |   223838 |
| _transient_feed_ac0b00fe65abe10e0c5b588f3ed8c7ca |    98184 |
| _transient_is_cached_instagram_images_self       |    97315 |
| mytheme_storage                                  |    18354 |
+--------------------------------------------------+----------+
10 rows in set (0.02 sec)
```
CRON is 12 megs. That would be the problem. Of course the only way I know of to fix that would be to totally trash cron and let it start over.

Is That It?

When you see a ginormous wp_options table, what do you do?
23 March, 2015

Mailbag: Debugging the Dread “No Permissions”

Sometimes customers hunt me up here. That’s okay. I’d rather you opened a ticket since I like to eat, sleep, and hang out with my wife, but in this case they also had an interesting problem.

When I log in, I get “You do not have sufficient permissions to access this page.”

I found the support ticket and solved it, and then I mailed the DreamPress Ninja’s with this methodology. It relies on wp-cli, so bear with me.

The first thing to do is check the user list:

user@server:~/example.com$ wp user list
+----+-----------------+--------------------------------+-----------------------------+---------------------+---------------+
| ID | user_login      | display_name                   | user_email                  | user_registered     | roles         |
+----+-----------------+--------------------------------+-----------------------------+---------------------+---------------+
| 2  | bobby           | Bobby Done Nightly             | bobby@example.com           | 2014-02-12 17:44:28 | administrator |
| 3  | darren          | Darren Done Rightly            | darren@example.com          | 2014-09-29 17:49:11 | contributor   |
| 4  | ethan           | Ethan Done Wrongly             | ethan@example.com           | 2014-10-27 21:01:07 | subscriber    |
| 5  | jimmybear       | Jimmy Eaten By A Bear          | jimmy@example.com           | 2013-12-16 14:45:18 | author        |
| 1  | iamempty        | Admin Account                  | admin@example.com           | 2015-03-05 01:30:09 |               |
| 6  | sonnyboy        | Sonny Boyd                     | sonny@example.com           | 2014-10-27 22:13:08 | contributor   |
+----+-----------------+--------------------------------+-----------------------------+---------------------+---------------+

Notice how imaginet has NO role? That’s the problem. So let’s give it a role!

user@server:~/example.com$ wp user add-role 1 administrator
Success: Added 'administrator' role for imaginet (1).

Check again and all is happy!

user@server:~/example.com$ wp user list
+----+-----------------+--------------------------------+-----------------------------+---------------------+---------------+
| ID | user_login      | display_name                   | user_email                  | user_registered     | roles         |
+----+-----------------+--------------------------------+-----------------------------+---------------------+---------------+
| 2  | bobby           | Bobby Done Nightly             | bobby@example.com           | 2014-02-12 17:44:28 | administrator |
| 3  | darren          | Darren Done Rightly            | darren@example.com          | 2014-09-29 17:49:11 | contributor   |
| 4  | ethan           | Ethan Done Wrongly             | ethan@example.com           | 2014-10-27 21:01:07 | subscriber    |
| 5  | jimmybear       | Jimmy Eaten By A Bear          | jimmy@example.com           | 2013-12-16 14:45:18 | author        |
| 1  | iamempty        | Admin Account                  | admin@example.com           | 2015-03-05 01:30:09 | administrator |
| 6  | sonnyboy        | Sonny Boyd                     | sonny@example.com           | 2014-10-27 22:13:08 | contributor   |
+----+-----------------+--------------------------------+-----------------------------+---------------------+---------------+

If the roles had be totally empty, it’s a case where the database is looking for the ‘wrong’ table prefix and that’s a little messier. There, you’ll want to grab the table prefix from the wp-config.php file:

$table_prefix  = 'wp_hsy671e_';

Then go into the database and look at wp_hsy671e_options for a field called wp_hsy671e_user_roles – if you don’t see one, that’s the problem. Check for one named wp_user_roles and rename it.

If you DO see the right user_roles, then the problem is all the users are pointing to the wrong table. You can just run wp search-replace wp_user_roles wp_hsy671e_user_roles to force it back.

20 March, 2015

Not The Bug I Expected
“Comments aren’t working,” he said and I winced. I hate when people say ‘broken’ or ‘not working’ because I don’t magically know what they mean by ‘working.’ But this guy was a coworker whom I like and trust so I asked what he meant by not working, and he said “When you enter a comment, the page reloads, but there’s no comment.”

“Okay,” I muttered to myself. “Touché, Mika.” Because that, indeed, was ‘not working.’ We turned on debugging and got a strange error:
```
WordPress database error: [Duplicate entry '0' for key 'PRIMARY']
INSERT INTO `wp_comments` (`comment_post_ID`,`comment_author`,`comment_author_email`,`comment_author_url`,`comment_author_IP`,`comment_date`,`comment_date_gmt`,`comment_content`,`comment_karma`,`comment_approved`,`comment_agent`,`comment_type`,`comment_parent`,`user_id`) VALUES (507,'lori','admin@example.com','','12.248.40.138','2014-02-07 23:07:08','2014-02-07 23:07:08','test',0,'1','Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:26.0) Gecko/20100101 Firefox/26.0','',0,1)
```
And then it was a mess of ‘Trying to get property of a non-object…’ and ‘headers already sent.’

After some kicking of the usual suspects (reinstall WP, remove plugins, theme, etc) I realized I was stupid and the problem was that the table wasn’t updating the primary entry. It should start at 1 and move up to 2 and so on. Since everything else was working, I compared the table to a working one … and they were not the same. Here’s a good one:

There’s had comment_id as the name, and the rest of the values were blank. So I restored the values and it worked! Except … I was getting a new error.
```
Notice: Undefined property: stdClass::$comment_ID in /home/wp-includes/comment.php on line 155

Warning: Cannot modify header information - headers already sent by (output started at /home/wp-includes/comment.php:155) in /home/wp-includes/pluggable.php on line 896
```
I stared at that for a moment and after I had given up and proposed we reinstall WP and copy tables over, my brain kicked in and I said “Wait a second, isn’t that comment_ID and not id?” I changed it on a whim and shouted “HOLY **** I AM GOOD!”

Yes, it was in all caps. Yes, it was said to my coworker, who said I have his vote.

That was, by far, the weirdest error I’ve seen in months. And the moral of all this? You can’t know everything, but if you read and pay attention and are willing to experiment, you can solve pretty much anything.
14 March, 2014
MySQL – my.cnf
This is a fairly rare file, and one I never would have found had I not needed to run a standard SQL process via cron.

Names have been changed to protect the innocent.

As the story goes, no matter what I did, I could not get this one app to stop spewing out ‘smart’ quotes. You know the fancy apostrophes and quotes that curl? Well, that’s not normally a problem, like in WordPress I’d just filter it out, but in this locked down system, I didn’t have that option. I called the vendor, and they said “Make sure you don’t paste in smart quotes.”

That was all fine and dandy for me but I’m not the master of the universe like that. Well, not all the time. I had people to input data for me! They were going to have to manually take the forms (Word Docs), filled in by non-techs, and copy the data into the right places in the app. And you want me to tell them they have to fix this for the non-techs? I thought about how much time that would take, and decided the best fix was to change the forms! Right?

If you’ve ever worked for a major company, you know why this was about as effective as aspirin for a root canal. No deal. So I decided to get inventive.

The only time this was a problem, these ugly quotes, was when we ran our weekly reports. This was how I found out about it, a manager complained that there was garbage instead of quotes on the form titles. Ergo: All I need to do is script something to clean them out!

Enter SQL!
```
# REPLACE SMART QUOTES WITH STUPID ONES
# FIRST, REPLACE UTF-8 characters.
UPDATE `secretapp_table` SET `formtitle` = REPLACE(`formtitle`, 0xE2809C, '&quot;');
UPDATE `secretapp_table` SET `formtitle` = REPLACE(`formtitle`, 0xE2809D, '&quot;');
# NEXT, REPLACE their Windows-1252 equivalents.
UPDATE `secretapp_table` SET `formtitle` = REPLACE(`formtitle`, CHAR(147), '&quot;');
UPDATE `secretapp_table` SET `formtitle` = REPLACE(`formtitle`, CHAR(148), '&quot;');
```
In my testing, if I ran that on formtitle, it cleaned it up for the report. This was a default report in the app, by the way, not something I had any control to change. And you wonder why I love open source? Anyhow, once I knew how this would work, I sent about scripting it. I couldn’t hook into any triggers on the app, though, because they don’t like to make it easy.

Fine, I decided. A crontab time it is! I made this simple script to run at midnight, every night, and clean up the DB:
```
#! /bin/bash

mysql -h &quot;dbname-secretapp&quot; &quot;secretapp_db&quot; &lt; &quot;quotecleaner.sql&quot;
```
It worked when I ran it by hand, but it failed when cron’d. This took me some headbanging, but after reading up on how SQL works, I realized it worked when I ran it as me because I’m me! But cron is not me. I have permissions to run whatever I want in my database. Cron does not. Nor should it! So how do I script it? I don’t want the passwords sitting in that file, which would be accessible by anyone with the CMS to update it.

I went around the corner to my buddy who was a DB expert, and after explaining my situation (and him agreeing that the cron/sql mashup was the best), he asked a simple question. “Who has access to log in as you?” The answer? Just me and the admins. The updating tool for our scripts was all stuff we ran on our PCs that pushed out to the servers, so no one but an admin (me) ever logged in directly.

He grinned and wrote down this on a sticky “.my.cnf”

Google and a Drupal site told me that it was a file that was used to give the mysql command line tools extra information. You shove it in the home directory of the account, and, well, here’s ours:
```
# Secret App user and password
user=secretapp_user
password=secretapp_password
```
The only reason I even remembered all this was because an ex-coworker said he ran into the documentation I left explaining all of this, and was thankful. He had to have it scan the body of the form now, because the managers wanted that in the report too!
30 September, 2013