MySQL: run a script when logging in

mysql-5.5

Is it possible, in MySQL, to run a 'login profile' similar to the bash/ksh profile, when connecting with the mysql client? That is to say, something similar to mysql -e 'select * from a_table', but where mysql doesn't terminate after executing the statement?

Best Answer

The mysql client has the init-command option, which does what you want, I think.

From the manual:

--init-command=str

SQL statement to execute after connecting to the server. If auto-reconnect is enabled, the statement is executed again after reconnection occurs.

Related Solutions

Mysql – thesql session logging tables

First, from looking at your existing indexes and comparing them to your example query, you are missing an index on just accountid. The way MySQL handles indexes is left-most, meaning you can have a composite index like this:

KEY `contactaccounts` (`contactid`,`accountid`,`journalcode`) USING BTREE,

and run a query that looks for contactid and the contactaccounts index would be a potential index*. However doing a query on accountid will not utilize the index, because accountid is not the left-most column.

If you never search for contactid without an accountid, I would create the index like this:

DROP INDEX `contactaccounts` ON `tbl_sessions_2012`;
CREATE INDEX `accountscontact` ON `tbl_sessions_2012` (`accountid`,`contactid`,`journalcode`);

Now, analyzing your other indexes, the first of each set is redundant using the left-most rule, and can be dropped in favor of the second:

KEY `contactaccounts` (`contactid`,`accountid`,`journalcode`) USING BTREE 
KEY `contactaccountloginmonths` (`contactid`,`accountid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE

KEY `contactaccountcollections` (`contactid`,`accountcollectionid`,`journalcode`) USING BTREE
KEY `contactaccountcollectionloginmonths` (`contactid`,`accountcollectionid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE

KEY `organisationaccounts` (`organisationid`,`accountid`) USING BTREE
KEY `organisationaccountloginmonths` (`organisationid`,`accountid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE

KEY `organisationaccountcollection` (`organisationid`,`accountcollectionid`,`journalcode`) USING BTREE
KEY `organisationaccountcollectionsloginmonths` (`organisationid`,`accountcollectionid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE

By having the duplicates you are using up a lot of space on indexes.

_{* I say potential because there is still the possibility that your index will not be used due to the amount of rows MySQL has to scan through. As your account_id IN () statement grows, MySQL will determine that it's faster just to do a full table scan regardless of the index.}

Mysql – Create Table If Not Exists Hanging

I find this troubling for two reasons

REASON #1

This could be a bug that never got squashed out of MySQL 5.5.17

Nov 08, 2011 : http://bugs.mysql.com/bug.php?id=63144 (Status is Needs Triage on MySQL 5.5.17)
Mar 21, 2011 : http://bugs.mysql.com/bug.php?id=60563 (MySQL 5.5.10)
Jun 23, 2006 : http://bugs.mysql.com/bug.php?id=20662

REASON #2

You are trying to acquire a metadata lock on an InnoDB table in an RDS environment? Ouch !!!

Possible Workaround

Instead of trusting the metadata locking in RDS, ask the information_schema if the table exists already. Suppose the table is mydb.backupfiles. Run this query:

SELECT COUNT(1) FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='backupfiles';

If the answer comes back 0, run CREATE TABLE backupfiles .... Otherwise, don't run it.

Possible Solution

Upgrade to the Latest MySQL (5.6.12) or the Latest MySQL5.5 (5.5.31). Hopefully, this issue was solved in a more recent version

Give it a Try !!!

UPDATE 2013-07-11 15:16 EDT

Here is a rewrite of your bash code to check if the table already exists

    echo "CREATE DATABASE IF NOT EXISTS $DATABASE;" > /tmp/runthis.sql
    echo "use $DATABASE;" >> /tmp/runthis.sql
    SQLSTMT="SELECT COUNT(1) FROM information_schema.tables"
    SQLSTMT="${SQLSTMT} WHERE table_schema='${DATABASE}' AND table_name='backupfiles';"
    TABLE_EXISTS=`mysql -h portal-rds -u $user --password=$mysqlpw -ANe"${SQLSTMT}"`
    if [[ ${TABLE_EXISTS} -eq 0 ]]; then
      SQLSTMT="CREATE TABLE backupfiles (fileName VARCHAR(20),"
      SQLSTMT="${SQLSTMT} archiveId VARCHAR(500), checkSum VARCHAR(100),"
      SQLSTMT="${SQLSTMT} glacierVault VARCHAR(100), timeStamp date);"
      echo "${SQLSTMT}" >> /tmp/runthis.sql
    fi
    SQLSTMT="INSERT INTO backupfiles VALUES ('$archive_file_name',"
    SQLSTMT="${SQLSTMT} '$archiveID', '$CURRENTVAULT', '$checkSum', CURDATE());"
    echo "${SQLSTMT}" >> /tmp/runthis.sql
    echo "COMMIT;" >> /tmp/runthis.sql
    mysql -h portal-rds -u $user --password=$mysqlpw < /tmp/runthis.sql