Cleaning the log tables and rebuilding indexes with shell scripts

magento shells scripts usage

Based on the same idea as my “cache clearing script” (but 10x better), Magento now includes some handy scripts to clean the log tables, to compile source files and to rebuild indexes. For people with very large databases (10k+ products) this is sometimes the only way to actually accomplish something without running out of memory or exceeding execution time limits. The scripts are located in the /shell folder. These scripts are made to be run manually by logging on the server with SSH or they may also be setup to be executed periodically as a cron job.

Below, is a little preview of the possible operations

Usage: php -f log.php -- [options]
php -f log.php -- clean --days 1
clean                Clean Logs
--days <days>  Save log, days. (Minimum 1 day, if defined - ignoring system value)
status               Display statistics per log tables
help                 This help
Usage: php -f indexer.php -- [options]
--status <indexer>         Show Indexer(s) Status
--mode <indexer>           Show Indexer(s) Index Mode
--mode-realtime <indexer>  Set index mode type "Update on Save"
--mode-manual <indexer>    Set index mode type "Manual Update"
--reindex <indexer>        Reindex Data
info                       Show allowed indexers
reindexall                 Reindex Data by all indexers
help                       This help
<indexer> Comma separated indexer codes or value "all" for all indexers
Usage:  php -f compiler.php -- [options]
state         Show Compilation State
compile       Run Compilation Process
clear         Disable Compiler include path and Remove compiled files
enable        Enable Compiler include path
disable       Disable Compiler include path
help          This help

Netbeans Preparing Commit

If you ever tried to SVN commit a big project using Netbeans (Magento?) you have most likely seen that “Preparing Commit” message doing seemingly absolutely nothing for several minutes. Apparently the commit takes forever because Netbeans is scanning the entire projet to localize any changes done by external applications before committing. This is not really an issue in 99% of my usual work days thus I had to find a way to disable this.

Seems that adding

-J-Dnetbeans.subversion.commit.deepStatusRefresh=false

To he netbeans configuration file will disable the automatic scan during the commit preparation phase. If for whatever reason  you end up changing a file outside Netbeans (external text editor, adding new pictures…) you cam simply go to the “Files” tab and right-click your projet and hit “Refresh Folder” to let it scan everything.

See http://wiki.netbeans.org/FaqNetbeansConf for information on how to change the netbeans configuration file.

Running MySQL Proxy as a daemon

I had to figure out how to setup mySQL Proxy to run as a daemon (system service).
The original information came from this page and has been slightly modified.
It is assumed that mySQL Proxy has been installed and the mysql-proxy executable is located at /usr/local/sbin/mysql-proxy.
The first file is the init.d launch script:

/etc/init.d/mysql-proxy

#!/bin/sh
#
# mysql-proxy This script starts and stops the mysql-proxy daemon
#
# chkconfig: - 78 30
# processname: mysql-proxy
# description: mysql-proxy is a proxy daemon to mysql

# Source function library.
. /etc/rc.d/init.d/functions

PROXY_PATH="/usr/local/sbin"

prog="mysql-proxy"

# Source networking configuration.
. /etc/sysconfig/network

# Check that networking is up.
[ ${NETWORKING} = "no" ] && exit 0

# Set default mysql-proxy configuration.
PROXY_OPTIONS="--daemon"
PROXY_PID=/var/run/mysql-proxy.pid

# Source mysql-proxy configuration.
if [ -f /etc/sysconfig/mysql-proxy ] ; then
        . /etc/sysconfig/mysql-proxy
fi

PATH=$PATH:/usr/bin:/usr/local/bin:$PROXY_PATH

# By default it's all good
RETVAL=0

# See how we were called.
case "$1" in
  start)
        # Start daemon.
        echo -n $"Starting $prog: "
        daemon $NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS --pid-file $PROXY_PID
        RETVAL=$?
        echo
        if [ $RETVAL = 0 ]; then
                touch /var/lock/subsys/mysql-proxy
        fi
        ;;
  stop)
        # Stop daemons.
        echo -n $"Stopping $prog: "
        killproc $prog
        RETVAL=$?
        echo
        if [ $RETVAL = 0 ]; then
                rm -f /var/lock/subsys/mysql-proxy
                rm -f $PROXY_PID
        fi
        ;;
  restart)
        $0 stop
        sleep 3
        $0 start
        ;;
  condrestart)
       [ -e /var/lock/subsys/mysql-proxy ] && $0 restart
       ;;
  status)
        status mysql-proxy
        RETVAL=$?
        ;;
  *)
        echo "Usage: $0 {start|stop|restart|status|condrestart}"
        RETVAL=1
        ;;
esac

exit $RETVAL
The launch script will either use the default configuration or read it from the file /etc/sysconfig/mysql-proxy if it exists.
To set mysql-proxy parameters create:

/etc/sysconfig/mysql-proxy

# Options to mysql-proxy
# do not remove --daemon
PROXY_OPTIONS="
 --daemon
 --proxy-backend-addresses=127.0.0.1:3306
 --proxy-backend-addresses=127.0.0.1:3307
 --proxy-lua-script=load-balancing.lua"

(partial) MySQL Proxy API Doc

I have been working a lot with MySQL proxy lately and trying to figure out how it works is a major PITA. There is virtually no documentation for it and the best way to understand how it actually works is to read the source code.

This is a list of API functions discovered by reading plugin.c in the mysql_proxy SVN source. The list may be incomplete or not entirely accurate.

[i] = integer based index

[s] = string based index

[proxy]

proxy.global – data structure shared amongst all lua state machines

proxy.global.config – sub structure (possibly added for esthetical reasons only)

proxy.backends – array of backends (shared)

proxy.connection – mysql connection object

[proxy.backends[i]]

proxy.backends[i].connected_clients – connected clients

proxy.backends[i].address – server Address

proxy.backends[i].state – status {BACKEND_STATE_UNKNOWN, BACKEND_STATE_UP, BACKEND_STATE_DOWN}

proxy.backends[i].type – type {BACKEND_TYPE_UNKNOWN, BACKEND_TYPE_RW, BACKEND_TYPE_RO}

proxy.backends[i].pool – connection pool object

[proxy.backends[i].pool]

proxy.backends[i].pool.max_idle_connections – max connections

proxy.backends[i].pool.min_idle_connections – min connections

proxy.backends[i].pool.users – hash table containing sockets hashed by username

[proxy.backends[i].pool.users[s]]

proxy.backends[i].pool.users[s].cur_idle_connections – number of sockets currently in the pool (meaning they are idle)

[proxy.connection]

proxy.connection.backend_ndx – id of active backend (magic value, setting it can trigger server socket changes, i.e: setting it to 0 returns the socket into the connection pool)

proxy.connection.server – server socket object

proxy.connection.client – client socket object

[proxy.connection.client]

proxy.connection.client.default_db – database

proxy.connection.client.username – user

proxy.connection.client.address – ip address (?)

proxy.connection.client.scrambled_password – password as it was sent from the client

[proxy.connection.server]

proxy.connection.server.default_db – database

proxy.connection.server.username – user

proxy.connection.server.address – ip address (?)

proxy.connection.server.scrambled_password – password as it was sent from the client

proxy.connection.server.mysqld_version – mysql version

proxy.connection.server.thread_id – connection id as set during the server handshake

proxy.connection.server.scramble_buffer – password hash (?)

Add a self-generated SSL certificate to the list of trusted certificates

Usually Java only accepts SSL certificates that can be validated with one of the CA providers in JRE’s internal cacerts keystore.

The cacerts keystore is a file located at $JAVA_HOME/lib/security/cacerts

How to import a self generated SSL certificate

First, export the self-generated key 'mywebsite.com' to a file called mywebsite.com.cert on the server
keytool -export -keystore ~/mywebsite.com.keystore -alias mywebsite.com -file mywebsite.com.cert

Then download the cert file with FTP or SFTP to your local computer.

Finally, import the certificate 'mywebsite.com.cert' into a local cacerts keystore:
keytool -import -keystore $JAVA_HOME/lib/security/cacerts -storetype jks -alias mywebsite.com -file ./mywebsite.com.cert

References

keytool export command ˆ
keytool import command ˆ Continue reading “Add a self-generated SSL certificate to the list of trusted certificates”