40m QIL Cryo_Lab CTN SUS_Lab TCS_Lab OMC_Lab CRIME_Lab FEA ENG_Labs OptContFac Mariner WBEEShop
  40m Log  Not logged in ELOG logo
Entry  Wed Mar 7 13:59:07 2012, Ryan Fisher, Summary, Computer Scripts / Programs, Alterations to base epics install for installing aLIGO conlog: 
    Reply  Wed Mar 7 18:00:39 2012, Ryan Fisher, Summary, Computer Scripts / Programs, Alterations to base epics install for installing aLIGO conlog: 
       Reply  Thu Mar 8 17:18:19 2012, Ryan Fisher, Summary, Computer Scripts / Programs, Alterations to base epics install for installing aLIGO conlog: 
          Reply  Fri Mar 9 11:02:56 2012, Ryan Fisher, Summary, Computer Scripts / Programs, Alterations to base epics install for installing aLIGO conlog: 
             Reply  Fri Mar 9 15:48:56 2012, Ryan Fisher, Summary, Computer Scripts / Programs, Alterations to base epics install for installing aLIGO conlog: 
                Reply  Fri Mar 9 16:28:10 2012, Ryan Fisher, Summary, Computer Scripts / Programs, Alterations to base epics install for installing aLIGO conlog: 
          Reply  Mon Jul 23 07:39:55 2012, Ryan Fisher, Summary, Computer Scripts / Programs, Alterations to base epics install for installing aLIGO conlog: 
Message ID: 6394     Entry time: Fri Mar 9 15:48:56 2012     In reply to: 6391     Reply to this: 6396
Author: Ryan Fisher 
Type: Summary 
Category: Computer Scripts / Programs 
Subject: Alterations to base epics install for installing aLIGO conlog: 

I decided to make a backup of the database and then delete it and make a new database:
 
cd ~/ryan/database_dumpMar92012
mysqldump -u root -p C1_conlog > C1_conlog.dump.Mar92012 
Note: it appears this failed the first time, thankfully this wasn't a production service yet... In the future, do not trust this backup method for important data!

Next, log into mysql as root, dump the database, remake it and grant privileges again.:
(This is saved in megatron:~/ryan/restore_database.txt
megatron:~/ryan>mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 174
Server version: 5.1.41-3ubuntu12.10 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> list databases;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'list databases' at line 1
mysql> list users;      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'list users' at line 1
mysql> use C1_conlog
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> list users;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'list users' at line 1
mysql> select User from mysql.user;                                             +------------------+
| User             |
+------------------+
| php              |
| C1_conlog_epics  |
| c1_conlog_epics  |
| root             |
| C1_conlog_epics  |
| c1_conlog_epics  |
| debian-sys-maint |
| root             |
| root             |
+------------------+
9 rows in set (0.00 sec)

mysql> show databases;                                                          +--------------------+
| Database           |
+--------------------+
| information_schema |
| C1_conlog          |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)

mysql> drop database C1_conlog ;
Query OK, 2 rows affected (0.56 sec)

mysql> create database C1_conlog;
Query OK, 1 row affected (0.00 sec)

mysql> use C1_conlog ;
Database changed
mysql> SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE TABLE `channels` (
    ->   `channel_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
    ->   `channel_name` varchar(60) NOT NULL,
    ->   PRIMARY KEY (`channel_id`),
    ->   UNIQUE KEY `channel_name` (`channel_name`)
    -> ) ENGINE=MyISAM  DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.04 sec)

mysql>
mysql> CREATE TABLE `data` (
    ->   `acquire_time` decimal(26,6) NOT NULL,
    ->   `channel_id` mediumint(8) unsigned NOT NULL,
    ->   `value` varchar(40) DEFAULT NULL,
    ->   `status` tinyint(3) unsigned DEFAULT NULL,
    ->   `connected` tinyint(1) unsigned NOT NULL,
    ->   PRIMARY KEY (`channel_id`,`acquire_time`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.03 sec)

mysql> grant select, insert, update, execute on * to 'c1_conlog_epics'@'127.0.0.1';  Query OK, 0 rows affected (0.00 sec) 
mysql> grant select, insert, update, execute on * to 'C1_conlog_epics'@'127.0.0.1';   Query OK, 0 rows affected (0.00 sec) 
 mysql> grant select, insert, update, execute on * to 'c1_conlog_epics'@'localhost';  Query OK, 0 rows affected (0.00 sec) 
mysql> grant select, insert, update, execute on * to 'C1_conlog_epics'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on C1_conlog to 'php'@'%';
ERROR 1146 (42S02): Table 'C1_conlog.C1_conlog' doesn't exist
mysql> grant select on * to 'php'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mysql.users
    -> ;
ERROR 1146 (42S02): Table 'mysql.users' doesn't exist
mysql> select User from mysql.user;        
| C1_conlog_epics  |
| c1_conlog_epics  |
| root             |
| C1_conlog_epics  |
| c1_conlog_epics  |
| debian-sys-maint |
| root             |
| root             |
+------------------+
9 rows in set (0.00 sec)

mysql> Bye 



Next, I decided that I want to index on the acquire_time instead of the combination of channel_id and acquire_time (I think it makes a lot of sense for several query types, and especially debugging the conlog!):
mysql> create index acquire_time_index on data(acquire_time);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Next Fix:


The above worked well, but when I restarted the conlog, I had to re-execute the "remove_channels" from the medm, because initially all channels were being loaded (use_channel_names had all the channels still).
Additionally, there were a lot of channels with "*RMS*" in the name that were being recorded, and were changing relatively quickly, so I have added those to the remove_channel_names file.

I am going to: Backup the files in /ligo/caltech/data/conlog/c1
Edit use_channel_names to only have the good channels.
Dump the database again
Stop conlog.
Wipe the database again.
Remake the database again (with permissions and the new index).
Restart the conlog and hope!

The fix above seems to be in place and working. The database has the initial entries for the channels it monitors and is not growing without operators changing EPICs values.

ELOG V3.1.3-