Category Archives : MySQL

Apache2/MySQL on Ubuntu Configuration

Once you’ve installed apache2 and mysql on your server, you might want to try these configuration changes to make it perform a bit better: In /etc/mysql/my.cnf key_buffer = 16K max_allowed_packet = 1M thread_stack = 64K table_cache = 4 sort_buffer = 64K net_buffer_length = 2K And in Apache2′s config, /etc/apache2/apache2.conf, find the section for MPM prefork if yo have it and: StartServers 1 MinSpareServers 3 MaxSpareServers 6 ServerLimit 24 MaxClients 24 MaxRequestsPerChild 3000 The above are just some things to try if you encounter a situation where apache is using too much memory, or the same for mysqld.

HowTo Knock it off with crazy MYSQL queries and the qTranslate plugin unistall

We’ve all done it. Either we learn some tricks in MYSQL, or read a cool post about some obscure programming trick you can do with SQL and decide to try it in a pinch on a live database. Just don’t do it. 9 times out of 10 you have access to SSH, and PHP, and you can write a script that will do what you want. You can also “test” it on a small set of inputs and have it echo out the necessary queries to the console instead of sending them to MYSQL. This is called the poor man’s unit test, or poor man’s BDD. Now, on one word press site, before the whole wordpress MU thing, we made the mistake of using qTranslate, well, it’s not evil, it’s just not good, so we wanted to stop using it, however, there’s no real way out of it. Search around and you find this post on their boards DON’T YOU EVEN DARE DO THIS. It’s just bad programming. You will probably hose your database doing this. Instead, let’s look at a more sensible solution, using your favorite scripting language, Ruby or PHP will do fine, I’ll choose PHP. $connection = mysql_connect(‘’,’user’,’pass’); mysql_select_db(‘yourdb’); $prefix = ‘your_table_prefix’; $res = mysql_query(“select * from {$prefix}posts limit 5″); //limit for testing $wanted_language = ‘en’; function split_text($text,$quicktags = true) { //The following code is kluged from the actual qTranslate Plugin $split_regex = “#(<!–[^-]*–>|\[:[a-z]{2}\])#ism”; $current_language = “”; $result = array(); $blocks = preg_split($split_regex, $text, -1, PREG_SPLIT_NO_EMPTY|PREG_SPLIT_DELIM_CAPTURE); foreach($blocks as $block) { if(preg_match(“#^<!–:([a-z]{2})–>$#ism”, $block, […]

Saving Array to MySQL Table

I thought that I would start by making a few posts about some common task, especially in php. One thing that comes up often, id how to dynamically munge and save an array, for instance from $_POST, to the database? The answer is easier than you might think, first, let’s create a munging function, which you can extend for error checking and data sanitization: function munge($array) { if (is_array($array)) { foreach ($array as &$a) { //we keep the key in case we want to do something special here, like verify an email //address or some other field. $a = munge($a); } } else { $array = mysql_real_escape_string($array); $array = “‘$array’”; } return $array; } Munge is an old computer term, sometimes called a backronym: Modify Until Not Guessed Easily, but is usually defined as: iterating over data and altering a piece of data. Now we need to collect and prepare the field names. Now you could just use array_keys, however, It’s better and less bug prone to write your own prep function for the field names. We’ll also implement a whitelist of fields that are allowed to be set into the database. There are many reasons to do this, functional and security wise. I.E. you don’t really know what’s been posted, and it could case an error. $fields_whitelist = array(‘name’,’date’,’message’); function keys_for($array) { global $fields_whitelist; foreach (array_keys($array) as $key) { if (!in_array($key,$fields_whitelist)) { continue; } $keys[] = “`$key`”; } return $keys; } Now that we’ve done that, we need to write something to collect up […]