Recently, I was checking the apache error logs of a project I was working on and found that it has lot of 404 errors for images. I used my apache error parsing script and found that most of the errors were because the production db was still having some references to images present in the stage machine.
I had to do a quick find and replace in the MySQL db and after a quick search came up with the following query, using the replace function.
UPDATE table SET field = REPLACE(field, 'stage-url', 'prod-url');
Since I was about to run this query on a prod db directly, I wanted to add another condition to replace only rows that contained the stage url as an additional precaution (of course after taking backup of the db 😉 ).
UPDATE table SET field = REPLACE(field, 'stage-url', 'prod-url') WHERE INSTR(field, 'stage-url') > 0;
Hope this query is useful for someone who also had a shock like me when they looked at the apache error logs 😉
Today, I learned a very valuable lesson. Make sure you always enable binary logs in MySQL. I would have lost atleast 3 months of effort, if it was not enabled. Luckily it was enabled and I was able to do a point in time restore after someone did a wrong click.
If you maintain any kind of MySQL server (even if it is just a stage machine) make sure you enable binary logs in it. It is very easy to do and when something bad happens, you will be happy that you enabled it.
How to enable it
To enable binary logs all you have to do is to enable the following two settings in your my.cnf config file.
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 100
The first setting specifies the location where the logs should be kept and the second setting specifies the number of days to keep the logs. The default value is 10, but I changed it to 100.
How to restore from binary logs
You can do a point in time restore by using the mysqlbinlog tool, which comes with MySQL. Refer to this excellent tutorial to find out how to use mysqlbinlog tool.
Just a quick note to let you know that O’Reilly is conducting a webcast on using CouchDB on Android phone tomorrow, Sep 22, 2010 at 10 AM PST (10:30 PM IST).
Agenda
Following is the agenda
Why CouchDB on a phone is awesome, and what you can do with it
Deploying existing CouchApps to Android CouchDB
Using CouchDB in native Android apps
Aaron Miller who works for Couch.io is going to conduct this webcast. If you are interested in Android or CouchDB, then you should definitely attend it.
You can register for the webcast from the event page.
Okay, I got to admit. My latest crush is CouchDB. 🙂
I found lot of people referring to CouchDB when they were talking about node.js which made to find out more about CouchDB. I read a couple of articles and then came to know that O’Reilly was having a webcast (in fact two), in which Chris Anderson, one of the core committers of CouchDB explains about it. I thought of posting the videos here, so that even you could get hooked up to CouchDB 😉
Introduction to Apache CouchDB
This is part one of the webcast. In this webcast, Chris gives a technical overview. He also describes some of CouchDB’s existing users. This webcast also had a question and answer session where Chris answered user’s questions.
This is part two of the webcast. In this webcast, Chris shows how to hack JQuery CouchApps, which is a p2p web applications that can be deployed anywhere there’s a couch DB.
This is a talk which Chris Anderson and Jan Lehnardt gave in JSConf 2009 titled “CouchDB to the edge”. They give a nice introduction to CouchDB and also explain about how to write offline web apps that can synchronize the data once they are online.
You can view the video in blip.tv. I have embedded it below for easy viewing.
What’s new in CouchDB 0.11 and 1.0
This is an upcoming webcast (again by O’Reilly) which will happen on June 22, 2010.
In this webcast, Jan Lehnardt will be talking about the new features that will be coming up in the latest version of CouchDB like Views, Replication, Authentication, Virtual Hosts and the Rewriter etc.
During my days with dreamhost, in order to access MySQL from my local machine, I used to add my ip to the allowed host list. Even though security is compromised here, I really liked to use HeidiSQL for accessing MySQL database server instead of the built in MySQL console. But after my move to SliceHost, I found a little trick using which I can continue to use HeidiSQL from my local machine without adding my ip to the allowed host list.
This nice little trick is called port forwarding. Let me show you how I configured PuTTY so as to enable port forwarding. First install MySQL and then configure SSH to use key based authentication and change the default port by following the articles at Slicehost. After installing MySQL and configuring SSH, download and install PuTTY from its download page. I recommend you to download the zip file containing all the files.
Then create a new session in PuTTY by entering the ip address and also the port. Then choose Connection -> SSH -> Tunnels. In the source port field enter a valid port number like 8600. In the destination field enter the value 127.0.0.1:3306. 3306 is the default port in which MySQL runs. The reason why I asked you to enter a different port in the source is that, in future if you run a MySQL server in your local machine for testing, it will clash with your port forwarding. Click the Add button and then start the session. Don’t forget to save the session.
Now open your favourite MySQL GUI client. Mine is HeidiSQL. In the connection settings, enter 127.0.0.1 as the Hostname and enter the port which you specified in the source field in PuTTY (8600) as port. Also enter your username, password, default database name and the click connect.
Now the request which goes to port 8600 of your local machine is forwarded to port 3600 of your MySQL server by PuTTY and you can safely use a GUI client for MySQL without adding any ip to the allowed host list. Note that it will work only when PuTTY is having the session opened.
I hope this is of help to you and let me know how it is working for you. Happy PuTTYing 😉
Recently I moved my WordPress blog to a new server. I took the dump of the old database and imported it into the new MySQL server. Everything was fine except that I stated getting some strange characters in my posts. For instance I was getting (Nov — 3rd — 2007) instead of (Nov – 3rd – 2007).
It took me lot of research and googling to find the solution and so I thought of sharing it here so that it would be helpful for others who might face the same problem.
First I raised a support request in WordPress, but didn’t get a reply. After some googling, I found that it was due to wrong character set in my new MySQL server. Instead of having utf8 as the character set the MySQL database server was running in the default latin1 character set.
I changed the character set in the MySQL my.conf file and also in the wp-config.php file of WordPress and re imported the tables. Even this didn’t solve my problem. Later I found that there were certain characters in my wp-posts table which were encoded in latin1 character set even though the table is set to utf8 character set.
I then exported the table using a tool called Heidisql (which is by the way an excellent alternative to the command line MySQL client). I then opened the sql file in a text editor and changed all instances of latin1 to utf8 (basically a find/replace). I saved the file and imported the tables again and the junk characters are gone. 🙂
So the lesson learned the harder way, KEEP EVERYTHING IN UTF-8, ABSOLUTELY EVERYWHERE, FROM DAY ONE. You’ll be glad you did some day.
One of my colleagues showed me this gem in Oracle and I thought of documenting it here so that it will be useful for everyone and also I can find it next time I need it by just searching my blog instead of Google. 🙂 Using this query you can find out the start time of the Oracle database server.
SELECT * FROM V$INSTANCE
In addition to the start time the query also returns other useful information like instance name, version, hostname etc. The hostname is particularly useful when TNSNAMES.ORA is playing tricks with you.
A friend of mine recently asked me how to select random rows from a table using a SQL query which let me to find a solution. If you are wondering why we need to select rows randomly from a table, consider a simple example of an online quiz which needs to display the questions in a random order for each person who is going to take the exam.
After a bit of research and googling I found the answer and then thought of sharing it here so that I can refer it at a later point of time and at the same time it may be useful for others who are searching for a similar solution.
In Oracle we have to use the sys_guid() function. The query will look like
SELECT col1, col2 FROM tblname ORDER BY SYS_GUID()
I came across a unique requirement at work for which I struggled a lot to write the SQL query. Then after some googling and tips from my DBA friends I finally came up with the query. I never came across this query before, so I thought of posting about it here, since some of you might benefit from it.
Since I cannot tell you the exact requirement or the table structure I mapped it with the classic emp table to illustrate the query.
Ok so consider the following table structure
SEQ_ID - unique sequence number
EMP_ID - unique emp id
DEP_ID - department id (from a master table)
SAL - salary
And now the requirement is to find the employee with the max salary in each department.
The query
SELECT dep_id, MAX(sal) FROM tbl_name GROUP BY dep_id
will give me the maximum salary in each department, but now the real problem is to find out the person with that maximum salary in each department.
So finally I came up with this query
SELECT * FROM tbl_name WHERE (dept_id, sal) IN (SELECT dep_id, MAX(sal) FROM tbl_name GROUP BY dept_id)
The reason why I found this syntax to be strange for me is that I have never used two parameters in the IN clause.
The above query works, (atleast for me in Oracle 9i) but I am not sure whether it is the most optimal way of doing it. So if any of you know a better solution then do let me know.
Update:Jeff in the comments has specified that the equivalent in SQL Server is
SELECT A,* FROM tbl_name A
INNER JOIN (SELECT dept_id, MAX(Sal) AS Salary FROM tbl_name GROUP BY dept_id) B
ON B.dept_id = A.dept_id AND B.Sal = A.Sal
I found SQLyog GUI – MySQL Front end tool while searching for a replacement for phpMyAdmin. Really cool… It is similar to Query Analyser which comes with SQL Server. Just now installed it and playing around it.
