SpecDatabaseUsage

From The Linux Foundation

Note that Bazaar doesn't store *.init files for community part of the database (they are simply too huge to be handled by Bazaar, and there is actually no need in this). For some purposes (e.g. for application analysis in Navigator) community tables are vital. The required files are now located at spidey, in /srv/www/bzr/unofficial/ispras-lsb/community_init/ folder. All files from that folder should be copied to the specdb/ one obtained from Bazaar.

Setting up the MySQL server

Make sure that the following lines are present in the `[mysqldump]` section of your MySQL configuration file (commonly found at /etc/my.cnf or /etc/mysql/my.cnf)

[mysqldump]
: quote-names=false
: extended-insert=false

Creating the LSB MySQL admin user

1. Start the mysql server and set the password.

sh# /sbin/chkconfig --level 35 mysqld on
sh# /sbin/service mysqld start
sh# /usr/bin/mysqladmin -u root password 'password'

2. First, use the mysql program to connect to the server as the MySQL root user.

sh# mysql -u root -p

3. At the `mysql` prompt

mysql> GRANT ALL PRIVILEGES ON *.* TO 'lsbadmin'@'localhost' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'lsbadmin'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
myqsl> exit

4. At the shell prompt

sh# mysqladmin -u root -p reload

NOTE: More fine-grained access control is specified in the dbperms.sql file within the repository.

Environment Variables

Almost all scripts and tools require to access the database after querying for shell environment variables. The following variables will also be referred to when initializing the database. It is useful to put the following in a file called 'environ.sh' and source it.

WARNING: Most Scripts/Tools WILL NOT work if these variables are NOT set

export LSBDBHOST LSBDB LSBUSER LSBDBPASSWD
LSBDBHOST=localhost   # The name of the host which hosts the MySQL database server>
LSBDB=lsb             # The name of the LSB MySQL database>
LSBUSER=lsbadmin      # The name of the mysql user with access to modify the LSB MySQL database>
LSBDBPASSWD=navigat0r # The password of the mysql user who has access to the LSB MySQL database>

NOTE: These variables could be put into .bashrc or .bash_profile files.

Initializing the Database

1. `cd` to the directory where you have checked out the database module from CVS.

sh# cd /path/to/specdb

2. create and populate the database. You can either restore all the database, including Community tables (this can take some time, the size of data is huge!):

sh# make restoreall

or populate only tables that form the specification part:

sh# make -k restore

Submitting Changes to the Main Database

This is an abstraction of the 00README file written by NickStoughton available in the specdb bazaar branch. PLEASE read this file before making changes or the consequences may be dire. This procedure seems convoluted but it is designed to isolate from changes in the mysqldump output format, which seem to happen fairly frequently as MySQL evolves. If you're lucky enough to produce identical output to the checked-in dump in bazaar, you wouldn't need the step of first dumping your local copy in order to do the diffs.

Step 1

Create a local copy of the SpecDatabase. This can be done by following the instructions given above.

Step 2

Create a dump of your local database -

sh# cd specdb
sh# make dump 
sh# mkdir old_dump
sh# cp *.init *.sql old_dump/

Step 3

After making your changes to the local database -

sh# make dump
sh# mkdir new_dump
sh# cp *.init *.sql new_dump/

Step 4 (FIXME)

Generate a diff -

sh# diff -u new_dump/ old_dump/ | grep '^+[^+]'

(Note:Your mileage may vary with the above command, It helped me in one particular case)

Step 5

Generate SQL statements script from the diff output.

Note: Care needs to be taken that the database changes occur in the right order in the SQL script. for eg. If a column characterstics change from <NULL> to <NOT NULL>, Insert or Update commands might fail if they are run BEFORE the column properties are changed

Step 6

Test your changes. Make sure that they do not break anything.

Step 7

Send the SQL statements script to the designated database gatekeeper along with a Changelog.

Troubleshooting

The are some known problems that can arise during database setup and data population. Here is their short classification and solutions:

Errors concerning read permissions on the specdb directory ('cannot create file or something like this) - make sure that the mysql daemon can read this directory contents. Since community data is restored through mysql's 'load data infile', all file operations are performed by mysql's user (usually also named 'mysql').
Complaining about 'syntax error near IF EXISTS ' in the 'DROP TRIGGER IF EXISTS' statement - 'DROP TRIGGER IF EXISTS' is supported since MySQL 5.0.32. If you have older version, you should through away such statements from the 'create_triggers.sql' file and drop the triggers manually (if they exist; surely, if you set up the database for the first time then you have nothing to drop).
ERROR 1227 (42000) at line 9: Access denied - you need the SUPER privilege for this operation - well, this is self-explaining message. In MySQL < 5.1.6, there is no special privilege for triggers creation. You should operate with the database through user with SUPER privilege. If this is not possible, the only solution is to comment out call to 'create_triggers.sql' in the makefile.

[Article] [Discussion] [View source] [History]