[SOLVED] Unable to add new content

[nlindq]

I upgraded to the latest (at the time) 1.11.10 version of CMSMS several months ago, also migrating from PostgreSQL to the required MySQL. At the time I'd thought everything was working, but I guess nobody had attempted to add new content until now.

ETA: I should mention that modifying existing content works flawlessly.

Anyway, the first attempt to add new content seems to be successful in the UI, but the content doesn't show up in either the admin interface or on the site. Any subsequent attempts to add content result in a "Duplicate entry '0' for key 'PRIMARY'" error.

I had a look at the MySQL table, and there is a new row in cms_content with the data as entered, but it has content_id set to 0 which seems odd to me. I would have expected inserting a row with content_id 0 to auto-increment the primary key. Interestingly, when I dump the schema for a newly auto-created database, one of the lines is...

Code: Select all

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

... which seems to indicate that auto-increment on zero is disabled, in which case I would expect CMSMS to insert NULL into the PK field so that auto_increment works.

I also attempted to reset the auto-increment index on the content_id field using:

Code: Select all

ALTER TABLE cms_content AUTO_INCREMENT = [nn]

but that made no difference.

I'm pretty sure something is munged as a result of my migration/upgrade process, but I'm not quite sure where to start looking...

[nlindq]

Okay, I believe I may have figured this out. I noticed all the *_seq tables in the MySQL schema which didn't exist in the old PostgreSQL schema. Due to the method I used to migrate my data, those tables ended up with no rows in them as there was nothing to import.

It seems that CMSMS does not rely on MySQL's auto-increment behaviour at all, but stores the sequence number for each table in the meta *_seq table, with the sequence number corresponding to the last_insert_id. Once I inserted the correct values into cms_content_seq and cms_content_props_seq, new content creation appears to be functioning as expected.

I'll now go back and populate the other *_seq tables.