A web development/programming blog providing info, tips, and tricks on programming languages, scripting, Linux, MySQL and more
MySQL
What I learned from my first OWASP meeting (or why I will never use PHP Nuke)
Jul 6th
I just got home from attending my first meeting of the Phoenix chapter of OWASP. WOW!!! That’s all I have to say. The guest speaker was Mike Brooks, currently the top answerer and asker of security questions on Stack Overflow, who will be giving the same talk at the upcoming DEF CON 18. Mike gave an amazing presentation on chaining vulnerabilities in order to bypass layered security systems and ways of obtaining wormable remote code execution on a modern LAMP platform.
Read the rest of this entry »
Improve the performance of the WordPress plugin Statpress (and your blog)
Jul 3rd
I haven’t hid my feelings about the poor performing StatPress plugin for WordPress. However, performance issues aside, I will say that the information this plugin provides is useful, and detailed. I’ve been running this plugin for 8 months now and have a good sized data set (125,000+ rows of data). While I myself have not experienced as many issues with this plugin as I have seen on other blogs, it is mainly because I am running my blog on a virtual dedicated server as opposed to shared hosting. I have seen smaller data sets than mine cause problems on shared hosting servers.
Read the rest of this entry »
Improve Your WordPress Blog’s Performance With this Database Hack
Jan 9th
**UPDATE**: After further research of the query below, I have found some additional information. The query was first introduced in WP 2.3 and is used for comment flood protection. I am currently running WP 2.9.1 and do not have the issue as the query has been modified and using a proper index to assist with the query speed. If you are using the latest version of WP, you won’t have this problem. I am still trying to find exactly when the query was changed so you all can know where you stand. However, I know that some people don’t upgrade their WP version due to changes they have made which upgrading will break. If you are on an older version of WP and have a large number of comments (tens to hundreds of thousands), this query will help improve your comment post times. If you are familiar enough with MySQL, look at the wp_comments table in your database. If there is an index on the `comment_date_gmt` column, you are ok. If not, read on, and run the query below as adding an index to the `comment_date_gmt` column won’t work as your query does not have that column in it’s WHERE clause.
One of daily responsibilities as a database administrator is maintaining the health of our shared hosting environment. In doing so, I deal with plenty of WordPress blogs daily. The one thing I have noticed is that many WordPress plugins are very poorly designed and can cause problems in a shared hosting environment. WordPress at it’s core though, is very well designed, although I have noticed some areas of possible improvement. As I come across common issues, I will post the fixes for them here for all to use.
Read the rest of this entry »
What does “Impossible WHERE noticed after reading const tables” mean?
Dec 9th
When it comes to basic query optimization and analysis, in my opinion, there is no better tool than the MySQL EXPLAIN command. It can prove to be an invaluable tool for deciphering the MySQL query execution plan, or the way that MySQL is optimizing and executing the query. It will show you the type of SELECT being made on the table, the join type, possible keys, used keys, rows analyzed and more. I won’t get into the nitty gritty of EXPLAIN in this post, but if you are using it, on occasion you may get the following output:
+-----------------------------------------------------+ | Extra | +-----------------------------------------------------+ | Impossible WHERE noticed after reading const tables | +-----------------------------------------------------+
mysqldump: Got error: 2008: MySQL client run out of memory when retrieving data from server
Dec 7th
I came across this error today while at work. While trying to process a MySQL dump of a database of approximately 8 GB in size, I got the following error:
mysqldump: Got error: 2008: MySQL client run out of memory when retrieving data from server
This occurred on a MySQL 4.1 server. To get around this, you will need to use the -q switch as part of your mysqldump command.
This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.
(source: MySQL :: MySQL 3.23, 4.0, 4.1 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program)
Your full command should look something like this:
$ mysqldump -u user -p password -q database > outfile.sql
12 Quick and Easy MySQL Tricks
Dec 5th
In my first two months as a MySQL DBA, I have picked up a number of tips and tricks that have helped me in my daily job. In this post, I share them here with everyone. Hopefully they will help you as much as they have helped me.
Read the rest of this entry »
Top 20+ MySQL Best Practices
Dec 4th
I came across an excellent article a couple days ago in my NetTuts+ RSS Feed titled “Top 20+ MySQL Best Practices”. In the article, author Burak Guzel gives some excellent MySQL tips. Due to my argumentative nature (not really) I made a few comments on the post that you can see at the bottom about 1 or 2 of the tips. The tips were by no means wrong, I just wanted to add some clarification and caveats to them. A couple of the tips, I have been using every single day in my job and they have proven invaluable. The article is excellent and I highly recommend reading it.
Read the rest of this entry »
Avoid WordPress StatPress plugin like the plague!
Dec 2nd
** Update: I have released my first set of updates to the StatPress plugin. Check out the post for code to improve your blogs performance of StatPress
The WordPress plugin StatPress is an absolutely horrid plugin to use. Yes, I said it…horrid! After seeing many people praising this plugin on their sites, this is a bold statement.
Read the rest of this entry »
Temporary Tables are Bad!
Dec 1st
This article was written by a co-worker of mine and I wanted to share with everyone else.
Temporary Tables. What are they? Why are they bad? You might think you don’t explicitly use any, but in actuality you may be using them all the time.
Temporary tables come in two flavors. The kind you know you’re making, and the kind you don’t. You know you’re making a temporary table when you use the CREATE TEMPORARY TABLE syntax. On the other hand, there are lots of other cases in which MySQL will create a temporary table for you, in the background, to help out the query execution to get you your results.
Read the rest of this entry »
MySQL Quick-Tip: Target specific versions of MySQL with conditional comments
Jul 14th
Posted by Mark Stoecker in MySQL
No comments
I was working on a project over the weekend that required me to run some code relating to MySQL privileges across a large number of MySQL servers all running various versions of MySQL between 4.0 and 5.0. My initial efforts at this entailed writing out the version specific code and targeting the version with bash thusly:
This method involved a lot of code duplication. Yesterday, a co-worker mentioned using MySQL version specific comments, which I had seen before, but never fully understood.
Read the rest of this entry »