Make WordPress Core

Tagged: wpdb Toggle Comment Threads | Keyboard Shortcuts

  • Gary Pendergast 2:25 am on April 2, 2015 Permalink
    Tags: , , utf8mb4, wpdb   

    The utf8mb4 Upgrade 

    In WordPress 4.2, we’re upgrading tables to utf8mb4, when we can. Your site will only upgrade when the following conditions are met:

    • You’re currently using the utf8 character set.
    • Your MySQL server is version 5.5.3 or higher (including all 10.x versions of MariaDB).
    • Your MySQL client libraries are version 5.5.3 or higher. If you’re using mysqlnd, 5.0.9 or higher.

    The difference between utf8 and utf8mb4 is that the former can only store 3 byte characters, while the latter can store 4 byte characters. In Unicode terms, utf8 can only store characters in the Basic Multilingual Plane, while utf8mb4 can store any Unicode character. This greatly expands the language usability of WordPress, especially in countries that use Han character sets. Unicode isn’t without its problems, but it’s the best option available.

    utf8mb4 is 100% backwards compatible with utf8.

    Due to index size restrictions in MySQL, this does mean we need to re-create a handful of indexes to fit within MySQL’s rules. Using a standard configuration, MySQL allows 767 bytes per index, which for utf8 means 767 bytes / 3 bytes = 255 characters. For utf8mb4, that means 767 bytes / 4 bytes = 191 characters. The indexes that will be resized are:


    And from Multisite:


    Of course, the Multisite (and wp_usermeta) keys obey the DO_NOT_UPGRADE_GLOBAL_TABLES setting. The upgrade will only be attempted once, though we’ll probably add a check in a future WordPress version to see if we can upgrade now (say, if you’ve upgraded your MySQL server since upgrading to WordPress 4.2).

    If you’re a plugin developer and your plugin includes custom tables, please test that your indexes fit within MySQL’s limits. MySQL won’t always produce an error when the index is too big, so you’ll need to manually check the size of each index, instead of relying on automated testing.

    EDIT: One more thing…

    If you’d like to upgrade your custom tables to utf8mb4 (and your indexes are all in order), you can do it really easily with the shiny new maybe_convert_table_to_utf8mb4( $tablename ) function. It’s available in `wp-admin/includes/upgrade.php`, and will sanity check that your tables are entirely utf8 before upgrading.

    • Thomas Townsend 2:33 am on April 2, 2015 Permalink | Log in to Reply

      Hi Gary, glad to see you and the team are looking after the devs too ? I owe you a beer or tow if you even get down to Tampa Florid area…

    • Pippin Williamson 2:36 am on April 2, 2015 Permalink | Log in to Reply

      Excellent, thanks for the notice!

    • Todd Lahman 3:47 am on April 2, 2015 Permalink | Log in to Reply

      Great to see WP Keeps moving forward. Thanks for the update Gary.

    • J.D. Grimes 1:03 pm on April 2, 2015 Permalink | Log in to Reply

      @pento I’ve noticed that the difference in index length can cause notices when updating tables using `dpDelta()`.

      I was getting this error locally, when running the install tests for a plugin against WordPress trunk:

      WordPress database error: [Duplicate key name ‘meta_key’]
      ALTER TABLE wptests_dbdelta_test3 ADD KEY meta_key (meta_key)

      After some debugging, I found that dbDelta() was being thrown off by KEYs for VARCHAR columns, because the description of the key from the database would be like this:

      `KEY meta_key (meta_key(191))`

      But in my CREATE TABLE string, they are defined like this:

      `KEY meta_key (meta_key)`

      Since these don’t match `dbDelta()` attempts to add the key again, resulting in the error. This would happen to users on sites that support utf8mb4, if the plugin is deactivated and then reactivated.

      Kind of related: #31388.

      • Gary Pendergast 1:29 pm on April 2, 2015 Permalink | Log in to Reply

        Thanks for the bug report, @jdgrimes! I’ve recorded it in #31869, I’ll have a think about the best way to fix it.

        In the mean time, a valid work around is to specify the index length in your CREATE TABLE statement – this is what we’ve done in core, hence why I haven’t run into this bug previously.

    • pix365 3:05 pm on April 13, 2015 Permalink | Log in to Reply

      For folks on v4.1.1 ior earlier – Will there be a plugin released that will perform the the sanity checks to ensure existing sites have all the SQL pre-requisites in place before folks even attempt the upgrade to 4.2. ( or have miss understood the above “Edit on the upgrade.php” or is this check is already built in to 4.1.1)

      • Ipstenu (Mika Epstein) 4:00 pm on April 13, 2015 Permalink | Log in to Reply

        The checks are built in.

        > Your site will only upgrade when the following conditions are met:…

        That’s what’s doing it :) If any condition fails, no DB changes. If you’re not even on the right version of MySQL, it won’t do it either.

    • snowboardmommy 12:30 am on April 24, 2015 Permalink | Log in to Reply

      FWIW, I upgraded a multisite installation (with debug mode ON) to 4.2 today and also found a similar DB error message in the error_log in wp-admin:

      WordPress database error Duplicate key name ‘meta_key’ for query ALTER TABLE wp_usermeta ADD KEY meta_key (meta_key(191)) made by wp_upgrade, make_db_current_silent, dbDelta
      WordPress database error Duplicate key name ‘domain’ for query ALTER TABLE wp_site ADD KEY domain (domain(140),path(51)) made by wp_upgrade, make_db_current_silent, dbDelta
      WordPress database error Duplicate key name ‘meta_key’ for query ALTER TABLE wp_sitemeta ADD KEY meta_key (meta_key(191)) made by wp_upgrade, make_db_current_silent, dbDelta
      WordPress database error Duplicate key name ‘domain_path’ for query ALTER TABLE wp_signups ADD KEY domain_path (domain(140),path(51)) made by wp_upgrade, make_db_current_silent, dbDelta

      I haven’t peeked at the database (phpMyAdmin) yet, but everything appears to be working alright.

    • jalev 8:01 am on April 24, 2015 Permalink | Log in to Reply

      Hi Gary, I did the upgrade to 4.2 and afterwards it asks me for a database upgrade as I obviously belong to one of the cases you described above. The problem is that the Database Update process timeouts.

      I have increased the timeout to 60 minutes but there is an alter query that after almost 15 minutes of running, it crashes my server as it takes all the ram and swap available. The query is the following: ALTER TABLE nB8KH50Wbb_options CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

      and below it there is a huge que of locked queries as they try to write in the same table “Waiting for table metadata lock”.

      Any ideas?

      • Ipstenu (Mika Epstein) 12:06 pm on April 24, 2015 Permalink | Log in to Reply

        How big is your db? How big are your post and post meta tables?

        • FolioVision 8:31 am on May 7, 2015 Permalink | Log in to Reply

          Hello Mika,

          we had the same issue, we had to do this with a command line PHP, see our link in other comment here.

          Here is some info about the two sites where we had the issue:

          Site 1: wp_posts 256MB, wp_postmeta 57MB, wp_comments 292MB, wp_commentmeta 181.9MB, wp_options 19.8MB
          Site 2: wp_posts 84.9MB, wp_postmeta 5MB, wp_comments 134MB, wp_commentmeta 173.7MB, wp_options 9MB

          Perhaps you should improve the upgrade process to remember which queries finished before it times out. When it times out, it seems to be executing all the queries again, even if they finished before.


          • jstensved 4:26 pm on May 22, 2015 Permalink | Log in to Reply

            I can confirm that I’m having the exact same issue on another large site which I can’t update. I’m updating from command line with WP-CLI but still the command will eventually consume all memory.

    • l3hworks 4:00 pm on April 24, 2015 Permalink | Log in to Reply

      Hello, I updated to 4.2 in xampp and now I can’t go back to my life server (mysql 5.1), is there any way I could revert this?

    • Nihad 4:46 pm on April 24, 2015 Permalink | Log in to Reply

      I’ve upgraded to 4.2 today, on two different domains, one is MultiSite (Danish) other one (in English) is not.
      I’ve got no errors, so all is good. But my encoding is wrong. Content is not being displayed properly, even if it is created and saved as UTF8. On both sites.

      Since then, in troubleshooting, I’ve manually converted my db/data/tables from UTF8 to UTF8MB4, to see if this will resolve the issue. Nope.
      Iconv utility converting data to UTF8 just in case it was not. Did nothing either.
      My data looking at it directly in mysql looks good. Even Sequel Pro displays data correctly.

      It worked perfectly on pre 4.2… now it does not.
      Posts that are incorrectly displayed are non editable in WordPress either. It show, empty titles and contents. and when you save them, they are empty. So it’s like it can’t recognize encoding and then it just saves it as empty.

      But… If i copy data directly from database, paste is into a post, that is displayed as empty, in WordPress editor, and save it… It works correctly. So already existing data is somehow read incorrectly, but if the same data is copied into the editor and save to database it is good.

      Seems like there is an issue, in updating of database that went wrong somewhere.

      • sistemasBebetter 6:51 pm on April 29, 2015 Permalink | Log in to Reply

        Have you found any solution to this? I’m having the same problem but only with one of the sites I have (the others in the same computer and database upgrade correctly).
        Which WP version had your sites before upgrading?

      • watomsk 7:50 am on May 3, 2015 Permalink | Log in to Reply

        Did you manage to find a solution to this? I upgraded to 4.2 and have the same problem with my website.

    • neo7 12:58 pm on April 26, 2015 Permalink | Log in to Reply

      Got this error while updating “PHP message: WordPress database error Specified key was too long; max key length is 1000 bytes for query ALTER TABLE wp_commentmeta CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci made by wp_upgrade, upgrade_all, upgrade_420, maybe_convert_table_to_utf8mb4”

      Now all tables are utf8mb4_unicode_ci except wp_commentmeta which is utf8_general_ci.

      How to covert this table?

    • interglobalmedia 1:47 am on April 27, 2015 Permalink | Log in to Reply

      Hi Nihad. I had kind of a similar issue when I updated to 4.2. What is actually happening is that incorrect code that was previously undetected or slipped under the radar so to speak, has now been detected and then “erased”. I’ll give an example. I was working with a theme that someone else had begun developing, and there was incorrect code in it. I hadn’t gone over everything yet, and did not realize that the theme’s sidebar had not been registered or configured properly. So, when I updated to 4.2, I was told in the backend that my sidebar’s id had been incorrectly configured. Could I please manually correct it? I forget exactly what I did, but when I did it, the sidebar disappeared. What was good about this and I saw as a real plus, was that it told me what was wrong, and then I knew what I had to do to fix it. i fixed it. Luckily, it was on a local install. Of course. I was still developing it. Then I had another issue post 4.2 that had never come to the fore previously. I have a theme that uses a page template called “full width content”. However, I had purchased a premium plugin called “Ultimate Landing Pages” back in February. It wasn’t quite clear what sort of template the developer used for these landing pages, but everything seemed to work just great. Until 4.2. Today, I went into one of my site’s regular pages that I had configured to “full width content a couple of months ago, and I noticed that it no longer ran the full page width that it had before 4.2! The now invisible sidebar prevented the content from running the full width of the page. I thought it bizarre and went into the Editor to see what was going on. The Ultimate Landing Pages plugin’s template finally had shown up. It was configured to the “Front Page Template, which is one of the WordPress “Reserved Theme File Names”, has special meaning for WordPress, and if present along with a custom theme template that does the same thing, it will take precedence. So the fact that I had both the Front Page template and the Full Width template which both displayed the page in the same way, caused the Full Width template to no longer display “properly”. I had to go into the Editor and change the page template to “Front Page”. This partially has to do with cache issues, but also 4.2 lets you know either overtly (or covertly) about incorrect code/configuration issues. I am going to try and find out from the WordPress core developers what changes they made that made this possible. I see it as a very positive thing. Hope this helps.

    • DrLightman 10:40 am on April 27, 2015 Permalink | Log in to Reply

      Could this index upgrade fail or time out on databases with large tables, let say 250’000+ posts thus lot more postmetas?

      If we do this upgrade manually let say via command line on dedicated servers, will WP 4.2 skip the upgrade during installation?

    • jtleathers 9:56 pm on April 27, 2015 Permalink | Log in to Reply

      Developing a site locally on WAMP with SQL 5.6.17. When I attempt to import the database to a server using an older version of SQL I get the following error:

      – Unknown collation: ‘utf8mb4_unicode_ci’

      How do I properly rollback this “upgrade” from WordPress 4.2?

      • Ipstenu (Mika Epstein) 11:30 pm on April 27, 2015 Permalink | Log in to Reply

        Import how? Phpmyadmin?

        • mctenold 11:55 pm on April 27, 2015 Permalink | Log in to Reply

          Same issue here – importing any way, CLI, phpMyAdmin, or personally I’m using MySQL Workbench for Mac and getting the same errors. For now I’m going to have to not use 4.2 until I can find a workflow to go from Local -> Dev Server, where local MySQL is compatible with 4.2 but dev server is not.

          • Max Sixblade 5:16 am on May 4, 2015 Permalink | Log in to Reply

            True! Using XAMPP for Windows, this is definitely not a phpmyadmin issue. Older mysql version on remote server leads to errors while importing db. Compatibility modes while exporting db didn’t help in my case.

        • jtleathers 12:45 am on April 28, 2015 Permalink | Log in to Reply

          Yeah, import through PHPMyAdmin.

        • MBWD 3:23 pm on May 16, 2015 Permalink | Log in to Reply

          I am having the same problem with my shared hosting server, which is using an older version of PHP. The upgrade to 4.2 apparently went fine for sites already hosted, but importing a db with utf8mb4 doesn’t work. I tried downgrading my version of WP to 4.0 and upgrading the database (which I thought would revert the charset) but it didn’t work. Manually replacing didn’t work either.

    • mctenold 12:54 am on April 28, 2015 Permalink | Log in to Reply

      I’m in the same boat as @jtleathers, my typical workflow is to install WordPress locally for a new site, build the site locally, and then move everything to a dev or production server once I’m at a good place.

      The issue comes in when my client’s dev or production server has a MySQL version 5.5.3. I get the same error upon attempting to import the database: Unknown collation: ‘utf8mb4_unicode_ci’.

      This seems like a misstep of an upgrade, I think my workflow is pretty common practice and will affect a lot of people. I don’t think it’s common to install/develop the site on the production server.

      Any remedy for my dilemma?

      Seems like the only possible fix right now is to downgrade my local MySQL version, or not use WordPress 4.2.


    • dustinbolton 10:20 pm on May 6, 2015 Permalink | Log in to Reply

      BackupBuddy developer here: It is absolutely INCORRECT to say that migrating sites to new server is uncommon. BackupBuddy helps users move several hundred thousand sites per year to new servers.

      We are already seeing a flood of users encountering this problem attempting to move their site to new servers with older MySQL versions. They of course are puzzled why this is a problem since WordPress states it supports this older MySQL version.

      Many users these days practice things such as local development, staging, etc which are good modern practices. WordPress has effectively put a huge thorn in the sides of developers and in my opinion has made an extremely big mistake.

    • lucaboccianti 6:38 pm on May 11, 2015 Permalink | Log in to Reply

      I may have found a possible walkaround.

      I’m in the same situation and problem as above: develop locally with xampp then upload on the production server (mostly shared hosting).

      with phpmyadmin for each table edit each field codified with utf8mb4-something to be codified as utf8-something (select the table, then Structure, etc.). then edit the table properties (Operations) and do the same.

      finally export the dump and restore it on the production server.

      it’s long and painful but maybe better than restart a project from scratch.

    • lucaboccianti 6:49 pm on May 11, 2015 Permalink | Log in to Reply

      problems: you’ll end with all the extended characters looking funny and apparently the text edit area appear blank even if the text is visible on the frontend.

    • Max Sixblade 11:36 pm on May 11, 2015 Permalink | Log in to Reply

      So.. where shall we vote to get that “install in default UTF-8” checkbox on WP installation?)

    • Ben Lobaugh (blobaugh) 1:44 pm on May 12, 2015 Permalink | Log in to Reply

      I have had several migrations happen this week, and even between the dev, staging, and production servers. Ran into lots of issues and what I wound up doing is running a script to convert the database back to utf8. This worked great to get the data imported and the next time wp-admin was loaded the database was successfully updated with the security patch.

      If interested here is the script I used http://ben.lobaugh.net/blog/201740/script-to-convert-mysql-collation-from-utf8mb4-to-utf8

      • dblomster 3:23 pm on May 12, 2015 Permalink | Log in to Reply

        Nice! What about table indexes and such? Is there anything else that need to be changed when going from utf8mb4 to utf8 again?

        • Ben Lobaugh (blobaugh) 4:54 pm on May 12, 2015 Permalink | Log in to Reply

          All I did was run that and it magic worked. Before running that script the db was complaining when I tried to import via sql files.

        • MBWD 4:00 pm on May 16, 2015 Permalink | Log in to Reply

          Hi Ben, I’m also on a Mac and have saved this script you so thoughtfully provided as a .sh file to run through Terminal, but when I run it I am getting an error saying “mysql: command not found”. Any idea what could be wrong?

      • dotwongdotcom 4:40 pm on May 12, 2015 Permalink | Log in to Reply

        Ben! Thanks for this! You may have just saved me a huge headache. I stumbled upon your post through a rabbit hole of clicking while trying to figure out a workaround for errors that I was encountering while using the migration tool BackupBuddy. My local dev is running WP 4.2, but the migration won’t allow utf8mb4 on the live site because their version of mysql. I’m a relative novice when it comes to these things… how would I implement your script to convert my local build’s tables back to utf8 so that I can migrate to my live server?

        • Ben Lobaugh (blobaugh) 9:09 pm on May 12, 2015 Permalink | Log in to Reply

          Copy it into a bash script on your local dev. Edit the variables for db access and run it from the terminal. This assumes you are on a unix based system such as Linux or OS X. I do not have Windows to know how to build a script for that.

    • zanozik 1:38 am on June 23, 2015 Permalink | Log in to Reply

      Server version: 5.5.19-cll
      MySQL client version: 5.5.33
      Upgraded to 4.2.2 and my wp “crashed”. Every option that was in unicode was either unavailable or missing non-latin chars. Pages and posts content was unviewable. Titles were gibberish.
      Since I use a web hosting (no root, or cpanel access) with remote phpmyadmin I logged in there to see that all of the tables collation have been changed to utf8mb4-general. Database collation was still utf8-general, strange, I thought.
      The hosting I use, let a user create/change a database in custom interface only. Rather popular solution. So I thought maybe the upgrade was only partly successful and the database was not charset was not actually upgraded, just collation changed. So I changed charset in wp-config, changed collations for each tables, and voalia – everything working again…
      Could that buggy force upgrade happen to “omg-so-professional” WordPress? I just dont know what to do, laugh or cry right now…

    • physalis 11:20 pm on July 2, 2015 Permalink | Log in to Reply

      @zanozik I had the exact same problem, only when I changed from a dev to the final server – every little special character was nuked, plus options and posts in the backend where cracked. I tried setting my dev server database, but to no avail (so I already thought it must be the files then).
      Your description helped me save a release date for that project tomorrow morning and thus quite some people around it ;). So thanks a bunch!

    • treavioli 8:34 am on July 10, 2015 Permalink | Log in to Reply

      I encountered this issue when I tried to export my database tables via phpmyadmin. I would get several errors and warnings about collation. I switched to Sequel Pro and exported/imported that way, but I’m still not without issues. My post editor won’t recognize posts/pages/etc I created before site migration. Nothing appears in the edit panel for either Visual or Text tab..

  • Gary Pendergast 6:20 am on April 7, 2014 Permalink
    Tags: , , , , wpdb   

    MySQL in WordPress 3.9 

    In WordPress 3.9, we added an extra layer to WPDB, causing it to switch to using the mysqli PHP library, when using PHP 5.5 or higher.

    For plugin developers, this means that you absolutely shouldn’t be using PHP’s mysql_*() functions any more – you can use the equivalent WPDB functions instead.


    There are a few different options for replacing the query functions, depending on what you want to do:

    As a drop in replacement to run a query that you don’t expect a return value from (i.e., an INSERT, UPDATE or DELETE query), use $wpdb->query(). This will always return the number of rows effected by the query.

    Alternatively, $wpdb->insert(), $wpdb->update(), $wpdb->delete() and $wpdb->replace() are all helper functions that will automatically escape your data, then generate and run the queries for you. Ideally, you should never need to write an SQL statement!


    If you have a SELECT query, for which you’d normally do a mysql_query() followed by a mysql_fetch_*(), WPDB lets you combine this into one function call.

    To get all of the results from a query that returns more than one row, use $wpdb->get_results() to return an array of objects containing your data.

    There are also some shortcut functions for common usage:

    If you only need a single row from your query, $wpdb->get_row() will return just the data object from that row.

    If you only need a single column from a single row, $wpdb->get_var() will return only that field.

    And if you need a single column, $wpdb->get_col() will return an array of all the data from that column.


    For a drop in replacement, you can use esc_sql(). That said, we strongly recommend switching to $wpdb->prepare(), instead. We have a pretty thorough tutorial available for $wpdb->prepare().


    If you need to get the Insert ID from the last query, $wpdb->insert_id is where you need to look.

    Updating your plugin to use WPDB will also future proof it for if we make changes to how WordPress connects to the database – we’ll always maintain backwards compatibility with the current WPDB interface.

    For more reading, check the WPDB Codex page, and #21663.

    If you’re using MySQL in a way that I haven’t covered here, please post it in the comments, we’d be happy to help you out!

    • Samuel Wood (Otto) 6:23 am on April 7, 2014 Permalink | Log in to Reply

      Note: $wpdb->escape() is deprecated. Please use esc_sql() instead. Or $wpdb->prepare(), of course.

      Also note that $wpdb->escape() is not a proper replacement for mysql_real_escape_string() to begin with, as it only does weak escaping.

    • Doug Wollison 12:09 pm on April 7, 2014 Permalink | Log in to Reply

      I though I heard it was going to switch to PDO, not MySQLi?

      • Gary Pendergast 12:30 pm on April 7, 2014 Permalink | Log in to Reply

        That’s still on the cards for a future release, but it will be a significantly bigger project. The primary goal with this change was to stop using ext/mysql on PHP 5.5+, where it’s deprecated.

    • Brian Layman 4:10 pm on April 7, 2014 Permalink | Log in to Reply

      Wow! Huge change, and with the MySQL extension being deprecated in PHP 5.5 it’s a good one. I am trying to remember if 5.5 REQUIRES MySQLi to be built in. Does anyone know? Should you also throw in a check for the existence of mysqli_connect?

      Also, are there plans to start preparing queries? (Not WP prepare but the database meaning of prepare.)

      • Gary Pendergast 3:06 am on April 10, 2014 Permalink | Log in to Reply

        We do check that mysqli_connect() exists before trying to use it. (See wpdb::__construct().)

        There are no plans for adding statement prepare in the near future, though it is something I would like to get to!

    • webaware 10:46 pm on April 7, 2014 Permalink | Log in to Reply

      Plugin writers sometimes call mysql_get_server_info() to get the raw version information that $wpdb->db_version() strips out. Since the database handle isn’t public (nor is the mysqli indicator), there isn’t a way to do this in 3.9-beta3. I’ve opened a trac in hopes we can get a new method added to class wpdb so that plugin writers can still access this raw version information:


      • Gary Pendergast 5:28 am on April 8, 2014 Permalink | Log in to Reply

        To summarise the ticket, for anyone using mysql_get_server_info() – the best option is to have a switch in your code for mysqli, we’ll look at expanding $wpdb->db_version() at a later date.

        if ( empty( $wpdb->use_mysqli ) ) {
        	$ver = mysql_get_server_info();
        } else {
        	$ver = mysqli_get_server_info( $wpdb->dbh );
    • Claudio 8:51 pm on April 13, 2014 Permalink | Log in to Reply

      I use mysql_connect to test DB connectivity. How should I replace it for WP3.9/PHP5.5 compatibility?

      • Gary Pendergast 12:13 am on April 14, 2014 Permalink | Log in to Reply

        To test the connection, you can use $wpdb->check_connection(), which will check that the connection is up, and try to reconnect if it isn’t.

        This is particularly useful for long running cron jobs, where the MySQL connection might drop out due to inactivity, but there’s no actual problem with the server.

    • Ross Seddon 6:08 am on May 5, 2014 Permalink | Log in to Reply

      My web site which was designed using a standard off shelf theme I’m advised by our web site managers they site is providing the response “Access denied for user ‘www-data’@’localhost’ (using password: NO).” They advise
      Quote: “the one statement in there is exactly the issue: For plugin developers, this means that you absolutely shouldn’t be using PHP’s mysql_*() functions any more – you can use the equivalent WPDB functions instead.”

      The developer how wrote the plugin that is used site wide on your site for the skin, uses this no longer available method. All there work needs to be updated to use the correct method of connecting to the database. We know what is required, just that the plugin / skin is none of our work, and it is time consuming to fix.”

      We been down now for 3 weeks in terms of accessing site. Is what your referring to this thread relative to my site problem site http://www.totallyoutdoors.com.au

      how time consuming is this problem?


    • lwall 1:07 pm on May 15, 2014 Permalink | Log in to Reply

      I am having troubles with admin permissions at different points.

      When clicking on “Posts”, I get error “Invalid post type”, or when trying to create a new post, there is no save box/button.

      It also happens when trying to change options in one of my themes in other places with plugins. I get “You do not have sufficient permissions to access this page.”

      For the most part, the back-end is functional, the front-end is fully functional.

      I am using appengine 1.9.3 and wordpress 3.9, python 2.7.6.

      I have uninstalled 1.9.3 and updated to 1.9.4, I have also accepted WordPress’ request to install 3.9.1, the problem persists.

      I have installed the 1.9.3, 3.9, 2.7.6 configuration on a different machine where appengine was never installed before and the same problem occurs.

      I am discarding plugins because there were no plugins in the separate machine.

      I had appengine 1.9.0 and 1.9.3 working with WordPress 3.8.1. The problem started a few days ago after a number of upgrades (from 1.9.3 to 1.9.4, and into wordpress 3.9.1).

      Could this extra layer to WPDB be the source of this?

    • Michael Simpson 1:49 pm on July 11, 2014 Permalink | Log in to Reply

      For a plugin, I would like to be able to do unbuffered queries (MYSQLI_USE_RESULT). This is to handle cases when there are a lot of rows being returned and I want to keep the memory footprint down. wpdb doesn’t support this well and it would be nice if it would.

      For now, I have to create a new wpdb object and directly call mysqli_query($wpdb->dbh, $sql, MYSQLI_USE_RESULT); It is not using MySQLi, then I need to call mysql_unbuffered_query($sql, $wpdb->dbh);

      To know which one to call, I would like to consult $wpdb->use_mysqli but I cannot because it is private and there is no accessor method. So I have to copy the code from wp-db.php to determine it.

      For a start, it would be nice to be able to access $wpdb->use_mysqli. Even better would be an API on $wpdb to do unbuffered queries.


      • Gary Pendergast 2:02 pm on July 11, 2014 Permalink | Log in to Reply

        You can access $wpdb->use_mysqli directly, because of the wpdb::__get() magic getter.

        There’s unlikely to ever be an API in WPDB for doing unbuffered queries. There are too many gotchas that will just cause maintenance problems.

  • Andrew Nacin 6:14 am on December 12, 2012 Permalink
    Tags: , , sql, wpdb   

    PHP Warning: Missing argument 2 for wpdb::prepare() 

    Hello plugin or theme author! You possibly found this post after searching the Internet for the error above: “PHP Warning: Missing argument 2 for wpdb::prepare().”

    So, this is a new warning in 3.5. No sites are broken, everything is fine as before. But, this is indeed something you need to look at, because you may be exposing your users to a possible SQL injection vulnerability. Now that’s no fun!

    First, if you’re a user and you want to get rid of these errors, you should turn off the displaying of errors in PHP. There are many ways to do this, such as in php.ini, .htaccess, etc. For this, you can just put this in wp-config.php. (Note that hiding errors on production sites is good practice anyway.)

    <a href='https://profiles.wordpress.org/ini_set' class='mention'>@ini_set</a>('display_errors', 0);

    If you’re a user, you can stop here. (If you need more help, please don’t comment here, try the helpful Support Forums.) Just be sure to send a link to this post to the developer of the theme or plugin referenced in the error.

    Now, developers: Here’s how $wpdb->prepare() is supposed to work:

    $wpdb->prepare( "SELECT * FROM table WHERE ID = %d AND name = %s", $id, $name );

    See how $id — an integer, presumably — was passed as the second argument? That corresponds to the first placeholder, %d. Then, $name (a string) was passed as the third argument, thus the second placeholder, %s. This makes sure your query is safe, and prevents something like little bobby tables. (Note: the comic is wrong, don’t sanitize — always prepare your queries.)

    The problem is, a number of people were calling $wpdb->prepare() with only one argument, like so:

    $wpdb->prepare( "SELECT COUNT(*) FROM table" );

    See, there’s no parameter (%d, %s, or for floats, %f) in this query. This happens to work fine, but the prepare call isn’t doing anything. You should instead the query directly, as there are no inputs.

    But here’s where the problem lies:

    $wpdb->prepare( "SELECT * FROM table WHERE id = $id" );

    See the problem? That query isn’t secure! You may think you are “preparing” this query, but you’re not — you’re passing $id directly into the query, unprepared. And this, right here, is why $wpdb->prepare() now issues a warning if it isn’t called with more than one argument. Because you can’t prepare a query without more than one argument. Here’s a correct example:

    $wpdb->prepare( "SELECT * FROM table WHERE id = %d", $id );

    This wasn’t a decision done lightly. We don’t like shoving PHP warnings into the faces of users and developers. But given the potential security risks, we wanted everyone to immediately look at how they are running queries. And, of course, always prepare them properly.

    For more: wpdb Codex reference, #22262, and [22429].

    • Samuel Wood (Otto) 6:17 am on December 12, 2012 Permalink | Log in to Reply

      Dangit. Stole my ottopress post for tomorrow. Now I have to come up with fresh, original content. 😉

    • Emil Uzelac 6:23 am on December 12, 2012 Permalink | Log in to Reply

      Good one, just submitted this https://wordpress.org/support/topic/warning-missing-argument-2-for-wpdbprepare-3 15 minutes ago. Let me link to this post as well, to help author out :)


    • chacha102 6:30 am on December 12, 2012 Permalink | Log in to Reply

      Because you can’t prepare a query with more than one argument. Here’s a correct example:

      I think you mean, you can’t prepare a query without more than one argument.

    • Brian Layman 6:56 am on December 12, 2012 Permalink | Log in to Reply

      Interesting.. So the rule of “Always use prepare on queries” is simply wrong.
      It should be “Always use prepare on queries that built with variable arguments.”

      I’d always thought it did further sanitization of the query string itself, but I suppose that would be really hard to do without blocking some valid query people would inevitably want.

      • Samuel Wood (Otto) 6:58 am on December 12, 2012 Permalink | Log in to Reply

        The *vast* majority of problems with this I’ve seen today had variable arguments, but were putting them directly in the strings, like the third code example there. Which basically means that prepare did nothing to protect them.

        Previously, prepare(‘string’) returned ‘string’. Now it returns ‘string’ and a warning that you are doing-it-wrong. :)

      • chacha102 7:03 am on December 12, 2012 Permalink | Log in to Reply

        If you check out the source code for wpdb::prepare it really isn’t that interesting.

    • Vitor Carvalho 11:17 am on December 12, 2012 Permalink | Log in to Reply

      Fantastic explanation Nacin 😉

    • Joost de Valk 2:14 pm on December 12, 2012 Permalink | Log in to Reply

      Had to chuckle a bit when I found this:

      Missing argument 2 for wpdb::prepare(), called in /home/example/public_html/wp-content/plugins/akismet/admin.php

    • rfair404 2:14 pm on December 12, 2012 Permalink | Log in to Reply

      Thanks Nacin, I started seeing these notices in the last few weeks on several plugins that I use. glad to know what’s going on here.

    • a6april 3:18 pm on December 12, 2012 Permalink | Log in to Reply

      Thanks Huge Andrew, I had no idea! I am always glad to learn something everyday! I appreciate the quick followup and all of the responses. Have a great day all!

    • Josh 4:31 pm on December 12, 2012 Permalink | Log in to Reply

      Extremely useful… and “juicy”! Thanks Andrew! Saved me tons of time. Nice to know you guys are on top of security. Thanks again!

    • nomadentech 11:29 am on December 13, 2012 Permalink | Log in to Reply

      This is works perfectly, i think similar error will found in another plugins, so we dont need to fear for updating WordPress core. 😀
      Thank you,


    • properwp 6:01 pm on December 13, 2012 Permalink | Log in to Reply

      Thank you very much! We’re using a modified version of a clunky plugin in the repo and we keep finding new, wonderful problems. Another one of those “probably should have started from scratch” situations!

    • Mark de Scande BlogLines 8:04 pm on December 13, 2012 Permalink | Log in to Reply

      The only thing here is that it should have been noted some were or it was and i did not see it or i did not read i had the same problem on my Wife site SuperBlogs.co.za but on BlogLines.co.za it all was perfect on SB i just added some dirty code to make it go away


      But thx for posting it here for us all to see

    • Nashwan Doaqan 5:32 pm on December 14, 2012 Permalink | Log in to Reply

      Thank You Andrew Nacin , I see many plugins have this PHP warning .
      I hope a good life for all plugins authors 😀

    • DigiproveDevelopment 12:51 pm on December 15, 2012 Permalink | Log in to Reply

      Correction: some sites WERE broken because the warning messages screwed up the buffer and prevented normal operation.

      Am I the only one who thinks it was the wrong decision for WordPress team to decide that warnings will be spouted out for a situation where previously there was not even a Notice-level message? Like many authors I turn on notice-level messages when testing. And the underlying change caused so much difficulty because of a widespread but incorrect assumption that wpdb->prepare did actually do something with standalone sql strings when it fact what it did was – nothing.

      Suggest that:
      a) WordPress automatic upgrade process at least gives a warning to users who are using plugins not marked as being compatible with new release (e.g. like Firefox does).

      b) More use is made of notice-level messages fo situations like this so that sites don’t actually break when WordPress changes

      c) All registered plugin developers are warned by email of situations identified in beta testing which require action

compose new post
next post/next comment
previous post/previous comment
show/hide comments
go to top
go to login
show/hide help
shift + esc
Skip to toolbar