Error adding single subscriber

corey34

corey34

Active Member
When trying to add a specific subscriber to a list, I get this error: "This email address is already registered in this list!"

However, I have looked through the list and even searched in the database and I cannot see the email address that I am trying to add.

It's a subscriber that was on the list at one point but was deleted.

Any ideas why this might be happening? Thanks
 
corey34 said:
Do you know what he did?
Click to expand...
Yeah, i know :D

Basically, some of your database tables are missing the foreign keys constraints.
The good news is we have them stored in a file so you can run the queries via phpmyadmin to add the missing constraints.
The file apps/common/data/install-sql/schema.sql contains all the queries mailwizz runs at install.
If you scroll down to line ~2273, you can see how the script starts adding contraints, i.e:
Code: 
ALTER TABLE `article_category`
  ADD CONSTRAINT `fk_article_category_article_category1` FOREIGN KEY (`parent_id`) REFERENCES `article_category` (`category_id`) ON DELETE CASCADE ON UPDATE NO ACTION;

Of course, you have to add the table prefix, that is mw_ so:
Code: 
ALTER TABLE `mw_article_category`
  ADD CONSTRAINT `fk_article_category_article_category1` FOREIGN KEY (`parent_id`) REFERENCES `mw_article_category` (`category_id`) ON DELETE CASCADE ON UPDATE NO ACTION;

Some tables, do have the constraints, for example, if we run:
Code: 
show create table mw_article_category;
We can see:
Code: 
| mw_article_category | CREATE TABLE `mw_article_category` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `name` varchar(200) NOT NULL,
  `slug` varchar(250) NOT NULL,
  `description` text,
  `status` char(15) NOT NULL DEFAULT 'active',
  `date_added` datetime NOT NULL,
  `last_updated` datetime NOT NULL,
  PRIMARY KEY (`category_id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `fk_article_category_article_category1_idx` (`parent_id`),
  CONSTRAINT `fk_article_category_article_category1` FOREIGN KEY (`parent_id`) REFERENCES `mw_article_category` (`category_id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
Which shows it has the constraints in it, so we can skip it and move to the next table, if the next table has the constraints, we can move to the next one, and so on.

Just follow the order of the queries as they are set in the above file. before running the query to add the constraint, check if the constraint exists and run the addition query only if it doesn't.


Now, you might also want to run this query:
Code: 
DELETE FROM mw_list_field_value WHERE subscriber_id IN(
   SELECT * FROM(
       SELECT distinct(t.subscriber_id) AS subscriber_id from mw_list_field_value t left join mw_list_subscriber s on s.subscriber_id = t.subscriber_id WHERE s.subscriber_id IS NULL
   ) as t1
);
Which deletes the orphaned records from mw_list_field_value.

Please make sure you backup before going this road.
 
When running that, I get thsi error:

Cannot add or update a child row: a foreign key constraint fails (`******_mailwizz`.`#sql-9c9_16d9340`, CONSTRAINT `fk_list_field_value_list_subscriber1` FOREIGN KEY (`subscriber_id`) REFERENCES `mw_list_subscriber` (`subscriber_id`) ON DELETE CASCADE O)

The command 'show create table mw_list_field_value' returns:

CREATE TABLE `mw_list_field_value` (
`value_id` int(11) NOT NULL AUTO_INCREMENT,
`field_id` int(11) NOT NULL,
`subscriber_id` int(11) NOT NULL,
`value` varchar(255) NOT NULL,
`date_added` datetime NOT NULL,
`last_updated` datetime NOT NULL,
PRIMARY KEY (`value_id`),
KEY `fk_list_field_value_list_field1_idx` (`field_id`),
KEY `fk_list_field_value_list_subscriber1_idx` (`subscriber_id`),
KEY `field_subscriber` (`field_id`,`subscriber_id`),
KEY `field_id_value` (`field_id`,`value`)
) ENGINE=InnoDB AUTO_INCREMENT=5392781 DEFAULT CHARSET=utf8

I see no CONSTRAINTs in there so I'm not sure what the problem is.

Any thoughts? Is this maybe a different issue?

Thanks
 
twisted1919 said:
Now, you might also want to run this query:
Code: 
DELETE FROM mw_list_field_value WHERE subscriber_id IN(
SELECT * FROM(
SELECT distinct(t.subscriber_id) AS subscriber_id from mw_list_field_value t left join mw_list_subscriber s on s.subscriber_id = t.subscriber_id WHERE s.subscriber_id IS NULL
) as t1
);
Which deletes the orphaned records from mw_list_field_value.
Click to expand...
 
@twisted1919, so after running the "DELETE FROM..." query it was "successful" in that it came back with "571604 rows affected." However, running the "ALTER TABLE..." command after this still gives the same error.
 
@twisted1919, I'm getting the same error:

Cannot add or update a child row: a foreign key constraint fails (`******_mailwizz`.`#sql-7d6_8999a3`, CONSTRAINT `fk_list_field_value_list_field1` FOREIGN KEY (`field_id`) REFERENCES `mw_list_field` (`field_id`) ON DELETE CASCADE ON UPDATE NO ACTION)
 
Not sure but maybe you should also run this:
Code: 
DELETE FROM mw_list_field_value WHERE field_id IN(
SELECT * FROM(
SELECT distinct(t.field_id) AS field_id from mw_list_field_value t left join mw_list_field s on s.field_id = t.field_id WHERE s.field_id IS NULL
) as t1
);
 
You must log in or register to reply here.
Back
Top