I’m in the process of migrating from an EngineHosting server to an Arcustech server. I’ve upgraded to EE 2.11.9 on my EH site, and everything is working well. That’s currently live and functional.
Now, Arcustech has migrated the exact files and databases over to the new server, and the site loads ok, but when I try to create a new channel entry, I am getting an SQL error. It seems to be something related to a relationships field (field 11 is a relationship, and elsewhere in the table the other values are all “0”). The error is coming from the EE code, not an add-on.
Question: I would rather just go live with this EE 2 install because of some add-ons, but I can’t until this is resolved. Is there a fix i can do in EE 2, and/or would upgrading to EE 3 solve this problem?
MySQL error is :
Error Number: 1366
Incorrect integer value: ” for column ‘field_id_11’ at row 1
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 (1261, ‘4’, ‘1’, ”, ‘David Smith’, ‘
Thanks for any help. Peter
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?
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;
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
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 NULL
s 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.
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.
Packet Tide owns and develops ExpressionEngine. © Packet Tide, All Rights Reserved.