Often you have the situation that you need to check if an table entry exists, before you can make an update. If it does not exist, you have to do an insert first.

The simple straightforward approach is this:

(The example is for an entry in the WordPress wp_postmeta table)

SELECT meta_id FROM wp_postmeta
WHERE post_id = ?id AND meta_key = ‘page_title’

If ResultCount == 0

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
VALUES (?id, ‘page_title’, ?page_title)

Else

UPDATE wp_postmeta SET meta_value = ?page_title
WHERE post_id = ?id AND meta_key = ‘page_title’

This is not too bad, but we could actually combine everything into one query. I found different solutions on the internet. The simplest, but MySQL only solution is this:

INSERT INTO users (username, email) VALUES (‘Jo’, ‘jo@email.com’)
ON DUPLICATE KEY UPDATE email = ‘jo@email.com’

Unfortunately, this the ‘ON DUPLICATE KEY’ statement only works on PRIMARY KEY and UNIQUE columns. Which is not a solution for my case.

You can follow the discussion here on Sql Recipes.

What worked best for me, was this solution:

INSERT INTO wp_postmeta (post_id, meta_key) SELECT ?id, ‘page_title’  FROM DUAL WHERE NOT EXISTS (SELECT meta_id FROM wp_postmeta WHERE post_id = ?id AND meta_key = ‘page_title’);
UPDATE wp_postmeta SET meta_value = ?page_title WHERE post_id = ?id AND meta_key = ‘page_title’;

It’s a bit more complex, but you can run that in one go and it will do the trick. If you adjust it a little it should even work on different database systems.

After upgrading WordPress to 3.0 and then to 3.0.1 I couldn’t save newly created posts anymore.

Creating new users didn’t work either.

Turns out there is a problem with the DB tables. For some reason some of the fields don’t have default values and if you try to save something, the save SQL operation fails.

You can easy check if you have the same issue:

Just add define(‘WP_DEBUG’, true); to your wp-config.php file and then you should see the error messages.

To fix it, just run this query:

ALTER TABLE wp_posts CHANGE COLUMN post_mime_type post_mime_type VARCHAR(100) NOT NULL DEFAULT ”;

Same for creating a new user. This is the error message:

WordPress database error: [Field 'user_activation_key' doesn't have a default value]

INSERT INTO `wp_users` (`user_pass`,`user_email`,`user_url`,`user_nicename`,
`display_name`,`user_registered`,`user_login`) VALUES (‘$P$BdvJNscOoULkBECWck8tL0′,’natasa@email.ch’,
‘http://www.supertext.ch’,'natasa’,'Natasa’,
’2010-09-10 15:08:08′,’Natasa’)

And this is the solution:

ALTER TABLE wp_users CHANGE COLUMN user_activation_key user_activation_key VARCHAR(60) NOT NULL DEFAULT ”;

Maybe there are other places where something similar is missing. I’m sure you can fix it in the same way.

Ever got the following error message after installing MySQL on a Windows Server:

ERROR 1045 (28000): Access denied for user ‘root’@'localhost’ (using password: YES)

Sounds like this is quite a common problem if you look check in google.

This issue could have several roots:

• The MySQL Service is not running
• Service is not configured to accept network connections
• Wrong password/username
• Firewall setup
• Wrong port (standard is 3306)

The mysql help actually has some good hints for these issues:

http://dev.mysql.com/doc/refman/5.0/en/access-denied.html

(From my own experience, port 3306 actually does not need to be open in order to use the MySql command line tool or the MySQL Workbench.)

My service was running and configured for tcp/ip, the firewall was disabled and the port was correct. So it had to be something with the password.

You can reset the root password like this:

1. Stop the MySQL service:
   sc stop mysql
2. Start MySQL with the –skip-grant-tables option, this disables the password checks. You might wanna start it in a different command prompt window.
   mysqld.exe –skip-grant-tables
3. Run the following commands:
   mysql -u root mysql
   mysql> UPDATE user SET Password=PASSWORD(‘your_new_password’) where USER=’root’;mysql> FLUSH PRIVILEGES;
4. Stop the Server:
   mysqladmin -uroot -pyour_new_password shutdown
5. You should be able to login now
   mysql -uroot -pyour_new_password

If this does not help, there is a long discussion in the MySQL forum about this issue:

http://forums.mysql.com/read.php?11,34014,34014#msg-34014

Good luck!

After not having updated WordPress for a while (we were still on 2.3), the new WordPress iPhone Application convinced us that it was about time.

Unfortunately, all my categories were missing after the upgrade. Seems like the wp_categories table got removed an replaced by wp_terms and wp_term_taxonomy. The entries are still there, just without name and descriptions.

Fortunately there are ways to fix it (if you have a backup, harhar).

David Cumps wrote a nice post about how to fix it manually, but if you have lots of Categories, you can do it automatically too (if you prepare of it).

First step (before the upgrade) is to save a version of the wp_categories table:

mysqldump -uUsername -pPassword wordpress_db
wp_categories > wp_categories.sql

After the upgrade you have to play it back and run the following two queries:

mysql -uUsername -pPassword
wordpress_db < wp_categories.sql

UPDATE wp_terms, wp_categories
SET wp_terms.name = wp_categories.cat_name,
wp_terms.slug = wp_categories.category_nicename
WHERE wp_terms.term_id = wp_categories.cat_ID

UPDATE wp_term_taxonomy, wp_categories
SET wp_term_taxonomy.description =
wp_categories.category_description
WHERE wp_term_taxonomy.term_id = wp_categories.cat_ID

If you didn’t make a backup of the wp_categories table, you can just load the whole backup again and give it a different name. When you run the 2 queries, just prefix the table names with the name of the backup DB.

Update: If you have subcategories, you can try the query I posted in my comment below. I didn’t actually try it myself, so be careful.

There are already tons of Pages dedicated to the tuning and benchmarking of MySQL. But one thing that took me a long time to figure out is who to easy figure out what makes a query fast and what not. The problem is that all queries are cached, so every time you run it, you get different results. The solution is

Select SQL_NO_CACHE * FROM mytable

The official documentation from MySQL is here. If you need some other good things, I can only recommend the MySQL Performance Blog.

You can also disable cache on a DB level with the Admin Interface under Health > System Variables > Memory > Cache or via query_cache_type in the config file. This way you can run your app normally and track down slow queries in the Slow Query Log.

http://www.petefreitag.com/item/390.cfm

There are tons of pretty enhanced scripts out there to make backup and also to copy them to FTP sites, but what I needed was just something really simple to have my daily backup saved somewhere.

The script below will dump your DB into the backup.sql file and copy it to an FTP server of your choice into the root directory of that user. Can’t be any simpler.

Here it is:

The BackupAndFTP.cmd file:

mysqldump -uuserName -ppassword DBName > backup.sql 

ftp -s:ftpcommands.txt ftp.hostname.com

The ftpcommands.txt file:

username
password
binary
put backup.sql
bye

Just put both code blocks into an individual file, replace the usernames and passwords (first for MySQL and then for the FTP access), replace DBName with the name of the database you wanna save and that’s it.