Error applying User Permission

[woodhome]

Just upgraded to make use of the new user permission feature, I can create a new permission just fine, but trying to assign a user to the newly created permission results in an error message every time: "Items on row 1 must be both unique and not blank (User Permission Items)".

I'd only created one permission, and only tried to assign it to one user. The search found it just fine, so the line is not blank.
I'm sure it's something minor but would sure like this feature to work. It solves a big problem of mine.

This error is appearing in the log:
[0] : ===PDO MESSAGE===

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'mysite_data.sys_zzzzsys_permission_item' doesn't exist

===SQL===========

REPLACE INTO zzzzsys_permission_item SELECT * FROM sys_zzzzsys_permission_item

===BACK TRACE====

/home1/mysite/data.mysite.org/core/nusystemupdatelibs.php - line 368 (nuRunQuery)

/home1/mysite/data.mysite.org/core/nusystemupdate.php - line 103 (nuAppendToSystemTables)

/home1/mysite/data.mysite.org/core/nusystemupdate.php - line 23 (nuRunUpdate)

UPDATE WITH SOLUTION: For anyone running across this problem in the future, kev1n solved the problem. It was the validation setting on the upe_ident object of the edit form. It was set to something other than 'None' and for some reason, perhaps PHP version related, was not being correctly changed by the update script.

[kev1n]

Hi,

Running this SQL should solve the issue "Items on row 1 must be both unique and not blank (User Permission Items)".
If there is still an issue, try the latest from Github (not the release)

Code: Select all

REPLACE INTO `zzzzsys_object` (`zzzzsys_object_id`, `sob_all_zzzzsys_form_id`, `sob_all_table`, `sob_all_type`, `sob_all_id`, `sob_all_label`, `sob_all_zzzzsys_tab_id`, `sob_all_order`, `sob_all_top`, `sob_all_left`, `sob_all_width`, `sob_all_height`, `sob_all_cloneable`, `sob_all_align`, `sob_all_validate`, `sob_all_access`, `sob_all_access_condition`, `sob_all_event`, `sob_all_style_type`, `sob_all_style`, `sob_calc_formula`, `sob_calc_format`, `sob_run_zzzzsys_form_id`, `sob_run_filter`, `sob_run_method`, `sob_run_target`, `sob_run_type`, `sob_run_id`, `sob_display_sql`, `sob_select_multiple`, `sob_select_2`, `sob_select_sql`, `sob_lookup_code`, `sob_lookup_description`, `sob_lookup_description_width`, `sob_lookup_autocomplete`, `sob_lookup_zzzzsys_form_id`, `sob_lookup_javascript`, `sob_lookup_php`, `sob_lookup_table`, `sob_subform_zzzzsys_form_id`, `sob_subform_foreign_key`, `sob_subform_add`, `sob_subform_delete`, `sob_subform_type`, `sob_subform_table`, `sob_input_count`, `sob_input_format`, `sob_input_type`, `sob_input_icon`, `sob_input_javascript`, `sob_input_datalist`, `sob_input_attribute`, `sob_input_file_target`, `sob_html_code`, `sob_html_chart_type`, `sob_html_javascript`, `sob_html_title`, `sob_html_vertical_label`, `sob_html_horizontal_label`, `sob_image_zzzzsys_file_id`, `sob_all_json`, `sob_json`) VALUES ('nu65d7db18a6e2bab', 'nuuser', 'zzzzsys_user', 'subform', 'sf_permission', ' ', 'nu65d7dbb966b5d37', '230', '20', '20', '616', '515', '1', 'left', '0', '0', '', '0', '', '', '', '', '', '', '', '', '', '', '', '0', '0', '', '', '', '', NULL, '', '', NULL, '', 'nuuserpermission', 'upe_zzzzsys_user_id', '1', '1', 'g', 'zzzzsys_user_permission', '0', '', '', '', '', '', NULL, '0', '', '', '', '', '', '', '', '{\"type\":{\"version\":\"1.0\",\"mobile\":{\"version\":\"1.0\",\"name\":\"test1\",\"visible\":true,\"location\":{\"top\":\"295\",\"left\":\"52\"},\"size\":{\"width\":\"250\",\"height\":\"25\"},\"class\":[\"nuMobileInput\"]}}}', NULL)

[woodhome]

Neither the SQL statement, nor upgrading to the 3-14-2024 version has resolved the error. When I try to assign an existing permission to a user the form finds the permission as expected, but on Save shows this error: "Items on row 1 must be both unique and not blank (User Permission Items)"

HOWEVER the error written to the log has changed so that's nice:
[0] : ===PDO MESSAGE===

SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes.

===SQL===========

ALTER TABLE zzzzsys_report ENGINE = InnoDB

===BACK TRACE====

/home1/myuser/data.mysitename.org/core/nusystemupdatelibs.php - line 448 (nuRunQuery)

/home1/myuser/data.mysitename.org/core/nusystemupdate.php - line 129 (nuSetCollation)

/home1/myuser/data.mysitename.org/core/nusystemupdate.php - line 23 (nuRunUpdate)

[kev1n]

Are you using the latest from Github?

https://github.com/nuBuilder/nuBuilder- ... master.zip

Also make sure to run the database update.

[woodhome]

Sorry it took a while to test this, I had to wait till the system wasn't being used. I'm still getting the error, I updated to the version posted March 17th. The update produced a few errors in the SQL log

*****FIRST ERROR*****************.

[0] : ===PDO MESSAGE===

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'information_schema.tables.TABLE_NAME' in 'field list'

===SQL===========

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `zzzzsys_object_list` AS SELECT `information_schema`.`tables`.`TABLE_NAME` AS `zzzzsys_object_list_id` FROM `information_schema`.`TABLES` WHERE (`information_schema`.`tables`.`TABLE_SCHEMA` = database())

===BACK TRACE====

/home1/mysite/data.mysitename.org/core/nusystemupdatelibs.php - line 63 (nuRunQuery)

/home1/mysite/data.mysitename.org/core/nusystemupdate.php - line 83 (nuImportSystemFiles)

/home1/mysite/data.mysitename.org/core/nusystemupdate.php - line 23 (nuRunUpdate)

****SECOND ERRROR*****************.
[0] : ===PDO MESSAGE===

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'mysite_data.zzzzsys_object_list' doesn't exist

===SQL===========

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `zzzzsys_report_data` AS SELECT concat('PROCEDURE:',`zzzzsys_php`.`zzzzsys_php_id`) AS `id`, `zzzzsys_php`.`sph_code` AS `code`, `zzzzsys_php`.`sph_description` AS `description` FROM `zzzzsys_php` WHERE ((`zzzzsys_php`.`sph_system` <> '1') AND (locate('#TABLE_ID#',`zzzzsys_php`.`sph_php`) > '0')) union select concat('SQL:',`zzzzsys_select`.`zzzzsys_select_id`) AS `id`,'nuSQL' AS `code`,`zzzzsys_select`.`sse_description` AS `description` from `zzzzsys_select` where ((`zzzzsys_select`.`sse_system` is null) or (`zzzzsys_select`.`sse_system` = '')) union select concat('TABLE:',`zzzzsys_object_list`.`zzzzsys_object_list_id`) AS `id`,'nuTABLE' AS `code`,`zzzzsys_object_list`.`zzzzsys_object_list_id` AS `description` from `zzzzsys_object_list`

===BACK TRACE====

/home1/mysite/data.mysitename.org/core/nusystemupdatelibs.php - line 63 (nuRunQuery)

/home1/mysite/data.mysitename.org/core/nusystemupdate.php - line 83 (nuImportSystemFiles)

/home1/mysite/data.mysitename.org/core/nusystemupdate.php - line 23 (nuRunUpdate)

*******THIRD ERROR***************

[0] : ===PDO MESSAGE===

SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes.

===SQL===========

ALTER TABLE zzzzsys_report ENGINE = InnoDB

===BACK TRACE====

/home1/mysite/data.mysitename.org/core/nusystemupdatelibs.php - line 448 (nuRunQuery)

/home1/mysite/data.mysitename.org/core/nusystemupdate.php - line 129 (nuSetCollation)

/home1/mysite/data.mysitename.org/core/nusystemupdate.php - line 23 (nuRunUpdate)

[kev1n]

The error is not related to your other problem. Is it still there?

As for the other error, if the view zzzzsys_object_list doesn't actually exist, could you run this query directly in phpMyAdmin and see if there's an error?

Code: Select all

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `zzzzsys_object_list` AS 
    SELECT information_schema.tables.TABLE_NAME AS `zzzzsys_object_list_id` 
    FROM information_schema.TABLES 
    WHERE (information_schema.tables.TABLE_SCHEMA = database());

[woodhome]

It returned this error:
#1054 - Unknown column 'information_schema.tables.TABLE_NAME' in 'field list'

Quick Correction. I noticed 'tables' was uncapitalized in the original query which was causing the error. When I run

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `zzzzsys_object_list` AS
SELECT information_schema.TABLES.TABLE_NAME AS `zzzzsys_object_list_id`
FROM information_schema.TABLES
WHERE (information_schema.TABLES.TABLE_SCHEMA = database());

I get:#1044 - Access denied for user 'mysite_data'@'localhost' to database 'information_schema'

Is there a new permission needed?

[kev1n]

Could you run the update again using the latest nubuilder4.sql from Github?

[woodhome]

Hi Kev1n, I ran the update again using the version:
nuBuilder Forte 4.5

DB Version: V.4.5-2024.03.20.0
Files Version: V.4.5-2024.03.20.13
(V.MajorVersion-CurrentDate.BuildNumber)

The bug report generated a very large number of errors, mostly "Duplicate Column name" errors. This is the first

[0] : ===PDO MESSAGE===
SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'deb_flag'
===SQL===========
ALTER TABLE `zzzzsys_debug` ADD `deb_flag` VARCHAR(50) NULL DEFAULT NULL AFTER `deb_message`;

There were 48 errors like this one, then these two,

[0] : ===PDO MESSAGE===
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'mysite_data.pdf_temp' doesn't exist
===SQL===========
ALTER TABLE `pdf_temp` ADD `pdf_code` VARCHAR(100) NULL DEFAULT NULL AFTER `pdf_added_by`;

[0] : ===PDO MESSAGE===
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'mysite_data.pdf_temp' doesn't exist
===SQL===========
ALTER TABLE `pdf_temp` ADD `pdf_tag` VARCHAR(100) NULL DEFAULT NULL AFTER `pdf_code`;

Then this:
[0] : ===PDO MESSAGE===
SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes.
===SQL===========
ALTER TABLE zzzzsys_report ENGINE = InnoDB

and of course still getting the error when trying to set permission. The site seems to be working OK, but I'm a bit concerned that perhaps I should restore the backup.

[kev1n]

You can ignore the errors, they are not suppressed in the current Github commit.

To be able to reproduce the permission issue, please create a dump with the Cloner as shown in the screenshot and upload the output as a .txt attachment here.

2024-03-20_081115.png