Oct
27
2009
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
5 comments | tags: database, error, float, function, mysql, mysql query, problem, query, round, solution, sql, sum | posted in mysql, problems and solutions, Uncategorized
Oct
21
2009
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’.
Comments Off on Search the wordpress content management system database | tags: cms, content management system, mysql, mysql query, replace, search, wordpress, wordpress database | posted in mysql, wordpress
Oct
7
2009
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.
2 comments | tags: database, linux, mysql, network, networking, password, reset mysql root password, reset password, reset root password, root, sql | posted in linux, mysql, reference, Uncategorized