Query

JunaidKhanJunaidKhan Registered User

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.

Comments

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert
    edited July 3

    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:

    [[email protected] ~]# 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
    [[email protected] ~]# 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)
    
    [[email protected] ~]# 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)
    
  • JunaidKhanJunaidKhan Registered User

    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_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

    Oh. I thought you meant you had a failed disk :pensive:

    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)
    
  • JunaidKhanJunaidKhan Registered User

    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_KnicelyJim_Knicely Employee, Registered User, VerticaExpert
    edited July 13
    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)
    
  • JunaidKhanJunaidKhan Registered User

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file