I don’t know why, but any time I edit posts on this site, the block editor always goes into fullscreen mode. Even if I disable it, the next time I edit a post or refresh, it goes right back. My preferences aren’t being saved.
One WP-CLI command that I’ve found handy is this db-size command. It allows you to output a site’s registered database tables along with the data and index size in any format that WP-CLI natively supports, with multiple sort options:
/**
* Gets size of database tables for the current site.
*
* ## OPTIONS
*
* [--raw]
* : Outputs full size in bytes instead of human readable sizes.
*
* [--order_by=<Total><total>]
* : Allows custom ordering of the data.
* ---
* default: Total
* options:
* - Table
* - Data Size
* - Index Size
* - Total
* ---
*
* [--order=<asc><asc>]
* : Allows custom ordering direction of the data.
* ---
* default: asc
* options:
* - asc
* - desc
* ---
*
* [--format=<format><format>]
* : Render output in a particular format.
* ---
* default: table
* options:
* - table
* - csv
* - json
* - count
* - yaml
* ---
*
* @subcommand db-size
*/publicfunctiondb_size( $args, $assoc_args ){
global $wpdb;
$output = array();
$db_size = array();
$order_by = WP_CLI\Utils\get_flag_value( $assoc_args, 'order_by', 'Total' );
$order = WP_CLI\Utils\get_flag_value( $assoc_args, 'order', 'asc' );
$format = WP_CLI\Utils\get_flag_value( $assoc_args, 'format', 'table' );
$raw = (bool) WP_CLI\Utils\get_flag_value( $assoc_args, 'raw', false );
// Fetch list of tables from database.
$tables = array_map(
function( $val ){
return $val[0];
},
$wpdb->get_results( 'SHOW TABLES;', ARRAY_N ) // phpcs:ignore WordPress.DB.DirectDatabaseQuery
);
// Fetch table information from database.
$report = array_map(
function( $table )use( $wpdb ){ // phpcs:ignore WordPress.DB.DirectDatabaseQueryreturn $wpdb->get_row( "SHOW TABLE STATUS LIKE '$table'" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
},
$tables
);
foreach ( $report as $table ) {
// Keep a running total of sizes.
$db_size['data'] += $table->Data_length; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
$db_size['index'] += $table->Index_length; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase// Format output for WP-CLI's format_items function.
$output[] = array(
'Table' => $table->Name, // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase'Data Size' => $table->Data_length, // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase'Index Size' => $table->Index_length, // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase'Total' => $table->Data_length + $table->Index_length, // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
);
}
// Sort table data.self::sort_table_by( $order_by, $output, $order );
if ( ! $raw ) {
// Make data human readable.foreach ( array_keys( $output ) as $key ) {
$output[ $key ]['Data Size'] = size_format( $output[ $key ]['Data Size'] );
$output[ $key ]['Index Size'] = size_format( $output[ $key ]['Index Size'] );
$output[ $key ]['Total'] = size_format( $output[ $key ]['Total'] );
}
}
// Output data.
WP_CLI\Utils\format_items( $format, $output, array( 'Table', 'Data Size', 'Index Size', 'Total' ) );
// Output totals if we're not piping somewhere.if ( ! WP_CLI\Utils\isPiped() ) {
WP_CLI::success(
sprintf(
'Total size of the database for %s is %s. Data: %s; Index: %s',
home_url(),
WP_CLI::colorize( '%g' . size_format( $db_size['data'] + $db_size['index'] ) . '%n' ),
WP_CLI::colorize( '%g' . size_format( $db_size['data'] ) . '%n' ),
WP_CLI::colorize( '%g' . size_format( $db_size['index'] ) . '%n' )
)
);
}
}
/**
* Sorts a table by a specific field and direction.
*
* @param string $field The field to order by.
* @param array &$array The table array to sort.
* @param string $direction The direction to sort. Ascending ('asc') or descending ('desc').
*/privatefunctionsort_table_by( $field, &$array, $direction ){
// Taken from https://joshtronic.com/2013/09/23/sorting-associative-array-specific-key/ Thanks!
usort(
$array,
function( $a, $b )use( $field, $direction ){
$a = $a[ $field ];
$b = $b[ $field ];
if ( $a === $b ) {
return0;
}
switch ( $direction ) {
case'asc':
return ( $a < $b ) ? -1 : 1;
case'desc':
return ( $a > $b ) ? -1 : 1;
default:
return0;
}
}
);
returntrue;
}</format></asc></total>Code language:PHP(php)
Here’s some example output from one of my test sites:
If for some reason you can’t run wp db query, but need to export SQL output to a CSV or other file, then have a look at this small WP-CLI command I whipped up that should allow this:
/**
* Runs a SQL query against the site database.
*
* ## OPTIONS
*
*
* : SQL Query to run.
*
* [--format=]
* : Render output in a particular format.
* ---
* default: table
* options:
* - table
* - csv
* - json
* - count
* - yaml
* ---
*
* [--dry-run=]
* : Performa a dry run
*
* @subcommand sql
*/publicfunctionsql( $args, $assoc_args ){
global $wpdb;
$sql = $args[0];
$format = WP_CLI\Utils\get_flag_value( $assoc_args, 'format', 'table' );
$dry_run = WP_CLI\Utils\get_flag_value( $assoc_args, 'dry-run', 'true' );
// Just some precautions.if ( preg_match( '/[update|delete|drop|insert|create|alter|rename|truncate|replace]/i', $sql ) ) {
WP_CLI::error( 'Please do not modify the database with this command.' );
}
if ( 'false' !== $dry_run ) {
WP_CLI::log( WP_CLI::colorize( '%gDRY-RUN%n: <code>EXPLAIN</code> of the query is below: https://mariadb.com/kb/en/explain/' ) );
$sql = 'EXPLAIN ' . $sql;
}
// Fetch results from database.
$results = $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB// Output data.
WP_CLI\Utils\format_items( $format, $results, array_keys( $results[0] ) );
}Code language:PHP(php)
I’d add an example here, but I don’t have any right now that I can share 😐 I’ll try to find one later (don’t hold your breath on me remembering to do that)
In my forever attempt to learn and understand Gutenberg, React, and ES6 (which I am failing at horribly) I sometimes build WordPress plugins just for the learning experience. I don’t have any full intent on releasing these to the WordPress.org Plugin Repository because I honestly don’t feel like dealing with end users and support. I’m a horrible person, I know.
One that I’ve got in my barrel of cobwebs is Embeds for Twitch and Gutenberg
Recently I’ve been working with a client who’s site we’re going to soon be migrating. To help with any downtime, we’ve been looking at reducing their database size, which is something around 50-60 gigabytes. After looking through the database, one easy win would be to purge as many post revisions as possible, since their wp_posts table was about 50% revisions 😱
We couldn’t just mass delete anything with a revision post type though, because the client had some specific needs:
Keep all revisions for posts from the last 12 months.
Keep all revisions that were made after the post was published.
Keep a backup of all revisions deleted.
To do this, I crafted a custom WP-CLI command to purge these revisions. I’ve accidentally deleted the final version of the script, since it was only a one-run thing, but here’s an earlier version that could be a good starting point for anyone else that has a similar need to prune revisions:
/**
* WP-CLI command that deletes pre-publish post revisions for posts older than 12 months.
*
* @subcommand prune-revisions [--live] [--verbose]
*/publicfunctionprune_revisions( $args, $assoc_args ){
global $wpdb;
$live = (bool) $assoc_args[ 'live' ];
$verbose = (bool) $assoc_args[ 'verbose' ];
$offset = 0;
$limit = 500;
$count = 0;
$deletes = 0;
if ( $live ) {
$output_file = sanitize_file_name( sprintf( 'prune-revisions-backup_%d_%d.csv', get_bloginfo( 'name' ), current_time( 'timestamp', true ) ) );
$handle = fopen( $output_file, 'wb' );
if ( false === $handle ) {
WP_CLI::error( sprintf( 'Error opening %s for writing!', $output_file ) );
}
// Headers.
$csv_headers = array(
'ID',
'post_author',
'post_date',
'post_date_gmt',
'post_content',
'post_title',
'post_excerpt',
'post_status',
'comment_status',
'ping_status',
'post_password',
'post_name',
'to_ping',
'pinged',
'post_modified',
'post_modified_gmt',
'post_content_filtered',
'post_parent',
'guid',
'menu_order',
'post_type',
'post_mime_type',
'comment_count',
'filter',
);
fputcsv(
$handle,
$csv_headers
);
}
$count_sql = 'SELECT COUNT(ID) FROM ' . $wpdb->posts . ' WHERE post_type = "revision"';
$revision_count = (int) $wpdb->get_row( $count_sql, ARRAY_N )[0];
$progress = \WP_CLI\Utils\make_progress_bar( sprintf( 'Checking %s revisions', number_format( $revision_count ) ), $revision_count );
do {
$sql = $wpdb->prepare( 'SELECT ID FROM ' . $wpdb->posts . ' WHERE post_type = "revision" LIMIT %d,%d', $offset, $limit );
$revisions = $wpdb->get_results( $sql );
foreach ( $revisions as $revision ) {
$count++;
$post_parent_id = wp_get_post_parent_id( $revision->ID );
// Fail on either 0 or false.if ( false == $post_parent_id ) {
WP_CLI::warning( sprintf( 'Revision %d does not have a parent! Skipping!', $revision->ID ) );
continue;
}
$revision_modified = get_post_modified_time( 'U', false, $revision->ID );
$parent_publish_time = get_post_time( 'U', false, $post_parent_id );
if ( $parent_publish_time < current_time( 'timestamp') - ( MONTH_IN_SECONDS * 12 ) ) {
// Post is older than 12 months, safe to delete pre-publish revisions.if ( $revision_modified < $parent_publish_time ) {
if ( $live ) {
// We're doing it live!
WP_CLI::log( sprintf( 'Deleting revision %d for post %d. (%d%% done)', $revision->ID, $post_parent_id, ( $count / $revision_count ) * 100 ) );
// Backup data!
$output = [];
$data = get_post( $revision->ID );
// Validate the field is set, just in case. IDK how it couldn't be.foreach ( $csv_headers as $field ) {
$output = isset( $data->$field ) ? $data->$field : '';
}
fputcsv( $handle, $output );
$did_delete = wp_delete_post_revision( $revision->ID );
// Something went wrong while deleting the revision?if ( false === $did_delete || is_wp_error( $did_delete ) ) {
WP_CLI::warning( sprintf( 'Revision %d for post %d DID NOT DELETE! wp_delete_post_revision returned:', $revision->ID, $post_parent_id ) );
}
$deletes++;
// Pause after lots of db modifications.if ( 0 === $deletes % 50 ) {
if ( $verbose ) {
WP_CLI::log( sprintf( 'Current Deletes: %d', $deletes ) );
}
sleep( 1 );
}
} else {
// Not live, just output info.
WP_CLI::log( sprintf( 'Will delete revision %d for post %d.', $revision->ID, $post_parent_id ) );
}
} else {
// Revision is after the post has been published.if ( $verbose ) {
WP_CLI::log( sprintf( 'Post-Publish: Will NOT delete Revision %d for post %d.', $revision->ID, $post_parent_id ) );
}
}
} else {
// Post is too new to prune.if ( $verbose ) {
WP_CLI::log( sprintf( 'Too-New: Will NOT delete Revision %d for post %d.', $revision->ID, $post_parent_id ) );
}
}
}
// Pause after lots of db reads.if ( 0 === $count % 5000 ) {
if ( $verbose ) {
WP_CLI::log( sprintf( 'Current Count: %d', $count ) );
}
sleep( 1 );
}
// Free up memory.$this->stop_the_insanity();
// Paginate.if ( count( $revisions ) ) {
$offset += $limit;
$progress->tick( $limit );
} else {
WP_CLI::warning( 'Possible MySQL Error, retrying in 10 seconds!' );
sleep( 10 );
}
} while ( $count < $revision_count );
$progress->finish();
if ( $live ) {
fclose( $handle );
WP_CLI::success( sprintf( 'Deleted %d revisions', $deleted ) );
} else {
WP_CLI::success( sprintf( 'Processed %d revisions', $revision_count ) );
}
}Code language:PHP(php)
Last year I had a need for an editable JSON file that was retrievable via HTTP. Of course there’s a million ways that I could do this, but the easiest I thought of would be to have it inside of WordPress, since all of the people that needed access to edit the file already had edit access to a specific site. So I built a plugin.
Doing this inside WordPress already brings a lot of benefits with little to no effort:
User Management
Revision History
oEmbed Support
Permalinks
Syntax Highlighting Code Editor
Self-Hosted Data
Possibly more benefits as well, depending on the setup, such as caching.
I’ve tweaked the plugin some, and I’m almost ready to submit it to the WordPress.org Plugin Repository. I just need to do the hard part of figuring out artwork. Ugh.
Introducing Wisps:
Wisps are embeddable and sharable code snippets for WordPress.
With Wisps, you can have code snippets similar to Gist, Pastebin, or similar code sharing sites. Using the built-in WordPress code editor, you can write snippets to post and share. This has the benefit of WordPress revisions, auto-drafts, etc to keep a record of how code changes.
Wisps can be downloaded by appending /download/ to the permalink, or viewed raw by adding /view/ or /raw/. There is full oEmbed support so you can just paste in a link to a wisp in the editor and it will be fully embedded.
PrismJS is used for syntax highlighting for oEmbeds.
You can add Wisp support to your theme either by modifying the custom post type page-wisp.php template, which will continue to display Wisps in the loop securely, or you can use add_theme_support( 'wisps' ) to tell the plugin to not automatically escape the output. You can then do what you like, such as potentially adding frontend support for syntax highlighting.
Here’s what the oEmbed data looks like:
(Yeah, I totally stole the design from Gists, because I’m not talented 😬)
If you google debugging WordPress hooks you’ll find a lot of information.
About 1,180,000 results
Let’s add another one.
WordPress hooks are powerful, but also complex under the hood. There’s plenty of topics I could talk about here, but right now I’m only going to talk about speed. How long does it take for a hook to fire and return?
Some cool work in this area has already been done thanks to Debug Bar Slow Actions and Query Monitor, but outside of something like Xdebug or New Relic, you’ll have a hard time figuring out how long each individual hook callback takes without modifying either WordPress core or each hook call.
… or maybe not …
While doing some client debugging for my job at WordPress VIP (did I mention we’re hiring?) I came across the need to do this exact thing. I’ve just finished the code that will make this happen, and I’m releasing it into the wild for everyone to benefit.
What’s the problem we’re trying to solve? Well, this client has a sporadic issue where posts saving in the admin time out and sometimes fail. We’ve ruled out some potential issues, and are looking at a save_post hook going haywire.
Now I can capture every single save_post action, what the callback was, and how long it took. Here’s an example for this exact post:
We add an all action that will fire for every other action.
In the all callback, we make sure we’re looking for the correct hook.
We then build an array to store some data, use the $wp_filter global to fill out information such as the priority and the callback, and store the start time.
Next we have to add a new action to run for our hook right before the callback we want to time. We use the fact that, even though add_action() is supposed to use an int for the priority, it will also accept a string. We add new hooks, and re-prioritze all of the existing hooks with floats that are stringified.
This allows us to capture the start time and end time of each individual callback, instead of the priority group as a whole.
Of course, this does add a tiny bit of overhead, and could cause some problems if any other plugins use stringified hook priorities, or other odd issues–so be careful 🙂
Finally, here’s the code:
classVIP_Hook_Timeline{
public $hook;
public $callbacks = [];
public $callback_mod = 0.0001;
public $callback_mods = [];
publicfunction__construct( $hook ){
$this->hook = $hook;
add_action( 'all', array( $this, 'start' ) );
}
publicfunctionstart(){
// We only want to get a timeline for one hook.if ( $this->hook !== current_filter() ) {
return;
}
global $wp_filter;
// Iterate over each priority level and set up array.foreach( $wp_filter[ $this->hook ] as $priority => $callback ) {
// Make the mod counter if not exists.if ( ! isset( $this->callback_mods[ $priority ] ) ) {
$this->callback_mods[ $priority ] = $priority - $this->callback_mod;
}
// Make the array if not exists.if ( ! is_array( $this->callbacks[ $priority ] ) ) {
$this->callbacks[ $priority ] = [];
}
// Iterate over each callback and set up array.foreach( array_keys( $callback ) as $callback_func ) {
if ( ! is_array( $this->callbacks[ $priority ][ $callback_func ] ) ) {
$this->callbacks[ $priority ][ $callback_func ] = [ 'start' => 0, 'stop' => 0 ];
}
}
}
foreach( $this->callbacks as $priority => $callback ) {
foreach ( array_keys( $callback ) as $callback_func ) {
// Get data befmore we move things around.
$human_callback = $this->get_human_callback( $wp_filter[ $this->hook ][ $priority ][$callback_func] );
// Modify the priorities.
$pre_callback_priority = $this->callback_mods[ $priority ];
$this->callback_mods[ $priority ] = $this->callback_mods[ $priority ] + $this->callback_mod;
$new_callback_priority = $this->callback_mods[ $priority ];
$this->callback_mods[ $priority ] = $this->callback_mods[ $priority ] + $this->callback_mod;
$post_callback_priority = $this->callback_mods[ $priority ];
$this->callback_mods[ $priority ] = $this->callback_mods[ $priority ] + $this->callback_mod;
// Move the callback to our "new" priority.if ( $new_callback_priority != $priority ) {
$wp_filter[ $this->hook ]->callbacks[ strval( $new_callback_priority ) ][ $callback_func ] = $wp_filter[ $this->hook ]->callbacks[ $priority ][ $callback_func ];
unset( $wp_filter[ $this->hook ]->callbacks[ $priority ][ $callback_func ] );
if ( empty( $wp_filter[ $this->hook ]->callbacks[ $priority ] ) ) {
unset( $wp_filter[ $this->hook ]->callbacks[ $priority ] );
}
}
// Add a new action right before the one we want to debug to capture start time.
add_action( $this->hook, function( $value = null )use( $callback_func, $priority, $human_callback ){
$this->callbacks[ $priority ][ $callback_func ]['start'] = microtime( true );
// Uncomment this if you just want to dump data to the PHP error log, otherwise add your own logic.//$message = 'START: %d:%s, (Callback: `%s`)';// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log//error_log( sprintf( $message,// $priority,// $this->hook,// $human_callback,//) );// Just in case it's a filter, return.return $value;
}, strval( $pre_callback_priority ) );
// Add a new action right after the one we want to debug to capture end time.
add_action( $this->hook, function( $value = null )use( $callback_func, $priority, $human_callback ){
$this->callbacks[ $priority ][ $callback_func ]['stop'] = microtime( true );
// Uncomment this if you just want to dump data to the PHP error log, otherwise add your own logic.//$message = 'STOP: %d:%s, Taken %s (Callback: `%s`)';// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log//error_log( sprintf( $message,// $priority,// $this->hook,// $this->get_human_diff( $priority, $callback_func ),// $human_callback,//) );// Just in case it's a filter, return.return $value;
}, strval( $post_callback_priority ) );
}
}
}
publicfunctionget_human_callback( $callback ){
$human_callback = '[UNKNOWN HOOK]';
if ( is_array( $callback['function'] ) && count( $callback['function'] ) == 2 ) {
list( $object_or_class, $method ) = $callback['function'];
if ( is_object( $object_or_class ) ) {
$object_or_class = get_class( $object_or_class );
}
$human_callback = sprintf( '%s::%s', $object_or_class, $method );
} elseif ( is_object( $callback['function'] ) ) {
// Probably an anonymous function.
$human_callback = get_class( $callback['function'] );
} else {
$human_callback = $callback['function'];
}
return $human_callback;
}
publicfunctionget_start( $priority, $callback_func ){
return (float) $this->callbacks[ $priority ][ $callback_func ]['start'];
}
publicfunctionget_stop( $priority, $callback_func ){
return (float) $this->callbacks[ $priority ][ $callback_func ]['stop'];
}
publicfunctionget_diff( $priority, $callback_func ){
return (float) ( $this->get_stop( $priority, $callback_func ) - $this->get_start( $priority, $callback_func ) );
}
publicfunctionget_human_diff( $priority, $callback_func ){
$seconds = $this->get_diff( $priority, $callback_func );
// Seconds.if ( $seconds >= 1 || $seconds == 0 ) {
return number_format( $seconds, 3 ) . 's';
}
// Milliseconds.if ( $seconds >= .001 ) {
return number_format( $seconds * 1000, 3 ) . 'ms';
}
// Microseconds.if ( $seconds >= .000001 ) {
return number_format( $seconds * 1000000, 3 ) . 'μs';
}
// Nanoseconds.if ( $seconds >= .000000001 ) {
// WOW THAT'S FAST!return number_format( $seconds * 1000000000, 3 ) . 'ns';
}
return $seconds . 's?';
}
}
new VIP_Hook_Timeline( 'save_post' );Code language:PHP(php)
Of course, you could go through each post and check the meta, check for duplicates, and delete any of them but…
I think I’ve figured out a beast of an SQL query that will give you the Meta IDs of any duplicate post meta so that you can delete them with delete_metadata_by_mid().
SELECT * FROM (
SELECT meta_id FROM (
SELECT meta_table_1.* FROM wp_postmeta meta_table_1, wp_postmeta meta_table_2 WHERE (
meta_table_1.post_id = meta_table_2.post_id AND meta_table_1.meta_value = meta_table_2.meta_value AND meta_table_1.meta_key = meta_table_2.meta_key
) ORDER BY meta_table_2.post_id
) Table_All_Duplicates GROUP BY meta_id HAVING COUNT(*)>1
) Unique_Dupes WHERE meta_id NOT IN (
SELECT min(meta_id) AS min_meta_id FROM wp_postmeta GROUP BY post_id,meta_key,meta_value HAVING COUNT(post_id) > 1AND COUNT(meta_key) > 1AND COUNT(meta_value) > 1AND COUNT(meta_key >= 1)
);Code language:PHP(php)
An example:
mysql> SELECT * FROM wp_postmeta WHERE post_id = 919 AND meta_key = '_totally_duped';
+---------+---------+----------------+------------+
| meta_id | post_id | meta_key | meta_value |
+---------+---------+----------------+------------+
| 2123 | 919 | _totally_duped | 1 |
| 2124 | 919 | _totally_duped | 1 |
| 2125 | 919 | _totally_duped | 1 |
+---------+---------+----------------+------------+
3 rows inset (0.01 sec)
mysql> SELECT * FROM (SELECT meta_id FROM (SELECT meta_table_1.* FROM wp_postmeta meta_table_1, wp_postmeta meta_table_2 WHERE ( meta_table_1.post_id = meta_table_2.post_id AND meta_table_1.meta_value = meta_table_2.meta_value AND meta_table_1.meta_key = meta_table_2.meta_key ) ORDER BY meta_table_2.post_id) Table_All_Duplicates GROUP BY meta_id HAVING COUNT(*)>1) Unique_Dupes WHERE meta_id NOT IN (SELECT min(meta_id) AS min_meta_id FROM wp_postmeta GROUP BY post_id,meta_key,meta_value HAVING COUNT(post_id) > 1 AND COUNT(meta_key) > 1 AND COUNT(meta_value) > 1 AND COUNT(meta_key >= 1));
+---------+
| meta_id |
+---------+
| 2124 |
| 2125 |
+---------+
2 rows in set (0.03 sec)Code language:JavaScript(javascript)
I just want to note though, this is VERY slow. I did this because I’m currently working on trying to clean up a site that has about 24 million rows of postmeta, and I really hope that there’s some duplicates to delete. This one query has been running for about 12 hours now, and I have no idea when it will end.
So I guess I do have time for that after all ¯\_(ツ)_/¯
UPDATE:
I’ve discovered that it might be good to have more data for forensic investigation… so I’ve adjusted the query to also give me the post id, meta key, meta value length, and the original meta id:
mysql> SELECT post_id,group_concat(distinct meta_id) as meta_ids,min( distinct meta_id) as primary_meta_id,meta_key,char_length(meta_value) as meta_length,count( distinct meta_id ) * char_length(meta_value) as total_size FROM ( SELECT meta_table_1.* FROM wp_postmeta meta_table_1, wp_postmeta meta_table_2 WHERE (
-> meta_table_1.post_id = meta_table_2.post_id AND meta_table_1.meta_value = meta_table_2.meta_value AND meta_table_1.meta_key = meta_table_2.meta_key ) ORDER BY meta_table_2.post_id ) Table_All_Duplicates GROUP BY post_id,meta_key HAVING COUNT(*)>1;
+---------+----------------+-----------------+------------------+-------------+------------+
| post_id | meta_ids | primary_meta_id | meta_key | meta_length | total_size |
+---------+----------------+-----------------+------------------+-------------+------------+
| 915 | 2176,2177,2178 | 2176 | _totally_duped_2 | 1 | 3 |
| 919 | 2123,2124,2125 | 2123 | _totally_duped | 1 | 3 |
+---------+----------------+-----------------+------------------+-------------+------------+
2 rows inset (0.03 sec)Code language:JavaScript(javascript)
…or how I stopped worrying and learned to love the slashes.
Post Meta in WordPress is a great tool to store random bits of data associated with your posts. But did you know that it comes with its own set of problems?
Have you ever tried storing a JSON string inside of post meta?
This is because WordPress passes the meta value through wp_unslash() before storing it in the database. This causes the already existing escaped/slashes data inside the JSON string to be destroyed:
Luckily, there’s a quick solution to this. You can pass your data through wp_slash() to double-encode it, so that when wp_unslash() does its job, it only unslashes a single iteration of slashes, leaving your JSON string safe: