Today's Question:  What does your personal desk look like?        GIVE A SHOUT

 MYSQL


  Why MySQL 8 drops support of query cache

Many of you may have heard or used MySQL's query cache, because it used to be a popular way to improve MySQL's performance. As an important feature for improving MySQL's performance, the query cache was often recommended as a solution for slow queries. However, why has MySQL 8 abandoned the query cache? Today, we will analyze and explore this decision.What is query cache?According to official document:The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retriev...

1,771 1       MYSQL 8 QUERY CACHE


  Guide on recovering data in MySQL

In our daily work, there might be mistakes made which got some data or even databases deleted in MySQL. If this happens on production, it would be a nightmare. In case this happens, normally DBA would jump in to save the world. And they would try to recover the data from the backup if there is any. But if there is no backup, then the show stops.Hence database backup is necessary on production environments to avoid such awkward situation. Also normally in MySQL, binlog should be enabled as well in ROW mode so that recovery can be replayed starting from the backup time point to the incident time...

3,392 0       BINLOG EXAMPLE DATABASE MYSQL BACKUP


  How to reset root password in MySQL 8

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

59,997 5       MYSQL PASSWORD MYSQL 8


  Unknown system variable 'query_cache_size' in DbVisualizer while accessing MySQL

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

11,878 0       MYSQL 8 MYSQL DBVISUALIZER QUERY_CACHE_SIZE


  Workaround size limit of phpMyAdmin import sql file

When doing website development with MySQL, we often need to do database backup and restore. For website, the data in database will grow quickly, so when we back up the database, the size of the generated sql file may be over 80MB which is the max allowed size when we want to import a sql file for restoring our database using phpMyAdmin. To workaround this limit, we need to review the documentation of phpMyAdmin. Fortunately, I found an online article written by David Pratt  which gave us a very simple solution to this problem.Find the config.inc.php file located in the phpmyadmin director...

11,853 0       MYSQL SOLUTION PHPMYSQLADMIN 80M IMPORT LIMIT


  <=> operator in MySQL

Have you ever seen "<=>" in a SQL query while using MySQL? Does it mean less and equals to and greater than? Actually if you consider it as the union of <= and =>, great, you are close to it. This is one form of equal operator in MySQL, it has the similar meaning to the = operator with some subtle difference.According to MySQL documentation, <=> is NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.For example:mysql> SELECT 1 <=> 1,...

3,897 2       MYSQL NULL SAFE <=>


  Solve Hibernate "Too many connections" issue in MySQL

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

26,193 2       MYSQL HIBERNATE CLOS


  Some cases where MySQL cannot be started

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

4,576 0       MYSQL ERROR LOG