Libre Things

CV Diaspora
fr

Sunday 13 November 2011

Import CSV file to MySQL

Here is the little sh script I made to do that. The LOAD DATA INFILE command exists but is not capable of creating the table structure.

#!/bin/sh

MYSQL_ARGS="--defaults-file=/etc/mysql/debian.cnf"
DB="mbctest"
DELIM=";"

CSV="$1"
TABLE="$2"

[ "$CSV" = "" -o "$TABLE" = "" ] && echo "Syntax: $0 csvfile tablename" && exit 1

FIELDS=$(head -1 "$CSV" | sed -e 's/'$DELIM'/` varchar(255),\n`/g' -e 's/\r//g')
FIELDS='`'"$FIELDS"'` varchar(255)'

#echo "$FIELDS" && exit

mysql $MYSQL_ARGS $DB -e "
DROP TABLE IF EXISTS $TABLE;
CREATE TABLE $TABLE ($FIELDS);

LOAD DATA INFILE '$(pwd)/$CSV' INTO TABLE $TABLE
FIELDS TERMINATED BY '$DELIM'
IGNORE 1 LINES
;
"

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

(See comment: "Posted by John Swapceinski on September 5 2011 5:33am")

Friday 22 October 2010

A solution to the umask problem: inotify to force permissions

Finding a good solution for sharing files between Linux users is a nightmare.

If using a unique UID is not a problem, it's the most simple solution. All clients access files with the same UID. This way you cannot know who does what, and users cannot fine tune access rights.

The problem: default umask is ALWAYS 0022, so that any created file will get rw– r–– r–– permissions. Only the owner can write. Nobody else. To share files, a group must have write access.

You can change the umask. For command line, you set it in .bashrc or .profile, or /etc/profile for all users. For a SFTP share, you can set it with a trick. For Apache HTTP server, you can set it with /etc/apache2/envvars under Debian.

If file sharing is only done via on service, changing umask is simple, otherwise it's not that easy. And even if you change umask for all services, nothing is perfect: for example it doesn't work with Nautilus and SFTP. Some clients drop files and issue a chmod right after: the hell. You can also try the power of POSIX ACL to force permissions. But problems still remain with some clients.

And for the umask, maybe you don't want all files to be dropped group writable. Maybe you want more granularity on permissions.

So I abandonned the idea of fixing the problem at the source in favor of some trick AFTER file creation. The most simple solution is the cron task: every X minutes, run chmod -R g+w on the directory. This way permissions are not fixed immediately, but asynchronously. And it adds a (very) little more load to your system.

My solution uses inotify to listen for file changes and force permissions when files are created:

aptitude install inotify-tools

And the magical command:

inotifywait -mrq -e CREATE --format %w%f /tmp/mytest/ | while read FILE; do chmod g=u "$FILE"; done

UPDATE 2010-10-30 To support spaces at the end of filenames, and backslashes, use:

inotifywait -mrq -e CREATE --format %w%f /tmp/mytest/ | while IFS= read -r FILE; do chmod g=u "$FILE"; done

Thanks to vitoreiji (see comments)

inotifywait listens for events in the /tmp/mytest directory. When a file is created, it's displayed on standard output. Then each fileline is read by the while loop and permissions are changed. g=u gives the group the user's permissions (with g+w, if the user drops a file with rw– ––– –––, permissions will be rw– –w– –––).

You can now test file/directory creation and copy. mkdir -p a/b/c/d/e shoud also work.

Finally, add it in a boot script:

vi /usr/local/bin/inotifywait.sh && chmod +x /usr/local/bin/inotifywait.sh
#!/bin/sh
# Take the directory name as argument

inotifywait -mrq -e CREATE --format %w%f "$1" | while read FILE
do
	chmod g=u "$FILE"
done
vi /etc/init.d/inotifywait.sh && chmod +x /etc/init.d/inotifywait.sh
#! /bin/sh

case "$1" in
  start|"")

	rm -f /tmp/inotifywait.log
	/usr/local/bin/inotifywait.sh /path/to/dir/ >/tmp/inotifywait.log 2>&1 &
	
	;;
  restart|reload|force-reload)
	echo "Error: argument '$1' not supported" >&2
	exit 3
	;;
  stop)
	# killall inotifywait ???
	;;
  *)
	echo "Usage: inotifywait.sh [start|stop]" >&2
	exit 3
	;;
esac

:

(Debian way)

update-rc.d inotifywait.sh defaults

Note: a drawback: there is a limit on the number of tracked files. See -r option in man inotifywait.

Then the final touch in order for the new files to be created with the same group as their parent: setgid bit for all directories.

find /path/to/dir -type d -exec chmod g+s {} \;

Links:

Thursday 10 December 2009

[SSH] Change directory while connecting

Problem:

I want to create a server-www alias that connects me to the SSH server and change the directory to /var/www/ right after the connection.

There it is :

ssh -t server 'cd /var/www && $SHELL'

And for the alias, add this in your ~/.bashrc:

alias server-www="ssh -t server 'cd /var/www && $SHELL'"
server-www # test it !

References :

Thursday 20 August 2009

What is my outgoing IP?

To know the IP address you use to go out on the Internet, you can use a site like http://www.whatismyip.com

In text mode with no Web browser, it's a bit more complicated. You can use the following command:

wget --user-agent="Mozilla/5.0" -O - http://www.whatismyip.com 2>/dev/null | grep -o "Your IP Address Is: [0-9.]*"

We have to simulate a real browser, or the site refuses us.

UPDATE 14/01/2010

To find your external IP address, an even more elegant way:

dig +short myip.opendns.com @resolver1.opendns.com

UPDATE 06/05/2010

The best way:

curl icanhazip.com

http://www.commandlinefu.com/commands/view/2966/return-external-ip