• twitter image
  • facebook image
  • youtube image
  • linkedin image
Language: CMS Made Simple Czech CMS Made Simple France CMS Made Simple Hungary CMS Made Simple Russia CMS Made Simple Netherlands

All times are UTC




Post new topic Reply to topic  [ 8 posts ] 
Author Message
 Post subject: MySQL tables may lack primary keys
PostPosted: Wed Apr 10, 2019 1:47 pm 
Offline
Forum Members
Forum Members
User avatar

Joined: Sat Jul 05, 2008 8:25 pm
Posts: 88
Location: Norfolk, UK
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?


Top
  Profile  
 
Share On:
Share on Facebook Share on Twitter Share on Google+
 Post subject: Re: MySQL tables may lack primary keys
PostPosted: Wed Apr 10, 2019 4:18 pm 
Offline
Dev Team Member
Dev Team Member
User avatar

Joined: Tue Oct 19, 2004 6:44 pm
Posts: 7998
Location: Fernie British Columbia, Canada
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:
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.


Top
  Profile  
 
Share On:
Share on Facebook Share on Twitter Share on Google+
 Post subject: Re: MySQL tables may lack primary keys
PostPosted: Wed Apr 10, 2019 5:28 pm 
Offline
Forum Members
Forum Members
User avatar

Joined: Sat Jul 05, 2008 8:25 pm
Posts: 88
Location: Norfolk, UK
Thanks very much. I get:

Code:
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.


Top
  Profile  
 
Share On:
Share on Facebook Share on Twitter Share on Google+
 Post subject: Re: MySQL tables may lack primary keys
PostPosted: Fri Apr 12, 2019 9:52 pm 
Offline
Forum Members
Forum Members
User avatar

Joined: Sat Jul 05, 2008 8:25 pm
Posts: 88
Location: Norfolk, UK
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?


Top
  Profile  
 
Share On:
Share on Facebook Share on Twitter Share on Google+
 Post subject: Re: MySQL tables may lack primary keys
PostPosted: Sat Apr 13, 2019 12:23 am 
Offline
Administrator
Administrator
User avatar

Joined: Thu Mar 09, 2006 5:32 am
Posts: 12656
Location: Arizona
Have you tried doing an upgrade, even if it is the same version, maybe that might fix/add these missing items..?

_________________
Check ver. CMSMS, PHP, server OS, in System Information page.
Default content http://multiintech.com/defaultcontent/
People are Wonderful
Business is Great
Life is Terrific
Ever wonder what happened to the Album module? Well it is alive and well.
http://album.multiintech.com/
Image


Top
  Profile  
 
Share On:
Share on Facebook Share on Twitter Share on Google+
 Post subject: Re: MySQL tables may lack primary keys
PostPosted: Sat Apr 13, 2019 9:38 am 
Offline
Forum Members
Forum Members
User avatar

Joined: Sat Jul 05, 2008 8:25 pm
Posts: 88
Location: Norfolk, UK
Dr.CSS wrote:
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.


Top
  Profile  
 
Share On:
Share on Facebook Share on Twitter Share on Google+
 Post subject: Re: MySQL tables may lack primary keys
PostPosted: Sat Apr 13, 2019 11:55 am 
Offline
Dev Team Member
Dev Team Member
User avatar

Joined: Tue Oct 19, 2004 6:44 pm
Posts: 7998
Location: Fernie British Columbia, Canada
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.


Top
  Profile  
 
Share On:
Share on Facebook Share on Twitter Share on Google+
 Post subject: Re: MySQL tables may lack primary keys
PostPosted: Sat Apr 13, 2019 12:00 pm 
Offline
Forum Members
Forum Members
User avatar

Joined: Sat Jul 05, 2008 8:25 pm
Posts: 88
Location: Norfolk, UK
calguy1000 wrote:
An upgrade won't solve this problem.


Thanks. I'll do it manually.


Top
  Profile  
 
Share On:
Share on Facebook Share on Twitter Share on Google+
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 8 posts ] 

All times are UTC


Who is online

Users browsing this forum: No registered users


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Hosting Nation - Managed CMSMS Hosting