Configure MySQL 5.6 LONGBLOB for large binary data

The reason for this issue is a change in MySQL 5.6.20 as one could read in the change log:

As a result of the redo log BLOB write limit introduced for MySQL 5.6, the innodb_log_file_size setting should be 10 times larger than the largest BLOB data size found in the rows of your tables plus the length of other variable length fields (VARCHAR, VARBINARY, and TEXT type fields). No action is required if your innodb_log_file_size setting is already sufficiently large or your tables contain no BLOB data.

Set or increase the value of the innodb_log_file_size option in my.ini below the [mysqld] section. Its default value is 48M. Setting it to

[mysqld]
innodb_log_file_size=256M

Be careful when changing the value of innodb_log_file_size. Follow these steps to do this safely:

  • Shut the server down cleanly and normally.
  • Shutting down MySQL may not be as simple as just service mysql stop!
  • Following things should be done to shut down mysql server normally and cleanly:
    1. Double check the instance you are going to shutdown!!
    2. Stop Replication
    3. Flush the dirty pages
    4. Check the long running transactions
    5. Dump and reload the buffer pool
  • Move away (don’t delete) the log files, which are named ib_logfile0, ib_logfile1, and so on.
  • Check the error log to ensure there was no problem shutting down.
  • Then restart the server and watch the error log output carefully.
    • There should see InnoDB print messages saying that the log files don’t exist. It will create new ones and then start.
  • Verify that InnoDB is working. If it’s working, then the old log files can be deleted.

How to automatically kill slow MySQL queries after N seconds?

Good MySQL related application requires right performance tuning. Without right tuning of MySQL configuration, application connections started failing and application became slowing down.

If you are using persistent connection, there may be so many connections which are running but in ‘Sleep’ mode currently. If you have MySQL >= 5.1, where there is a processlist in the INFORMATION_SCHEMA. Generate the KILL QUERY commands in bulk from within the MySQL client for query running longer than 20 minutes (1200 seconds)

Good MySQL related application requires right performance tuning. Without right tuning of MySQL configuration, application connections started failing and application became slowing down.

If you are using persistent connection, there may be so many connections which are running but in ‘Sleep’ mode currently. If you have MySQL >= 5.1, where there is a processlist in the INFORMATION_SCHEMA. Generate the KILL QUERY commands in bulk from within the MySQL client for query running longer than 20 minutes (1200 seconds):

SELECT GROUP_CONCAT(CONCAT('KILL QUERY ',id,';') SEPARATOR ' ') KillQuery
FROM information_schema.processlist WHERE user<>'system user'
AND time >= 1200

You can do WHERE clauses against the INFO field to look for a specific query, the TIME field against long running queries, or the DB field against a specific database.

To run add this query in crontab, create a shell file with following content:

SECONDS_TOO_LONG=1200
QUERIES_RUNNING_TOO_LONG=`mysql -uroot -ppassword -ANe"SELECT COUNT(1) FROM information_schema.processlist WHERE user<>'system user' AND time >= ${SECONDS_TOO_LONG}"`
if [ ${QUERIES_RUNNING_TOO_LONG} -gt 0 ]
then
    KILLPROC_SQLSTMT="SELECT GROUP_CONCAT(CONCAT('KILL QUERY ',id,';') SEPARATOR ' ') KillQuery FROM information_schema.processlist WHERE user<>'system user' AND time >= ${SECONDS_TOO_LONG}"
    mysql -uroot -ppassword -ANe"${KILLPROC_SQLSTMT}" | mysql -uroot -ppassword
fi

Shell file with above content will kill all queries running more than 1200 seconds (20 mins).

Here is another variation:

SECONDS_TOO_LONG=1200
QUERIES_RUNNING_TOO_LONG=`mysql -uroot -ppassword -ANe"SELECT COUNT(1) FROM information_schema.processlist WHERE user<>'system user' AND time >= ${SECONDS_TOO_LONG}"`
if [ ${QUERIES_RUNNING_TOO_LONG} -gt 0 ]
then
    KILLPROC_SQLSTMT="SELECT CONCAT('KILL QUERY ',id,';') KillQuery FROM information_schema.processlist WHERE user<>'system user' AND time >= ${SECONDS_TOO_LONG}"
    mysql -uroot -ppassword -ANe"${KILLPROC_SQLSTMT}" > /tmp/kill_log_queries.sql
    mysql -uroot -ppassword < /tmp/kill_log_queries.sql
fi

Shell file with above content will kill all queries running more than 1200 seconds (20 mins) with adding all queries in log file and running same log file afterwards.

Resetting forgotten phpmyadmin password

Simply change or reset your MySQL root password in Ubuntu by doing the following:

  1. Stop the MySQL serversudo service mysql stop
  2. Start mysqldsudo mysqld --skip-grant-tables &
  3. Login to MySQL as rootmysql -u root mysql
  4. Change ‘{your_new_password}‘ with your new root passwordUPDATE user SET Password=PASSWORD('{your_new_password}') WHERE User='root';
    FLUSH PRIVILEGES;
    exit;
  5. Kill mysqldsudo pkill mysqld
  6. Start mysqlsudo service mysql start
  7. Login to phpmyadmin as root with your new password

MySQL database difference with MySQL Workbench

MySQL Workbench is one of the best tools to manage databases. You can connect multiple databases and query each database separately. And most importantly, it also synchronizes and find difference of online as well as offline database. In both cases, you have to manage models of offline database.

mysql-workbenchAs a DB Manager, there is always a need for database synchronization or difference tool to upload the latest changes to the server.

For uploading latest files, there are so many tools freely available like SVN. But when there is a time to upload the database changes, there are very few options available for free. MySQL Workbench is one of the free tools provided by Oracle which provides database synchronization as well as database difference facility.

MySQL Workbench is one of the best tools to manage databases. You can connect multiple databases and query each database separately. And most importantly, it also synchronizes and find difference of online as well as offline database. In both cases, you have to manage models of offline database.

It is a unified visual tool which provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, and much more. MySQL Workbench is available for free on Windows, Linux and Mac OS.

MySQL Workbench is used to visually design, model, generate, and manage databases, which includes creating complex ER models, forward and reverse engineering. It also delivers visual tools for creating, executing, and optimizing SQL queries. SQL editor of MySQL Workbench provides color.

MySQL Workbench also delivers key features like performing difficult change management and documentation tasks, which normally requires much time and effort.

New version of MySQL Workbench also provide database migration from popular database servers to MySQL, which includes Microsoft SQL Server, Sybase ASE, PostreSQL, and other RDBMS. It’s easy to use solution for migrating can migrate tables, objects and data to MySQL to quickly and easily convert existing applications to run on windows and other platforms.

For more information about MySQL Workbench, visit http://www.mysql.com/products/workbench/
MySQL Workbench is freely available at http://www.mysql.com/downloads/workbench/.