Přenos MySQL – greater than 10% of redo log size

Dobrý den, Potřebují přenést databázi na nový web a phpmyadmin mi hází “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. Zkoušela jsem zvýšit innodb_log_file_size, bigdump, i importovat jednotlivě, ale nic z toho nešlo. Prostě pár řídků z _posts to nebere kvůli velikosti a i bigdump se to vždy po určitých řádcích zastaví. Poradí mi někdo prosím co s tím? Děkuji

Search for config file:

sudo find / -name my.cnf

By default config is located here: /etc/mysql/my.cnf Search for innodb_log_file_size in mysql directory sudo grep -rl "innodb_log_file_size" /etc/mysql Just to check if you have something already configured Open my.cnf: sudo nano /etc/mysql/my.cnf You can edit this file and add innodb_log_file_size=256M  is the section name, use ctrl+W to search in nano, don't add it twice! OR better create  /etc/mysql/conf.d/innodb.cnf  and place the above innodb config there. NB! Ensure you have !includedir /etc/mysql/conf.d/ string in the  my.cnf  file for custom config to be included.

sudo service mysql restart Pokud nestačí 256M, tak třeba 512M---------- Příspěvek doplněn 06.06.2022 v 17:36 ----------

19

Before I ask my question a little background: I'm doing the Data Export/Import using the MySQL Workbench 6.1 of a MySQL 5.5 database from one machine to a 5.6 on another. both machines are ubuntu one 32-bit the other 64-bit. I dump the data no problem, but when I try to load it I get the:

ERROR 1118 (42000) at line 1807: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

Here is the table create: CREATE TABLE `file_content` (   `fileid` bigint(20) NOT NULL,   `content` LONGBLOB NOT NULL,   PRIMARY KEY (`fileid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; I have the following relevant my.cnf settings ...

max_allowed_packet=1G

innodb_file_per_table=1

innodb_file_format=Barracuda

I spend a lot of googling time trying to figure out what the problem is. BTW if I remove the primary key (no problem here, but another table that has a foreign key into this table complains. As luck would have it, I have the ssh access to the box so I can see actual mysqldb.log. why I find there is really interesting ...

2014-08-12 20:42:12 25246 InnoDB: The total blob data length (14179167) is greater than 10% of the redo log file size (3072). Please increase innodb_log_file_size.

So increasing the redo log file size to 10x LONGBLOB size fixed my issue. However does that mean that to insert a 1G LONGBLOB (thats the actual maximum because of the packet size) I will need a 10G innodb_log_file_size. Can anyone explain how "redo log size error" turns into "row size too large (>8126)" error. BTW I have no control over the structure of this db so no "why are you storing large blobs in the database". TIA