gh-ost does not support foreign key

gh-ost was released in August, it might be the best tool to upgrade MySQL table structure online so far. But haven’t gotten a chance to try.

This week, I was preparing release of one of our big sites, which has three big tables need to be upgraded (structure change), they have more than 70 million rows in total, data and index occupy more than 30G space on disk. I tried the normal structure change in MySQL, it took me around 5 hours to finish all changes on all of the three tables. That means we have to close the production site for 5 hours, sounds crazy, but what we did always.

Then, I thought maybe I can try this new tool, gh-ost, which created and tested by GitHub. But, finally, I found it does not support foreign key!

user1@db1:~/gh-ost$ ./gh-ost --max-load=Threads_running=25 --critical-load=Threads_running=1000 --chunk-size=1000 --max-lag-millis=1500 --user="user" --password="******" --host="" --allow-on-master --database="database1" --table="TRANSACTION2" --verbose --alter="ALTER TABLE TRANSACTION2 MODIFY COLUMN TEMP_GH_OST_TEST_FIELD_1 INT NULL" --switch-to-rbr --cut-over=default --exact-rowcount --concurrent-rowcount --default-retries=60 --nice-ratio=0.5 --serve-socket-file=/home/user1/gh-ost/game_tx2/ --throttle-flag-file=/home/user1/gh-ost/game_tx2/ --panic-flag-file=/home/user1/gh-ost/game_tx2/ --postpone-cut-over-flag-file=/home/user1/gh-ost/game_tx2/
2016-10-06 03:54:25 INFO starting gh-ost 1.0.20
2016-10-06 03:54:25 INFO Migrating `database1`.`TRANSACTION2`
2016-10-06 03:54:25 INFO connection validated on
2016-10-06 03:54:25 INFO User has ALL privileges
2016-10-06 03:54:25 INFO binary logs validated on
2016-10-06 03:54:25 INFO Restarting replication on to make sure binlog settings apply to replication thread
2016-10-06 03:54:26 INFO Table found. Engine=InnoDB
2016-10-06 03:54:54 INFO Found foreign key on `database1`.`ALARM_LOG` related to `database1`.`TRANSACTION2`
2016-10-06 03:54:54 INFO Found foreign key on `database1`.`TRANSACTION2` related to `database1`.`TRANSACTION2`
2016-10-06 03:54:54 INFO Found foreign key on `database1`.`TRANSACTION2` related to `database1`.`TRANSACTION2`
2016-10-06 03:54:54 INFO Found foreign key on `database1`.`TRANSACTION2` related to `database1`.`TRANSACTION2`
2016-10-06 03:54:54 INFO Found foreign key on `database1`.`UNKNOWN_WIN` related to `database1`.`TRANSACTION2`
2016-10-06 03:54:54 ERROR Found 5 foreign keys related to `database1`.`TRANSACTION2`. Foreign keys are not supported. Bailing out
2016-10-06 03:54:54 FATAL 2016-10-06 03:54:54 ERROR Found 5 foreign keys related to `database1`.`TRANSACTION2`. Foreign keys are not supported. Bailing out

Aixs2: disable chunked encoding

It seems that iCheque doesn’t support chunked transfer in their new version of payment API, but chunked transfer is enabled in AXIS2 by default, so you will get

org.apache.axis2.AxisFault: Transport error: 411 Error: Length Required

When chunked transfer is enabled, the Content-Length will not be present in the HTTP header of request. Because by using chunked transfer, the sender can dynamically generate the content, and send it, the sender doesn’t need to know the length of the content. Another HTTP header, Transfer-Encoding: chunked, will be put in.

If the receiver doesn’t support chunked transfer, you have to disable it.

In AXIS2, you can disable it like this

serviceStub._getServiceClient().getOptions().setProperty(org.apache.axis2.transport.http.HTTPConstants.CHUNKED, Boolean.FALSE)

Process state codes in ps

The meaning of values of column STAT in output of ps command on Linux.

       Here are the different values that the s, stat and state output specifiers (header "STAT" or "S") will display to describe the state of a process:
       D    uninterruptible sleep (usually IO)
       R    running or runnable (on run queue)
       S    interruptible sleep (waiting for an event to complete)
       T    stopped, either by a job control signal or because it is being traced.
       W    paging (not valid since the 2.6.xx kernel)
       X    dead (should never be seen)
       Z    defunct ("zombie") process, terminated but not reaped by its parent.
       For BSD formats and when the stat keyword is used, additional characters may be displayed:
       < high-priority (not nice to other users)
       N    low-priority (nice to other users)
       L    has pages locked into memory (for real-time and custom IO)
       s    is a session leader
       l    is multi-threaded (using CLONE_THREAD, like NPTL pthreads do
       +    is in the foreground process group.

Compile, install Apache Portable Runtime (APR) on Ubuntu

Compile and install APR
root needed!

Download source packages, apr-1.5.2.tar.gz and apr-util-1.5.4.tar.gz, from Apache site,

Unpack the packages to /root/apache/apr-1.5.2 and /root/apache/apr-util-1.5.4

Installation path: /usr/local/apr

# compile and install apr
# /root/apache/apr-1.5.2
./configure --prefix=/usr/local/apr
make install
# compile and install apr-util
./configure --prefix=/usr/local/apr/lib --with-apr=/usr/local/apr
make install

For Tomcat
Compile and install tomcat native
Go to tomcat/bin, unpack tomcat-native.tar.gz, then go to tomcat-native-1.1.29-src/jni/native, compile and install tomcat-native

# /home/root/apache-tomcat-7.0.53/bin/tomcat-native-1.1.29-src/jni/native
./configure --with-apr=/usr/local/apr --with-java-home=/usr/lib/jvm/java-VERSION-oracle/
make install

In the start script of Tomcat, add

CATALINA_OPTS="$CATALINA_OPTS -Djava.library.path=/usr/local/apr/lib"

In conf/server.xml, update protocol of connectors to use the following protocols,


Restart tomcat, if you see the following in the catalina.out and no exception, the APR is running.

INFO: Loaded APR based Apache Tomcat Native library 1.1.29 using APR version 1.5.2.
Nov 26, 2015 2:58:54 PM org.apache.catalina.core.AprLifecycleListener init

How to get rows, size of data and index from information_schema.TABLES

round(((data_length) / 1024 / 1024), 2) AS "Data (MB)",
round(((index_length) / 1024 / 1024), 2) AS "Index (MB)"
FROM information_schema.TABLES
WHERE table_schema = "database_name"
ORDER BY (data_length + index_length) DESC;


Install and configure Apple Time Machine service on Solaris

I installed Netatalk on Solaris to backup my Macs, it works perfectly!

Here is the steps,

1. install bdb, this is needed by netatalk

cd build_unix
make install

2. install nettalk by running configure, make, make install

3. prepare disk. create the filesystem in ZFS and mount

zfs create -o mountpoint=/sp/timemachine sp/timemachine
zfs create -o mountpoint=/sp/timemachine/yang sp/timemachine/yang

4. configure Netatalk: afp.conf

; Netatalk 3.x configuration file
hostname = Solar
mimic model = RackMac
; [Homes]
; [My AFP Volume]
path = /sp/timemachine/yang
valid users = yang
time machine = yes

5. enable service

svcadm enable multicast
svcadm enable svc:/system/avahi-bridge-dsd:default
svcadm enable netatalk