Saturday, September 26, 2015

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);
...

Thursday, September 10, 2015

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)

Wednesday, September 2, 2015

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.


image

 

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
image