MySQL Default DATETIME Value – A Quick Rant
Nov0
I was reviewing a year old code which I am adding a feature to. It so happened I came to a familiar issue about having two timestamp/datetime columns, one which should have the CURRENT_DATE / NOW() as default value and the other with an 'ON UPDATE CURENT_TIMESTAMP'. Examine the simple structure below:
CREATE TABLE `stories` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`title` VARCHAR( 255 ) NOT NULL ,
`text` TEXT NOT NULL ,
`creationdate` DATETIME NOT NULL ,
`lastupdate` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = MYISAM
When you want to store stories, you would also want to record when it was originally created as well track the last time it was updated. The problem here is that adding a `DEFAULT NOW()` clause will not work for the `CREATE TABLE` query above as it is not supported. So when your insert a new story you will have to explicitly add a `NOW()` function for the `creationdate` row so it will reflect the current date as creation date. This should've been a simple schema functionality, turns out after more than a year MySQL seems to ignore for some reason.
Go on have yourself a read here http://bugs.mysql.com/bug.php?id=27645
How about you, how many times have you have to work around this from your application code?
FreeBSD NFS Server and CentOS NFS Client
Dec0
We were recently migrating a busy site from an aging FreeBSD 5.4 and we cannot disrupt operations while switching platforms from FreeBSD to CentOS. So part of the plan was to share via NFS media files from the old FreeBSD server to the new CentOS.
Here are the steps that I had to do:
- Modify
/etc/exportsfile to include directories you want shared. Ours look something like this:/usr/home/web01/images /usr/home/web01/swf 192.168.0.4
Notice the two consecutive directories. From the man pages, when mounting two directories from the same mountpoint or filesystem it will go the same line on the exports file or you'll get errors like:
Dec 8 10:20:42 web01 mountd[5900]: can't change attributes for /usr/home/web01/swf 192.168.0.4 Dec 8 10:20:42 web01 mountd[5900]: bad exports list line /usr/home/web01/swf 192.168.0.4
-
Restart service on the server. First stop, nfsd.
/etc/rc.d/nfsd stop
You have to make sure that mountd is stopped as well.
/etc/rc.d/mountd forcestop
Then kill rpcbind, I do this by finding the PID for rpcbind and killing it manually.
ps aux|grep rpcbind
It should return something like:
root 6087 0.0 0.1 4776 1248 ?? Ss 10:28AM 0:00.01 rpcbind
Then do the killing:
kill -9 6087
Then we start these processes in reverse order:
rpcbind /etc/rc.d/nfsd start
Starting nfsd should start mountd as well.
- Verify your exports are properly listed
showmount -e
Should give you something like:
Exports list on localhost: /usr/home/web01/images 192.168.0.4 /usr/home/web01/swf 192.168.0.4
- Now if you use
hosts.allowandhosts.deny, make sure that the NFS client is allowed on the NFS services. We have these on our/etc/hosts.allownfs : 192.168.0.4 : allow mountd : 192.168.0.4 : allow rpcbind : 192.168.0.4 : allow
- Now, before mounting on or CentOS client machine we verify first wether the NFS exports are visible:
rpcinfo -p 192.168.0.3
You should get something like:
program vers proto port 100000 4 tcp 111 portmapper 100000 3 tcp 111 portmapper 100000 2 tcp 111 portmapper 100000 4 udp 111 portmapper 100000 3 udp 111 portmapper 100000 2 udp 111 portmapper 100000 4 7 111 portmapper 100000 3 7 111 portmapper 100000 2 7 111 portmapper 100005 1 udp 861 mountd 100005 3 udp 861 mountd 100005 1 tcp 767 mountd 100005 3 tcp 767 mountd 100003 2 udp 2049 nfs 100003 3 udp 2049 nfs 100003 2 tcp 2049 nfs 100003 3 tcp 2049 nfs - So, our exports are visible we can now mount those shares.
mount -t nfs 192.168.0.3:/usr/home/web01/images /usr/home/web02/images mount -t nfs 192.168.0.3:/usr/home/web01/swf /usr/home/web02/swf
- That should do it, you should be able to see your mount points and browse files within them.
Questions welcome!