CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 106

rritz

rritz

Member
Hi,
I have some weird issue. I migrated my mw installation to a bigger server. All seemed to work well, but now trying to import subscribers to my list O get this error: CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0' for key 'PRIMARY'

I have a second mw installation on that same server and it's working fine, so I guess the general mysql settings are good.

Any help as to what's happening and how to resolve? Thxs, very much appreciated
 
rritz said:
Integrity constraint violation: 1062 Duplicate entry '0' for key 'PRIMARY'
Click to expand...
This basically means the import of the database wasn't successful.
rritz said:
Duplicate entry '0' for key 'PRIMARY'
Click to expand...
It is because a table in the database does not have the AUTO_INCREMENT attribute set for the primary key.
I advise you do export the database from command line if possible and import it like so too.
 
I imported the database from commandline, as it's pretty big. I'll try again ... exporting it from commandline fails now as mysql will not start up on the old server
Thank you
 
@rritz - make sure your table structure contain the autoincrement keyword, for example:
This is correct:
PHP: 
CREATE TABLE IF NOT EXISTS `article` (
  `article_id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `content` longtext NOT NULL,
  `status` char(15) NOT NULL DEFAULT 'published',
  `date_added` datetime NOT NULL,
  `last_updated` datetime NOT NULL,
  PRIMARY KEY (`article_id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

This isn't:
PHP: 
CREATE TABLE IF NOT EXISTS `article` (
  `article_id` int(11) NOT NULL,
  `title` varchar(200) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `content` longtext NOT NULL,
  `status` char(15) NOT NULL DEFAULT 'published',
  `date_added` datetime NOT NULL,
  `last_updated` datetime NOT NULL,
  PRIMARY KEY (`article_id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

You might also take the exiting database table, one by one, and make sure the first column is set to auto_increment:
Screenshot 2017-12-15 20.17.42.png
And if it isn't, just make it.
 
Ok cool, so I can set all the primary keys to extra = auto increment and then it should work?
None of the tables have the auto increment set on my import
I got an import error too, so I increased the buffer pool size as per mysql documentation

Trying to import again now and then I'll set the auto increment
thxs a lot for your help

BTW, the biggest tables in the database are the delivery server usage logs and the campaign delivery logs - is there a way to get rid of these before the export next time? I don't need usage logs at all, and the delivery logs are only of temporary use. Can I set up a cron job to flush these tables say once a month or so? Or get rid of the entries older than x days?
 
rritz said:
Ok cool, so I can set all the primary keys to extra = auto increment and then it should work?
None of the tables have the auto increment set on my import
I got an import error too, so I increased the buffer pool size as per mysql documentation
Click to expand...
This is a sign that the import really wasn't successful, so maybe something else is broken, maybe your tables don't have foreign keys, etc etc. I highly suggest you check the integrity of the import.

rritz said:
BTW, the biggest tables in the database are the delivery server usage logs and the campaign delivery logs - is there a way to get rid of these before the export next time? I don't need usage logs at all, and the delivery logs are only of temporary use. Can I set up a cron job to flush these tables say once a month or so? Or get rid of the entries older than x days?
Click to expand...
You can empty both tables. Also there are options in mailwizz to cleanup these tables at a given time interval.
 
II think I got it running now. Most of the tables had autoincrement, only a few missing. I added them manually, except two, where i got error when trying to do so.
I think it could be something with foreign key. I remember last time I had to do a database import I hat to disable foreign key check to make it work.

The options in mailwizz to cleanup these tables at a given time interval are they added after 1.4.3 ? I was looking in settings, and searching the knowledge base but could find no hint as to this. Also, no hint how to disable logging for delivery server usage
 
You must log in or register to reply here.
Back
Top