There is no easy way to know which queries failed to execute. You can log the unsuccessful queries to /var/log/messages for debugging purpose. Add the following function to your include file and then use log_sql_query function instead of regular mysql_query function.
$myquery='update amytest set id = 132 where id = 12;';
log_sql_query($myquery);
function log_sql_query($data)
{
$sql = mysql_query($data);
if($sql == FALSE){
//do some logging here
exec('logger "MySQL error at `date` QUERY:'. $data. ' "');
}
return $sql;
}
The entry in the log file will look something like this...
# tail /var/log/messages
Mar 30 12:26:03 localhost logger: MySQL error at Tue Mar 30 12:26:03 IST 2010 QUERY:update amytest set id = 132 where id = 12;
Labels: mysql tips
When the computer won't start, use any of the Live CD's available.
The options include 1) Knoppix 2) Puppy Live 3) Fedora 9
Once the machine starts, insert the pen drive and use the following commands.
# fdisk -l
Disk /dev/sda: 160.0 GB, 160041885696 bytes
255 heads, 63 sectors/track, 19457 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sda1 * 1 13 104391 83 Linux
/dev/sda2 14 3837 30716280 83 Linux
/dev/sda3 3838 10211 51199155 83 Linux
/dev/sda4 10212 19457 74268495 5 Extended
/dev/sda5 10212 15310 40957686 83 Linux
/dev/sda6 15311 17222 15358108+ 83 Linux
/dev/sda7 17223 17732 4096543+ 82 Linux swap / Solaris
Disk /dev/sdb: 2019 MB, 2019557376 bytes
19 heads, 18 sectors/track, 11533 cylinders
Units = cylinders of 342 * 512 = 175104 bytes
Device Boot Start End Blocks Id System
/dev/sdb1 24 11534 1968128 b W95 FAT32
# mkdir /mypend
# mount /dev/sdb1 /mypend
# cp somefile.zip /mypend
Labels: usability
#!/bin/sh
# check if any zip file in current folder is corrupt
> result.txt
find . -maxdepth 1 -name '*.zip' | while read myzip
do
unzip -tq $myzip
if [[ $? -ne 0 ]];then
echo "$myzip is not good" >> result.txt
fi
done
Labels: shell script
1) Python server
Serve the current directory at http://localhost:8000/
$ python -m SimpleHTTPServer
This one-liner starts a web server on port 8000 with the contents of current directory
If you have “index.html” or “index.htm” files, it will serve those, otherwise it will list the contents of the currently working directory.
2) Save a file you edited in vim without the needed permissions
:w !sudo tee %
3) login without password
# ssh-keygen
# ssh 111.222.333.444 'cat >> .ssh/authorized_keys' < /root/.ssh/id_rsa.pub
ssh-keygen command will generate the required file.
(either SSHv1 file identity.pub or SSHv2 file id_rsa.pub)
copy the file to the remove server so that next time you can log-in without a password.
4) Other commands:
diff from remote host
# ssh root@111.222.333.444 cat /home/develop/messages.txt | diff /home/develop/messages.txt -
Create and mount a temporary RAM partition
# mount -t tmpfs -o size=1024m tmpfs /mnt
Find out which programs listen on which TCP ports
# netstat -tlnp
Reset terminal
# reset
Labels: linux tips
Assume we’ve a table with 100 rows and we need to select all columns but one. The problem is that one actually has to type out all 99 required columns!
DELIMITER $$
DROP PROCEDURE IF EXISTS `selectAllButOne` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `selectAllButOne`(in_db_nm varchar(20),in_tbl_nm varchar(20),in_colm_nm varchar(20))
BEGIN
SET @stmnt= CONCAT('SELECT ',(SELECT GROUP_CONCAT(column_name) FROM information_schema.columns WHERE table_name=in_tbl_nm AND table_schema=in_db_nm AND column_name<>in_colm_nm), ' FROM ',in_db_nm,'.',in_tbl_nm,';');
PREPARE stmnt FROM @stmnt;
EXECUTE stmnt;
END $$
DELIMITER ;
http://kedar.nitty-witty.com/blog/ideas-for-select-all-columns-but-one-mysql-stored-procedure/
Labels: mysql tips
Here is an easy to create statements for loading data into mysql from text files.
#!/bin/sh
echo "SET FOREIGN_KEY_CHECKS = 0;"
for f in `ls -1 *.txt`; do
table=${f/.txt/}
echo "load data infile '`pwd`/$f' INTO TABLE $table;"
done;
http://www.electrictoolbox.com/load-data-mysql-foreign-key-constraints-2/
Labels: mysql tips
If I want to change the sequence of columns in a comma delimited file, I can either use sed or awk.
# cat mycomma.txt
a,b,c,d,e
12,32,43,54,54
as,ewr,tre,yy,dfg
But there is another way do this...
#!/bin/bash
input=$1
while read -r line
do
IFS=, read -r f1 f2 f3 f4 f5 <<<"$line"
# quote fields if needed
echo $f5 $f1 $f2 $f3 $f4
done <"$input"
# sh testme.sh mycomma.txt
e a b c d
54 12 32 43 54
dfg as ewr tre yy
Labels: unix case study
The timeout in seconds an InnoDB transaction may wait for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction will hang for at most this many seconds before issuing the following error:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
When a lock wait timeout occurs, the current statement is not executed. If you want the current transaction to roll back, start the server with the --innodb_rollback_on_timeout option, available as of MySQL 5.0.32
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout
Labels: mysql tips
Using shell like SQLLets say I have a csv file like this:
a,b1,12,
a,b1,42,
d,e1,12,
r,12,33,
I want to use grep to return only only the rows where the third column = 12. So it would return:
a,b1,12,
d,e1,12,
but not:
r,12,33,
Any ideas for a regular expression that will allow me to do this?
http://stackoverflow.com/questions/2373885/searching-a-csv-file-using-grep
_____
while IFS="," read a b c d
do
case "$c" in
12) echo "$a,$b,$c,$d"
esac
done <"file"
Labels: unix case study