#518 - Database Decimal Data Length not working properly - dropping D of M,D length setting - Component-Builder

cpaschen commented

2020-01-27 20:58:40 +00:00

(Migrated from github.com)

Steps to reproduce the issue

Create a field that stores it's data as DECIMAL
Set Data Length to : Other
in Other Length enter: 3,1
(or any #,# value which should be acceptable as a decimal length for mysql)
Save the field

Expected result

Length should be set to 3,1
and Component Updates should show:

ALTER TABLE #__kr_books_reviews_assignment ADD rating_overall rating_overall DECIMAL(3,1) NULL DEFAULT 0 AFTER rating_overall_display;

Actual result

Length (in the field GUI view) is set to: 3
and component updates shows:

ALTER TABLE #__kr_books_reviews_assignment ADD rating_overall rating_overall DECIMAL(3) NULL DEFAULT 0 AFTER rating_overall_display;

System information (as much as possible)

OS Name & Version: Bitnami LAMP Stack on Windows 10
MySql Version: 8.0.18
Apache Version:
PHP Version: 7.3.13
Joomla Version: 3.9.14
JCB Version: 2.10.9
Browser: Firefox

Additional comments

This was working properly at some point before JCB 2.10.9
Also, if you have a field that was originally created with "3,1" and you edit that field and save it then recompile, it creates a component update to change all the updated fields to "3" (dropping the decimal portion).

### Steps to reproduce the issue Create a field that stores it's data as DECIMAL Set Data Length to : Other in Other Length enter: 3,1 (or any #,# value which should be acceptable as a decimal length for mysql) Save the field ### Expected result Length should be set to 3,1 and Component Updates should show: ALTER TABLE `#__kr_books_reviews_assignment` ADD `rating_overall` `rating_overall` DECIMAL(3,1) NULL DEFAULT 0 AFTER `rating_overall_display`; ### Actual result Length (in the field GUI view) is set to: 3 and component updates shows: ALTER TABLE `#__kr_books_reviews_assignment` ADD `rating_overall` `rating_overall` DECIMAL(3) NULL DEFAULT 0 AFTER `rating_overall_display`; ### System information (as much as possible) - OS Name & Version: Bitnami LAMP Stack on Windows 10 - MySql Version: 8.0.18 - Apache Version: - PHP Version: 7.3.13 - Joomla Version: 3.9.14 - JCB Version: 2.10.9 - Browser: Firefox ### Additional comments This was working properly at some point before JCB 2.10.9 Also, if you have a field that was originally created with "3,1" and you edit that field and save it then recompile, it creates a component update to change all the updated fields to "3" (dropping the decimal portion).

Llewellyn commented

2020-01-29 03:57:32 +00:00

Does decimal not get saved as 3.1 instead of 3,1 in the database?

I know the other field is expecting an integer, so I think this is part of the issue.Yet I did not know you could define data length with a decimal, I will need to look that up.

But my first question is still my greater concern.

Lastly I would deal with this kind of value management with a field validation rule instead of with the database itself. Since the database will only give an error, where the field validation rule can work well with a message that is also set in the field directing the user to provide the correct input.

Does decimal not get saved as **3.1** instead of **3,1** in the database? I know the other field is [expecting an integer](https://github.com/vdm-io/Joomla-Component-Builder/blob/staging/admin/models/forms/field.xml#L401), so I think this is part of the issue.Yet I did not know you could define data length with a decimal, I will need to look that up. But my first question is still my greater concern. Lastly I would deal with this kind of value management with a [field validation rule](https://youtu.be/Z6-ggKtX35o?list=PLQRGFI8XZ_wtGvPQZWBfDzzlERLQgpMRE) instead of with the database itself. Since the database will only give an error, where the field validation rule can work well with a message that is also set in the field directing the user to provide the correct input.

cpaschen commented

2020-01-29 04:11:18 +00:00

(Migrated from github.com)

No. The proper length syntax in the SQL is "3,1" (three-comma-one).

Again, the issue is not with the database or anything else. It's just when you are in the field manager and have a DECIMAL and set the length to "3,1" (anything with any decimal notation - i.e. 'comma-number') and press save to save the field, it strips the ",1".
The problem is in the JCB GUI, it should allow the ,1 notation.

BTW ... this was working properly prior to 2.10.9 I think (or maybe one or two releases before that. So something got changed.

No. The proper length syntax in the SQL is "3,1" (three-comma-one). Again, the issue is not with the database or anything else. It's just when you are in the field manager and have a DECIMAL and set the length to "3,1" (anything with any decimal notation - i.e. 'comma-number') and press save to save the field, it strips the ",1". The problem is in the JCB GUI, it should allow the ,1 notation. BTW ... this was working properly prior to 2.10.9 I think (or maybe one or two releases before that. So something got changed.

Llewellyn commented

2020-02-05 11:55:29 +00:00

BTW ... this was working properly prior to 2.10.9 I think (or maybe one or two releases before that. So something got changed.

This change is not due to JCB as far as I know. If it worked before and now does not, it must be a change made in Joomla. I will see how we can help restore/fix this issue.

> BTW ... this was working properly prior to 2.10.9 I think (or maybe one or two releases before that. So something got changed. This change is not due to JCB as far as I know. If it worked before and now does not, it must be a change made in Joomla. I will see how we can help restore/fix this issue.

cpaschen commented

2020-02-06 16:25:56 +00:00

(Migrated from github.com)

Strange. I haven't updated Joomla on this site (still on 3.9.14) and I'm pretty sure that it was working prior to upgrading to 3.9.14.
I'll see if I can find any of my dev sites running 3.9.13 and test it there.
I'll also do some more testing on 3.9.15 just in case it was in Joomla and fixed in the latest release.
I'll post back here shortly.

Strange. I haven't updated Joomla on this site (still on 3.9.14) and I'm pretty sure that it was working prior to upgrading to 3.9.14. I'll see if I can find any of my dev sites running 3.9.13 and test it there. I'll also do some more testing on 3.9.15 just in case it was in Joomla and fixed in the latest release. I'll post back here shortly.

cpaschen commented

2020-02-06 16:26:53 +00:00

(Migrated from github.com)

BTW ... there have been a number of js problems/conflicts with the latest JCE editor; however, this site where I'm seeing this does not have JCE (just using TinyMCE), so the JCE issue is not related to this.

cpaschen commented

2020-02-06 20:00:51 +00:00

(Migrated from github.com)

FYI ... I've tested on 3.9.15 and also confirmed that this is not related to JCE (tested on site where JCE has never been installed).
Having trouble finding any site with any older version of JCB installed.
I did try to do some debugging of the jcb code but can't see anything in the PHP code. There are some places in the /administrator/components/com_componentbuilder/models/forms/field.js file that deal with that field value but it doesn't appear to be doing anything either.
I'm starting to wonder if this is related to PHP or MySQL versions as I just realized that those got updated recently here.
I'm going to do some testing on older php/mysql just to isolate that.

FYI ... I've tested on 3.9.15 and also confirmed that this is not related to JCE (tested on site where JCE has never been installed). Having trouble finding any site with any older version of JCB installed. I did try to do some debugging of the jcb code but can't see anything in the PHP code. There are some places in the /administrator/components/com_componentbuilder/models/forms/field.js file that deal with that field value but it doesn't appear to be doing anything either. I'm starting to wonder if this is related to PHP or MySQL versions as I just realized that those got updated recently here. I'm going to do some testing on older php/mysql just to isolate that.

cpaschen commented

2020-02-06 20:54:14 +00:00

(Migrated from github.com)

I did some more testing:

Round 1 (old system)
J: 3.9.12
JCB: 2.10.1
Mysql: 5.7.19
PHP: 7.2.14
STATUS: It works

Round 2 (upgrade Joomla)
J: 3.9.15
OTHERS: Same version (JCB: 2.10.1)
STATUS: It works

Round 3 (upgrade JCB to 2.10.5)
JCB: 2.10.5 (I don't have local copies of 2.10.2 - 2.10.4)
OTHERS: Same version
STATUS: BROKEN

So the issue appears to have been introduced somewhere between 2.10.1 and 2.10.5.
Unfortunately I don't have install packages for 2.10.2-2.10.4 to possibly narrow it down further; however, it sure does appear to me that the issue is in JCB somehow stripping that field of anything other than an integer (or decimal) value, and it needs to allow x,x format for proper mySQL DECIMAL field definition.

I hope that helps locate the issue.

Can anyone else at least confirm that they are having similar issues?
(I've only tested on WAMP and Binami-Lamp stack, but with same results, all in a windows environment)

I did some more testing: **Round 1 (old system)** J: 3.9.12 JCB: 2.10.1 Mysql: 5.7.19 PHP: 7.2.14 STATUS: It works **Round 2 (upgrade Joomla)** J: 3.9.15 OTHERS: Same version (JCB: 2.10.1) STATUS: It works **Round 3 (upgrade JCB to 2.10.5)** JCB: 2.10.5 (I don't have local copies of 2.10.2 - 2.10.4) OTHERS: Same version STATUS: BROKEN So the issue appears to have been introduced somewhere between 2.10.1 and 2.10.5. Unfortunately I don't have install packages for 2.10.2-2.10.4 to possibly narrow it down further; however, it sure does appear to me that the issue is in JCB somehow stripping that field of anything other than an integer (or decimal) value, and it needs to allow x,x format for proper mySQL DECIMAL field definition. I hope that helps locate the issue. Can anyone else at least confirm that they are having similar issues? (I've only tested on WAMP and Binami-Lamp stack, but with same results, all in a windows environment)

cpaschen commented

2020-02-06 21:17:36 +00:00

(Migrated from github.com)

Also, I downgraded (installed JCB 2.10.1 overtop 2.10.9) on the original dev site with the problem and that corrected the problem.
So, seems pretty clear to me that the issue is in JCB and happened sometime after 2.10.1 an by 2.10.5

Also, I downgraded (installed JCB 2.10.1 overtop 2.10.9) on the original dev site with the problem and that corrected the problem. So, seems pretty clear to me that the issue is in JCB and happened sometime after 2.10.1 an by 2.10.5

cpaschen commented

2020-02-07 17:40:32 +00:00

(Migrated from github.com)

Can anyone else test (using the steps at the start of this issue) to see if you are able to save a new field was a DECIMAL with length OTHER and "3,1" as the length?
It would be helpful to know if it's just me (or if others are able to catch anything that might be causing this - like possibly javascript issues?)

Can anyone else test (using the steps at the start of this issue) to see if you are able to save a new field was a DECIMAL with length OTHER and "3,1" as the length? It would be helpful to know if it's just me (or if others are able to catch anything that might be causing this - like possibly javascript issues?)

Llewellyn commented

2020-02-08 20:18:54 +00:00

Wow thank you @cpaschen for all your debugging here. I see this is the classic fixed it and the fix revealed other things that are not correct. Well not correct according to Llewellyn 😄

Wow thank you @cpaschen for all your debugging here. I see this is the classic fixed it and the fix revealed other things that are not correct. Well not correct according to Llewellyn :smile:

Llewellyn commented

2020-02-08 20:19:41 +00:00

Okay I will do a quick trace of this... and see where the breakdown occur. Will keep you posted.

Llewellyn commented

2020-02-09 11:09:57 +00:00

Here is the patch. Go to line 2736 in the compiler_fields class and replace all from 2736 to 2766 with the following:

// insure default not none if number type
$numberKeys = array('INT', 'TINYINT', 'BIGINT', 'FLOAT', 'DECIMAL', 'DOUBLE');
// don't use these as index or uniqe keys
$textKeys = array('TEXT', 'TINYTEXT', 'MEDIUMTEXT', 'LONGTEXT', 'BLOB', 'TINYBLOB', 'MEDIUMBLOB', 'LONGBLOB');
// build the query values
$this->queryBuilder[$view_name_single][$name]['type'] = $field['settings']->datatype;
// check if this is a number
if (in_array($field['settings']->datatype, $numberKeys))
{
	if ($field['settings']->datadefault === 'Other')
	{
		// setup the checking
		$number_check = $field['settings']->datadefault_other;
		// Decimals in SQL needs some help
		if ('DECIMAL' === $field['settings']->datatype && !is_numeric($number_check))
		{
			$number_check = str_replace(',', '.', $field['settings']->datadefault_other);
		}
		// check if we have a valid number value
		if (!is_numeric($number_check))
		{
			$field['settings']->datadefault_other = '0';
		}
	}
	elseif (!is_numeric($field['settings']->datadefault))
	{
		$field['settings']->datadefault = '0';
	}
}
// check if this is not text
if (!in_array($field['settings']->datatype, $textKeys))
{
	$this->queryBuilder[$view_name_single][$name]['lenght'] = $field['settings']->datalenght;
	$this->queryBuilder[$view_name_single][$name]['lenght_other'] = $field['settings']->datalenght_other;
	$this->queryBuilder[$view_name_single][$name]['default'] = $field['settings']->datadefault;
	$this->queryBuilder[$view_name_single][$name]['other'] = $field['settings']->datadefault_other;
}
// fall back unto EMPTY for text
else
{
	$this->queryBuilder[$view_name_single][$name]['default'] = 'EMPTY';
}

I will push out the fix with the next release. Let me know if you have any more issues.

Here is the patch. Go to [line 2736](https://github.com/vdm-io/Joomla-Component-Builder/blob/staging/admin/helpers/compiler/c_Fields.php#L2736) in the compiler_fields class and replace all from [2736](https://github.com/vdm-io/Joomla-Component-Builder/blob/staging/admin/helpers/compiler/c_Fields.php#L2736) to [2766](https://github.com/vdm-io/Joomla-Component-Builder/blob/staging/admin/helpers/compiler/c_Fields.php#L2766) with the following: ```php // insure default not none if number type $numberKeys = array('INT', 'TINYINT', 'BIGINT', 'FLOAT', 'DECIMAL', 'DOUBLE'); // don't use these as index or uniqe keys $textKeys = array('TEXT', 'TINYTEXT', 'MEDIUMTEXT', 'LONGTEXT', 'BLOB', 'TINYBLOB', 'MEDIUMBLOB', 'LONGBLOB'); // build the query values $this->queryBuilder[$view_name_single][$name]['type'] = $field['settings']->datatype; // check if this is a number if (in_array($field['settings']->datatype, $numberKeys)) { if ($field['settings']->datadefault === 'Other') { // setup the checking $number_check = $field['settings']->datadefault_other; // Decimals in SQL needs some help if ('DECIMAL' === $field['settings']->datatype && !is_numeric($number_check)) { $number_check = str_replace(',', '.', $field['settings']->datadefault_other); } // check if we have a valid number value if (!is_numeric($number_check)) { $field['settings']->datadefault_other = '0'; } } elseif (!is_numeric($field['settings']->datadefault)) { $field['settings']->datadefault = '0'; } } // check if this is not text if (!in_array($field['settings']->datatype, $textKeys)) { $this->queryBuilder[$view_name_single][$name]['lenght'] = $field['settings']->datalenght; $this->queryBuilder[$view_name_single][$name]['lenght_other'] = $field['settings']->datalenght_other; $this->queryBuilder[$view_name_single][$name]['default'] = $field['settings']->datadefault; $this->queryBuilder[$view_name_single][$name]['other'] = $field['settings']->datadefault_other; } // fall back unto EMPTY for text else { $this->queryBuilder[$view_name_single][$name]['default'] = 'EMPTY'; } ``` I will push out the fix with the next release. Let me know if you have any more issues.

duetnet commented

2020-03-07 13:30:49 +00:00

(Migrated from github.com)

I am still experiencing problems as per this issue.

Steps to reproduce the issue

Create new field “testnumber” with type NUMBER.
Set Data type = DECIMAL, Data Length = OTHER, Other length = 5,2.
Save

Expected Result

Field “Other length” retains value 5,2
On compile with JCB the SQL the code in install.mysql.utf8.php correctly reflects the code myfieldname DECIMAL(5,2) NOT NULL DEFAULT 0,

Actual Result

On SAVE, Field “Other length” changes value to 5 (drops the “,2”)
On compile, code generated reflects myfieldname DECIMAL(5) NOT NULL DEFAULT 0,

System information

OS Name & Version: Win 10 Pro 1909
MySql Version: 5.5.5-10.4.6-MariaDB
Apache Version: 2.4.39 (Win64) OpenSSL/1.1.1c PHP/7.3.8
PHP Version: 7.3.8
Joomla Version: Joomla! 3.9.15 Stable [ Amani ] 27-January-2020 15:00 GMT
JCB Version: 2.10.11
Browser: FireFox 73.0.1 64bit/Chrome 80.0.3987 64bit/IE 11.592/Edge

Additional comments

The fix provided as per issue gh-518 on 27 Jan 2020 did not correct problem.
On further investigation of previous JCB versions, I established the following:

the "fields.XML" file for v2.10.11 (..builder/model/forms) shows the field name="datalenght_other" is defined with the filter="INT".
I find the same issue with code on v2.10.5 and v2.10.9.
However, when I look at v2.10.1 the filter="STRING".
Could it be that the resolution of issue "gh-463- to insure data types that require length is enforced by default in the GUI", as per release v2.10.5, could have resulted in changes to this code(filter)??

I can understand that the INTEGER filter may be necessary for other Data types, but surely we still need a mechanism to set up the database with the correct number of decimals when configuring a DECIMAL Data type?

To overcome this problem temporarily until a solution is provided, I have manually modified the my compiler's code in the "fields.xml" file to reflect filter="STRING" again.

Thanks

I am still experiencing problems as per this issue. **Steps to reproduce the issue** - Create new field “testnumber” with type NUMBER. - Set Data type = DECIMAL, Data Length = OTHER, Other length = 5,2. - Save **Expected Result** - Field “Other length” retains value 5,2 - On compile with JCB the SQL the code in install.mysql.utf8.php correctly reflects the code myfieldname DECIMAL(5,2) NOT NULL DEFAULT 0, **Actual Result** - On SAVE, Field “Other length” changes value to 5 (drops the “,2”) - On compile, code generated reflects myfieldname DECIMAL(5) NOT NULL DEFAULT 0, **System information** - OS Name & Version: Win 10 Pro 1909 - MySql Version: 5.5.5-10.4.6-MariaDB - Apache Version: 2.4.39 (Win64) OpenSSL/1.1.1c PHP/7.3.8 - PHP Version: 7.3.8 - Joomla Version: Joomla! 3.9.15 Stable [ Amani ] 27-January-2020 15:00 GMT - JCB Version: 2.10.11 - Browser: FireFox 73.0.1 64bit/Chrome 80.0.3987 64bit/IE 11.592/Edge **Additional comments** The fix provided as per issue gh-518 on 27 Jan 2020 did not correct problem. On further investigation of previous JCB versions, I established the following: - the "fields.XML" file for v2.10.11 (..builder/model/forms) shows the field name="datalenght_other" is defined with the filter="INT". - I find the same issue with code on v2.10.5 and v2.10.9. - However, when I look at v2.10.1 the filter="STRING". - Could it be that the resolution of issue "gh-463- to insure data types that require length is enforced by default in the GUI", as per release v2.10.5, could have resulted in changes to this code(filter)?? I can understand that the INTEGER filter may be necessary for other Data types, but surely we still need a mechanism to set up the database with the correct number of decimals when configuring a DECIMAL Data type? To overcome this problem temporarily until a solution is provided, I have manually modified the my compiler's code in the "fields.xml" file to reflect filter="STRING" again. Thanks

Llewellyn commented

2020-03-07 13:42:57 +00:00

You are indeed correct that these two issues has a relationship. I will dig in to this issue, and get back to you 👍

You are indeed correct that these two issues has a relationship. I will dig in to this issue, and get back to you :+1:

jcodewalker commented

2020-03-20 11:38:06 +00:00

(Migrated from github.com)

Any update for this issue?

jcodewalker commented

2020-04-07 10:25:19 +00:00

(Migrated from github.com)

So, this is now a urgent issue.
The field works fine in JCB v 2.9.21...
We (me for one) really need this fixed.
Thanks in advance.

So, this is now a urgent issue. The field works fine in JCB v 2.9.21... We (me for one) really need this fixed. Thanks in advance.

Llewellyn commented

2020-04-08 22:00:31 +00:00

@jcodewalker really trying to get to this, I am currently focused on gh-444 issue, this one is also on the short list. Should have this resolved soon.

Llewellyn commented

2020-04-08 22:04:08 +00:00

@duetnet said:

To overcome this problem temporarily until a solution is provided, I have manually modified the my compiler's code in the "fields.xml" file to reflect filter="STRING" again.

@jcodewalker can this not work for you as a workaround at the moment?

@duetnet said: > To overcome this problem temporarily until a solution is provided, I have manually modified the my compiler's code in the "[fields.xml](https://github.com/vdm-io/Joomla-Component-Builder/blob/staging/admin/models/forms/field.xml#L411)" file to reflect filter="STRING" again. @jcodewalker can this not work for you as a workaround at the moment?

wado55 commented

2020-04-18 09:55:40 +00:00

(Migrated from github.com)

I have the same problem and if it is not solved, i have to look for an other comonent builder.
Have you found a patch in the meantime?

I have the same problem and if it is not solved, i have to look for an other comonent builder. Have you found a patch in the meantime?

Llewellyn commented

2020-04-18 10:40:14 +00:00

@wado55 did you read the following:

To overcome this problem temporarily until a solution is provided, I have manually modified the my compiler's code in the "fields.xml" file to reflect filter="STRING" again.

So it really is that simple, try it, you can do it!

@wado55 did you [read the following](https://github.com/vdm-io/Joomla-Component-Builder/issues/518#issuecomment-611218253): > To overcome this problem temporarily until a solution is provided, I have manually modified the my compiler's code in the "fields.xml" file to reflect filter="STRING" again. So it really is that simple, try it, you can do it!

Database Decimal Data Length not working properly - dropping D of M,D length setting #518

Steps to reproduce the issue

Expected result

Actual result

System information (as much as possible)

Additional comments