Database error: data inserted in one transaction is greater than 10% of redo log size

Question

I encounter an issue with Drupal Content type : when I create a new field type (ex : text) in a personalised Content type which already have ~10 field types, the website become unavailable and I get this message

The website encountered an unexpected error. Please try again later." instead of the website's pages.

If I don't leave page and click on "delete" (the new field type), it's OK I get back the website. If I let it, then after refreshing the page, the whole website become unreachable and the only way to make it work again is to restore a save of the database on my website host.

I tried to add a new field several time in two different Content type and the same result arrive. It looks like if there is a limitation in the number of field to be created (I only make approx. ~25 max. for the whole website...).

Could anyone help me to solve this issue ?

Here is the begining of the 'long' error message log :

The website encountered an unexpected error. Please try again later. Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42000]: Syntax error or access violation: 1118 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.: INSERT INTO {watchdog} (uid, type, message, variables, severity, link, location, referer, hostname, timestamp) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7, :db_insert_placeholder_8, :db_insert_placeholder_9); Array ( [:db_insert_placeholder_0] => 5 [:db_insert_placeholder_1] => php [:db_insert_placeholder_2] => %type: @message in %function (line %line of %file). [:db_insert_placeholder_3] => a:6:{s:5:"%type";s:45:"Drupal\Core\Database\DatabaseExceptionWrapper";s:8:"@message";s:1062480:"SQLSTATE[42000]: Syntax error or access violation: 1118 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.: INSERT INTO {cache_default} (cid, expire, created, tags, checksum, data, serialized) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6) ON DUPLICATE KEY UPDATE cid = VALUES(cid), expire = VALUES(expire), created = VALUES(created), tags = VALUES(tags), checksum = VALUES(checksum), data = VALUES(data), serialized = VALUES(serialized); Array ( [:db_insert_placeholder_0] => views_data:en [:db_insert_placeholder_1] => -1 [:db_insert_placeholder_2] => 1513178721.199

And there is plenty of text... And the last paragraph is that :

Drupal\dblog\Logger\DbLog->log(3, '%type: @message in %function (line %line of %file).', Array) (Line: 104)
Drupal\dblog\Logger\DbLog->log(3, '%type: @message in %function (line %line of %file).', Array) (Line: 136)
Drupal\Core\Logger\LoggerChannel->log(3, '%type: @message in %function (line %line of %file).', Array) (Line: 65)
Drupal\Core\EventSubscriber\ExceptionLoggingSubscriber->onError(Object) (Line: 92)
Drupal\Core\EventSubscriber\ExceptionLoggingSubscriber->onException(Object, 'kernel.exception', Object) (Line: 108)
Drupal\Component\EventDispatcher\ContainerAwareEventDispatcher->dispatch('kernel.exception', Object) (Line: 230)
Symfony\Component\HttpKernel\HttpKernel->handleException(Object, Object, 1) (Line: 79)
Symfony\Component\HttpKernel\HttpKernel->handle(Object, 1, 1) (Line: 57)
Drupal\Core\StackMiddleware\Session->handle(Object, 1, 1) (Line: 47)
Drupal\Core\StackMiddleware\KernelPreHandle->handle(Object, 1, 1) (Line: 99)
Drupal\page_cache\StackMiddleware\PageCache->pass(Object, 1, 1) (Line: 78)
Drupal\page_cache\StackMiddleware\PageCache->handle(Object, 1, 1) (Line: 47)
Drupal\Core\StackMiddleware\ReverseProxyMiddleware->handle(Object, 1, 1) (Line: 50)
Drupal\Core\StackMiddleware\NegotiationMiddleware->handle(Object, 1, 1) (Line: 23)
Stack\StackedHttpKernel->handle(Object, 1, 1) (Line: 657)
Drupal\Core\DrupalKernel->handle(Object) (Line: 19)

Composed

Pretty clear I think. Research that SQLSTATE. – Emprise

Could you give me hints on how to do that please ? – Composed

Try searching the web, including this site and perhaps places like dba.stackexchange.com, for this error message. It is trying to tell you how to fix it on the DB side. – Emprise

Ok, I beging to understant a bit more what I have to do ; after some researches, it seems that I have to increase innodb_log_file_size to 256MB. But, I totally don't know how to do that and where to look for tips... Any hints ? – Composed

This becomes a bit of a different question, and now involves MySQL which isn't even mentioned here. It sounds like you need to search on "innodb_log_file_size" and read the MySQL refs for your version. The first hit I get for this text is a discussion of how to set it. I don't think you are running into anything here that has not been discussed before, so my advice is to exhaust those research possibilities first. – Emprise

Apparently you have a large TEXT or BLOB column. Please elaborate on this aspect. – Abadan

Answer 1

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

[mysqld]
innodb_log_file_size=256M

[mysqld] 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

Psalter

Thank you very much for this solution proposal ! I will try it. But, to begin, let me know : to do that I must use MySQL software and find how to access to my database ? I am on a mutualised server (OVH) ; I think I will have to add a 'Private SQL' option. Am I in the good path ? – Composed

I assume you use Debian/Ubuntu based hosting server and have sudo privileges to edit server configs. – Psalter

... OK, I have to find a way to do theses servers configs ; don't hesitate to give me 'hints for beginer' if you see some relevant ones. – Composed

Try to contact hosting company support team and ask what they can do for you. Probably your hosting plan is limited and they suggest you to pay more )). As an alternative, you can run webserver locally, set everything as you need and then export-import configs to production server. – Psalter

In old versions, it is not as simple as changing the setting and restarting. What version are you using? – Abadan

Answer 2

If you're using windows and xampp, Do the below changes in C:\xampp\mysql\bin\my.ini file

innodb_log_file_size = 512M

Airboat

And Don't forget to restart mysql. – Airboat