SSL Connection Error: SSL is required but the server doesn’t support it.

When users using connecting MYSQL using MySQL Workbench they received following error

By default MySQL Workbench, use SSL and users may receive above error. It is recommended to use certificates to avoid above error. In case, if you do not have the certificates then you can follow below steps to skip the certificate and connect to the MySQL Database

 



Option 1:

• Open MySQL workbench
• Right click on MySQL instance and select "Edit Connection"
• Go to the "SSL" tab under Connection Method
• Choose "If Available" instead of "Required" in the drop-down for the "Use SSL"
• Click the "Test Connection" and you should connect without errors.

Option 2:

• 
  
• Right click on MySQL instance and select "Edit Connection"
• Go to “Advanced” tab and enter "useSSL=0" in the 'Others' tab
• Click the "Test Connection" and you should connect without errors

Option 3:

 MySQL Workbench version 8.0.26 and below doesn’t use SSL Options by default

Thanks & Regards

http://oracleracexpert.com, Oracle ACE

Failed to connect to MySQL at with user root

When users connecting to MySQL using Workbench or from command prompt they may receive the error when they don’t have access to connect from the machine.

It is mainly due to security setup that allows to connect to “root” from specific IP address or localhost



In this scenario, ask your admins to create a new user account and setup the required access instead of using “root”

If the root account is not configure to authenticate with a password, you may need below command

MySQL> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';


If user need to login as root, you they can follow the steps to grant access to local host and or IP Address

MySQL> GRANT ALL ON [DB_Name].* to 'root'@'127.0.0.1' IDENTIFIED BY '[PASSWORD]';
MySQL> GRANT ALL ON [DB_Name].* to 'root'@ ‘IP Address’ IDENTIFIED BY '[PASSWORD]';

Instead of using “GRANT ALL ON”, use required privileges only.

Thanks & Regards

http://oracleracexpert.com, Oracle ACE

Convert the database from MyISAM to innoDB

When we are trying to use application tool to convert MySQL DB engine from MyISAM to innoDB it failed and received below error message.

Error: Pre-conversion step failed: unable to restart database: exit status 5

So I have tried converting manually using below steps.

1. Before you convert make sure you stop the application or web proxy

2. Run below command to generate a “alter table” command to convert to InnoDB engine.

mysql -u root -p -N -e "select concat('alter table ', table_name, ' engine =InnoDB;') from information_schema.tables where table_schema = 'TimeDB'" > TimeDB_innodb.sql

3. Run below command to start conversion processes. Note that the below command will run sequentially and may take some time.

mysql -u root -p TimeDB < TimeDB_innodb.sql

4. After the conversion completed bring up the application or web proxy

Thanks & Regards,
Satishbabu G, Oracle ACE
http://www.oracleracexpert.com

ERROR 1827 (HY000): The password hash doesn't have the expected format

When creating a user with Grant option we have received below error message

ERROR 1827 (HY000): The password hash doesn't have the expected format
That means you have to use hash password. Here is the simple solution that you can use

Enter the password that you want to set and you will get HASH password.
mysql> select password(‘enter password you want');
+-------------------------------------------+
| password(‘enter password you want') |
+-------------------------------------------+
| *B535BN128KK03E74BE2AC0EE23D07ABX6AD8165E |
+-------------------------------------------+
1 row in set (0.00 sec)

Use the has password while creating the user

mysql> GRANT ALL ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*B535BN128KK03E74BE2AC0EE23D07ABX6AD8165E ' WITH GRANT OPTION;

Query OK, 0 rows affected (0.01 sec)

Thanks
Satishbabu Gunukula, Oracle ACE

http://oracleracexpert.com

MySQL: Too many connection errors.

When user trying to connect MySQL Database, he got below error

ERROR 1040 (hy000): Too many connections
By looking the error we can see that the max_connections got exhausted. You can check the connection info by running below commands.

-- To find max_connections value run below command
SHOW VARIABLES LIKE '%max_connections%';

-- To see the all processes and connections run below command
SHOW FULL PROCESSLIST;

Note that when you get this error you will be able to login using root. Becoz My SQL by default will consider maximum allowed connections as MAX_CONNECTIONS +1 for super user.

But if you have used root to connect to any other app you will not able to connect to run the above commands. That means you should use root for only Administration purpose only.

You can run below command to change the MAX_CONNECTIONS

SET GLOBAL max_connections = 200;
But note that you need to find out is there any application issue which is creating more connections and causing this issue and fix that later.

Regards
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com

MySQL thread_stack overrun error

When user working with trigger code in MySQL he receives below error.

Thread stack overrun: 8304 bytes used of a 131072 byte stack, and 128000 bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack.


I see that MYSQL have stack value of 128K, which seems to be less and receiving the error

mysql> show variables where `Variable_name` = 'thread_stack';
+---------------+--------+
| Variable_name | Value |

+---------------+--------+

| thread_stack | 131072 |

+---------------+--------+

We have updated 'thread_stack' in my.cnf to 256k and restart the MySQL and everything working started working fine.

Thanks
Satishbabu Gunukula, Oracle ACE

http://oracleracexpert.com

InnoDB: Unable to lock ./ib_logfile0, error: 11

One of our MySQL servers got restarted and after restart MySQL instance was not coming online.

I see below error message in mysqld.log

InnoDB: Unable to lock ./ib_logfile0, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Error in opening ./ib_logfile0
160712 19:16:34 [ERROR] Plugin 'InnoDB' init function returned error.
160712 19:16:34 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160712 19:16:34 [ERROR] Unknown/unsupported storage engine: InnoDB
160712 19:16:34 [ERROR] Aborting
160712 19:16:34 [Note] /u01/mysql/5.5.22/bin/mysqld: Shutdown complete
I receive below error message when trying to access the database from command line

# mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/u01/mysql/5.5.22/mysqld.sock' (2) 

It looks like the sudden server crash didn’t update the logfile or datafile header properly and not able to lock to bring the instance up.

After research I was able to fix this error by following below steps

1. Go to the data directory and backup the ./ib_logfile0 and copy a new one
$ mv ib_logfile0 ib_logfile0.bak
$ cp -a ib_logfile0.bak ib_logfile0

2. Restart the mySQL database

Thanks,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Database error : (CS) "Java Class not found in classpath : com.mysql.jdbc.Driver"(WIS 10901)

The users will see below error if they don’t configure JDBC connection properly to connect MYSQL database.

Database error : (CS) "Java Class not found in classpath : com.mysql.jdbc.Driver" . (WIS 10901)

Follow below troubleshooting steps

1. Locate jdbc.sbo file and make sure that you have added “ClassPath” with correct MySQL Connector jar file.

<ClassPath>
<Path>/home/boxi/ bobj/enterprise_xi40/dataAccess/connectionServer/jdbc/mysql-connector-java-5.1.25-bin.jar</Path>
</ClassPath>

2. Update environment variable CLASSPATH with correct jar file path.

3. Make sure that the MySQL Connector jar file is right version, means for 64bit O/S or app use 64bit jar file.

Common Error in ODBC:- 

1. The below error message is very common when using ODBC(.odbc.ini) connection

[01000][unixODBC][Driver Manager]Can't open lib '/home/boxi/bobj/enterprise_xi40/linux_x64/odbc/lib/libmyodbc5a.so' : /home/boxi/bobj/enterprise_xi40/linux_x64/odbc/lib/libmyodbc5a.so: wrong ELF class: ELFCLASS32

Cause: The file is not the right version

Possible Solution: The file is not 64-bits version of the libmyodbc5a.so, install the right version. Use 32bit version of the libmyodbc5a.so, if using 32bit.

Download MySQL Connector/ODBC

Regards
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com 

cannot open shared object file: No such file or directory

This is very generic error and you will see this error with many situations.

I have received this error when connecting to Mysql using isql

$ isql -v MySQL test_user testpass
[01000][unixODBC][Driver Manager]Can't open lib '/usr/lib/libmyodbc.so' : /usr/lib/libmyodbc.so: cannot open shared object file: No such file or directory
[ISQL]ERROR: Could not SQLConnect 

Cause: The Mysql cannot find the required library

Possible Solution: Locate the file libmyodbc.so, if you don’t find the file then you might be using a different version. Find the lib file that you are using ‘libmyodbc*’ and create a link.

     For ex:- ln –s <Path to file> < Link Name>
     $ ln –s /home/mysql/lib/libmyodbc3-3.51.07.so /usr/lib/libmyodbc.so

In many causes users will see this error, if they don’t include the library path in LD_LIBRARY_PATH

Users might receive below error, when connecting using “odbcinst”. This is also one of the common error.

$ odbcinst –q -s
odbcinst: SQLGetPrivateProfileString failed with . 

Cause: The environment variables are not set properly

Solution: For Linux, edit .bash_profile and add following environment variables 

export ODBCSYSINI=/etc
export ODBCINI=/etc/odbc.ini

In caseof CSH,

setenv ODBCSYSINI /etc
setenv ODBCINI /etc/odbc.ini

Reference:

Configure a Connector/ODBC DNS on Unix
Problems with connecting other platforms to MySQL with ODBC
Download MySQL Connector/ODBC

Regards
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Error 1130: #HY000 Host ‘hostname’ is not allowed to connect to this MySQL server

The below are the most common errors, when connecting remotely to MySQL Server from MySQL Client.

Error 1130: #HY000 Host ‘hostname’ is not allowed to connect to this MySQL server

Cause: The host that you are using to connect MySQL Server does not have privilege

Solution: Grant required privileges
GRANT ON *.* TO ‘user’@'hostname' IDENTIFIED BY ‘password’ ;
For ex:- if you are connecting as root then use below syntax
Mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@’test-server’ IDENTIFIED BY ‘test’ ;

Now you should able to see a new user in mysql.user table.
Mysql> select host,user from mysql.user;
+------+---------------------------
| User | Host
+------+---------------------------
| root | test-server
+------+---------------------------

If you want to grant privilege to a host on specific database try below syntax.
mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@’test-server’ IDENTIFIED BY ‘test’ ;

Error 1130: #HY000 Host ‘IP address’ is not allowed to connect to this MySQL server

Cause: The user host IP address is not allowed to connect to MySQL Server

Solution: Grant the privilege to host IP address
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘user’@’IPADDRESS’ IDENTIFIED BY ‘password’ ;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@’x.x.x.x’ IDENTIFIED BY ‘test’ ;

ERROR 1045 (28000): Access denied for user 'root'@hostname' (using password: NO)

Cause: The root user is password protected and you need to use password parameter in order to connect

Solution: - Try to connect MySQL Server using below command
# mysql -u root -h test-server –p

ERROR 1045 (28000): Access denied for user 'root'@hostname' (using password: YES)

Cause: The 'root'@hostname' doesn’t have privilege to connect MySQL Server

Solution: - Grant permission using below command.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@hostname' IDENTIFIED BY ‘password’

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

I was working on MySQL Server setup and modified the default location of MySQL datadir. After I restart the MySQL, I was unable to connect and receiving below error.

#mysql –u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

Cause:
When we restart mysql server, it creates a mysql.sock file under socket variable path. The default location is /var/lib/mysql/mysql.sock.

I have updated the new location in /etc/my.cnf file.

[mysqld]
datadir=/opt/mysql/data
socket=/opt/mysql/mysql.sock

I can see the new file under /opt/mysql/mysql.sock, but I still receive the same error and not able to connect to MySQL.

I have modified the socket path under [mysqld], but the parameter was missing under [client] section. When I was trying to connect using MySQL client, it could not able to find mysql.sock file.

Solution:
Add [client] section in /etc/my.cnf

[client]
socket=/opt/mysql/mysql.sock

I have restarted MySQL in order to take effect of new changes in my.cnf

# service mysql stop
# service mysql start

MySQL Client able to find mysql.sock file and I was able to connect.

[root@iwebsql etc]# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.21 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

If you don’t want to restart mysql then use below command to tell client to use /opt/mysql/mysql.sock file

#mysql -u root –socket=/opt/mysql/mysql.sock

You may receive the same error, if mysqld didn't start. You can try below solution.

# /etc/init.d/mysqld start
# mysql –u root

Regards
Satishbabu Gunukula
http://www.oracleracexpert.com