errors in DB if JEM 2.3.6, if JEM is uninstalled before update

[Heklaterriol]

I checked out the errors which occur, if someone does the update process, but uninstalled JEM 2.3.6. I did a correct update (without uninstalling), one with uninstalling and then compared the DBs.

I then created a SQL command list, to fix the defective DB:

ALTER TABLE `#__jem_attachments` CHANGE `added` `added` DATETIME NULL DEFAULT NULL; 
ALTER TABLE `#__jem_categories` CHANGE `description` `description` mediumtext DEFAULT NULL;
ALTER TABLE `#__jem_categories` CHANGE `meta_keywords` `meta_keywords` text DEFAULT NULL;
ALTER TABLE `#__jem_categories` CHANGE `meta_description` `meta_description` text DEFAULT NULL;
ALTER TABLE `#__jem_categories` CHANGE `checked_out_time` `checked_out_time` datetime DEFAULT NULL;
ALTER TABLE `#__jem_categories` CHANGE `checked_out_time` `checked_out_time` datetime DEFAULT NULL;
ALTER TABLE `#__jem_categories` CHANGE `note` `note` varchar(255) DEFAULT NULL;
ALTER TABLE `#__jem_categories` CHANGE `language` `language` varchar(7) DEFAULT NULL;
ALTER TABLE `#__jem_categories` CHANGE `created_time` `created_time` datetime NOT NULL DEFAULT current_timestamp();
ALTER TABLE `#__jem_categories` CHANGE `path` `path` varchar(255) DEFAULT NULL;
ALTER TABLE `#__jem_categories` CHANGE `metadata` `metadata` varchar(2048) DEFAULT NULL;
ALTER TABLE `#__jem_categories` CHANGE `modified_time` `modified_time` datetime DEFAULT NULL;
ALTER TABLE `#__jem_categories` CHANGE `email` `email` varchar(200) DEFAULT NULL;
ALTER TABLE `#__jem_categories` ADD `emailacljl` tinyint(4) NOT NULL DEFAULT 0;
ALTER TABLE `#__jem_cats_event_relations` CHANGE `ordering` `ordering` tinyint(11) NOT NULL DEFAULT 0
UPDATE #__jem_config SET value='H:i' WHERE `value`='%H:%M';
UPDATE #__jem_config SET value='H' WHERE `value`='%H';
UPDATE #__jem_config SET value='jpg,gif,png,webp' WHERE `value`='jpg,gif,png';
ALTER TABLE `#__jem_events` CHANGE `modified` `modified` datetime DEFAULT NULL;
ALTER TABLE `#__jem_events` CHANGE `created` `created` datetime NOT NULL DEFAULT current_timestamp();
ALTER TABLE `#__jem_events` CHANGE `meta_keywords` `meta_keywords` varchar(200) DEFAULT NULL;
ALTER TABLE `#__jem_events` CHANGE `meta_description` `meta_description` varchar(255) DEFAULT NULL;
ALTER TABLE `#__jem_events` CHANGE `checked_out_time` `checked_out_time` datetime DEFAULT NULL;
ALTER TABLE `#__jem_events` ADD `reservedplaces` int(11) NOT NULL DEFAULT 1 AFTER `maxplaces`;
ALTER TABLE `#__jem_events` ADD `maxbookeduser` int(11) NOT NULL DEFAULT 1 AFTER `maxplaces`;
ALTER TABLE `#__jem_events` ADD `minbookeduser` int(11) NOT NULL DEFAULT 0 AFTER `maxplaces`;
ALTER TABLE `#__jem_events` CHANGE `attribs` `attribs` varchar(5120) NOT NULL DEFAULT '''';
ALTER TABLE `#__jem_events` CHANGE `language` `language` char(7) NOT NULL DEFAULT '''';
ALTER TABLE `#__jem_events`  MODIFY author_ip varchar(39);
ALTER TABLE `#__jem_groups` CHANGE `description` `description` mediumtext DEFAULT NULL;
ALTER TABLE `#__jem_groups` CHANGE `checked_out_time` `checked_out_time` datetime DEFAULT NULL;
ALTER TABLE `#__jem_register` ADD `places` int(11) NOT NULL DEFAULT 1 AFTER `uid`;
ALTER TABLE `#__jem_venues` CHANGE `locdescription` `locdescription` mediumtext DEFAULT NULL;
ALTER TABLE `#__jem_venues` CHANGE `meta_keywords` `meta_keywords` text DEFAULT NULL;
ALTER TABLE `#__jem_venues` CHANGE `meta_description` `meta_description` text DEFAULT NULL;
ALTER TABLE `#__jem_venues` CHANGE `created` `created` datetime NOT NULL DEFAULT current_timestamp();
ALTER TABLE `#__jem_venues` CHANGE `modified` `modified` datetime DEFAULT NULL;
ALTER TABLE `#__jem_venues` CHANGE `checked_out_time` `checked_out_time` datetime DEFAULT NULL;
ALTER TABLE `#__jem_venues` CHANGE `publish_up` `publish_up` datetime DEFAULT NULL;
ALTER TABLE `#__jem_venues` CHANGE `publish_down` `publish_down` datetime DEFAULT NULL;
ALTER TABLE `#__jem_venues` CHANGE `attribs` `attribs` varchar(5120) DEFAULT NULL;
ALTER TABLE `#__jem_venues` CHANGE `language` `language` char(7) DEFAULT NULL;
UPDATE #__jem_categories SET `checked_out_time` = NULL WHERE `checked_out_time`='0000-00-00 00:00:00';
UPDATE #__jem_categories SET `modified_time` = NULL WHERE `modified_time`='0000-00-00 00:00:00';
UPDATE #__jem_events SET `modified` = NULL WHERE `modified`='0000-00-00 00:00:00';
UPDATE #__jem_events SET `checked_out_time` = NULL WHERE `checked_out_time`='0000-00-00 00:00:00'; 

I hope I caught all.

Maybe it is of some use …

[Heklaterriol]

Or is there a way to check during installation, if the tables are set up correctly and if not apply these commands?

[mckillo]

During the installation scrips, check if JEM exist as JEM extension, if it's start the update process with upgrade tables. The issue is when JEM isn't installed and the tables exist, because the installation script start the installation process and when it wants to create the table, they exist and the tables aren't updated. I think that I can change the installation script to run your script to update the tables. So, we have covered the case of users with JEM uninstaller. Can we try this solution? I go on it

[mckillo]

I have tried to modify the pkg_jem\packages\com_jem.zip\com_jem\admin\sql\install.mysql.utf8.sql with:

SET @database_name = DATABASE();

SELECT COUNT(*) INTO @table_exists FROM information_schema.tables WHERE table_schema = @database_name AND table_name = `#__jem_events`;
IF @table_exists = 0 THEN
    CREATE TABLE `#__jem_events` (
        `id` int(11) unsigned NOT NULL auto_increment,
        `locid` int(11) unsigned NOT NULL DEFAULT '0',
...

        KEY `idx_language` (`language`)
    ) ENGINE=MyISAM CHARACTER SET `utf8` COLLATE `utf8_general_ci`;
END IF;
IF @table_exists > 0 THEN
    ALTER TABLE `#__jem_events` CHANGE `modified` `modified` datetime DEFAULT NULL;
    ALTER TABLE `#__jem_events` CHANGE `created` `created` datetime NOT NULL DEFAULT current_timestamp();
...
    ALTER TABLE `#__jem_events` ADD `reservedplaces`  INT NOT NULL DEFAULT '1' AFTER `maxbookeduser`;
END IF;
...

But the install process in Joomla, read one by one line to execute query and it give errors. Doesn't work. I'll try to do other invent.

[Wolfgang-Chess]

How about: ALTER TABLE `YOUR_PREFIX_jem_register` ADD `status` tinyint(3) NOT NULL DEFAULT 1 AFTER `waiting`; see https://www.joomlaeventmanager.net/forum/bugs/12404-solved-unknown-column-status-in-field-list

[mckillo]

This issue was detected, the error was that there was to add COLUMN after ADD. It can be closed.