Tag Archives: mysql

 

Snippet! Change the year of all posts in a particular category to 2012 with a single SQL query (use with phpMyAdmin or the MySQL command line interface)

UPDATE wp_posts AS p
  JOIN wp_term_relationships AS tr ON tr.object_id = p.id
  JOIN wp_term_taxonomy AS tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
  JOIN wp_terms AS t ON tt.term_id = t.term_id
  SET p.post_date = REPLACE(p.post_date, YEAR(p.post_date), 2012)
  WHERE t.slug = 'my-category-slug' AND tt.taxonomy = 'category';

Obviously replace my-category-slug with the category you’d like to target and perform the same magic on the p.post_date_gmt field. Not entirely sure why you would use such a technique, but it was asked by Jim Edwards on Twitter.

Published 5 months ago with 2 comments tagged , ,


 

Did you know that you can give a hint to MySQL on which index to use? It’s called Index Hint and can be part of a query where you feel MySQL is doing a wrong choice (although that’s quite unlikely these days.)

SELECT * FROM table1 USE INDEX (your_index)
    WHERE col1 = 1 AND col2 = 2 AND col3 = 3;

Where your_index is the name of the index you’d like to use for this query. You can supply several comma-separated indexes and MySQL will pick the one it thinks is best. Alternatively you can tell MySQL to IGNORE INDEX too! Do benchmark though before making the final decision ;)

Published 7 months ago with 2 comments tagged , ,


 

Encode Entities Inside PRE Tags

Here’s a little Python script that searches through a given file for pre tags and encodes anything in between. This is useful for when escaping from syntax highlighting plugins and replacing every occurrence of the code shortcode with a pre tag.

import re, sys

# First argument is the filename, output is filename.encoded
filename = sys.argv[1]
f = file(filename)
output = open('%s.encoded' % filename, 'w+');

# Read the whole file, fire the regular expressions
contents = f.read()
expr = re.compile(r'<pre>(.*?)</pre>', re.MULTILINE|re.DOTALL)
matches = expr.findall(contents)

# Loop through each match and replace < > with &lt; and &gt;
for match in matches:
	contents = contents.replace(match, match.replace('<', '&lt;').replace('>', '&gt;'));

# Write output file and close both files
output.write(contents)
output.close()
f.close()

Most syntax highlighting plugins will encode all entities on the fly for you so when you stop using them your code might break. Also, most highlighting plugins will render your TinyMCE visual editor useless when working with code, and I think it’s quite common to work with code using the visual editor in WordPress. At least Twenty Ten and Twenty Eleven understand that ;)

However, as seen from the replacement part, I don’t really encode all entities but rather replace the greater than and less than symbols. It’s enough for most cases but if you need a real entity encoding you should use the cgi.escape function which is similar to htmlspecialchars in php.

Feed this script with your database dump and it’ll create a new file with an .encoded prefix which you can feed back to MySQL. Please note though that this script reads the entier input file which may lead to slow execution, high memory usage and swapping when working with large files. Worked fine on my 30 megabyte database though.

Published 7 months ago with 2 comments tagged , ,


 

Regex Replace in MySQL or lib_mysqludf_preg in Ubuntu Linux

I’ve been working a lot with MySQL lately, especially after the major theme and plugin upgrades on my blog. I was dealing with a bunch of content issues like redundant shortcodes and post meta, URL changes, images directories and more.

One simple solution would be to grab the database dump, perform various search and replace operations and then feed it back in, and my goal was to do that without data loss, without going offline, without sending files back and forth but mainly for learning purposes.

I found a UDF for MySQL called lib_mysqludf_preg and here’s how I got it to run on Ubuntu 11.04, somewhere in a temporary directory:

sudo apt-get install libpcre3-dev libmysqlclient-dev
wget http://www.mysqludf.org/lib_mysqludf_preg/lib_mysqludf_preg-1.0.1.tar.gz
tar -xf lib_mysqludf_preg-1.0.1.tar.gz
cd lib_mysqludf_preg-1.0.1
sudo ./configure
sudo make install
sudo service mysql restart

So, install some libraries upon which the UDF may depend, download the UDF archive, extract it, browse to what has been extracted, configure, install and restart MySQL (just in case.) If you’re reading this and it’s not 2011, make sure you get the latest version and read the release notes too.

At this point the UDF library is installed but the functions are not available yet, so log in to your MySQL command line, preferably as root and create your new preg_replace function like this:

CREATE FUNCTION preg_replace RETURNS STRING SONAME 'lib_mysqludf_preg.so';

Voila! And here’s how I removed a shortcode from all posts and pages:

UPDATE wp_posts
  SET post_content = PREG_REPLACE('/[my_shortcode*?.*?]/', '', post_content)
  WHERE post_content LIKE '%[my_shortcode%';

Make sure you backup your database first though before doing any of this. I did! But everything went fine. My next goal is to get the shortcode provided by a syntax highlighting plugin replaced with a <pre> tag. Hopefully I won’t break anything so good luck to me and thank you so much for stopping by!

Published 7 months ago with no comments tagged , , , ,


 

Unreplied Comments in WordPress

Dealing with comments. What a mess! I’ve been quite busy lately so I hadn’t had too much time to reply to each and every comment on my blog, but I’d really love too, seriously! The problem however is that there were times when I replied to somebody, and times when I hadn’t and now with this mess in my comments admin it’s impossible to find out ones I haven’t replied to.

Comments: Such a mess!

I was looking for a plugin but haven’t found anything good enough. Even hosted commenting services like Disqus and Livefyre seem to lack that. Ideally I’d like to work with comments like with e-mail — read them, mark as unread, flag, assign to somebody, etc. So I quickly drafted an SQL query that gave me a list of IDs of comments that have not been replied to excluding my own:

SELECT t1.comment_ID FROM wp_comments AS t1
    LEFT JOIN wp_comments AS t2 ON t2.comment_parent = t1.comment_ID
    WHERE t2.comment_ID IS NULL
    AND t1.user_id = 0
    AND (t1.comment_approved = '0' OR t1.comment_approved = '1')
    ORDER BY t1.comment_date_gmt;

I was shocked by the amount of rows it returned so I switched the “disable commenting on posts older than 14 days” checkbox to make sure that doesn’t happen again. You can always reach me on Twitter or e-mail, right? ;)

I’d love to see this as a beginning for a new WordPress plugin that would implement some goodies for handling comments, or maybe as a tip to the hosted commenting services to create new features. In any case, I’m now stuck with over a thousand comments I have to go through.

Published 7 months ago with 2 comments tagged , , ,


 

Published 7 months ago with no comments tagged ,


 

Quick Tip! If you’ve been using WordPress for a long time you might still be locked in to using a video embed plugin like I was a few hours ago. Yes plugins are great, but these days it’s part of the WordPress core. So how do you get rid of those YouTube and Vimeo shortcodes?

I haven’t found an easy way to do it and yes I’m still frustrated with the shortcodes concept in general, but there are times you simply can’t avoid them. You should be comfortable with MySQL, at least using something like phpMyAdmin to carry out the following query:

<code>UPDATE wp_posts SET post_content =  </code>
    REPLACE( REPLACE( post_content, '[youtube]', '' ), '[/youtube]', '' )
    WHERE post_content LIKE '%[youtube]%[/youtube]%';

And that did the trick for all my 20 posts and pages that included YouTube videos embedded in shortcodes. You can do the same for Vimeo and other services, but this won’t work if you used extra shortcode attributes. You’ll have to do it the hard way, i.e. regular expression search and replace through a MySQL dump file, ugh!

Published 7 months ago with no comments tagged , ,


 

How to Generate Quality Data for MySQL

We all had fun with the World Database, Sakila and the others when learning MySQL (see Example Databases), but it sometimes isn’t enough to run certain experiments, benchmarks within your own schema. Of course you could write a script that would generate junk data based on your column types and populate your database with a few thousand entries, but as it turns out, Benjamin Keen already did.

Meet Generate Data – a free and open source script written in php, generates quality data for your databases. Works with MySQL and, well, pretty much with any SQL compliant database I guess. What I liked about Generate Data is that you get to pick your columns, their types, and the sample data like names, last names, integer between two values, lorem ipsum (my favorite) and a bunch of others. This is why I said “quality data”.

There are a few issues I encountered, like trying to get 5000 rows gave me only 200, but such issues could easily be solved by downloading the source code and launching it locally with a few fixing (I wonder why Benjamin did such a bad job at documenting the whole thing). It took me a few minutes to fill up 30,000 rows of sample data, so who needs the World database anyway?

The script is being updated from time to time and new features are being added, not too fast, but they are. Let’s see where Benjamin takes this by the end of this year ;)

Published 2 years ago with 5 comments tagged , , ,


 

From MySQL GUI Tools to MySQL Workbench

I bet that some of you still work with phpMyAdmin and there are plenty of good reasons for that. Perhaps the main reason would be its mobility. Being run by a web server, phpMyAdmin is accessible from anywhere, without installing any extra software, even on the mobiles phones. And the second reason is of course security – most web hosting providers restrict external access to MySQL servers. But there’s absolutely no reason to get used to phpMyAdmin, as there’s software far better than that.

Most of you probably used the MySQL GUI Tools bundle, which is very similar to what phpMyAdmin is – database tweaking, user management, structure design, query builder, etc. Browsing up to the usual MySQL GUI Tools place a few days ago, I encountered the following message:

Users of the MySQL GUI Tools Bundle should plan to upgrade to MySQL Workbench. MySQL Workbench provides DBAs and developers an integrated tools environment for:

  • Database Design & Modeling
  • SQL Development (replacing MySQL Query Browser)
  • Database Administration (replacing MySQL Administrator)

So we’re now forced to upgrade to MySQL Workbench. Honestly, for a moment there I though that it’s just a naming issue, and that MySQL GUI Tools has gone so far, that the guys from Sun Microsystems decided to ship the bundle as a single product – MySQL Workbench, but I was wrong. Browsing the MySQL Workbench website I noticed the archive, which dates back to Novermber 8th, 2007! So it seems that they’ve just merged the two pieces.

MySQL Workbench is far more professional then the GUI Tools, but may be complicated at first sight. And the most funny thing about it is that the MySQL GUI Tools leads to a page to download the 5.1 community version of MySQL Workbench, which unfortunatelly does not have all the promised features (SQL development, data modelling, server administration), and there’s no home screen at all, which is shown on each and every screenshot related to the move!

I thought something was wrong, so I went back to the Workbench homepage, browsed the blogs, forums and downloads. It turned out that 5.2 is the one we were supposed to move to, but it’s still in beta, thus hidden in the downloads. After installing MySQL Workbench 5.2 I finally managed to get to the promising home screen, a little bit fancier than 5.1, and very similar to what we used to see in MySQL GUI Tools, plus the improvements.

Here are a few screenshots:

[nggallery id=19]

So I hope to see the 5.2 release very very soon, and, oh come on, drop the phpMyAdmin stuff, that’s for kids ;)

Published 3 years ago with 18 comments tagged ,


 

Cloud Tips: Automatic Backups to S3

In a previous post about backing up EC2 MySQL to an Amazon S3 bucket we covered dumping MySQL datasets, compressing them and uploading to S3. After a few weeks test-driving the shell script, I came up with a new version that checks, fixes and optimizes all tables before generating the dump. This is pretty important as mysqldump will fail on whatever step would cause an error (data corruption, crashed tables, etc), thus your uploaded to S3 archive would be kind of corrupt. Here’s the script:

filename=mysql.`date +%Y-%m-%d`.sql.gz
echo Checking, Fixing and Optimizing all tables
mysqlcheck -u username -p password --auto-repair --check --optimize --all-databases
echo Generating MySQL Dump: ${filename}
mysqldump -u username -p password --all-databases | gzip -c9 > /tmp/${filename}
echo Uploading ${filename} to S3 bucket
php /ebs/data/s3-php/upload.php ${filename}
echo Removing local ${filename}
rm -f /tmp/${filename}
echo Complete
<pre>

There you go. If you remember my previous example I stored the temporary backup file on Amazon EBS (Elastic Block Storage) which is quite not appropriate. Amazon charges for EBS storage, reads and writes, so why the extra cost? Dump everything into your temp folder on EC2 and remove afterwards. Don't forget to make changes in your upload.php script ($local_dir settings). Also, just as a personal not and to people who didn't figure out how to upload archives with data to S3, here's another version of the script which takes your public_html (www, htdocs, etc) directory, archives it, compresses and uploads to an Amazon S3 bucket:

<pre>filename=data.`date +%Y-%m-%d`.sql.gz
echo Collecting data
tar -czf /tmp/${filename} /ebs/home/yourusername/www
echo Uploading ${filename} to S3 bucket
php /ebs/data/s3-php/upload.php ${filename}
echo Removing local ${filename}
rm -f /tmp/${filename}
echo Complete

Oh and have you noticed? Amazon has changed the design a little bit, and woah! They’ve finally changed the way they show the Access Secret without a trailing space character! Congrats Amazon, it took you only a few months.

Published 3 years ago with 2 comments tagged , , , , ,



← older posts back to top