Recently I realized that in MySQL if you declare a column as type TIMESTAMP, it would be automatically updated to the current time stamp any time the row is updated.

mysql> CREATE TABLE IF NOT EXISTS `MyTable` (
`id` int(10) NOT NULL auto_increment,
`name` varchar(50) NOT NULL,
`expiry_date` TIMESTAMP NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

mysql> desc MyTable;

If you don’t want this behavior then you can use the following command:

mysql> ALTER TABLE MyTable CHANGE expiry_date expiry_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

mysql> desc MyTable;

Point is, Timestamps in MySQL are generally used to track changes to records, and are updated every time the record is changed. However if you want to store a specific value and don’t want it updated, its best you use a DATETIME field.

Change it using the following command:

mysql> ALTER TABLE MyTable CHANGE expiry_date expiry_date DATETIME NOT NULL DEFAULT ‘2012-10-01 00:00:00’;

mysql> desc MyTable;

I also learned that if a table has 2 Timestamps than MySQL does not know which one it needs to do an auto update. Hence this behavior does not work. The work around is:

mysql> CREATE TABLE IF NOT EXISTS `AnotherTable` (
`id` int(10) NOT NULL auto_increment,
`name` varchar(150) NOT NULL,
`creation_date` TIMESTAMP DEFAULT ‘0000-00-00 00:00:00’,
`last_updated` TIMESTAMP DEFAULT now() on update now(),
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Just learned today that for char or varchar columns, MySQL ignores trailing white spaces for comparison in a select statement’s where clause.

This came as a surprise because I was thinking that the string ‘[email protected]’ is different from ‘[email protected] ‘.

mysql> CREATE TABLE `mysql_trailing_test` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
`email` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

mysql> INSERT INTO `mysql_trailing_test` VALUES (1, 'Naresh', '[email protected]');

mysql> SELECT * FROM `mysql_trailing_test` WHERE name='Naresh';

1 row in set (0.01 sec)

Perfect, as expected. But watch now:

mysql> SELECT * FROM `mysql_trailing_test` WHERE name='Naresh ';

1 row in set (0.01 sec)

AND

mysql> INSERT INTO `mysql_trailing_test` VALUES (2, 'Naresh ', '[email protected]');

mysql> SELECT * FROM `mysql_trailing_test` WHERE name='Naresh';

2 row in set (0.01 sec)

Also because of this, if you had a unique constraint on the column and tried to insert another value with trailing space, MySQL would return ERROR 1062 (23000): Duplicate entry.

mysql> INSERT INTO `mysql_trailing_test` VALUES (3, 'Naresh  ', '[email protected] ');

ERROR 1062 (23000): Duplicate entry ‘[email protected] ‘ for key ’email’

More details on MySQL Documentation: The CHAR and VARCHAR Types

Recently I was trying to load a CSV file into a MySQL database on a Linux box using the following command:

LOAD DATA INFILE '/path/to/my_data.csv' INTO TABLE `my_data` 
        FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
        LINES TERMINATED BY '\r\n';

I kept getting the following error:

ERROR 1045 (28000): Access denied for user …

After Googling around I got some suggestions:

• chown mysql.mysql my_data.csv; chmod 666 my_data.csv
• move the data file to /tmp folder, where all processes have read access
• Grant a FILE permissions to the user. Basically GRANT *.* usage.
• Load data LOCAL infile…

Nothing seemed to help.

Luckily I found the following command which did the job:

mysqlimport -v --local --fields-enclosed-by='"' 
        --fields-escaped-by='\' 
        --fields-terminated-by=',' 
        --lines-terminated-by='\r\n' 
        -u[db_user] -p[db_password] -h [hostname] [db_name] 
        '/path/to/my_data.csv'

If you have your web application running on one tomcat instance and want to add another tomcat instance (ideally on a different machine), following steps will guide you.

Step 1: Independently deploy your web application (WAR file) on each instance and make sure they can work independently.

Step 2: Stop tomcat

Step 3: Update the <Cluster> element under the <Engine> element in the Server.xml file (under the conf dir in tomcat installation dir) on both your servers with:

<Engine name="<meaningful_unique_name>" defaultHost="localhost">      
     <Cluster className="org.apache.catalina.ha.tcp.SimpleTcpCluster"
              channelSendOptions="8">
          <Manager className="org.apache.catalina.ha.session.DeltaManager"
                   expireSessionsOnShutdown="false"
                   notifyListenersOnReplication="true"/>
          <Channel className="org.apache.catalina.tribes.group.GroupChannel">
               <Membership className="org.apache.catalina.tribes.membership.McastService"
                           address="228.0.0.4"
                           port="45564"
                           frequency="500"
                           dropTime="3000"/>
               <Receiver className="org.apache.catalina.tribes.transport.nio.NioReceiver"
                         address="auto"
                         port="4000"
                         autoBind="100"
                         selectorTimeout="5000"
                         maxThreads="6"/>
               <Sender className="org.apache.catalina.tribes.transport.ReplicationTransmitter">
                   <Transport className="org.apache.catalina.tribes.transport.nio.PooledParallelSender"/>
               </Sender>
               <Interceptor className="org.apache.catalina.tribes.group.interceptors.TcpFailureDetector"/>
               <Interceptor className="org.apache.catalina.tribes.group.interceptors.MessageDispatch15Interceptor"/>
          </Channel>
          <Valve className="org.apache.catalina.ha.tcp.ReplicationValve"
                 filter=".*\.gif;.*\.js;.*\.jpg;.*\.png;.*\.css;.*\.txt;"/>
          <ClusterListener className="org.apache.catalina.ha.session.ClusterSessionListener"/>
     </Cluster>
     ...
</Engine>

For more details on these parameters, check https://sec1.woopra.com/docs/cluster-howto.html

Step 4: Start tomcat and make sure it starts up correctly. You should be able to access http://locahost:8080. Most default tomcat installations come with an examples web app. Try access http://localhost:8080/examples/jsp/ You should see a list of JSP files.

Step 4.a: Also if you see catalina.out log file, you should see:

INFO: Initializing Coyote HTTP/1.1 on http-8080
Nov 9, 2009 9:29:43 AM org.apache.catalina.startup.Catalina load
INFO: Initialization processed in 762 ms
Nov 9, 2009 9:29:43 AM org.apache.catalina.core.StandardService start
INFO: Starting service <server_name>
Nov 9, 2009 9:29:43 AM org.apache.catalina.core.StandardEngine start
INFO: Starting Servlet Engine: Apache Tomcat/6.0.16
Nov 9, 2009 9:29:43 AM org.apache.catalina.ha.tcp.SimpleTcpCluster start
INFO: Cluster is about to start
Nov 9, 2009 9:29:43 AM org.apache.catalina.tribes.transport.ReceiverBase bind
INFO: Receiver Server Socket bound to:/<server_ip>:4000
Nov 9, 2009 9:29:43 AM org.apache.catalina.tribes.membership.McastServiceImpl setupSocket
INFO: Setting cluster mcast soTimeout to 500
Nov 9, 2009 9:29:43 AM org.apache.catalina.tribes.membership.McastServiceImpl waitForMembers
INFO: Sleeping for 1000 milliseconds to establish cluster membership, start level:4

Step 5: Stop tomcat.

Step 6: We’ll use the examples web app to test if our session replication is working as expected.

Step 6.a: Open the Web.xml file of the “examples” web app in your webapps. Mark this web app distributable, by adding a <distributable/> element at the end of the Web.xml file (just before the </web-app> element)

Step 6.b: Add the session JSP file. This JSP prints the contents of the session and also adds/increments a counter stored in the session.

Step 6.c: Start tomcat on both machines

Step 6.d: You should see the following log in catalina.out

Nov 9, 2009 9:29:44 AM org.apache.catalina.ha.tcp.SimpleTcpCluster memberAdded
INFO: Replication member added:org.apache.catalina.tribes.membership.MemberImpl[tcp://{-64, -88, 0, 101}:4000,{-64, -88, 0, 101},4000, alive=10035,id={68 106 92 39 -110 -8 73 124 -116 -122 -15 -3 11 117 56 105 }, payload={}, command={}, domain={}, ] 
 
Nov 9, 2009 9:29:49 AM org.apache.catalina.ha.session.DeltaManager start
INFO: Register manager /examples to cluster element Engine with name <server_name>
Nov 9, 2009 9:29:49 AM org.apache.catalina.ha.session.DeltaManager start
INFO: Starting clustering manager at /examples
Nov 9, 2009 9:29:49 AM org.apache.catalina.ha.session.DeltaManager getAllClusterSessions
WARNING: Manager [localhost#/examples], requesting session state from org.apache.catalina.tribes.membership.MemberImpl[tcp://{-64, -88, 0, 101}:4000,{-64, -88, 0, 101},4000, alive=15538,id={68 106 92 39 -110 -8 73 124 -116 -122 -15 -3 11 117 56 105 }, payload={}, command={}, domain={}, ]. This operation will timeout if no session state has been received within 60 seconds.
Nov 9, 2009 9:29:49 AM org.apache.catalina.ha.session.DeltaManager waitForSendAllSessions
INFO: Manager [localhost#/examples]; session state send at 11/9/09 9:29 AM received in 101 ms.
 
Nov 9, 2009 9:29:49 AM org.apache.catalina.core.ApplicationContext log
INFO: ContextListener: contextInitialized()
Nov 9, 2009 9:29:49 AM org.apache.catalina.core.ApplicationContext log
INFO: SessionListener: contextInitialized()
Nov 9, 2009 9:29:50 AM org.apache.coyote.http11.Http11Protocol start
INFO: Starting Coyote HTTP/1.1 on http-8080
Nov 9, 2009 9:29:50 AM org.apache.jk.common.ChannelSocket init
INFO: JK: ajp13 listening on /0.0.0.0:8009
Nov 9, 2009 9:29:50 AM org.apache.jk.server.JkMain start
INFO: Jk running ID=0 time=0/49  config=null
Nov 9, 2009 9:29:50 AM org.apache.catalina.startup.Catalina start
INFO: Server startup in 6331 ms

Step 6.e: Try to access http://localhost:8080/examples/jsp/session.jsp Try refreshing the page a few times, you should see the counter getting updated.

Step 6.f: You should see the same behavior when you try to access the other tomcat server. Open another tab in your browser and hit http://<other_server_ip>:8080/examples/jsp/session.jsp

Step 6.g: At this point we know the app works fine and the session is working correctly. Now we want to check if the tomcat cluster is replicating the session info. To check this, we want to pass the session from server 1 to session 2 and see if it increments the counter from where we left.

Step 6.h: Before accessing the page, make sure you copy the j_session_id from server 1 (displayed on the http://localhost:8080/examples/jsp/session.jsp). Also make sure to clear all cookies from server 2. (All browsers give you a facility to clear cookies from a specific host/ip).

Step 6.i: Now hit http://<server_2_ip>:8080/examples/jsp/session.jsp;jsessionid=<jsession_id_from_server1>

Step 6.j: If you see the counter incrementing from where ever you had left, congrats! You have session replication working.

Step 6.k: Also catalina.out log file should have:

Nov 9, 2009 9:42:03 AM org.apache.catalina.core.ApplicationContext log
INFO: SessionListener: sessionCreated('CDC57B8C5CFDFDDC2C8572E7D14C0D28')
Nov 9, 2009 9:42:03 AM org.apache.catalina.core.ApplicationContext log
INFO: SessionListener: attributeAdded('CDC57B8C5CFDFDDC2C8572E7D14C0D28', 'counter', '1')
Nov 9, 2009 9:42:05 AM org.apache.catalina.core.ApplicationContext log
INFO: SessionListener: attributeReplaced('CDC57B8C5CFDFDDC2C8572E7D14C0D28', 'counter', '2')

While this might like smooth, I ran into lot of issues when getting to this point. Following are some trap routes I ran into:

1) java.sql.SQLException: No suitable driver tomcat cluster
Make sure your DB Driver jar (in our case mysql-connector-java-x.x.xx-bin.jar) is in tomcat/lib folder

2) In catalina.org if you see the following exception:

Nov 7, 2009 3:48:53 PM org.apache.catalina.ha.session.DeltaManager requestCompleted
SEVERE: Unable to serialize delta request for sessionid [1F43C3926FF3CC231574EF248896DCA6]
java.io.NotSerializableException: com.company.product.Class
	at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1156)
	at java.io.ObjectOutputStream.writeObject(ObjectOutputStream.java:326)
	at java.util.ArrayList.writeObject(ArrayList.java:570)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)

This means that you are storing com.company.product.Class object (or some other object that holds a reference to this Object) in your session. And you’ll need to make com.company.product.Class implement Serializable interface.

3) In your catalina.out log if you see

INFO: Register manager /<your_app_name> to cluster element Engine with name <tomcat_engine_name>
Nov 7, 2009 11:56:20 AM org.apache.catalina.ha.session.DeltaManager start
INFO: Starting clustering manager at /<your_app_name>
Nov 7, 2009 11:56:20 AM org.apache.catalina.ha.session.DeltaManager getAllClusterSessions
INFO: Manager [localhost#/<your_app_name>]: <strong>skipping state transfer. No members active in cluster group</strong>.

If both your tomcat instance are up and running, then check if your tomcat servers can communicate with each other using Multicast with the following commands:

$ ping -t 1 -c 2 228.0.0.4
PING 228.0.0.4 (228.0.0.4): 56 data bytes
64 bytes from <server_1_ip>: icmp_seq=0 ttl=64 time=0.076 ms
64 bytes from <server_2_ip>: icmp_seq=0 ttl=64 time=0.645 ms

— 228.0.0.4 ping statistics —
1 packets transmitted, 1 packets received, +1 duplicates, 0.0% packet loss

or

$ sudo tcpdump -ni en0 host 228.0.0.4
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on en0, link-type EN10MB (Ethernet), capture size 65535 bytes
22:11:50.016147 IP <server_1_ip>.45564 > 228.0.0.4.45564: UDP, length 69
22:11:50.033336 IP <server_2_ip>.45564 > 228.0.0.4.45564: UDP, length 69
22:11:50.516746 IP <server_1_ip>.45564 > 228.0.0.4.45564: UDP, length 69
22:11:50.533613 IP <server_2_ip>.45564 > 228.0.0.4.45564: UDP, length 69
…

If you don’t see the results as described above, you might want to read my blog on Enabling Multicast.

A few months ago I had an exact same issue but with a different table. This time tiki_sessions table crashed.

I still don’t understand what is causing the crash. Luckily I know how to get rid of this issue quickly once I discover it.

executing the following command fixes the issue:
mysqlcheck –auto-repair -u{username} -p{password} {database_name} {database_table_name}

output:

{database.name}.tiki_sessions
error    : Table ‘./{database.name}/tiki_sessions’ is marked as crashed and should be repaired

Repairing tables
{database.name}.tiki_sessions
warning  : Number of rows changed from 0 to 2
status   : OK