chhh, quiet plxplx...

Don't forget to turn on mysql strict mode

2011-04-29T21:48:00.004+04:00

One day I noticed that ALL the tables on our DB server were MyISAM. I was kind of shocked by such news. Not that we were using transactions or something, but the create statements clearly set the storage engine to InnoDB for most important tables. After moving to new harware nobody ever checked DB engine that was used after DB migration. Turned out, mysql silently ignored those statements (innodb was turned off in my.cnf at the moment of DB restoration).


sql-mode="STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION"

This line should reside in your my.cnf/my.ini file. It will save you from such situations as well as save you from seemingly successfull attempts to put a string value into a TINYINT column or something.

the bare minimum is:


sql-mode="NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION"

after making those changes don't forget to restart mysqld.
If you don't want to restart your server, you can set this variable through executing an SQL query, e.g.:


SET @@global.sql_mode= '';

for completely disabling strict mode.

dbg-php-5.3 + apache 2.2 + phped = The Quest

2011-04-25T16:05:00.004+04:00

Moving from PHP 5.2 to 5.3 was going to be a seamless update... in theory.
I've downloaded php 5.3.6, which now only comes compiled by VC9 and that's where the problems began. By that time I have long forgotten that my apache was VC6 version from apache.org.
So if you want to use Apache 2.2, PHP >=5.3.6 and dbg-php-5.3.dll your only option seems to move to VC9 versions of all those packages.

Apache
The latest VC9 version can be grabbed at apachelounge. It can be used as a drop in replacement for your existing apache 2.2 installation, just don't forget to NOT overwrite your original httpd.conf file.

PHP
Windows binaries are available at php.net, you should opt for Thread Safe (TS) VC9 version.
After it's installation I've encountered a weird problem, which was not obvious at all - php didn't find the correct php.ini file, even though the right PHPIniDir was set in httpd.conf. The only solution for this was moving php.ini to my Windows directory.

dbg-php-5.3
After two previous steps are done, simply don't forget to choose the right dbg-php-5.3 for your platform and don't forget to use it's VC9 flavour. For me it was x86_VC9.

Hope this helps someone someday.

Eight Dayz - What's so strange about me?

2011-03-30T05:00:00.013+04:00

band: eight dayz (also covered by trevor 'trouble' andrew)
album: ev’ry day is like a new beginning
song: what’s so strange about me
year: 1988

I've found only a few versions of lyrics, most of which were confusing. So i decided to post my version here (at least that's what i hear) (some parts, that deserve attention compared to other lyrics you might find on the web are emphasized with bold text and underlined):


Whereever I am going
they’re calling me a stranger
they’re calling me a liar
they’re calling me a thief gypsy

say take away the children
take away the work
they disfavour my behaviour
tell me by my smell
they disfavour my behaviour
tell me by my smell

chorus:
what's so strange about me
i know the same with you
just cuz i'm far away from home
you say i don't belong to you

you want me to shine on my culture
the things that i've learned
you want the total adaption
the pride that i've earned

but what remains as a question
i'd like to ask you
as soon as i fit in
who are you gonna bring the bad thigs to?

...

yeah, they think they've got a reason
so they're looking down on us
but in fact they're only using us
as a bad excuse
for the mistakes that they're making 
the bad things on this earth

they disfavour our behaviour
tell us by our smell
they disfavour our behaviour
tell us by our smell

Remove localization (l10n, i18n) from git gui and gitk

2010-04-15T18:23:00.003+04:00

I think such tools as git should not be translated into other languages, this only makes it cumbersome to figure out a 1:1 relation to the original English version. And e.g. Russian version of git-gui looks horrible, because fonts have huge spacings for some reason.

Solution:
- just remove/rename the following 2 items:
1) <git installation path>/share/git-gui/lib/msgs
2) <git installation path>/share/gitk/lib/msgs

Proper case function in Perl

2010-02-16T01:10:00.009+03:00

if you, like me, consider

print ucfirst lc for split /\b/;

not enough of a 'proper' case, then try this function (taken from this thread http://www.webmasterworld.com/forum13/1673.htm, thx hiker_jjw)

#!/usr/local/bin/perl
#
# test.cgi
#
$¦ = 1;
print "Content-type: text/html\n\n";

$string = "IS D.H. LAWRENCE'S LADY: (DE'ALOUME E'FRENCHE'S)";
print "\n\n$string\n\n";
$new_string = &make_proper($string);
print "$new_string\n\n";

$string = "THIS IS A TESTING'S VOLUME I.";
print "\n\n$string\n\n";
$new_string = &make_proper($string);
print "$new_string\n\n";

exit;

sub make_proper
{
my ($string) = @_;
my @words = split (/\s+/, lc $string);
my @new_words = ();
my $new_word = "";

foreach my $word (@words) {

# Starts with Non-Alphanum Character
my $starting_non_alphanum = "";
if ($word =~ /^(\W)+(.*)/) {
$starting_non_alphanum = $1;
$word = $2;
}

# Ends with Non-Alphanum Character
my $ending_non_alphanum = "";
if ($word =~ /(.*)(\W)+$/) {
$word = $1;
$ending_non_alphanum = $2;
}

# Contains a Non-Alphanum Character
if ($word =~ /^(\w+)(\W)(\w+)(\W?)(\w?)$/) {
my $p1_word = $1;
my $p2_non_alphanum = $2;
my $p3_word = $3;
my $p4_non_alphanum = $4;
my $p5_letter = $5;

$p1_word = ucfirst $p1_word;
$p5_letter = lc $p5_letter;
if (length $p1_word > 2 && length $p3_word == 1) {
$p3_word = lc $p3_word;
} elsif (length $p1_word == 1 && length $p3_word == 1) {
$p3_word = uc $p3_word;
} else {
$p3_word = ucfirst $p3_word;
}

$new_word = $p1_word . $p2_non_alphanum . $p3_word . $p4_non_alphanum . $p5_letter;

# Other
} else {
$new_word = ucfirst $word;
}

# Recombine the Alphanum Character
$new_word = $starting_non_alphanum . $new_word . $ending_non_alphanum;

push (@new_words, $new_word);
}
my $new_string = join(" ", @new_words);

$new_string =~ s/(\w,?) And (\w)/$1 and $2/g;
$new_string =~ s/(\w,?) Or (\w)/$1 or $2/g;
$new_string =~ s/(\w,?) But (\w)/$1 but $2/g;

$new_string =~ s/(\w) At (\w)/$1 at $2/g;
$new_string =~ s/(\w) In (\w)/$1 in $2/g;
$new_string =~ s/(\w) On (\w)/$1 on $2/g;
$new_string =~ s/(\w) To (\w)/$1 to $2/g;
$new_string =~ s/(\w) From (\w)/$1 from $2/g;

$new_string =~ s/(\w) Is (\w)/$1 is $2/g;
$new_string =~ s/(\w) A (\w)/$1 a $2/g;
$new_string =~ s/(\w) An (\w)/$1 an $2/g;
$new_string =~ s/(\w) Am (\w)/$1 am $2/g;
$new_string =~ s/(\w) For (\w)/$1 for $2/g;
$new_string =~ s/(\w) Of (\w)/$1 of $2/g;
$new_string =~ s/(\w) The (\w)/$1 the $2/g;

if (length $new_string > 60) {
$new_string =~ s/(\w) With (\w)/$1 with $2/g; #?
$new_string =~ s/(\w) That (\w)/$1 that $2/g; #?
}

$new_string = ucfirst $new_string;

return ($new_string);

} # End sub make_proper

Archiving files with Perl (using only standard modules)

2009-07-10T01:49:00.012+04:00

I stumbled upon this problem when decided to write an automated script for backing up my projects. The first solution was to use Archive::Rar module, but there are 2 catches: you need to have winRar installed and you need the Archive::Rar module itself.
Finding this not acceptable, I looked into the Archive module to find the Archive::Tar quite good!

The only problem was that it didn't want to simply archive a specified directory and all of it's contents, instead you need to provide the list of all the files you want archived in a list.

If you simply provide it with a list of full absolute paths to files, you'll end up with the archive containing all your directory structure like having folders 'D:', 'Program Files', 'etc' and so on. What you can do is first chdir() (not everyone knows it's possible to change the current working directory from inside the script) to the directory one level above the one you're going to archive and then perform all operations relative to that path which is much more convinient.

To add all the files in nested folders you have to recursively traverse the directory structure either manually or, for example, using File::Find. We'll do it th latter way as File::Find is also a standard perl module.

(you can grab the full fledged version of the script here [http://github.com/chhh/backup/blob/master/backup.pl])


use Archive::Tar;
use File::Find;

# assume you have the following folder for archiving 
# d:/some_data/folder_for_archiving
# you can work with this path and split it using RegEx or how 
# ever you like
# you can also use Cwd module to get the absolute path like 
# this:
#    use Cwd qw/abs_path/;
#    my $abs_dir_path = abs_path($some_relative_path);
# for this example we'll keep it even simpler

my $abs_dir_path = 'd:/some_data/';
my $dir_name = 'folder_for_archiving';
my $archive_filename = 'archived_filder';

# changing the working directory
chdir $abs_dir_path 
    or die "Can't change dir to $abs_dir_path: $!\n";

# The find() function recursively traverses the directory 
# structure returning
# the name of each file in a special variable $File::Find::name,
# it uses a
# callback function for each file, here we used anonymous 
# function defined
# right inside the find() call, that pushes each filename
# to  our @files array.
my @files;
find(sub {push @files,$File::Find::name},$folder_for_archiving);

# the 1st argument to Archive::Tar is the archive name, 
# 2nd level of
 compression (1-9), 3rd is argument list
if ( !Archive::Tar->create_archive( 
            "$abs_dir_path/$archive_filename.tar",
            5,
            @files)) {
    print "Archivation failed.\n";
}
print "Archivation complete.\n";

(you can grab the full fledged version of the script here [http://github.com/chhh/backup/blob/master/backup.pl])

MySQL LOAD DATA INFILE workaround for stored procedures

2009-07-01T03:03:00.014+04:00

Intro
Having googled on the topic I haven't found any relevant sources suggesting a fix and here it comes. All of this was done for 5.0.x versions of MySQL that don't support LOAD DATA INFILE for some misterious security reasons (another explanation, that i've seen was it's due to the fact that stored procedures should contain valid SQL, but it doesn't really make much sense, as cursors, for example, are allowed). It was promised to return this feature in future releases, but i'm yet to try 5.1 and 5.4 to see if it's there or not. I've also read that 5.0.4b (which is beta) didn't have such problems. But let's move to the question under investigation.
Solution
As calling LOAD DATA INFILE directly from procedure is not allowed, the only option we're left with is force the procedure to tell something from outside to execute the desired LOAD DATA INFILE statement. In this post i'll show you how to do it with Perl, but it can as well be done with any other language.
We will make a system call, that'll run a Perl script which will be passed all the necessary parameters to call LOAD DATA INFILE. For this we need 2 things:
1) A way to make a system call from MySQL
2) Perl installation (any version will do) with Perl DBI (DataBase Interface) and DBD::MySQL (DataBase Driver) modules installed
3) Perl script performing the job

So let's get to it one by one.

1) A way to make a system call from MySQL
There's no native way to perform system calls from mysql 5 (why would anyone want that anyway?!) but there's a great UDF (User Defined Function) by Roland Bowman, which he posted on his blog, allowing you to do this. The UDFs are hosted at http://www.mysqludf.org/ , the one of interest for us is lib_mysqludf_sys, particularly the function sys_exec from there.
Download and unzip the archive, place the lib_mysqludf_sys.so file into your /MySQL/bin folder. If you're using Windows, like me, you'll need not the .so file, but .dll which you can build from source, or just get mine precompiled version of it (i'll post the link later).
Then run MySQL query browser or command line tool and


drop function if exists sys_exec;
create function sys_exec returns int soname 'lib_mysqludf_sys.so';

ofcourse you'll need to change 'lib_mysqludf_sys.so' to 'lib_mysqludf_sys.dll' under Windows.
That's it, you can now perform any command line operations right from MySQL! The usage syntax is:
select sys_exec('command string');
or if you don't need the output, simply
do sys_exec('command string');
Beware not to run any programs, that require user input, as this will hang your process. Now to test it you can, for example:
in windows - do sys_exec('time /T > time.txt');
in unix like systems - do sys_exec('date > date.txt');
if everything works, you'll find those files in /MySQL/data/

2) Perl installation (any version will do) with Perl DBI, DBD::MySQL and DataTable modules installed
This is much simpler. You can get a distro of perl for your platform from from ActiveState here. During install it's generally a good idea to allow perl to put itself to PATH. After install is complete run ppm (perl package manager), simply type ppm in command line prompt and it should run either with GUI (newer ppm versions) or in text mode. Search for DBD and DBI:MySql modules, and install them. In text mode (which you can enforce in newer ppm versions by running ppm-shell) you do:


ppm (or ppm-shell)
search dbi
install *number of the module from the search results here*
search dbd::mysql
install *number of the module from the search results here*

and if you're using the graphical shell everything is self-explanatory there. Now you should be ready to rock!

3) Perl script performing the job
The script should be placed in /MySQL/data/, i called mine LOAD_DATA_INFILE.pl
Here is a sample script, allowing basic parameter handling, that issues LOAD DATA INFILE query to the database.

 
#!/usr/bin/perl
###
###    simply calls LOAD DATA INFILE for specified database, tablename and csv file
###
use DBI;
use Getopt::Long;

GetOptions( "host=s"         => \$hostname,
        "port=i"          => \$port,
        "username=s"    => \$username,
        "password=s"    => \$password,
        "database=s"    => \$database,
        "file=s"        => \$file,
        "table=s"        => \$table);

#data file MUST be specified (plus we substitute all occurances of \ with a /)
die "No path to *.csv file specified!!!" unless defined $file;
die "No table name specified!!!" unless defined $table;
$file =~ s/\\/\//g;

#here we set default DB connection parameters
if (not defined $hostname)     {$hostname = "localhost";}
if (not defined $port)         {$port = "3306";}
if (not defined $username)     {$username = "root";}
if (not defined $password)     {$password = 'admin';}
if (not defined $database)     {$database = "default_db_name";}


#connect to database
$dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
$dbh = DBI->connect($dsn, $username, $password) or die("Could not connect to database '$database'!");

#create and execute SQL queries
$sql = "load data infile '$file'
    into table $table;";
$sth = $dbh->prepare($sql);
$sth->execute;

#disconnect from database
$dbh->disconnect;

this code looks pretty simple and clear, no special explanations needed.

Now it's time to create a procedure, that loads something into our tables!


DELIMITER $$
CREATE  PROCEDURE `LOAD_FILE`(file_path TEXT)
BEGIN

#CREATE TABLE table_name like some_other_table;

do sys_exec(  concat('perl LOAD_DATA_INFILE.pl --table table_name --file ', file_path)  );

# do something with data here
# and maybe
DROP TABLE if exists table_name;
END $$
DELIMITER ;

Certailnly this is not an easy way, but i couldn't find anything better on the net when i needed this a year and a half ago.