Archive

Archive for January, 2009

Linux / Unix find command

January 20th, 2009 No comments

 

Source: http://www.computerhope.com/unix/ufind.htm 

 

About find

Finds one or more files assuming that you know their approximate filenames.

Syntax

find path expressions

path
A path name of a starting point in the directory hierarchy.

-atime n
True if the file was accessed n days ago. The access time of directories in path is changed by find itself.

-cpio device
Always true; write the current file on device in cpio format (5120-byte records).

-ctime n
True if the file’s status was changed n days ago.

-depth
Always true; causes descent of the directory hierarchy to be done so that all entries in a directory are acted on before the directory itself. This can be useful when find is used with cpio to transfer files that are contain edin directories without write permission.

-exec command
True if the executed command returns a zero value as exit status. The end of command must be punctuated by an escaped semicolon. A command argument {} is replaced by the current path name.

-follow
Always true; causes symbolic links to be followed. When following symbolic links, find keeps track of the directories visited so that it can detect infinite loops; for example, such a loop would occur if a symbolic link pointed to an ancestor. This expression should not be used with the -type l expression.

-fstype type
True if the filesystem to which the file belongs is of type type .

-group gname
True if the file belongs to the group gname. If gname is numeric and does not appear in the /etc/group file, it is taken as a group ID.

-inum n
True if the file has inode number n.

-links
True if the file has n links.

-local
True if the file system type is not a remote file system type as defined in the /etc/dfs/fstypes file. nfsis used as the default remote filesystem type if the/etc/dfs/fstypes file is not present.

-ls
Always true; prints current path name together
with its associated statistics. These include
(respectively):

  • inode number
  • size in kilobytes (1024 bytes)
  • protection mode
  • number of hard links
  • user
  • group
  • size in bytes
  • modification time.

If the file is a special file the size field will instead contain the major and minor device numbers.

If the file is a symbolic link the pathname of the linked-to file is printed preceded by `->’. The format is identical to that of ls -gilds ls Note: Formatting is done internally, without executing the ls program.

-mount
Always true; restricts the search to the file system containing the directory specified. Does not list mount points to other file systems.

-mtime n
True if the file’s data was modified n days ago.

-name pattern
True if pattern matches the current file name.  Normal shell file name generation characters (see sh) may be used. A backslash (\) is used as an escape character within the pattern. The pattern should be escaped or quoted when find is invoked from the shell.

-ncpio device
Always true; write the current file on device in cpio -c format (5120 byte records).

-newer file
True if the current file has been modified more recently than the argument file.

-nogroup
True if the file belongs to a group not in the /etc/group file.

-nouser
True if the file belongs to a user not in the /etc/passwd file.

-ok command
Like -exec except that the generated command line is printed with a question mark first, and is executed only if the user responds by typing y.

-perm [-]mode
The mode argument is used to represent file mode bits. It will be identical in format to the <symbolicmode> operand described in chmod, and will be interpreted as follows. To start, a template will be assumed with all file mode bits cleared. An op symbol of:

+
will set the appropriate mode bits in the template;

-
will clear the appropriate bits;

=
will set the appropriate mode bits, without regard to the contents of process’ file mode creation mask.

The op symbol of – cannot be the first character of mode; this avoids ambiguity with the optional leading hyphen. Since the initial mode is all bits off, there are not any symbolic modes that need to use – as the first character.

If the hyphen is omitted, the primary will evaluate as true when the file permission bits exactly match the value of the resulting template.

Otherwise, if mode is prefixed by a hyphen, the primary will evaluate as true if at least all the bits in the resulting template are set in the file permission bits.

-perm [-]onum
True if the file permission flags exactly match the octal number onum see chmod). If onum is prefixed by a minus sign (-), only the bits that are set in onum are compared with the file permission flags, and the expression evaluates true if they match.

-print
Always true; causes the current path name to be printed.

-prune
Always yields true. Do not examine any directories or files in the directory structure below the pattern just matched. If -depth is specified, -prune will have no effect.

-size n[c]
True if the file is n blocks long (512 bytes per block). If n is followed by a c, the size is in
bytes.

-type c
True if the type of the file is c, where c is b, c, d, D, f, l, p, or s for block special file, character special file, directory, door, plain file, symbolic link, fifo (named pipe), or socket, respectively.

-user uname
True if the file belongs to the user uname . If uname is numeric and does not appear as a login name in the /etc/passwd file, it is taken as a user ID.

-xdev
Same as the -mount primary.

When using find to determine files modified within a range of time, one must use the ?time argument before the -print argument; otherwise, find will give all files.

Examples

find -name ‘mypage.htm’

In the above command the system would search for any file named mypage.htm in the current directory and any subdirectory.

find / -name ‘mypage.htm’

In the above example the system would search for any file named mypage.htm on the root and all subdirectories from the root.

find -name ‘file*’

In the above example the system would search for any file beginning with file in the current directory and any subdirectory.

find -name ‘*’ -size +1000k

In the above example the system would search for any file that is larger then 1000k.

 

 

Source : http://www.linux.ie/newusers/beginners-linux-guide/find.php 

 

finder-keepers.

In it’s simplest use the find command searches for files in the current directory and its subdirectories:

$ find .
./tp1301.txt
./up1301.txt
./tp1302.txt
./up1302.txt
./Up1303.txt
./misc/uploads
./misc/uploads/patch12_13.diff

As always, the dot indicates the current directory. Here find has listed all files found in the current directory and its subdirectories.

If we only want to find files with ‘up’ at the start of their name, we use the ‘-name’ argument.

So the following would be used:

$ find . -name up\*
./up1301.txt
./up1302.txt
./misc/uploads

find defaults to being case sensitive. If we want the find utility to locate the file ‘Up1303.txt’ we could either do ‘find -name Up\*‘ or use the iname argument instead of the name argument.

The wildcard character is escaped with a slash so BASH sends a literal asterisk to the find utility as an argument instead of performing filename expansion and passing any number of files in as arguments.

This ‘gotcha’ is important. Be aware of the characters which the shell attaches special meaning to.

Now we know there are files that should have their names in lowercase we can utilise find to get a list of files with names that aren’t:

$ find -iname up\* -not -name up\*

Smooth Operator

find supports boolean algebra with the -and, -or and -not arguments. These are abbreviated as -a, -o and ! (which in bash must be escaped as \!) respectively. The and operator is mentioned here for completeness. Its presence is implied:

$ find . -iname david\*gray\*ogg -type f > david_gray.m3u

These operators are processed in the following order:

Parentheses

Use parentheses to force the order in which the operators are evaluated.

-not

Invert the result of the tested expression.

-and

E.g. ex1 -and ex2; the second expression isn’t checked if the first evaluated to true

-or

E.g. ex1 -or ex2; as with -AND, the second expression isn’t checked if the first evaluated to true

‘,’

This is the list operator where unlike the ‘-AND’ and ‘-OR’ operators both expressions are evaluated. Read the ’2 into 1 does go’ section for more information.

The example in the Smooth Operator boxout creates an m3u playlist listing all ogg files that start ‘David Gray’ (and all case-permutations)

$ find . -iname david\ gray\*ogg -type f > david_gray.m3u

This will find any files called, in one way or the other, "david gray….ogg".

This is semantically equivalent to:

$ find . -iname david\ gray\*ogg -and -type f > david_gray.m3u

It’s equivalent to:

$ find . -iname "david gray*ogg" -and -type f > david_gray.m3u

What if the ogg files themselves mightn’t have the artists name in them and are in some subdirectory of one called ‘David Gray’, how do we find them?

$ find . -ipath \*david\ gray\*ogg -type f > david_gray.m3u

The expression starts with a wildcard because its possible there’s more than one subdirectory named ‘david gray’ that might really be nothing more than symlinks for categorisations.

Here’s another example, we list the contents of the humour directory (one line per file) and do a case-insensitive search for .mp3 files with ‘yoda’ in the name of the file:

$ ls humour -1
Weird Al - Yoda.mp3
welcome_to_the_internet_helpdesk.mp3
werid al - livin' la vida yoda.mp3

$ find -ipath \*humour\*yoda\* -type f
./humour/Weird Al - Yoda.mp3
./humour/werid al - livin' la vida yoda.mp3

2 into 1 does go

As implied in the Smooth Operator boxout, it’s possible to have one invocation of find perform more than one task.

To compile two lists, one containing the names of all .php files and the other the names of all .js files use:

$ find ~ -type f \( -name \*.php -fprint php_files ,
                    -name \*.js -fprint javascript_files \)

Pruning

Suppose you have a playlist file listing all David Gray .ogg files but there are a few albums you don’t want included.

You can prevent those albums from going into the playlist by using the -prune action which works by attempting to match the names of directories against the given expression.

This example excludes the Flesh and Lost Songs albums :

$ find   \( -path  ./mp3/David_Gray/Flesh\* -o -path
"./mp3/David_Gray/Lost Songs" \* \) -prune -o -ipath \*david\ gray\*

The first thing you’ll notice here is the parentheses are escaped out so BASH doesn’t misinterpret them. Notice using -prune takes the form

"don’t look for these, look for these other ones instead". ie:

$ find (-path <don't want this> -o -path <don't want this#2>)
\-prune -o -path <global expression for what I do want>

It might take a bit longer to invoke find to use the -prune action: decide exactly what you want to do first. I find using the -prune action saves me time I can use on other tasks.

Fussy Fozzy!

There’s a host of other expressions and criteria that can be used with find.

Here is a brief rundown on the ones you’ll most likely want to use:

-nouser

file is owned by someone no longer listed in /etc/passwd

-nogroup

the group the file belongs to is no longer listed in /etc/groups

-owner <username>

file is owned by specified user.

We’ll delve into using these, and others, later on.

Print me the way you want me, baby!

Changing the output information

If you want more than just the names of the files displayed, find’s -printf action lets you have just about any type of information displayed. Looking at the man page there is a startling array of options.

These are used the most:

%p

filename, including name(s) of directory the file is in

%m

permissions of file, displayed in octal.

%f

displays the filename, no directory names are included

%g

name of the group the file belongs to.

%h

display name of directory file is in, filename isn’t included.

%u

username of the owner of the file

As an example:

$ find . -name \*.ogg -printf %f\\n

generates a list of the filenames of all .ogg files in and under the current directory.

The ‘double backslash n’ is important; ‘\n’ indicates the start of a new line. The single backslash needs to be escaped by another one so the shell doesn’t take it as one of its own.

Where to output information?

find has a set of actions that tell it to write the information to any file you wish. These are the -fprint, -fprint0 and -fprintf actions.

Thus

$ find . -iname david\ gray\*ogg -type f -fprint david_gray.m3u

is more efficient than

$ find . -iname david\ gray\*ogg -type f > david_gray.m3u

Execute!

File is an excellent tool for generating reports on basic information regarding files, but what if you want more than just reports? You could just pipe the output to some other utility:

$ find ~/oggs/ -iname \*.mp3 | xargs rm

This isn’t all that efficient though.

It is much better to use the -exec action:

$ find ~/oggs/ -iname \*.mp3 -exec rm {} \;

It mightn’t read as well, but it does mean the files are immediately deleted once found.

‘{}’ is a placeholder for the name of the file that has been found and as we want BASH to ignore the semicolon and pass it verbatim to find we have to escape it.

To be cautious, the -ok action can be used instead of -exec. The -ok action means you’ll be asked for confirmation before the command is executed.

There are many ways these can be used in ‘real life’ situations:

If you are locked out from the default Mozilla profile, this will unlock you:

$ find ~/.mozilla -name lock -exec rm {} \;

To compress .log files on an individual basis:

$ find . -name \*.log -exec bzip {} \;

Give user ken ownership of files that aren’t owned by any current user:

$ find . -nouser -exec chown ken {} \;

View all .dat files that are in the current directory with vim. Don’t search any subdirectories.

$ vim -R `find . -name \*.dat -maxdepth 1`

Look for directories called CVS which are at least four levels below the current directory:

$ find -mindepth 4 -type d -name CVS 

Time waits for no-one

You might want to search for recently created files, or grep through the last 3 days worth of log files.

Find comes into its own here: it can limit the scope of the files found according to timestamps.

Now, suppose you want to see what hidden files in your home directory changed in the last 5 days:

$ find ~ -mtime -5 -name \.\*

If you know something has changed much more recently than that, say in the last 14 minutes, and want to know what it was there’s the mmin argument:

$ find ~ -mmin 14 -name \.\*

Be aware that doing a ‘ls’ will affect the access time-stamps of the files shown by that action. If you do an ls to see what’s in a directory and try the above to see what files were accessed in the last 14 minutes all files will be listed by find.

To locate files that have been modified since some arbitrary date use this little trick:

$ touch -d "13 may 2001 17:54:19" date_marker
$ find . -newer date_marker 

To find files created before that date, use the cnewer and negation conditions:

$ find . \! -cnewer date_marker

To find a file which was modified yesterday, but less than 24 hours ago:

$ find . -daystart -atime 1 -maxdepth

The -daystart argument means the day starts at the actual beginning of the day, not 24 hours ago.

This argument has meaning for the -amin, -atime, -cmin, ctime, -mmin and -mtime options.

Finding files of a specific size

A file of character (bytes)

To locate files that have a certain amount of characters present then you can’t go far wrong with

# find files with exactly 1000 characters
$ find . -size 1000c
#find files containing between 600 to 700 characters, inclusive.
$ find . -size +599c -and -size -701c       

‘Characters’ is a misnomer: ‘c’ is find’s shorthand for bytes; thus this will only work for ASCII text not Unicode.

Consulting the man page we see

c = bytes

w = 2 byte words

k = kilobytes

b = 512-byte blocks

Thus we can use find to list files of a certain size:

$ find /usr/bin -size 48k
Empty files

You can find empty files with $ find . -size 0c

Using the -empty argument is more efficient.

To delete empty files in the current directory:

$ find . -empty -maxdepth 1 -exec rm {} \;

Users & Groupies

Users

To locate files belonging to a certain user:

# find /etc -type f \!  -user root -exec ls -l {} \;
-rw------- 1 lp sys 19731 2002-08-23 15:04 /etc/cups/cupsd.conf
-rw------- 1 lp sys    97 2002-07-26 23:38 /etc/cups/printers.conf

A subset of that same information, without having the cost of an exec:

root@ttyp0[etc]# find /etc -type f \!  -user root \
                 -printf "%h/%f %u\\n"
/etc/cups/cupsd.conf lp
/etc/cups/printers.conf lp

If you know the uid and not the username then use the -uid argument:

$ find /usr/local/htdocs/www.linux.ie/ -uid 401

-nouser means there is no user in the /etc/passwd file for the files in question.

Groupies

find can locate files that belong to a specific group – or not, depending on how you use it.

This is especially suited to tracking down files that should belong to the www group but don’t:

$ find /www/ilug/htdocs/  -type f \! -group  www

The -nogroup argument means there is no group in the /etc/group file for the files in question.

This may arise if a group is removed from the /etc/group file sometime after it’s been used.

To search for files by the numerical group ID use the -gid argument:

$ find -gid 100

Permissions

If you’ve ever had one or more shell scripts not work because their execute bits weren’t set and want to sort things out for once and for all, then you should like this little example:

knoppix@ttyp1[bin]$ ls -l ~/bin/
total 8
-rwxr-xr-x    1 knoppix  knoppix	21 2004-01-20 21:42 wl
-rw-r--r--    1 knoppix  knoppix	21 2004-01-20 21:47 ww

knoppix@ttyp1[bin]$ find ~/bin/ -maxdepth 1 -perm 644 -type f \
                    -not -name .\*
/home/knoppix/bin/ww

Find locates the file that isn’t set to execute, as we can see from the output of ls.

Types of files

The ‘-type’ argument obviously specifies what type of file find is to go looking for (remember in Linux absolutely everything is represented as some type of file).

So far I’ve been using ‘-type f’ which means search for normal files.

If we want to locate directories with ‘_of_’ in their name we’d use:

$ find . -type d -name '*_of_*'

The list generated by this won’t include symbolic links to directories.

To get a list including directories and symbolic links:

$ find . \( -type d -or -type l \) -name '*_of_*'

For a complete list of types check the man page.

Regular expressions

Thus far we’ve been using casual wildcards to specify certain groups of files. Find also support regular expressions, so we can use more advanced criteria with regards to locating files. The matching expression must apply to the entire path:

ken@gemmell:/home/library/code$ find . -regex '.*/mp[0-4].*'
./library/sql/mp3_genre_types.sql

The -regex test has a case insensitive counterpart, -iregex.

There is a little gotcha with using regular expressions: You must allow for the full path of the files found, even if find is to search the current directory:

$ cd /usr/share/doc/samba-doc/htmldocs/using_samba
$ find . -regex './ch0[1-2]_0[1-3].*'
./ch01_01.html
./ch01_02.html
./ch02_01.html
./ch02_02.html
./ch02_03.html

Limiting by filesytem

As an experiment, get a MS formatted floppy disk and mount it as root:

$ su -
# mount /floppy
# mount
/dev/sda2 on / type ext2 (rw,errors=remount-ro)
proc on /proc type proc (rw)
devpts on /dev/pts type devpts (rw,gid=5,mode=620)
/dev/fd0 on /floppy type msdos (rw,noexec,nosuid,nodev)

Now try

$ find / -fstype msdos -maxdepth 1 

You should see only /floppy listed.

To get the reverse of this, ie a listing of directories that are not on msdos file-systems, use

$ find / -maxdepth 1 \( -fstype msdos \) -prune -or -print

This is a start on limiting the files found by system type.

Summary

I’ve covered the vast majority of ways to use the find utility, but not absolutely everything. If you’ve any questions please don’t hesitate in emailing me

Categories: Linux Commands Tags:

Cpanel Updates

January 20th, 2009 No comments
Categories: Cpanel, Upgrade Tags:

MYSQL – How to Repair Tables

January 20th, 2009 No comments

 

Source: http://dev.mysql.com/doc/refman/5.1/en/repair.html

 

The discussion in this section describes how to use myisamchk on MyISAM tables (extensions .MYI and .MYD).

You can also (and should, if possible) use the CHECK TABLE and REPAIR TABLE statements to check and repair MyISAM tables. See Section 12.5.2.3, “CHECK TABLE Syntax”, and Section 12.5.2.6, “REPAIR TABLE Syntax”.

Symptoms of corrupted tables include queries that abort unexpectedly and observable errors such as these:

  • tbl_name.frm is locked against change

  • Can’t find file tbl_name.MYI (Errcode: nnn)

  • Unexpected end of file

  • Record file is crashed

  • Got error nnn from table handler

To get more information about the error, run perror nnn, where nnn is the error number. The following example shows how to use perror to find the meanings for the most common error numbers that indicate a problem with a table:

shell> perror 126 127 132 134 135 136 141 144 145
MySQL error code 126 = Index file is crashed
MySQL error code 127 = Record-file is crashed
MySQL error code 132 = Old database file
MySQL error code 134 = Record was already deleted (or record file crashed)
MySQL error code 135 = No more room in record file
MySQL error code 136 = No more room in index file
MySQL error code 141 = Duplicate unique key or constraint on write or update
MySQL error code 144 = Table is crashed and last repair failed
MySQL error code 145 = Table was marked as crashed and should be repaired

Note that error 135 (no more room in record file) and error 136 (no more room in index file) are not errors that can be fixed by a simple repair. In this case, you must use ALTER TABLE to increase the MAX_ROWS and AVG_ROW_LENGTH table option values:

ALTER TABLE tbl_name MAX_ROWS=xxx AVG_ROW_LENGTH=yyy;

If you do not know the current table option values, use SHOW CREATE TABLE.

For the other errors, you must repair your tables. myisamchk can usually detect and fix most problems that occur.

The repair process involves up to four stages, described here. Before you begin, you should change location to the database directory and check the permissions of the table files. On Unix, make sure that they are readable by the user that mysqld runs as (and to you, because you need to access the files you are checking). If it turns out you need to modify files, they must also be writable by you.

This section is for the cases where a table check fails (such as those described in Section 6.4.2, “How to Check MyISAM Tables for Errors”), or you want to use the extended features that myisamchk provides.

The options that you can use for table maintenance with myisamchk are described in Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”.

If you are going to repair a table from the command line, you must first stop the mysqld server. Note that when you do mysqladmin shutdown on a remote server, the mysqld server is still alive for a while after mysqladmin returns, until all statement-processing has stopped and all index changes have been flushed to disk.

Stage 1: Checking your tables

Run myisamchk *.MYI or myisamchk -e *.MYI if you have more time. Use the -s (silent) option to suppress unnecessary information.

If the mysqld server is stopped, you should use the --update-state option to tell myisamchk to mark the table as “checked.”

You have to repair only those tables for which myisamchk announces an error. For such tables, proceed to Stage 2.

If you get unexpected errors when checking (such as out of memory errors), or if myisamchk crashes, go to Stage 3.

Stage 2: Easy safe repair

First, try myisamchk -r -q tbl_name (-r -q means “quick recovery mode”). This attempts to repair the index file without touching the data file. If the data file contains everything that it should and the delete links point at the correct locations within the data file, this should work, and the table is fixed. Start repairing the next table. Otherwise, use the following procedure:

  1. Make a backup of the data file before continuing.

  2. Use myisamchk -r tbl_name (-r means “recovery mode”). This removes incorrect rows and deleted rows from the data file and reconstructs the index file.

  3. If the preceding step fails, use myisamchk –safe-recover tbl_name. Safe recovery mode uses an old recovery method that handles a few cases that regular recovery mode does not (but is slower).

Note

If you want a repair operation to go much faster, you should set the values of the sort_buffer_size and key_buffer_size variables each to about 25% of your available memory when running myisamchk.

If you get unexpected errors when repairing (such as out of memory errors), or if myisamchk crashes, go to Stage 3.

Stage 3: Difficult repair

You should reach this stage only if the first 16KB block in the index file is destroyed or contains incorrect information, or if the index file is missing. In this case, it is necessary to create a new index file. Do so as follows:

  1. Move the data file to a safe place.

  2. Use the table description file to create new (empty) data and index files:

    shell> mysql db_name
    mysql> SET autocommit=1;
    mysql> TRUNCATE TABLE tbl_name;
    mysql> quit
  3. Copy the old data file back onto the newly created data file. (Do not just move the old file back onto the new file. You want to retain a copy in case something goes wrong.)

Important

If you are using replication, you should stop it prior to performing the above procedure, since it involves filesystem operations, and these are not logged by MySQL.

Go back to Stage 2. myisamchk -r -q should work. (This should not be an endless loop.)

You can also use the REPAIR TABLE tbl_name USE_FRM SQL statement, which performs the whole procedure automatically. There is also no possibility of unwanted interaction between a utility and the server, because the server does all the work when you use REPAIR TABLE. See Section 12.5.2.6, “REPAIR TABLE Syntax”.

Stage 4: Very difficult repair

You should reach this stage only if the .frm description file has also crashed. That should never happen, because the description file is not changed after the table is created:

  1. Restore the description file from a backup and go back to Stage 3. You can also restore the index file and go back to Stage 2. In the latter case, you should start with myisamchk -r.

  2. If you do not have a backup but know exactly how the table was created, create a copy of the table in another database. Remove the new data file, and then move the .frm description and .MYI index files from the other database to your crashed database. This gives you new description and index files, but leaves the .MYD data file alone. Go back to Stage 2 and attempt to reconstruct the index file.

Categories: How To, MYSQL, Repair Tags:

ClamAV Anti Virus

January 20th, 2009 No comments

 

Source: http://www.clamav.net

 

Clam AntiVirus is an open source (GPL) anti-virus toolkit for UNIX, designed especially for e-mail scanning on mail gateways. It provides a number of utilities including a flexible and scalable multi-threaded daemon, a command line scanner and advanced tool for automatic database updates. The core of the package is an anti-virus engine available in a form of shared library.

Here is a list of the main features:

  • command-line scanner
  • fast, multi-threaded daemon with support for on-access scanning
  • milter interface for sendmail
  • advanced database updater with support for scripted updates and digital signatures
  • virus scanner C library
  • on-access scanning (Linux® and FreeBSD®)
  • virus database updated multiple times per day (see home page for total number of signatures)
  • built-in support for various archive formats, including Zip, RAR, Tar, Gzip, Bzip2, OLE2, Cabinet, CHM, BinHex, SIS and others
  • built-in support for almost all mail file formats
  • built-in support for ELF executables and Portable Executable files compressed with UPX, FSG, Petite, NsPack, wwpack32, MEW, Upack and obfuscated with SUE, Y0da Cryptor and others
  • built-in support for popular document formats including MS Office and MacOffice files, HTML, RTF and PDF

Read the documentation for more details.

Categories: Security Tags:

Install a Mail Server with Antivirus and Antispam

January 20th, 2009 No comments

 

Source:  http://librenix.com/?inode=10025

 

This article illustrates a situation where you need to set up your own mail server (be it your home mail server, or a small office one). It actually shows that, if using an integrated service mail server, anyone can do the job, all in a matter of minutes.
AXIGEN Mail Server, the solution chosen for this example, can send and receive e-mails securely via "mydomain.com" and is able to retrieve them in a WebMail interface – this means that it includes all mail services needed for a fully functional mail server (SMTP, IMAP, POP3, WebMail, WebAdmin).
To get an idea of the amount of time you can spare by installing such a solution, just think of all the different open source applications you would need to install instead (i.e. an MTA, Squirrelmail for Webmail, QmailAdmin for web configuration, Courier for IMAP and POP3 and many others.)
AXIGEN Mail Server can virtually integrate with any Antivirus/Antispam application and it comes with built-in connectors ClamAV Antivirus and SpamAssassin. The second part of this article shows you how to install these applications and configure these connectors for use with AXIGEN.
Thus, at the end of this process which can take up half an hour at most, you will not only have your mail server up and running, but also virus and spam protection for your incoming and outgoing mail traffic.
AXIGEN runs on several Linux distributions (Gentoo, Redhat/Fedora Core, Slackware, Debian, Ubuntu, Mandrake/Mandriva, SUSE), on BSD versions (FreeBSD, OpenBSD and NetBSD) and on Solaris but for the purpose of this article, let’s suppose you are setting up your mail system on a Fedora Core 6 platform.In five easy steps, you will have your server installed, your primary domain running and access to the Web configuration interface (WebAdmin).
1. Download / unpack corresponding package
Download AXIGEN rpm package from the AXIGEN website (packages are available as 30 day evaluation versions). Save the corresponding package for Fedora Core 6 "axigen-2.0.4.i386.rpm.gcc4.tar.gz" on your local machine and unpack the file, by issuing in the same directory as the download file:

tar xzvf axigen-2.0.4.i386.rpm.gcc4.tar.gz

2. Install command

Then, in order to install the RPM package, issue (while logged in as root) the following command, from the same directory as the rpm file:

rpm -ivh axigen-2.0.4.gcc4-1.i386.rpm

This will create the entire directory structure needed for AXIGEN to run. After the installation, no daemons or related application will be started.

3. Configuration options

AXIGEN provides several configuration options (configuration file, Command Line Interface), but the most intuitive and comprehensive one is WebAdmin, the Web configuration interface.

The corresponding WebAdmin service is enabled by default, as well as the other default services: IMAP, Logging, POP3, Processing and SMTP.

4. Initial configuration

The first configuration steps take place using the configuration wizard. You will set the administrator’s password, select which services are started and what interfaces will be used. In this stage of the setup you also create the primary domain that your server will use.

The wizard can be run by issuing the following command in the console right after the installation of the package has finished:

/opt/axigen/bin/axigen-cfg-wizard

NOTE: You have to make sure you do not start the mail server before the initial configuration.

5. Start AXIGEN

You can then start AXIGEN, using its initscript, by issuing this command:

/etc/init.d/axigen start

Now that your server is running, you can connect the antivirus and anti-spam applications. By default, AXIGEN comes with connectors for the ClamAV Antivirus and SpamAssasin Antispam application. The setup process below describes how to make these two applications work with AXIGEN. However, note that AXIGEN implements a proprietary filter scripting language that allows you to implement connectors for any third party Antivirus and Antispam applications.

Connecting to ClamAV

A. Install ClamAV (daemon), on the same machine on which AXIGEN Mail Server is installed. Follow these steps in order to configure ClamAv for use with AXIGEN and start the clamd daemon.

1. Install clamav-server, using yum (Yellow Dog Updater, Modified):

yum install clamav-server

2. Copy the sample config file shipped with clamav-server:

cp /usr/share/doc/clamav-server-*/clamd.conf /etc/clamd.d/axigen.conf

3. Edit: /etc/clamd.d/axigen.conf

# comment out the Example line # Example # insert/modify the following lines: LogFile /var/log/clamd.axigen PidFile /var/run/clamd.axigen/clamd.pid LocalSocket /var/run/clamd.axigen/clamd.sock User axigen 

4. Create a link to the clamd binary:

ln -s /usr/sbin/clamd /usr/sbin/clamd.axigen

5. Create the run directory, where the PID file and clamd socket will be stored, and change its permissions:

mkdir -p /var/run/clamd.axigen chown axigen:axigen /var/run/clamd.axigen

6. Create and setup the initscript:

cp /usr/share/doc/clamav-server-*/clamd.init /etc/init.d/clamd.axigen chmod 755 /etc/init.d/clamd.axigen /sbin/chkconfig clamd.axigen on

7. Edit: /etc/init.d/clamd.axigen and modify the following lines, as specified below:

# description: The clamd server running for axigen CLAMD_SERVICE=axigen

8. Finally, start the clamd daemon:

/etc/init.d/clamd.axigen

B. Configure AXIGEN antivirus filter at server level using WebAdmin

In order to activate the ClamAV filter, go through the following steps:

In the "Server" context, click on the Add new filter button. This will open up and display the Active Filter list. It is empty right now, so we need to add the clamav filter to the list.

In the Priority field, enter a priority between 0 and 500 (a filter with priority 0 will be applied first and the one with 500, last).

Important – the domain-level filters have the priority limited to range 100-400 and the user-level filters are limited to the 200-300 range. A value of "10" should be fine, leaving you space to apply some other future filters before this one.

After setting the filter priority, select the socket value from in the Filter type dropdown list and the clamav value from the Filter Name list.

In the Apply on checklist, select the relay option, to apply the filter on outgoing mails. To make sure you scan both incoming and outgoing mails, you have to create the filter and select both values, local and relay.

In AXIGEN, it is possible to enable filters either at domain or user level, in the corresponding WebAdmin tabs. The filters activated at server level will be automatically applied for all domains and accounts. However, you have the possibility to add additional filters at domain or account level.

Connecting to SpamAssasin

The process for Connecting SpamAssassin is similar and even less time-consuming as no configurations are necessary after the product installation.

C. Install SpamAssassin using the yum application:

yum install spamassassin

No further configurations are necessary.

D. Configure SpamAssassin at server level, using Webadmin

The connector for SpamAssassin is a socket filter for AXIGEN, so the configuration procedure is the same as for ClamAV. The difference would be that for SpamAssassin, a TCP socket is more likely to be used.

Also, when activating the SpamAssassin filter, you need to keep in mind the following:

  • Enter a different priority value for the SpamAssasin filter (if you have chosen 10 for ClamAV, choose a higher value for SpamAssassin in order to apply this filter after ClamAV in the filtering chain)
  • Select the corresponding filter name, spamassassin in the Filter name list
Access AXIGEN WebMail

At this step of the way, your mail server is ready to go, and you can also you can access the AXIGEN WebMail to send and receive test messages. Then, use the full email address and password to log on to AXIGEN WebMail, at the default address: http://127.0.0.1:8000, or use the address you specified in the initial configuration phase when you ran the setup wizard.

Now you’re really done: you can securely send and receive messages from your home domain and easily make any further configurations, to accommodate your specific network requirements. As you have seen, installing all mail services from one single executable and an intuitive Web configuration interface make things a lot easier and a lot less time-consuming.

Authors:

Liviu Anghel, Chief Security Officer, Gecad Technologies

Ciprian Negrila, Technical Support Engineer, Gecad Technologies

read more…

Categories: Mail server, Security, Server Services Tags:

remove an IP from the cphulkd blacklist

January 20th, 2009 No comments

 

User root blocked – cPHulk Brute Force Protection

Source: http://hostechs.com/2008/10/user-root-blocked-cphulk-brute-force-protection

 

Had a few clients lately blocking them self out and when i say blocking i mean blocking the user root because of them inserting the wrong password more then X times when Brute Force Protection was enabled on the server.

Of course that they were unable to login anymore to the server using the root user and more to it no one was able to login over an ssh connection.

A way around this is to access the server in single user and delete the blocked users:

mysql cphulkd
mysql> delete from brutes;
mysql> delete from logins;
mysql> quit

Now this happens usually if the following setting is set to low when setting up Brute Force Protection:

Maximum Failures By Account:

For anyone who has this kind of issues you may want to create a second user on the server and add that user to the sudoers /etc/sudoers group so that if you block the root user out you will still be able to access the server and fix the problem.

All this goes also for anyone who uses another user to connect to the server and has root ssh access disabled.

 

 

 

How to remove an IP from the cphulkd blacklist

Source : http://openhelp.info/index.php?option=com_content&task=view&id=60&Itemid=33 

 

:-cPHulk Brute Force Protection prevents malicious forces from trying to access your server’s services

by guessing the login password for that service.

It blacklists IPs that it thinks are trying to run a brute force attack.

Just follow the below steps  for removing the black listed IP:-
1)Login to MySQL .
root@server [/var/log]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 425476 to server version: 3.23.58
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> show databases;
+——————-+
| Database    |
+——————-+
| cphulkd       |
| eximstats     |
| horde          |
| mail             |
| mirror_com  |
| mysql          |
+——————-+
9 rows in set (0.02 sec)
2) "use" the cphulkd database and show tables inside the cphulkd  database.
mysql> use cphulkd
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+——————————+
| Tables_in_cphulkd |
+——————————+
| auths                     |
| brutes                    |
| logins                     |
+—————————– +
3 rows in set (0.00 sec)
mysql>
3)Check the black listed IP’s in the tables brutes and logins.
mysql> select * from brutes;  
+————–+—————————————————————–+
| IP           | NOTES                                                        |
+————–+—————————————————————–+
| 24.90.253.66 | 6 login failures to ad@ope.inad@ope.in This email address is being protected from spam bots, you need Javascript enabled to view it (ftp)       |
+————–+—————————————————————–+
1 row in set (0.00 sec)
mysql>
4)Remove the iP Address s from the table and restart cPanel service.
mysql> delete from brutes where IP=’11.20.254.56′;
Query OK, 1 row affected (0.00 sec)
mysql>   
5) Restart cPanel service.
service cpanel restart
This will fix the issue.
Note:-Error log for cphulkd is  /usr/local/cpanel/logs/cphulkd_errors.log

If you want to remove all the IP’s which are balacklisted by the cPhulkd.

Then just issue the folowing MySQL query:-

delete from brutes ; ( Brutes is the table name  here)

Categories: Cpanel, Security Tags:

Upgrading to MySQL 5.1.x on cPanel

January 20th, 2009 No comments

 

Source: http://www.v-nessa.net/2007/07/20/upgrading-to-mysql-5120-on-cpanel 

 

If you have server that run on cPanel, you’ll probably know how big of a Nazi it is in regards to the MySQL versions it can run. We just got this new line of servers at work and one of them I was pretty heartset on installing MySQL 5.1, mainly because of its loadable plugin features where you can install a plugin or module without having to recompile the whole damn thing. Upgrading to 5.1 is easy, you just have to follow the right steps.

First, I would recommend upgrading to cPanel 11 or EDGE, which should have support for compiling Apache with non-supported versions of MySQL. On this server, I’m currently running 11 on the bleeding edge build. Also, it’s a splendid idea to dump all your databases before upgrading.
Note that these instructions mention MySQL 5.1.20-beta because that’s the latest release available at the time of my writing….but you can essentially follow this guide for any version of MySQL!

1. Copy the MySQL libraries from the server into a temporary location:

mkdir /root/sqllibs
cp /usr/lib/libmysqlclient.* /root/sqllibs

2. Find any installed MySQL packages:
rpm -qa | grep -i mysql-

This should present a list of installed rpm’s…remove them with rpm -e ,but note that some may need to be removed before others. Some people also would remove the /var/lib/mysql directory, but you can leave that there.

3. Download and install the MySQL 5.1.x packages:

Hop on over to http://dev.mysql.com/downloads/mysql/5.1.html#linux-x86-32bit-rpms and download the 5.1 RPM’s and install them:
rpm -i MySQL-client-5.1.20-0.glibc23.i386.rpm
rpm -i MySQL-devel-5.1.20-0.glibc23.i386.rpm
rpm -i MySQL-embedded-5.1.20-0.glibc23.i386.rpm
rpm -i MySQL-test-5.1.20-0.glibc23.i386.rpm
rpm -i MySQL-server-5.1.20-0.glibc23.i386.rpm

4. Prepare cPanel

You’ll want to make sure that cPanel’s updates don’t reset the MySQL version, so you need to run the following commands to force cPanel to skip MySQL updates:
touch /etc/mysqldisable
touch /etc/mysqlupdisable

Now edit /var/cpanel/cpanel.config and change the MySQL version to 5.1

Create the symlink:
ln -s /var/lib/mysql/mysql.sock /tmp

Also, verify that the MySQL version is correct by running mysql -V

root@vps [~]# mysql -V
mysql Ver 14.13 Distrib 5.1.20-beta, for pc-linux-gnu (i686) using readline 5.0

5. Set up MySQL

MySQL should have already been started at this point, so you can attempt to log in as root using mysql -u root . If you are able to log in on the first try, great. If not, you’ll need to reset the MySQL password:
pico /etc/my.cnf

Add this line, and restart MySQL
skip-grant-tables
service mysql restart

Now log into MySQL root and set the password:


mysql -u root
mysql> FLUSH PRIVILEGES;
mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY ‘yourpassword’ WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
mysql> exit;

service mysql restart

When you’re done, remove the skip-grant-tables line from /etc/my.cnf and restart MySQL. Then log into Webhost Manager and reset the password *again*… this is necessary to build a bridge between the linux root user and the MySQL root user, so you can log into MySQL both through WHM’s phpMyAdmin, and SSH without a password when logged into the server as root.

All you need to do now is recompile Apache, but move the MySQL libraries back so easyapache can find them:

mv /root/sqllibs/libmysqlclient.* /usr/lib/mysql/

I haven’t tested too many configurations yet, but what works for me is Apache 2.2.4 with php 5.2.3, compiled with MySQL and mysqli, but NOT system MySQL.

Categories: Cpanel, MYSQL, Upgrade Tags:

CPanel: upgrading to MySQL5

January 20th, 2009 No comments

 

Source : http://www.ducea.com/2006/07/16/cpanel-upgrading-to-mysql5/

 

CPanel has included support for MySQL 5 in all its latest releases - 10.8.2-xxx – (Stable/Release/Current). I find this very cool and it is a big difference in trying to get MySQL4 to run on Plesk for example. Anyway the upgrade process is very simple, and probably no one will need any additional information to complete this safely. I just wanted to point out some of the problems you might encounter in performing this upgrade.
How can you upgrade to MySQL5? Simply login to the WHM interface, and from “Server Configuration / Tweak Settings” choose in the “MySQL” section the 5.0 radio button. Then click “Save” and you are done. This will actually run the script: /scripts/mysqlup that will download and install the latest MySQL5 rpms available at that time.
After completion as instructed you should rebuild your perl mysql libraries and rebuild apache (to have php build against the newly installed mysql library).

/scripts/perlinstaller --force Bundle::DBD::mysql

and for apache/php:

/scripts/easyapache

or you can see this post for more details on compiling apache/php on CPanel from WHM.

What should you be aware prior to perform this upgrade? Here are some issues that you might find useful to know before doing this…

1. The upgrade is not reversible…

Even if you see the warning and this is even in bold:

“Updating from a previous verion of MySQL to a later version is not automaticlly reversable. You should backup your databases if you think you might wish to downgrade in the future.”

you might not believe it… Well I have not believed it as I have performed safely downgrades to mysql4.1 in case of problems on manual installs of mysql. But in this case CPanel is telling the truth… After you will complete the upgrade in WHM the choice for MySQL 4.1 (the radio button) will disappear and you will no longer be able to choose it. You can probably downgrade manually if you really need to, by downloading the MySQL4 rpms and install them manually from the command line while removing the MySQL5 ones. In this case you might find useful the link to the location of CPanel rpms:

http://updates.cpanel.net/pub/mysqlinstall/

2. MySQL might fail to start…

Since I have done this only on RHEL4 servers I am not aware if this problem exists on other distributions as well. But in RHEL the MySQL5 init script was failing after the upgrade with the following error:

/etc/init.d/mysql start
Starting MySQLCouldn't find MySQL manager or server        [FAILED]

Tracking down this error I found out that the MySQL configuration file (/etc/my.cnf) installed by the upgrade had a wrong line that was causing this behaviour:

/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
#old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib    # <= you will need to comment this line
old-passwords = 1

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

The line basedir=/var/lib was causing the error and in case you are having the same problem commenting out that line will solve the problem and MySQL will start properly.

These are the 2 issues I have found out when upgrading MySQL5 and hopefully you will be aware of them prior to loosing too much time trying to solve them ;) Besides this MySQL5 is running fine and I have not found any problems in normal operation or with the integration with other CPanel functions. Have fun.

Categories: Cpanel, MYSQL, Upgrade Tags:

MySQL 5.0: When, why and how to upgrade

January 20th, 2009 No comments

 

Source: http://searchenterpriselinux.techtarget.com/tip/0,289483,sid39_gci1130131,00.html

 

If a company is running MySQL 4 now and is happy with it, shouldn’t it stick with that version? Yes and no, says Mike Hillyer, this site’s resident MySQL expert. In this tip, he discusses when and why MySQL users should upgrade, the benefits of MySQL’s new features, and how to evaluate and deploy the upcoming MySQL 5.0, now available in its beta release.

Many companies keep a policy of staying one version back from the latest release, meaning that they use Windows 2000 instead of Windows XP, MySQL 4 instead of 4.1, and so on. Such a policy promotes stability over features.

So, companies that find MySQL 4 sufficient for their needs may want to stay where they are because MySQL 4 and MySQL 4.1 are quite stable at the moment and work reliably.

Why would that company want to move to MySQL 5 now? The biggest three features of interest to most companies are stored procedures, triggers, and views.

MySQL 5′s big bonuses

Stored procedures allow you to move business logic from end applications to the MySQL server, which can save effort when you have multiple end applications (instead of implementing a price calculation routine in PHP for the web and VC for the desktop, you can implement it in a stored procedure and call it from both PHP and VC).

Stored procedures can also enhance security by allowing you to prevent a user from accessing a table directly, but instead giving them permission to call a stored procedure that has access to the table.

Triggers are stored routines that are written with a syntax like that of stored procedures, but instead of being called by a user or application, triggers are activated by table events such as inserts, updates, and deletes. The triggers can modify or abort the triggering table actions, or take the information provided by the action and use it elsewhere.

I recently used a trigger to create a MyISAM FULLTEXT lookup table for an InnoDB table: any inserts, updates, or deletes to the InnoDB table were mirrored to the lookup table by triggers, and the end users didn’t have to change anything about the way they interacted with the InnoDB table.

Views are essentially named virtual tables defined by SQL queries. I can take a complex set of tables, create a SELECT query that might be more meaningful to end users, then turn it into a view. The end users can then perform SELECT queries against the view and, in most cases, also execute INSERT, UPDATE, and DELETE statements against the view, with the changes being reflected in the underlying tables. Views help to simplify complicated data and also enhance security. As with a stored procedure, you can create a view and give a user permission to SELECT from the view but not the underlying table.

Evaluating MySQL 5

In the end most users will eventually move to MySQL 5 as MySQL 4 and 4.1 age and become outdated. The timing will have to depend on whether you consider the features of MySQL 5 to be useful to your organization.

When evaluating the new features, I would recommend installing MySQL 5 on a separate machine and trying it with your existing applications, run as many tests as you can and see if everything runs smoothly. If you do not encounter any errors you can then bring MySQL 5 into your production systems (after a production release of MySQL 5).

There really should not be things to watch out for, in the sense of bugs or incompatibilities, but you can always watch the changelogs at the 5.0 news page on MySQL’s site and look for entries that are marked ‘Incompatible Change’.

So far, bug reports have been many and varied, depending on the feature being used. As expected, most of the bugs are related to new MySQL features, such as stored procedures, triggers, and views. The full list of features added and bugs fixed can be browsed in MySQL’s 5.0 news.

Preparing for an upgrade

The best tip before upgrading is to back up your data: while the upgrade process should be relatively painless, not having a backup is a recipe for disaster.

Those looking to upgrade major versions of MySQL in a production environment should test and evaluate their systems before upgrading. The ideal would be to install all software on a test machine, upgrade the MySQL installation on the test machine to MySQL 5, and then run the test machine with real data for a while to ensure there are no suprises. If all goes well on the test machine you can then move the production systems to the newer version.

Production machines should also only be upgraded to a production version of MySQL. While the beta versions of MySQL have been very stable, there are no guarantees that your application will run flawlessly on beta software.

A common error when upgrading from MySQL 4.0 to MySQL 4.1 is password hashing. Users who encounter an error such as "Client does not support authentication protocol requested" can find at solution at OpenWin.org.

There are detailed upgrade notes for all versions of MySQL on this MySQL.com page.

Most applications should not have any problem with MySQL 5, as opposed to MySQL 4.1. However, users upgrading from MySQL 4.0 should first upgrade to MySQL 4.1, then upgrade to MySQL 5. You can get all MySQL downloads on MySQL’s download page.

Categories: Article, MYSQL, Upgrade Tags:

Upgrading from MySQL 4.1 to 5.0

January 20th, 2009 No comments

 

Source: http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html

 

Note

It is good practice to back up your data before installing any new version of software. Although MySQL works very hard to ensure a high level of quality, you should protect your data by making a backup. MySQL recommends that you dump and reload your tables from any previous version to upgrade to 5.0.

In general, you should do the following when upgrading from MySQL 4.1 to 5.0:

  • Read all the items in the following sections to see whether any of them might affect your applications:
    • Section 2.18, “Upgrading MySQL”, has general update information.
    • The items in the change lists found later in this section enable you to identify upgrade issues that apply to your current MySQL installation.
    • The MySQL 5.0 change history describes significant new features you can use in 5.0 or that differ from those found in MySQL 4.1. Some of these changes may result in incompatibilities. See Section E.1, “Changes in release 5.0.x (Production)”.
  • Note particularly any changes that are marked Known issue or Incompatible change. These incompatibilities with earlier versions of MySQL may require your attention before you upgrade.

Our aim is to avoid these changes, but occasionally they are necessary to correct problems that would be worse than an incompatibility between releases. If any upgrade issue applicable to your installation involves an incompatibility that requires special handling, follow the instructions given in the incompatibility description. Often this will involve a dump and reload, or use of a statement such as CHECK TABLE or REPAIR TABLE.

A dump and reload is done like this:

    • Dump the affected tables with mysqldump before upgrading
    • Reload the dump file (for example, with mysql) to recreate the tables after upgrading

Any procedure that involves REPAIR TABLE with the USE_FRM option must be done before upgrading. Use of this statement with a version of MySQL different from the one used to create the table (that is, using it after upgrading) may damage the table. See Section 12.5.2.6, “REPAIR TABLE Syntax”.

Several visible behaviors have changed between MySQL 4.1 and MySQL 5.0 to make MySQL more compatible with standard SQL. These changes may affect your applications.

The following lists describe changes that may affect applications and that you should watch out for when upgrading to MySQL 5.0.

Server Changes:

  • Incompatible change: The indexing order for end-space in TEXT columns for InnoDB and MyISAM tables has changed. Starting from 5.0.3, TEXT indexes are compared as space-padded at the end (just as MySQL sorts CHAR, VARCHAR and TEXT fields). If you have a index on a TEXT column, you should run CHECK TABLE on it. If the check reports errors, rebuild the indexes: Dump and reload the table if it is an InnoDB table, or run OPTIMIZE TABLE or REPAIR TABLE if it is a MyISAM table.
  • Incompatible change. For BINARY columns, the pad value and how it is handled has changed as of MySQL 5.0.15. The pad value for inserts now is 0×00 rather than space, and there is no stripping of the pad value for retrievals. For details, see Section 10.4.2, “The BINARY and VARBINARY Types”.
  • Incompatible change: As of MySQL 5.0.3, the server by default no longer loads user-defined functions (UDFs) unless they have at least one auxiliary symbol (for example, an xxx_init or xxx_deinit symbol) defined in addition to the main function symbol. This behavior can be overridden with the –allow-suspicious-udfs option. See Section 21.2.2.6, “User-Defined Function Security Precautions”.
  • Incompatible change: As of MySQL 5.0.13, InnoDB rolls back only the last statement on a transaction timeout. In MySQL 5.0.32, a new option, –innodb_rollback_on_timeout, causes InnoDB to abort and roll back the entire transaction if a transaction timeout occurs (the same behavior as in MySQL 4.1).
  • Incompatible change: For ENUM columns that had enumeration values containing commas, the commas were mapped to 0xff internally. However, this rendered the commas indistinguishable from true 0xff characters in the values. This no longer occurs. However, the fix requires that you dump and reload any tables that have ENUM columns containing true 0xff in their values: Dump the tables using mysqldump with the current server before upgrading from a version of MySQL 5.0 older than 5.0.36 to version 5.0.36 or newer.
  • Incompatible change: The update log has been removed in MySQL 5.0. If you had enabled it previously, you should enable the binary log instead.
  • Incompatible change: Support for the ISAM storage engine has been removed in MySQL 5.0. If you have any ISAM tables, you should convert them before upgrading. For example, to convert an ISAM table to use the MyISAM storage engine, use this statement:

· ALTER TABLE tbl_name ENGINE = MyISAM;

Use a similar statement for every ISAM table in each of your databases.

  • Incompatible change: Support for RAID options in MyISAM tables has been removed in MySQL 5.0. If you have tables that use these options, you should convert them before upgrading. One way to do this is to dump them with mysqldump, edit the dump file to remove the RAID options in the CREATE TABLE statements, and reload the dump file. Another possibility is to use CREATE TABLE new_tbl … SELECT raid_tbl to create a new table from the RAID table. However, the CREATE TABLE part of the statement must contain sufficient information to re-create column attributes as well as indexes, or column attributes may be lost and indexes will not appear in the new table. See Section 12.1.10, “CREATE TABLE Syntax”.

The .MYD files for RAID tables in a given database are stored under the database directory in subdirectories that have names consisting of two hex digits in the range from 00 to ff. After converting all tables that use RAID options, these RAID-related subdirectories still will exist but can be removed. Verify that they are empty, and then remove them manually. (If they are not empty, there is some RAID table that has not been converted.)

  • Incompatible change: Beginning with MySQL 5.0.42, when a DATE value is compared with a DATETIME value, the DATE value is coerced to the DATETIME type by adding the time portion as 00:00:00. Previously, the time portion of the DATETIME value was ignored, or the comparison could be performed as a string comparison. To mimic the old behavior, use the CAST() function as shonw in this example: SELECT date_col = CAST(NOW() AS DATE) FROM table;.
  • As of MySQL 5.0.25, the lc_time_names system variable specifies the locale that controls the language used to display day and month names and abbreviations. This variable affects the output from the DATE_FORMAT(), DAYNAME() and MONTHNAME() functions. See Section 9.8, “MySQL Server Locale Support”.
  • In MySQL 5.0.6, binary logging of stored routines and triggers was changed. This change has implications for security, replication, and data recovery, as discussed in Section 18.5, “Binary Logging of Stored Programs”.
  • As of MySQL 5.0.28, mysqld_safe no longer implicitly invokes mysqld-max if it exists. Instead, it invokes mysqld unless a –mysqld or –mysqld-version option is given to specify another server explicitly. If you previously relied on the implicit invocation of mysqld-max, you should use an appropriate option now.

SQL Changes:

  • Important note: Prior to MySQL 5.0.46, the parser accepted invalid code in SQL condition handlers, leading to server crashes or unexpected execution behavior in stored programs. Specifically, the parser allowed a condition handler to refer to labels for blocks that enclose the handler declaration. This was incorrect because block label scope does not include the code for handlers declared within the labeled block.

As of 5.0.46, the parser rejects this invalid construct, but if you upgrade in place (without dumping and reloading your databases), existing handlers that contain the construct still are invalid even if they appear to function as you expect and should be rewritten.

To find affected handlers, use mysqldump to dump all stored functions and procedures, triggers, and events. Then attempt to reload them into an upgraded server. Handlers that contain illegal label references will be rejected.

For more information about condition handlers and writing them to avoid invalid jumps, see Section 12.8.4.2, “DECLARE for Handlers”.

  • Incompatible change: If you have created a user-defined function (UDF) with a given name and upgrade MySQL to a version that implements a new built-in function with the same name, the UDF becomes inaccessible. To correct this, use DROP FUNCTION to drop the UDF, and then use CREATE FUNCTION to re-create the UDF with a different non-conflicting name. If a new version of MySQL implements a built-in function with the same name as an existing stored function, you have two choices: Rename the stored function to use a non-conflicting name, or change calls to the function so that they use a database qualifier (that is, use db_name.func_name() syntax). See Section 8.2.3, “Function Name Parsing and Resolution”, for the rules describing how the server interprets references to different kinds of functions.
  • Incompatible change: The parser accepted statements that contained /* … */ that were not properly closed with */, such as SELECT 1 /* + 2. As of MySQL 5.0.50, statements that contain unclosed /*-comments now are rejected with a syntax error.

This fix has the potential to cause incompatibilities. Because of Bug#26302, which caused the trailing */ to be truncated from comments in views, stored routines, triggers, and events, it is possible that objects of those types may have been stored with definitions that now will be rejected as syntactically invalid. Such objects should be dropped and re-created so that their definitions do not contain truncated comments. If a stored object definition contains only a single statement (does not use a BEGIN … END block) and contains a comment within the statement, the comment should be moved to follow the statement or the object should be rewritten to use a BEGIN … END block. For example, this statement:

CREATE PROCEDURE p() SELECT 1 /* my comment */ ;

Can be rewritten in either of these ways:

CREATE PROCEDURE p() SELECT 1; /* my comment */

CREATE PROCEDURE p() BEGIN SELECT 1 /* my comment */ ; END;

  • Incompatible change: Beginning with MySQL 5.0.12, natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. The changes include elimination of redundant output columns for NATURAL joins and joins specified with a USING clause and proper ordering of output columns. The precedence of the comma operator also now is lower compared to JOIN, LEFT JOIN, and so forth.

These changes make MySQL more compliant with standard SQL. However, they can result in different output columns for some joins. Also, some queries that appeared to work correctly prior to 5.0.12 must be rewritten to comply with the standard. For details about the scope of the changes and examples that show what query rewrites are necessary, see Section 12.2.8.1, “JOIN Syntax”.

  • Incompatible change: The namespace for triggers has changed in MySQL 5.0.10. Previously, trigger names had to be unique per table. Now they must be unique within the schema (database). An implication of this change is that DROP TRIGGER syntax now uses a schema name instead of a table name (schema name is optional and, if omitted, the current schema will be used).

After upgrading from a previous version of MySQL 5 to MySQL 5.0.10 or newer, you must drop all triggers and re-create them or DROP TRIGGER will not work after the upgrade. Here is a suggested procedure for doing this:

    1. Upgrade to MySQL 5.0.10 or later to be able to access trigger information in the INFORMATION_SCHEMA.TRIGGERS table. (It should work even for pre-5.0.10 triggers.)
    2. Dump all trigger definitions using the following SELECT statement:

3. SELECT CONCAT(‘CREATE TRIGGER ‘, t.TRIGGER_SCHEMA, ‘.’, t.TRIGGER_NAME,

4. ‘ ‘, t.ACTION_TIMING, ‘ ‘, t.EVENT_MANIPULATION, ‘ ON ‘,

5. t.EVENT_OBJECT_SCHEMA, ‘.’, t.EVENT_OBJECT_TABLE,

6. ‘ FOR EACH ROW ‘, t.ACTION_STATEMENT, ‘//’ )

7. INTO OUTFILE ‘/tmp/triggers.sql’

8. FROM INFORMATION_SCHEMA.TRIGGERS AS t;

The statement uses INTO OUTFILE, so you must have the FILE privilege. The file will be created on the server host. Use a different filename if you like. To be 100% safe, inspect the trigger definitions in the triggers.sql file, and perhaps make a backup of the file.

    1. Stop the server and drop all triggers by removing all .TRG files in your database directories. Change location to your data directory and issue this command:

10.shell> rm */*.TRG

    1. Start the server and re-create all triggers using the triggers.sql file. For the file created earlier, use these commands in the mysql program:

12.mysql> delimiter // ;

13.mysql> source /tmp/triggers.sql //

    1. Use the SHOW TRIGGERS statement to check that all triggers were created successfully.
  • Incompatible change: As of MySQL 5.0.15, the CHAR() function returns a binary string rather than a string in the connection character set. An optional USING charset_name clause may be used to produce a result in a specific character set instead. Also, arguments larger than 256 produce multiple characters. They are no longer interpreted modulo 256 to produce a single character each. These changes may cause some incompatibilities:
    • CHAR(ORD(‘A’)) = ‘a’ is no longer true:

o mysql> SELECT CHAR(ORD(‘A’)) = ‘a’;

o +———————-+

o | CHAR(ORD(‘A’)) = ‘a’ |

o +———————-+

o | 0 |

o +———————-+

To perform a case-insensitive comparison, you can produce a result string in a non-binary character set by adding a USING clause or converting the result:

mysql> SELECT CHAR(ORD(‘A’) USING latin1) = ‘a’;

+———————————–+

| CHAR(ORD(‘A’) USING latin1) = ‘a’ |

+———————————–+

| 1 |

+———————————–+

mysql> SELECT CONVERT(CHAR(ORD(‘A’)) USING latin1) = ‘a’;

+——————————————–+

| CONVERT(CHAR(ORD(‘A’)) USING latin1) = ‘a’ |

+——————————————–+

| 1 |

+——————————————–+

    • CREATE TABLE … SELECT CHAR(…) produces a VARBINARY column, not a VARCHAR column. To produce a VARCHAR column, use USING or CONVERT() as just described to convert the CHAR() result into a non-binary character set.
    • Previously, the following statements inserted the value 0×00410041 (‘AA’ as a ucs2 string) into the table:

o CREATE TABLE t (ucs2_column CHAR(2) CHARACTER SET ucs2);

o INSERT INTO t VALUES (CHAR(0×41,0×41));

As of MySQL 5.0.15, the statements insert a single ucs2 character with value 0×4141.

  • Incompatible change: By default, integer subtraction involving an unsigned value should produce an unsigned result. Tracking of the “unsignedness” of an expression was improved in MySQL 5.0.13. This means that, in some cases where an unsigned subtraction would have resulted in a signed integer, it now results in an unsigned integer. One context in which this difference manifests itself is when a subtraction involving an unsigned operand would be negative.

Suppose that i is a TINYINT UNSIGNED column and has a value of 0. The server evaluates the following expression using 64-bit unsigned integer arithmetic with the following result:

mysql> SELECT i – 1 FROM t;

+———————-+

| i – 1 |

+———————-+

| 18446744073709551615 |

+———————-+

If the expression is used in an UPDATE t SET i = i – 1 statement, the expression is evaluated and the result assigned to i according to the usual rules for handling values outside the column range or 0 to 255. That is, the value is clipped to the nearest endpoint of the range. However, the result is version-specific:

    • Before MySQL 5.0.13, the expression is evaluated but is treated as the equivalent 64-bit signed value (–1) for the assignment. The value of –1 is clipped to the nearest endpoint of the column range, resulting in a value of 0:

o mysql> UPDATE t SET i = i – 1; SELECT i FROM t;

o +——+

o | i |

o +——+

o | 0 |

o +——+

    • As of MySQL 5.0.13, the expression is evaluated and retains its unsigned attribute for the assignment. The value of 18446744073709551615 is clipped to the nearest endpoint of the column range, resulting in a value of 255:

o mysql> UPDATE t SET i = i – 1; SELECT i FROM t;

o +——+

o | i |

o +——+

o | 255 |

o +——+

To get the older behavior, use CAST() to convert the expression result to a signed value:

UPDATE t SET i = CAST(i – 1 AS SIGNED);

Alternatively, set the NO_UNSIGNED_SUBTRACTION SQL mode. However, this will affect all integer subtractions involving unsigned values.

  • Incompatible change: Before MySQL 5.0.13, NOW() and SYSDATE() return the same value (the time at which the statement in which the function occurs begins executing). As of MySQL 5.0.13, SYSDATE() returns the time at which it executes, which can differ from the value returned by NOW(). For information about the implications for binary logging, replication, and use of indexes, see the description for SYSDATE() in Section 11.6, “Date and Time Functions” and for SET TIMESTAMP in Section 12.5.4, “SET Syntax”. To restore the former behavior for SYSDATE() and cause it to be an alias for NOW(), start the server with the –sysdate-is-now option (available as of MySQL 5.0.20).
  • Incompatible change: Before MySQL 5.0.13, GREATEST(x,NULL) and LEAST(x,NULL) return x when x is a non-NULL value. As of 5.0.3, both functions return NULL if any argument is NULL, the same as Oracle. This change can cause problems for applications that rely on the old behavior.
  • Incompatible change: Before MySQL 4.1.13/5.0.8, conversion of DATETIME values to numeric form by adding zero produced a result in YYYYMMDDHHMMSS format. The result of DATETIME+0 is now in YYYYMMDDHHMMSS.000000 format.
  • Incompatible change: In MySQL 4.1.12/5.0.6, the behavior of LOAD DATA INFILE and SELECT … INTO OUTFILE has changed when the FIELDS TERMINATED BY and FIELDS ENCLOSED BY values both are empty. Formerly, a column was read or written the display width of the column. For example, INT(4) was read or written using a field with a width of 4. Now columns are read and written using a field width wide enough to hold all values in the field. However, data files written before this change was made might not be reloaded correctly with LOAD DATA INFILE for MySQL 4.1.12/5.0.6 and up. This change also affects data files read by mysqlimport and written by mysqldump –tab, which use LOAD DATA INFILE and SELECT … INTO OUTFILE. For more information, see Section 12.2.6, “LOAD DATA INFILE Syntax”.
  • Incompatible change: The implementation of DECIMAL has changed in MySQL 5.0.3. You should make your applications aware of this change. For information about this change, and about possible incompatibilities with old applications, see Section 11.13, “Precision Math”, in particular, Section 11.13.2, “DECIMAL Data Type Changes”.

DECIMAL columns are stored in a more efficient format. To convert a table to use the new DECIMAL type, you should do an ALTER TABLE on it. (The ALTER TABLE also will change the table’s VARCHAR columns to use the new VARCHAR data type properties, described in a separate item.)

A consequence of the change in handling of the DECIMAL and NUMERIC fixed-point data types is that the server is more strict to follow standard SQL. For example, a data type of DECIMAL(3,1) stores a maximum value of 99.9. Before MySQL 5.0.3, the server allowed larger numbers to be stored. That is, it stored a value such as 100.0 as 100.0. As of MySQL 5.0.3, the server clips 100.0 to the maximum allowable value of 99.9. If you have tables that were created before MySQL 5.0.3 and that contain floating-point data not strictly legal for the data type, you should alter the data types of those columns. For example:

ALTER TABLE tbl_name MODIFY col_name DECIMAL(4,1);

The behavior used by the server for DECIMAL columns in a table depends on the version of MySQL used to create the table. If your server is from MySQL 5.0.3 or higher, but you have DECIMAL columns in tables that were created before 5.0.3, the old behavior still applies to those columns. To convert the tables to the newer DECIMAL format, dump them with mysqldump and reload them.

  • Incompatible change: MySQL 5.0.3 and up uses precision math when calculating with DECIMAL and integer columns (64 decimal digits) and for rounding exact-value numbers. Rounding behavior is well-defined, not dependent on the implementation of the underlying C library. However, this might result in incompatibilities for applications that rely on the old behavior. (For example, inserting .5 into an INT column results in 1 as of MySQL 5.0.3, but might be 0 in older versions.) For more information about rounding behavior, see Section 11.13.4, “Rounding Behavior”, and Section 11.13.5, “Precision Math Examples”.
  • Incompatible change: MyISAM and InnoDB tables created with DECIMAL columns in MySQL 5.0.3 to 5.0.5 will appear corrupt after an upgrade to MySQL 5.0.6. (The same incompatibility will occur for these tables created in MySQL 5.0.6 after a downgrade to MySQL 5.0.3 to 5.0.5.) If you have such tables, check and repair them with mysql_upgrade after upgrading. See Section 4.4.9, “mysql_upgrade — Check Tables for MySQL Upgrade”.
  • Incompatible change: For user-defined functions, exact-value decimal arguments such as 1.3 or DECIMAL column values were passed as REAL_RESULT values prior to MySQL 5.0.3. As of 5.0.3, they are passed as strings with a type of DECIMAL_RESULT. If you upgrade to 5.0.3 and find that your UDF now receives string values, use the initialization function to coerce the arguments to numbers as described in Section 21.2.2.3, “UDF Argument Processing”.
  • Incompatible change: Before MySQL 5.0.2, SHOW STATUS returned global status values. The default as of 5.0.2 is to return session values, which is incompatible with previous versions. To issue a SHOW STATUS statement that will retrieve global status values for all versions of MySQL, write it like this:

· SHOW /*!50002 GLOBAL */ STATUS;

  • Incompatible change: User variables are not case sensitive in MySQL 5.0. In MySQL 4.1, SET @x = 0; SET @X = 1; SELECT @x; created two variables and returned 0. In MySQL 5.0, it creates one variable and returns 1. Replication setups that rely on the old behavior may be affected by this change.
  • Some keywords are reserved in MySQL 5.0 that were not reserved in MySQL 4.1. See Section 8.3, “Reserved Words”.
  • The LOAD DATA FROM MASTER and LOAD TABLE FROM MASTER statements are deprecated. See Section 12.6.2.2, “LOAD DATA FROM MASTER Syntax”, for recommended alternatives.
  • As of MySQL 5.0.3, trailing spaces no longer are removed from values stored in VARCHAR and VARBINARY columns. The maximum lengths for VARCHAR and VARBINARY columns in MySQL 5.0.3 and later are 65,535 characters and 65,535 bytes, respectively.

When a binary upgrade (filesystem-level copy of data files) to MySQL 5.0 is performed for a table with a VARBINARY column, the column is space-padded to the full allowable width of the column. This causes values in VARBINARY columns that do not occupy the full width of the column to include extra trailing spaces after the upgrade, which means that the data in the column is different.

In addition, new rows inserted into a table upgraded in this way will be space padded to the full width of the column.

This issue can be resolved as follows:

    1. For each table containing VARBINARY columns, execute the statement

1. ALTER TABLE table_name ENGINE=engine_name;

where table_name is the name of the table and engine_name is the name of the storage engine currently used by table_name. In other words, if the table named mytable uses the MyISAM storage engine, then you would use this statement:

ALTER TABLE mytable ENGINE=MYISAM;

This rebuilds the table so that it uses the 5.0 VARBINARY format.

    1. Then you must remove all trailing spaces from any VARBINARY column values. For each VARBINARY column varbinary_column, you should perform the following statement (where table_name is the name of the table containing the VARBINARY column):

3. UPDATE table_name SET varbinary_column = RTRIM(varbinary_column);

This is necessary and safe because trailing spaces are stripped before 5.0.3, meaning that any trailing spaces are erroneous.

This problem does not occur (and thus these two steps are not required) for tables upgraded using the recommended procedure of dumping tables prior to the upgrade and reloading them afterwards.

Note

If you create a table with new VARCHAR or VARBINARY columns in MySQL 5.0.3 or later, the table will not be usable if you downgrade to a version older than 5.0.3. Dump the table with mysqldump before downgrading and reload it after downgrading.

  • Comparisons made between FLOAT or DOUBLE values that happened to work in MySQL 4.1 may not do so in 5.0. Values of these types are imprecise in all MySQL versions, and you are strongly advised to avoid such comparisons as WHERE col_name=some_double, regardless of the MySQL version you are using. See Section B.1.5.8, “Problems with Floating-Point Comparisons”.
  • As of MySQL 5.0.3, BIT is a separate data type, not a synonym for TINYINT(1). See Section 10.1.1, “Overview of Numeric Types”.
  • MySQL 5.0.2 adds several SQL modes that allow stricter control over rejecting records that have invalid or missing values. See Section 5.1.7, “Server SQL Modes”, and Section 1.7.6.2, “Constraints on Invalid Data”. If you want to enable this control but continue to use MySQL’s capability for storing incorrect dates such as ’2004-02-31′, you should start the server with –sql_mode="TRADITIONAL,ALLOW_INVALID_DATES".
  • As of MySQL 5.0.2, the SCHEMA and SCHEMAS keywords are accepted as synonyms for DATABASE and DATABASES, respectively. (While “schemata” is grammatically correct and even appears in some MySQL 5.0 system database and table names, it cannot be used as a keyword.)
  • As of MySQL 5.0.25, TIMESTAMP columns that are NOT NULL now are reported that way by SHOW COLUMNS and INFORMATION_SCHEMA, rather than as NULL.

C API Changes:

  • Incompatible change: Because the MySQL 5.0 server has a new implementation of the DECIMAL data type, a problem may occur if the server is used by older clients that still are linked against MySQL 4.1 client libraries. If a client uses the binary client/server protocol to execute prepared statements that generate result sets containing numeric values, an error will be raised: ‘Using unsupported buffer type: 246′

This error occurs because the 4.1 client libraries do not support the new MYSQL_TYPE_NEWDECIMAL type value added in 5.0. There is no way to disable the new DECIMAL data type on the server side. You can avoid the problem by relinking the application with the client libraries from MySQL 5.0.

  • Incompatible change: The ER_WARN_DATA_TRUNCATED warning symbol was renamed to WARN_DATA_TRUNCATED in MySQL 5.0.3.
  • The reconnect flag in the MYSQL structure is set to 0 by mysql_real_connect(). Only those client programs which did not explicitly set this flag to 0 or 1 after mysql_real_connect() experience a change. Having automatic reconnection enabled by default was considered too dangerous (due to the fact that table locks, temporary tables, user variables, and session variables are lost after reconnection).

MySQL Enterprise. MySQL Enterprise subscribers will find more information about upgrading in the Knowledge Base articles found at Upgrading. Access to the MySQL Knowledge Base collection of articles is one of the advantages of subscribing to MySQL Enterprise. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

Categories: MYSQL, Upgrade Tags: