MySQL Server – Explicitly State Database in Query Requirement

MySQLmysql-5.7Ubuntu

I am running mysql-client (Ver 14.14 Distrib 5.7.19) and mysql-server versions 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. I have replicated the behavior below over and over again.

Can someone please explain why this is happening ?

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,…

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;

This returns the full table (145 rows) with no problems whatsoever.

Best Answer

It sounds like 5.7.19 jumped the gun.

----- 2016-02-05 5.7.11 General Availability -- Functionality Added or Changed -- -----

The mysql_kill() ( /doc/refman/5.7/en/mysql-kill.html ), mysql_list_fields() ( /doc/refman/5.7/en/mysql-list-fields.html ), mysql_list_processes() ( /doc/refman/5.7/en/mysql-list-processes.html ), and mysql_refresh() ( /doc/refman/5.7/en/mysql-refresh.html ) C API functions are deprecated and will be removed in a future version of MySQL. The same is true of the corresponding COM_PROCESS_KILL, COM_FIELD_LIST, COM_PROCESS_INFO, and COM_REFRESH client/server protocol commands. Instead, use mysql_query() ( /doc/refman/5.7/en/mysql-query.html ) to execute a KILL ( /doc/refman/5.7/en/kill.html ), SHOW COLUMNS ( /doc/refman/5.7/en/show-columns.html ), SHOW PROCESSLIST ( /doc/refman/5.7/en/show-processlist.html ), or FLUSH ( /doc/refman/5.7/en/flush.html ) statement, respectively.

Recommend you post a bug with bugs.mysql.com .

Related Solutions

MySQL ERROR 2000 (HY000) – Unknown Error Causes and Solutions

Well I figured it out, and it isn't pretty. I guess I'll have to file a bug with MySQL.

The server is compiled with OpenSSL.
The working client is compiled with OpenSSL.
The client that can't connect is compiled with yaSSL.

The user I was trying to connect with used the sha256_password authentication plugin. When I dropped and re-created the user with mysql_native_password instead, the problem went away and I can connect from all clients. It appears that the sha256_password plugin doesn't work if the server and client are compiled against different SSL providers. (It might not even work with yaSSL at all.)

MySQL Error – Server Responds with Empty Packet During Session Negotiation

The packet that I thought was empty was actually not, it was an Old Auth Switch Request:

Payload
1     [fe]
Fields
status (1) -- 0xfe
Returns
Protocol::AuthSwitchResponse with old password hash
Example
01 00 00 02 fe

The 1, 2, 254 that wireshark parsed is actually 01 00 00 02 fe if you look at the actual byte strings.

So it isn't that there is a misunderstanding, the server understands completely and correctly responds and the client correctly terminates because it cannot negotiate down. The protocol is not too old as it was changed in 4.1, so both 5.0 and 5.7 understand each other perfectly. This should be a clearer error message.

The client is too new to use --skip-secure-auth (reference). They purposefully removed the ability to negotiate down prior to 5.7.

So my options are to allow new passwords on the server (which is a user specific configuration, not global server option) or to use an older binary.

The specific configuration issue is based on the username I was given. At some point in the past, someone using this username was using an older client and they changed the password method:

B.5.2.4 Client does not support authentication protocol

The current implementation of the authentication protocol uses a password hashing algorithm that is incompatible with that used by older (pre-4.1) clients. Attempts to connect to a 4.1 or newer server with an older client may fail with the following message:

shell> mysql
Client does not support authentication protocol requested
by server; consider upgrading MySQL client

To deal with this problem, the preferred solution is to upgrade all client programs to use a 4.1.1 or newer client library. If that is not possible, use one of the following approaches:

    To connect to the server with a pre-4.1 client program, use an account that still has a pre-4.1-style password.

    Reset the password to pre-4.1 style for each user that needs to use a pre-4.1 client program. This can be done using the SET PASSWORD statement and the OLD_PASSWORD() function. As of MySQL 5.6.6, it is also necessary to first ensure that the authentication plugin for the account is mysql_old_password:

    mysql> UPDATE mysql.user SET plugin = 'mysql_old_password'
    mysql> WHERE User = 'some_user' AND Host = 'some_host';
    mysql> FLUSH PRIVILEGES;
    mysql> SET PASSWORD FOR
        -> 'some_user'@'some_host' = OLD_PASSWORD('new_password');

Best Answer

Related Solutions

MySQL ERROR 2000 (HY000) – Unknown Error Causes and Solutions

MySQL Error – Server Responds with Empty Packet During Session Negotiation

Related Question