Query takes too much time when acl_get_list is used

Discussion forum for Extension Writers regarding Extension Development.
User avatar
3Di
Former Team Member
Posts: 16039
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milan (IT) Frankfurt (DE)
Name: Marco
Contact:

Re: Query takes too much time when acl_get_list is used

Post by 3Di »

andreask wrote:
Sun Mar 19, 2017 1:32 pm
Because on my live board TPOTM is working fine, while on my local/dev environment I have the same problems.
Unfortunately I cannot install my ext to my live board because of the 3.1.10 bug.
Specifically, which one?
To request support for our extensions you can also contact me here: phpBB Studio

Please PM me only to request paid works. Thx. Want to compensate me for my interest? Donate
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
🚀 Looking for a specific feature or alternative option? We will rock you! 🚀
User avatar
3Di
Former Team Member
Posts: 16039
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milan (IT) Frankfurt (DE)
Name: Marco
Contact:

Re: Query takes too much time when acl_get_list is used

Post by 3Di »

[OT]

Curious enough, if you click on the little arrow next to the "wrote" in the quote
http://prntscr.com/emhntl
You will be (ofcourse) lead to the post related but you lose your online presence in the "users online string" and also the last post you made doesn't show/display.

[/OT]

I have to think on how to correctly spell/phrase this bug before to issue a report.
If somebody else is willing and offers as volunteer, feel free.
To request support for our extensions you can also contact me here: phpBB Studio

Please PM me only to request paid works. Thx. Want to compensate me for my interest? Donate
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
🚀 Looking for a specific feature or alternative option? We will rock you! 🚀
andreask
Registered User
Posts: 636
Joined: Fri Feb 27, 2009 6:13 pm
Name: Andreas

Re: Query takes too much time when acl_get_list is used

Post by andreask »

Hi Marco,

You replied to me on this post but as I wrote back I was not able to pinpoint the problem.
Here is what I am working on right now...
Inactive User Manager for phpBB
Give it a try...
If you would like to buy me a bier ;) for my work I will drink it on a hot summer day and thank you!!!
User avatar
3Di
Former Team Member
Posts: 16039
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milan (IT) Frankfurt (DE)
Name: Marco
Contact:

Re: Query takes too much time when acl_get_list is used

Post by 3Di »

I see, I replied there.
To request support for our extensions you can also contact me here: phpBB Studio

Please PM me only to request paid works. Thx. Want to compensate me for my interest? Donate
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
🚀 Looking for a specific feature or alternative option? We will rock you! 🚀
andreask
Registered User
Posts: 636
Joined: Fri Feb 27, 2009 6:13 pm
Name: Andreas

Re: Query takes too much time when acl_get_list is used

Post by andreask »

pierredu wrote:
Sun Mar 19, 2017 8:30 pm
andreask wrote:
Sun Mar 19, 2017 1:32 pm
Finally I was able to do some tests and it seems that my local sql OR vm is the problem.
Curious minds would like to know, whenever you can pinpoint the problem.
As promised!
This is the mysql configuration that works fine and there is no delay.
It is from file /etc/mysql/my.cnf

Code: Select all

# Example MariaDB config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MariaDB plays
# an important part, or systems up to 128M where MariaDB is used together with
# other programs (such as a web server)
#
# MariaDB programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, do:
# 'my_print_defaults --help' and see what is printed under
# Default options are read from the following files in the given order:
# More information at: http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MariaDB clients
[client]
#password       = your_password
port            = 3306
socket          = /run/mysqld/mysqld.sock

# Here follows entries for some specific programs

# The MariaDB server
[mysqld]
port            = 3306
socket          = /run/mysqld/mysqld.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# Point the following paths to different dedicated disks
#tmpdir         = /tmp/

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
Now, the other one (the slow). /etc/mysql/mysql.conf.d

Code: Select all

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries       = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size   = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
I hope this can clear out somehow the problem!
Here is what I am working on right now...
Inactive User Manager for phpBB
Give it a try...
If you would like to buy me a bier ;) for my work I will drink it on a hot summer day and thank you!!!
User avatar
3Di
Former Team Member
Posts: 16039
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milan (IT) Frankfurt (DE)
Name: Marco
Contact:

Re: Query takes too much time when acl_get_list is used

Post by 3Di »

andreask wrote:
Fri Mar 17, 2017 9:46 pm
I've installed TPOTM (3Di) on my local 3.2 board...
But I've run in some problems. After enabling it and going to my index page I get an error...

Code: Select all

General Error
SQL ERROR [ mysql4 ]

Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 't.p.post_time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by [1055]
Though the ext clearly states it's for 3.1.9...
You say it works on 3.2?
Well, I think my query should adhere to the new rules -> ONLY_FULL_GROUP_BY (MySQL >= 5.7).
Here I posted an untested fix for that.
https://github.com/3D-I/tpotm/issues/14

Thanks for the report, in my local environment the MySql of mine ignores ONLY_FULL_GROUP_BY, I will fix it.
To request support for our extensions you can also contact me here: phpBB Studio

Please PM me only to request paid works. Thx. Want to compensate me for my interest? Donate
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
🚀 Looking for a specific feature or alternative option? We will rock you! 🚀
Post Reply

Return to “Extension Writers Discussion”