"could not open database" - MySQL Workbench

[Brian Barnes]

Search Forums (could not open database) => 0 hits on http://forums.mysql.com
Google ("could not open database" mysql) => 133,000 hits

Many people are attempting bizarre and apparently random system hacks to try to fix this pervasive problem. It is unbelieveable that nobody has ever asked on this forum.

Is there a finite series of steps to diagnose why MysqlWorkbench fails to connect where many other programs work fine? Hypothesis: workbench has mangled its own xml file, encrypted owner portion. ???

Perl, mysql command line, Toad and mysql workbench -> model work perfectly. Only workbench -> connections “can not open database”. I just re-re-re-re-installed the latest mysql-workbench-community-6.1.6-win32.msi.
=======================================================================================

Perl works :
&do_sql_ara();

sub connect() {
my $dbhost = $_[0] || '127.0.0.1'; # Vulcan or Raptor?.
my $dbase = $_[1] || 'asset';
my $duid = $_[2] || 'brianp';
my $dpw = $_[3] || <SNIP>
my($dbh);
$dbh = DBI->connect("dbi:mysql:database=$dbase;host=$dbhost",
$duid, $dpw, {'RaiseError' => 1}) or die "Couldn't connect $DBI::errstr";
return $dbh;
}


sub do_sql_ara() {
my($dbh, $sql, $sth, $rtn, $count);
$dbh = &connect(); # Get static IPTC values from database.
$sql = "select count(*) from attr";
$sth = $dbh->prepare($sql);
$rtn = $sth->execute();
$ii = -1;
while(($count) = $sth->fetchrow_array) {
$ii++;
printf("SQL[$ii] \"$sql\" -> $rtn rows -> $count\n");
}
$dbh->disconnect();
return($dbh);
}

d:\pic>bb.pl
Running C:\bin\bb.pl Thu Jun 5 12:42:14 2014
SQL[0] "select count(*) from attr" -> 1 rows -> 203820
Elapsed time = 2.040 seconds.
--------------------------------

Mysql command line works:
d:\pic>mysql -u brianp -p -h 127.0.0.1 -P 3306
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 5.6.16 MySQL Community Server (GPL)
-----------------------------------------------

Toad works fine:
C:\Windows\system32>pslist -m toad
Process memory detail for VIPER:
Name Pid VM WS Priv Priv Pk Faults NonP Page
toad 6992 548908 66656 120100 130628 1353417 107 820
------------------------------------------

C:\Windows\system32>pslist -m mysql
Process memory detail for VIPER:
Name Pid VM WS Priv Priv Pk Faults NonP Page
MySQLWorkbench 6264 500492 154080 169252 173452 850273 111 758
mysql 1528 69396 8852 9300 9384 4288 44 123
mysqld 6716 2433316 622644 2357548 2372560 159132 91 124
mysql 6268 70468 9088 9240 9328 4458 44 125
---------------------------------------------------------------------------------------------------------------

Server starts as a service without incident with this command from services dialog:
"C:\Program Files\mysql-5.6.16-winx64\bin\mysqld" --defaults-file="C:\Program Files\mysql-5.6.16-winx64\my.ini" MySQL
C:\Windows\system32>net stop mysql
The MySQL service is stopping..
The MySQL service was stopped successfully.

C:\Windows\system32>net start mysql
The MySQL service is starting.
The MySQL service was started successfully.
---------------------------------------------------------------------------------------------------------------

AND THE FIREWALL AND ALL ANTIVIRUS SOFTWARE WAS TURNED OFF FOR TESTING.

---------------------------------------------------------------------------------------------------------------

The TEST_CONNECTION always shows "connection parameters are correct". Then connect fails 100%. Not a very authoritative test???

Error msg from C:\Users\brianp\AppData\Roaming\MySQL\Workbench\log\wb.log
17:13:54 [ERR][ SqlEditor]: SQL editor could not be connected: Could not open database
17:13:54 [ERR][ SqlEditor]: Your connection attempt failed for user 'brianp' from your host to server at 127.0.0.1:3306:
Could not open database

Please:
1 Check that mysql is running on server 127.0.0.1
2 Check that mysql is running on port 3306 (note: 3306 is the default, but this can be changed)
3 Check the brianp has rights to connect to 127.0.0.1 from your address (mysql rights define what clients can connect to the server and from which machines)
4 Make sure you are both providing a password if needed and using the correct password for 127.0.0.1 connecting from the host address you're connecting from

mysql> use mysql;
Database changed
mysql> select host from user where user='brianp';
| host |
+-----------+
| % |
| 127.0.0.1 |
| localhost |
3 rows in set (0.00 sec)

---------------------------------------------------------------------------------------------------------------

The windows task manager shows mysql.exe and mySQLWorkbench owned by me, brianp. mysqld.exe is owned by system.

THe permissions on M:\mysql\data have r-x for everybody for both the asset and mysql dirs and read perms for everybody for everybody on every MYD, MYI and frm file.

Every program other than Mysql Workbench works perfectly.

The strangest thing is that the Workbench modeler works. I have a full EER Diagram on the ASSET database with the SAME LOGIN INFO.

I suspect that somehow the C:\Users\brianp\AppData\Roaming\MySQL\Workbench/connections.xml has misconfigured. I deleted all old connections and created one new one. THe encrypted (obfuscated, mis-over-configured) BrtObject “owner” looks very suspicious.

<?xml version="1.0"?>
<data grt_format="2.0">
<value _ptr_="0C43A4E0" type="list" content-type="object" content-struct-name="db.mgmt.Connection">
<value type="object" struct-name="db.mgmt.Connection" id="{F236B96F-79EB-467D-A0D1-D7C59E294C3A}" struct-checksum="0x96ba47d8">
<link type="object" struct-name="db.mgmt.Driver" key="driver">com.mysql.rdbms.mysql.driver.native</link>
<value type="string" key="hostIdentifier">Mysql@127.0.0.1:3306</value>
<value type="int" key="isDefault">1</value>
<value _ptr_="0C43B3E0" type="dict" key="modules"/>
<value _ptr_="0C43B110" type="dict" key="parameterValues">
<value type="string" key="SQL_MODE"></value>
<value type="string" key="hostName">127.0.0.1</value>
<value type="string" key="password"></value>
<value type="int" key="port">3306</value>
<value type="string" key="schema">asset</value>
<value type="string" key="sslCA"></value>
<value type="string" key="sslCert"></value>
<value type="string" key="sslCipher"></value>
<value type="string" key="sslKey"></value>
<value type="string" key="userName">brianp</value>
</value>
<value type="string" key="name">brianp.std.tcp.127.asset</value>
<link type="object" struct-name="GrtObject" key="owner">{620651B5-FF51-47C8-BDCE-85689EBDCEB9}</link>
</value>
</value>
</data>

[[ NOTE ]] that the user (brianp), schema (asset), host (127.0.0.1) and port (3306) are identical to the command line version which works.

=========================================================================================================
connection -> system profile is correct: windows config file, section mysqld and service name MySQL


P:\br1\ks-2014.0517>egrep "port|mysqld|basedir|client|datadir" "C:\Program Files\mysql-5.6.16-winx64\my.ini" | grep -v "^#"
[client]
port=3306
[mysqld]
port=3306
basedir="C:/Program Files/mysql-5.6.16-winx64/"
datadir = "M:/mysql/data"