Update Joomla 3.4 with Joomla-Updater
Open an article and save it.
No error messages.
getting the following error:
Fehler
Speichern fehlgeschlagen! Fehler: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0-11' for key 'PRIMARY'
PHP 7.0 as CGI
MySQL 5.5.46
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?
Just a hunch: Are you using Smart Search?
Is Smart Search the thing which gives me all these tables called "finder_links_terms1" to "finder_links_termsf"?
Yes.
So yes I use it. Just checked my enabled plugins and there it is ("Suche - Inhalt") in german.
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.
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"?
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.
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
Bitte schön.
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...
Can you check the collation on the Smart Search tables (that's all the ones called #__finder_xxxxx)?
Sorry for that question but do you mean the arranging when you say collation?
I guess you mean how the data is structured.
So sometimes the collation is just empty, sometimes it is set to utf8mb4_general_ci
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?
Okay, please try this:
Clear the index again.
Go to Extensions -> Manage -> Database and click the "Fix" toolbar button.
Rebuild the index again.
Same error.
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).
I disabled the Smart Search plugin and successfully saved an article without an error.
So it is like you said the Smart Search.
Could it also be a problem with another plugin? For example with JEvents?
I checked the the collation in the file and there normally utf8_general_ci is used.
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.
But make sure you re-enable the Smart Search content plugin first, of course. :-)
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.
How can I send you a dump? Can you give me a mail address?
Do you have phpMyAdmin? You can do an export from that. Email to chris.davenport@joomla.org
Mail is send
Got it. Thanks.
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!
Category | ⇒ | Search |
Labels |
Added:
?
|
I exactly (?) have the same problem after update to 3.5.0 and wait curiously for chrisdavenport's answer
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"
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.
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
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
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/
I am using Phoca Search Plugin (for finding pictures based on their descriptions). Does Phoca Search need the index?
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/
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 !!).
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
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.
At the bottom of the list on this page http://www.phoca.cz/download
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.
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.
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.
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
Closed
Status | New | ⇒ | Closed |
Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2016-03-25 17:11:51 |
Closed_By | ⇒ | brianteeman |
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.
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.
Great stuff - amazing detective work
Chris: I would like to stay wirh mySQLi according to e.g. http://www.joomlafreaks.net/blog-item/mysql-vs-mysqli-is-the-improvement-right-for-you.html
I can confirm that indexing works now with mySQL(PDO) too.
Great support!
Thank you very much.
I akso can confirm that indexing (and thus storing articles) now works with mySQL(PDO) too.
Great support and work!
Thank you very much.
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.