mysql

mysql-connector-java: unknown was not found

by

The IntelliJ cannot find the mysql-connector-java dependency and hits error "unknown was not found"? pom.xml <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> Terminal mysql:mysql-connector-java:jar:unknown was not found in https://repo.maven.apache.org/maven2 during a previous attempt. This failure was cached in the local repository and resolution is not reattempted until the update interval of central has elapsed or updates are …

Read more

How to install MariaDB on Debian 11

by

This tutorial shows you how to install MariaDB on Debian 11. Table of contents: 1. Install MariaDB 2. Verify MariaDB 3. Secure the MariaDB 3.1 Enter current password for root (enter for none): enter 3.2 Switch to unix_socket authentication [Y/n] n 3.3 Remove anonymous users? [Y/n] y 3.4 Disallow root login remotely? [Y/n] y 3.5 …

Read more

java.sql.SQLException: The server time zone value ‘xx time’ is unrecognized

by

Making a JDBC connection to the MySQL server with the latest mysql-connector-java:8.0.16 and hits the following SQLException: Tested with MySQL 5.7 Java 8 JDBC driver, mysql-connector-java 8.0.16 try (Connection conn = DriverManager.getConnection( "jdbc:mysql://127.0.0.1:3306/test", "root", "password")) { //… } catch (Exception e) { e.printStackTrace(); } Output java.sql.SQLException: The server time zone value ‘Malay Peninsula Standard Time’ …

Read more

Spring Boot MySQL : Table ‘DB_NAME.hibernate_sequence’ doesn’t exist

by

Spring Boot + Spring Data JPA + MySQL, and hits the following error message when the application starts : Tested with : Spring Boot 2.1.2.RELEASE mysql-connector-java 8.0.13 Hibernate 5.3.7 java.sql.SQLSyntaxErrorException: Table ‘DB_NAME.hibernate_sequence’ doesn’t exist at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:974) at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1024) at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) at org.hibernate.id.enhanced.TableStructure.executeQuery(TableStructure.java:216) at org.hibernate.id.enhanced.TableStructure.access$300(TableStructure.java:46) application.properties spring.datasource.url=jdbc:mysql://localhost:3306/test spring.datasource.username=mkyong …

Read more

Spring Boot + Spring Data JPA + MySQL example

by

This article shows how to use Spring Web MVC to create REST endpoints to perform CRUD database operations using the Spring Data JPA and MySQL. At the end of the tutorial, we will use Docker to start a MySQL container to test the Spring Boot REST endpoints using curl commands. We will use Spring Test …

Read more

JDBC PreparedStatement SQL IN condition

by

Java JDBC PreparedStatement example to create a SQL IN condition. 1. PreparedStatement + Array In JDBC, we can use createArrayOf to create a PreparedStatement IN query. @Override public List<Integer> getPostIdByTagId(List<Integer> tagIds) { List<Integer> result = new ArrayList<>(); String sql = "SELECT tr.object_id as post_id FROM wp_term_relationships tr " + " JOIN wp_term_taxonomy tt JOIN wp_terms …

Read more

How to backup MySQL / MariaDB database to Amazon S3, shell script example

by

This article shows a shell script to backup or export a MySQL / MariaDB database, gzip the exported file, and upload the gzipped backup file to Amazon S3. Table of contents 1. Install AWS CLI 2. Backup MySQL/MariaDB database to S3 (Shell Script) 3. How to run the backup script? 4. Run the backup script …

Read more

Upgrade MySQL 5.5 to MySQL 5.7

by

On Debian 9.5, the MySQL package is still the old MySQL 5.5. This little guide show you how to upgrade it to MySQL 5.7 1. Download MySQL APT 1.1 Get the MySQL APT and select MySQL 5.7 $ wget https://dev.mysql.com/get/mysql-apt-config_0.8.10-1_all.deb $ sudo gdebi mysql-apt-config_0.8.10-1_all.deb 1.2 Below “configuration mysql-apt-config” screen will be prompted, clicks the first …

Read more

How to check MySQL version

by

In the terminal, we can type mysql -V (uppercase V) to display the current MySQL version installed on the server. Terminal $ mysql -V mysql Ver 8.0.18 for Linux on x86_64 (MySQL Community Server – GPL) The above output show MySQL version 8.0.18 is installed on the server. Server System Variables Alternatively, we can connect …

Read more

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

by

Try to log into the MySQL server, but it prompts the following error message: Terminal $ mysql ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO) 1. Solution – (using password: NO) The user ‘root’ need a password to log into the MySQL server. To solve it, please provide a password with -p …

Read more

PHP + Windows – Call to undefined function mysql_connect()

by

Install WordPress and hits the following error message : Fatal error: Uncaught Error: Call to undefined function mysql_connect() in C:\nginx-1.12.1\www\wordpress\wp-includes\wp-db.php… Tested PHP 7.1.10 WordPress 4.8.3 Nginx 1.12.1 MySQL 5.7.17 Windows 10 Solution To install WordPress, PHP needs Mysql extension, to fix this, loads php_mysqli.dll in php.ini file. php.ini ; Directory in which the loadable extensions …

Read more

MySQL – Establishing SSL connection without server’s identity verification is not recommended

by

Start a Spring Boot application and making a JDBC connection, hits the following warning messages on console : Fri Feb 10 18:43:02 SGT 2017 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. …

Read more

Spring Boot JDBC + MySQL + HikariCP example

by

In this article, we will show you how to create a Spring Boot JDBC application + MySQL and HikariCP. Tools used in this article : Spring Boot 1.5.1.RELEASE MySQL 5.7.x HikariCP 2.6 Maven Java 8 Note Related – Spring Boot JDBC + Oracle database + Commons DBCP2 example 1. Project Structure A standard Maven project …

Read more

How to backup and restore (import and export) MySQL database or table

by

This tutorial will show you how to back up and restore (import or export) MySQL / MariaDB database or tables. Table of contents: 1. MySQL – Backup or export databases and tables 2. MySQL – Restore or import databases and tables 3. Backup and Restore MySQL 4. References 1. MySQL – Backup or export databases …

Read more

Can’t delete records in MySQL Workbench

by

In MySQL workbench, issue a simple delete all commands delete FROM users; But it shows me following error message : Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL …

Read more

Spring Batch Example – MySQL Database To XML

by

In this tutorial, we will show you how to read data from a MySQL database, with JdbcCursorItemReader and JdbcPagingItemReader, and write it into an XML file. Tools and libraries used Maven 3 Eclipse 4.2 JDK 1.6 Spring Core 3.2.2.RELEASE Spring OXM 3.2.2.RELEASE Spring Batch 2.2.0.RELEASE MySQL Java Driver 5.1.25 P.S This example – MySQL jdbc …

Read more

How to run a MySQL Script using Java

by

In this tutorial, I will show you how to run a MySQL script file using ibatis ScriptRunner class. First, download the ibatis and Mysql JDBC Driver, and add the jar files into your classpath. Now, run below code. It will execute a script.sql file. RunSqlScript.java package com.mkyong; import java.io.BufferedReader; import java.io.FileReader; import java.io.Reader; import java.sql.Connection; …

Read more

How to configure MySQL DataSource in Tomcat 6

by

Here’s a guide to show you how to configure MySQL datasource in Tomcat 6. 1. Get MySQL JDBC Driver Get JDBC driver here – http://www.mysql.com/products/connector/ , for example, mysql-connector-java-5.1.9.jar, and copy it to $TOMCAT\lib folder. 2. Create META-INF/context.xml Add a file META-INF/context.xml into the root of your web application folder, which defines database connection detail …

Read more

Maven + (Spring + Hibernate) Annotation + MySql Example

by

Download It – Spring-Hibernate-Annotation-Example.zip In last tutorial, you use Maven to create a simple Java project structure, and demonstrate how to use Hibernate in Spring framework to do the data manipulation works(insert, select, update and delete) in MySQL database. In this tutorial, you will learn how to do the same thing in Spring and Hibernate …

Read more

Maven + Spring + Hibernate + MySql Example

by

This example will use Maven to create a simple Java project structure, and demonstrate how to use Hibernate in Spring framework to do the data manipulation works(insert, select, update and delete) in MySQL database. Final project structure Your final project file structure should look exactly like following, if you get lost in the folder structure …

Read more

How to define one-to-one relationship in MySQL

by

One-to-one relationships occur when there is exactly one record in the first table that corresponds to exactly one record in the related table. MySQL does not contains any “ready” options to define the one-to-one relationship, but, if you want to enforce it, you can add a foreign key from one primary key to the other …

Read more

Maven 2 + Hibernate 3.2 + MySQL Example (Annotation)

by

Note This article is outdated, and some information is no longer valid in latest Hibernate development. You should refer to this latest – Maven 3 + Hibernate 3.6.3 + Oracle 11g Example (Annotation) tutorial. This tutorial will modify the previous Maven 2 + Hibernate 3.2 + MySQL Example (XML mapping), and replace the Hibernate XML …

Read more

How to calculate the MySQL database size

by

As i know, there are two ways to calculate MySQL database size. 1. SQL script Sum up the data_length + index_length is equal to the total table size. data_length – store the real data. index_length – store the table index. Here’s the SQL script to list out the entire databases size SELECT table_schema "Data Base …

Read more

MySQL Error – exceeded the ‘max_questions’ resource (current value: 1000)

by

While i using my little fancy program to simulate millions of data in MySQL database for volumn testing, my program straight hit the following error after execute few seconds. Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: User ‘mkyong’ has exceeded the ‘max_questions’ resource (current value: 1000) at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) at com.mysql.jdbc.Util.getInstance(Util.java:381) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3536) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3468) at …

Read more

How to modify the ‘max_questions’ resource value in MySQL?

by

Often times, the following error is happened. This is due to the MySQL resource monitor feature, the “max_questions” means “Number of queries the user can execute within one hour”. MySQL Error – exceeded the ‘max_questions’ resource (current value: 1000) Here i will demonstrate how to update or modify the ‘max_questions’ value in MySQL. 1) Log …

Read more

Where does MySQL stored the data in my harddisk?

by

Here i provide a simple user guide to find out where does MySQL database stored the data in our hard disk, both in Windows and Linux. Windows 1) Locate the my.ini, which store in the MySQL installation folder. For Example, C:\Program Files\MySQL\MySQL Server 5.1\my.ini 2) Open the “my.ini” with our favor text editor. #Path to …

Read more

Connect to MySQL with JDBC driver

by

A JDBC example to show you how to connect to a MySQL database with a JDBC driver. Tested with: Java 8 MySQL 5.7 MySQL JDBC driver mysql-connector-java:8.0.16 1. Download MySQL JDBC Driver Visit https://dev.mysql.com/downloads/connector/j/ to download the latest MySQL JDBC Driver. 2. JDBC Connection 2.1 Make a connection to the MySQL database. JDBCExample.java import java.sql.Connection; …

Read more