Support

Akeeba Backup for Joomla!

#8736 big dilemma how to upgrade with changed databasetable structure and yet keep the old data?

Posted in ‘Akeeba Backup for Joomla! 4 & 5’
This is a public ticket

Everybody will be able to see its contents. Do not include usernames, passwords or any other sensitive information.

Environment Information

Joomla! version
n/a
PHP version
n/a
Akeeba Backup version
n/a

Latest post by nicholas on Wednesday, 15 December 2010 03:03 CST

Saturday, 11 December 2010 17:03 CST

user13191
I have a local development site and a life site, where people are feeding info in the database via forms etc.
Before the site was open for users I just made a fullsite backup of my localhost site, put the archive on the internet site and the kickstart.php and voila, my internet site was "upgraded" in no-time.
And that is what I need, because they are feeding it with new information continously, day and night.
Hard to find a moment to upgrade and then it must be done very fast.

So I try to prepare everything on my localhost as much as possible, make a backup and then install this on the internet.

From the moment the site is open for users this becomes a lot more tricky of course, because I don't want to replace their entries with the dummy ones on my local host which are there only for developmental reasons.

So I bought the proversion of akeeba backup because here I can pick more precisely what to include or exlude in the backup.
For instance when I exlude the jos_content table from the backup of my localhost, make a backup and then restore that with kickstart on my lifesite, the jos_content table will still be there untouched, isn't it?
Because the option "drop tables" in kickstart only applies to tables with the same name, right?

Now I have the following problem:
I have a table on my life site and people are adding data to that table.
On my localhost I made a lot of changes to the structure of that particular table.
Changes like varchar options, and changing the kind of field etc.

In the proversion, when I am making a backup of my localhost I check the second item (no content), because the content on my localhost is just nonsense for developmental reasons.

When I make a backup and put that on my life site and install with kickstart, I have this table with the right structure but all the previous data gone.
Boy was I glad I did that on a demosite first?
I stupidly thought that when I said not to save the data on my localhost, it would let alone the data on my life-site.
After thinking through I kind of understan the logic.

The "drop table" option in kickstart means that everytable of the same name will be replaced by the table of that name in the backup.
Isn't it?

Now here is my problem:
After installing the new "upgrade" to my life site I have a few empty tables but with the new structure, most of which are changes in varchar options etc.

I want to get my old data back in this table.
How do I do that?

I have the following plan, but don't know how to execute that plan correctly.
Before I do anything I first go to phpmyadmin and save the table in question as "data only" in a sql file.( so not the structure but only the data and only for this particular table).
Then after the whole process I just can import this file in the just table.

Is this a prudent way to go or am I just stupid to think it would be that easy?
I am doubting more and more because I just can't find anything on the internet.
That's the reason I ask you, doesn't anybody has the same problem or maybe I am looking in the wrong direction?

But if my idea is right:
What settings must I use in phpmyadmin to save only my tables data and not the structure in my sql file?

Please help me
Greetings harbrum
T





Sunday, 12 December 2010 03:16 CST

nicholas
Your procedure is correct, until you try handling the table whose structure you've changed. You can't back up this table. Backing up the structure of the table means issuing a DROP TABLE and a CREATE TABLE command during restoration, which removes the existing content on the site you are restoring to. The solution is intertwined, but achievable.

First, record all changes you have made to the structure of that table and create ALTER TABLE commands so that you can replicate them at will.

Second, create a partial (no content) backup as you described it, completely excluding the table with the modified structure and restore to the live site. Then, immediately, take the live site off-line.

Finally, using your host's phpMyAdmin apply the ALTER TABLE commands from the first step to replicate the structure changes without affecting the content. Then take the site back on-line.

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

Sunday, 12 December 2010 10:35 CST

user13191
Hi Nicholas,
Thank you for your answer.I am not familiar with those ALTER TABLE commands
so I will have to study that before I will be able to do that of course.
I am working with the extension Fabrik with Joomla, so I could do this changes online also I guess.

But maybe you think I am a little stubborn but I would really like to know what is wrong with my idea doing it the other way around, because I tried that and it seems to work OK.

I do the following:
first I make a full backup of my life-site. (so I can allways put that back.
Then I export the table in question as data only - so I just uncheck structure- in PhpMyAdmin, while the site is offline.

I make a backup with akeeba including this table, but without content beforeehand. Now I put this backup and kickstart.php on my site and run kickstart and the istaller.

Indeed at the end of the proces I have my site, but because of the drop tables thing this specific table is empty, but has the right structure.

Now I just import my sql file with the only data export with PhpMyAdmin and hopla my table is filled again with the saved data.

I checked things but all seems to be right.
So what is wrong with this procedure in your opinion?
Do I overlook something?
I am very curious about the reason why I shouldn't do this, so I can understand it better.
Greetings, harbrum

Sunday, 12 December 2010 16:56 CST

nicholas
Your idea might work, depending on the changes you made to your table. If you have changed the type of some fields in a way that makes the old data incompatible with the new structure or if you have added NOT NULL columns, the data import will fail. However, you can easily test that.

First go to the origina site's phpMyAdmin and create a data dump of that table. Here's how. Open phpMyAdmin's page and click on the table name. Click on the Export tab. Choose SQL in the "Export" pane. Uncheck the "Structure" checkbox on the right. Make sure that "Data" is checked, as well as its sub-options "Complete inserts" and "Extended inserts". Click on Go and download the resulting SQL file.

Back to the new site's phpMyAdmin, choose Import and import the SQL file you created in the previous step. If you're lucky, the data will be restored. If this doesn't work, you'll have to follow the other method which is tedious, as you haven't created the ALTER TABLE commands manually, but relied on Fabrik to do that.

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

Monday, 13 December 2010 19:28 CST

user13191
Hi Nicholas,
Thank you very very much for your answer!!

Fortunately I was lucky, because everything went well.
What I was looking for were the settings I have to use when exporting the data (only)options in phpMyAdmin.
I searched the internet and could not find an answer to that.
You told me this step by step and I am very gratefull for that.
At the moment everything is working fine.

But I understand I am just "lucky" that it worked my way and I could better learn to do it your way also in case my way is not working right - and your way is the other way around, not changing the content, but changing the structure by hand in phpMyAdmin.
Am I right?

I mean there are two ways to do this if I understand you right:
1)saving the whole table (with content)with akeeba and then later on changing the structure of the table manually with phpMyAdmin ALTER TABLE commands.(your way).

2)saving only the structure of the table with no content with akeeba and later on import a previously exported sql- file with the data for this table exported as a data - only export.(my way).

Thus far I have been "lucky" things got right my way, but I can imagine that I will encounter situations that I need your way sooner or later.

So I would appreciate it very much if you would learn me how to do the ALTER DATA stuff.

As I told you I am working with fabrik they do this for me.
You said it would be "tedious" because of that, but I don't mind that.
Can you please help me with this?
I really would appreciate it and frankly I have no clue even where to begin with.
Greetings,
harbrum




















Edited by user13191 on 2010-12-13 19:35 CST

Tuesday, 14 December 2010 06:37 CST

nicholas
I am glad it worked out fine! Yup, you have got it right. These are the two ways to perform changes in the table structure. I usually recommend using ALTER TABLE commands for two reasons:
1. This is what is done behind the scenes by the CCK you're using
2. ALTER TABLE commands allow you to change the table structure in a way which doesn't invalidate existing data rows.

The first step to figuring out those commands is to issue a SHOW CREATE TABLE `my_table_name` which dumps the SQL commands for creating the table. Doing that in the original and final state of the table allows you to see which row definitions have been modified, which have been added etc. From that point you can experiment creating and running ALTER TABLE commands against a copy of that table. For more information on ALTER TABLE commands, you can take a look at the MySQL reference for ALTER TABLE.

The big downside of this method is that you, evidently, have to learn SQL commands. Nobody was born knowing it everything, yours truly included. I learned SQL commands by experimenting. I suggest you do the same. Practicing on a local copy of the table is very safe and will allow you to roll back unwanted changes very easily. The first time you try it, it might take you a week to figure out which ALTER TABLE commands you should issue. The good thing is that doing similar work the next time will only take you about an hour. After a few practice rounds it'll take you just a few minutes to figure out the necessary commands.

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

Tuesday, 14 December 2010 08:55 CST

user13191
Hi Nicholas,
Thank you so much for your answer.
You made this crystal clear for me and I can work with this explanation of you.
I now fully understand how things work.
This topic can be closed
Greetings, harbrum.

Wednesday, 15 December 2010 03:03 CST

nicholas
You're welcome!

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

Support Information

Working hours: We are open Monday to Friday, 9am to 7pm Cyprus timezone (EET / EEST). Support is provided by the same developers writing the software, all of which live in Europe. You can still file tickets outside of our working hours, but we cannot respond to them until we're back at the office.

Support policy: We would like to kindly inform you that when using our support you have already agreed to the Support Policy which is part of our Terms of Service. Thank you for your understanding and for helping us help you!