I got an error when HA-JDBC synchronize Oracle sequences.

[yylqinghao]

I have a sequence in my Oracle databases, and when HA-JDBC try to synchronize it, I got an exception: ORA-01722: invalid number.

This is because of the generated sql by HA-JDBC: ALTER SEQUENCE S_HAJDBCTESTTBL INCREMENT BY (1011 - (SELECT S_HAJDBCTESTTBL.NEXTVAL FROM DUAL)); And I get same error if I directly execute this sql in SQL-Developer tool, and sql: ALTER SEQUENCE S_HAJDBCTESTTBL INCREMENT BY (1011 - 1010) cause that error too.

I think in Oracle's "Alter sequence" grammer, the calculate method may cann't like this: (1011 - 1010), but I didn't find out the right way.

[yylqinghao]

And in net.sf.hajdbc.sync.SynchronizationSupportImpl, line 244:
String sql = support.getAlterSequenceSQL(sequence, sequenceMap.get(sequence) + 1);

What does "1" means? I think your wanted here is sequence.getIncrement(). However, I think it doesn't need to do that.

Let's assume the situation is:
From sourcedatabase: "select S_HAJDBCTESTTBL.nextValue from dual"'s result is 1000, the increment is 5;
From targetdatabase: "select S_HAJDBCTESTTBL.nextValue from dual"'s result is 900, the increment is 5;

Then we execute: ALTER SEQUENCE S_HAJDBCTESTTBL INCREMENT BY 1000-900=100;
execute: "select S_HAJDBCTESTTBL.nextValue from dual", the result will be 900+100=1000! The increment is 100, we change it to 5 by: ALTER SEQUENCE S_HAJDBCTESTTBL INCREMENT BY 5;

everything is OK now:
From sourcedatabase: "select S_HAJDBCTESTTBL.nextValue from dual" will be 1005
From targetdatabase: "select S_HAJDBCTESTTBL.nextValue from dual" will be 1005 too!

[yylqinghao]

From Oracle online document, I find this:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2012.htm#SQLRF00817
It introduces the "ALTER SEQUENCE" sql's grammer, and only integer can be used in it, expressions are forbbiden.

[Paul Ferraro]

I should be able to wrap the subtraction with an eval(...) Or whatever the oracle equivalent is. This is just a fix to the oracle dialect. I'll fix this later today and then release 3.0.3.

[Paul Ferraro]

Actually - drop and recreating is much easier.
https://github.com/ha-jdbc/ha-jdbc/commit/52d79d23961050968ec31d27f3ebd417b7c3d672

[yylqinghao]

With drop and recreating, It may lost some attributes with the sequence, for example: maxvalue、minvalue、cache、cycle, and so on.

[Paul Ferraro]

Hmm. That's true. As far as I can tell, Oracle doesn't support resetting the value of a sequence. Do you have any suggestions?

[yylqinghao]

And there is another problem: with Statement.addBatch(), can't pass an string with ';', because it will cause an exception when executeBatch(). And can't pass an SELECT sql to addBatch() too.

The old Alter sql has ';' and SELECT, The drop and recreating sql has ';', both can't success.

[Paul Ferraro]

Hmm. That's doubly unfortunate. I think the only way to do this is to install some stored procedure on startup, call it alter_sequence(value), similar to this:
http://www.gokhanatil.com/2011/01/how-to-set-current-value-of-a-sequence-without-droppingrecreating.html
Then have the alterSequencePattern() method call this procedure using the target value. WDYT?

[yylqinghao]

That depend on your design. You can fix it by program or by install a procedure.

In my opinion, I prefer to solve it by program:
1. set Oracle alterSequenceFormat="ALTER SEQUENCE {0} INCREMENT BY {1}; SELECT {0}.NEXTVAL FROM DUAL; ALTER SEQUENCE {0} INCREMENT BY {2}"
2. in synchronizeSequences(), retrive the target database's next value manually, not in "alterSequenceFormat".
3. compare the target database sequence's next value and the source database sequence's next value, if they equals, then skip. Note: this step is very needed because in sql:"Alter sequence s_squence increment by number", the number can't be zero! But in the blog you mentioned and HA-JDBC's previous code, it was not mentioned!
4. in Oracle dialect, override getAlterSequenceSQL method, pass in the target database's next value.
5. after get the alterSequence sqls, you must split it by ';', then execute one by one.

There are some other details, I think you can finish it.

This is my suggest, because I don't think an JDBC driver store some private datas in production database is a good idea, and you can't sure the procedure name is already used or not.

[yylqinghao]

In the blog you mentioned, there is an sql caughts my attention: SELECT last_number FROM user_sequences WHERE sequence_name = 'SAMPLE_SEQ'; this sql can used to replace sql: select SAMPLE_SEQ.nextval from dual, for the last sql may change the SAMPLE_SEQ's nextvalue.
I don't know if there are similar sqls for other types of database. if so, I suggest you use it to replace the front one.