can someone explain this change on #__content table state field https://github.com/joomla/joomla-cms/pull/20381/files#diff-135c6f58583408312a709459b19594c7R333

when in the update sql script we do this :

INSERT INTO `#__workflow_associations` (`item_id`, `state_id`, `extension`)
SELECT `id`, CASE WHEN `state` = -2 THEN 3 WHEN `state` = 0 THEN 1 WHEN `state` = 2 THEN 4 ELSE 2 END, 'com_content' FROM `#__content`;

ping @bembelimen @HLeithner

I think the reason is that the update script converts the old state values to the default workflow. but the new Workflow can have more states then tinyint, its converted.

I think the insert query copies the current states to the new workflow table to preserve the old behavior.

so then we lack the alter table in the update script only ?

Thats possible. I will check later.

Yes, that's true

OK I've merged the PR that fixes all the state stuff - so this needs conflicts fixing. Then let's get this tested and merged :)

please hold on ..... work in progress

ready for review/test

I have tested this item ✅ successfully on f9bd425

OK here with msqli

You may need to change to tinyint also in joomla.sql. We have now:

CREATE TABLE IF NOT EXISTS `#__content` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `asset_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'FK to the #__assets table.',
  `title` varchar(255) NOT NULL DEFAULT '',
  `alias` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `introtext` mediumtext NOT NULL,
  `fulltext` mediumtext NOT NULL,
`state` int(10) NOT NULL DEFAULT 0,

EDIT: just a second too late :)

state field of #__content table back to tinyint(3)/smallint

In the installation files and Postgres ones too please (sorry)

installation sql joomla.sql is already done.
for Postgres, do we need a smallint instead of bighint ?

Looks they are already done, @wilsonge

postgresql done too both install & update
yes for postgresql smallint is quite like the tinyint of mysql

Yup I’m happy with this. Can we test this please :)

I have already tested sqli here (yes, modified directly to tinyint in db)

Thanks guys!