Monday 15 October 2018

Fix MySQL slave relay log crash

One of my MySQL server replication stopped cos of this server auto reboot,here the details from SHOW SLAVE STATUS\G;

              Slave_IO_State: Queueing master event to the relay log
                  Master_Host: xx.xx.xx.xxx
                  Master_User: repl
                  Master_Port:  3306
                Connect_Retry: 10
            Master_Log_File: logbin.014750
   Read_Master_Log_Pos: 582392000
             Relay_Log_File: relay-bin.000323
               Relay_Log_Pos: 582391127
  Relay_Master_Log_File: logbin.014750
           Slave_IO_Running: Yes
        Slave_SQL_Running: No
            Replicate_Do_DB:
      Replicate_Ignore_DB:
         Replicate_Do_Table:
   Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
                      Last_Errno: 1594
                        Last_Error: Relay log read failure: Could not parse relay log event entry.
                               The possible reasons are: the master's binary log is corrupted
                               (you can check this by running 'mysqlbinlog' on the binary log),
                               the slave's relay log is corrupted (you can check this by running
                               'mysqlbinlog' on the relay log), a network problem, or a bug in the
                               master's or slave's MySQL code. If you want to check the master's
                               binary log or slave's relay log, you will be able to know their names
                               by issuing 'SHOW SLAVE STATUS' on this slave.
                 Skip_Counter: 0
  Exec_Master_Log_Pos: 582390967
          Relay_Log_Space: 582392371
              Until_Condition: None
                Until_Log_File:
                 Until_Log_Pos: 0
      Master_SSL_Allowed: No
      Master_SSL_CA_File:
     Master_SSL_CA_Path:
             Master_SSL_Cert:
         Master_SSL_Cipher:
              Master_SSL_Key:
   Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1594
                Last_IO_Error: [the same error description as in Last_Error]
                ......

To fix this error, we need setting the new position for slave start to sync, the very important we need to find the Relay_Master_Log_File and Exec_Master_Log_Pos  values:

Relay_Master_Log_File: logbin.014750
Exec_Master_Log_Pos: 582390967

With the 2 values we can reset the position:
mysql> stop slave;
mysql> reset slave;
mysql> change master to master_log_file='logbin.014750', master_log_pos=582390967;
mysql> start slave;

Show slave status\G again, it works.

Sunday 30 July 2017

Could not locate statistics in the system catalogs

select top 10 * from [linkedserver].[dbname].dbo.tblauditlog

throw me error:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 2767, Level 16, State 1, Procedure sp_table_statistics2_rowset, Line 105
Could not locate statistics '_WA_Sys_0000001D_6502C82F' in the system catalogs.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

solution:
drop statistics tblauditlog._WA_Sys_0000001D_6502C82F

drop the statistics on primary itworks for me.

its the sql server AG bugs?

Friday 28 April 2017

Cassandra change data_file_directories

[root@garey1 default.conf]# service cassandra stop
Shutdown Cassandra: OK
[root@garey1 default.conf]# cd /usr/local/
[root@garey1 local]# mkdir cassandra
[root@garey1 local]# mkdir cassandra/data
[root@garey1 local]# cp -r /var/lib/cassandra/data/* /usr/local/cassandra/data/
[root@garey1 local]# ls -lah /usr/local/cassandra/data/
total 36K
drwxr-xr-x.  9 root root 4.0K Apr 29 07:50 .
drwxr-xr-x.  3 root root 4.0K Apr 29 07:50 ..
drwxr-xr-x.  6 root root 4.0K Apr 29 07:50 demo
drwxr-xr-x.  3 root root 4.0K Apr 29 07:50 experiments
drwxr-xr-x. 24 root root 4.0K Apr 29 07:50 system
drwxr-xr-x.  6 root root 4.0K Apr 29 07:50 system_auth
drwxr-xr-x.  5 root root 4.0K Apr 29 07:50 system_distributed
drwxr-xr-x. 12 root root 4.0K Apr 29 07:50 system_schema
drwxr-xr-x.  4 root root 4.0K Apr 29 07:50 system_traces
[root@garey1 local]# chown -R cassandra:cassandra /usr/local/cassandra/data
[root@garey1 local]# ls -lah /usr/local/cassandra/data/
total 36K
drwxr-xr-x.  9 cassandra cassandra 4.0K Apr 29 07:50 .
drwxr-xr-x.  3 root      root      4.0K Apr 29 07:50 ..
drwxr-xr-x.  6 cassandra cassandra 4.0K Apr 29 07:50 demo
drwxr-xr-x.  3 cassandra cassandra 4.0K Apr 29 07:50 experiments
drwxr-xr-x. 24 cassandra cassandra 4.0K Apr 29 07:50 system
drwxr-xr-x.  6 cassandra cassandra 4.0K Apr 29 07:50 system_auth
drwxr-xr-x.  5 cassandra cassandra 4.0K Apr 29 07:50 system_distributed
drwxr-xr-x. 12 cassandra cassandra 4.0K Apr 29 07:50 system_schema
drwxr-xr-x.  4 cassandra cassandra 4.0K Apr 29 07:50 system_traces
[root@garey1 local]# vim /etc/cassandra/default.conf/cassandra.yaml
find the key data_file_directories
data_file_directories:
    - /var/lib/cassandra/data

and set to
data_file_directories:
    - /usr/local/cassandra/data
[root@garey1 local]# service cassandra start

Cassandra create new super user and change default user password

[root@garey-mysql1]# cqlsh 192.168.142.200 -u cassandra -p cassandra --cqlversion='3.4.2'
Connected to Test Cluster at 192.168.142.200:9042.
[cqlsh 5.0.1 | Cassandra 3.9.0 | CQL spec 3.4.2 | Native protocol v4]
Use HELP for help.
cassandra@cqlsh> create user dba01 with password 'P@ssw0rd123' superuser;
cassandra@cqlsh> quit
[root@garey-mysql1 default.conf]# cqlsh 192.168.142.200 -u dba01 -p P@ssw0rd123 --cqlversion='3.4.2'
Connected to Test Cluster at 192.168.142.200:9042.
[cqlsh 5.0.1 | Cassandra 3.9.0 | CQL spec 3.4.2 | Native protocol v4]
Use HELP for help.
dba01@cqlsh> alter user cassandra with password 'P@ssw0rd123' superuser;
dba01@cqlsh> list users;

 name      | super
-----------+-------
 cassandra |  True
     dba01 |  True

(2 rows)
dba01@cqlsh>

Cassandra 3.9 installation

Cassandra 3.9

[root@garey-mysql2 ~]# yum install java-1.8.0-openjdk
Loaded plugins: fastestmirror
* updates: mirror.ehost.vn
Resolving Dependencies
--> Running transaction check
---> Package java-1.8.0-openjdk.x86_64 1:1.8.0.121-0.b13.el6_8 will be installed
。。。。
Dependency Installed:
  giflib.x86_64 0:4.1.6-3.1.el6                 java-1.8.0-openjdk-headless.x86_64 1:1.8.0.121-0.b13.el6_8      jpackage-utils.noarch 0:1.7.5-3.16.el6      libXfont.x86_64 0:1.5.1-2.el6
  libfontenc.x86_64 0:1.1.2-3.el6               ttmkfdir.x86_64 0:3.0.9-32.1.el6                                tzdata-java.noarch 0:2017a-1.el6            xorg-x11-font-utils.x86_64 1:7.2-11.el6
  xorg-x11-fonts-Type1.noarch 0:7.2-11.el6

Complete!

[root@garey-mysql2 ~]# java -version
openjdk version "1.8.0_131"
OpenJDK Runtime Environment (build 1.8.0_131-b11)
OpenJDK 64-Bit Server VM (build 25.131-b11, mixed mode)

[root@garey-mysql2 ~]# vi /etc/yum.repos.d/datastax.repo
[datastax-ddc]
name = DataStax Repo for Apache Cassandra
enabled = 1
gpgcheck = 0

[root@garey-mysql2 ~]# yum install datastax-ddc
Loaded plugins: fastestmirror
---> Package datastax-ddc-tools.noarch 0:3.9.0-1 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
===========================================================================
 Package                                                 Arch                                        Version                                      Repository                                         Size
Installing:
 datastax-ddc                                            noarch                                      3.9.0-1                                      datastax-ddc                                       28 M
Installing for dependencies:
 datastax-ddc-tools                                      noarch                                      3.9.0-1                                      datastax-ddc                                      5.3 k
Transaction Summary
===========================================================================
 (1/2): datastax-ddc-3.9.0-1.noarch.rpm                                                                                                                                             |  28 MB     00:43
(2/2): datastax-ddc-tools-3.9.0-1.noarch.rpm                                                                                                                                       | 5.3 kB     00:00
---------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                                                     658 kB/s |  28 MB     00:43
  Installing : datastax-ddc-tools-3.9.0-1.noarch                                            1/2
  Installing : datastax-ddc-3.9.0-1.noarch                                               2/2
  Verifying  : datastax-ddc-3.9.0-1.noarch                                1/2
  Verifying  : datastax-ddc-tools-3.9.0-1.noarch              2/2
…….
Complete!
[root@garey-mysql2 ~]# service cassandra status
cassandra is stopped
[root@garey-mysql2 ~]# service cassandra start
Starting Cassandra: OK
[root@garey-mysql2 ~]# nodetool status
nodetool: Failed to connect to '127.0.0.1:7199' - ConnectException: 'Connection refused (Connection refused)'.
[root@garey-mysql2 ~]# vi /etc/hosts



[root@garey-mysql2 ~]# nodetool status
nodetool: Failed to connect to '127.0.0.1:7199' - ConnectException: 'Connection refused (Connection refused)'.
[root@garey-mysql2 ~]# find / -name cassandra.yaml
/etc/cassandra/default.conf/cassandra.yaml
[root@garey-mysql2 ~]# vi /etc/cassandra/default.conf/cassandra.yaml
Find rpc_address and set to
rpc_address: 127.0.0.1
[root@garey-mysql2 ~]# service cassandra restart
Shutdown Cassandra: bash: line 0: kill: (12030) - No such process
ERROR: could not stop cassandra:  cassandra dead but pid file exists
Starting Cassandra: OK
[root@garey-mysql2 ~]# service cassandra status
cassandra (pid  12400) is running...
[root@garey-mysql2 ~]# nodetool status
Datacenter: datacenter1
=======================
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
--  Address    Load       Tokens       Owns (effective)  Host ID                               Rack
UN  127.0.0.1  103.5 KiB  256          100.0%            56a87427-0ca2-4afe-97df-56bd9498b2bd  rack1


[root@garey default.conf]# python -V
Python 2.6.6
Cassandra 3.9 need python 2.7 for cqlsh
yum install scl-utils
yum install centos-release-scl-rh
yum install python27
[root@garey default.conf]# python -V
Python 2.6.6
[root@garey default.conf]# scl enable python27 bash
[root@garey default.conf]# python -V
Python 2.7.8
[root@garey ~]# cqlsh -h
Traceback (most recent call last):
  File "/usr/bin/cqlsh.py", line 167, in <module>
    from cqlshlib import cql3handling, cqlhandling, pylexotron, sslhandling
ImportError: No module named cqlshlib
[root@garey ~]# pip install cqlsh
You are using pip version 7.1.0, however version 9.0.1 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.
Collecting cqlsh
  Downloading cqlsh-5.0.4.tar.gz (99kB)
    100% |████████████████████████████████| 102kB 926kB/s
Collecting cql (from cqlsh)
  Downloading cql-1.4.0.tar.gz (76kB)
    100% |████████████████████████████████| 77kB 1.5MB/s
Collecting cassandra-driver (from cqlsh)
  Downloading cassandra-driver-3.9.0.tar.gz (216kB)
    100% |████████████████████████████████| 217kB 474kB/s
Collecting thrift (from cql->cqlsh)
  Downloading thrift-0.10.0.zip (87kB)
    100% |████████████████████████████████| 90kB 1.1MB/s
Collecting six>=1.6 (from cassandra-driver->cqlsh)
  Downloading six-1.10.0-py2.py3-none-any.whl

Successfully built cqlsh cql cassandra-driver thrift
……
Successfully installed cassandra-driver-3.9.0 cql-1.4.0 cqlsh-5.0.4 futures-3.1.1 six-1.10.0 thrift-0.10.0
[root@garey ~]# cqlsh
Connection error: ('Unable to connect to any servers', {'127.0.0.1': ProtocolError("cql_version '3.3.1' is not supported by remote (w/ native protocol). Supported versions: [u'3.4.2']",)})
[root@garey ~]# cqlsh --cqlversion='3.4.2'
Connected to Test Cluster at 127.0.0.1:9042.
[cqlsh 5.0.1 | Cassandra 3.9.0 | CQL spec 3.4.2 | Native protocol v4]
Use HELP for help.












Monday 7 November 2016

mytop Can't load '/usr/lib64/perl5/auto/DBD/mysql/mysql.so' for module DBD::mysql: libmysqlclient

[root@garey-mysql2 ~]# mytop
install_driver(mysql) failed: Can't load '/usr/lib64/perl5/auto/DBD/mysql/mysql.so' for module DBD::mysql: libmysqlclient.so.16: cannot open shared object file: No such file or directory at /usr/lib64/perl5/DynaLoader.pm line 200.
 at (eval 12) line 3
Compilation failed in require at (eval 12) line 3.
Perhaps a required shared library or dll isn't installed where expected
 at /usr/bin/mytop line 235
 [root@garey-mysql2 ~]# yum install perl-DBD-MySQL
Loaded plugins: fastestmirror
Setting up Install Process
Loading mirror speeds from cached hostfile
 * base: centos-hn.viettelidc.com.vn
 * epel: mirror.premi.st
 * extras: centos-hn.viettelidc.com.vn
 * updates: centos-hn.viettelidc.com.vn
Package perl-DBD-MySQL-4.013-3.el6.x86_64 already installed and latest version
Nothing to do
[root@garey-mysql2 ~]# rpm -qa | grep -i dbd
perl-DBD-MySQL-4.013-3.el6.x86_64
[root@garey-mysql2 ~]# rpm -e --nodeps perl-DBD-MySQL-4.013-3.el6.x86_64
[root@garey-mysql2 ~]# rpm -qa | grep dbd
[root@garey-mysql2 ~]# yum install perl-DBD-mysql

Loaded plugins: fastestmirror
Setting up Install Process
Loading mirror speeds from cached hostfile
 * base: centos-hn.viettelidc.com.vn
 * epel: mirror.premi.st
 * extras: centos-hn.viettelidc.com.vn
 * updates: centos-hn.viettelidc.com.vn
Resolving Dependencies
--> Running transaction check
---> Package perl-DBD-MySQL.x86_64 0:4.013-3.el6 will be installed
--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16)(64bit) for package: perl-DBD-MySQL-4.013-3.e                                                                                           l6.x86_64
--> Processing Dependency: libmysqlclient.so.16()(64bit) for package: perl-DBD-MySQL-4.013-3.el6.x86_64
--> Running transaction check
---> Package mysql-libs.x86_64 0:5.1.73-7.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

===============================================================================================================
 Package                        Arch                   Version                      Repository            Size
===============================================================================================================
Installing:
 perl-DBD-MySQL                 x86_64                 4.013-3.el6                  base                 134 k
Installing for dependencies:
 mysql-libs                     x86_64                 5.1.73-7.el6                 base                 1.2 M

Transaction Summary
===============================================================================================================
Install       2 Package(s)

Total download size: 1.4 M
Installed size: 4.4 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): mysql-libs-5.1.73-7.el6.x86_64.rpm                                                                                                                                          | 1.2 MB     00:00
(2/2): perl-DBD-MySQL-4.013-3.el6.x86_64.rpm                                                                                                                                       | 134 kB     00:00
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                                                      39 kB/s | 1.4 MB     00:35
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
** Found 4 pre-existing rpmdb problem(s), 'yum check' output follows:
mytop-1.7-3.b737f60.el6.noarch has missing requires of perl(DBD::mysql) >= ('0', '1', None)
2:postfix-2.6.6-6.el6_7.1.x86_64 has missing requires of libmysqlclient.so.16()(64bit)
2:postfix-2.6.6-6.el6_7.1.x86_64 has missing requires of libmysqlclient.so.16(libmysqlclient_16)(64bit)
2:postfix-2.6.6-6.el6_7.1.x86_64 has missing requires of mysql-libs
  Installing : mysql-libs-5.1.73-7.el6.x86_64                                                                                                                                                         1/2
warning: /etc/my.cnf created as /etc/my.cnf.rpmnew
  Installing : perl-DBD-MySQL-4.013-3.el6.x86_64                                                                                                                                                      2/2
  Verifying  : perl-DBD-MySQL-4.013-3.el6.x86_64                                                                                                                                                      1/2
  Verifying  : mysql-libs-5.1.73-7.el6.x86_64                                                                                                                                                         2/2

Installed:
  perl-DBD-MySQL.x86_64 0:4.013-3.el6

Dependency Installed:
  mysql-libs.x86_64 0:5.1.73-7.el6

Complete!

[root@garey-mysql2 ~]# rpm -qa | grep -i dbd
perl-DBD-MySQL-4.013-3.el6.x86_64

[root@garey-mysql2 ~]# mytop
MySQL on localhost (5.6.24-log)                                                                                                                                                   up 4+03:52:10 [15:38:39]
 Queries: 7.0    qps:    0 Slow:     0.0         Se/In/Up/De(%):    14/00/00/00

 Key Efficiency: 100.0%  Bps in/out:   0.0/  0.0

      Id      User         Host/IP         DB      Time    Cmd Query or State
       --      ----         -------         --      ----    --- ----------
        4      root       localhost       test         0  Query show full processlist
        1 system us                               359512 Connec Slave has read all relay log; waiting for the slave I/O thread to update it
        2 system us                               359513 Connec Connecting to master