Didn't I already do this ages ago

yes #26137 or #24020
but you have used sql script for menu table which is dangerous for nested set tables like #__menu

Drone failure seems not to be related to this PR.

@alikon As I can see, @brianteeman has closed his PR #26137, and PR #24020 is by @Hackwar .

No submenu shown using your branch and dev branch with Patchtester.

Commented the lines and still no go. Shouldn't it work with a new install of v4 without having to comment out these lines?

When installing the patch on v3.10:

The file marked for modification does not exist: administrator/components/com_finder/Helper/FinderHelper.php

forgive me i've completely forgot to provide the sql for the install ?
and told you wrong path..sorry

And what about Joomla.sql for PostgreSQL?

added

Add language strings.

just refresh the page string are there

See the other PR for the new language strings.

https://github.com/joomla/joomla-cms/pull/26137/files#diff-a37aa6c91545033de1ec16c00cf31733

@Quy is not matter of missing string.... visit another menu item and return back to the smart search menu item.... strings suddenly are translated

@alikon Sorry you are right. Thanks.

no problem and thanks for your patience

Actually, it is still an issue.
Go to Newsfeeds > Feed.
Click Smart Search. See strings not translated.

that's really weird... i confirm your findings .... but no clue

I don't know either, however, adding the new language strings fixes the issue.

you are right....

Menu strings have to be in the sys.ini as the other language file is only available to the component

How to get the code in com_admin/script.php to run?

i'm afraid you need to create a joomla pkg from this branch, and then update from 3.10

You can find an update package based on nightly build of tonight + this PR applied here:
https://test5.richard-fath.de/Joomla_4.0.0-beta1-dev-Development-Update_Package_2019-12-08_pr-27032.zip

To test the update, download the package and use the "Upload&Update" tab of the Joomla Update Component. You can test updating a 3.10 or a 3.9.13 or staging in this way, whatever you have.

@alikon Postgresql system test fails at installation. Not sure if related to this PR. Have to test here but this can take a while.

well i've tested/writed this pr on postgresql...... if you can tell me WTF that failure means i would be happy to fix it.....
....more than often there are this kind of failure... and OK i'm a bad coder i know.... but this happens even from not my pr's....so...

Maybe not related to your PR but some other recent change in 4.0-dev, don't know yet. Will check.

@alikon I've found it. Will provide fix in a PR to your branch in a few minutes, so you can merge with 1 click.

@alikon See alikon#62.

The update zip package I've linked above is still good because the last commit only changed postgresql/joomla.sql for new install.

@alikon See alikon#63.

Now there are conflicts in language files.

@alikon Now you have to rename the language files after PR #27130 has been merged.

I have tested this item ✅ successfully on d7c03e7

I've tested as follows:

Test 1: New installation

1. On a clean current 4.0-dev installation, applied the patch of this PR (e.g. with Patch Tester 3.0.0 Beta 4)
2. Removed configuration.php and deleted database tables.
3. Made a new install.
   Result: See section "Expected result" in the description of this PR.

Test 2: Update

1. Updated a clean staging using the "Upload&Update" tab of the Joomla Update Component and the patched update package based on last nightly build + this PR downloaded from here: https://test5.richard-fath.de/Joomla_4.0.0-beta1-dev-Development-Update_Package_2019-12-08_pr-27032.zip
   Result: See section "Expected result" in the description of this PR.

I've done both tests with MySQL 5.7 and PostgreSQL 10.10, but for the update test this was not really necessary.

Error reporting was set to "Development", no new PHP errors or warning or notices and nothing bad in the database server's log file added by this PR.

Upgraded from staging and 3.10. No menu items below Smart Search in the side menu.

PHP Warning: count(): Parameter must be an array or an object that implements Countable in \plugins\installer\override\override.php on line 134

PHP 7.3.9 MySQLi 10.4.6-MariaDB

The error about template is normal when updating to 4.

The warning about SQL error seems to be new, I did not have that when testing => maybe the result of a recent merge into 4.0-dev?

The warning aboutr override.php I had, too, but with and without the PR, and it did not do any harm.

@Quy What happens if you update to a "normal" nightly build, i.e. not my patched one, i.e. without this PR?

I've just tested again and can't replicate @Quy 's issue.

Same errors without your patched one.

Anything in the log file of the db server? On MySQL and Linux it would be "/var/log/mysql/error.log". No idea how it is with MariaDB.

It could be that the upgrade is incomplete for the following columns in menu table:

checked_out_time | datetime |   |   | No | 0000-00-00 00:00:00
publish_up and publish_down missing

Yes, that could be. Reason is maybe that you have some sql mode set which we don't support? As far as I remember from a recent posting by @HLeithner , it could be STRICT_ALL_TABLES, while we (Joomla) uses STRICT_TRANS_TABLES.

It could be solved by combining all alter table statements for all datetime columns into 1 single alter table statement, so when one columns is altered, there is no other column anymore having the invalid default value '0000 ...', which causes the alter table fail in case of strict mode. But the database schema checker/fixer does not support that and so would show errors after a new install where no errors are, and it would fail trying to fix that. To change the db checker/fixer to support that is one of the things I wanna do when I have much time, but it is not trivial. That's why I think I can't do this before my xmas holiday (if ever).

The menu table is the first one which will be altered by a 4.0 update sql, and that's why this table fails, because it comes first.

How you can test that:

First run following SQL script in PhpMyAdmin, having replaces #__ by your db prefix:

--
-- Switch off STRICT_TABLES by setting some SQL_MODE without STRICT_TABLES,
-- otherwise you get following SQL error:
-- #1067 - Invalid default value for 'test_col_1'
--

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

--
-- Set timezone to UTC so CURRENT_TIMESTAMP is stored as UTC
--
SET time_zone = "+00:00";

--
-- Table structure for table `#__test_table_1`
--

CREATE TABLE IF NOT EXISTS `#__test_table_1` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `test_col_1` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `test_col_2` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `test_col_3` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `test_col_4` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `idx_test_col_1` (`test_col_1`),
  KEY `idx_test_col_2` (`test_col_2`),
  KEY `idx_test_col_3` (`test_col_3`),
  KEY `idx_test_col_4` (`test_col_4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `#__test_table_1`
--

INSERT INTO `#__test_table_1` (`id`, `test_col_1`, `test_col_2`, `test_col_3`, `test_col_4`) VALUES
(1, '0000-00-00 00:00:00', '0000-00-00 00:00:00', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

INSERT INTO `#__test_table_1` (`id`, `test_col_1`, `test_col_2`, `test_col_3`, `test_col_4`) VALUES
(2, '2019-01-01 00:00:00', '0000-00-00 00:00:00', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

INSERT INTO `#__test_table_1` (`id`, `test_col_1`, `test_col_2`, `test_col_3`, `test_col_4`) VALUES
(3, '0000-00-00 00:00:00', '2019-01-01 00:00:00', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

Then i a new database session, i.e. new instance of PhpMyAdmin, run the following, also with the right db prefix:

ALTER TABLE `#__test_table_1` MODIFY `test_col_1` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `#__test_table_1` MODIFY `test_col_2` datetime NOT NULL DEFAULT '1000-01-01 00:00:00';
ALTER TABLE `#__test_table_1` MODIFY `test_col_3` datetime NULL DEFAULT NULL;
ALTER TABLE `#__test_table_1` MODIFY `test_col_4` datetime NULL DEFAULT NULL;

This will fail with error invalid default value for datetime column so-and-so (I think it will be test_col_2).

Then run following, also with correct db prefix:

ALTER TABLE `#__test_table_1`
	MODIFY `test_col_1` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	MODIFY `test_col_2` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
	MODIFY `test_col_3` datetime NULL DEFAULT NULL,
	MODIFY `test_col_4` datetime NULL DEFAULT NULL;

You will see that the latter works because when the table is altered there will not be any column anymore with invalid default value after the statement has run.

JInstaller: :Install: Error SQL Incorrect datetime value: '' for column 'core_checked_out_time' at row 1

core_checked_out_time | varchar(255) | utf8mb4_unicode_ci |   | No | 0000-00-00 00:00:00

Upgraded on CloudAccess. Does it matter that the column is varchar and not datetime initially?

In the following sql, the 4 datetime columns prior to core_checked_out_time updated but failed at core_checked_out_time.

\administrator\components\com_admin\sql\updates\mysql\4.0.0-2018-08-01.sql

ALTER TABLE `#__ucm_content` MODIFY `core_created_time` datetime NOT NULL;
ALTER TABLE `#__ucm_content` MODIFY `core_modified_time` datetime NOT NULL;

ALTER TABLE `#__ucm_content` MODIFY `core_publish_up` datetime NULL DEFAULT NULL;
ALTER TABLE `#__ucm_content` MODIFY `core_publish_down` datetime NULL DEFAULT NULL;
ALTER TABLE `#__ucm_content` MODIFY `core_checked_out_time` datetime NULL DEFAULT NULL;

ALTER TABLE `#__ucm_history` MODIFY `save_date` datetime NOT NULL;

Does it matter that the column is varchar and not datetime initially?

Seems so, but I don't see yet why, and I can't reproduce the problem here with a local MySQL 5.7 installation on Linux.

CloudAccess system information:
Linux lamp129.cloudaccess.net 3.10.0-962.3.2.lve1.5.24.5.el6h.x86_64
5.7.23-cll-lve

@Quy Please execute following 2 SQL statements in PhpMyAdmin or whatever tool and post the result of each of the statements here in some text quote block, if not too big, or send it to me on glip:

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

I'll check on weekend and if I find some difference to my settings, and if so, I'll apply them on my DB and see if I can replicate the issue.

@Quy P.S.: Maybe we should continue this discussion somewhere else because it is not really related to this PR here but to the database update in general?

OK. Lets do in Glip.

Updated the update package: https://test5.richard-fath.de/Joomla_4.0.0-beta1-dev-Development-Update_Package_2019-12-08_pr-27032.zip.

Issues with that update package linked above and e.g. untranslated texts in backend are not related to this PR, they happen also with the unmodified update package from tonight's nightly build.

Upgraded successfully with no sample data, but failed with sample data.

@Quy Which kind of sample data? Blog? Testing? Multilingual? Btw. after update there might be problems with language files not being deleted because deleted files list in script.php is not up to date, but that's another thing not related to this PR, just wanted to mention.

Installed Test English.

@Quy I see. Will test later, too.

@Quy Confirmed. But not related to this PR. Will you make a new issue? Unfortunately I see nothing in mysql or php log about the reason, but I can investigate and try to find a fix now as I can reprocude it.

@Quy I think I have a solution. Will test and make PR.

@Quy PR #27228 is almost ready. I only have to provide testing instructions and a modified update package. The problem is that I have other, unrelated problems with language stings with update packages from current nightly build, which makes testing not nice.

@Quy PR #27228 is ready, please test. And if it works, maybe you can modify the update zip containter I provided in some comment and my test result above by that change and give this PR here a good test, too?

I have tested this item ✅ successfully on d7c03e7

RTC

@wilsonge can we merge this? looks correct for me

Thanks!