Updating lots of sites with the latest 7.34 core I have a problem with one site. The dbupdate fails for block_update_7009. I get the following error message:

WD php: PDOException: SQLSTATE[01000]: Warning: 1265 Data truncated for column 'title' at row 75: ALTER TABLE {block} CHANGE `title` `title` VARCHAR(255) NOT NULL
DEFAULT '' COMMENT 'Custom title for the block. (Empty string will use block default title, will remove the title, text will cause block to use specified title.)';
Array
(
)
in db_change_field() (line 3020 of /path/to/my/drupalroot/includes/database/database.inc).
PDOException: SQLSTATE[01000]: Warning: 1265 Data truncated for column 'title' at row 75: ALTER TABLE {block} CHANGE `title` `title` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Custom title for the block. (Empty string will use block default title, will remove the title, text will cause block to use specified title.)'; Array
(
)
i db_change_field() (rad 3020 av /path/to/my/drupalroot/includes/database/database.inc).

I have manually copy the update code with the change code to a separate php file and running it with "drush scr" and it gives the same error. I am using Aeigr so the site is in a multisite setup. And running 30+ other sites gives no problem. But this site and with its dev + stage copy have the same error message.

Running the sql commando in phpmyadmin goes cleanly and after that the update hook runs as it should with out error

"ALTER TABLE block CHANGE `title` `title` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Custom title for the block. (Empty string will use block default title, will remove the title, text will cause block to use specified title.)';"

The source table has the following structure:

CREATE TABLE IF NOT EXISTS `block` (
  `bid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key: Unique block ID.',
  `module` varchar(64) COLLATE utf8_swedish_ci DEFAULT NULL,
  `delta` varchar(32) COLLATE utf8_swedish_ci DEFAULT NULL,
  `theme` varchar(64) COLLATE utf8_swedish_ci DEFAULT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Block enabled status. (1 = enabled, 0 = disabled)',
  `weight` int(11) NOT NULL DEFAULT '0' COMMENT 'Block weight within region.',
  `region` varchar(64) COLLATE utf8_swedish_ci DEFAULT NULL,
  `custom` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Flag to indicate how users may control visibility of the block. (0 = Users cannot control, 1 = On by default, but can be hidden, 2 = Hidden by default, but can be shown)',
  `visibility` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Flag to indicate how to show blocks on pages. (0 = Show on all pages except listed pages, 1 = Show only on listed pages, 2 = Use custom PHP code to determine visibility)',
  `pages` text COLLATE utf8_swedish_ci,
  `title` varchar(64) COLLATE utf8_swedish_ci DEFAULT NULL,
  `cache` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'Binary flag to indicate block cache mode. (-2: Custom cache, -1: Do not cache, 1: Cache per role, 2: Cache per user, 4: Cache per page, 8: Block cache global) See DRUPAL_CACHE_* constants in ../includes/common.inc for more detailed information.',
  `css_class` varchar(255) COLLATE utf8_swedish_ci DEFAULT NULL,
  PRIMARY KEY (`bid`),
  UNIQUE KEY `tmd` (`theme`,`module`,`delta`),
  KEY `list` (`theme`,`status`,`region`,`weight`,`module`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci COMMENT='Stores block settings, such as region and visibility...' AUTO_INCREMENT=124 ;

The current data in the block table has only empty fields or the value ''

CommentFileSizeAuthor
#13 2378561-blocktitle-12.patch462 bytesstefan.r
#12 2378561-blocktitle-11.patch460 bytesstefan.r

Comments

ianthomas_uk’s picture

ianthomas_uk
he/him
Primary language English
Location Brighton, UK
commented

What is the table structure after the update has run, specifically for the title column.

I wonder if it's the charset, which is "truncating" characters that can't be represented.

What is the table type? Do MyISAM tables enforce column lengths?

ianthomas_uk’s picture

ianthomas_uk
he/him
Primary language English
Location Brighton, UK
commented

Can you check what is on row 75 of your source database? What is the longest title you have in your source database?

http://bugs.mysql.com/bug.php?id=34197 talks about comments over 64 characters long, which the one on this table is. That bug is about stored procedures, but maybe it applies to tables too.

kristofferwiklund’s picture

kristofferwiklund commented

The data structure after the hook_update is:

CREATE TABLE IF NOT EXISTS `block` (
  `bid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key: Unique block ID.',
  `module` varchar(64) COLLATE utf8_swedish_ci DEFAULT NULL,
  `delta` varchar(32) COLLATE utf8_swedish_ci DEFAULT NULL,
  `theme` varchar(64) COLLATE utf8_swedish_ci DEFAULT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Block enabled status. (1 = enabled, 0 = disabled)',
  `weight` int(11) NOT NULL DEFAULT '0' COMMENT 'Block weight within region.',
  `region` varchar(64) COLLATE utf8_swedish_ci DEFAULT NULL,
  `custom` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Flag to indicate how users may control visibility of the block. (0 = Users cannot control, 1 = On by default, but can be hidden, 2 = Hidden by default, but can be shown)',
  `visibility` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Flag to indicate how to show blocks on pages. (0 = Show on all pages except listed pages, 1 = Show only on listed pages, 2 = Use custom PHP code to determine visibility)',
  `pages` text COLLATE utf8_swedish_ci,
  `title` varchar(255) COLLATE utf8_swedish_ci NOT NULL DEFAULT '' COMMENT 'Custom title for the block. (Empty string will use block default title, <none> will remove the title, text will cause block to use specified title.)',
  `cache` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'Binary flag to indicate block cache mode. (-2: Custom cache, -1: Do not cache, 1: Cache per role, 2: Cache per user, 4: Cache per page, 8: Block cache global) See DRUPAL_CACHE_* constants in ../includes/common.inc for more detailed information.',
  `css_class` varchar(255) COLLATE utf8_swedish_ci DEFAULT NULL,
  PRIMARY KEY (`bid`),
  UNIQUE KEY `tmd` (`theme`,`module`,`delta`),
  KEY `list` (`theme`,`status`,`region`,`weight`,`module`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci COMMENT='Stores block settings, such as region and visibility...' AUTO_INCREMENT=124 ;

One thing I see is that the NULL has become NOT NULL

The table type is InnoDB.

David_Rothstein’s picture

David_Rothstein commented

Yeah, this is very curious... http://cgit.drupalcode.org/drupal/commit/?id=f68c6ce is the commit where this was added, and you can see that it's increasing the size of the title column (and the comment length isn't changing either).

One other thing you might check is whether there is any difference in the database table structure between this site and the 30+ you ran it on that didn't have this problem?

kamenrs’s picture

kamenrs commented

I have the same error when performing the database update of Drupal 7.34:

The following updates returned messages
block module
Update #7009

Failed: PDOException: SQLSTATE[01000]: Warning: 1265 Data truncated for column 'title' at row 233: ALTER TABLE {block} CHANGE `title` `title` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Custom title for the block. (Empty string will use block default title, will remove the title, text will cause block to use specified title.)'; Array ( ) in db_change_field() (line 3020 of /home/amirabul/public_html/includes/database/database.inc).

I use a single site setup. My other sites updated correctly, but this one.
As described above, after running the command cleanly in PhpMyAdmin, the update script executed without errors.

kristofferwiklund’s picture

kristofferwiklund commented
Version: 7.34 » 7.33
Status: Active » Postponed (maintainer needs more info)

The problem seems to be the NULL. Because in the source we did have some NULL values for the title. And it happen to be row 75 (and some more after that).

I don't know how this table field has been allowing NULL values. I will dig into backups to see when it happened. According to Drupal core git log that table should be NOT NULL since 2007. (And this site is 1 year old)

And also this bug should be for 7.33 as the update hook was introduced there.

Postponing it until I can find some more input.

ianthomas_uk’s picture

ianthomas_uk
he/him
Primary language English
Location Brighton, UK
commented

So we understand what is causing the error message, but not why the title column was ever set to allow nulls. If anyone has any ideas, please say.

If it's a common problem it may be worth changing the hook to run "Update block set title = '' where title is null" before changing the table structure, but I don't think that's necessary if we've only seen a couple of databases with this incorrect schema.

kristofferwiklund’s picture

kristofferwiklund commented
Status: Postponed (maintainer needs more info) » Closed (cannot reproduce)

What I have found in my backups is that between 2014-01-01 and 2014-02-01 the table has changed. This was before the site was launched and still in development.

Going through the git log. The only thing that I found that might be sometime is that I have installed the block_class module. But I have installed that module on a test branch with latest Drupal 7.34 and it does not change the block table.

geodaniel’s picture

geodaniel commented

I see the same problem during an update on one of my sites too. Is it worth updating the update hook to set all NULL titles to '' before doing the alter? Doing this manually allowed the update to run successfully.

The two rows causing issues:

  • 'system', 'help', 'zen', '1', '0', 'help', '0', '0', '', NULL, '109', '1'
  • 'system','main','zen','1','0','content','0','0','',NULL,'110','-1'

This site was one that was upgraded from Drupal 6, if it helps.

kari.kaariainen’s picture

kari.kaariainen commented

Same here, a couple of blocks with null values on title column. Manual changing allowed the update go through. Discussed in here, too: https://www.drupal.org/node/2371573

The deltas of these blocks were 'example_configurable_text' and 'example_empty' originating from the Examples module, though looking at the code doesn't explain this.

This site was also upgraded from Drupal 6.

I vote for "Update block set title = '' where title is null".

David_Rothstein’s picture

David_Rothstein commented
Status: Closed (cannot reproduce) » Postponed (maintainer needs more info)
Related issues: +#2377223: Update #7009 failed

Since there are a number of people experiencing this (I'm marking #2377223: Update #7009 failed as a duplicate), I guess it's worth keeping open to try to get to the bottom of it. Can anyone figure out what code out there was modifying the database structure of the core block table to allow NULL values?

I tend to agree that if enough people are experiencing it we can consider adding UPDATE block SET title = '' WHERE title IS NULL to the update function. (Normally don't like to add those kinds of workarounds; however, since it should be harmless and since this is only a one-time update function that runs once and then never again, it might be worth it in this case.)

I will add a note about this to the 7.33 release notes also.

stefan.r’s picture

stefan.r commented
Priority: Normal » Major
Status: Postponed (maintainer needs more info) » Needs review
StatusFileSize
new 2378561-blocktitle-11.patch460 bytes

Shouldn't we add this anyway out of precaution?

stefan.r’s picture

stefan.r commented
StatusFileSize
new 2378561-blocktitle-12.patch462 bytes

With support for prefixed tables...

Version: 7.33 » 7.x-dev

Core issues are now filed against the dev versions where changes will be made. Document the specific release you are using in your issue comment. More information about choosing a version.

Status: Needs review » Closed (outdated)

Automatically closed because Drupal 7 security and bugfix support has ended as of 5 January 2025. If the issue verifiably applies to later versions, please reopen with details and update the version.