Increase size of info column in system table. [#2580473]

There is some a problem when exporting a complex components using Features (e.g. long Entity form with fields which results in 1000 lines) which causing PDOException that the data is too long in system table.

The workaround is to do:

ALTER TABLE `system` CHANGE `info` `info` LONGBLOB /* or MEDIUMBLOB */ NULL DEFAULT NULL

as shown in here:

- #2056263: Branch 2.x results in large info files that exceed the 'info' column limit in the 'system' table,
- #1911022: Exception: PDOException: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'info' at row 1.

Is there any consideration to increase the size of `info` in Drupal core to MEDIUMBLOB?

Example PDOException when there is module present with a large .info file, so this happens on system_rebuild_module_data():

Starting Drupal installation. This takes a while. Consider using the        [ok]
--notify global option.
exception 'PDOException' with message 'SQLSTATE[22001]: String data,     [error]
right truncated: 1406 Data too long for column 'info' at row 167' in
/vagrant/build/docroot/includes/database/database.inc:2171
Stack trace:
#0 /vagrant/build/docroot/includes/database/database.inc(2171):
PDOStatement->execute(Array)
#1 /vagrant/build/docroot/includes/database/database.inc(683):
DatabaseStatementBase->execute(Array, Array)
#2 /vagrant/build/docroot/includes/database/mysql/query.inc(36):
DatabaseConnection->query('INSERT INTO {sy...', Array, Array)
#3 /vagrant/build/docroot/modules/system/system.module(2314):
InsertQuery_mysql->execute()
#4 /vagrant/build/docroot/modules/system/system.module(2467):
system_update_files_database(Array, 'module')
#5 /vagrant/build/docroot/includes/install.inc(747):
system_rebuild_module_data()
#6 /vagrant/build/docroot/includes/install.core.inc(787):
drupal_install_system()
#7 /vagrant/build/docroot/includes/install.core.inc(471):
install_system_module(Array)
#8 /vagrant/build/docroot/includes/install.core.inc(339):
install_run_task(Array, Array)
#9 /vagrant/build/docroot/includes/install.core.inc(77):
install_run_tasks(Array)
#10 /usr/local/share/drush/includes/drush.inc(725):
install_drupal(Array)
#11 /usr/local/share/drush/includes/drush.inc(711):
drush_call_user_func_array('install_drupal', Array)
#12
/usr/local/share/drush/commands/core/drupal/site_install_7.inc(82):
drush_op('install_drupal', Array)
#13 /usr/local/share/drush/commands/core/site_install.drush.inc(247):
drush_core_site_install_version('foo_profile', Array)
#14 [internal function]: drush_core_site_install('foo_profile',
'install_configu...')
#15 /usr/local/share/drush/includes/command.inc(366):
call_user_func_array('drush_core_site...', Array)
#16 /usr/local/share/drush/includes/command.inc(217):
_drush_invoke_hooks(Array, Array)
#17 [internal function]: drush_command('foo_profile',
'install_configu...')
#18 /usr/local/share/drush/includes/command.inc(185):
call_user_func_array('drush_command', Array)
#19 /usr/local/share/drush/lib/Drush/Boot/BaseBoot.php(67):
drush_dispatch(Array)
#20 /usr/local/share/drush/includes/preflight.inc(66):
Drush\Boot\BaseBoot->bootstrap_and_dispatch()
#21 /usr/local/share/drush/drush.php(12): drush_main()
#22 {main}
Command dispatch complete                                               [notice]

Comment	File	Size	Author
#28	interdiff-2580473-26-28.txt	627 bytes	Ronino
#28	system--increase-size-field-info-2580473-28.patch	1.15 KB	Ronino
#28	7.x: PHP 7.4 & MySQL 5.7 2,149 pass
#26	system--increase-size-field-info-2580473-26.patch	1.17 KB	MaxMendez
#26	7.x: PHP 7.4 & MySQL 5.7 2,125 pass PHP 7.4 & PostgreSQL 9.5 Patch Failed to Apply PHP 7.4 & SQLite 3.27 2,121 pass
#24	system--increase-size-field-info-2580473-24.patch	1.17 KB	danyg
#24	7.x: PHP 7.2 & MySQL 5.5 2,125 pass, 1 fail
#23	system--increase-size-field-info-2580473-23.patch	1.19 KB	44sunsets
#23	7.x: PHP 7.2 & MySQL 5.5 Patch Failed to Apply
#6	system--increase-size-field-info.patch	599 bytes	kenorb
#6	7.x: PHP 5.3 & MySQL 5.5 1,998 pass
#11	system--increase-size-field-info-2580473-11.patch	1.25 KB	sudaz
#11	7.x: PHP 7 & MySQL 5.5 PHPLint Failed
#14	system--increase-size-field-info-2580473-11.patch	1.25 KB	sudaz
#14	7.x: PHP 7 & MySQL 5.5 2,038 pass
#18	system--increase-size-field-info-2580473-18.patch	998 bytes	Rob C
#18	7.x: PHP 7 & MySQL 5.5 run-tests.sh exception
#19	system--increase-size-field-info-2580473-19.patch	1.19 KB	init90
#19	7.x: PHP 7 & MySQL 5.5 Patch Failed to Apply
#20	system--increase-size-field-info-2580473-20.patch	1.11 KB	init90
#20	7.x: PHP 7 & MySQL 5.5 2,053 pass
#20	interdiff-18-20.txt	752 bytes	init90

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

5 October 2015 at 15:48

kenorb created an issue. See original summary.

Comment #2

kenorb CreditAttribution: kenorb commented 5 October 2015 at 15:49

Issue summary:

View changes

Comment #3

kenorb CreditAttribution: kenorb commented 5 October 2015 at 15:49

Issue summary:

View changes

Comment #4

kenorb CreditAttribution: kenorb commented 5 October 2015 at 15:52

Comment #5

longwave

he/him

English

CreditAttribution: longwave commented 5 October 2015 at 20:41

I am not sure this is useful for 99% of users so does not need to be changed in core, but I think this can be achieved in contrib/custom code by altering the table definition in hook_install() and hook_schema_alter()?

Comment #6

kenorb CreditAttribution: kenorb commented 25 February 2016 at 18:34

File	Size
system--increase-size-field-info.patch	599 bytes
7.x: PHP 5.3 & MySQL 5.5 1,998 pass

Uploading patch from https://gist.github.com/danielmccosker/3174970

Comment #7

kenorb CreditAttribution: kenorb commented 25 February 2016 at 18:34

Status:

Active

» Needs review

Comment #8

kenorb CreditAttribution: kenorb commented 25 February 2016 at 18:37

Issue summary:

View changes

Comment #9

kenorb CreditAttribution: kenorb commented 26 February 2016 at 16:25

Issue summary:

View changes

This one-time code should work as well:

db_change_field("system", "info", "info", array('type' => 'blob', "size" => "big"));

Probably the patch needs to include some hook_update_X similar to system_update_7055().

Comment #10

iamEAP CreditAttribution: iamEAP as a volunteer commented 10 October 2017 at 18:26

Status:

Needs review

» Needs work

Patch in #6 works for brand new Drupal 7 installs, but a field change update hook will need to be implemented in the System module, as mentioned in #9.

Comment #11

sudaz CreditAttribution: sudaz commented 12 October 2017 at 21:22

File	Size
system--increase-size-field-info-2580473-11.patch	1.25 KB
7.x: PHP 7 & MySQL 5.5 PHPLint Failed

Updated patch to add a field change update hook as mentioned in #9 for existing installs

Comment #12

Etroid CreditAttribution: Etroid commented 12 October 2017 at 22:02

Status:

Needs work

» Needs review

Comment #13

12 October 2017 at 22:04

Status:

Needs review

» Needs work

The last submitted patch, 11: system--increase-size-field-info-2580473-11.patch, failed testing. View results

Comment #14

sudaz CreditAttribution: sudaz commented 12 October 2017 at 22:42

File	Size
system--increase-size-field-info-2580473-11.patch	1.25 KB
7.x: PHP 7 & MySQL 5.5 2,038 pass

1 file was hidden/shown/deleted

File	Size
system--increase-size-field-info-2580473-11.patch	1.25 KB
7.x: PHP 7 & MySQL 5.5 PHPLint Failed

Comment #15

Etroid CreditAttribution: Etroid commented 12 October 2017 at 22:47

Status:

Needs work

» Needs review

Comment #16

iamEAP CreditAttribution: iamEAP as a volunteer commented 12 October 2017 at 23:22

Category:	Feature request	» Bug report
Status:	Needs review	» Reviewed & tested by the community

Looks like Drupal does not support mediumblob for portability reasons, so largeblob it is.

This issue can crop up from a variety of sources related to Features, but not directly caused by Features itself. With that in mind, I don't see a better place for this to live other than core.

Moving to RTBC.

Comment #17

Pol

the/that

Brussels

CreditAttribution: Pol at European Commission and European Union Institutions, Agencies and Bodies for European Commission and European Union Institutions, Agencies and Bodies commented 19 July 2018 at 20:35

Looks fine to me.

Comment #18

Rob C CreditAttribution: Rob C commented 9 May 2019 at 10:16

File	Size
system--increase-size-field-info-2580473-18.patch	998 bytes
7.x: PHP 7 & MySQL 5.5 run-tests.sh exception

Upped the update hook, system_update_7082() now exists in core.

Comment #19

init90

Ukraine

CreditAttribution: init90 at ZANZARRA Drupal Agency commented 10 July 2019 at 12:49

Status:

Reviewed & tested by the community

» Needs review

File	Size
system--increase-size-field-info-2580473-19.patch	1.19 KB
7.x: PHP 7 & MySQL 5.5 Patch Failed to Apply

3 files were hidden/shown/deleted

File	Size
system--increase-size-field-info.patch	599 bytes
7.x: PHP 5.3 & MySQL 5.5 1,998 pass
system--increase-size-field-info-2580473-11.patch	1.25 KB
7.x: PHP 7 & MySQL 5.5 2,038 pass
system--increase-size-field-info-2580473-18.patch	998 bytes
7.x: PHP 7 & MySQL 5.5 run-tests.sh exception

I've also encountered with such problem when worked with feature which contain huge amount of data.

Reupload patch with some cosmetic corrections.

Comment #20

init90

Ukraine

CreditAttribution: init90 at ZANZARRA Drupal Agency commented 10 July 2019 at 20:57

File	Size
interdiff-18-20.txt	752 bytes
system--increase-size-field-info-2580473-20.patch	1.11 KB
7.x: PHP 7 & MySQL 5.5 2,053 pass

1 file was hidden/shown/deleted

File	Size
system--increase-size-field-info-2580473-19.patch	1.19 KB
7.x: PHP 7 & MySQL 5.5 Patch Failed to Apply

I've recreated patch in proper way.

Comment #21

luismagr CreditAttribution: luismagr as a volunteer and at Code Enigma commented 27 January 2020 at 16:51

Hi @all

Thanks for the patch @init90. I've checked it locally and it works for me.

Could it be possible to move this issue forward so we can have this patch included in a core update?

Thanks

Comment #22

danyg CreditAttribution: danyg at Cheppers commented 12 March 2020 at 16:54

Status:

Needs review

» Reviewed & tested by the community

Hi, I've tested the patch at #20 and it works like a charm. I'm changing it to RTBC.
@init90 Thanks for the patch!

Comment #23

44sunsets CreditAttribution: 44sunsets commented 22 May 2020 at 06:09

File	Size
system--increase-size-field-info-2580473-23.patch	1.19 KB
7.x: PHP 7.2 & MySQL 5.5 Patch Failed to Apply

We've been using patch #20 for a while and it's been great, but the recent Drupal 7.70 update broke this due to a system update function number conflict in /modules/system/system.install.

I have slightly modified and re-rolled patch #20 so that it works with Drupal 7.70.

Comment #24

danyg CreditAttribution: danyg at Cheppers commented 5 June 2020 at 21:28

File	Size
system--increase-size-field-info-2580473-24.patch	1.17 KB
7.x: PHP 7.2 & MySQL 5.5 2,125 pass, 1 fail

1 file was hidden/shown/deleted

File	Size
system--increase-size-field-info-2580473-23.patch	1.19 KB
7.x: PHP 7.2 & MySQL 5.5 Patch Failed to Apply

Here is the updated patch for the latest dev (Drupal version is 7.71)

Comment #25

mibfire CreditAttribution: mibfire commented 21 October 2021 at 18:29

I ran into this issue too. When can this be released? I am asking because this is a serious issue and should be fixed asap.

Comment #26

MaxMendez CreditAttribution: MaxMendez commented 1 August 2022 at 20:39

File	Size
system--increase-size-field-info-2580473-26.patch	1.17 KB
7.x: PHP 7.4 & MySQL 5.7 2,125 pass PHP 7.4 & PostgreSQL 9.5 Patch Failed to Apply PHP 7.4 & SQLite 3.27 2,121 pass

1 file was hidden/shown/deleted

File	Size
system--increase-size-field-info-2580473-24.patch	1.17 KB
7.x: PHP 7.2 & MySQL 5.5 2,125 pass, 1 fail

I ran into this issue to, would be so useful to merge and finally release the fixed of this issue.

Here patch for the latest dev (Drupal version is 7.91)

Comment #27

poker10 CreditAttribution: poker10 at ActivIT s.r.o. commented 28 August 2022 at 23:40

Thanks for the patch and reviews. I agree with @longwave that it must be considered carefully if this will be usefull for majority of D7 users. The system table should not have thousands of rows so this should not cause performance issues (note that longblob adds 2 bytes of storage for each row in comparission with blob).

But do we need to run the db_change_field() for other DB engines (PostgreSQL/SQLite), or only for MySQL? According to this: https://www.drupal.org/docs/7/api/schema-api/data-types/data-types-overview in PostgreSQL and SQLite the normal blob and big blob should be the same datatypes.

Comment #28

Ronino CreditAttribution: Ronino commented 8 December 2022 at 12:08

Status:

Reviewed & tested by the community

» Needs review

File	Size
system--increase-size-field-info-2580473-28.patch	1.15 KB
7.x: PHP 7.4 & MySQL 5.7 2,149 pass
interdiff-2580473-26-28.txt	627 bytes

Adapted patch #26 for today's release of Drupal 7.93.

To update a running system, reverse #26, apply this new patch, then rerun system_update_7085() which is now supplied by Drupal core:

$ drush eval "module_load_include('install', 'system'); system_update_7085();"
$ drush updb

Increase size of info column in system table.

Comments

Comment #1

Comment #2

Comment #3

Comment #4

Comment #5

Comment #6

Comment #7

Comment #8

Comment #9

Comment #10

Comment #11

Comment #12

Comment #13

Comment #14

Comment #15

Comment #16

Comment #17

Comment #18

Comment #19

Comment #20

Comment #21

Comment #22

Comment #23

Comment #24

Comment #25

Comment #26

Comment #27

Comment #28

Related issues

Thank you to these Drupal contributors

News items

Our community

Documentation

Drupal code base

Governance of community