Hey all, We are using Hydra

v1.8.5

more than a year now, and the tables are getting massive, some tables have over 16 Million rows. So I upgraded to a recent version

v1.11.10

, but one of the migrations failed trying to execute this migration with this error

ERROR 1180 (HY000): Got error 5 during COMMIT

, I searched this error and the recommended solution is to run the update or insert instructions in batches to reduce the transaction log size. Though I had another concern, I thought I would need data from the last year, so I tried to run

janitor

it did no clean up that much 🧹. Is it safe to clean up the data manually from these tables? •

hydra_oauth2_consent_request

•

hydra_oauth2_authentication_request

I have facing the same issue but with the different version. I was trying to upgrade to v2 but many change such as DB migrations, CLI etc Please suggest how ti upgrade from v1 to v2 with the safe way

@hallowed-postman-21092 I have written this to do the partial updates for the aforementioned tables.

DROP PROCEDURE IF EXISTS partialUpdates;

CREATE PROCEDURE partialUpdates(tableName varchar(255), startDate varchar(255), endDate varchar(255))
BEGIN
    DECLARE counter INT DEFAULT 0;

    SET @sql = CONCAT('UPDATE ', tableName);
    SET @sql = CONCAT(@sql, ' ', 'SET amr=\'\'') ;
    SET @sql = CONCAT(@sql, ' ', 'WHERE amr IS NULL');
    SET @sql = CONCAT(@sql, ' ', 'AND requested_at BETWEEN ? AND ?');

    PREPARE stmt FROM @sql;

    label: LOOP
        SET counter = counter + 1;
        SET @batchEndDate = date_add(startDate, INTERVAL counter MONTH);

        IF @batchEndDate > endDate THEN
            LEAVE label;
        END IF;

        EXECUTE stmt USING @startDate, @batchEndDate;


        SET @startDate = @batchEndDate;

        ITERATE label;
    END LOOP label;

    DEALLOCATE PREPARE stmt;
END;

SET @startDate = '2020-10-01';
SET @endDate = NOW();

CALL partialUpdates('hydra_oauth2_consent_request', @startDate, @endDate);
CALL partialUpdates('hydra_oauth2_authentication_request_handled', @startDate, @endDate);

DROP PROCEDURE IF EXISTS partialUpdates;

@best-room-11609 Let me try. Thanks

Sure, no worries 🙂.