Tag: mysql

maven September 25, 2023 by mkyong 1 comment

mysql-connector-java: unknown was not found

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 […]

mysql August 9, 2023 by mkyong 0 comments

How to install MariaDB on Debian 11

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 […]

jdbc July 5, 2019 by mkyong 14 comments

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

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’ […]

spring boot March 25, 2019 by mkyong 6 comments

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

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 […]

spring boot March 23, 2019 by mkyong 3 comments

Spring Boot + Spring Data JPA + MySQL example

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 […]

jdbc March 21, 2019 by mkyong 5 comments

JDBC PreparedStatement SQL IN condition

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 […]

unix / linux March 7, 2019 by mkyong 5 comments

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

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 […]

mysql November 4, 2018 by mkyong 0 comments

Upgrade MySQL 5.5 to MySQL 5.7

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 […]

mysql May 2, 2018 by mkyong 0 comments

How to check MySQL version

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 […]

mysql January 2, 2018 by mkyong 0 comments

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

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 […]

php November 16, 2017 by mkyong 1 comment

PHP + Windows – Call to undefined function mysql_connect()

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 […]

mysql February 14, 2017 by mkyong 15 comments

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

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. […]

spring boot February 12, 2017 by mkyong 8 comments

Spring Boot JDBC + MySQL + HikariCP example

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 […]

mysql July 27, 2016 by mkyong 0 comments

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

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 […]

mysql April 28, 2014 by mkyong 4 comments

Can’t delete records in MySQL Workbench

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 […]

spring batch July 28, 2013 by mkyong 20 comments

Spring Batch Example – MySQL Database To XML

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 […]

jdbc January 11, 2013 by Dhina Prakash 17 comments

How to run a MySQL Script using Java

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; […]

tomcat December 7, 2010 by mkyong 21 comments

How to configure MySQL DataSource in Tomcat 6

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 […]

spring March 31, 2010 by mkyong 149 comments

Maven + (Spring + Hibernate) Annotation + MySql Example

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 […]

spring March 23, 2010 by mkyong 202 comments

Maven + Spring + Hibernate + MySql Example

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 […]

mysql January 30, 2010 by mkyong 11 comments

How to define one-to-one relationship in MySQL

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 […]

hibernate December 30, 2009 by mkyong 44 comments

Maven 2 + Hibernate 3.2 + MySQL Example (Annotation)

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 […]

hibernate December 17, 2009 by mkyong 85 comments

Maven 2 + Hibernate 3.2 + MySQL Example (XML Mapping)

Note This article is outdated, please refer to the latest Hibernate 3.6 tutorial at – Maven 3 + Hibernate 3.6 + Oracle 11g Example (XML Mapping). This quick guide show you how to use Maven to generate a simple Java project, and uses Hibernate to insert a record into MySQL database. Tools & technologies used […]

mysql August 29, 2009 by mkyong 27 comments

How to calculate the MySQL database size

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 […]

mysql August 27, 2009 by mkyong 1 comment

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

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 […]

mysql August 27, 2009 by mkyong 12 comments

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

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 […]

mysql August 27, 2009 by mkyong 29 comments

Where does MySQL stored the data in my harddisk?

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 […]

jdbc August 19, 2009 by mkyong 95 comments

Connect to MySQL with JDBC driver

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; […]