Bug #87666 MySQL requires to explicidly state database in query or give limit
Submitted: 5 Sep 2017 7:34 Modified: 5 Oct 2017 13:52
Reporter: Romanos Papatheodoulou Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.7.19 OS:Ubuntu (server 16.04.2 LTS)
Assigned to: CPU Architecture:Any

[5 Sep 2017 7:34] Romanos Papatheodoulou
Description:

I am running mysql-client (Ver 14.14 Distrib 5.7.19) and mysql-server version 5.7.19 with user root from localhost on Ubuntu server 16.04.2 LTS.

I have noticed that mysql doesn't return the data I'm asking unless I explicitly specify the database in the query or I give a limit. I have replicated the behavior below over and over again.

How to repeat:
>> use db1;
>> select database();
db1

>> select count(*) from users;
145

>> select * from users;
Empty set, 28416 warnings
>> select * from users;
ERROR 2006 (HY000): MySQL server has gone away

# The result is changing every time you run the query above.
# One empty set, one Error2006, one empty set, one Error2006,...

>> select * from users limit 146;
returns 145 rows

>> select * from users;
Empty set, 28416 warnings

>> show warnings;
ERROR 2013 (HY000): Lost connection to MySQL server during query
>> show warnings;
Warning 1287 'COM_FIELD_LIST' is deprecated and will be removed in a future release. Please use SHOW COLUMNS FROM statement instead

# There's 64 rows of this warning. I get Error2013 only the first time that I
# run "show warnings". There is no pause or delay.
# All subsequent runs return the same 64 rows.

>> select * from db1.users;
returns 145 rows
[5 Sep 2017 9:19] MySQL Verification Team
Thank you for the bug report. Please provide the dump file of the database attaching the zip file here using the Files tab, please attach the my.cnf file also. Thanks.
[5 Sep 2017 13:31] Romanos Papatheodoulou
I cannot provide you with a dump of the database as the data is of sensitive nature.

How else can I help ?

The my.cnf file is shown below:

#
# 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

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
# BEGIN ANSIBLE MANAGED BLOCK
[mysqld]
#
# * Basic Settings
#
collation-server     = utf8mb4_general_ci # Replaces utf8_general_ci
character-set-server = utf8mb4            # Replaces utf8
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
bind-address    = 0.0.0.0
#
# BUFFERS
#+10% more will be used by innodb
innodb_buffer_pool_size=2G
#step for inc/dec pool size
innodb_buffer_pool_chunk_size=128M
#16*128M=2G
innodb_buffer_pool_instances=16
#buffers used for sorting
innodb_sort_buffer_size=4M
#
# FLUSH
#faster writes to disk for logs and data
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit = 2
# line above breaks ACID compliance
#
# LOGS
#speeds transactions that update multiple rows at once
innodb_log_buffer_size=256M
#the files that will be used for recovery. Should be able to hold ~1h of operations
innodb_log_file_size=128M
#2*128M=total size of redo log
innodb_log_files_in_group=2
#
# QUERY CACHE
#cache to save resilts of previous queries
query_cache_size=128M
query_cache_type=1
#max query size that can be saved
query_cache_limit=16M
#
# TABLE CACHE
#how many tables can be oppened
table_open_cache=1000
table_open_cache_instances=4
#
# PACKET
max_allowed_packet=128M
# FILE LIMITS
open_files_limit = 1024000
#
# REPLICATION
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format=ROW
binlog_do_db = qobo_fxprimus_live
#binlog-ignore-db=mysql
# two lines below set the maximum transaction size that can be logged
max_binlog_cache_size = 2G
binlog_cache_size = 32K
# ????
max_binlog_stmt_cache_size = 2G
binlog_stmt_cache_size = 32K
# this sets the maximum bin log file size
max_binlog_size = 100M
# 
expire_logs_days=3
# END ANSIBLE MANAGED BLOCK
[5 Sep 2017 13:52] MySQL Verification Team
Thank you for the feedback. I need a test case which could prove the issue reported if you are able to repeat with a fake data then it's ok. Thanks.
[6 Oct 2017 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".