allowMasterDownConnections = true works but long delay to connect to slave

cat ReplicationDriverDemo.java
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.Properties;
import com.mysql.jdbc.ReplicationDriver;
public class ReplicationDriverDemo {
  public static void main(String[] args) throws Exception {
    ReplicationDriver driver = new ReplicationDriver();
    Properties props = new Properties();
    // We want this for failover on the slaves
    props.put("autoReconnect", "false");
    props.put("connectTimeout", "5");
    props.put("socketTimeout", "5");
    // We want to load balance between the slaves
    props.put("roundRobinLoadBalance", "false");

    props.put("allowMasterDownConnections", "true");
    props.put("user", "root");
    props.put("password", "password");

    // Looks like a normal MySQL JDBC url, with a
    // comma-separated list of hosts, the first
    // being the 'master', the rest being any number
    // of slaves that the driver will load balance against
    //
    Connection conn =
        driver.connect("jdbc:mysql:replication://localhost:5001,localhost:5002/appdb", props);
        //driver.connect("jdbc:mysql:replication://address=(type=master)(protocol=tcp)(host=localhost)(port=5001),address=(type=master)(protocol=tcp)(host=localhost)(port=5002)/appdb", props);
    //
    // Perform read/write work on the master
    // by setting the read-only flag to "false"
    //
    // conn.setReadOnly(false);
    // conn.setAutoCommit(false);
//   conn.createStatement().executeUpdate("UPDATE some_table ....");
//   conn.commit();
    //
    // Now, do a query from a slave, the driver automatically picks one
    // from the list
    //
    conn.setReadOnly(true);
    ResultSet rs =
      conn.createStatement().executeQuery("SELECT 'ReplicationDriverDemo'+current_timestamp()");
        conn.close();

  }
}

--------------------------------------

[mysql@localhost ~]$ time /usr/java/jdk1.8.0_60/bin/java  ReplicationDriverDemo
real    0m31.454s
user    0m1.626s
sys     0m0.113s
--------------------------------------

150926 17:29:06    70 Connect   root@localhost on appdb
                   70 Query     /* mysql-connector-java-5.1.36 ( Revision: 4fc1f969f740409a4e03750316df2c0e429f3dc8 ) */SELECT @@session.auto_increment_increment, @@character_set_client, @@character_set_connection, @@character_set_results, @@character_set_server, @@init_connect, @@interactive_timeout, @@license, @@lower_case_table_names, @@max_allowed_packet, @@net_buffer_length, @@net_write_timeout, @@query_cache_size, @@query_cache_type, @@sql_mode, @@system_time_zone, @@time_zone, @@tx_isolation, @@wait_timeout
                   70 Query     SET NAMES latin1
                   70 Query     SET character_set_results = NULL
                   70 Query     SET sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'
                   70 Query     set session transaction read only
                   70 Query     SELECT 'ReplicationDriverDemo'+current_timestamp()
                   70 Quit

=================================
[mysql@localhost ~]$  /usr/java/jdk1.8.0_60/bin/java ReplicationDriverDemo
2015-09-26 23:36:58.284: Create connection
2015-09-26 23:37:29.571: Set Readonly
2015-09-26 23:37:29.571: Execute Query

2015-09-26 23:37:29.602: Close Connection

=================================

import java.sql.Timestamp;

...

System.out.println(new Timestamp(System.currentTimeMillis())+": Create connection");

    Connection conn =

        driver.connect("jdbc:mysql:replication://localhost:5001,localhost:5002/appdb", props);

...

Example about MySQL character set support

MySQL Character set supports 4 levels: Server, Database, Table and Column.

server1>  create table tc(c1 varchar(20) character set latin1, c2 varchar(20) character set utf8);
Query OK, 0 rows affected (0.03 sec)

server1> insert into tc values('abc','abc');
Query OK, 1 row affected (0.01 sec)

server1> insert into tc values ('中文','中文');
Query OK, 1 row affected, 1 warning (0.01 sec)

server1> select * from tc;
+------+--------+
| c1   | c2     |
+------+--------+
| abc  | abc    |
| ??   | 中文   |
+------+--------+
2 rows in set (0.00 sec)

server1> select hex(c1),hex(c2) from tc;
+---------+--------------+
| hex(c1) | hex(c2)      |
+---------+--------------+
| 616263  | 616263       |
| 3F3F    | E4B8ADE69687 |
+---------+--------------+
2 rows in set (0.00 sec)

server1> show variables like '%character%';
+--------------------------+--------------------------------------------------------------------+
| Variable_name            | Value                                                              |
+--------------------------+--------------------------------------------------------------------+
| character_set_client     | utf8                                                               |
| character_set_connection | utf8                                                               |
| character_set_database   | latin1                                                             |
| character_set_filesystem | binary                                                             |
| character_set_results    | utf8                                                               |
| character_set_server     | latin1                                                             |
| character_set_system     | utf8                                                               |
| character_sets_dir       | /mysql/mysql-advanced-5.6.26-linux-glibc2.5-x86_64/share/charsets/ |
+--------------------------+--------------------------------------------------------------------+
8 rows in set (0.00 sec)

SQL Server Isolation Levels with concurrent updates on nonclustered index

Testing under Read Committed Isolation Level

Session 1(PID 90):
begin transaction
update DimGeography set PostalCode='000001' where GeographyKey=1;
Session 2(PID 93): (Session will wait after executing)
update DimGeography set PostalCode='000002' where GeographyKey=1;
Session 1 (PID 90):
update DimGeography set PostalCode='000003' where GeographyKey=1;
Session 2 (PID 93):  (Session receive error)
Msg 1205, Level 13, State 45, Line 1
Transaction (Process ID 93) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

 

Testing under Repeatable Read Isolation Level

Session 1 (PID 90):
set transaction isolation level repeatable read
go
begin transaction
update DimGeography set PostalCode='000001' where GeographyKey=1;
Session 2(PID 93): (Session will wait after executing)
update DimGeography set PostalCode='000002' where GeographyKey=1;
Session 1 (PID 90):
update DimGeography set PostalCode='000003' where GeographyKey=1;
Session 2 (PID 93): (Session will continue wait for lock)
Session 1 (PID 90):
commit;
Session 2 (PID 93): (returned with message “(1 row(s) affected)”)

Query the data from Session 1, the update performed by session 1 lost