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

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.

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.

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.

[quote="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.[/quote] 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?

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

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.

[quote="ACDC"]I would love to find a solution that can do a database compare.[/quote] 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.

[quote="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.[/quote] Great and please keep up informed.

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/

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?

Data loss when publishing BSV

RocketRod

Further to my suggestion I've had some more thoughts. I think that the file check should be available outside of the configuration tool. It can then prompt for a bsv file, which the developer can of course export. It should also prompt for the database name. In this way developers can check a bsv against a DB on a different machine AND it would assist developers who distribute runtime versions. Runtime version upgrades can be especially thwarft with danger if you have upgraded your AwareIM version several times and made quite a few changes to the BSV. In these instances the developer has no idea if they will encounter issues when distributing their new runtime or not.

Cheers Rod

kklosson

Another excellent contribution. I must say, I'm alarmed that several have come out with similar experiences, especially in the runtime environment.

A note to AwareSoft: AwareIM is a sound product and we in this thread are huge devotees. I believe this thread demonstrates a vulnerability which, despite the soundness of the product and the care taken by the developer, can leave one with a damaged database and no indication that the database was affected in an unforeseen manner. The observations and suggestions here are offered in hopes that you can provide developers with a bit more visibility into the database actions to be taken when a BSV is published or put into testing mode. We heard you say that the database update aspect of publishing a BSV is complex. No doubt it is. But we respectfully ask that you give this thread some consideration and contemplate what can be done to improve this aspect of AwareIM.

aware_support

Thank you for your suggestions, we'll consider them

hpl123

Support / all,
I have a question related to these problems. If I have a live BSV (v1), do some changes to the BS and publish a new BSV (v2). The structure of the database (v2) would now be different because I have changed the application. If I then restore a database backup (using NaviCat) from before the BS changes (i.e. restore database from v1 to database of v2), wouldn´t that mean the new changes I have made to the BSV (v2) would be lost because the restore of a backup restores structure and data from v1?.

I am a DB etc. newbie so any help, comments etc. are appreciated and this is a VERY IMPORTANT thing. Please enlighten me 🙂. Thanks

PS: Thanks support for considering creating better tools to counteract the problems discussed in this post.

aware_support

Yes, database restore will restore the system to v1 and the changes to v2 will be lost unless you also save v2 as a BSV file before you restore and import it again, then publish again.

hpl123

aware_support wrote
Yes, database restore will restore the system to v1 and the changes to v2 will be lost unless you also save v2 as a BSV file before you restore and import it again, then publish again.

Thanks for the info.

kklosson

There is some discussion earlier in this thread about using Navicat to compare database changes. Having explored this, I find it very useful and it gives me more confidence when building and deploying. I will outline the basic steps below, though most of you could likely figure it out on your own.

Create a new database. I called mine basdbtest_current.
Select all tables in the basdbtest database and drag them over the top of basdbtest_current. You will be prompted to copy structure, data, or both - your choice. Note that you have to close and reopen the basdbtest_current database to see the copied tables. You now have a baseline database for comparison.
Under Tools, select Structure Synchronization. Set basdbtest as the source and basdbtest_current as the target and press the Compare button. When finished, you get a good list of all database changes as you see below.

After deployment, you can repeat everything to create a new baseline for your next session. You can also simply run the queries in the compare window to alter the baseline table accordingly. For now, I intend to do this for basdbtest during development and basdb right after publishing a new BSV.

hpl123

kklosson wrote
There is some discussion earlier in this thread about using Navicat to compare database changes. Having explored this, I find it very useful and it gives me more confidence when building and deploying. I will outline the basic steps below, though most of you could likely figure it out on your own.

Create a new database. I called mine basdbtest_current.

Select all tables in the basdbtest database and drag them over the top of basdbtest_current. You will be prompted to copy structure, data, or both - your choice. Note that you have to close and reopen the basdbtest_current database to see the copied tables. You now have a baseline database for comparison.

Under Tools, select Structure Synchronization. Set basdbtest as the source and basdbtest_current as the target and press the Compare button. When finished, you get a good list of all database changes as you see below.

After deployment, you can repeat everything to create a new baseline for your next session. You can also simply run the queries in the compare window to alter the baseline table accordingly. For now, I intend to do this for basdbtest during development and basdb right after publishing a new BSV.

Nice 🙂

kklosson

Yes but now I am thinking that if AwareIM drops a column, then creates it, as it has done in the past, that this function won't perceive it.

hpl123

How about data loss/data "mismatch", can't NaviCat pick up at all on that when doing the syncronization?

hpl123

Found a free tool that actually compare both structure and data and this tool could maybe be used in the same way you did kklosson (i.e. compare after changes).

http://www.percona.com/software/percona-toolkit

Here is another link with a couple of other tools and some are commercial but there are possble other free tools here.
http://stackoverflow.com/questions/225772/compare-two-mysql-databases

PS: If anyone finds a working solution for compare etc please share your findings.

hpl123

hpl123 wrote
Found a free tool that actually compare both structure and data and this tool could maybe be used in the same way you did kklosson (i.e. compare after changes).

http://www.percona.com/software/percona-toolkit

Here is another link with a couple of other tools and some are commercial but there are possble other free tools here.
http://stackoverflow.com/questions/225772/compare-two-mysql-databases

PS: If anyone finds a working solution for compare etc please share your findings.

The following tool seems to be ideal for our purposes or at least in uncertain scenarios when doing changes on existing configurations (all tables i.e structure on both DB's has to match). It's a simple command line "action" and it compares master-slave DATA only (checksum check) and my thought is it's possible to A) copy DB before AIM changes , B) run this tool to compare data checksum and if it differs AT ALL restore BSV and DB backup and try again. Thoughts?
http://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html

« Previous Page