Tango database
avatar par défaut
Manu
OK, this means that the package tango_db does not use the script create_db.sql as is.

Cheers

Manu
avatar par défaut
jan
Manu,

No. It use the script create_db.sql as it is (copying it from the tango sources) and that's the problem. One have to change the script either in the debian package or in the tango sources.

Cheers,
Jan
avatar par défaut
Manu
Jan,

I am lost! How is it possible to run this script several times if the MySQL tango database is not dropped between each run?
This script aborts after its first line if the database is already there and therefore could not add entries in
XXX_id tables.

Cheers

Manu
avatar par défaut
jan
Manu,

Ah, yes. You are right. In the package script from debian (tango-db 8.1.2c+dfsg-4~bpo70+1)
this two first lines are missing.


CREATE DATABASE tango;
USE tango;

Cheers,
Jan
avatar par défaut
Manu
Jan,

OK, I understand now. This morning I have sent a mail to Fred (Our Soleil colleague who did the debian packaging) to ask him
his opinion on this subject. May be there is may be a good reason why these two lines have been removed

Cheers

Manu
avatar par défaut
jan
Hi,

I attach the current state plot from my PC with all my patches.



I can correlate groups of the highest spikes with some processes (which e.g. use my harddisk)
Top 5 are:
  • TSM backup
  • rsyslogd
  • serverdump.sh (dumping all tango db properties)
  • login after screensaver
  • puppet
and other cron jobs.

What I've learnt is that it is better to keep tango DB separately on dedicated host with big amounts of memory and a large number of cores where no other program is using host resources.

Cheers,
Jan

Edited 8 years ago
Attachments:
avatar par défaut
picca
Hello,

you are right, I am using the create_db.sql script but not only and with some modifications.

here the patch applyed to the

diff –git a/cppserver/database/create_db.sql.in b/cppserver/database/create_db.sql.in
index 210a335..292d005 100644
— a/cppserver/database/create_db.sql.in
+++ b/cppserver/database/create_db.sql.in
@@ -1,12 +1,8 @@
-
-CREATE DATABASE @TANGO_DB_NAME@;
-USE @TANGO_DB_NAME@;
-
#
# Create all database tables
#

-source create_db_tables.sql
+source /usr/share/tango-db/create_db_tables.sql

#
# Init the history identifiers
@@ -126,5 +122,5 @@ INSERT INTO property_class VALUES ('TangoAccessControl','AllowedAccessCmd',6,'Ge
# Load the stored procedures
#

-source stored_proc.sql
+source /usr/share/tango-db/stored_proc.sql


In fact this script is used during the package installation so I fixed the path.
Now If I remember correctly (I did this years ago with almost zero knowledge about databases…), I removed the first line of the script in order to be able to re-install the package wihtout error during the installation.

On Debian/Ubuntu/Mint… I am using the dbconfig-common[1] system to deal with all the database creation and upgrade from one version to the other.

In the debian/rules file you will see an sql target which create the script used by dbconfig-common to do the database upgrade.

sql:
./configure $(CONFIGURE_OPTS)
# use this target to generate the dbconfig-common upgrade script once the debian/changelog is ok
cat cppserver/database/stored_proc.sql\
cppserver/database/update_db.sql > debian/mysql/$(FULLDEBVERSION)
$(MAKE) distclean


this way during an upgrade the upgrade script take care to create the right tables and fill them with the default values.
I regenerate also the stored_proc in the same time.


Now I need your help to rethink all this and tune the tango sources script in order to make it work properly.
the ideal solution for the packaging woud be to have by default the right scripts expected by dbconfig-common in the tango source.

I also need your help to propose a script which fix the databases generated with the Debian packages.
In order to sanitize them.


Let's speak about all this.


cheers

Fred
[1] https://people.debian.org/~seanius/policy/dbconfig-common.html/
avatar par défaut
jan
Hello Fred,

I see your problem. The dbconfig tool creates DB by itself so "two first lines" are executed by dbconfig and you cannot check if DB was created or it was before.

Therefore, you have to check in the sql sript if the rows from tango tables exist.
(For creating of tables it is done inside create_db_tables.sql so create_db_tables.sql is safe).

My first solution I've send in one of my previous posts (about a revision counter from NeXus Configuraton Server). Then, one has to add into each insert query a check if row was already inserted.

The second solution is to group all insert queries into one sql block with an if statement in front of it. The if command can check whether one of the table rows was added or not.
Unfortunately, the if statement in sql scripts can be only used in an sql procedure or a function so one has to create a procedure with insert queries and call it after.
One has to remember that an sql procedure cannot contain any 'source' command ( which is executed by an sql client interpreter).

So createdb.sql could look like
source create_db_tables.sql

delimiter |

drop procedure if exists populate_tango_rows |

create procedure populate_tango_rows()
begin
    if not exists (select id from device_history_id limit 1) then
        begin
            INSERT INTO device_history_id VALUES (0);
            INSERT INTO device_attribute_history_id VALUES (0);
            INSERT INTO class_history_id VALUES (0);

             /* here there should be all INSERT statements from create_db.sql */

            INSERT INTO property_class VALUES ('TangoAccessControl','AllowedAccessCmd',5,'GetAllowedCommands',NULL,NULL,NULL);
            INSERT INTO property_class VALUES ('TangoAccessControl','AllowedAccessCmd',6,'GetAllowedCommandClassList',NULL,NULL,NULL);
	end;
end |

delimiter ;

call populate_tango_rows();
drop procedure if exists populate_tango_rows;

source stored_proc.sql

Actually, such a procedure could be put into the stored_proc.sql file.
To avoid changing of paths in the source commands you can put create_db.sql into /usr/share/tango-db/
and in your 'mysql' file source it as
source /usr/share/tango-db/create_db.sql

Finally, also those "two first lines" could be put into a separate file, i.e.
CREATE DATABASE @TANGO_DB_NAME@;
USE @TANGO_DB_NAME@;
source create_db.sql

If none of the changes are performed in the tango sources
the second solution is easier to perform (INSERT statments stay as there are).

Cheers,
Jan
avatar par défaut
jan
Hi,

I've found a bug in my innodb-patch which causes a memory leak. To fix it
diff –git a/cppserver/database/DataBase.cpp b/cppserver/database/DataBase.cpp
index b7d779c..0bff3a1 100644
— a/cppserver/database/DataBase.cpp
+++ b/cppserver/database/DataBase.cpp
@@ -6625,6 +6625,7 @@ void DataBase::db_put_device_attribute_property2(const Tango::DevVarStringArray
                                  DEBUG_STREAM << "DataBase::PutAttributeProperty(): sql_query " << sql_query_stream.str() << endl;
                                  result = query(sql_query_stream.str(),"db_put_device_attribute_property2()",al.get_con_nb());
                                  my_ulonglong count = mysql_num_rows(result);
+                                 mysql_free_result(result);
                                  
                                  if (count == 1)
                                    {
This fix improves a situation on the second part of my last plot.

One can only see a spike caused by TSM.

Bests,
Jan
Edited 8 years ago
Attachments:
agotz avatar
Andy
Getting better and better! Nice result - well done Jan!
 
Register or login to create to post a reply.