GraceDB Server issueshttps://git.ligo.org/computing/gracedb/server/-/issues2019-04-22T18:22:09Zhttps://git.ligo.org/computing/gracedb/server/-/issues/50MySQL strict mode2019-04-22T18:22:09ZTanner PrestegardMySQL strict modeCreated November 16, 2017. Copied from redmine (https://bugs.ligo.org/redmine/issues/5985)
After upgrading to Django 1.11, you get the following warning when doing migrations:
```
?: (mysql.W002) MySQL Strict Mode is not set for databa...Created November 16, 2017. Copied from redmine (https://bugs.ligo.org/redmine/issues/5985)
After upgrading to Django 1.11, you get the following warning when doing migrations:
```
?: (mysql.W002) MySQL Strict Mode is not set for database connection 'default'
HINT: MySQL's Strict Mode fixes many data integrity problems in MySQL, such as data truncation upon insertion, by escalating warnings into errors. It is strongly recommended you activate it. See: https://docs.djangoproject.com/en/1.11/ref/databases/#mysql-sql-mode
```
The link suggests adding one of the following to your DATABASES OPTIONS:
```
'init_command': "SET sql_mode='STRICT_TRANS_TABLES'"
'init_command': "SET sql_mode='STRICT_ALL_TABLES'"
```
Both of these can lead to issues with the MyISAM engine (see https://www.noelherrick.com/blog/mysql-strict_all_tables-vs-strict_trans_tables). I think we're going to ignore this warning until we potentially update to InnoDB.https://git.ligo.org/computing/gracedb/server/-/issues/51Change table engine for database2022-08-03T18:44:32ZTanner PrestegardChange table engine for databaseCreated on November 16, 2017. Copied from redmine (https://bugs.ligo.org/redmine/issues/5984)
Starting this issue just to have a place to record some research, notes, etc. on our database configuration. Currently, we use MySQL (effectiv...Created on November 16, 2017. Copied from redmine (https://bugs.ligo.org/redmine/issues/5984)
Starting this issue just to have a place to record some research, notes, etc. on our database configuration. Currently, we use MySQL (effectively; moving to MariaDB shortly, in Debian 9) and we use MyISAM as a table engine. My understanding is that MyISAM is antiquated and really only around for legacy support. Note that MariaDB offers an updated alternative to MyISAM called Aria. But most things I've read seem to indicate that InnoDB is highly preferred and is a good general-purpose engine. The main issues I see with MyISAM is that it doesn't have transactions (I would think that is very important) and it technically doesn't have foreign keys, but Django seems to handle that sufficiently well on its own.
However, I note that Branson specifically switched from InnoDB to MyISAM in the following commit from 2014:
```
commit 2d4b3c483a4e40af8ff3ef3336bd9272b28ef01a
Author: Branson Stephens <branson.stephens@ligo.org>
Date: Sat Sep 27 16:10:37 2014 -0500
Committed changes to avoid new tables using INNODB and old ones using MYISAM. This leads to fk constraint errors.
```
So do we really want to go to InnoDB? I ran a few tests and noted that just running the initial migrations for a new 'gracedb' database took ~ 2 mins with InnoDB, compared to 10 secs with MyISAM. So speed might be an issue (or maybe there are just some knobs to tweak which would help). I don't think this is a top priority so I'm just jotting down some notes here for future reference. Note that these are based on brief research and are definitely not exhaustive.
InnoDB benefits:
* ACID transactions
* Row-level locking (only table-level with MyISAM)
* Foreign key constraints (technically don't exist in MyISAM)
* Automatic crash recovery
* Table compression (read/write)
* Spatial data types and indexes
MyISAM benefits:
* Fast COUNT(*) (when WHERE, GROUP BY, or JOIN is not used)
* Smaller disk footprint
* Very high table compression (read only)
If we end up wanting to switch to InnoDB, we need to do two things:
* Remove the line in settings/base.py which forces new tables to use MyISAM ('init_command': "SET storage_engine=MyISAM")
* Convert all tables from MyISAM to InnoDB:
```
SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=InnoDB;') FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'gracedb' AND ENGINE = 'MyISAM' AND TABLE_TYPE = 'BASE TABLE';
```
Note that this SQL command hasn't been tested and definitely should be!https://git.ligo.org/computing/gracedb/server/-/issues/52Change database backend from MySQL (MariaDB) to PostgresQL2022-02-01T18:44:18ZTanner PrestegardChange database backend from MySQL (MariaDB) to PostgresQLCreated on November 16, 2017. Copied from redmine (https://bugs.ligo.org/redmine/issues/5986)
PostgreSQL seems to the the preferred RDBMS for much of the Django community. A comparison here: https://www.digitalocean.com/community/tutori...Created on November 16, 2017. Copied from redmine (https://bugs.ligo.org/redmine/issues/5986)
PostgreSQL seems to the the preferred RDBMS for much of the Django community. A comparison here: https://www.digitalocean.com/community/tutorials/sqlite-vs-mysql-vs-postgresql-a-comparison-of-relational-database-management-systems.
Making the switch would likely be complicated. But if we can make the argument that it would significantly improve performance (speed and/or reliability), it may be worth it.
One point of note: the abstract base `AutoIncrementModel` would need to be reworked since it executes raw MySQL queries, which likely wouldn't be compatible.O4 Infrastructure ImprovementsDuncan MeacherDuncan Meacherhttps://git.ligo.org/computing/gracedb/server/-/issues/140Add VOEvent columns2019-07-11T13:22:52ZTanner PrestegardAdd VOEvent columnsThere are a variety of VOEvent attributes which are required from the user to create a VOEvent and are included in the resulting files, but are **not** saved in the VOEvent database table.
We should
a) Fix this by adding the columns
b)...There are a variety of VOEvent attributes which are required from the user to create a VOEvent and are included in the resulting files, but are **not** saved in the VOEvent database table.
We should
a) Fix this by adding the columns
b) Try to retroactively update VOEvents from their corresponding fileshttps://git.ligo.org/computing/gracedb/server/-/issues/159Old database tables2019-09-11T14:33:24ZTanner PrestegardOld database tablesThere are a number of tables in the production database which appear to be completely unused by the Django application:
* coinc_definer
* coinc_event
* coinc_event_map
* coinc_inspiral
* events_approval
* experiment
* experiment_map
* e...There are a number of tables in the production database which appear to be completely unused by the Django application:
* coinc_definer
* coinc_event
* coinc_event_map
* coinc_inspiral
* events_approval
* experiment
* experiment_map
* experiment_summary
* ligolwids
* multi_burst
* process
* process_params
* search_summary
* search_summvars
* sngl_inspiral
* south_migrationhistory
* time_slide
Unsure about the `auth_message` table: maybe used for messages (?). Doubtful.
These tables should be briefly checked for the contents and likely just deleted from the MariaDB interface with `DROP TABLE tbl_name;`