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?
MySQL tables may lack primary keys
- Charles Butcher
- Forum Members
- Posts: 102
- Joined: Sat Jul 05, 2008 8:25 pm
- Location: Norfolk, UK
-
- Support Guru
- Posts: 8169
- Joined: Tue Oct 19, 2004 6:44 pm
- Location: Fernie British Columbia, Canada
Re: MySQL tables may lack primary keys
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;'
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
Follow me on twitter
Please post system information from "Extensions >> System Information" (there is a bbcode option) on all posts asking for assistance.
--------------------
If you can't bother explaining your problem well, you shouldn't expect much in the way of assistance.
Please post system information from "Extensions >> System Information" (there is a bbcode option) on all posts asking for assistance.
--------------------
If you can't bother explaining your problem well, you shouldn't expect much in the way of assistance.
- Charles Butcher
- Forum Members
- Posts: 102
- Joined: Sat Jul 05, 2008 8:25 pm
- Location: Norfolk, UK
Re: MySQL tables may lack primary keys
Thanks very much. I get:
i.e. without any keys, otherwise the same. I'll ask my host.
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
- Charles Butcher
- Forum Members
- Posts: 102
- Joined: Sat Jul 05, 2008 8:25 pm
- Location: Norfolk, UK
Re: MySQL tables may lack primary keys
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:
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:
- 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.
- A few tables are InnoDB rather than MyISAM.
- Some fields have changed from DEFAULT NULL to NOT NULL.
- A few field sizes and increment values have changed (I guess that's not important).
Re: MySQL tables may lack primary keys
Have you tried doing an upgrade, even if it is the same version, maybe that might fix/add these missing items..?
- Charles Butcher
- Forum Members
- Posts: 102
- Joined: Sat Jul 05, 2008 8:25 pm
- Location: Norfolk, UK
Re: MySQL tables may lack primary keys
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.Dr.CSS wrote:Have you tried doing an upgrade, even if it is the same version, maybe that might fix/add these missing items..?
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.
-
- Support Guru
- Posts: 8169
- Joined: Tue Oct 19, 2004 6:44 pm
- Location: Fernie British Columbia, Canada
Re: MySQL tables may lack primary keys
An upgrade won't solve this problem.
Follow me on twitter
Please post system information from "Extensions >> System Information" (there is a bbcode option) on all posts asking for assistance.
--------------------
If you can't bother explaining your problem well, you shouldn't expect much in the way of assistance.
Please post system information from "Extensions >> System Information" (there is a bbcode option) on all posts asking for assistance.
--------------------
If you can't bother explaining your problem well, you shouldn't expect much in the way of assistance.
- Charles Butcher
- Forum Members
- Posts: 102
- Joined: Sat Jul 05, 2008 8:25 pm
- Location: Norfolk, UK
Re: MySQL tables may lack primary keys
Thanks. I'll do it manually.calguy1000 wrote:An upgrade won't solve this problem.