renaming DB tables for compatibility?

Hi -

So, my database tables have a "zen_" prefix. This is causing some inconvenience, as at least one third-party add-on uses an install script that doesn't work with such prefixes.

Can anyone tell me what I have to do to strip out these prefixes? Is it as simple as using mySQL to go in and edit the table names? (Of course, I then have to modify my configure.php files, too.)

Thanks for any assistance.

Add-ons that have a SQL patch should have the patch run through "Install SQL Patches" in your store admin console.

ZC will know if the tables are prefixed, and if they are, the appropriate prefix will be added when the patch is run.

If you use phpMyAdmin to do the sql patching, then it's just a case of editing IN the prefix, where appropriate.

Hi, SB –

I did in fact use the "Install SQL Patches" facility. The author of the add-on recommended pasting the contents of the script into the box, but I tried it both that way and simply uploading the file. The result was the same:

1146 Table 'cindere8_znc1.configuration_group' doesn't exist
in:
[SELECT @cgi := configuration_group_id FROM configuration_group WHERE configuration_group_title = 'Zen Lightbox';]
If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields.

I'm ASSUMING that, since this error message looks very similar to the one I was getting before I fixed my configure files, that this is a prefix problem. If this assumption is incorrect, though, then I'll listen to whatever you suggest.

When you moved your database, it failed to create a table called:

configuration_group.

The problem is not prefixes. It is a missing table.

Go into phpMyAdmin and see if you have a table called configuration_group.

Another way to check is to go into your shop admin and see if you have a dropdown menu for configuration.

According to mySQL, there is indeed such a table (with the "zen_") prefix, of course. It is one of 95 tables in the new DB, which (from memory) is the same number the old DB had.

Hmmmm... who is hosting your site? It looks like one of those set ups that houses the dbase on a separate server.

When you set up your configure.php files, did you use** localhost**, or is there a different server address?

When I said "mySQL" above, I meant "phpMyAdmin." The table is there (again, with the prefix). And, the "Configuration" menu does appear on the admin page.

My host is bluehost. The configure files use localhost.

Everything in the stock admin area seems to work fine, so I'm kind of reluctant to believe the problem's not limited to this add-on.

One possible glitch is that I keep all of my ZC files under a directory called "shop" and use an .htaccess file to redirect from the root to /shop. Perhaps the script is tripping up on this? Just a wild guess.

mzimmers:

One possible glitch is that I keep all of my ZC files under a directory called "shop" and use an .htaccess file to redirect from the root to /shop. Perhaps the script is tripping up on this? Just a wild guess.

If you temporarily disable .htaccess (call it .htaccess-suspended) and then try? What happens?

You can always run patches via phpMyAdmin... just remember to edit in the zen_ prefix where necessary... This will ONLY be on the table name, NOT the field names within a table.

eg:
products (table) becomes...

zen_products (table)

products_id (field so NO prefix)

schoolboy:

If you temporarily disable .htaccess (call it .htaccess-suspended) and then try? What happens?
Same thing. (I'm actually relieved about that.)

schoolboy:

You can always run patches via phpMyAdmin... just remember to edit in the zen_ prefix where necessary... This will ONLY be on the table name, NOT the field names within a table.

eg:
products (table) becomes...

zen_products (table)

products_id (field so NO prefix)

I could do that, I guess. Wouldn't it seem a better long-term fix, though, to strip out those prefixes from the DB? I don't even remember making them, let alone why I would do that, and they don't seem to be necessary.

If that's not a good idea, though, I'll try to edit the patch file.

Thanks.

Edit: I just discovered another issue: under the Configuration menu, I now have a bunch of Zen Lightbox entries, presumably from the bad installation attempts. How do I get rid of those?

As long as you will never need to put another program's database in the same container, I would agree with you. The prefix is necessary if you only are allowed one db and need to run two programs. A SQL expert would be helpful to give the complete process for removing the prefix.
Manual install gives the option of defining a prefix, or choosing no prefix.

Aargh.

My attempts to edit the patch file resulted in a different error. Now, when I upload the file, I get multiple occurrences of:

ERROR: Cannot execute because table zen_configuration_group does not exist. CHECK PREFIXES!

According to phpMyAdmin, it does indeed exist. I think I'm somewhere in limbo between ZC not expecting the prefix, and the patch requiring them.

To add insult to injury, it creates a new table, zen_zen_ upgrade_exceptions, and puts the error messages in there.

Glenn, I don't have a SQL expert here...how difficult is it to manually rename the tables? If I downloaded the DB, edited the file and removed all occurrences of "zen_" would that do it, or are there other instances of that text that need to remain? I can't see yet how to modify table names through phpMyAdmin.

Thanks.

That's why I suggested an expert... I might be able to muddle through it, but would not want to experiment on a live database. There should be ways within phpMyAdmin.

Hopefully one will drop in here.

Mike... lesson one... (I learned it the hard way)...

Fiddle only with backup sites or test sites.

I have just done a MAJOR overhaul of a very busy UK site (turning over in excess of £10,000 a day, with more than 10,000 products and 15,000 customers) and the FIRST thing I did was create a full CLONE of the site, port it all to my dev servers, and practice all my tweaking on that.

One of the procedures involved changing all image file extensions from gif and bmp , to jpg (data digger helped me with the global sql command here). Another involved a full reassignment of product sort codes, using the category tree sort codes as base references. So if a product was sort code 112, and its category sort code was 44, and that category was inside another with a sort code of 27, then the PRODUCT sort code had to be a concatenation of 27, and 44, and 112 - to read 2744112.

A myriad of PHP and SQL scripts were written to achieve this...

Then we have to get all the bmp and gif images, resample them, save them as jpg, and then transfer everything to a NEW images folder.

I am quite experienced at matters relating to ZC, but during our trials I made several fatal errors on the process and killed the cloned database 4 times.

Did I worry? not a bit... I just dropped all the tables from the database and reinstalled it from the original sql dump.

The whole project took 38 hours - but the live site wasn't touched until we were ABSOLUTELY sure that the cloned site worked flawlessly.

Do your tinkering on a clone of your site... :smile:

Point taken, Schoolboy. I'm not worried about downtime, though, so I think I'll just back up the DB and try the global substitution within the .sql file, and see if that works. As long as I keep a copy of the DB, I don't see that I can do too much harm.

If it doesn't work, I guess I'll manually rename all the tables (tedious but not terribly so). I guess the one thing I'm wondering is, within the DB itself, are there internal references to table names that will get screwed up if I make these changes? This question is probably beyond the scope of this forum, but I figured I'd ask to see if anyone had any knowledge of the DB internals.

Thanks.

OK, I just downloaded a backup of the DB, edited all table references from "zen_" to "" (nothing), dropped the old DB tables and uploaded. It appears to be working just fine, in both the admin and shop areas.

Anyone wishing to do this themselves should note that a global substitution of "zen_" won't work, as it will also pick up some function names (and a very few gif names). A global substitution of "`zen_", however, seems to be OK. At least it was for my site; YMMV and all that.

Thanks to those who gave me their input and imbued me with the guts to give this a try. I'm keeping my fingers crossed that we can consider this one closed out.

mz

Global substitution via search-and-replace is always dangerous in SQL files. Michael has just mentioned some of the reasons. Another reason is the risk of data corruption during export and then edit and then import. Sometimes editors muck-up files, and sometimes uploads/imports fail esp with large databases. It's way more risk than necessary.

If you want to change the database table names, you can do so in a few ways:
a) use the supplied option inside zc_install on the database-upgrade page ... it'll do all the work for you in seconds
b) manually use phpMyAdmin to rename all the tables and then manually change the DB_PREFIX in your configure.php files
c) hand-edit the table names in an export of the database to a .sql file, then delete your database and re-import everything .... but if you have a live database, this is really not worth the risk

Aha...I was unaware of the option to use zc_install for this. I'll definitely keep this in mind. Thanks, Doc.