Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

flyway clean is not dropping Oracle scheduler jobs or programs #761

Closed
orbfish opened this issue May 21, 2014 · 9 comments
Closed

flyway clean is not dropping Oracle scheduler jobs or programs #761

orbfish opened this issue May 21, 2014 · 9 comments
Labels
Milestone

Comments

@orbfish
Copy link

@orbfish orbfish commented May 21, 2014

I recently added a scheduler job and program to my development schema. When I tried to refresh the schema, I did a flyway clean, and then a flyway migrate.

I got the following error:

ERROR: Found non-empty schema "TESTDATA" without metadata table! Use init() or set initOnMigrate to true to initialize the metadata table.

When I dropped the job and program by hand, I was then able to run migrate again.

Note: I see the option -initOnMigrate, but this causes me two problems:

  • I have a lot of batch files which would be sensitive to trying to add another runline option.
  • I use flyway both to update existing schemas and to refresh schemas from scratch. If I need to modify the job or program, I could only include initOnMigrate (and have it bomb on the update), or not include it, and have it bomb on refresh (my current problem).

StackOverflow link for this problem:

http://stackoverflow.com/questions/23767341/flyway-clean-is-not-dropping-scheduler-jobs-or-programs

Thank you

Update:

Statements used to create the stored procedure

CREATE OR REPLACE PACKAGE CLEANUP AS
PROCEDURE DONIGHTLY;
END CLEANUP;

CREATE OR REPLACE PACKAGE BODY CLEANUP AS
PROCEDURE DONIGHTLY IS
BEGIN
-- blah blah blah
NULL;
END;
END CLEANUP;

Statement used to create the program:

BEGIN
SYS.DBMS_SCHEDULER.CREATE_PROGRAM
(
program_name => 'DO_NIGHTLY'
,program_type => 'STORED_PROCEDURE'
,program_action => 'CLEANUP.DONIGHTLY'
,number_of_arguments => 0
,enabled => FALSE
,comments => NULL
);

SYS.DBMS_SCHEDULER.ENABLE
(name => 'DO_NIGHTLY');
END;

Statement used to create the job:

BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'TEST_CLEANUP'
,schedule_name => 'SYS.DAILY_PURGE_SCHEDULE'
,program_name => 'DO_NIGHTLY'
,comments => NULL
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'TEST_CLEANUP'
,attribute => 'RESTARTABLE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'TEST_CLEANUP'
,attribute => 'LOGGING_LEVEL'
,value => SYS.DBMS_SCHEDULER.LOGGING_OFF);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'TEST_CLEANUP'
,attribute => 'MAX_FAILURES');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'TEST_CLEANUP'
,attribute => 'MAX_RUNS');
BEGIN
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'TEST_CLEANUP'
,attribute => 'STOP_ON_WINDOW_CLOSE'
,value => FALSE);
EXCEPTION
-- could fail if program is of type EXECUTABLE...
WHEN OTHERS THEN
NULL;
END;
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'TEST_CLEANUP'
,attribute => 'JOB_PRIORITY'
,value => 3);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'TEST_CLEANUP'
,attribute => 'SCHEDULE_LIMIT');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'TEST_CLEANUP'
,attribute => 'AUTO_DROP'
,value => FALSE);

SYS.DBMS_SCHEDULER.ENABLE
(name => 'TEST_CLEANUP');
END;

These are the statements I use to clean up the jobs by hand:

BEGIN
SYS.DBMS_SCHEDULER.DROP_JOB
(job_name => 'TEST_CLEANUP');
END;

BEGIN
DBMS_SCHEDULER.DROP_PROGRAM
(program_name => 'DO_NIGHTLY');
END;

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented May 22, 2014

Could you add a small snippet containing the offending statements? This way I can make sure your case will be covered when we fix this.

@axelfontaine axelfontaine added this to the Flyway 3.1 milestone May 22, 2014
@orbfish
Copy link
Author

@orbfish orbfish commented May 22, 2014

I"m not sure what you need - I included the error message I got back. I'll
add the statements used to create the objects, is that what you mean?

On Wed, May 21, 2014 at 10:28 PM, Axel Fontaine notifications@github.comwrote:

Could you add a small snippet containing the offending statements? This
way I can make sure your case will be covered when we fix this.


Reply to this email directly or view it on GitHubhttps://github.com//issues/761#issuecomment-43847505
.

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented May 22, 2014

Yes
Am 22.05.2014 22:52 schrieb "orbfish" notifications@github.com:

I"m not sure what you need - I included the error message I got back. I'll
add the statements used to create the objects, is that what you mean?

On Wed, May 21, 2014 at 10:28 PM, Axel Fontaine notifications@github.comwrote:

Could you add a small snippet containing the offending statements? This
way I can make sure your case will be covered when we fix this.


Reply to this email directly or view it on GitHub<
https://github.com/flyway/flyway/issues/761#issuecomment-43847505>
.


Reply to this email directly or view it on GitHubhttps://github.com//issues/761#issuecomment-43942536
.

@orbfish
Copy link
Author

@orbfish orbfish commented Jun 18, 2014

Is there a schedule or ETA for v3.1? I didn't find any info on flywaydb.org.

Thank you!

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Jun 18, 2014

End of July

@orbfish
Copy link
Author

@orbfish orbfish commented Jun 18, 2014

Thanks so much! I'd like to contribute some code to your project at some
point, but am not able to at the moment. Great tool!

On Wed, Jun 18, 2014 at 11:08 AM, Axel Fontaine notifications@github.com
wrote:

End of July


Reply to this email directly or view it on GitHub
#761 (comment).

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Oct 16, 2014

Fixed with #799

@johnlmorgan
Copy link

@johnlmorgan johnlmorgan commented Mar 16, 2017

Looks like this problem still exists in flyway 4.0.1 when pointing to Oracle 10G (10.2.0.4) as we are on one of the dev teams. (Same flyway scripts work fine on 11G)

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Mar 16, 2017

@johnlmorgan If the problem persists, please open a new issue containing clear steps how to reproduce as well as a possible solution. Pull request with test case welcome!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
3 participants
You can’t perform that action at this time.