14 votes

"#1067 - Invalid default value for 'post_date'" when trying to reset AI after backup

The post_date default value is 0000-00-00 00:00:00. If you check the sql_mode variable like this: show variables like 'sql_mode'; ... it will show you the sql_mode variable, that will be sth like ...
Carlos Faria's user avatar
11 votes

How do I find all admin users using phpmyadmin?

We can generate the SQL with: $query = new WP_User_Query( [ 'role' => 'Administrator', 'count_total' => false, ] ); echo $query->request; that outputs: ...
birgire's user avatar
  • 67.1k
10 votes

"#1067 - Invalid default value for 'post_date'" when trying to reset AI after backup

You must to add this code at the top of your SQL SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET AUTOCOMMIT = 0; START TRANSACTION; SET time_zone = "+00:00";
Cristian Cartes's user avatar
9 votes

Redirecting to old domain after migration

Run the following when you are changing domain: UPDATE wp_options SET option_value = 'https://yournew.domain' WHERE option_name = 'siteurl'; UPDATE wp_options SET option_value = 'https://yournew....
crmpicco's user avatar
  • 831
8 votes

#1062 - Duplicate entry '1' for key 'PRIMARY'

in the export process from your server's PhpMyAdmin select Custom options, then - be sure to tick under Object Creation Options the next option: Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT ...
elicohenator's user avatar
6 votes

Redirecting to old domain after migration

Changing wp_options field will help a little, but sometimes your pages, posts and plugins will use absolute references to images and other files. In this case, you'll need to update the entire ...
Tone Williams's user avatar
4 votes
Accepted

How to Create a Admin User for A WordPress Site via MySQL (PHPMyAdmin)?

You need to run those below queries- INSERT INTO `your-wp-database`.`wp_users` (`ID`, `user_login`, `user_pass`, `user_nicename`, `user_email`, `user_status`, `display_name`) VALUES ('1000', '...
CodeMascot's user avatar
  • 4,527
4 votes

Making someone a WordPress administrator using PhpMyAdmin

Get access with phpMyAdmin to your WordPress database. In phpMyAdmin, click on the tab "SQL" in the top tab bar. Enter this SQL command (with your actual WordPress username instead of your_username) ...
tanius's user avatar
  • 163
4 votes
Accepted

How to replace values in Wordpress DB using phpMyAdmin

The data is serialized. In order to edit it, you need to unserialize it, edit, then serialize again, and save. You can do that with PHP. Check serialize and unserialize. You can check this tool as ...
Marcelo Henriques Cortez's user avatar
4 votes

WP CLI can't delete plugin related table

That is the responsibility of the author of the plugin. Amazingly some authors believe that it is wrong to delete information from the DB, which I personally find unbelievable, but I guess there are ...
Mark Kaplun's user avatar
  • 23.4k
4 votes

How to delete users with no order history through phpmyadmin sql?

Hector's answer works well, but if you have have a large number of users you need to remove (in my case, it was over 13,000 spam registrations) I find using this little utility script works wonders, ...
Chad Reitsma's user avatar
3 votes

SQL error #1062 - Duplicate entry '1' for key 'PRIMARY'

Your wp_comments table already has a comment with id "1". You will need to remove the existing entries. If you want to keep them, then remove the "id" field from all comments you're importing.
Nabeel Khan's user avatar
3 votes

How do I find all admin users using phpmyadmin?

SELECT u.ID, u.user_login, u.user_nicename, u.user_email FROM wp_users u INNER JOIN wp_usermeta m ON m.user_id = u.ID WHERE m.meta_key = 'wp_capabilities' AND m.meta_value LIKE '%administrator%' ORDER ...
Greg Much's user avatar
3 votes

"#1067 - Invalid default value for 'post_date'" when trying to reset AI after backup

You can use following command to change your default post_date:- ALTER TABLE `wp_posts` CHANGE `post_date` `post_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
Sumit Jangir's user avatar
3 votes
Accepted

I want to create a 300 multi-site wordpress network using subdomains

300 sites within a multisite shouldn't be a problem by itself. Just be aware that things scale out quickly when you are operating that many sites. In particular, the number of posts will grow fast, ...
joetek's user avatar
  • 313
3 votes

Connection refused on locahost after importing database?

Found the answer. I needed to update my siteurl and home in the database: https://stackoverflow.com/questions/27966733/can-not-connect-to-wordpress-in-localhost-get-http-localhost-neterr-connec
Aslan French's user avatar
3 votes
Accepted

AWS Bitnami Wordpress - SELECT command denied to user

This is something that has thrown me off in the past. You actually see the error that talking about the Privileges, but the actual error is that the database doesn't exist! You are searching for ...
Drupalizeme's user avatar
  • 1,606
3 votes
Accepted

Delete all post meta except featured image Using SQL

I rewrote the function from the link in your question to SQL. Keep in mind that this script will also clear the page-templates assigned to the pages. Make a backup of the wp_postmeta table first. ...
nmr's user avatar
  • 4,501
2 votes

How Do I Delete WordPress Posts Older Than 400 Days, From A WordPress Category

Addition note and warning: always always always take backups before doing anything in your database. It's very easy to do something you didn't mean to and lose tons of data, especially when dealing ...
phatskat's user avatar
  • 3,144
2 votes

unable to upload image locally

By far the easiest way on a Mac is to right click on "uploads" , select "Get Info", then change the permissions at the bottom of the "Uploads Info" window to include read and write for the appropriate ...
Paul Steinberg's user avatar
2 votes

How can I restore admin capabilities?

I made the database changes as instructed but when I signed in as an administrator I still could'nt alter users or pages. When comparing the database field for user roles I noticed it was different ...
Jens's user avatar
  • 21
2 votes
Accepted

Change sticky status of posts from phpMyAdmin

The sticky posts info is stored in the sticky_posts option in the options table. It's stored as serialized array e.g. a:2:{i:0;i:1272;i:1;i:1995;} You can modify it via the Options API but there's a ...
birgire's user avatar
  • 67.1k
2 votes
Accepted

"#1067 - Invalid default value for 'post_date'" when trying to reset AI after backup

I eventually solved this by deleting the faulty database, backing up again from the working database but exporting structure and data separately.
SinisterBeard's user avatar
2 votes
Accepted

Why am I unable to login to Dashboard after adding myself to admin via phpMyAdmin?

For several years already, WordPress does not use md5 to encrypt passwords. Instead, Portable PHP password hashing framework is used. That is why described method doesn't work anymore.
KAGG Design's user avatar
  • 1,842
2 votes
Accepted

Update slug (URL) of pending posts via phpMyAdmin

One disadvantage of using that SQL query is that the slug will only be updated when you really go edit the post. Plus, it might cause odd behavior when any plugin tries to do something with an empty ...
swissspidy's user avatar
  • 3,814
2 votes
Accepted

Can't update WordFence Options, clear data manually

I found a post on Stack Overflow advising that converting a column from blob to text should result in no data loss. I didn't really care about data loss since I wanted to remove the plugin data anyway,...
KingRichard's user avatar
2 votes

Where is the "default attribute" values located in the phpMyAdmin in Woocommerce?

Default attributes of WooCommerce variable products are stored as post meta in the database. You can find them in the wp_postmeta table, where the post_id column is the post ID of the parent product (...
Yashar's user avatar
  • 298
2 votes
Accepted

Changing root password in PHPMyAdmin for Wordpress Database when going live

The short answer is yes. Having a secure username and password is paramount to keeping your site secure.
Welcher's user avatar
  • 3,588
2 votes

How to delete users with no order history through phpmyadmin sql?

This is an expanded version of Chad's answer that does them in batches. It also adds a current timestamp, how long the script took, and how many records were deleted. <?php /*Load WP functions and ...
hrood's user avatar
  • 121

Only top scored, non community-wiki answers of a minimum length are eligible