CMS Made Simple Core


[#11594] Can't upgrade site with no database table prefix

Created By: Matt Hornsby (DIGI3) (DIGI3)
Date Submitted: Wed Oct 04 20:43:11 -0400 2017

Assigned To:
CMSMS Version:
Severity: Minor
Resolution: Fixed
State: Closed
Can't upgrade site with no database table prefix
Detailed Description:
If you have an older site that was created with no database prefix
($config['db_prefix'] = ''; in config.php) and try to upgrade, the installer
will give you the error message 'please enter a valid prefix for database
tables' and can't proceed with the upgrade.
If you add the prefix to the database, the upgrade will work, but page indexes
are lost - menus will only show the current page.

Steps to reproduce:
Have older site (in my case 2.1.2) with no database prefix
Run upgrade assistant

currently none, restore from backup and wait for fix before upgrading


Date: 2017-10-13 11:25
Posted By: SlurmMcKenzie (SlurmMcKenzie)

Thank you.
I was trying to upgrade my page too, but ran into the same problem.
If i entered something into the prefix field it did start the file upgrade, but
failed at the database upgrade.
(leaving the prefix empty was not allowed.)
The result was a mix of different versions, that could work, but mostly failed.

Unfortunatelly i did search for too long at the wrong place for a solution.
I suspected my database contained some error.

Since i wanted a clean solution for future updates, i was pretty close to
performing a clean install and porting the data to the new installation.

But your post set me on the right track.
Apperently, CMSMS requires a database prefix now and the new setup / upgrade
script does not check for database without prefix anymore. It now just assumes
that everyone has a prefix.
This is made worse by the fact, that the upgrade dialog does not mark the prefix
field as mandatory.

Since all of this will not get any better in the future, the only real solution
is to add a prefix to your old database, prior to the upgrade. Every workaround
will most likely cause problems with the next update again.

Fortunately adding the prefix should be relatively easy, but requires some
manual database modification.
In my case i did it like this.
I manualy edited the config.php file and set the prefix entry to:
$config['db_prefix'] = 'cms_';
(My prefix is now "cms_" )

Then i logged into the database admin tool, provided by my hoster.
I exported the whole database to a plain SQL file. 
Save the file to the pc an make a copy in case something goes wrong.

Now you can use an advanced texteditor like notepadd++ to edit the SQL file.
Use the search and replace funktion and run these 4 simple replacement
(Without the " " )

Search: "INSERT INTO `" Replace: "INSERT INTO `cms_"
Search: "ALTER TABLE `" Replace: "ALTER TABLE `cms_"
Search: "REFERENCES `" Replace: "REFERENCES `cms_"

Safe the file.
Delete everything from your database.
Import the new file.
The data is identical, just the table names contain the prefix now.
It should import just fine. 
If there is an error edit the file accordingly (should only be a name reference
error), delete database again, and try new import.

With the changes in the config.php file, the site should work again instantly,
and you now have a database prefix for easy future updates.
Date: 2017-10-13 11:37
Posted By: SlurmMcKenzie (SlurmMcKenzie)

Forgot something.
Be careful if you have other database tables in that database, which are not
part of CMSMS.
These will be renamed too by the search and replace commands, and will not work
afterwards, since the application looking for these is not aware of the new

In my case that was not too big of a problem. 
I just had 9 tables that i created for another purpose and i manually removed
the "cms_"-prefix on the "create", and "insert" commands for these few tables.
Date: 2017-10-13 11:39
Posted By: Matt Hornsby (DIGI3) (DIGI3)

You could also just wait for the next version, as far as I know this has been
fixed in svn.
Date: 2017-10-13 15:31
Posted By: Robert Campbell (calguy1000)

already fixed in svn.  waiting testing
Date: 2017-11-25 12:27
Posted By: Robert Campbell (calguy1000)

version 2.2.4 is released

Updated: 2017-11-25 12:27
state: Open => Closed

Updated: 2017-10-13 15:31
resolution_id: => 7