mwweb commented

(Migrated from github.com)

Currently in the dynamic gets there is the ordering section under the Tweak tab. In most cases, this is perfectly fine. However, let's assume that in Sermon Distributor you have 25 preachers. If you can only sort by ascending or descending, you could be perceived as showing favoritism by only showing the same preachers all the time.

It would be nice to have an option, whether in the Ordering Direction listbox, or a yes/no, or something, called Random. If Random, then instead of $query->order('a.ordering ASC');, it would generate $query->order('RAND ()');. In doing that, the records on that view would be randomized.

System information (as much as possible)

OS Name & Version: 14.04.1-Ubuntu
MySql Version: 5.5.5-10.1.28-MariaDB-1~trusty
Apache Version: 2.4.29
PHP Version: PHP Version 7.1.18-1
Joomla Version: 3.8.12
JCB Version: 2.9.0
Browser: Chrome

Additional comments

Currently in the dynamic gets there is the ordering section under the Tweak tab. In most cases, this is perfectly fine. However, let's assume that in Sermon Distributor you have 25 preachers. If you can only sort by ascending or descending, you could be perceived as showing favoritism by only showing the same preachers all the time. It would be nice to have an option, whether in the Ordering Direction listbox, or a yes/no, or something, called Random. If Random, then instead of `$query->order('a.ordering ASC');`, it would generate `$query->order('RAND ()');`. In doing that, the records on that view would be randomized. ### System information (as much as possible) - OS Name & Version: 14.04.1-Ubuntu - MySql Version: 5.5.5-10.1.28-MariaDB-1~trusty - Apache Version: 2.4.29 - PHP Version: PHP Version 7.1.18-1 - Joomla Version: 3.8.12 - JCB Version: 2.9.0 - Browser: Chrome ### Additional comments

Llewellyn commented

2018-09-29 00:49:41 +00:00

Have a look at this https://stackoverflow.com/a/23706906/1429677

It seems to say that we need a random seed passed to ensure it is random... did I understand this correct. Then reading this issue on the Joomla repo, it seems Joomla has been looking at making this part of the API.... but I can't find it... can you?

So on line 1132 the order value is loaded, and is triggering the JDatabaseQueryElement class.

The constructor loads the ORDER BY with RAND() in as an element.

So if we want the seed loaded we must do it PHP side, and via JCB we must do something like this:

$query->order('RAND(' . rand ( 10 , 500 ) . ')');

Will this work?

Have a look at this https://stackoverflow.com/a/23706906/1429677 It seems to say that we need a random seed passed to ensure it is random... did I understand this correct. Then reading [this issue](https://github.com/joomla/joomla-cms/issues/16081) on the Joomla repo, it seems Joomla has been looking at making this part of the API.... but I can't find it... can you? So on [line 1132](https://github.com/joomla/joomla-cms/blob/staging/libraries/joomla/database/query.php#L1132) the order value is loaded, and is triggering the `JDatabaseQueryElement` [class](https://github.com/joomla/joomla-cms/blob/staging/libraries/joomla/database/query/element.php#L21). The [constructor](https://github.com/joomla/joomla-cms/blob/staging/libraries/joomla/database/query/element.php#L50) loads the `ORDER BY` with `RAND()` in as an element. So if we want the seed loaded we must do it PHP side, and via JCB we must do something like this: ``` $query->order('RAND(' . rand ( 10 , 500 ) . ')'); ``` Will this work?

Llewellyn commented

2018-09-29 00:57:30 +00:00

I for now just added what you asked... please test and give feedback, also on the first post I made.

I for now [just added what you asked](https://github.com/vdm-io/Joomla-Component-Builder/commit/fdd1cf73905167a00423ca8b7054aaa54af69e18#diff-001cb31b03c7dade5d5f5df9897ff487R2082)... please test and give feedback, also on the first post I made.

mwweb commented

2018-09-29 03:01:48 +00:00

(Migrated from github.com)

In attempting to install that staging branch on a test site:

Warning
JInstaller: :Install: Error SQL Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
Extension Update: SQL error processing query: DB function failed with error number 1118
Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
SQL =
ALTER TABLE #__componentbuilder_joomla_component ENGINE = InnoDB;

In attempting to install that staging branch on a test site: Warning JInstaller: :Install: Error SQL Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline. Extension Update: SQL error processing query: DB function failed with error number 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline. SQL = ALTER TABLE `#__componentbuilder_joomla_component` ENGINE = InnoDB;

Llewellyn commented

2018-09-29 14:25:16 +00:00

Hmm this must be due to your database being to big to make this alteration to the tables. So you will need to check your local environment values. Never install the staging branch on a production site, always in a testing environment. Yet this issue that you encountered will not go away, unless you increase your local limits... strange that it did, that. I mean I also have a huge system and it did not break down.

just that you know this issue should be further discussed on issue gh-345 as it is not part of the current changes, but related to that issue instead.

Hmm this must be due to your database being to big to make this alteration to the tables. So you will need to check your local environment values. Never install the staging branch on a production site, always in a testing environment. Yet this issue that you encountered will not go away, unless you increase your local limits... strange that it did, that. I mean I also have a huge system and it did not break down. just that you know this issue should be further discussed on [issue gh-345](https://github.com/vdm-io/Joomla-Component-Builder/issues/345) as it is not part of the current changes, but related to that issue instead.

peterpetrov commented

2018-09-29 14:52:42 +00:00

(Migrated from github.com)

@mwweb can you provide me with your innodb setting from my.cnf please? I think that might be related to your DB settings there. InnoDB can be tricky to configure before it work its magic.

peterpetrov commented

2018-09-29 15:00:53 +00:00

(Migrated from github.com)

@Llewellynvdm have you changed anything in the installation script of the db for JCB? Just wondering if you updated it to InnoDB instead MyISAM.

Llewellyn commented

2018-09-29 17:10:07 +00:00

Yes I did move the whole of JCB to InnoDB, that is the issue...

The file that does this is 2.9.5.sql

Yes I did move the whole of JCB to InnoDB, that is the issue... The file that does this is [2.9.5.sql](https://github.com/vdm-io/Joomla-Component-Builder/blob/staging/admin/sql/updates/mysql/2.9.5.sql)

Llewellyn commented

2018-09-29 17:15:34 +00:00

On my side the whole system has been more responsive since. Really better, so I don't think that was a mistake. Also know that I run on a huge office server, that has like 36GB of memory and SSD drives with almost no limits that can easy be reached... so I take it smaller systems with huge DB's could run into trouble during the conversion. I am wondering if there is a better path to make this transition. Joomla made it a few months ago. Or did they just start shipping with InnoDB, and not attempt the conversion. We could remove the conversion... and just start shipping JCB with InnoDB, that is an option.

mwweb commented

2018-09-29 17:23:13 +00:00

(Migrated from github.com)

I'm not a my computer right now (been trying to get over a cold). When i am in front of my computer I'll look at the my.cnf. But, i think keeping it on innodb would be a good option. But the best option needs to be set, then i don't know if there's a preflight check that can be done to ensure the settings are correct, and if not warn the user. I do something similar with php.

mwweb commented

2018-09-29 20:41:56 +00:00

(Migrated from github.com)

To answer you @peterpetrov, my innodb settings are:

InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.

default_storage_engine = InnoDB

you can't just change log file size, requires special procedure

#innodb_log_file_size = 50M
innodb_buffer_pool_size = 256M
innodb_log_buffer_size = 8M
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT

To answer you @peterpetrov, my innodb settings are: # # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! default_storage_engine = InnoDB # you can't just change log file size, requires special procedure #innodb_log_file_size = 50M innodb_buffer_pool_size = 256M innodb_log_buffer_size = 8M innodb_file_per_table = 1 innodb_open_files = 400 innodb_io_capacity = 400 innodb_flush_method = O_DIRECT

mwweb commented

2018-09-29 20:44:43 +00:00

(Migrated from github.com)

I have played with a lot of those settings. I'm still testing, but out of curiosity, and seeing that there were 102 columns, I tested by dropping some columns, and at 1/2 the columns (50 instead of 102) it works. I'll continue testing.

mwweb commented

2018-09-30 07:30:35 +00:00

(Migrated from github.com)

OK. I further test. At the very least, the following fields are causing this. I went through my biggest component, and removed columns in ##__componentbuilder_joomla_component that had large amounts of data. There are 11 components, and with these fields removed, conversion to innodb had no issues:

addadmin_views
addconfig
addfiles
addsite_views
php_method_uninstall
php_postflight_install
php_postflight_update

OK. I further test. At the very least, the following fields are causing this. I went through my biggest component, and removed columns in `##__componentbuilder_joomla_component` that had large amounts of data. There are 11 components, and with these fields removed, conversion to innodb had no issues: addadmin_views addconfig addfiles addsite_views php_method_uninstall php_postflight_install php_postflight_update

peterpetrov commented

2018-10-02 17:52:22 +00:00

(Migrated from github.com)

I haven't got the time to make tests. What i gather from @mwweb investigation is that since JCB was originally MyISAM it must been doing upgrade to the tables. So when there is a bigger table it might get stuck if the table is bigger than the buffer set size in @mwweb's case - innodb_log_buffer_size = 8M. The problem here is people would not know exactly how to fix that. Maybe a pre-flight check of some of the values and eventually sets higher upon installation. That is not ideal too because next time the sql service is restarted it might not keep the values and reset to defaults. So i think only current users with many components might be experiencing it.

Llewellyn commented

2018-10-02 19:38:44 +00:00

Yes I understand the issue, reality is there is no easy way to achieve this. But to either just steam forwards and catch the fallout with a few helper scripts of helpful tutorial....

Reality is I think @mwweb is the exception, have a huge database like that, seems bigger then mine... lol

The other option is to remove the upgrade sql and instead dump it on a gist for those who want to manually change over. The rest can just stay on MyISAM, and all those who do a fresh install from here forward will start with Innodb. That is the second option...

Third is this pre-flight script... I am not so much for it. But okay... if this is the way we choose to go, can any of you give me a proposed script you think should be used. I will not have time to research this in the next few days, so would be great if one of you can help with that.

Yes I understand the issue, reality is there is no easy way to achieve this. But to either just steam forwards and catch the fallout with a few helper scripts of helpful tutorial.... Reality is I think @mwweb is the exception, have a huge database like that, seems bigger then mine... lol The other option is to remove the [upgrade sql](https://github.com/vdm-io/Joomla-Component-Builder/blob/staging/admin/sql/updates/mysql/2.9.5.sql) and instead dump it on a gist for those who want to manually change over. The rest can just stay on MyISAM, and all those who do a fresh install from here forward will start with Innodb. That is the second option... Third is this pre-flight script... I am not so much for it. But okay... if this is the way we choose to go, can any of you give me a proposed script you think should be used. I will not have time to research this in the next few days, so would be great if one of you can help with that.

mwweb commented

2018-10-02 19:41:22 +00:00

(Migrated from github.com)

I'm just "guessing", but I'm thinking that perhaps this could happen with someone who has a lot of custom scripting in any of the fields below. That is where my breakdown is occurring. If I remove the custom scripting, then it works fine.

addadmin_views
addconfig
addfiles
addsite_views
php_method_uninstall
php_postflight_install
php_postflight_update

I'm just "guessing", but I'm thinking that perhaps this could happen with someone who has a lot of custom scripting in any of the fields below. That is where my breakdown is occurring. If I remove the custom scripting, then it works fine. addadmin_views addconfig addfiles addsite_views php_method_uninstall php_postflight_install php_postflight_update

Llewellyn commented

2018-10-02 19:43:26 +00:00

We moved most of these out to there own tables did we not.... so much of those are still fields with old data, or are you speaking of the new fields/tables

Llewellyn commented

2018-10-02 19:44:38 +00:00

I can clear-out the old fields from the admin and component tables alike.... if that will help.

mwweb commented

2018-10-02 19:52:43 +00:00

(Migrated from github.com)

I'm seeing that even though I'm using the new tables, the old columns still exist in componentbuilder_joomla_component. That could be the issue. Shouldn't those columns been dropped after the upgrade to the new format?

Llewellyn commented

2018-10-02 19:55:44 +00:00

I have not yet done it, I was at the time careful if for some reason the upgrade broke JCB, not to lose the data... and have not since removed them. So yes, the time has come to clean house I suppose, by now we are way head from that upgrade so removing that data now should not hurt any 👍

I have not yet done it, I was at the time careful if for some reason the upgrade broke JCB, not to lose the data... and have not since removed them. So yes, the time has come to clean house I suppose, by now we are way head from that upgrade so removing that data now should not hurt any :+1:

mwweb commented

2018-10-02 19:56:38 +00:00

(Migrated from github.com)

I'm testing right now.

Llewellyn commented

2018-10-02 20:01:23 +00:00

In #__componentbuilder_joomla_component we must remove:

addconfig,
addadmin_views,
addcustom_admin_views,
addsite_views,
version_update,
sql_tweak,
addcustommenus,
dashboard_tab,
php_dashboard_methods,
addfiles,
addfolders

Then in the #__componentbuilder_admin_view we should also now remove:

addfields,
addconditions

These are not huge columns... but yes no longer needed as they have been moved each to its own table, with respected relations.

In `#__componentbuilder_joomla_component` we must remove: ``` addconfig, addadmin_views, addcustom_admin_views, addsite_views, version_update, sql_tweak, addcustommenus, dashboard_tab, php_dashboard_methods, addfiles, addfolders ``` Then in the `#__componentbuilder_admin_view` we should also now remove: ``` addfields, addconditions ``` These are not huge columns... but yes no longer needed as they have been moved each to its own table, with respected relations.

mwweb commented

2018-10-02 21:14:35 +00:00

(Migrated from github.com)

Not quite. I think it's a moot point, really. The way MYISAM and INNODB handle things creates problems, due to the size of rows. I was getting errors on componentbuilder_admin_fields, componentbuilder_admin_view, componentbuilder_joomla_component. Some of the fields just contain too much data. Even my exporting, say, componentbuilder_admin_view (data only), convert to INNODB, then attempt import, there's the error.

Llewellyn commented

2018-10-02 21:43:21 +00:00

Well I just finished with the script to clear that data out, I knew it is not the whole hold-up.... but time to move on.

Okay so in your case you will need to stay in MyISAM... or what is your conclusion.

I did not have to tweak any DB settings and my system upgraded with no issues. But I realize this can be a huge drawback for other.... so I would like to make this work.

Does any of you know how Joomla did this? I mean they must have changed in the last few years, since last I looked they were on MyISAM and when this issue came up... I was like hmmm they changed. So I totally missed that move. Would be interesting to see how they handled it.

Well I just finished with the script to clear that data out, I knew it is not the whole hold-up.... but time to move on. Okay so in your case you will need to stay in MyISAM... or what is your conclusion. I did not have to tweak any DB settings and my system upgraded with no issues. But I realize this can be a huge drawback for other.... so I would like to make this work. Does any of you know how Joomla did this? I mean they must have changed in the last few years, since last I looked they were on MyISAM and when this issue came up... I was like hmmm they changed. So I totally missed that move. Would be interesting to see how they handled it.

mwweb commented

2018-10-02 23:20:27 +00:00

(Migrated from github.com)

OK. I did some testing and experimenting. I'm just not sure how you would integrate this into the JCB Upgrade. Here is what I found that WORKS!!

Dump the table (structure and data)
In the create table change: ENGINE=MyISAM AUTO_INCREMENT=39 DEFAULT CHARSET=utf8; to ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=dynamic;
In the rows (if exists), change ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; to ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=dynamic;
Drop existing table, then import.

OK. I did some testing and experimenting. I'm just not sure how you would integrate this into the JCB Upgrade. Here is what I found that WORKS!! 1. Dump the table (structure and data) 2. In the create table change: `ENGINE=MyISAM AUTO_INCREMENT=39 DEFAULT CHARSET=utf8;` to `ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=dynamic;` 3. In the rows (if exists), change `ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;` to `ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=dynamic;` 4. Drop existing table, then import.

mwweb commented

2018-10-02 23:55:33 +00:00

(Migrated from github.com)

I further tested with every JCB table doing the above, and all tables successfully converted to InnoDB.

The only error received in doing this was:

QL query:

ALTER TABLE j_componentbuilder_external_code
ADD PRIMARY KEY (target)
MySQL said: Documentation

#1071 - Specified key was too long; max key length is 767 bytes

I further tested with every JCB table doing the above, and all tables successfully converted to InnoDB. The only error received in doing this was: QL query: ALTER TABLE `j_componentbuilder_external_code` ADD PRIMARY KEY (`target`) MySQL said: Documentation #1071 - Specified key was too long; max key length is 767 bytes

Llewellyn commented

2018-10-03 11:28:15 +00:00

Okay so this is what we will do... I am removing the update of all JCB tables from MyISAM to InnoDB, but I have dump the sql update script in this gist for those who want to take the leap...

Yet all new installations of JCB will be on InnoDB.

I just don't want to get involved in the data juggling at this point, too many projects that need my attention.

So the next push will implement this direction.

Back to this issue at hand.

@mwweb did you get to test the random ordering?

Okay so this is what we will do... I am removing the update of all JCB tables from MyISAM to InnoDB, but I have dump the sql update script in [this gist](https://gist.github.com/Llewellynvdm/85234c6bd1e7d0eb4ddd6c925770aa2c) for those who want to take the leap... Yet all new installations of JCB will be on InnoDB. I just don't want to get involved in the data juggling at this point, too many projects that need my attention. So the next push will implement this direction. # Back to this issue at hand. @mwweb did you get to test the random ordering?

👍 1

mwweb commented

2018-10-07 03:57:00 +00:00

(Migrated from github.com)

I think the ordering is working. I haven't heard any issues in where i implemented it.

👍 1

Feature Request -- Dynamic Get -- Ordering #348

System information (as much as possible)

Additional comments

InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.

Read the manual for more InnoDB related options. There are many!

you can't just change log file size, requires special procedure

Back to this issue at hand.