Opened 7 years ago

Last modified 4 years ago

#2766 new enhancement

reconsider the use of varbinary in database tables

Reported by: cas Owned by:
Priority: medium Milestone: Outstanding Tasks
Component: dhcp4 Version:
Keywords: Cc:
CVSS Scoring: Parent Tickets:
Sensitive: no Defect Severity: N/A
Sub-Project: DHCP Feature Depending on Ticket:
Estimated Difficulty: 0 Add Hours to Ticket: 0
Total Hours: 0 Internal?: no


The dhcp4 component (maybe also the dhcp6 component) makes use of the varbinary datatype:

| hwaddr | varbinary(20) | YES | MUL | NULL | |
| client_id | varbinary(128) | YES | MUL | NULL | |

while this might give a performance benefit over text encoded fields, it makes working with common "off the shelf" database tools difficult, as the value stored is just a binary blob and hard for a user to work with.

if the performance penalty is not too big, the use of varbinary might be reconsidered in favor for a dataype that is easy to handle for users (e.g. typing into SQL statements in the mysql command line tool when searching for all leases of a certail client-id or hw-address).

searching for a hw-addr in an SQL database is quite a common task for DHCP administrators. A common text encoding (08:00:27:CB:AA:68) would make that task much easier.


Change History (4)

comment:1 Changed 7 years ago by cas

If performance does not allow the varbinary to be changed, consider to store the data in human-readable form in addition to the varbinary. While that creates a redundancy, it would greatly enhance the usefulness of the database.

comment:2 Changed 7 years ago by cas

In Dhcp6, the above applies to the DUID

comment:3 Changed 7 years ago by jelte

  • Milestone changed from New Tasks to DHCP Outstanding Tasks

comment:4 Changed 4 years ago by tomek

  • Milestone changed from DHCP Outstanding Tasks to Outstanding Tasks

Milestone renamed

Note: See TracTickets for help on using tickets.