Query

JunaidKhan

Hi All, i have a table in my vertica db having some failed devices i need a help how can i select failed device.

column having failed entries is like below.

Resume of ZTE_E300_SYS .E300_02 Connectivity FAILED!!!..

ZTE_E300_SYS .E300_02 this is device name

Thanks in advance.

Br.

Jim_Knicely

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)

JunaidKhan

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.

Jim_Knicely

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)

JunaidKhan

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!!!..

Jim_Knicely

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)

JunaidKhan

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