The big nothing

So, I was actually using Microsoft Windows for quite a while. My first windows was Windows/286 which was nothing like the current Windows, but it was okay somehow. Today I just realized that I haven’t really been using Windows on any of my own machines and even though Vista was released three months ago, I haven’t seen it yet. It is sort of funny, but since switching to a Mac, I’ve been spending less time keeping the machine running – debugging, tweaking and doing other odd stuff – and more time doing actual fun work at the computer – like expanding my portfolio at istockphoto.

Comment spam

I hate comment spammers. I really do. The SCode captacha works, but the load the evil spammers create is pretty bad in itself. The comments were disabled a few hours, but they’re back now, after some counter measures has been applied.

Mysql: Change account password

Through the mysql client: 

update mysql.user set password=password('NEW_PASSWORD') where user='USERNAME' and host='HOSTNAME';
flush privileges;

Through the command line:

mysqladmin -u USERNAME -p CURRENT_PWD password NEW_PWD

Replace USERNAME, CURRENT_PWD and NEW_PWD with appropriate values.

Mysql: Delete orphan records

Finding records that do not match between two tables.

          CREATE TABLE bookreport (
            b\_id int(11) NOT NULL auto\_increment,
            s\_id int(11) NOT NULL,
            report varchar(50),
            PRIMARY KEY  (b\_id)

          );

          CREATE TABLE student (
            s\_id int(11) NOT NULL auto\_increment,
            name varchar(15),
            PRIMARY KEY  (s\_id)
          );

          insert into student (name) values ('bob');
          insert into bookreport (s\_id,report)
            values ( last\_insert\_id(),'A Death in the Family');

          insert into student (name) values ('sue');
          insert into bookreport (s\_id,report)
            values ( last\_insert\_id(),'Go Tell It On the Mountain');

          insert into student (name) values ('doug');
          insert into bookreport (s\_id,report)
            values ( last\_insert\_id(),'The Red Badge of Courage');

          insert into student (name) values ('tom');
 To find the sudents where are missing reports:

          select s.name from student s
            left outer join bookreport b on s.s_id = b.s_id
          where b.s_id is null;

              +------+
              | name |
              +------+
              | tom  |
              +------+
              1 row in set (0.00 sec)
 Ok, next suppose there is an orphan record in
 in bookreport. First delete a matching record
 in student:

       delete from student where s_id in (select max(s_id) from bookreport);
 Now, how to find which one is orphaned:


       select * from bookreport b left outer join
       student s on b.s_id=s.s_id where s.s_id is null;

     +------+------+--------------------------+------+------+
     | b_id | s_id | report                   | s_id | name |
     +------+------+--------------------------+------+------+
     |    4 |    4 | The Red Badge of Courage | NULL | NULL |
     +------+------+--------------------------+------+------+
     1 row in set (0.00 sec)
To clean things up (Note in 4.1 you can’t do subquery on same table in a delete so it has to be done in 2 steps):