Data loss when publishing BSV

kklosson

I have had three identifiable instances when a change to a business object resulted in the loss of data when publishing.

In each case, I simply added an attribute. The attribute was added just fine but in testing I noticed that another attribute was dropped and then added as the right-most column, and of course any existing data in that column was cleared. This last time, I didn't catch it and it went to production. Fortunately, the attribute simply joined the first and last name to make a full name, so I can run a process to fix them all.

But this gives me great pause and for now I will have to examine data tables closely when making such changes.

If anyone else has experienced this, please chime in so support can take some notice. If it's just me, I can't imagine the root cause. But it is definitely happening and it definitely should not.

Running AwareIM 5.7, MySQL 5.6

hpl123

This sounds no good and giving me nightmares 🙂. Can you reproduce the bug in any way and have support take a look? This is a very important potential bug.

kklosson

I wish I could. I wonder if there is any log or other file that includes the database changes made during publishing. And to be clear, it occurs when the app is "compiled" or whatever is the term when you put the business space into test. About all I can do is say "it happened again", but support would need to see more. It must be a very unique set of circumstances since I've never heard of anyone else reporting it. I couldn't reproduce it if I tried. But when this happens, I have to go back to the current BSV and restore the database. So I'm making snapshots any time I alter a BO.

weblike

I had this issue 2 months ago, but I thought that I did something wrong. I lost information not only from a column, but from entire BO. I had to restore the DB. Fortunately I am a backup maniac and I follow the message from AW before to publish an app.
In my case was also not a major release, just added some attributes in the BO.

hpl123

kklosson wrote
I wish I could. I wonder if there is any log or other file that includes the database changes made during publishing. And to be clear, it occurs when the app is "compiled" or whatever is the term when you put the business space into test. About all I can do is say "it happened again", but support would need to see more. It must be a very unique set of circumstances since I've never heard of anyone else reporting it. I couldn't reproduce it if I tried. But when this happens, I have to go back to the current BSV and restore the database. So I'm making snapshots any time I alter a BO.

Are you using NaviCat or MySQL Workbench? If so, there are monitoring features that monitor all operations to the database. I haven´t played around with this much, just tested it so don´t know how well you can use this "tool" for troubleshooting. If the monitor could e.g. record all operations, that should be enough for you to figure out what´s happening?

ACDC

kklosson.

You say you are using version 5.7, please can you confirm this.

Also, have you changed anything on the Mysql database connector side -

I recently did a publish update to a runtime version 5.7 on Myql 5.6 and in the process there was a corruption in the DB that would not allow users to login. So everyone was locked out . Fortunately I was able to recover from a database restore but was never able to find out what the problem was. Once I did the restore and followed through with the exact same bsv version it all worked out okay and was published.

So I am still none the wiser, my only suspicion lies with the version 5.6 MySql -

When problems like this occur its easy to resolve by going to backup. BUT if the problem of data loss occurs and its only discovered 3 weeks later then you are most certainly in the crap because you can't restore.

I would love to find a solution that can do a database compare. So each and every time a production update takes place part of the procedure is to compare the new version DB with the backed up version.

I suppose one could do a sql dump of both new and old db and then do a text compare. I would love to hear any other suggestions anyone may have on a best practice to prevent something like this from ever happening. This is definitely a vital step to any update on a production server

kklosson

Yes on AwareIM 5.7 and MySQL 5.6. I do believe my first encounter was on AwareIM 5.6. I see that Navicat has a history file. It's not exhaustive but it looks that it will report a dropped/created table. So if this happens again I can submit it as Exhibit A.

hpl123

ACDC wrote
I would love to find a solution that can do a database compare.

ACDC, I actually think NaviCat can do this and in a somewhat simple way i.e. no dump and text compare but rather automatic table etc. compare.

hpl123

kklosson wrote
Yes on AwareIM 5.7 and MySQL 5.6. I do believe my first encounter was on AwareIM 5.6. I see that Navicat has a history file. It's not exhaustive but it looks that it will report a dropped/created table. So if this happens again I can submit it as Exhibit A.

Great and please keep up informed.

ACDC

I don't use navicat, their trial version does not seem to feature a "data comparer". I use SQLyog and they feature a data comparer but only at a table level.

There is a product by Redgate called MySQL Data Comparer which scans the DB and presents that data differences in the whole DB in one scan . It works very well, a bit pricey though

http://www.red-gate.com/products/mysql/mysql-data-compare/

hpl123

Not sure about Navicat but after what i´ve read it seems it can be used for a good enough comparing of DB´s. I´m a DB (and Navicat) newbie though so might be things I don´t know but here is a link to the description of this functionality (if anyone knows for sure regarding Navicat or tries please share the results so we know if NaviCat can be used):
http://www.navicat.com/manual/online_manual/en/navicat/mac_manual/StructureSync.html

Here is another link with multiple resources that do comparing of DB´s:
http://stackoverflow.com/questions/225772/compare-two-mysql-databases

PS: Having this as a Aware IM standard functionality would be great but I don´t know if this is even possible but some kind or warning/rollback system could maybe be developed as a part of Aware IM?

hpl123

hpl123 wrote
Not sure about Navicat but after what i´ve read it seems it can be used for a good enough comparing of DB´s. I´m a DB (and Navicat) newbie though so might be things I don´t know but here is a link to the description of this functionality (if anyone knows for sure regarding Navicat or tries please share the results so we know if NaviCat can be used):
http://www.navicat.com/manual/online_manual/en/navicat/mac_manual/StructureSync.html

Here is another link with multiple resources that do comparing of DB´s:
http://stackoverflow.com/questions/225772/compare-two-mysql-databases

PS: Having this as a Aware IM standard functionality would be great but I don´t know if this is even possible but some kind or warning/rollback system could maybe be developed as a part of Aware IM?

Aha see the difference now comparing structure VS comparing data. The redgate tool looks useful though for this purpose.

weblike

Hi,

It happened again. Just erased an attribute from BO. The attribute was a simple "plain text".When I wanted to put the BSV on test mode it says the error:

Server encountered an exception Error dropping columns for table BASTESTDOMAINAPP_ORDERS_COMPANY Can't DROP 'CustomerEmail'; check that column/key exists

When I look in DB I see that all my records are gone. Fortunately this is only test...

weblike

I checked this problem and in my case happened like this:

I have 2 similar BSV's. In 1st BSV I have added/changed some attributes in a BO. I have added some new attributes (plain text) and changed a few (name, type).

To win time I have deleted the BO from 2nd BSV and copied the updated BO from 1st BSV.

Now I see that the fields in DB are not updated when I want to put the BSV in test mode, the old columns still appears...
It not concerns me the data loss as much that DB columns are not updated.
AW it shouldn't update the columns in DB when I put the bsv in test mode ?? Is this a bug??

weblike

After a AW server restart the columns were updated in DB, but the data loss is concerning me...
So, my advice is to backup as frequently as possible.

kklosson

This is a serious issue and I'm sure Support will take it seriously, but we've got to give them something to work with. Support will need some hard evidence of the issue and some assurance that it's not caused by the user. Clearly, Support has not experienced this nor can they duplicate it. My plan is this... I intend to make BO changes very meticulously with Navicat engaged to log changes. Whenever I change a BO (like adding an attribute, which has been the trigger in the past) I will review the database and the log. If I witness the dropping of an attribute during the build, I will give my evidence to Support with all my steps and configurations. This will be very difficult for Support to resolve as I'm sure it's not just some rookie mistake they made. But the first steps is to give Support enough evidence that it is a real problem.

One thing Support may want to consider... I think it would be very useful to have a mechanism to display a list of "Proposed" changes that the database will undergo prior to going into test, or when publishing a build. This could be an option that the developer could turn off when doing initial development when radical database changes are expected. I've said before that AwareIM's ability to abstract the database from the developer is very powerful. But we could use a bit more visibility into what is happening. If nothing else, I would just want visibility to any database change that will delete data, like dropping a column or table.

tford

If nothing else, I would just want visibility to any database change that will delete data, like dropping a column or table.

It would be very helpful to see a list of BOs & attributes that will be deleted when an particular BSV is updated.

A few times I have updated a BSV to the wrong business space in my haste to publish a fix. Thankfully I always have backups before publishing a new version, but a overview listing the BOs that will be deleted would have been another safety net to save me from my mistake.

hpl123

tford wrote
If nothing else, I would just want visibility to any database change that will delete data, like dropping a column or table.

It would be very helpful to see a list of BOs & attributes that will be deleted when an particular BSV is updated.

A few times I have updated a BSV to the wrong business space in my haste to publish a fix. Thankfully I always have backups before publishing a new version, but a overview listing the BOs that will be deleted would have been another safety net to save me from my mistake.

I agree, that would be valuable and even better would be to have that (Toms post) and also a history/event log i.e changes made to DB's in a list/archive (all previous changes).
Thanks

aware_support

We are not aware of any problems in Aware IM AS LONG AS THE BSV is CONSISTENT with the database structure.

You have to understand the process that Aware IM goes through when it publishes a BSV. This is what happens:

There is always an old version of the BSV which is "CURRENT" at the moment. The important thing is the database structure currently corresponds to this version of the BSV.
You are now publishing a new version of the BSV and Aware IM is supposed to make the corresponding changes to the database structure to reflect the changes. This is quite a complicated process.
Aware IM goes through every attribute of every object to check whether the attribute has been removed, added, renamed or modified. When doing this it compares the structure of the old BSV (current in the database) and the new BSV. Then it makes the corresponding changes to the database.
Aware IM does not rely on attributes names when determining what happened to the attribute (because names can be changed by the user). Instead every attribute is assigned a unique id and Aware IM uses these id's to determine what happened to the attribute.
So for a particular id in the old version Aware IM looks for the same id in the new one. If it doesn't find it thinks that the attribute has been removed and it tries to delete the corresponding column in the database. However, if something is inconsistent it may delete the column unnecessarily.

One example: you delete an attribute and save the object. An attribute with this id is now gone. You create an attribute again WITH THE SAME NAME. However, it has a DIFFERENT internal id now. You think that it's the same attribute, though, and don't expect Aware IM to make any changes. But it does and you have problems.

This is especially important if you are working with a BSV on a different machine compared to where your old BSV version is (the one that is published now). Normally Aware IM would try to compare the old and the new version and warn you even before the new version is published. But if the old version is different (which it most likely is, if you are working on a different machine) then you won't get any meaningful warnings.

Therefore, you need to be careful when you are making changes to the database structure (adding, deleting and renaming attributes) and there is a current version in the database that you want to remain compatible with. ESPECIALLY if you are making changes to the BSV on a different machine compare to where the current version is.

That's why you always need to backup and that's why you need to be careful.

If you are in the middle of the development and you don't care about your current data, then you can always put the version under testing with no initial data or create a new business space and start from blank data.

If you encounter a problem like this when publishing you should ALWAYS START by restoring from a backup as you can no longer trust the current database structure. After you restore then look at the column that caused the problem, go to the original BSV and see what happened to that column in the new one.

kklosson

Thanks Support. Your response is appreciated. You emphasize the difference when developing on one machine and publishing to another. Assuming that one does this and is careful when modifying objects, attributes, and relationships, why must one be more careful or attentive in this scenario? It seems that the same rules apply either way.

In my case, I am quite positive I did nothing to the BSV that would inform the database to drop a column, yet it did.

Q1: Do you recommend developing and publishing on the same machine as a best practice or to avoid these potential problems?