[Kea-users] Problem on query ISC KEA PgSQL by client_id

classic Classic list List threaded Threaded
2 messages Options
RBK
Reply | Threaded
Open this post in threaded view
|

[Kea-users] Problem on query ISC KEA PgSQL by client_id

RBK
Hi

I have an installation on using KEA 1.6 with PgSQL and I have issue on
retrieving record by client_id. anyone can help?

below is the entry that I had.

  address   |     hwaddr     |                   client_id
       | valid_lifetime |         expire         | subnet_id |
fqdn_fwd | fqdn_rev | hostname | state | user_context
------------+----------------+------------------------------------------------+----------------+------------------------+-----------+----------+----------+----------+-------+--------------
3227711669 | \x115131110400 |
\x003836303031383735303030303030315f61706e31 |         864000 |
2020-01-12 07:51:02+00 |         1 | f        | f        |          |
   0 |
(1 row)

I tired to issue below select statement, but nothing can find.

dhcp=# select * from lease4 where client_id like
'%3836303031383735303030303030315f61706e31';
 address | hwaddr | client_id | valid_lifetime | expire | subnet_id |
fqdn_fwd | fqdn_rev | hostname | state | user_context
---------+--------+-----------+----------------+--------+-----------+----------+----------+----------+-------+--------------
(0 rows)
_______________________________________________
Kea-users mailing list
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users
Reply | Threaded
Open this post in threaded view
|

Re: [Kea-users] Problem on query ISC KEA PgSQL by client_id

Brian Candler
On 03/01/2020 03:37, BALL SUN wrote:

> below is the entry that I had.
>
>    address   |     hwaddr     |                   client_id
>         | valid_lifetime |         expire         | subnet_id |
> fqdn_fwd | fqdn_rev | hostname | state | user_context
> ------------+----------------+------------------------------------------------+----------------+------------------------+-----------+----------+----------+----------+-------+--------------
> 3227711669 | \x115131110400 |
> \x003836303031383735303030303030315f61706e31 |         864000 |
> 2020-01-12 07:51:02+00 |         1 | f        | f        |          |
>     0 |
> (1 row)
>
> I tired to issue below select statement, but nothing can find.
>
> dhcp=# select * from lease4 where client_id like
> '%3836303031383735303030303030315f61706e31';
>   address | hwaddr | client_id | valid_lifetime | expire | subnet_id |
> fqdn_fwd | fqdn_rev | hostname | state | user_context
> ---------+--------+-----------+----------------+--------+-----------+----------+----------+----------+-------+--------------
> (0 rows)

I'm not using postgresql backend, but have you tried either of these:

select * from lease4 where
client_id=E'\x003836303031383735303030303030315f61706e31';
select * from lease4 where
client_id=x'003836303031383735303030303030315f61706e31';

See:

https://dba.stackexchange.com/questions/203358/how-do-i-write-a-hex-literal-in-postgresql

Note that client_id is a "BYTEA" column:

-- Holds the IPv4 leases.
CREATE TABLE lease4 (
     address BIGINT PRIMARY KEY NOT NULL,        -- IPv4 address
     hwaddr BYTEA,                               -- Hardware address
     client_id BYTEA,                            -- Client ID
     valid_lifetime BIGINT,                      -- Length of the lease
(seconds)
     expire TIMESTAMP WITH TIME ZONE,            -- Expiration time of
the lease
     subnet_id BIGINT,                           -- Subnet identification
     fqdn_fwd BOOLEAN,                           -- Has forward DNS
update been performed by a server
     fqdn_rev BOOLEAN,                           -- Has reverse DNS
update been performed by a server
     hostname VARCHAR(255)                       -- The FQDN of the client
     );


_______________________________________________
Kea-users mailing list
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users