SQL Error when deleting a job

[Xavier Humbert]

Hi,

A user encountered an error message when trying to delete a job :

>     could not execute statement; SQL [n/a]; constraint [null]; nested
> exception is org.hibernate.exception.ConstraintViolationException:
> could not execute statement
> Caused by
>     Cannot delete or update a parent row: a foreign key constraint
> fails (`rdhorsprodmutu`.`scheduled_execution_stats`, CONSTRAINT
> `FKpwc0snw00ehserhlpgftbu8aw` FOREIGN KEY (`se_id`) REFERENCES
> `scheduled_execution` (`id`))
What can cause this ?

Regards,

Xavier

--
Xavier Humbert
CRT Supervision et Exploitation de Niveau 1
Rectorat de Nancy-Metz
03 83 86 27 39

[rac...@rundeck.com]

Hi Xavier,

It seems a old rare issue. Which Rundeck version are you using? Is that version an upgraded instance? What database are you using on that instance? Is a random issue?

Greetings!

[Xavier Humbert]

Hi Rainer,

It has been, if I remember, as 3.0.x, upgraded regularly up to to 4.14. The database is MariaDB 5.5 (old, I know, but this is RedHat)

Thanks

Regards

Xavier

Le 6/27/23 15:53, rac...@rundeck.com a écrit :

--
You received this message because you are subscribed to the Google Groups "rundeck-discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rundeck-discu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/rundeck-discuss/f1aa0bb7-32b1-4884-b66d-6e690f8f304cn%40googlegroups.com.

[rac...@rundeck.com]

Hi Xavier,

Probably the structure wasn't migrated properly in some point. Take a look a this. Finding the root cause may require take some time and tests on a non prod environment.

Regards!

[Alexander Szele]

We're experiencing this same issue on 4.14 as well. Unclear if we ran that migration procedure back when we jumped from 3.3 to 3.4 as it was a while ago.

Is that process safe to run this late in the upgrade timeline, or are we basically forced to do a complete uninstall/reinstall to get this to a stable state?

[JP Lassnibatt]

Hello there, could you double check you have those attributes in rundeck-config.properties file?

dataSource.dbCreate=none
grails.plugin.databasemigration.updateOnStart=true

Are you seeing this issue only when trying to delete a job?

Thanks

[Alexander Szele]

We still have dataSource.dbCreate=update and do not have the databasemigration attribute, but that's my question. Is it safe to run that as late as 4.14? That was listed under the 3.4 upgrade step and we clearly missed it along the way. Is there fallout from running it this late?

We only see the issue when trying to delete SOME jobs. We can delete others without issue. No clear idea if there's a pattern to the jobs that are giving the problem, but if the issue is the db migration, it might be that the jobs giving errors predate our upgrade past 3.4...

[JP Lassnibatt]

In that case, I'd create a db backup and test the upgrade in a dev environment, I think it should be ok to add those parameters, but as it was not a scenario we tested, we can not be sure.

[Alexander Szele]

Thanks! It'll be a while before we're able to take a run at this, but if it works (or causes problems), I'll post back in here. 

Thanks again

[Xavier Humbert]

Hi, JP,

I  have set those attributes in rundeck-config.properties, upgraded to 4.15.0, restarted rundeck, the problem is still here :

An Error Occurred

could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

Error Details
Error 500: Internal Server Error

URI
    /hpmutu/scheduledExecution/delete
Class
    java.sql.SQLException
Message

    could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement
Caused by
    Cannot delete or update a parent row: a foreign key constraint fails (`rdhorsprodmutu`.`scheduled_execution_stats`, CONSTRAINT `FKpwc0snw00ehserhlpgftbu8aw` FOREIGN KEY (`se_id`) REFERENCES `scheduled_execution` (`id`))

Trace

No stack trace available

Is there any SQL statement I could execute to fix this ?

Regards,

Xavier

Le 7/20/23 21:02, JP Lassnibatt a écrit :

To view this discussion on the web visit https://groups.google.com/d/msgid/rundeck-discuss/0350e528-ef06-45ee-87fe-2f386da7e1f0n%40googlegroups.com.

-- 
Xavier Humbert
CRT Supervision et Exploitation de Niveau 1
Rectorat de Nancy-Metz
Ministère de l'Éducation Nationale et de la Jeunesse
03 83 86 27 39

[rac...@rundeck.com]

Hi Xavier,

I updated your Github request.

Let's continue this researching there.

Thanks!

[rac...@rundeck.com]

Hi folks, I reproduced the issue, just take a look at this.

As usual, please, test the solution on a non-prod environment first.

Regards!