[SOLVED] Update JEM 2.2.1 to JEM 2.2.2 on Joomla ERROR

Hello,

the update on my Joomla 3.8.4, php Version 7.0.27 from JEM 2.2.1 to 2.2.2 failed with the following message, help appreciated, thanks a lot guys!

Warning
JInstaller: :Install: Error SQL Duplicate key name 'idx_venue'
Extension Update: SQL error processing query: DB function failed with error number 1061
Duplicate key name 'idx_venue'
SQL =
ALTER TABLE `#__jem_events`
CHANGE `access` `access` INT(10) UNSIGNED NOT NULL DEFAULT '0',
ADD KEY `idx_venue` (`locid`),
ADD KEY `idx_access` (`access`),
ADD KEY `idx_checkout` (`checked_out`),
ADD KEY `idx_pubstate` (`published`),
ADD KEY `idx_createdby` (`created_by`),
ADD KEY `idx_language` (`language`)
;
Package Update: There was an error installing an extension: com_jem.zip

Error
Error installing package

Now the whole component doesn't work.

Joomla Version: 3.8.4
PHP Version: 7.0.27

Hi Origin,

seems there is some confision within your database. The SQL query causing the error message is within updates/2.1.6-dev3.sql. That means the Joomla installer "thinks" it's an update from a JEM version 2.1.5 or less - and not 2.2.1. But the table seems to be newer so the query must fail.

You can try following: Open Extensions - Manage - Manage in backend and find JEM Component. Take a look on Version, I would say it shouldn't show 2.2.1 (although I don't know why). Select this entry and click Refresh Cache. Check Version shown now. If now 2.2.1 is shown update to JEM 2.2.2 should work. If not there is something wrong with your files. (the version is read from administrator/components/com_jem/jem.xml)
At least now it becomes very speculative. You could try to remove all *.sql files from admin/sql/updates/ folder within com_jem.zip and try to install this new zip package. If that worked without errors you can install the original JEM package to get modules and plugins too. But as sayed, very speculative.

But finally I'm also surprised that JEM was working perfect before update but doesn't work after the failed update. In the past Joomla Updater was implemented to revert all changes on error so system should be in a state as before. Independent from the extension to update.

Hallo Hoffi,

thanks a lot for your answer. You're right, there is something with my JEM files and SQL tables. There is any version mismatch between my files and the SQL tables. After refreshing the cache of the JEM component my installed version of the JEM is 2.1.4.2 and not 2.2.1 as expected. I'm really surprised because I don't understand how. That means that all my previous updates went wrong. That's really weird because I didn't notice any error or issue during or after these updates.

Anyways...
I modified the files "2.1.6-dev3.sql" and "2.1.6-dev5.sql" from admin/sql/updates/ directory. I removed line for line (the "ADD KEY" and the "ADD" statements) in the .sql files (try&error) until the update works correctly.

So the update routine to 2.2.2 passed successfully without any error. But I have some new issues now. The component doesn't work correctly. For example, my template and some data are missing now. I m not able to modify my current events in the backend. It seems there is something wrong with the date time field because all dates and the times are missing in the event overview in the backend. Some settings are also lost.

Do you have any idea how can I fix this component?

I think I have to uninstall and install it again to fix the database and the files. Isn't it? That means also to enter all of my data again...

(be sure you backup first)
I propose to export to csv all events, venues, categories and catevents. and copy images/jem via ftp
You choose in jem>settings>global parameters>Cleanup DB on Uninstall=yes
then you uninstall Jem gooing to extensions>manage and uninstall all JEMs
then you install a fresh JEM 2.2.2 and import the venues,categories,events and catevents. You insert the images via ftp.

Hí guys,

I'm sorry for my late response. My problem is solved.
I made backups of my JEM config and content. Then I uninstalled JEM (with Cleanup DB on Uninstall = TRUE). I made a fresh Installation of JEM 2.2.2 and I entered my data again. Everything works fine now.

Thanks a lot for your help.

Thanks for your feedback.
Two things: Meanwhile we are on JEM 2.2.3 and Joomla! 3.8.5. But I am sure, you know