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 ...

After installing MySQL, when we try to start MySQL, sometimes we may not be able to start it. The reasons can be different. We share some general cases where MySQL cannot be started.Case 1: Directory or file permission issueIf the permission is set wrongly in MySQL's $datadir and its sub directories or files, MySQL will not be able to read and write files normally.Error message:mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data/usr/local/mysql/bin/mysqld_safe: line 107: /usr/local/mysql/data/imysql.local.err: Permission deniedCase 2: Port conflictThere may be other my...

The biggest movement of escaping from MySQL in this century starts, openSUSE,Fedora and Arch have started to use MariaDB instead of MySQL as their default database. Many people also dislike the attitude of Oracle on MySQL, so it's reasonable to migrate from MySQL to MariaDB. The whole process is not complicated. Here we share the steps to migrate from MySQL to MariaDB in Ubuntu.Installation procedure:sudo apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943dbModify /etc/apt/sources.list, add:# MariaDB 5.5 repository list - created 2013-04-08 14:44 UTC# http://mariadb.org/m...

If you want to debug the issue of "MySQL server has gone away", you can reproduce it with below steps:Modify configuration file:sudo vi /etc/mysql/my.cnf  Make below changes:[mysqld]  wait_timeout = 30  interactive_timeout = 30  Restart the service:sudo /etc/init.d/mysql restart  Write below PHP codes:$link = mysql_connect('127.0.0.1', 'root', 'root');  if (!$link) {      die('Could not connect: ' . mysql_error());  ...

Database access speed is always the bottle neck of many applications. Many application have large amount of data to search, retrieve and display nowadays. How do we improve the performance of our applications, how do we reduce the cost of database access? Apart from the design of database, the quality of the query is also one important factor to take care.  But before that, we need to know how much network traffic a query will consume.Yunyang,Zhang from Nubee in Singapore did some research about MySQL query cost in his post "How Much Network Traffic Does A MySQL Query Consume?". Let's hav...

For both small free database space and large e-commerce websites, reasonable database table structure design is essential. To achieve this, it requires us to have a full understanding of commonly used data types in database system. Below we share some knowledge about data types in MySQL.1. Numeric typesThe numeric types can be classified as : integer, float and decimal type.The so-called "decimal" refers DECIMAL and NUMERIC, they are of the same type. Strictly speaking it is not a numeric type, because they are actually stored as strings; Every single digit of its value(including the decimal p...

Problem description:We need to access a very big table which has 40 million records. In the table, id is the primary key and program_id is indexed.When executing a select query:select * from program_access_log where program_id between 1 and 4000The above query executes very slowly, we thought it was because there were too many records in the table. So we added id in the where condition to limit the search so that each time only half a million records would be read.select * from program_access_log where id between 1 and 500000 and program_id between 1 and 4000But this query is st...

In some situations, MySQL will just use an index to fulfill the requirement of an ORDER BY or GROUP BY statement without extra sorting.Although ORDER BY will not have the exact match with index, index can still be used as long as the portion that is not included in the index is included in the where clause.The following queries will all use index to process the ORDER BY or GROUP BY part:SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2;SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2;SELECT * FROM t1 ORDER BY key_part1 D...