0

I'm running a staging server on an Ubuntu 16.04,

When I try to save a particular document, I get the following error:

OperationalError at /product/1172
(1118, '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.')

The document contains many text fields with anywhere between 10 - 1000 characters. When I delete some of the text, I could actually save the document just fine. So it's probably character bound.

The interesting bit is that when I try to clone the same document details into my local MYSQL version, it works just fine. So my next logical step was to compare innodb_ global variabls.

I compared and the only variable that was different was innodb_log_buffer_size was set to 16M on local, but 8M on the staging version. So I tried to set it up from mysql.conf under [mysqld] statement.

However, when I try to restart the service for mysql, I get the following error:

Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

And when checking the error.log file, I find this:


2019-08-07T05:42:54.144803Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2019-08-07T05:42:54.144847Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2019-08-07T05:42:54.302070Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please $
2019-08-07T05:42:54.303636Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.21-0ubuntu0.16.04.1) starting as p$
2019-08-07T05:42:54.308390Z 0 [Warning] InnoDB: innodb-page-size has been changed from the default v$
2019-08-07T05:42:54.308451Z 0 [Note] InnoDB: PUNCH HOLE support available
2019-08-07T05:42:54.308460Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-08-07T05:42:54.308464Z 0 [Note] InnoDB: Uses event mutexes
2019-08-07T05:42:54.308468Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory $
2019-08-07T05:42:54.308472Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2019-08-07T05:42:54.308475Z 0 [Note] InnoDB: Using Linux native AIO
2019-08-07T05:42:54.308706Z 0 [Note] InnoDB: Number of pools: 1
2019-08-07T05:42:54.308808Z 0 [Note] InnoDB: Using CPU crc32 instructions
2019-08-07T05:42:54.310251Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances $
2019-08-07T05:42:54.315213Z 0 [Note] InnoDB: Completed initialization of buffer pool
2019-08-07T05:42:54.316859Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleane$
2019-08-07T05:42:54.327052Z 0 [ERROR] InnoDB: Data file './ibdata1' uses page size 16384, but the in$
2019-08-07T05:42:54.327079Z 0 [ERROR] InnoDB: Corrupted page [page id: space=0, page number=0] of da$
2019-08-07T05:42:54.327088Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2019-08-07T05:42:54.928006Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2019-08-07T05:42:54.928037Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2019-08-07T05:42:54.928043Z 0 [ERROR] Failed to initialize builtin plugins.
2019-08-07T05:42:54.928046Z 0 [ERROR] Aborting

2019-08-07T05:42:54.928061Z 0 [Note] Binlog end
2019-08-07T05:42:54.928415Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

I only get this error when trying to add the innodb_log_buffer_sizevariable, not with any other innodb variable.

Is the original OpretaionalError being caused by having a lower buffer variable? If so, how do I safely change it to an appropriate range?

Python: 3.5.2 Running Django

MYSQL: Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine wrapper

Server: Apache 2 on Ubuntu 16.04

PythonBeg
  • 103
  • 10
  • Changing the innodb_log_buffer_size should not have any effect on your initial problem (you probably have some other deviation). It should also not create this problem though, you may have accidentally changed something else. The *new* problem (unless you can simply revert your changes) belongs on https://dba.stackexchange.com; for the original problem, post at least for the failing server: `create table tablename`-statement, the variables listed [here](https://stackoverflow.com/q/15585602) and the `innodb_page_size`. But I'd suggest a new question for this, as it is completely different. – Solarflare Aug 07 '19 at 07:38
  • Please post results from OS of ulimit -a so we can see your limits. It is obvious in the first two lines of the error log, you need more capacity for Open Files. This can be accomplished with ulimit -n (number needed) dynamically from OS command prompt. Are you aware of ALL the considerations when using innodb_page_size other than default? Not that you can not invest the resources to make it possible. Posting 'chopped off' lines from the error log is not as helpful as posting the entire lines. – Wilson Hauck Aug 08 '19 at 11:55
  • Thanks guys, the error is fixed, as per the comment below! Thank you for your help. PS I did not realise that the error was cut out, my bad! – PythonBeg Aug 12 '19 at 00:36

1 Answers1

1

Thanks guys!

I managed to resolve the issue by changing the following innodb_file_format = BARRACUDA innodb_large_prefix = 1

Restarted the DB

Then I had to alter table ALTER TABLE [table name] ENGINE=INNODB ROW_FORMAT = DYNAMIC;

Then it worked like magic! Apparently the error above was unrelated to my issue and it was some other setting that was causing it, hindering the changes to file format and large prefix changes, as @Solarflare pointed out!

PythonBeg
  • 103
  • 10
  • Thanks for your good news. Open question, have you eliminated these from your current error log? 2019-08-07T05:42:54.144803Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000) 2019-08-07T05:42:54.144847Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000) This is an indication your Open Files limit is too low. Check capacity from OS command line prompt with ulimit -a You may dyamically set with ulimit -n (numberneeded) and to make persistent, follow url, https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/ – Wilson Hauck Aug 22 '19 at 21:59
  • @WilsonHauck After I removed innodb_log_buffer global variable, those errors dissapeared! Maybe it was the cause of that? – PythonBeg Aug 27 '19 at 07:19
  • Interesting! I will have a look, thank you for your concerns! – PythonBeg Aug 29 '19 at 03:35