Project

General

Profile

Actions

Bug #17619

closed

INSERT-Error on table "index_words" on ORACLE and PostgreSQL

Added by Moreno Feltscher over 16 years ago. Updated over 6 years ago.

Status:
Closed
Priority:
Should have
Assignee:
-
Category:
Indexed Search
Target version:
-
Start date:
2007-09-19
Due date:
% Done:

0%

Estimated time:
TYPO3 Version:
8
PHP Version:
Tags:
Complexity:
Is Regression:
No
Sprint Focus:

Description

An error occurs when I try to index external documents like PDF:
Warning: ociexecute() [function.ociexecute]: ORA-00001: unique constraint (INTRANET.SYS_C0090792) violated in ...\typo3\sysext\adodb\adodb\drivers\adodb-oci8.inc.php on line 1017

In the dbal-Log I see this (example):
sqlError ORA-00001: unique constraint (*.SYS_C0090792) violated

INSERT INTO "index_words" ( "wid", "baseword", "metaphone" ) VALUES ( '88728859', 'schalter', '212743736' )

This error means there is already one record with this "wid" ("wid" is the key).

So I tried to fix this bug and found something interesting on typo3\sysext\indexed_search\class.indexer.php on line 1843: "// A duplicate-key error will occur here if a word is NOT unset in the unset() line. However as long as the words in $wl are NOT longer as 60 chars (the baseword varchar is 60 characters...) this is not a problem."
This may be true on MySQL, but with Oracle it failes.

So I fixed the function like this:
function checkWordList($wl) {
reset($wl);
$phashArr = array();
while(list($key,) = each($wl)) {
$phashArr[] = $wl[$key]['hash'];
}
if (count($phashArr)) {
$cwl = implode(',',$phashArr);
$res = $GLOBALS['TYPO3_DB']->exec_SELECTquery('baseword', 'index_words', 'wid IN ('.$cwl.')');

if($GLOBALS['TYPO3_DB']->sql_num_rows($res)!=count($wl)) {
$this->log_setTSlogMessage('Inserting words: '.(count($wl)-$GLOBALS['TYPO3_DB']->sql_num_rows($res)),1);
while($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
unset($wl[$row['baseword']]);
}
reset($wl);
while(list($key,$val)=each($wl)) {
$insertFields = array(
'wid' => $val['hash'],
'baseword' => $key,
'metaphone' => $val['metaphone']
);
/* NEW: FIX /
$res = $GLOBALS['TYPO3_DB']->exec_SELECTquery('
', 'index_words', 'wid = \''.$val['hash'].'\'');
if($GLOBALS['TYPO3_DB']->sql_num_rows($res)==0) {
$GLOBALS['TYPO3_DB']->exec_INSERTquery('index_words', $insertFields);
}
/* END FIX */
}
}
}
}

Now it works.
Can please anyone check the fix and give me feedback?

Indexed Search Engine version is 2.10.0
(issue imported from #M6369)


Files

0006369.diff (1.29 KB) 0006369.diff Administrator Admin, 2008-01-15 12:39

Related issues 1 (0 open1 closed)

Has duplicate TYPO3 Core - Bug #84541: Uncaught TYPO3 Exception in indexed_search: duplicate key (hash) errorResolved2018-03-27

Actions
Actions #1

Updated by Martin Kutschker over 16 years ago

Same problem exists for PostgreSQL.

Actions #2

Updated by Moreno Feltscher over 16 years ago

What about adding this fix in the core?

Actions #3

Updated by Ernesto Baschny over 16 years ago

Isn't the "real" problem the fact that the "wid" is just a 32bit integer and that colisions WILL occur (two words generating the same 32bit integer hash? I have seen this happening in another application where I tried to use the same "hashing" method (first couple of bytes of md5). So if in mysql we are just "ignoring" the colision, we might as well do the same in other DBs. I think adding a "select" before inserting isn't the most efficient way of dealing with it, as the DB itself already does that when inserting.

For most compatibility it would require that under DBAL, this:

$GLOBALS['TYPO3_DB']->exec_INSERTquery('index_words', $insertFields);

never dies on constraint violations.

Actions #4

Updated by Moreno Feltscher about 16 years ago

I don't think it is a good way to accept errors in an application at all. So in this case SELECT before INSERT is the only way to provide a software without DB errors.

Actions #5

Updated by Ernesto Baschny about 16 years ago

In this particular case, there is no difference between:

- not inserting because we check it first or
- not inserting because the database checks it while inserting.

In both cases the record is not inserted.

I would say that best would be that exec_INSERTquery should return an error code if there was a constraint violation and not PHP die, which is in my eyes ugly and probably more difficult to catch.

Actions #6

Updated by Moreno Feltscher about 16 years ago

OK this also would be nice. Is it possible to do a patch for this?

I just don't want to have such PHP errors in my log.

Actions #7

Updated by Francois Suter over 15 years ago

Actually the error happens with MySQL too. Having SQLdebug turned on I sometimes get messages like:

Duplicate entry '4371349' for key 1

for a query such as:

INSERT INTO index_words
(
wid,
baseword,
metaphone
) VALUES (
'4371349',
'dernières',
'139961241'
)

Actions #8

Updated by Alexander Opitz almost 11 years ago

  • Status changed from New to Needs Feedback
  • Target version deleted (0)
  • TYPO3 Version set to 4.2

The issue is very old, does this issue exists in newer versions of TYPO3 CMS (4.5 or 6.1)?

Actions #9

Updated by Alexander Opitz over 10 years ago

  • Status changed from Needs Feedback to Closed

No feedback for over 90 days.

Actions #10

Updated by Markus Kappe about 7 years ago

  • Is Regression set to No

With TYPO3 7.6.16 / MySQL I occasionally get the error message:

'Duplicate entry '116391022' for key 'PRIMARY''
'INSERT INTO index_words (wid,baseword,metaphone) VALUES ('116391022','rosemarie','14925119')'

Could it be that the this issue is still valid?

Actions #11

Updated by Hannes Bochmann almost 7 years ago

  • TYPO3 Version changed from 4.2 to 7

I can confirm that this error occurs occasionally in TYPO3 7.6.20.

Actions #12

Updated by Deividas Simas over 6 years ago

  • TYPO3 Version changed from 7 to 8

I can confirm this happening in 8.7 with sqlDebug off...

Actions #13

Updated by Sven Burkert over 4 years ago

  • Has duplicate Bug #84541: Uncaught TYPO3 Exception in indexed_search: duplicate key (hash) error added
Actions

Also available in: Atom PDF