I need help mounting an lvm2 partition with 14.04 LTS live USB

Question

I have this table:

CREATE TABLE STATO_VERS_METODO(
    METODO INT NOT NULL,
    PROGETTO INT NOT NULL,
    VERS NUMBER NOT NULL,
    STATO VARCHAR2(20) DEFAULT 'Nuovo' NOT NULL,
    NOTA_VERSM VARCHAR2(500),
    CONSTRAINT UK_STATO_METODO UNIQUE(METODO, PROGETTO, VERS)
);

If I insert a row with the same unique key (metodo, progetto, vers) I get an error for the violation of uniqueness. At this point I want to modify the row already present and update the "Notes" field, adding the value of :NEW.NOTE to the existing one. I wrote this trigger:

create or replace TRIGGER AGGIORNA_NOTA_METODO
BEFORE INSERT ON STATO_VERS_METODO
FOR EACH ROW
DECLARE
    n_righe INT;
    nota VARCHAR2(500);

BEGIN
    SELECT COUNT(*) INTO n_righe FROM STATO_VERS_METODO WHERE METODO = :NEW.METODO AND PROGETTO = :NEW.PROGETTO AND VERS = :NEW.VERS;
    IF(n_righe > 0)
    THEN
        IF(:NEW.STATO = 'Modificato')
        THEN
            SELECT NOTA_VERSM INTO nota FROM STATO_VERS_METODO WHERE METODO = :NEW.METODO AND PROGETTO = :NEW.PROGETTO AND VERS = :NEW.VERS;
            UPDATE STATO_VERS_METODO
            SET NOTA_VERSM = nota||CHR(10)||:NEW.NOTA_VERSM WHERE METODO = :NEW.METODO AND PROGETTO = :NEW.PROGETTO AND VERS = :NEW.VERS;
        END IF;
    END IF;



END;

It gives me the error for the violation of uniqueness and does not update. How can I modify the existing row?

Solution

You don't mount the partition containing the LVM, you mount the partitions contained in the LVM. Try mounting /dev/mapper/vg1-lv1 instead of /dev/sdb8.

Additionally, since the drive is from a non-x86 NAS, you may need to mount it through fuseext2 due to the non-standard block size (read this question).

Comments

[HoneyBadger]

A before trigger doesn't stop the insert. So after this trigger is fired, the insert is still attempted. That's why you still get the error.

[Bob Jarvis - Слава Україні]

Please edit your question using the edit button just below the tags and include the exact errors you're getting. Thanks.

[davidm]

You just said: If I insert a row with the same unique key (method, project, vers)..... Maybe you want to set the STATO as 'Modificato' when you update a row?

[Gibser]

@dmak2709 let me explain you with an example: let's say we have the following row in the table stato_vers_metodo (1, 1, 10, 'Modificato', 'Source code changed'). I insert a new row in stato_vers_metodo (1, 1, 10, 'Modificato', 'Minor fix') and this causes a violation of the unique constraint on the key (metodo, progetto, vers). So I can't insert the row but I want to modify the existing one by concatenating the NOTA_VERSM field to the old one like this: (1, 1, 10, 'Modificato', 'Source code changed Minor fix')

[xrkelly]

Fuseext2 seems to be the answer so far. I've made some progress with this, but at best it is painfully slow. I was able to 'fuseext2 -o ro -o sync_read /dev/sdb4 /mnt/'. Navigating to /mnt through nautilus was non-productive, I forget the error it gave, something about permissions. I tried 'gksudo nautilus' - it seemed like something was happening, opened a window and said "loading", but seemed to take forever. I was away from the machine for a few days, going to try again later tonight or tomorrow.

[xrkelly]

Finally, I have made some progress!! I still cannot view the files in nautilus, but I was able to navigate the folders in the drive in terminal. I have begun copying the files into another drive, and once I have the irreplaceable stuff moved I'll format the drive into something useable. Thank you so much for your help, and the helpful links.

[Gibser]

Thank you so much. Since I'm not familiar with merge, where should I enter this command? After the insert command or instead of it?

[Volkan Albayrak]

instead of it. If it found a record it does an update else insert.

[Gibser]

Thanks for the alternative with a trigger. So to update automatically when I insert the line and get the exception for the unique key, should I use merge as recommended by Ankit?

[davidm]

You don't update automatically on INSERT,only when an UPDATE statement occurs. Because the row with STATO 'Nuovo' doesn't exist yet. Please read the answer and check all the code.

[Gibser]

I read the answer and thank you, I was just asking to find out if there was a way to "automatically" update the table instead of getting the uniqueness violation error

[Ankit Bajpai]

@Gibser, The best possible alternative is to use MERGE command. 1. You can avoid having a new object in your DB. 2. Triggers are not appreciated at all in RDBMS. Since you once executed them and forgot to maintain later.