17
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 ...
- 477
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:
...
- 67.6k
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";
- 101
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....
- 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 ...
- 269
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 ...
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) ...
- 163
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', '...
- 4,537
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 ...
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 ...
- 23.5k
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, ...
- 79
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
- 37
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 ...
- 81
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, ...
- 333
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
- 235
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 ...
- 1,616
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.
...
- 4,521
3
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 ...
- 131
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 ...
- 3,164
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 ...
2
votes
How to manually delete post from database without causing conflicts?
Expanding on @Brian Fegter's answer Above you could use the below plugin to remove posts in bulk. It creates a WP admin page with a text area for ids of pages/posts to remove.
<?php
/*
Plugin ...
- 153
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 ...
- 21
2
votes
"#1067 - Invalid default value for 'post_date'" when trying to reset AI after backup
First run
ALTER TABLE `wp_posts`
CHANGE `post_date` `post_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
CHANGE `post_date_gmt` `post_date_gmt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
...
- 21
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.
- 1,198
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.
- 1,842
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 ...
- 67.6k
2
votes
"#1067 - Invalid default value for 'post_date'" when trying to reset AI after backup
The answer of Carlos Faria is perfect !
But here is a small code you can use if you are on PhpMyAdmin and doesn't want to mess with the SQL configuration.
Here I gonna change the sql_mode value but ...
- 242
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 ...
- 3,894
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,...
- 177
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 (...
- 298
Only top scored, non community-wiki answers of a minimum length are eligible