Mysql Galera WSREP_SST: [ERROR] xtrabackup_checkpoints missing

Adding new node to galera fails with following error :

WSREP_SST: [INFO] Waiting for SST streaming to complete! (20160607 12:21:20.988)
160607 12:21:23 [Note] WSREP: (3d7453d3, 'tcp://0.0.0.0:4567') turning message relay requesting off
WSREP_SST: [ERROR] xtrabackup_checkpoints missing, failed innobackupex/SST on donor (20160607 12:21:30.966)
WSREP_SST: [ERROR] Cleanup after exit with status:2 (20160607 12:21:30.968)
160607 12:21:30 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.103.34.27' --auth 'sstuser:dev2' --datadir '/var/untd/mysql/data/' --defaults-file '/etc/my.cnf' --parent '4858': 2 (No such file or directory)
160607 12:21:30 [ERROR] WSREP: Failed to read uuid:seqno from joiner script.
160607 12:21:30 [ERROR] WSREP: SST failed: 2 (No such file or directory)
160607 12:21:30 [ERROR] Aborting

160607 12:21:30 [Warning] WSREP: 1 (hadoop-dn1qa.cs1cmates.com): State transfer to 0 (node2) failed: -22 (Invalid argument)
160607 12:21:30 [ERROR] WSREP: gcs/src/gcs_group.cpp:long int gcs_group_handle_join_msg(gcs_group_t*, const gcs_recv_msg_t*)():717: Will never receive state. Need to abort.
160607 12:21:30 [Note] WSREP: gcomm: terminating thread
160607 12:21:30 [Note] WSREP: gcomm: joining thread
160607 12:21:30 [Note] WSREP: gcomm: joining thread
160607 12:21:30 [Note] WSREP: gcomm: closing backend
160607 12:21:31 [Note] WSREP: gcomm: closed
160607 12:21:31 [Note] WSREP: /usr/sbin/mysqld: Terminated.
160607 12:21:32 mysqld_safe mysqld from pid file /var/untd/mysql/log/mysqld.pid ended

Fix :

Read More

Basic User Administration commands in NETEZZA

  • CONNECTING AS A USER
nzsql -h `hostname` -u testuser -pw testuser
  • GRANT/ REVOKE SELECT ON DATABASE ‘COMMON’
system(admin)=> grant list on common to testuser;
GRANT
common(admin)=> grant select on common to testuser;
common(admin)=> revoke all on common from testuser;
  • DISPLAY PERMISSIONS
common(admin)=> \dpu testuser
  • GRANT LIST/SELECT ON ALL TABLES IN A DATABASE ‘COMMON’
system(admin)=> \c common
common(admin)=> grant list on table to testuser;
common(admin)=> GRANT SELECT ON TABLE TO TESTUSER;

MORE -
grant all on table to <user>;
grant all on view to <user>;
grant all on procedure to <user>;
grant all on function to <user>;
grant all on sequence to <user>;
grant all on synonym to <user>;

Read More

Creating or Dropping a database in Oracle 11g

CREATING A NEW DATABASE

  • Creating a initialization parameter file at location $ORACLE_HOME/dbs which is the default location where oracle looks for a init file .

Lets create a database by name ‘test’ :

[[email protected]]$ cat $ORACLE_HOME/dbs/inittest.ora
db_name=test
db_block_size=8192
control_files=(/home/oracle/app/oracle/oradata/test/control01.ctl,/home/oracle/app/oracle/oradata/test/control02.ctl)
  • Startup instance in nomount mode and create a database
[[email protected] ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 18 23:55:15 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 150667264 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 6725632 bytes

SQL> select status from v$instance;

STATUS
------------
NOMOUNT

SQL> CREATE DATABASE test
LOGFILE
GROUP 1 ('/home/oracle/app/oracle/oradata/test/redo01a.log', '/home/oracle/app/oracle/oradata/test/redo01b.log') SIZE 10M,
GROUP 2 ('/home/oracle/app/oracle/oradata/test/redo02a.log', '/home/oracle/app/oracle/oradata/test/redo02b.log') SIZE 10M
DATAFILE
 '/home/oracle/app/oracle/oradata/test/system01.dbf' SIZE 200M
SYSAUX DATAFILE '/home/oracle/app/oracle/oradata/test/sysaux01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M
UNDO TABLESPACE "UNDOTBS1" DATAFILE '/home/oracle/app/oracle/oradata/test/undotbs01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/home/oracle/app/oracle/oradata/test/temp01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 500M
DEFAULT TABLESPACE USERS DATAFILE '/home/oracle/app/oracle/oradata/test/users01.dbf' SIZE 50M AUTOEXTEND ON MAXSIZE 200M
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16;

Database created.


SQL> select status from v$instance;

STATUS
------------
OPEN

Read More

Mysql Secure Installation for non default socket.

SECURING MYSQL SERVER –

mysql_secure_installation is a script useful to

  • set root password
  • disallowing root login remotely
  • removing anonymous user accounts after first installation
  • removing test database which can be accessed by any users

Above script would work for default socket location i.e /var/lib/mysql/mysql.sock

If the server is started with a non-default socket location, mysql_secure_installation does not consider any socket options you specify and the script would fail (For example -> mysql_secure_installation –socket=/mysql/socket/mysql.sock)

We should also consider this situation in case if we need to install more than one instance of mysql on a single server. Read More

Hive connection using java

Here is a sample java code to connect hive database through JDBC.

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;

public class HiveJdbcTest{
  private static String driverName = "org.apache.hive.jdbc.HiveDriver";

  public static void main(String[] args) throws SQLException {
    try {
      Class.forName(driverName);
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
      System.exit(1);
    }

    Connection con = DriverManager.getConnection("jdbc:hive2://<hive_server>:10000/<db>", "", "");
    Statement stmt = con.createStatement();
    String sql = "show tables";
    System.out.println("Current query: " + sql);
    ResultSet res = stmt.executeQuery(sql);
    if (res.next()) {
      System.out.println(res.getString(1));
        }

  }
}

If you are facing any trouble compiling or running this program include hive and hadoop libraries to classpath using : Read More

Partition maintainance script for Mysql

A simple way for adding future partitions or purging old partitions is through mysql procedures. Later we can use any scheduler or a cronjob to call this procedure daily.

Here I will provide a sample procedure for maintainance of a daily partitioned table based on a datetime column. Please do look at my previous post on mysql range partitioning in the following post. I have taken the example from this post and modified it based on our requirement.

First we will create a sample table along with a partition for our testing purposes.

mysql> create table test_partitions(id int,name varchar(50),tx_date datetime);
mysql> alter table test_partitions PARTITION BY RANGE (to_seconds(tx_date)) (
partition test_partitions_16092015 values less than ( to_seconds('2015-09-17 00:00:00') ));

Now lets create a table for holding list of tables and number of partitions to preserve/drop.

mysql> create table test.part_tables(dbname varchar(50),tabname varchar(50),numpart int,nextpart int);

Lets go ahead and insert a sample row there :

mysql> insert into test.part_tables values('test','test_partitions',30,7);

So, we will be preserving only last 30 days partitions and make sure we have next 7 days partitions for ‘test_partitions’ table.

Mysql Procedure 1 (part_maint) : This procedure takes in table list from our meta-table i.e part_tables and runs partition maintainance for each table :

Read More

Mysql range partitioning on date column

Here is a simple example for partitioning a mysql table based on datetime column :

  • Creating a test table
mysql> create table test_partitions(id int,name varchar(50),tx_date datetime);
Query OK, 0 rows affected (11.83 sec)
  • Creating a initial partition :
alter table test_partitions PARTITION BY RANGE (to_seconds(tx_date)) (
partition test_partitions_16052016 values less than ( to_seconds('2016-05-17 00:00:00') ));
  • Adding new partition:
mysql> alter table test_partitions add partition ( partition test_partitions_17052016 values less than ( to_seconds('2016-05-18 00:00:00') ));
Query OK, 0 rows affected (10.87 sec)
Records: 0 Duplicates: 0 Warnings: 0
  • Verifying data in specific partition

Read More

Character set conversion in Oracle

Here is the procedure to convert Oracle database (11G) from  US7ASCII to  UTF8/ALT32UTF8, we must be using similar steps to convert to a different character set.

In this example we will be using csscan utility to find lossy and convertible data in database and csalter utility to alter characterset.

  • Install csscan utility :
SQL> @$ORACLE_HOME/rdbms/admin/csminst.sql
  • Verify current characterset :
SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET US7ASCII
  • Check for invalid objects :
SQL> SELECT owner,
object_name,
object_type,
status
FROM dba_objects
WHERE status ='INVALID'
/

Recompile all invalid objects in the database : Read More