Phpmyadmin: SQL Error upon login

[y2kbadbug]

Hello,

So on the latest master I am now experiencing the following error when logging into some of my MySQL servers (not all):

Error
SQL query: DocumentationEdit Edit
SELECT * FROM information_schema.CHARACTER_SETS
MySQL said: Documentation

2006 -

If I remove everything except the base url itself, it takes me to the main server on my list which is logged in and gives me a bunch of tracebacks:

Warning in ./libraries/dbi/DBIMysqli.php#243
mysqli_query(): SSL operation failed with code 1. OpenSSL Error messages: error:0607A082:digital envelope routines:EVP_CIPHER_CTX_set_key_length:invalid key length error:0607A082:digital envelope routines:EVP_CIPHER_CTX_set_key_length:invalid key length

Backtrace

./libraries/dbi/DBIMysqli.php#243: mysqli_query(
object,
string 'SELECT * FROM information_schema.CHARACTER_SETS',
integer 0,
)
./libraries/DatabaseInterface.php#240: PMA\libraries\dbi\DBIMysqli->realQuery(
string 'SELECT * FROM information_schema.CHARACTER_SETS',
object,
integer 0,
)
./libraries/DatabaseInterface.php#88: PMA\libraries\DatabaseInterface->tryQuery(
string 'SELECT * FROM information_schema.CHARACTER_SETS',
NULL,
integer 0,
boolean true,
)
./libraries/mysql_charsets.inc.php#21: PMA\libraries\DatabaseInterface->query(string 'SELECT * FROM information_schema.CHARACTER_SETS')
./libraries/common.inc.php#1023: include_once(./libraries/mysql_charsets.inc.php)
./index.php#13: require_once(./libraries/common.inc.php)
Warning in ./libraries/dbi/DBIMysqli.php#243
mysqli_query(): MySQL server has gone away

Backtrace

./libraries/dbi/DBIMysqli.php#243: mysqli_query(
object,
string 'SELECT * FROM information_schema.CHARACTER_SETS',
integer 0,
)
./libraries/DatabaseInterface.php#240: PMA\libraries\dbi\DBIMysqli->realQuery(
string 'SELECT * FROM information_schema.CHARACTER_SETS',
object,
integer 0,
)
./libraries/DatabaseInterface.php#88: PMA\libraries\DatabaseInterface->tryQuery(
string 'SELECT * FROM information_schema.CHARACTER_SETS',
NULL,
integer 0,
boolean true,
)
./libraries/mysql_charsets.inc.php#21: PMA\libraries\DatabaseInterface->query(string 'SELECT * FROM information_schema.CHARACTER_SETS')
./libraries/common.inc.php#1023: include_once(./libraries/mysql_charsets.inc.php)
./index.php#13: require_once(./libraries/common.inc.php)
Warning in ./libraries/dbi/DBIMysqli.php#243
mysqli_query(): Error reading result set's header

Backtrace

./libraries/dbi/DBIMysqli.php#243: mysqli_query(
object,
string 'SELECT * FROM information_schema.CHARACTER_SETS',
integer 0,
)
./libraries/DatabaseInterface.php#240: PMA\libraries\dbi\DBIMysqli->realQuery(
string 'SELECT * FROM information_schema.CHARACTER_SETS',
object,
integer 0,
)
./libraries/DatabaseInterface.php#88: PMA\libraries\DatabaseInterface->tryQuery(
string 'SELECT * FROM information_schema.CHARACTER_SETS',
NULL,
integer 0,
boolean true,
)
./libraries/mysql_charsets.inc.php#21: PMA\libraries\DatabaseInterface->query(string 'SELECT * FROM information_schema.CHARACTER_SETS')
./libraries/common.inc.php#1023: include_once(./libraries/mysql_charsets.inc.php)
./index.php#13: require_once(./libraries/common.inc.php)
Notice in ./libraries/charset_conversion.lib.php#106
iconv(): Wrong charset, conversion from CP1252' toutf-8//TRANSLIT' is not allowed

Backtrace

./libraries/charset_conversion.lib.php#106: iconv(
string 'CP1252',
string 'utf-8//TRANSLIT',
string 'MySQL server has gone away',
)
./libraries/DatabaseInterface.php#342: PMA_convertString(
string 'CP1252',
string 'utf-8',
string 'MySQL server has gone away',
)
./libraries/DatabaseInterface.php#2173: PMA\libraries\DatabaseInterface->convertMessage(string 'MySQL server has gone away')
./libraries/dbi/DBIMysqli.php#418: PMA\libraries\DatabaseInterface->formatError(
integer 2006,
string 'MySQL server has gone away',
)
./libraries/DatabaseInterface.php#2613: PMA\libraries\dbi\DBIMysqli->getError(object)
./libraries/DatabaseInterface.php#89: PMA\libraries\DatabaseInterface->getError(NULL)
./libraries/mysql_charsets.inc.php#21: PMA\libraries\DatabaseInterface->query(string 'SELECT * FROM information_schema.CHARACTER_SETS')
./libraries/common.inc.php#1023: include_once(./libraries/mysql_charsets.inc.php)
./index.php#13: require_once(./libraries/common.inc.php)

[y2kbadbug]

Latest commit is: b5d6170b2280d22a191f3127ef8955f516948afa

[lem9]

Is there a difference in the MySQL version between the servers where this happens and those where it does not?

[y2kbadbug]

Yes there is, these are running on Amazon RDS. Some versions may be off a little bit but only minor versions.

[lem9]

Which MySQL version is running on Amazon RDS and which one outside of Amazon RDS?

[y2kbadbug]

All of them are on RDS:

All the versions are the same:
Server version: 5.6.21-log MySQL Community Server (GPL)

Some work, some don't.

One server is version: Server version: 5.5.40-log Source distribution, and this server logs in fine.

Let me know if there is any more information I can provide.

[lem9]

MySQL error 2006 is "MySQL server has gone away" https://dev.mysql.com/doc/refman/5.6/en/gone-away.html and I don't see what we can do at the phpMyAdmin level, especially as some of the servers run fine. By comparison, if you were running your own PHP application that does the same thing, you would get the same results.

[y2kbadbug]

It works perfectly fine in 4.0.6, so basically I am just stuck on this potentially insecure version. In a production environment we can't just immediately upgrade all our services to the latest version.

I feel more an effort on backwards compatibility should be made on the project.

Thanks anyways.

[lem9]

Not sure why you're talking about 4.0.6. As you can see on https://www.phpmyadmin.net/downloads, the latest 4.0 is 4.0.10.11 which is more up to date and is still supported for security fixes. I thought you were reporting this because you're testing master.

[y2kbadbug]

You did not read this correctly. Our current live version is 4.0.6. I tested the master version in a staging environment not in production. Good point on updating to 4.0.10.11, but we were hoping to bring our sqladmin up to date more.

[lem9]

The master branch is for development; did you try the latest stable version (this will be 4.5.1 when it's released tomorrow) ?

[y2kbadbug]

I reset the TAG to the stable release, still had the same problems.

[lem9]

You still have the same problems, but on some servers, not all?

[y2kbadbug]

Yes, we are working on getting all of our servers on to the latest stable version of MySQL, might be moving to MariaDB. Once that is done, I will try upgrading again. Until then I will just update the minor security versions for now.

[lem9]

I am quite confused. Your error 'MySQL error 2006 is "MySQL server has gone away" ' depends, in my opinion, on your MySQL servers, not on phpMyAdmin. You said that you were running 5.6.21-log MySQL Community Server (GPL) and that some of these servers gave the error, some did not give the error. So I wonder how this is a phpMyAdmin problem...

[y2kbadbug]

I am basically assuming a phpMyAdmin problem due to the fact that an older version of the software does not have this problem at all, none, not even once. Nothing was changed on our end, just the phpMyAdmin version. Regardless of the error code, it sounds like there is something phpMyAdmin is doing or not doing that is causing these errors. Like I said, we will hang back and either evaluate other solutions, or stick to compatible ones with security updates. I basically lost hope in this being observed and looked at any further honestly. Thanks for the feedback though.

[lem9]

Reopening to gather more feedback. I would hope that you're not the only one having this problem... Have you tested phpMyAdmin 4.0.10.11 in your context?

[jdaily]

I just ran into this error running MariaDB 10.1.8 and phpMyAdmin 4.5.1

Warning in ./libraries/dbi/DBIMysqli.class.php#261
 mysqli_query(): MySQL server has gone away

Backtrace

./libraries/dbi/DBIMysqli.class.php#261: mysqli_query(
object,
string 'SELECT COUNT(*) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA`=\'database\' AND `TABLE_TYPE`=\'BASE TABLE\' ',
integer 0,
)
./libraries/DatabaseInterface.class.php#246: PMA_DBI_Mysqli->realQuery(
string 'SELECT COUNT(*) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA`=\'database\' AND `TABLE_TYPE`=\'BASE TABLE\' ',
object,
integer 1,
)
./libraries/DatabaseInterface.class.php#1946: PMA_DatabaseInterface->tryQuery(
string 'SELECT COUNT(*) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA`=\'database\' AND `TABLE_TYPE`=\'BASE TABLE\' ',
NULL,
integer 1,
boolean false,
)
./libraries/navigation/Nodes/Node_Database.class.php#129: PMA_DatabaseInterface->fetchValue(string 'SELECT COUNT(*) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA`=\'database\' AND `TABLE_TYPE`=\'BASE TABLE\' ')
./libraries/navigation/Nodes/Node_Database.class.php#160: Node_Database->_getTableOrViewCount(
string 'tables',
string '',
boolean false,
)
./libraries/navigation/Nodes/Node_Database.class.php#74: Node_Database->_getTableCount(
string '',
boolean false,
)
./libraries/navigation/NavigationTree.class.php#595: Node_Database->getPresence(string 'tables')
./libraries/navigation/NavigationTree.class.php#342: PMA_NavigationTree->_addDbContainers(
object,
string '',
integer 0,
)
./libraries/navigation/NavigationTree.class.php#298: PMA_NavigationTree->_buildPathPart(
array,
string '',
string '',
string '',
string '',
)
./libraries/navigation/NavigationTree.class.php#867: PMA_NavigationTree->_buildPath()
./libraries/navigation/Navigation.class.php#50: PMA_NavigationTree->renderPath()
./navigation.php#75: PMA_Navigation->getDisplay()

[jdaily]

An update for my issue. Looks like a corrupted upgrade from 10.0 to 10.1 caused a information_schema query to fail in such a way it took down mariadb.

[lem9]

@y2kbadbug Can @jdaily 's explanation have an effect in your case?

[lem9]

No feedback.

[Jeremlicious]

I get the same error when trying to connect to a RDS with SSL. I have other PHP scripts on the same web server that connect successfully to the same database server with SSL.

On a local server phpMyAdmin works fine with SSL.

Config :

$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['verbose'] = 'Aurora AWS RDS SSL';
/* Server connection settings */
$cfg['Servers'][$i]['ssl']=true;
$cfg['Servers'][$i]['ssl_ca'] = 'rds-combined-ca-bundle.pem';

Error that I get when I go back to the home screen after the crash :

Any idea ?

[Jeremlicious]

• phpMyAdmin 4.5.4.1
• PHP Version 5.5.31 (fpm)
• mysqli
  Client API library version mysqlnd 5.0.11-dev - 20120503 - $Id: xxxxxxxx $
• mysqlnd 5.0.11-dev - 20120503 - $Id: xxxxxxxxxxx $
• OpenSSL 1.0.1e-fips 11 Feb 2013

[LucasNN]

Same error here.

Warning in ./libraries/dbi/DBIMysqli.php#252
mysqli_query(): SSL operation failed with code 1. OpenSSL Error messages: error:0607A082:digital envelope routines:EVP_CIPHER_CTX_set_key_length:invalid key length error:0607A082:digital envelope routines:EVP_CIPHER_CTX_set_key_length:invalid key length

Backtrace

./libraries/dbi/DBIMysqli.php#252: mysqli_query(
object,
string 'SELECT * FROM information_schema.CHARACTER_SETS',
integer 0,
)
./libraries/DatabaseInterface.php#240: PMA\libraries\dbi\DBIMysqli->realQuery(
string 'SELECT * FROM information_schema.CHARACTER_SETS',
object,
integer 0,
)
./libraries/DatabaseInterface.php#89: PMA\libraries\DatabaseInterface->tryQuery(
string 'SELECT * FROM information_schema.CHARACTER_SETS',
NULL,
integer 0,
boolean true,
)
./libraries/mysql_charsets.inc.php#21: PMA\libraries\DatabaseInterface->query(string 'SELECT * FROM information_schema.CHARACTER_SETS')
./libraries/common.inc.php#905: include_once(./libraries/mysql_charsets.inc.php)
./index.php#13: require_once(./libraries/common.inc.php)

[nijel]

The problem is that the file is encoded in iso-8859-1 and you're chosing utf-8 as encoding. However the parser probably shouldn't break that hard on this...

[nijel]

Sorry, commented wrong issue :-(.

[nijel]

While looking into #12146 I got same error with Google Cloud SQL...

[nijel]

For me it happens on QA_4_6, but works fine on master. However I was unable to discover change which causes this...

[jrchamp]

This is the error I'm seeing also. For some reason if fails that way on page load 1, 3, 5 and throws a different error on page load 2, 4, 6 (and it's not a load balanced webserver). The $GLOBALS['dbi'] variable becomes NULL in libraries/check_user_privileges.lib.php/libraries/db_common.inc.php and fatal errors. Also, the double underscore function was not defined in libraries/sanitizing.lib.php. That's about as far as I got before I had to just call it.

[eugenio11]

I am experiencing this problem too with Amazon RDS.
Same problem also explained here https://github.com/phpmyadmin/phpmyadmin/issues/12146

If I disable SSL ($cfg['Servers'][$i]['ssl'] = false;) everything works fine, if I enable it and I add:

$cfg['Servers'][$i]['ssl_ca'] = '/path/to/rds/rds-combined-ca-bundle.pem';

it doesn't work. As other people pointed out, connecting in SSL via terminal is fine, using the same ssl_ca. I am using phpmyadmin 4.6.0, PHP 5.5.33, mysql 5.6.27.

Is there a solution to this problem?

Thanks.

[nijel]

As for SSL you most likely have to disable SSL verification as PHP does verify whether host name matches CN of certificate what is in most cases not true. See https://docs.phpmyadmin.net/en/latest/config.html#google-cloud-sql-with-ssl

Anyway as this bug is fixed in master, I'm marking it as such.

[eugenio11]

Hello, thanks for the reply.
I am not sure if it is the same bug, in this case even if I disable SSL verification it still doesn't work.

The fix, anyway, will be available in the next release?

[nijel]

No, it's fixed in the master branch only, so targeted to 4.7.0.

[eugenio11]

Is the fix you are mentioning this one:
https://github.com/phpmyadmin/phpmyadmin/commit/7413208ce723d7d1c315a70b8789de91b0c202f8
?
if yes, I've tried to add ti to my 4.6.0 version but I'm still getting errors if SSL is enabled, with or without verification.

[nijel]

No it's not. I was not able to find specific fix, but it works fine for me in master, but not in 4.6.x.

[eugenio11]

Thanks, I've tried the master and I confirm the bug has disappeared for me too.

[jrchamp]

I think it's related to the MySQL information caching or charset changes. I'm seeing charset mentioned in the error messages, but the database connection may have simply broken just prior.

[nijel]

Well that used to be one of very first SQL queries executed. With master this has changed and the query is executed way later (if needed).

[rjbrown99]

Two quick notes -

1) 4.6.3 did not work for me, but the latest git clone of the master did work.

2) If you are having problems, you might also consider upgrading to at least php 5.6.x. I was having challenges with 5.5 based on a missing MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT which was introduced only in php 5.6+.

[jacobcyl]

I get the same error after upgrade mysql to 5.7.16 from 5.6.28 and run mysql_upgrade . So I restart mysql and solve this problem.

[sarveshcchauhan]

hey while exporting go to custom and scroll at bottom uncheck Dump binary columns in hexadecimal notation (for example, "abc" becomes 0x616263)

and then import the database in my case it works