MySQL JDBC

• JDBC 드라이버 : com.mysql.jdbc.Driver
• Download JDBC 드라이버 : http://www.mysql.com/products/connector/j/
• JDBC URL for utf-8 :

  jdbc:mysql://localhost:3306/DB명?useUnicode=true&characterEncoding=utf8

• MySQL Connector/J Reference Configuration Properties

• 일반적인 데이터베이스의 Schema 개념을 지원하지 않기 때문에 JDBC DatabaseMetaData 에서 schema 관련해서 항상 null 값이 반환되는 문제가 발생할 수 있다. catalog 개념을 사용해야 한다.MySQL :: Re: catalog versus schema

> You're correct. For legacy reasons (including compatibility with ODBC, and SQL Server), JDBC's concept of "catalog" maps to MySQL's concept of "databases". 
> Starting in MySQL-4.1, "schema" is an alias for "database", but it doesn't act like a SQL-standard schema, so we don't support the concept in the JDBC driver. 

• useServerPrepStmts=true : 서버사이드 PreparedStatement를 사용하도록 한다.
  • MySQL JDBC 드라이버 기본은 ClientPreparedStatement : 이는 MySQL에만 있는 개념으로 사실은 PreparedStatement를 흉내낸 것일 뿐 진짜 PreparedStatement가 아니다.
  • MySQL 5.0 이하에서는 PreparedStatement 사용으로 인한 이점이 거의 없어서 ClientPreparedStatement로 사용해도 무관하다.
  • MySQL 5.1 이상에서는 제대로 PreparedStatement가 서버사이드에서 구현되었고 PrepraedStatement에도 쿼리 캐시가 적용되었다. - 근데 테스트해보니 안빠르고 오히려 더 느린데?? 테스트 철저히 하고 사용여부 결정할 것.
• PreparedStatement 사용 분석

  SELECT * FROM information_schema.global_status
  WHERE variable_name IN ('Com_stmt_prepare', 'Com_stmt_execute', 'Prepared_stmt_count');

  • Com_stmt_prepare : Connection.preparedStatement() 호출 횟수라고 보면 된다.
  • Com_stmt_execute : 프리페어 스테이트먼트로 쿼리가 실행된 횟수
  • Prepared_stmt_count : 서버에 저장돼 있는 프리페어 스테이트먼트 갯수. MySQL 5.1.14.

• rewriteBatchedStatements=true&useServerPrepStmts=false : addBatch 구문을 INSERT INTO tablename values (), (), …' 형태로 변경한다.
  • useServerPrepStmts=false 현재 MySQL 서버가 불안정한지 rewriteBatchedStatements=true일 때는 이 값을 false로 해야 좋다.
• rewriteBatchedStatements=true 상황에서 addBatch 적정 레코드 수는 (12 * 1024) / 평균레코드크기 로 구한다.즉, 누적 데이터가 12kb일 때 최고 성능을 발휘한다고 한다.

• MySQL Connector/J에서 Statement.setFetchSize(int) 는 정상작동하지 않는다.
• 기본적으로 모든 쿼리 결과 데이터를 한꺼번에 전송받아 메모리에 올린 상태로 ResultSet에 접근한다.
• 이를 서버 커서 방식으로 바꾸려면 JDBC URL에 useCursorFetch=true?defaultFetchSize=100 형태로 옵션을 추가해야 한다.
  • Server Cursor를 사용하면 자동으로 useServerPrepStmts=true 상태가 된다.

• MySQL :: MySQL Connector/J 8.0 Developer Guide :: 6.3 Configuration Properties
• mysql JDBC Driver 는 useSSL=true 가 기본값이며, 이때 상황에 따라 SSL을 사용했다 안했다가 한다.
• 이는 개발자가 모르는 사이에 SSL 인증서 만료등이 발생했을 때 문제 소지가 있다.
• 8.0 드라이버 부터는 sslMode프라퍼티로 변경된다.
• useSSL=false|false, sslMode=DISABLED 로 항상 원하는 상태를 명시할것.

• 0000-00-00 00:00:00 인 날짜가 존재할 경우 JDBC 드라이버 입장에서는 말도 안되는 시간이다. 따라서 오류가 발생한다.
• zeroDateTimeBehavior=convertToNull 를 JDBC URL에 추가해주면 이 시간을 null로 간주한다.
• 그냥 오류를 발생시키고 잘못된 데이터를 보정하는 것이 맞을 것 같다.

zeroDateTimeBehavior
What should happen when the driver encounters DATETIME values
that are composed entirely of zeros (used by MySQL to represent invalid dates)?
Valid values are "exception", "round" and "convertToNull".
Default: exception 

• 계속 사용해본 결과 Replication Driver는 사용하지 않는 것이 좋겠다 → 2023년 현재 최신 드라이버는 괜찮을 수 있음..
  • Java 사용시에는 Java JDBC Replication Connection Pool(DataSource) 을 참조한다.
  • read/write 중 한쪽작업으로 몰릴 경우 Connection Pool입장에서는 어쨌든 사용중인 커넥션이므로 ping 쿼리가 다른쪽으로는 날라가지 않아서 간헐적으로 사용되는 커넥션이 끊기는 일이 발생한다. (근본적으로 ping 없이 connection 유지 시간을 끊기는 시간보다 작게 잡는게 더 좋을것 같긴 하다)
  • ping이 안날라가서 끊기는 문제는 Proxy나 MySQL서버의 접속 끊는 시간 이전에 커넥션 풀에서 커넥션을 무조건 끊고 다시 맺는 옵션을 주면 해결된다.(maxLifeTime)
  • 일반적으로 Master보다는 Slave에 커넥션이 많이 필요한데 이 드라이버를 사용하면 항상 동수의 커넥션을 맺는다. 따라서 Master에 불필요한 커넥션 수 증가가 발생한다.
  • JVM의 메모리 점유율이 높아지는 것 같다. ReplicationDriver를 사용하면 Full GC 빈도가 더 높아지는 현상이 발견되었다.
  • ping 문제만 해결하면 사용 자체에 큰 문제는 없다. → 해결됨.

• MySQL :: MySQL Connector/J 8.0 Developer Guide :: 9.4 Configuring Source/Replica Replication with Connector/J
• Connection.setReadOnly()에 지정된 값에 따라 쓰기일 때는 마스터, 그 외에는 슬레이브로 보내는 처리를 해준다. Spring @Transactional(readOnly=“true|false”)
  • MySQL Master/Slave Load Balancing with JPA and Spring 단순히 @Transactional만으로는 Master/Slave를 제대로 찾지 못한다는 글. → Spring 4.1 미만에서는 JPA의 @Transactional이 connection.setReadOnly()를 호출하지 않음. 호출하게 강제로 해주던가 4.1 이상을 사용하던가..
• com.mysql.jdbc.ReplicationDriver JDBC 드라이버 사용
  • Connector/J 5.0.6 버전에서는 그냥 기본 드라이버로 설정해도 된다. 기본 드라이버가 커넥션 요청이 들어올 때 Replication URL 이면 자동으로 리플리케이션 드라이버에게 위임한다.
• JDBC URL :

  jdbc:mysql:replication://master,slave1,slave2,slave3/test

  • roundRobinLoadBalance=true : 슬레이브들 간에 Load Balancing 파라미터
  • autoReconnect=true : 커넥션 끊겼을 때 자동 재접속
  • autoReconnectForPools=true
  • failOverReadOnly=true
• To enable this functionality, use the com.mysql.jdbc.ReplicationDriver class when configuring your application server's connection pool or when creating an instance of a JDBC driver for your standalone application. Because it accepts the same URL format as the standard MySQL JDBC driver, ReplicationDriver does not currently work with java.sql.DriverManager-based connection creation unless it is the only MySQL JDBC driver registered with the DriverManager.
• Ping용 쿼리를 주로 SELECT 1을 사용하는데 ReplicationDriver 사용시에는 문제가 된다. 항상 Master/Slave 두개의 커넥션을 유지하는데 이 경우에는 한쪽으로만 쿼리가 실행되기 때문에다. /* ping */ SELECT 1 이라고 해야 JDBC의 ping() 함수를 모든 커넥션에 실행한다.MySQL Bug #22643 fixed!
• java.sql.SQLException: No database selected 에러가 발생한다면, JDBC URL에 database를 지정하지 않았기 때문이다. MySQL 기본 드라이버는 DB를 지정하지 않고 쿼리에서 DB명을 명시해도 괜찮지만 replication 드라이버는 이 경우 오류를 냄.
• MySQL replication driver 사용시 문제될 수 있는 요소 → slave 다운시에는 괜찮으나, master 다운시에는 재접속을 하지 못한다. master가 재시작이 되어도 slave에 대한 접속이 복구가 안된다.
  • 관련 해결책이 5.1.27 에 나온듯.
  • allowMasterDownConnections=true

• http://www.hivedb.org/

• MySQL Connector/J reference configuration properties
• MySQL 5.1.0부터 지원하는 clientInfoProvider : 기본으로 JDBC4CommentClientInfoProvider로 설정되며, JDBC URL 파라미터에서 clientInfoProvider 값을 변경하여 조정할 수 있다. Replication Driver는 5.1.29 버전 현재 이 스펙을 구현하지 않고 있음.
• JDBC 참조.

• hello world » JDBC Internal - 타임아웃의 이해
• SocketTimeout이나 ConnectTimeout을 설정하지 않으면 네트워크 장애가 발생해도 애플리케이션이 대부분 이를 감지할 수 없다. 따라서 연결이 되거나 데이터를 읽을 수 있을 때까지 애플리케이션이 무한정 기다리게 된다. –> 따라서 꼭 socketTimeout을 지정하자.
• mysql jdbc driver url의 connectTimeout과 socketTimeout
• connectTimeout : Connection timeout in milliseconds. 소켓 연결을 맺기까지의 timeout.
• socketTimeout : Socket Operation timeout in milliseconds. server → client 전송 timeout.

• MySQL이 생성하는 모든 쿼리의 로그를 남길 수 있다.
• logger=com.mysql.jdbc.log.Slf4JLogger : 로거 지정
• profileSQL=true이면 로그로 모든 쿼리를 남기고 성능 지표도 함께 표시한다. 정확히는 프로파일링 하라라는 뜻이고, profilerEventHandler가 그걸 로그로 남기는 역할을 하는 듯 보임.
• MySQL :: MySQL Connector/J 8.0 Developer Guide :: 6.3.14 Debugging/Profiling
• logSlowQueries=true 및 하위 설정으로 slow query 자동 설정가능
  • slowQueryThresholdMillis
  • explainSlowQueries=true

• MySQL :: MySQL Connector/J Developer Guide :: 6.3.3 Session
• connection 을 맺을 때마다 세션에 대해 SET [SESSION] xxx=yyy 형태로 설정하는 값들을 JDBC URL에 지정 가능하다.
• https://stackoverflow.com/a/62582767

jdbc:mysql://[host:port]/[database]/?sessionVariables=&&key1=value1,key2=value2

jdbc:mysql://[host:port]/[database]/?sessionVariables=key1=value1,key2=value2

• MySQL JDBC는 기본적으로 하나의 SQL 실행 요청에 여러 SQL을 실행하는 것을 금지하고 있다.(세미콜론(;)으로 여러 SQL 전송 불가)
• 실서비스에서는 사용하지 말고, 테스트시에 필요한 경우 allowMultiQueries로 허용 가능하다.
• allowMultiQueries=true

• JDBC 드라이버를 통해 각 라이프사이클별 인터셉터를 끼워넣을 수 있다.
• Connector J Interceptors
• connectionLifecycleInterceptors, where you specify the fully qualified names of classes that implement the com.mysql.jdbc.ConnectionLifecycleInterceptor interface. In these kinds of interceptor classes, you might log events such as rollbacks, measure the time between transaction start and end, or count events such as calls to setAutoCommit().
• exceptionInterceptors, where you specify the fully qualified names of classes that implement the com.mysql.jdbc.ExceptionInterceptor interface. In these kinds of interceptor classes, you might add extra diagnostic information to exceptions that can have multiple causes or indicate a problem with server settings. Because exceptionInterceptors classes are only called when handling a SQLException thrown from Connector/J code, they can be used even in production deployments without substantial performance overhead.
• statementInterceptors, where you specify the fully qualified names of classes that implement the com.mysql.jdbc.StatementInterceptorV2 interface. In these kinds of interceptor classes, you might change or augment the processing done by certain kinds of statements, such as automatically checking for queried data in a memcached server, rewriting slow queries, logging information about statement execution, or route requests to remote servers.

// 이 코드의 클래스는 package가 com.mysql.jdbc
// Mockito.mock 사용
DatabaseMetaData metaData = new DatabaseMetaData(mock(MySQLConnection.class), null);
log.info("Version : {}, {}", metaData.getDriverName(), metaData.getDriverVersion());

com.mysql.jdbc.Driver 클래스에도 getMajorVersion(), getMinorVersion() 메소드가 있지만 마지막 subMinorVersion을 알 수 있는 방법은 없다.

• MySQL :: MySQL Connector/J Developer Guide :: 7 Connection Pooling with Connector/J에서 Validation Query 항목 검사.
• 쿼리가 /* ping */ 으로 시작하면 MYSQL JDBC 드라이버는 이 쿼리를 실제로 날리지 않고, MySQL 서버에 살아있는지 여부만 체크하는 통신을 수행한다. Statement, PreparedStatement 상관없이 작동한다.
• 공백하나 다른 것 없이 정확히 /* ping */와 완전히 동일한 문자열로 시작하는 쿼리여야 한다.
• ReplicationConnection이나 LoadBalancedConnection 사용시에는 연결된 모든 커넥션이 ping을 실행한다.
• JDBC4 의 Connection.isValid(int)도 동일한 역할을 한다.

• MySQL :: MySQL Connector/J Developer Guide :: 5.3 Java, JDBC and MySQL Types

JDBC Driver의 tinyInt1isBit=true 일 경우(기본값이 true임), tinyint(1)을 JDBC 드라이버에서 Boolean으로 리턴한다.