HOW TO ROLL BACK COMMITS TO AN EARLIER VERSION OF A REPOSITORY IN SVN
I’ve always wanted to know how to do this, and for some reason I always had a hard time finding out how. I needed to rollback a change I had committed to my SVN repository. The way you rollback to an earlier version of your repository is to do a reverse merge. Here’s the example off the SVN site. It will do a reverse merge, and roll back the commit you made in version 303.
DELETE FROM TABLE USING A JOIN
MySQL won’t let you delete from a table using a subquery that references itself. Fair enough. To get around this, up till now, I’ve used temporary tables. However, I’ve never really liked it, and I’ve always wanted a JOIN delete. Well, apparently it exists. I’m cleaning out some data from some really old database dump. I needed to preserve the data in the article table, but remove the rows in the city table based on a column in the article table.
AWK DOES NOT WORK AS EXPECTED WITH DOUBLE QUOTES
Of course, I felt like an idiot after being completely confused by this for about half an hour. Consider this: awk "{print $1}" somefile.txt This does not work as I had expected. The reason is because the $1 is evaluated within double quotes, (not single quotes). Yes, it’s a rookie mistake, but I never claimed to be the awk master. So, always write: awk ‘{print $1}’ somefile.txt and you will avoid a headache.
USING A RESULT SET TO CALL STORED PROCEDURES FOR AD HOC QUERIES
Using mysql -e’s feature, combined with awk and xargs, I was able to call an existing stored procedure repeatedly for a resultset. Yes, I could have written another stored procedure to do this, I realize. But I guess I like doing things the hard way. Either that, or this is just less code. Or I wanted the awk and xargs practice. Whatever. mysql database -e "select id from category where foreign_key in (2771, 2769, 2766, 2772, 2767)" | awk -F| ‘{print $1}’ | xargs -ivar mysql database -e "call move_category(var, 5666)"
10 (VERY) BASIC TIPS FOR PHP WEB APPLICATION SECURITY
Barebones list for the things you should be looking for when developing a web app in PHP. Some of it applies to all web apps, not just PHP. Use HTML_Safe (or a similar javascript stripping library) to check for and remove javascript when you’re accepting data that will be output to a page. Check every get and post variable for validity. Every web site has url like “dosomething.php?id=3”. Make sure that id you’re accepting is actually a number (or whatever type you’re allowing).
WHAT CAUSED THAT LOAD SPIKE?
Every now and then, we find that we will have a sudden increase in the number of apache processes, load average will spike up, and then go back down to normal. In rare cases, we will see the same thing happen, and the load avg spike WAY up, all queries appear locked up, and the server must be rebooted. I am looking for ways of determining what caused this. I should note that it happens extremely rarely, and has never shown up in a load test.
SHOW CREATE TRIGGER - NOPE
As of MySQL 5.0 there is no SHOW CREATE TRIGGER which is pretty annoying. I don’t like using “show triggers like “, so I figured I’d write a wrapper around mysqldump. For the record, this was a huge pain the ass. I did this on Fedora, GNU sed version 4.1.5. You need to have your password stored in ~./my.cnf for this to work, I believe. I haven’t tried it without it.
MYSQLDUMP TIPS BY CRAZYTOON
Our sysadmin has a nice blog post with a few tips for using mysqldump, especially if your database is used for more than a basic site, or if you have stored procedures and/or triggers.
COUNT(1) VS COUNT(*) - ANY DIFFERENCE?
A while ago when I started my first job in LA, I was using MSSQL. I was told to never use count(), and rather to use count(1). Several people insisted this performs better than count(), and since I really didn’t care to argue or look into it, I just started doing it. Fast forward to today, where I am still writing count(1), but on MySQL. I’m at a different company now, but working with a lot of the same people.
WHY HAVE REPLACE INTO AND INSERT ... ON DUPLICATE KEY UPDATE?
REPLACE INTO will actually perform a delete and then an insert, while INSERT … ON DUPLCIATE KEY UPDATE will perform an update (as the name suggests). I would think the latter would be faster. I have not done any performance testing between the two, but it only seems logical the update would be faster than the delete/insert. Please correct me if I’m wrong. Since the two statements both end up with the same result, I’m not sure yet what the benefit of REPLACE into it.