MySQL tables may lack primary keys

[Charles Butcher]

I have a working install of CMSMS 2.2.10 but I believe none of my database tables has a primary key. Or at least if they do it's not marked as such. If that's true, should I change something?

The background is that I planned to use a search-and-replace script to update the styles and CSS classes assigned to a bunch of content items. But the script says, for instance: "The table 'cms_module_news' has no primary key."

Sequel Pro seems to confirm this, and in phpMyAdmin I can see no mention of primary keys, whereas they seem to be there when I look at the databases for other CMSs (WP and Piwigo).

Probably needless to say, I am not at all familiar with MySQL. This database is actually MariaDB, supplied by a hosting company to which I moved a couple of weeks ago. I dumped everything to an .sql file using the simplest settings, and imported it to the new database.

When I look at the old (MySQL) database, it too seems to have no primary keys. Yet CMSMS has been running for years without problems.

Should I be worried about this, and is there a fix that will let me run SQL commands?

[calguy1000]

Yes, of course you should be worried about this.

I can confirm that on MOST tables (not all) there is a primary key.

The cms_module_news table is certainly supposed to have one.
Something must be wonky with your host.

Here is what I get when I do a 'show create table cms_module_news;'

Code: Select all

CREATE TABLE `cms_module_news` (
  `news_id` int(11) NOT NULL,
  `news_category_id` int(11) DEFAULT NULL,
  `news_title` varchar(255) DEFAULT NULL,
  `news_data` text,
  `news_date` datetime DEFAULT NULL,
  `summary` text,
  `start_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `status` varchar(25) DEFAULT NULL,
  `icon` varchar(255) DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `modified_date` datetime DEFAULT NULL,
  `author_id` int(11) DEFAULT NULL,
  `news_extra` varchar(255) DEFAULT NULL,
  `news_url` varchar(255) DEFAULT NULL,
  `searchable` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`news_id`),
  KEY `cms_news_postdate` (`news_date`),
  KEY `cms_news_daterange` (`start_time`,`end_time`),
  KEY `cms_news_author` (`author_id`),
  KEY `cms_news_hier` (`news_category_id`),
  KEY `cms_news_url` (`news_url`),
  KEY `cms_news_startenddate` (`start_time`,`end_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

[Charles Butcher]

Thanks very much. I get:

Code: Select all

CREATE TABLE `cms_module_news` (
  `news_id` int(11) NOT NULL,
  `news_category_id` int(11) DEFAULT NULL,
  `news_title` varchar(255) DEFAULT NULL,
  `news_data` text,
  `news_date` datetime DEFAULT NULL,
  `summary` text,
  `start_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `status` varchar(25) DEFAULT NULL,
  `icon` varchar(255) DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `modified_date` datetime DEFAULT NULL,
  `author_id` int(11) DEFAULT NULL,
  `news_extra` varchar(255) DEFAULT NULL,
  `news_url` varchar(255) DEFAULT NULL,
  `searchable` tinyint(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8

i.e. without any keys, otherwise the same. I'll ask my host.

[Charles Butcher]

Hosting company was not very helpful, which is fair enough since it turns out the version I exported from the previous hosting company lacked keys too. This database is ten years old and has been successively upgraded since CMSMS 1.x.

Running CREATE TABLE across the whole DB shows 62 differences between the existing one and a new install.

The primary keys are easy to fix, especially if I can get my head round the SQL needed to do them all at once. Correcting all the keys for `cms_module_news` didn't seem to break anything.

The other non-primary key fields are interesting, because where they exist in the old DB they often have different names. For example, in `cms_content_props` my DB has KEY `content_id` (`content_id`) while the clean install has KEY `cms_idx_content_props_by_content` (`content_id`).

There are a few other differences:

1. One or two fields are ,missing, e.g. `cms_module_news_categories` in the new DB has a field `item_order`, whereas my DB doesn't.
2. A few tables are InnoDB rather than MyISAM.
3. Some fields have changed from DEFAULT NULL to NOT NULL.
4. A few field sizes and increment values have changed (I guess that's not important).

Do you think I should and can change all of this safely?

[Dr.CSS]

Have you tried doing an upgrade, even if it is the same version, maybe that might fix/add these missing items..?

[Charles Butcher]

Have you tried doing an upgrade, even if it is the same version, maybe that might fix/add these missing items..?

Thank you. I will try this when I have time. This database has seen many CMSMS upgrades over the years, and looking at the code in the installer code I think it's unlikely that it will change the structure of an existing DB.

But it's worth a go. And `schema.php` in the installer gives a good clue to how the structure is supposed to look.

In the end there were not more than 3 or 4 missing primary keys, of which `cms_module_news` was by far the most important. But a lot of the other keys certainly have different names, even though they refer to the correct fields. I'm not sure if that's important.

[calguy1000]

An upgrade won't solve this problem.

[Charles Butcher]

An upgrade won't solve this problem.

Thanks. I'll do it manually.