
INCREASE MEMORY LIMIT, LOG FILE SIZE, AND SOME TRICKS ON MYSQL SERVICE
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.
12345 cd /etc/mysql/sudo gedit my.cnf# Add this line at the bottom of the fileinnodb_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”)
12345 cd /etc/mysql/ # Or the corresponding path to the used my.cnfsudo gedit my.cnf# Add this line at the bottom of the filemax_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):
123456789 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:
1234567 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:
12345678910 /*!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.