Tuesday, November 28, 2017

SUDOKU Solver based on CTE from MySQL 8.0 

SUDOKU is an interesting problem.   Using SQL to solve this problem is not anything magical.



An interesting twitter post from

https://twitter.com/VadimTk/status/916734787557064704

Thanks to  Vadim Tkachenko


The following SQL with MySQL 8.0.3 RC1, the New Feature "Recursive Common Table Expression/CTE" enables the easy way of this SUDOKU solver.



select @myproblem:='..41..2.3........12.....8..82.6.43.....8.9.....67.2.48..5.....64........3.7..69..';

WITH RECURSIVE
        my19(n) AS (
        SELECT 1 AS n
        UNION ALL
        SELECT 1+n FROM my19 WHERE n<9
)
select substr(@myproblem,(n-1)*9+1,9) as sud from my19;


select @t:=sysdate(6);

WITH RECURSIVE
        input(sud) as (
        select @myproblem
),
        digits(z,lp) as (
        select '1', 1
        union all
        select cast(lp+1 as char), lp+1 from digits where lp<9
),
        x(s,ind) as (
        select sud, instr(sud,'.') from input
        union all
        select concat(substr(s,1, ind-1), z, substr(s, ind+1)),
        instr( concat(substr(s,1,ind-1), z, substr(s, ind+1)), '.')
        from x, digits as z
        where ind> 0
        and not exists (
                select 1 from digits as lp
                where z.z = substr(s, ((ind-1) div 9) *9 + lp, 1)
                or z.z = substr(s, ((ind-1)%9) + (lp-1) *9 + 1, 1)
                or z.z = substr(s, (((ind-1) div 3) %3) * 3
                        + ((ind-1) div 27 ) * 27 + lp
                        + ((lp-1) div 3) * 6, 1)
                )
),
        my19(n) AS (
        SELECT 1 AS n
        UNION ALL
        SELECT 1+n FROM my19 WHERE n<9
)

select substr(s,(n-1)*9 + 1,9) as ans from x,my19 where ind=0 ;

select @t as start_time, timediff(sysdate(6),@t);



The Output from the above SQL is shown as follows :




Friday, March 3, 2017

WatchDog for MySQL Group Replication Servers

MySQL Group Replication has been released since MySQL 5.7.17.
A quick start guide can be on the following URL
http://mysqlhighavailability.com/mysql-group-replication-a-quick-start-guide/

If there is a network partition between a group of Servers, it may introduce arbitration to find out and determine the next action for the Group of the Servers.
For documentation about Network Partitioning with Group Replication, please refer to the following URL::
https://dev.mysql.com/doc/refman/5.7/en/group-replication-network-partitioning.html

This blog is written to introduce my 'demo' version of arbitrator / watchdog.   From here onwards, I refer this arbitrator as gr_watchdog.
Another public arbitrator from Matt Lord can also be found on :
https://github.com/mattlord/myarbitratord
As an example, an application connecting to a group of servers with 5 MySQL instances on 2 machines.   Machine 1 (S1, S2, S3) and Machine 2 (S4, S5)



The following table is an example of the configuration for the Group Replication.
Group Setting
Basic Server Configuration datadir=…
basedir=…
S1: port=3306
S2: port=3316
S3: port=3326
S4: port=3306
S5: port=3316
socket=<socket file not to be same as other on the same machine>
S1:server-id=101
S2:server-id=102
S3:server-id=103
S4:server-id=104
S5:server-id=105
Replication Configuration log-bin
binlog-format=row
binlog-checksum=NONE
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
Group Replication Configuration plugin-load
group_replication_group_name
= 8a94f357-aab4-11df-86ab-c80aa9429562
S1 : group_replication_local_address=primary:4306
S2:  group_replication_local_address=primary:4316
S3: group_replication_local_address=primary:4326
S4: group_replication_local_address=secondary:4306
S5: group_replication_local_address=secondary:4306
group_replication_group_seeds =primary:4306,primary:4316,primary:4326,secondary:4306,secondary:4316
transaction-write-set-extraction=XXHASH64
group_replication_single_primary_mode=false
group_replication_enforce_update_everywhere_checks=true

The 5 servers are all connected as Group Replication thru INTERNAL Network Interface. 
The setting below tells each of the member server to connect to “interface:port”.   “primary|secondary” on the machine can be the interface to INTERNAL Network dedicated for Group Replication.  
S1 : group_replication_local_address=primary:4306
S2:  group_replication_local_address=primary:4316
S3: group_replication_local_address=primary:4326
S4: group_replication_local_address=secondary:4306
S5: group_replication_local_address=secondary:4306


group_replication_group_seeds =primary:4306,primary:4316,primary:4326,secondary:4306,secondary:4316


Application connecting to the MySQL Servers is  going thru another Network Interface.  MySQL Router is used as the routing between MySQL Servers and the Application(s).

When the 5 MySQL Servers are up and running with the Group Replication, the member status can be checked using the following SQL statement on Performance_Schema
mysql> SELECT * FROM performance_schema.replication_group_members;
The following screenshot shows the 5 MySQL Instances running on 2 machines (virtual-23, virtual-24).  Given that the virtual-23 has 3 MySQL Instances and virtual-24 has 2 MySQL Instances.  They are all running with ONLINE state.


If there is any problem to the INTERVAL Network Interface between the 2 machines.   MySQL Instances (S1-S2-S3) is separated from (S4-S5).
It forms 2 group of Servers which they are disjointed.

 On the Majority Group with the Machine 1 (virtual-23), all three Server S1-S2-S3 are connected as ONLINE.   The size of the group becomes 3.
Where as On the Machine 2 (virtual-24), the Group Size is still 5 but only the 2 servers are ONLINE.   The size of 2 is a minority with regards to the Group Size as 5.   

The above diagram shows the Member State of Machine1:virtual-23 on the Left Side.  The Member State of Machine2:virtual-24 on the Right Side.
Machine1:virtual-23 has 3 ONLINE members in the Group. 
Machine2:virtual-24 has 2 ONLINE members out of the 5 member size in the Group.   3 members are marked as UNREACHABLE. 
Application if connected to this minority group (S4-S5) Servers may have uncertainty with data.  Updating data is prohibited by the nature of the Group Replication Architecture.

The gr_watchdog is shared as illustration to detect the member state in the group and take action (SHUTDOWN | READONLY | RESTARTGR) on the server(s).

A script “gr_watchdog.sh” is created to monitor the local MySQL Servers on the machine.

The gr_watchdog.sh monitor each of the MySQL Server Instances on the machine by using the
--login-path  which is created by mysql_config_editor.
For details about the “mysql_config_editor”, refer to the documentation :
https://dev.mysql.com/doc/refman/5.7/en/mysql-config-editor.html

A stored function “gr_is_major_online_member()” as show below :
delimiter //
use sys//
drop function if exists gr_is_major_online_member//
create function gr_is_major_online_member()
        returns boolean NOT DETERMINISTIC
        READS SQL DATA
BEGIN
        DECLARE li_row INT DEFAULT 0;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET li_row=0;
        SELECT IF( MEMBER_STATE='ONLINE' AND   ((SELECT COUNT(*) FROM performance_schema.replication_group_members
                WHERE MEMBER_STATE != 'ONLINE') >=  ((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0), 1, 0 )
                INTO li_row   FROM performance_schema.replication_group_members JOIN performance_schema.replication_group_member_stats USING(member_id);
        IF li_row = 1 THEN
                RETURN(true);
        END IF;
        RETURN(false);
END;
//

If MySQL servers are in a Group Replication and they are all connected, executing the stored function creation on any of the server instance will be able to install the Stored Function to all server instances.



The gr_comm.sh contains the configuration settings.
export MYSQL_HOME=/usr/local/mysql
Provide the MYSQL HOME installation
export PATH=$MYSQL_HOME/bin:$PATH
Add to the PATH
export ACTION=READONLY
# ACTION if there is any failure
 SHUTDOWN – shutdown using mysqladmin
READONLY – issue set READ_ONLY and SUPER_READ_ONLY on the server
 RESTARTGR – issue “stop group_replication;” and start group_replication;” to restart the Group Replication
export RETRY_COUNT=3
In case of Server Failed in the Group (Not online or not in majority group), the retry count determine the number of retry before ACTION is taken
export QUIET_MODE=0
INFO logging if QUIET_MODE=0
export GRSERVERS=gr3306:gr3316:gr3326
The member servers to be checked.  It is based on the
--login-path from the mysql_config_editor. 
Format :login-path[:login-path]…
export GRINTERVAL=30
Polling interval
export OUTDIR=/home/mysql/demo/GroupReplication/grwatchdog/log
Output directory (privilege to “WRITE and CREATE” file(s)

Starting gr_watchdog.sh
# gr_watchdog.sh start


Stopping gr_watchdog.sh
# gr_watchdog.sh stop
OR
kill the process

To illustrate the gr_watchdog for the ACTION=READONLY; while ALL member servers are running, issue “stop group_replication;” on one of the server.   After retrying up to RETRY_COUNT=3, the server is set to READ ONLY – including READ_ONLY and SUPER_READ_ONLY.  

To enable the GROUP REPLICATION, be sure to disable the READ_ONLY and SUPER_READ_ONLY if the “group_replication_single_primary_mode” is not set to TRUE.




You can download the grwatchdog.tar from the following LINK
https://github.com/ivanxma/grwatchdog/blob/master/grwatchdogV1.tar 
 
Any comment, appreciate your sharing!!!    

Again, the is just a demonstration how we can create external arbitrator / watchdog for MySQL Group Replication Servers.




Friday, June 10, 2016

Python App connecting to MySQL with mysql.connector + Failover



The following is the very basic Python App connecting to MySQL Database. (01-connect.py)
It connects to the MySQL Database (127.0.0.1:3306 with root and empty password). 
import mysql.connector
import sys

if len(sys.argv) > 1 :
        _pass = sys.argv[1]
else :
        _pass = ''
cnx = mysql.connector.connect(user='root', password=_pass, host='127.0.0.1', port=3306, database='mysql')

cnx.close()

The alternative using Python Dictionary parameter -

This can be useful way to connect to MySQL Database with named parameters. (02-connect.py)
import mysql.connector
import sys

_pass = ''
if len(sys.argv) > 1 :
        _pass = sys.argv[1]

config = {
        'user': 'root',
        'password': _pass,
        'host': '127.0.0.1',
        'database': 'mysql',
        'raise_on_warnings': True,
}

cnx = mysql.connector.connect(**config)
cnx.close()

Error Handling with MySQL  (03-connectTry.py)
The following example shows the connection to the default db ("test") with 127.0.0.1:3306 using root empty password user.    The error handling processing using try/except/else together with mysql.connector.errorcode.  By providing first argument with invalid password, the python app will get  exception - "errorcode.ER_ACCESS_DENIED_ERROR".  Whereas by giving 2nd argument with a non-exists database, the python app will generate exception "errorcode.ER_BAD_DB_ERROR".

import mysql.connector
from mysql.connector import errorcode
import sys

_pass=''
_db='test'
if len(sys.argv) > 1 :
        _pass = sys.argv[1]
if len(sys.argv) > 2 :
        _db = sys.argv[2]

try:
  cnx = mysql.connector.connect(user='root', host='127.0.0.1', port=3306,
                                password=_pass,
                                database=_db)

  print("connect success")
except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
       print("Something is wrong with your user name or password")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
       print("Database does not exist")
  else:
       print(err)
else:
  print ("Finally - close connection")
  cnx.close()
Lastly,  mysql.connector provides connection property "failover".
Please find more details on 
https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html

The following sample (04-failover.py) provides a sample to connect to databases (3306 and 3316).  If 3306 is stopped, the connection goes to the 3316. 

import mysql.connector
from mysql.connector import errorcode

DB_NAME = 'mysql'

operation = 'SELECT @@hostname, @@port'

config = {
        'raise_on_warnings': True,
        'failover' : [{
                'user': 'root',
                'password': '',
                'host': '127.0.0.1',
                'port': 3306,
                'database': 'mysql',
                }, {
                'user': 'root',
                'password': '',
                'host': '127.0.0.1',
                'port': 3316,
                'database': 'mysql',
                }]
}
cnx = mysql.connector.connect(**config)

cursor = cnx.cursor()

try:
  for result in cursor.execute(operation, multi=True):
     if result.with_rows:
        print("rows producted by statement '{}':".format(result.statement))
        row = cursor.fetchone()
        while row:
           print(row)
           row = cursor.fetchone()
     else:
        print("Number of rows affeted by statement '{}':{}".format(result.statement, result.rowcount))
except mysql.connector.Error as err:
        print(err.msg)

Enjoy!