The user password in MySQL is stored in the user table, the password reset is actually to change the value of record in this table. To change the password in case the password is forgotten, the idea is to bypass the authentication of MySQL and get into the system and update the record password value with SQL command.In MySQL 5, one can start MySQL service with --skip-grant-tables option, this option will tell the service to skip loading the grant tables when starting, hence the root user can login with empty password.mysqld –skip-grant-tables After login, can run below SQL command t...

Oracle database is the most popular database system among enterprises. To start working on Oracle database, some concepts must be understood first. They include database, instance, schema and user etc. And among these concepts, some have different meanings from those in other database systems such as MS SQL, MySQL, DB2.On the web, there are already some good posts which explain different concepts such as Ask Tom's database vs instances, Difference between database vs user vs schema. In this post, these concepts with be summarized based on real application development experience.Database : A co...

When working with Hibernate and MySQL, sometimes some exceptions will be thrown after sometime. The exception may seem like :java.sql.SQLException: Data source rejected establishment of connection, message from server: "Too many connections"This means there are too many active connections on the MySQL, you can useSHOW STATUS LIKE '%Threads_connected%';to check the active connections to MySQL. If you want to change the maximum connections allowed to MySQL. You can execute:set global max_connections = [num];You may forget to manually call session.close() methods iin your codes. If you have done ...

Yesterday, my friend Liu Bing asked me a question about how to select top 3 values for each person in a database table using SQL. I think this question is interesting and it deserves some thoughts. Here I record down how to solve this issue.Assume we have a table which has two columns, one column contains the names of some people and the other column contains some values related to each person. One person can have more than one value. Each value has a numeric type. The question is we want to select the top 3 values for each person from the table. If one person has less than 3 values, we select...

Oracle database is now a mature and popular RDBMS which is used by lots of enterprises. With its evolution, many utilities and applications are developed around it as well. Now Oracle database also provides cloud support. As a DBA, managing Oracle database is daily routine work and some tools may be used frequently. One of them is sqlplus, the command line tool for executing SQL command.sqlplus is installed while the Oracle database is installed. To start sqlplus, one just needs to open a terminal and then type sqlplus. Then it will connect to the default schema and will prompt you to enter th...

To connect to MySQL database on Windows through Command line, there are some steps to be followed.1. You need to start the MySQL service, you can go to Start->Control Panel->System and Securities->Administrative Tools->Component Service->Services(local), then on the right panel, you can find one service name called MySQL and you should start this service2. Go the the MySQL installation folder, which is something like this : C:\Program Files\MySQL\MySQL Server 5.1. In this folder, find the bin folder.3. Open a command console and change the working directory to C:\Program Fi...

We all know that DBMS (database management system) is used to store (a massive amount of) data. However, have you ever wondered how is data stored in DBMS? In this post, we will focus on data storage in RDBMS, the most traditional relational database systems.Physical StorageData can be stored in many different kinds of medium or devices, from the fastest but costy registers to the slow but cheap hard drives, or even magnetic tapes. Nowadays, IaaS providers such as AWS even provides services such as S3 Glacier as a low-cost archiving storage solution. The...

DbVisualizer is a very popular GUI application to view the data stored on a database. It supports various databases such as Oracle, MySQL etc. It relies on different database drivers to connect to the database server and access the data stored there. In this post, we will try to solve one problem when accessing the latest MySQL database(8.0+) through DbVisualizer. MySQL database starting from 8.0 has lots of changes which may not be compatible with old version of software or libraries which interact with it. There are also lots of issues reported while accessing the 8.0+ version of MySQL ...