Migration and upgrade issue: database error 1366

Sorry you’re running into this Peter. There is an issue that some folks coming from very old versions are hitting that we’re working on a fix for currently. Can you give me the field IDs of all of your relationship fields and I can give you some SQL to run to correct the issue?

Thank you, Derek.

It looks like Field IDs 11, 13, and 7

Those are in three different field groups, but I presume the IDs are unique.

Cheers Peter

Back up your database, and then run these three queries separately (Sequel Pro, phpMyAdmin, or even the ExpressionEngine SQL Manager):

ALTER TABLE exp_channel_data MODIFY COLUMN `field_id_11` VARCHAR(8) DEFAULT NULL;
ALTER TABLE exp_channel_data MODIFY COLUMN `field_id_13` VARCHAR(8) DEFAULT NULL;
ALTER TABLE exp_channel_data MODIFY COLUMN `field_id_7` VARCHAR(8) DEFAULT NULL;

Just saw this—long week. Will do and report back. Thank you very much. Peter

Alas, something’s still not working. Slightly different error this time. Now it’s error 1364:

Field ‘field_id_4’ doesn’t have a default value INSERT INTO exp_channel_data (entry_id, channel_id, site_id, field_id_11, field_id_8, field_id_9, field_ft_9, field_id_10, field_ft_10, field_id_12, field_ft_12, field_id_20, field_ft_20, field_id_14, field_id_19, field_ft_19, field_id_18, field_ft_18, field_ft_11, field_ft_8, field_ft_14) VALUES (1264, ‘4’, ‘1’, ”, ‘John Smith’, [chopped for length; I have the whole error message if needed] }

Filename: libraries/api/Api_channel_entries.php Line Number: 1514

But I’m not calling for field ID 4 —that’s not used in this channel. It looks like 4 is my channel ID.

It is creating an entry, but apparently not completely. When I try to edit the new entry I get an error, “You have tried to access a channel that does not exist.” But the channel does exist, of course. And when I edit any of the older entries, it’s working fine.

This is the edit request, where Channel ID 4 is my articles channel and the entry id is the new one that generated the error.

admin.php?/cp/content_publish/entry_form&channel_id=4&entry_id=1264

Any advice?

Peter

What field type is the custom field with the ID 4? The error is specifically because that channel doesn’t use it, but your db has no default value specified for the field. The type of field might yield a clue.

It is in the default field group, used for an illustration tag. It’s type text input, maxlength 300, field content set to ‘all’

Let’s look at them all in one swoop:

SHOW CREATE TABLE exp_channel_data;

Paste the results into a code block please (see the “Use Markdown” link below the reply field).

This is what I got back:

CREATE TABLE `exp_channel_data` (
  `entry_id` int(10) unsigned NOT NULL DEFAULT '0',
  `site_id` int(4) unsigned NOT NULL DEFAULT '1',
  `channel_id` int(4) unsigned NOT NULL,
  `field_id_1` text,
  `field_ft_1` tinytext,
  `field_id_2` text,
  `field_ft_2` tinytext,
  `field_id_3` text,
  `field_ft_3` tinytext,
  `field_id_4` text NOT NULL,
  `field_ft_4` tinytext,
  `field_id_5` text,
  `field_ft_5` tinytext,
  `field_id_6` text NOT NULL,
  `field_ft_6` tinytext,
  `field_id_7` varchar(8) DEFAULT NULL,
  `field_ft_7` tinytext,
  `field_id_8` text NOT NULL,
  `field_ft_8` tinytext,
  `field_id_9` text,
  `field_ft_9` tinytext,
  `field_id_10` text,
  `field_ft_10` tinytext,
  `field_id_11` varchar(8) DEFAULT NULL,
  `field_ft_11` tinytext,
  `field_id_12` text,
  `field_ft_12` tinytext,
  `field_id_13` varchar(8) DEFAULT NULL,
  `field_ft_13` tinytext,
  `field_id_14` text,
  `field_ft_14` tinytext,
  `field_id_15` text,
  `field_ft_15` tinytext,
  `field_id_16` text,
  `field_ft_16` tinytext,
  `field_id_17` text NOT NULL,
  `field_ft_17` tinytext,
  `field_id_18` text,
  `field_ft_18` tinytext,
  `field_id_19` text,
  `field_ft_19` tinytext,
  `field_id_20` text NOT NULL,
  `field_ft_20` tinytext,
  `field_id_21` text,
  `field_ft_21` tinytext,
  `field_id_23` text NOT NULL,
  `field_ft_23` tinytext,
  `field_id_24` text,
  `field_ft_24` tinytext,
  PRIMARY KEY (`entry_id`),
  KEY `site_id` (`site_id`),
  KEY `channel_id` (`channel_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Peter, the not NULLs that you have everywhere are going to cause issues. I’m not sure how your database got in that state, I’ve gone back to even old versions of v2 and we were not using those inserts. Has this database ever been migrated from one environment to another (including local/staging)? Perhaps something happened then. The easiest way to address this would be to use a tool like phpMyAdmin or Sequel Pro that has a UI to adjust the table properties, and to allow NULL in all of those fields that currently don’t.

Well, this was the result of a migration. I’ll see if I can figure out how to do that. I have the MySQLworkbench app configured, so presumably there’s some way there. Thanks.

MySQL Workbench would be fine; it’s a little more complicated than other MySQL GUIs, but it’s okay. Navigate to your database, then to the exp_channel_data table, click the wrench, and then select the field in the main window. The “NN” column stands for “Not NULL” and is the flag that should be removed from custom field columns.

Thank you. The clicking on the wrench was the piece I was missing.

It seems to be working now.

I didn’t toggle off the NN for Entry_ID, Channel_ID and Site_ID; let me know if that should be done.

Much appreciate the assistance. Moving forward with the migration.

Cheers Peter

Nope you got it, just the custom fields should allow NULL values.