Oct 27 2009

MySQL SUM() Doesn’t Play Well With Floats

I had to write some reports for some legacy software today and I was unpleasantly surprised with the results of my SQL queries. I was selecting dollar values and summing them to for the monthly spending of certain individuals. Easy enough right? I wrote a query something like this :


SELECT SUM(t.money_spent) as sum_of_spent,
c.customer_name 
from transactions t 
join customers c on t.customer_id=c.customer_id 
group by customer_name order by c.customer_name asc

I ended up getting numerical values that were 10 decimal places long with seemingly random numbers. After checking to make sure the database didn’t have any odd entries I stumbled on this bug report.
The ‘money_spent’ column had a data type of float, which is a waste, but I still don’t think that it should sum up incorrectly. When I select individual values I get proper two decimal results.
Apparently floats and doubles use floating point math, which deals with approximate values for numbers and can thus result in confusion like this. It seems that it isn’t really possible to store 0.1 in a column of type float. You can only store 0.00999999977648258. This behavior is a little silly but easily fixed by using the ROUND() function :

SELECT SUM(ROUND(t.money_spent)) as sum_of_money_spent,
c.customer_name from transactions t 
join customers c on t.customer_id=c.customer_id 
group by customer_name order by c.customer_name asc

Share

Oct 21 2009

Search the wordpress content management system database

WordPress is by far the most popular content management system for blog hosting. The wordpress content management system uses the mysql database. If you have a big site with a large number of posts then it can be handy to search the content of every post to find certain text. Sometimes you may even need to replace certain keywords with other keywords. As with most content management setups there is probably a plugin that will do just that, but it is far easier to just use basic sql if you know the structure of the wordpress database.
Within either phpmyadmin or mysqlyog (depending on what you are using) you can use this sql query to find the text that you are looking for:


select * from wp_posts where post_content 
like '%content management system%';

The ID that you get back is basically the page id. For example, if I query my database and get back an id of 13449 then that content will reside at http://codytaylor.org/?p=13449. Other useful columns are the post_content which is the content text of the post, post_name which is the title of the post, and the guid which is the full url (before mod_rewrite changes it) so you don’t have to copy and paste the id and append it to your url.

If you need to search and replace some text in more than one post then you can use this sql :


UPDATE wp_posts SET post_content = REPLACE (
post_content, 'content management system', 'CMS');

That SQL query will replace the ‘content management system’ with ‘CMS’.

Share

Oct 7 2009

Reset Mysql Root Password On Linux

If you have root access to a linux server and you don’t have the root mysql password, but need it, then you can easily reset the root mysql password in just a few commands. These commands probably differ depending on what linux distro you use. I was using Ubuntu 9.04 (Jaunty Jackalope) when I wrote this.

Firstly you will want to turn the mysql service off.


codytaylor@server:~$ sudo /etc/init.d/mysql stop
 * Stopping MySQL database server mysqld   

Now we restart the mysql server with the ‘skip-grant-tables’ option which basically allows anyone to do whatever they like. It’s usually preferable to include the ‘skip-networking’ option so that only localhost (you) have access to the naked database.

 codytaylor@server:~$ sudo mysqld_safe --skip-grant-tables --skip-networking &

Now all that is left is actually changing the root password. Log into the mysql monitor and change the root password.

codytaylor@server:~$ mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('password') WHERE User='root';
mysql> FLUSH PRIVILEGES;

Those commands will reset the root mysql password to ‘password’. Now you’ll probably want to restart the mysql service and have it run normally.

codytaylor@server:~$ sudo /etc/init.d/mysql restart

If you are using windows and you want to reset the mysql root password then check the mysql documentation.

Share