Query

Comments

• Jim_Knicely - Select Field - Administrator

  July 2018 edited July 2018

  Hi,

  How many nodes are in your cluster? Is your K Safety at least 1? If you have a failed device, you can replace it, and Vertica will recover the data from a buddy projection.

  I think you'll need to run some OS command to check for bad blocks on a device.

  Example:

  [root@vertica01 ~]# df -h
  Filesystem                         Size  Used Avail Use% Mounted on
  /dev/mapper/centos_vertica01-root   23G  8.3G   15G  37% /
  devtmpfs                           1.4G     0  1.4G   0% /dev
  tmpfs                              1.4G     0  1.4G   0% /dev/shm
  tmpfs                              1.4G  8.4M  1.4G   1% /run
  tmpfs                              1.4G     0  1.4G   0% /sys/fs/cgroup
  /dev/sda1                          497M  170M  327M  35% /boot
  DEMO                               465G  434G   32G  94% /media/sf_DEMO
  tmpfs                              285M     0  285M   0% /run/user/1000
  [root@vertica01 ~]# badblocks -v /dev/sda1
  Checking blocks 0 to 511999
  Checking for bad blocks (read-only test): done
  Pass completed, 0 bad blocks found. (0/0/0 errors)
  
  [root@vertica01 ~]# badblocks -v /dev/mapper/centos_vertica01-root
  Checking blocks 0 to 23556095
  Checking for bad blocks (read-only test): done
  Pass completed, 0 bad blocks found. (0/0/0 errors)
  

  0
• JunaidKhan ✭

  July 2018

  Hi, Thanks for your reply.

  there are 3 nodes in a vertica cluster.

  let me explain my query more for better understanding.

  For example, i have 5 failed devices in my db table and 7 opened devices just need to find out the 5 failed device using query.

  0
• Jim_Knicely - Select Field - Administrator

  July 2018

  Oh. I thought you meant you had a failed disk

  A simple REPLACE might help?

  dbadmin=> SELECT * FROM devices;
                           message
  ----------------------------------------------------------
   Resume of ZTE_E300_SYS .E300_02 Connectivity FAILED!!!..
  (1 row)
  
  dbadmin=> SELECT message,
  dbadmin->        REPLACE(REPLACE(message, 'Resume of ', ''), ' Connectivity FAILED!!!..', '') device
  dbadmin->   FROM devices;
                           message                          |        device
  ----------------------------------------------------------+-----------------------
   Resume of ZTE_E300_SYS .E300_02 Connectivity FAILED!!!.. | ZTE_E300_SYS .E300_02
  (1 row)
  

  0
• JunaidKhan ✭

  July 2018

  Hi, Thanks for your help this is now interesting and i need your help more here.

  how can i altered my query more to just select ".E300_02 Failed" from the string "Resume of ZTE_E300_SYS .E300_02 Connectivity Failed!!!..

  0
• Jim_Knicely - Select Field - Administrator

  July 2018 edited July 2018

  dbadmin=> SELECT message, rtrim(split_part(split_part(message, '.', 2), ' ', 1) || ' ' || split_part(split_part(message, '.', 2), ' ', 3), '!') error_msg from devices;
                           message                          |   error_msg
  ----------------------------------------------------------+----------------
   Resume of ZTE_E300_SYS .E300_02 Connectivity FAILED!!!.. | E300_02 FAILED
  (1 row)
  

  0
• JunaidKhan ✭

  July 2018

  Hi Jim, Thanks (Y) it is really helpfull (Y)

  0