Just to be clear - you are saying that if I install osmap 4.2.0 and then upgrade to 4.3.2 the database structure will be reported as out of date when it is not

Exactly.

Thanks for clarifying. Unfortunately I can't test it to be certain because osmap is not installable on joomla 4

2e.pdf2a.pdf2b.pdf2d.pdf2c.pdf

No false positives, like the Joomla 3 previous behavior. Before upgrading to Joomla 4, there wasn't any error.

@anibalsanchez In Joomla 3 the database checker did only check the CMS core but not 3rd party extensions. So if there is an error with the check for extensions, it concerns a new feature of J4.

I'll check later if I can reproduce that.

I use to check that the database is OK before and after upgrading to Joomla 4. The issues are not serious but it is confusing to find differences before and after upgrading.

I think I know what the problem is but as you cant install osmap on joomla 4 i cant test to confirm

@brianteeman I am subscribed in their customer database so they have sent me an email with a link to download a release candidate and asked me for testing it. I don't know if I'm allowed to post that link here, but I am sure if you email to "steve [at] joomlashack.com" they will send you one, too. Otherwise I will check tomorrow because today I'm busy with JoomlaDay Germany-Austria-Switzerland Online. I also have an idea what the problem could be and where to look for, but if you are faster then go.

I've just emailed them if I can post the download link here.

I have faked a test with the Weblinks extension. I deleted the file /administrator/components/com_weblinks/sql/updates/mysql/4.0.0.sql in the ZIP file to produce the extension v4.0.0 and the db schema version 3.5.1. The file is attached.

pkg-weblinks-4.0.0-fake.zip

Before the J4 upgrade on Joomla 3.10.2:

After the J4 upgrade on Joomla 4.0.3:

The screenshot shows two cases "No issues" and a new issue "One Problem".

If you hover over the badge which shows there is a problem, you get the details:

It seems they have some table in their update SQL script which in fact does not exist.

I will check details later and report back.

@anibalsanchez Your first screenshot shows there is a problem in J3, too, but you are on the tab with the other information so we can't see which problem was found.

The problem with OSMap is caused by their update SQL script 4.2.0.sql creating a table and later dropping it, i.e. the table is only used temporarily.

This is something which the database checker never did handle right. The database handler will only check the create table but not the drop table, and so it claims the table to be missing.

I think they will have to change their update SQL because I can't fix that in the database checker. If I make it check the drop table, too, it will end in a loop with error "table should be there but is missing" and error "table is there but should have been dropped", like it would already be with indexes.

I will report it to them (OSMap) later.

@richard67 Yes, but it seems that it is unrelated. Check the screenshot. After the upgrade, it is not shown in the list.

To sum up:

• New "No problems"
• New issue, OSMap case

@anibalsanchez Yes because with the update to J4, the finder tables are reorganized anyway, so this error disappears.

As I have explained above, the "new issue" comes from the fact that in Joomla 3, the database checker did only check the CMS core. In Joomla 4 it also checks 3rd party extensions, so that's of course new and different to Joomla 3.

P.S. It was not my idea to extend the database checker for 3rd party extensions. I would not have done this.

@richard67 Is "No problems" the expected output for Joomla or Weblinks?

Is "No problems" the expected output for Joomla or Weblinks?

@anibalsanchez Yes.

Ok, Case Closed.

@anibalsanchez But thanks for reporting. It pointed me to the issue with the OSMap update SQL. Not sure how I shall explain them now that not all you can do in SQL works with the database checker and that the fact that the checker checks their extensions in J4, too, is documented nowhere and not mentioned in any of our release announcements 😞 It was a bad idea to do that in my opinion.

This is the problematic part in their update SQL:

CREATE TABLE IF NOT EXISTS `#__osmap_sitemap_menus_new` (
...
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `#__osmap_sitemap_menus_new` SELECT * FROM `#__osmap_sitemap_menus`;

DROP TABLE `#__osmap_sitemap_menus`;

RENAME TABLE `#__osmap_sitemap_menus_new` TO `#__osmap_sitemap_menus`;

Pretty valid SQL, but the database checker can't handle that.

I think they can solve it by using proper alter table statements instead of creating a new one and selection data into that and then rename and so on.

I'll check and if I can provide a fix for them.

I've reported it to OSMap.

When comparing their tables in 4.0.0.sql and 4.2.0.sql, I see no difference beside the indexes.

4.0.0.sql:

CREATE TABLE IF NOT EXISTS `#__osmap_sitemap_menus` (
  `sitemap_id` INT(11) UNSIGNED NOT NULL,
  `menutype_id` INT(11) NOT NULL,
  `changefreq` ENUM('always','hourly','daily','weekly','monthly','yearly','never') NOT NULL DEFAULT 'weekly',
  `priority` FLOAT NOT NULL DEFAULT '0.5',
  `ordering` INT(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`sitemap_id`, `menutype_id`),
  INDEX `fk_osmap_sitemap_menus_osmap_sitemaps_idx` (`sitemap_id` ASC),
  INDEX `ordering` (`sitemap_id` ASC, `ordering` ASC)
)
ENGINE=INNODB DEFAULT CHARSET=utf8;

4.2.0.sql:

CREATE TABLE IF NOT EXISTS `#__osmap_sitemap_menus_new` (
  `sitemap_id` int(11) unsigned NOT NULL,
  `menutype_id` int(11) NOT NULL,
  `changefreq` enum('always','hourly','daily','weekly','monthly','yearly','never') NOT NULL DEFAULT 'weekly',
  `priority` float NOT NULL DEFAULT '0.5',
  `ordering` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`sitemap_id`,`menutype_id`),
  KEY `idx_ordering` (`sitemap_id`,`ordering`),
  KEY `idx_sitemap_menus` (`sitemap_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

So the solution to make their 4.2.0.sql ok for the database checker would be to replace

-- ============================================================================
-- Recreate the table to rename the foreign key and constraint.
-- A long name can cause issues on some servers when it get trimmed and force
-- duplicate names
DROP TABLE IF EXISTS `#__osmap_sitemap_menus_new`;

CREATE TABLE IF NOT EXISTS `#__osmap_sitemap_menus_new` (
  `sitemap_id` int(11) unsigned NOT NULL,
  `menutype_id` int(11) NOT NULL,
  `changefreq` enum('always','hourly','daily','weekly','monthly','yearly','never') NOT NULL DEFAULT 'weekly',
  `priority` float NOT NULL DEFAULT '0.5',
  `ordering` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`sitemap_id`,`menutype_id`),
  KEY `idx_ordering` (`sitemap_id`,`ordering`),
  KEY `idx_sitemap_menus` (`sitemap_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `#__osmap_sitemap_menus_new` SELECT * FROM `#__osmap_sitemap_menus`;

DROP TABLE `#__osmap_sitemap_menus`;

RENAME TABLE `#__osmap_sitemap_menus_new` TO `#__osmap_sitemap_menus`;

by

ALTER TABLE `#__osmap_sitemap_menus` DROP INDEX `fk_osmap_sitemap_menus_osmap_sitemaps_idx`;
ALTER TABLE `#__osmap_sitemap_menus` DROP INDEX `ordering`;
ALTER TABLE `#__osmap_sitemap_menus` ADD INDEX `idx_ordering` (`sitemap_id`,`ordering`);
ALTER TABLE `#__osmap_sitemap_menus` ADD INDEX `idx_sitemap_menus` (`sitemap_id`);

If the "DROP INDEX" statement for the "fk_osmap_sitemap_menus_osmap_sitemaps_idx" index fails due to the long name, which might have been the reason for the "trick" with that new table, then remove that one statement from the 4.0.0.sql and add code to the scriptfile "script.installer.php" to remove that index.

The code could check if some index exists with a name like 'fk_osmap_sitemap_menus%' and if so drop that index.

I will suggest that to them if they reply my email. But they have off on weekends.