12 hours plus lost to a bad database export / import. This one, it turned out, was entirely my own fault.

Here is what I was seeing. I had uploaded my database to this server and transferred ALL of the files from my 'base' installation on my own PC. When ever I added any new item to any of the menus things went seriously pear shaped - server 500 errors in the front end. In the /administrator/ section of the site every item in the menu was displayed to the right of the column in 'menu manager'. Every item was in effect a 10th level submenu entry?? Backtracking and removing the new menu entry made ALL entries disappear from the menu manager.

So what was going on?

A quick look in phpMyAdmin revealed the remaining entries had their 'published' setting set to -2. I have no idea what that means but it was wrong - I think the settings are usually 0 and 1 (for no yes).  It turns out that my menu database table had lost its 'AUTOINCREMENT' setting. New entries went in as item zero - the root or parent item. All existing items saw the new item as their parent, this parent pointed to the other zero and thought it was the parent, the parent thought the other zero was the parent. This cycle of parent, child, child, child, etc set everything to the maximum depth - and fritzed everything. Removing what appeared to be a parent item orphaned the child items (which must be what -2 means) exists but not owned by anyone so hidden from the menu AND the menu manager.

I have seen instances in the past where a database had lost ALL of its AUTOINCREMENT settings - usually a mismatch in phpMyAdmin versions (or MySQL versions) on the two systems (source and destination). In this instance I knew this wasn't the case. I move at least one database per day from my PC to this same webhost - or vice-versa. So if it wasn't phpMyAdmin then what had killed my database.

Then the penny dropped 

Then I remembered. I have been working on a tool to manipulate databases to try and remove some of the tedium from my busy schedule. I have grown weary of installing a fresh Joomla instance and manually installing all of the same components, modules, mambots and templates over and over again. My idea is simple - in theory - more complex in practise. I now maintain ONE and only ONE 'base' installation. That is to say one copy of Joomla installed complete with those elements I *always* install into a new site. If I need to quickly build a new site I export a copy of the database, import it into the destination and upload the files. Simple yes - it takes me about 5 minutes to set up a new site.Remember I said it isn't quite that straightforward. I can only have a limited number of databases on my hosting account - about 15 I think at present. Between my clients sites, my sites, my demo sites, and development sites I am approaching that limit. I need to make better use of those databases - 1 db per site is wasteful. Joomla has the ability to host multiple joomla sites within one database - as long as the table prefixes are different. Now do you see my problem. How does my one 'base' installation export multiple copies that can co-exist in one database when the table names will be repeated with each cloned database. It is a puzzler isn't it.

The long way around - I could copy the 'base' database to a new database on my local PC, rename 50+ tables manually to have different prefixes and then export from this temporary database and import into the destination database. It would work but would take a while to do each time and would risk typos and other 'human error'.

Well being a part time code monkey (programmer) the answer seemed obvious - write a script to copy the database tables and rename them on the fly. Then export the new tables and import as normal into the destination database. The tables would be given a different prefix each time I do this and so would co-operate with any and all existing joomla table sets.

So I saw the problem as four stage process

1. specify details: source database, current table prefix, output prefix,
2. Get a list of the tables in the database - and cycle through them all
3. Calculate the name the table will have afterwards: current table name, with the prefix removed and new prefix pre-pended.
4. Copy the source table's data to a new table with our previously calculated name.

   
Well - sounds easy enough.

1. Specifying details - for now I will just set them manually in a config block at the top of the script. Later I will move to an initial settings screen to request the data from the user.
2. Get a list of tables - the SQL query "SHOW tables" will retrieve a list of all of the tables in a database.
   $result = mysql_query("SHOW tables", $db_connection);
   we can then cycle through the table names contained in $result.
3. Basic string (text) functions can easily find our prefix at the start of the table name, remove it and then place the remnant at the end of our new prefix.
4. This just leaves the simple matter of copying the data within a table into a new table with our chosen name. How difficult can that be. This must be a basic function of any database / programming language.

Well, where do you start when you don't know the exact syntax to achieve a given result - yes, you do a search using your favourite search engine for the correct way to programatically copy a database table

So a search term like "SQL copy database table" (with or without the quotes) should take us somewhere close

It won't take you very long to come across this little gem - you will see this all over the internethttp://www.plus2net.com/sql_tutorial/sql_copy_table.php  (do not follow the advice in this link)

CREATE TABLE student2 SELECT * FROM student

I found variations on a theme but the above article is particularly galling - because it sounds authoritative - when the first three or four 'tutorial' type sites you find offer effectively the same advice and it looks about right, you are going to try it - honestly.

Not only that - but it worked. I ran my script, and after the usual period of debugging I had a second copy of ALL of my database tables with new names. To test the database I changed the table prefix in my configuration.php and ran my base site from the new tables.

Job done - and in not much more time than it would have taken to do the job manually, and I now have a re-usable tool that rename tables, copy tables to a new name within the same database, and with a little tweaking it will be able to copy them to a new database.

This was how I produced my database - and this was the cause of my problems. You see, whilst this command above creates a new table with the anme you specify, and populates it with a total copy of all of the data in the table you are copying from - it DOES NOT copy across AUTOINCREMENT, PRIMARY KEY status, INDEXES and any other attributes of the table structure. It literally copies the database data and just enough 'structure' to fool me.

Now it took forever to solve this - with hindsight it took longer than it should have - and then I had to correct the script with the correct way to copy a table.

CREATE TABLE new_table LIKE old_table

This copies the structure of the old_table including Keys and AUTOINCREMENT settings.
Once the table is created it can be populated with a standard INSERT INTO new_table (SELECT * FROM old_table)

So withe lesson learnt, it was time to delete my previous database, use my new script to copy and rename tables, then export and import to the site. To prove it worked I created a new menu item or two and saw no problems whatsoever.

If anyone else thinks that my little scriptlet will be useful I will consider polishing it up in due course, or at least adding it to my list of potential future projects.

Now I feel at home - my first post and it's a long one.

Dean (Verbose) Marshall