search

leginon-org / leginon-redmine-archive

1 stars 0 forks source link

migrate sinedon to be compatible with InnoDB sql engine #5223

Open leginonbot opened 5 months ago

leginonbot commented 5 months ago

Author Name: Anchi Cheng (@anchi2c) Original Redmine Issue: 5223, https://emg.nysbc.org/redmine/issues/5223 Original Date: 2017-09-20 Original Assignee: Anchi Cheng

This will open up the possibility of several features that helps database management.

leginonbot commented 5 months ago

Original Redmine Comment Author Name: Anchi Cheng (@anchi2c) Original Date: 2017-11-16T07:29:30Z

The default is still MyISAM. To switch to create new tables in InnoDB, you need to

  1. change /etc/my.cnf default_storage_engine to InnoDB
  2. restart mysql service
  3. modify your working sinedon.cfg to include at global level 
    
engine: InnoDB
leginonbot commented 5 months ago

Original Redmine Comment Author Name: Neil Voss (@vosslab) Original Date: 2018-08-16T16:04:43Z

Anchi, have we done anything to address the FOREIGN_KEY aspect of InnoDB

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

ALTER TABLE AcquisitionImageData
ADD FOREIGN KEY (`REF|SessionData|session`) REFERENCES SessionData(DEF_id);

I am trying to learn the ins and outs of InnoDB vs. MyISAM and what I can tell it looks we'll need to connect the tables using the FOREIGN KEY flag, especially if we want the multi-master setup to work. Otherwise, it wouldn't know how to properly connect the tables together when locking rows.

leginonbot commented 5 months ago

Original Redmine Comment Author Name: Anchi Cheng (@anchi2c) Original Date: 2018-08-16T19:09:47Z

No, I have not looked at Foreign Key, yet.