View Issue Details

IDProjectCategoryView StatusLast Update
0022070mantisbtdb oraclepublic2016-12-26 19:28
Reportercproensa Assigned To 
PrioritynormalSeveritymajorReproducibilityalways
Status newResolutionopen 
Product Version1.3.4 
Summary0022070: db_insert_id() errors
Description

1) This method, for oracle, issues this query:
SELECT seq_' . $p_table . '.CURRVAL FROM DUAL

This can create an incorrect sequence name, becasue ADOdb may have used an alternative name for the sequences and triggers related to autoincrement fields.
This happens when the resulting name for the trigger/seq is longer than 30 chars, in that case, the name is something like: "SEQ_585FB600B1B9A"

In mantis side, this ends with a "ORA-00972: identifier is too long"

2) This method may cause concurrency issues, as it's not isolated from other threads issuing concurrent inserts.
This may affect all databases.

Proposal: use native ADOdb insert_Id() function.

As it reads in the documentation:
<pre>
The function insert_Id() returns the last auto-increment number of any table,
generated by an insert on a table with an auto-increment column. If no such
insertion has occurred or the database does not support the operation, the
function returns false.

The value retrieved is connection based, meaning that each concurrent
connection (even if multiple connections are associated with the same
procedure) has its own instance of the number.
</pre>

http://adodb.org/dokuwiki/doku.php?id=v5:reference:connection:insert_id

[EDIT dregad - break long lines]

TagsNo tags attached.

Activities

cproensa

cproensa

2016-12-25 12:25

developer   ~0054825

@dregad
I think ADOdb does not support Insert_id() for oracle.

This means that in order to build a valid query for the sequence, there should exists some way to get the sequence associated to the table.
Does it exist in ADOdb?

dregad

dregad

2016-12-26 12:53

developer   ~0054829

Proposal: use native ADOdb insert_Id() function.
I think ADOdb does not support Insert_id() for oracle.

Not all ADOdb drivers implement that method (the oci8 driver does not - hasInsertId property is false).

there should exists some way to get the sequence associated to the table.
Does it exist in ADOdb?

Not currently, I don't think.

In Oracle, sequences are completely disconnected from tables, and to my knowledge it's not possible to retrieve a sequence's name from a given table / column.

Considering that ADOdb creates a trigger linked to the sequence to make the column behave like an autonumber, the best approach might be to retrieve the trigger's metadata and use that to get the sequence via dependencies, something like (I didn't actually test this):

<pre>
SELECT d.referenced_name AS seq_name FROM user_triggers t
JOIN user_dependencies d ON d.name = t.trigger_name
WHERE d.referenced_type = 'SEQUENCE'
AND t.table_name = :0
</pre>

Need to be careful if for some reason there are multiple sequences / triggers on a single table, which is technically possible.

References

cproensa

cproensa

2016-12-26 14:04

developer   ~0054831

As i see it now, the name is currently generated with uniqid(), so there is no way to get to it programatically (without querying the db)
This breaks part of the functionality for "autoincrement" fields in a portable scenario.

I think this could be proposed to ADOdb: make available the short name available, even if it's as a driver specific function.
Or implement it in the insert_id() method.

If the name could be generated as some kind of hashed string from the table name, in a repeatable way, all that could be done in the library portability layer.

cproensa

cproensa

2016-12-26 14:12

developer   ~0054832

Note that in mantis, this can be an issue with plugins:

resulting sequence name for plugin tables would be:
'SEQ_' + mantis_prefix + plugin_prefix + plugin_name + mantis_suffix.

Even with minimal prefixes, like "MP", the 30char limit is easily reached.
At that point autoincrement fields have this problem

My scenario is having these fields as foreign keys to other tables. So to insert on 2nd table, i need the inserted id in 1st table.

dregad

dregad

2016-12-26 18:35

developer   ~0054833

This is definitely something that should ideally be fixed in ADOdb and not in Mantis, but it's not so simple given the way Oracle is designed.

I do not know what the rationale was for using uniqid() instead of some other, more predictable hash to build the sequence name in the first place, that does not make much sense to me. To my knowledge, the library currently does not offer any way of retrieving the sequence name.

Implementing Insert_Id() in the oci8 driver using the sequence sounded like a good approach at first, and not so difficult to implement, but thinking about it more I'm concerned that it would likely cause issues in concurrent access scenarios.

An alternative approach could be to use an INSERT statement with a dynamic return clause [1], and retrieve that using bind variables e.g. something like
INSERT INTO table (...) VALUES (...) RETURNING id INTO :id

But that would not be trivial to implement in a generic and robust way at library level (need to modify the SQL).

[1] http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/returninginto_clause.htm#LNPLS01354

cproensa

cproensa

2016-12-26 19:28

developer   ~0054834

Implementing Insert_Id() in the oci8 driver using the sequence sounded like a good approach at first, and not so difficult to implement, but thinking about it more I'm concerned that it would likely cause issues in concurrent access scenarios

Haven't seen how it's implemented in, for example, pgsql (if it's so)
But i see how it may be not concurrency-safe, it's the same observation about the mantis function db_insert_id()
Unless it's executed inside a transaction...

INSERT INTO table (...) VALUES (...) RETURNING id INTO :id

Thanks for the tip.
Unfortunately, that seems to be PL/SQL syntax.