Tag Archives: mySql

Dec 15 2019
IT Stuff

Install MySQL server with Docker Compose

Inside a new directory, create a data directory and docker-compose.yml with these rows:

version: '3'

services: 
  db:
    container_name: docker-local-mysql
    image: mysql:5.7.21
    volumes:
      - "./data:/var/lib/mysql"
    restart: always
    ports:
      - 3306:3306
    environment:
      MYSQL_ROOT_PASSWORD: password

To start the container, run docker-compose up -d. To stop & remove the container, run docker-compose down.

To connect from a MySql client use this:

Host: 127.0.0.1
Username: root
Password: password
Port: 3306
Tagged ,
Aug 25 2017
IT Stuff

Warning about SSL connection when connecting to MySQL database

After a recent update of mySql, I get this warning:

WARN: Establishing SSL connection without server's identity verification is not recommended. 
According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established 
by default if explicit option isn't set. For compliance with existing applications not using SSL 
the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL 
by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

To disable disable SSL and also suppress the SSL warning, it’s possible to set to false the useSSL parameter on the connection string:

jdbc:mysql://localhost:3306/myDb?autoReconnect=true&useSSL=false

On applicationContext, something like this:

<!--  ############ MY SQL DATABASE SECTION ############ -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">	
    <property name="driverClassName"><value>com.mysql.jdbc.Driver</value></property>
    <property name="url">
        <value>jdbc:mysql://${mysql.hostname}:${mysql.port}/${mysql.db}?useSSL=false</value>
    </property>
    <property name="username"><value>${mysql.user}</value></property>
    <property name="password"><value>${mysql.password}</value></property>
</bean>
Tagged ,
Jul 01 2014
Leave a comment
IT Stuff

Multiple COUNT select from same table

I fixed the issue in this way:

SELECT R.id_request,
    (SELECT COUNT(*) FROM Flow F 
        WHERE F.id_request = R.id_request AND processStatus = 1) AS flowTotal,
    (SELECT COUNT(*) FROM Flow F 
        WHERE F.id_request = R.id_request AND processStatus = 2) AS flowApproved
FROM Request R
Tagged
Oct 04 2013
Leave a comment
IT Stuff

No suitable driver found for jdbc:mysql://localhost:3306/schema

If the exception “Could not get JDBC Connection; nested exception is java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/schema” is raised probably you forgot to add the

<property name="driverClassName"><value>com.mysql.jdbc.Driver</value></property>

property to your dataSource bean:

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">		
	<property name="driverClassName"><value>com.mysql.jdbc.Driver</value></property>
	<property name="url"><value>jdbc:mysql://172.16.0.11:3306/test_vale</value></property>
	<property name="username"><value>root</value></property>
	<property name="password"><value>password</value></property>
</bean>
Tagged , , ,
Sep 20 2012
Leave a comment
IT Stuff

Row count for all tables in schema

Counting all of the rows in a schema can require code that actually counts the table rows so, how do you count up all of the rows for all tables in a schema?

For Oracle with system user:

SELECT TABLE_NAME, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = UPPER('{schema}');

For mySql:

SELECT TABLE_NAME, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{schema}';
Tagged ,