0

I'm currently trying to create a database of 3 values from a server to easily check every minute and be able to easily query and display using a tool like Grafana.

Basically the data is

server01,value01,value02,date_time
server02,value01,value02,date_time
server03,value01,value02,date_time
server04,value01,value02,date_time

This script would run every minute or so, and then data displayed in Grafana. Currently I have the MySQL DB setup like this:

+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| server_name | varchar(20) | NO   | PRI |         |       |
| file_count  | float       | YES  |     | NULL    |       |
| file_size   | float       | YES  |     | NULL    |       |
| curtime     | datetime    | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

The problem is, every time the script runs, it inserts the records as new, so I have duplicate server names, which makes data display possible.

If I make the individual server names the columns, can data be inserted so value01 and value02 can be inserted per column, or does insert only work by row?

sdhummel
  • 15
  • 4
  • You can't get duplicate server names, since `server_name` is a primary key, and primary keys are unique. – Barmar Jul 14 '20 at 20:56
  • 1
    If you mean you're getting an error because of duplicate server names, use `ON DUPLICATE KEY UPDATE` to update the row if it already exists. – Barmar Jul 14 '20 at 20:56
  • Did you mean to say that it makes data display **im**possible? – Barmar Jul 14 '20 at 20:57
  • Correct, if I make another column as Primary key, then I can get duplicates, but right now for testing I made the server_name primary key. And I did mean impossible to display data – sdhummel Jul 15 '20 at 01:42
  • It doesn't have to be the primary key. As long as it has a unique index you can use `ON DUPLICATE KEY UPDATE`. – Barmar Jul 15 '20 at 14:37
  • Would that allow me to use Grafana to visualize historical data? Or would it overwrite the values everytime it runs? – sdhummel Jul 15 '20 at 22:36
  • It will overwrite the values every time. – Barmar Jul 15 '20 at 22:39
  • Oh, you want historical data. You need to make the primary key `(server_name, curtime)` – Barmar Jul 15 '20 at 22:39
  • That will allow you to have multiple rows with the same server and different times. – Barmar Jul 15 '20 at 22:39

0 Answers0