If you are facing the following error

1	ERROR 1118 (42000) at line xxxxx: The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size.

Or even, if you just want to increase the memory limit in order to avoid further pains in you head, you just need to add the following lines.

1 2 3 4 5

cd /etc/mysql/ sudo gedit my.cnf # Add this line at the bottom of the file innodb_log_file_size=2GB

Moreover, you could be facing this other problem:

1	ERROR 2006 (HY000): MySQL server has gone away

which is owing to lack of memory in mysql service.

That could be solved as simple as adding the following lines on my.cfg (to get it path, you can do “sudo find / -name my.cnf”)

1 2 3 4 5

cd /etc/mysql/  # Or the corresponding path to the used my.cnf sudo gedit my.cnf # Add this line at the bottom of the file max_allowed_packet=2GB

Or you can do it termorarily such as

1	mysql> set global max_allowed_packet=2GB;

If you are running the 64bit server, there was a little bug in the 64bit version, and you would see those errors at the end of your backup script(source of mysql dump):

1 2 3 4 5 6 7 8 9

ERROR 1231 (42000): Variable ‘time_zone’ can’t be set to the value of ‘NULL’ ERROR 1231 (42000): Variable ‘sql_mode’ can’t be set to the value of ‘NULL’ ERROR 1231 (42000): Variable ‘foreign_key_checks’ can’t be set to the value of ‘NULL’ ERROR 1231 (42000): Variable ‘unique_checks’ can’t be set to the value of ‘NULL’ ERROR 1231 (42000): Variable ‘character_set_client’ can’t be set to the value of ‘NULL’ Query OK, 0 rows affected (0.00 sec) ERROR 1231 (42000): Variable ‘collation_connection’ can’t be set to the value of ‘NULL’ ERROR 1231 (42000): Variable ‘sql_notes’ can’t be set to the value of ‘NULL’

To fix it, you need to do the following:

1 2 3 4 5 6 7

cd /etc/mysql/ sudo gedit my.cnf # Change [mysqld] # To [mysqld64]

Or you can try adding at the top of your dump file, the following code:

1 2 3 4 5 6 7 8 9 10

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

If you see the following error:

1	"SQLSTATE[HY000]: General error: 1114 The table '<table_name>' is full … "

you need to increase the rows limits of your table, to do so, lets run the following:

1	ALTER TABLE <table_name> MAX_ROWS=<new_max_number_of_rows> AVG_ROW_LENGTH=<new_avg_row_length>;

You have to specify AVG_ROW_LENGTH only for tables with BLOB or TEXT columns.