Tango database - TANGO Controls

# 9 years ago
jan	Hi, I would like to ask if it is possible to change create_db.sql script just to insert new rows into tables only if they not exist. In the corresponding debian package this script is called 'mysql'. It may cause problems during reinstallation of this package by creating to many rows, e.g. in device or *_history_id tables. Bests, Jan

# 9 years ago
Andy	Jan, yes this is what happened in the case you had multiple (61) rows of *_history_id. On my laptop I have 5 rows. Do you have a patch for the create_db.sql script? Do you know how to drop all but one entry of a table? Just asking to see if you know the answer already. Kind regards Andy

# 9 years ago
Jean-Luc	Hi, It is not possible to use auto increment for history ids due to array values. An interesting improvement would be to find a strategy to disable history for some properties. Properties which change often and by program may not need a history.

# 9 years ago
Andy	Claudio, I agree with you it could be a good idea to get rid of the history_id table. Your idea of using AUTO_INCREMENT could do it. Another way could be to use the time of the update to order the history. Just an idea. Kind regards Andy

# 9 years ago
jan	Hi Andy, No, I've not got any script or patch. Actually, now in DESY Hannes is working on scripts which should clean up all the mess in DB. But probably it will be DESY specific solution. Bests, Jan

# 9 years ago
jan	Hi Claudio, Your test looks really nice. From my test I can add that usually maximum value is quite random. And in my patch there was a lot of print-outs (I don't know if you have removed them or send to /dev/null) which are also a source of spikes. Bests, Jan

# 9 years ago
Jean-Luc	Andy, Using the time stamps may be slower than using ids (for history requests) as you will have to order by date and select on time/device/attribute/name instead of id. However for updates, we write without id (no locking problem) and the history cleaning might be faster but this solution does not solve the problem of multiple updates by several databadeds at the same time (which should be very rare). Jean-Luc

# 9 years ago

Jean-Luc

Andy,

Using the time stamps may be slower than using ids (for history requests) as you will have to order by date and select on time/device/attribute/name instead of id. However for updates, we write without id (no locking problem) and the history cleaning might be faster but this solution does not solve the problem of multiple updates by several databadeds at the same time (which should be very rare).

Jean-Luc

# 9 years ago
jan	I've taken a look on my sql script where I initialize a revision counter in a properties table for NeXus Configuration Server and my insert statement looks like `INSERT INTO properties (name, value) SELECT * FROM (SELECT 'revision', '0') AS tmp WHERE NOT EXISTS ( SELECT name FROM properties WHERE name ='revision' ) LIMIT 1;` We could do similar things for create_db.sql

# 9 years ago
Manu	Hello all, There is something I do not understand. The create_db script insert the history id in the XXX_id tables. I don't think the server is able to add entry in these tables because in its code there is only UPDATE command for these tables. The create_db script start with the line `CREATE DATABASE tango` If the database already exist, this line fails and the script abort. I have just tried this on my Ubuntu host. Therefore how is it possible to have several lines in these XXX_id tables if the create_db.sql script abort if the db is already created when it is executed? Cheers

# 9 years ago

Manu

Hello all,

There is something I do not understand.
The create_db script insert the history id in the XXX_id tables. I don't think the server is able to add entry in these tables
because in its code there is only UPDATE command for these tables.
The create_db script start with the line

CREATE DATABASE tango

If the database already exist, this line fails and the script abort. I have just tried this on my Ubuntu host.
Therefore how is it possible to have several lines in these XXX_id tables if the create_db.sql script abort if the db is already created when it is executed?

Cheers

# 9 years ago
jan	Hi Manu, The problem is not on the level of Databaseds but on the level of packages, i.e. .deb or .rpm, which usually use the create_db.sql script to install the tango database. E.g. for debian, if you install tango-db and remove it (without cleaning the database) and then install tango-db again you get duplicated rows. Of course one can do it by changing the script in the package but it would be easier if we do it in the original script. Bests, Jan

# 9 years ago

jan

Hi Manu,

The problem is not on the level of Databaseds but on the level of packages, i.e. .deb or .rpm, which usually use the create_db.sql script to install the tango database.

E.g. for debian, if you install tango-db and remove it (without cleaning the database) and then install tango-db again
you get duplicated rows.

Of course one can do it by changing the script in the package but it would be easier if we do it in the original script.

Bests,
Jan