Bug #22631
closed
DAM-related: DAM search function crashed on Oracle
0%
Description
I'm using TYPO3 4.3.3 with DBAL 1.0.4 with an Oracle 11g database. DAM 1.1.1 is installed.
When i use the search function in file module, list view, I get the following error:
SELECT COUNT as "count" FROM "tx_dam"
WHERE 1 = 1
AND "tx_dam"."pid" IN (23)
AND ((dbms_lob.instr("tx_dam"."uid", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."t3ver_label", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."title", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."file_name", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."file_path", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."file_dl_name", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."file_type", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."file_type_version", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."file_orig_location", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."file_orig_loc_desc", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."file_creator", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."file_mime_type", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."file_mime_subtype", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."ident", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."creator", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."publisher", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."copyright", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."keywords", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."description", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."caption", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."alt_text", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."instructions", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."abstract", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."loc_desc", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."loc_city", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."width", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."height", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."index_type", 'SAUS',1,1) > 0)
OR (dbms_lob.instr("tx_dam"."file_hash", 'SAUS',1,1) > 0))
AND "tx_dam"."sys_language_uid" = 0
AND "tx_dam"."file_status" != 255
AND ((dbms_lob.instr("tx_dam"."file_path", 'fileadmin/redaktion/bilder/',1,1) > 0))
AND "tx_dam"."deleted" = 0
SQL-Fehler: ORA-06553: PLS-306: Falsche Anzahl oder Typen von Argumenten in Aufruf von 'INSTR'
This is caused by the fields tx_dam.uid, tx_dam.width and tx_dam.height, which are number-fields, so that dbms_lob.instr() dosen't work.
In DAM changelog I found the following notice:
Follow-up of revision 26334: LIKE on Oracle should use dbms_lob.instr() function
I think this issue is related to this behaviour - the fields uid, width and height are originally queried with LIKE, that DBAL transformed to dbms_lob.instr().
Possible workaround could be to check the field type and transform the LIKE-comperator only if it's a LOB or to exclude the specific fields from the query (but if we do so, there will be no possibility to find DAM records with the id (or size)
(issue imported from #M14361)
Files
Updated by Philipp Thiele almost 14 years ago
the attatched patch solves the problem for this specific issue, but I don't know if there are any side-effects ( as far as I can see, there are none - TYPO3 backend search for example does his work) or if this is the most elegant/efficient way to solve this issue
Updated by Xavier Perseguers almost 14 years ago
Hi,
I do not understand why DAM is using LIKE to access number columns. This does not make sense as LIKE is a string operator. DAM should be fixed to use a simple "=" instead.
Problem is that LIKE used on numeric expressions is an extension of standard SQL, this allows to do
SELECT 34 LIKE '3%'
but this won't be supported by other DBMS unless the numeric column is first casted to a string.
Updated by Xavier Perseguers almost 14 years ago
Could you please see whether it now works with current DBAL trunk / head of branch DBAL_1-1? I implemented something similar to use instr instead of dbms_lob.instr for non LOB fields...
Updated by Philipp Thiele almost 14 years ago
Hi,
your patch (v3) works great on my environment. It speeds up TYPO3 FE as described in #14479 and solves the problem with the DAM search function.
Updated by Xavier Perseguers almost 14 years ago
Automatically fixed with related bug.
Thanks Philipp for the feedback.