RDS in aws.

RDS in aws.

Three steps to launch mysql on RDS.
1.Launch.
2.Connect.
3.Manage and moniter.

step 1. launch
a. create db instance.
b. Select engine :
Select db instance from option ex : here we choose mysql from option. (amazon aurora/mariadb/postgresql/oracle/sqlserver).
c. Production :
DEV/test (mysql/amazon aurora).
d. specify db details.
click on (Only show options that are eligible for RDS Free Tier) its a db.t2.micro instance.
select :
DB Instance Identifier* : mydbname
Master Username* : myusername
Master Password : mypassword
Confirm Password : mypassword

version : 5.6.29

select default security group:
default
give DB name.

Click on launch.

Previously created MySQL Schema ` ` was found. Statement ignored. ERROR: Previously created MySQL Table

Previously created MySQL Schema ` ` was found. Statement ignored. ERROR: Previously created MySQL Table

scenario : When performing reverse engineering in workbench. If there are multiple instance of DB created in workbench we get the error.
solution :  Make sure workbench dashboard only have one connection in open, to communicate with DB.

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column ' ' cannot be null

Let us check scenario of hibernate.
solution :
1.check entity  mapping with table.
2. check the primary key and foreign key relationship.
3. primary key cannot be "not null" in table, it can be null in other table. 

WARN (SqlExceptionHelper.java:233) - Unknown column 'xxx' in 'field list'

 WARN  (SqlExceptionHelper.java:233) - Unknown column 'EditedOn' in 'field list'

Solution :
1) In hibernate check the entity class.
2) In table check the column names.
3) Compare both the entity and column name.
4.Caused due to spelling mistake.

SQL Error: 0, SQLState: null, Cannot create PoolableConnectionFactory (Communications link failure)

2016-05-12 20:37:03,434 [http-bio-8080-exec-3] WARN  (SqlExceptionHelper.java:144) - SQL Error: 0, SQLState: null
2016-05-12 20:37:03,435 [http-bio-8080-exec-3] ERROR (SqlExceptionHelper.java:146) - Cannot create PoolableConnectionFactory (Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.)
2016-05-12 20:37:03,445 [http-bio-8080-exec-3] ERROR (BaseService.java:41) - org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.GenericJDBCException: Could not open connection
at org.springframework.orm.hibernate4.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:544)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:457)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:276)

Here is solution worked for me. provide relevant/correct db credentials and ip address. ex :

DB URL : jdbc:mysql://127.0.0.1:3306/voicedb
username : voice
password : voice

Access denied for user 'ec2-user'@'localhost' (using password: NO)

Access denied for user 'ec2-user'@'localhost' (using password: NO)

ref : ()

a) grep 'temporary password' /var/log/mysqld.log

login with temp password.

b) mysql -u root -p
password : 'temporary password'

(or)

mysql> UPDATE mysql.user SET Password=PASSWORD('your_new_password') WHERE User='root';

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

mysql> SET PASSWORD = PASSWORD('your_new_password');

Query OK, 0 rows affected, 1 warning (0.01 sec)

follow step - b.

ref : stackoverflow for a
stackoverflow : for b.

install/install mysql in rhel linux

install/install mysql in rhel linux
ref :
https://dev.mysql.com/doc/mysql-repo-excerpt/5.6/en/linux-installation-yum-repo.html
1) get the URL for the mysql rpm
sudo wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm

2) sudo yum install mysql57-community-release-el7-8.noarch.rpm


3) now install the server.
cmd : sudo yum install mysql-community-server

4) sudo service mysqld start

5) remove the mysql server.
cmd : yum remove mysql mysql-server

No database selected

ERROR 1046 (3D000): No database selected

we have two scenario
1) check if database exist.
2) if database not exist, create and use the database.

for case 1.

1) see database.
cmd :  show databases.
it will show default listing.
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec

now we have to create the DB and recheck again.

2)  if database not exist, create and use the database.
a) create database test.
cmd : create database test.
b) check test db created or not.
cmd : show databases.

+--------------------+
| Database           |
+--------------------+
| information_schema |
  test
+--------------------+
1 row in set (0.00 sec

c) start using the database.

cmd : use test.

  

mysql admin URL and port

Mysql admin URL

1) log in to mysql  from your box.
cmd : mysql.
2) To see the URL
cmd : mysql > select user();
result : root@localhost
3) Mysql port number.
cmd :
a) SHOW VARIABLES.
b) SHOW VARIABLES WHERE Variable_name = 'port';

Note :
a) default username : root
b) default password : ""(no password)
c) default port is 3306