?
avatar ggeorgg
ggeorgg
22 Mar 2016

Steps to reproduce the issue

Update Joomla 3.4 with Joomla-Updater
Open an article and save it.

Expected result

No error messages.

Actual result

getting the following error:

Fehler
Speichern fehlgeschlagen! Fehler: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0-11' for key 'PRIMARY'

System information (as much as possible)

PHP 7.0 as CGI
MySQL 5.5.46

Additional comments

The good thing is: The article is saved to the database even when I get this error.

How can I figure out where I have duplicate entries? How did this happen? Has it something to do with my recent update of Joomla to 3.5?
How can I solve this problem or should I just use a backup and try again?

avatar ggeorgg ggeorgg - open - 22 Mar 2016
avatar ggeorgg
ggeorgg - comment - 22 Mar 2016

From the mysql reference (https://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html):
Error: 1022 SQLSTATE: 23000 (ER_DUP_KEY)
Message: Can't write; duplicate key in table '%s'

I can't see in which table I have these duplicate keys.

avatar chrisdavenport
chrisdavenport - comment - 23 Mar 2016

Just a hunch: Are you using Smart Search?

avatar ggeorgg
ggeorgg - comment - 23 Mar 2016

Is Smart Search the thing which gives me all these tables called "finder_links_terms1" to "finder_links_termsf"?

avatar chrisdavenport
chrisdavenport - comment - 23 Mar 2016

Yes.

avatar ggeorgg
ggeorgg - comment - 23 Mar 2016

So yes I use it. Just checked my enabled plugins and there it is ("Suche - Inhalt") in german.

avatar chrisdavenport
chrisdavenport - comment - 23 Mar 2016

If you don't have any content filters set up then you can purge then rebuild the index. That should resolve the issue. If you have a lot of content, then you might need to run the indexer from the command line.

If you have some content filters set up then make a note of the settings because they will be destroyed when you purge, then manually re-enter them after rebuilding. Alternatively, run the indexer from the command line with the --purge option.

avatar ggeorgg
ggeorgg - comment - 23 Mar 2016

I think I don't have that much content so it should propably work without the command line.
By purging you mean just deleting the content of all these "finder_links_terms1"?

avatar chrisdavenport
chrisdavenport - comment - 23 Mar 2016

No. Log in to the Administrator. Go to Components -> Smart Search. Click on the Purge toolbar button (oh, sorry, it's been renamed as "Clear Index"). I think it gives you a little warning message, but that's okay. Then click on the Index toolbar button. It should open a modal progress window. When it's finished, you should be okay.

But before you do that, go to Components -> Smart Search -> Search Filters and make sure you don't have any filters listed there.

avatar ggeorgg
ggeorgg - comment - 23 Mar 2016

Great! Thank you very much your fast response ;)
I think I will get all this to work in minute. Just found all these "Buttons" :D

avatar chrisdavenport
chrisdavenport - comment - 23 Mar 2016

Bitte schön.

avatar ggeorgg
ggeorgg - comment - 23 Mar 2016

Unfortunately this did not solve my problem.
1. I cleared the index from my Smart Search
2. I tried to index my content again but I get again the error message (SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0-0' for key 'PRIMARY')
and only one or two elements are in the index.

So think there is another problem with my database...

avatar chrisdavenport
chrisdavenport - comment - 23 Mar 2016

Can you check the collation on the Smart Search tables (that's all the ones called #__finder_xxxxx)?

avatar ggeorgg
ggeorgg - comment - 23 Mar 2016

Sorry for that question but do you mean the arranging when you say collation?

avatar ggeorgg
ggeorgg - comment - 23 Mar 2016

I guess you mean how the data is structured.

avatar ggeorgg
ggeorgg - comment - 23 Mar 2016

So sometimes the collation is just empty, sometimes it is set to utf8mb4_general_ci

avatar ggeorgg
ggeorgg - comment - 23 Mar 2016

So am I right that every time I save an article the indexer is called and so we can be sure that this indexing is causing this error?

avatar chrisdavenport
chrisdavenport - comment - 23 Mar 2016

Okay, please try this:

Clear the index again.
Go to Extensions -> Manage -> Database and click the "Fix" toolbar button.
Rebuild the index again.

avatar ggeorgg
ggeorgg - comment - 23 Mar 2016

Same error.

avatar chrisdavenport
chrisdavenport - comment - 23 Mar 2016

Yes, the problem is definitely the Smart Search tables. I think you have a couple of words which used to have separate index entries but which are now causing a collision. It could be something like one uses ß but the other uses ss (although I thought we'd resolved that one; that's why the finder tables are utf8mb4_general_ci but the other tables are utf8mb4_unicode_ci).

avatar ggeorgg
ggeorgg - comment - 23 Mar 2016

I disabled the Smart Search plugin and successfully saved an article without an error.
So it is like you said the Smart Search.

avatar ggeorgg
ggeorgg - comment - 23 Mar 2016

Could it also be a problem with another plugin? For example with JEvents?

avatar ggeorgg
ggeorgg - comment - 23 Mar 2016

I checked the the collation in the file and there normally utf8_general_ci is used.

avatar chrisdavenport
chrisdavenport - comment - 23 Mar 2016

You can check that by going to Extensions -> Plugins, then Search Tools, then select "finder" from the "Select Type" dropdown. That will list all the Smart Search plugins. Disable all except one of them, then try clear index/index again. Repeat for each plugin until you get it to fail.

avatar chrisdavenport
chrisdavenport - comment - 23 Mar 2016

But make sure you re-enable the Smart Search content plugin first, of course. :-)

avatar chrisdavenport
chrisdavenport - comment - 23 Mar 2016

Assuming it is failing on Joomla content, could you send me a dump of your #__content table? Looks like there's something in one of the articles that is causing the indexer to fail. I'd like to try it on my localhost so I can figure out what it is.

avatar ggeorgg
ggeorgg - comment - 23 Mar 2016

How can I send you a dump? Can you give me a mail address?

avatar chrisdavenport
chrisdavenport - comment - 23 Mar 2016

Do you have phpMyAdmin? You can do an export from that. Email to chris.davenport@joomla.org

avatar ggeorgg
ggeorgg - comment - 23 Mar 2016

Mail is send

avatar chrisdavenport
chrisdavenport - comment - 23 Mar 2016

Got it. Thanks.

avatar ggeorgg
ggeorgg - comment - 23 Mar 2016

I hope I send you the right tables from my joomla instance.
Meanwhile I also tried this: http://forum.joomla.org/viewtopic.php?f=707&p=3379083#p3379211 without success.
I will take care of this tommorow evening...
Thanks for your support!

avatar brianteeman brianteeman - change - 23 Mar 2016
Category Search
avatar brianteeman brianteeman - change - 23 Mar 2016
Labels Added: ?
avatar sg08234
sg08234 - comment - 24 Mar 2016

I exactly (?) have the same problem after update to 3.5.0 and wait curiously for chrisdavenport's answer

avatar sg08234
sg08234 - comment - 24 Mar 2016

Addition: In my case I can avoid the SQL error message by disabling the plugin "Search index - Content" (Suchindex - Inhalt).
Thus I assume the error message is created by update of the search index. This assumption can be verified by the fact that deleting all search indices and trying to recreate them again creates "SQLSTATE[23000]: Integrity constraint violation"

avatar chrisdavenport
chrisdavenport - comment - 25 Mar 2016

Unfortunately I've not been able to determine what the problem is. Georg kindly sent me a dump of his database before and after the update and both look perfectly correct. I've loaded them into a localhost Joomla (with JEvents and the German language pack installed) and they work without error. I have been completely unable to replicate the problem.

At this point we know that the problem occurs in one of the #__finder_links_terms tables so it must be one of the Smart Search plugins that is triggering the problem. But at the moment I don't know which one.

If you can get the indexing process to fail consistently (that is, it fails every time you do a purge then re-index) then please try disabling the Smart Search plugins then repeat the purge/index with just one plugin enabled each time. That should at least allow us to find out which one is causing the issue. That would be a useful step forward.

I don't think there's anything else I can do without being able to replicate the problem.

avatar sg08234
sg08234 - comment - 25 Mar 2016

Good morning Chris,

and many thanks for your information and your work!

The good (?) news are: In my case the error can be replicated: Whenever I try to recreate the search index or try to store / create an article the SQL-Error is thrown. I have not enabled / installed SmartSearch but use old fashioned Joomla search.

Are you interested in any further information? Unfortunately my Akeeba-Backup is about 800 MB because of a lot of pictures on the website (www.fotoblues.nethttp://www.fotoblues.net).

Additional information :

After update to 3.5 the following messages are shown:
JInstaller: :Install: Die Joomla!-XML-Installationsdatei konnte nicht gefunden werden.
Die Manifest-Datei konnte nicht gefunden werden.
Die Site wurde aktualisiert: Die neue Joomla!-Version ist jetzt 3.5.0.

On Extension | Maintain | Database::
• Die Datenbankschemaversion (3.4.0-2015-02-26) passt nicht zur CMS-Version (3.5.0-2016-03-01).
• Die Datenbankaktualisierungsversion (3.4.8) passt nicht zur CMS-Version (3.5.0).
• Die Spalte „'session_id'“ vom Typ „varchar(191) ist nicht in Tabelle „'fbj_session'“ enthalten. (Von Datei: „3.5.0-2015-07-01.sql“.)
• Die Spalte „'series'“ vom Typ „varchar(191) ist nicht in Tabelle „'fbj_user_keys'“ enthalten. (Von Datei: „3.5.0-2015-07-01.sql“.)
• Der Index „'idx_tag'“ ist nicht in Tabelle „'fbj_contentitem_tag_map'“ enthalten. (Von Datei: „3.5.0-2015-10-26.sql“.)
• Der Index „'idx_type'“ ist nicht in Tabelle „'fbj_contentitem_tag_map'“ enthalten. (Von Datei: „3.5.0-2015-10-26.sql“.)
• Der Index „'idx_link_old'“ ist nicht in Tabelle „'fbj_redirect_links'“ enthalten. (Von Datei: „3.5.0-2016-03-01.sql“.)
• Die Spalte „'old_url'“ vom Typ „VARCHAR(2048) ist nicht in Tabelle „'fbj_redirect_links'“ enthalten. (Von Datei: „3.5.0-2016-03-01.sql“.)
• Die Spalte „'new_url'“ vom Typ „VARCHAR(2048) ist nicht in Tabelle „'fbj_redirect_links'“ enthalten. (Von Datei: „3.5.0-2016-03-01.sql“.)
• Die Spalte „'referer'“ vom Typ „VARCHAR(2048) ist nicht in Tabelle „'fbj_redirect_links'“ enthalten. (Von Datei: „3.5.0-2016-03-01.sql“.)
• Der Index „'idx_old_url'“ ist nicht in Tabelle „'fbj_redirect_links'“ enthalten. (Von Datei: „3.5.0-2016-03-01.sql“.)

This looks like the update process did not update the database. A « Repair » fixes the database problems but the main SQL-error remains.

Many thanks, kind regards and happy Eastern
Michael Rocholl

avatar sg08234
sg08234 - comment - 25 Mar 2016

Whenever I disable the plugin « Search – Index » I can store / create articles.

Thus from my understanding the problem is created by updating the index tables. Unfortunately Joomla debug does not give me the faulty SQL update statement.

Michael

avatar brianteeman
brianteeman - comment - 25 Mar 2016

If you are NOT using smart search then there is no need to enable the
search index. That is only used for "smart search"

On 25 March 2016 at 10:01, sg08234 notifications@github.com wrote:

Whenever I disable the plugin « Search – Index » I can store / create
articles.

Thus from my understanding the problem is created by updating the index
tables. Unfortunately Joomla debug does not give me the faulty SQL update
statement.

Michael


You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub
#9534 (comment)

Brian Teeman
Co-founder Joomla! and OpenSourceMatters Inc.
http://brian.teeman.net/

avatar sg08234
sg08234 - comment - 25 Mar 2016

I am using Phoca Search Plugin (for finding pictures based on their descriptions). Does Phoca Search need the index?

avatar brianteeman
brianteeman - comment - 25 Mar 2016

If you are using their search plugin - then no
If you are using their finder plugin- then yes

On 25 March 2016 at 10:25, sg08234 notifications@github.com wrote:

I am using Phoca Search Plugin (for finding pictures based on their
descriptions). Does Phoca Search need the index?


You are receiving this because you commented.
Reply to this email directly or view it on GitHub
#9534 (comment)

Brian Teeman
Co-founder Joomla! and OpenSourceMatters Inc.
http://brian.teeman.net/

avatar bertmert
bertmert - comment - 25 Mar 2016

After update to 3.5 the following messages are shown: JInstaller: :Install: Die Joomla!-XML-Installationsdatei konnte nicht gefunden werden. Die Manifest-Datei konnte nicht gefunden werden. Die Site wurde aktualisiert: Die neue Joomla!-Version ist jetzt 3.5.0.

You used the extension installer (doesn't work for Joomla updates anymore since J!3.5 !!).

avatar sg08234
sg08234 - comment - 25 Mar 2016

bertmert: I performed the update through the update hint on the admin start page.

If this is the wrong way: Could you please shortly explain the correct way?

Thanks - Michael

avatar sg08234
sg08234 - comment - 25 Mar 2016

brianteeman: What i the finder plugin. At Phoca I only find:

Phoca Gallery Plugin
Phoca Gallery Slideshow Plugin
Phoca Gallery Search Plugin
Phoca Gallery Simple Plugin
Phoca Gallery Button Plugin
Phoca Documentation Navigation Plugin
Phoca Download Plugin
Phoca Download Button Plugin
Phoca Maps Plugin
Phoca International Alias Plugin
Phoca Czech Alias Plugin
Phoca Site Plugin
Phoca Open Graph Plugin

From those I used the Phoca Gallery Search Plugin.

avatar brianteeman
brianteeman - comment - 25 Mar 2016

At the bottom of the list on this page http://www.phoca.cz/download

avatar bertmert
bertmert - comment - 25 Mar 2016

If this is the wrong way: Could you please shortly explain the correct way?

No, not wrong but the error messages look like that you used extension installer. Mabe I'm wrong.

avatar sg08234
sg08234 - comment - 25 Mar 2016

Thanks
brianteemann: OK - Thus I would not need Joomla index - search. On the other hand it gives me bad feeling if such things do not work.
bertmert: I'm just doing a restore and will retry.

avatar sg08234
sg08234 - comment - 25 Mar 2016

I redid everything (Akeeba Restore (version 3.4.8 ) of website and database to empty website / database) and Joomla update to 3.5 through the update hint on the admin start page.
The database obviously is not updated by this procedure (can be seen in extension | maintenance | database). A database repair seems to work (no database error messages after the repair anymore) but this does not eliminate the SQL-errors.

avatar sg08234
sg08234 - comment - 25 Mar 2016

Problem has been solved: I used the wrong database drive: rmySQL (PDO) instead of mySQLi.
Thanks to all supporters and happy Eastern - Michael

Problem ist gelöst: Es lag an der falschen Einstellung des Datenbank-Treibers: mySQL (PDO) statt mySQLi
Danke an alle Helfer und Frohe Ostern - Michael

avatar brianteeman
brianteeman - comment - 25 Mar 2016

Closed

avatar brianteeman brianteeman - change - 25 Mar 2016
Status New Closed
Closed_Date 0000-00-00 00:00:00 2016-03-25 17:11:51
Closed_By brianteeman
avatar brianteeman brianteeman - close - 25 Mar 2016
avatar brianteeman brianteeman - close - 25 Mar 2016
avatar ggeorgg
ggeorgg - comment - 25 Mar 2016

Same for me. I also used as database drive mySQL(PDO) instead of mySQLi.
I think this was already set by my provider when I installed Joomla with the software installation tool of my provider.
After changing to mySQLi (Joomla -> Configuration -> Server -> Database -> mySQLi) the indexing works fine now.
Still I am a bit confused. I thought mySQL(PDO) would be the better choice over mySQLi.
Besides that, my System worked well before the update with mySQL(PDO)...

Also from my side thanks to all of you who supported me to solve this problem.

avatar chrisdavenport
chrisdavenport - comment - 25 Mar 2016

Got it! I can now reproduce the issue so I've been stepping through it and it looks like the problem is in the PDO driver's insertid() method which is failing to return a value after a new record has been inserted.

Expect a PR soon.

avatar brianteeman
brianteeman - comment - 25 Mar 2016

Great stuff - amazing detective work

avatar chrisdavenport chrisdavenport - reference | 78c57c9 - 25 Mar 16
avatar chrisdavenport
chrisdavenport - comment - 25 Mar 2016

@ggeorgg and @sg08234 Please test. Thanks.

avatar sg08234
sg08234 - comment - 26 Mar 2016
avatar ggeorgg
ggeorgg - comment - 26 Mar 2016

I can confirm that indexing works now with mySQL(PDO) too.
Great support!
Thank you very much.

avatar wilsonge wilsonge - reference | aa99b10 - 26 Mar 16
avatar sg08234
sg08234 - comment - 27 Mar 2016

I akso can confirm that indexing (and thus storing articles) now works with mySQL(PDO) too.
Great support and work!
Thank you very much.

Add a Comment

Login with GitHub to post a comment