[4] JInstaller: :Install: Error SQL Field 'xxxxx' doesn't have a default value

[garkell]

Steps to reproduce the issue

New install of nightly build.
Try to install 3rd party component (my own small sample)

Expected result

Complete install or an error message relating to 3rd party component.

Actual result

JInstaller: :Install: Error SQL Field 'rules' doesn't have a default value
JInstaller: :Install: Error SQL Field 'field_mappings' doesn't have a default value

System information (as much as possible)

Nightly build of August 14, 2020
Apache/2.4.23 (Win64) PHP/7.2.13
5.7.14 - MySQL

Additional comments

JInstaller: :Install: Error SQL Field 'rules' doesn't have a default value
After altering all core tables that had the field referred to (these fields aren't part of 3rd party component) ie...
ALTER TABLE jdqp0_assets CHANGE rules rules VARCHAR(5120) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT 'JSON encoded access control.';
the next message came up
JInstaller: :Install: Error SQL Field 'field_mappings' doesn't have a default value

[richard67]

@garkell Could you provide a download link for your small sample component which failed to be installed?

[garkell]

Hi Richard,
I altered the field_mappings fiels also and the install went through but gave the following warning.
JInstaller: :Install: Can't find Joomla XML setup file.
JInstaller: :Install: Can't find XML setup file.

but then provided me with a successful install message
Installation of the component was successful.

https://www.glennarkell.com.au/joomlaorg/components/com_gavoting-0.1.01.zip

[garkell]

There are some problems with the component which I need to fix, just discovered since I've been able to install so don't be too concerned about the use of the component. Cheers.
Glenn

[alikon]

so i'm going to close this issue for now, feel free to open a new one when you think that you have discovered some bugs from core
thank you

[garkell]

Hi Nicola,
So if there is nothing wrong with the core why is it giving an error message relating to core fields?

[alikon]

good question
i have to admit i'vent looked at your component, yet, but as example the com_patchtester4 is workning/installed without any issues
but as i've already written feel free to open a new issue if you feel like that core is performing wrong
or if you prefer simply reopen this one

[richard67]

@garkell I'm just checking your install SQL and will report back what I find. You are inserting into the content types table, and that has been changed between J3 and J4. You may need to specify values for additional columns. I am just checking how we do it with our core extensions which we add in J4 on updates. Stay tuned.

[richard67]

@garkell In your component's install.mysql.utf8.sql file, you have two INSERT statements for the #__content_types table at the bottom.

In the first statement are missing columns rules and field_mappings, and values (empty string '') for these.

In the second statement only column rules and its values are missing, also here empty string for the value.

Here the corrected SQL statements:

INSERT INTO `#__content_types` (`type_title`, `type_alias`, `table`, `rules`, `field_mappings`, `content_history_options`)
SELECT * FROM ( SELECT 'Position','com_gavoting','{"special":{"dbtable":"#__gavoting_positions","key":"id","type":"Position","prefix":"Election Voting Management SystemTable"}}', '', '', '{"formFile":"administrator\/components\/com_gavoting\/models\/forms\/position.xml", "hideFields":["checked_out","checked_out_time","params","language" ,"comment"], "ignoreChanges":["modified_by", "modified", "checked_out", "checked_out_time"], "convertToInt":["publish_up", "publish_down"], "displayLookup":[{"sourceColumn":"catid","targetTable":"#__categories","targetColumn":"id","displayColumn":"title"},{"sourceColumn":"group_id","targetTable":"#__usergroups","targetColumn":"id","displayColumn":"title"},{"sourceColumn":"created_by","targetTable":"#__users","targetColumn":"id","displayColumn":"name"},{"sourceColumn":"access","targetTable":"#__viewlevels","targetColumn":"id","displayColumn":"title"},{"sourceColumn":"modified_by","targetTable":"#__users","targetColumn":"id","displayColumn":"name"}]}') AS tmp
WHERE NOT EXISTS (
	SELECT type_alias FROM `#__content_types` WHERE (`type_alias` = 'com_gavoting')
) LIMIT 1;

INSERT INTO `#__content_types` (`type_title`, `type_alias`, `table`, `rules`, `field_mappings`, `router`, `content_history_options`)
SELECT * FROM ( SELECT 'Election Year','com_gavoting.category','{"special":{"dbtable":"#__categories","key":"id","type":"Category","prefix":"JTable","config":"array()"},"common":   {"dbtable":"#__ucm_content","key":"ucm_id","type":"Corecontent","prefix":"JTable","config":"array()"}}', '', '{"common":{"core_content_item_id":"id","core_title":"title","core_state":"published","core_alias":"alias","core_created_time":"created_time","core_modified_time":"modified_time","core_body":"description", "core_hits":"hits","core_publish_up":"null","core_publish_down":"null","core_access":"access", "core_params":"params", "core_featured":"null", "core_metadata":"metadata", "core_language":"language", "core_images":"null", "core_urls":"null", "core_version":"version", "core_ordering":"null", "core_metakey":"metakey", "core_metadesc":"metadesc", "core_catid":"parent_id", "core_xreference":"null", "asset_id":"asset_id"}, "special":{"parent_id":"parent_id","lft":"lft","rgt":"rgt","level":"level","path":"path","extension":"extension","note":"note"}}', 'Election Voting Management SystemRouter::getCategoryRoute', '{"formFile":"administrator\/components\/com_categories\/models\/forms\/category.xml", "hideFields":["asset_id","checked_out","checked_out_time","version","lft","rgt","level","path","extension"], "ignoreChanges":["modified_user_id", "modified_time", "checked_out", "checked_out_time", "version", "hits", "path"],"convertToInt":["publish_up", "publish_down"], "displayLookup":[{"sourceColumn":"created_user_id","targetTable":"#__users","targetColumn":"id","displayColumn":"name"},{"sourceColumn":"access","targetTable":"#__viewlevels","targetColumn":"id","displayColumn":"title"},{"sourceColumn":"modified_user_id","targetTable":"#__users","targetColumn":"id","displayColumn":"name"},{"sourceColumn":"parent_id","targetTable":"#__categories","targetColumn":"id","displayColumn":"title"}]}') AS tmp WHERE NOT EXISTS (
	SELECT type_alias FROM `#__content_types` WHERE (`type_alias` = 'com_gavoting.category')
) LIMIT 1;

[garkell]

Ahhh brilliant, thank you very much Richard. I'll do a complete re-install of the latest nightly build and make those changes to my component for a clean test. Thank you again. Cheers.
Glenn

[richard67]

Thanks for helping us with testing J4.

[garkell]

@richard67 Thank you for your encouragement. I was still getting a warning message " duplicate column name '' " so I removed the offending 2 inserts (as above) and the install was successful. Unfortunately the column name was blank so it didn't give me any clue what it was. I know this is closed but just in case this latest update helps in any way. Cheers.
Glenn

[richard67]

@garkell Well these 2 insert statements have a fixed column list for the insert, but for the subquery a "SELECT *" is used, that's the problem, I think. You have to specifiy the columns in the subquery to be safe. But that's an issue of the extension, not of the Joomla core. So it's fine that this issue here remains closed.

[garkell]

Ahhh thank you for the explanation, this just shows up my limited understanding. Thank you again for taking the time.
Glenn
P.S. I'm in the process of re-writing the whole in the standard J4 format. Big learning curve.