MySQL Bugs: #72097: errors in edit

Bug #72097	errors in edit
Submitted:	21 Mar 2014 20:35	Modified:	12 Sep 2014 17:52
Reporter:	Peter Rowlands	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL for Windows: MySQL for Excel	Severity:	S3 (Non-critical)
Version:		OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	Edit XLHub

Description:
I have been very encouraged by the new edit function in Excel
It is the first new feature of MySQL I have been excited about for a long time.
However there are three bugs I have found (1-3).
and I have three feature requests (4-6).

1)
Edit(Commit) and export should store two columns for each excel column containing formulas, one with the formula (varchar) and one with the value (of the value type - double, varchar etc)
Edit(Revert) and import should then combine these columns appropriately (ie inserting the formula not the value, where there was a formula)

At the moment all formulas will be lost on edit with a Commit - Revert cycle (or by export and import).

2)
On edit, columns to the right of the data should be editable.  (by all means warn the user - but still allow these area to be used.
The area below the data should be editable.
After ALTER TABLE as added columns (outside excel), when the data is Reverted, the area to the right should be shifted further right to make room for the new columns.

3)
Not all data errors are being caught at the same level.  
As a result there are different results if there is a data error in a cell.

The cell should turn orange, but contain the erroneous data the user entered.  Other cells should be unaffected.  the commit should not have entered the value of that cell to the db.  Reverting data should return the cell the the previous value stored in the db.

Instead, there is a bug that does not catch the error at the correct level.  As a result the db is changed, and contains invalid data.  When a Revert is performed, the cells after the erroneous cell appear blank in excel (although they are fine in the db)
If there is a data error in one cell, (for instance a commit fails) and that is followed

4)
Finally after three related bugs, let me make three related feature request.
Add a synced import.  The synced cells would be protected and uneditable in excel, but would update automatically if the db is updated by another process.

Allow a user to change an area from synced import (autoupdate from db to excel) to edit (update from excel to db, with manual revert from db to excel)
and visa versa

Allow views to be used in edit and synced import.  Primary key(s), and all "must not be null columns", must be included in view.  Updates from edit in excel should update all the original tables.  Only direct cell value should be editable, so some views (eg with formulas) should have protected (uneditable) cells.

Allow insert column to generate the appropriate ALTER TABLE SQL to add a new column. 

5)
On edit if the db table being viewed is altered by another user, the excel user should see the MYSQL Commit/Revert change color.
It should also give a third option (in this situation) to Merge your changes with those of the other user (Rather than Commit that might overwrite the other users changes or Revert that will lose your changes).

6)
Add versioning of commits like XLHub

ps
Is the Revert the best word?  Would "Retrieve" or "Update from db" be better?

How to repeat:
As above

Hi Peter,
Thanks for your bug report.
Please refer to the MySQL bug guidelines when submitting other bugs:
http://bugs.mysql.com/how-to-report.php

It is very important that only 1 problem is reported in each report.

You reported 6 numbered issues, and here is the result of each of them:

1) Not a bug, this will be treated as an enhancement request and will be reviewed to see if it is implemented. The new feature would avoid formulas to be lost and re-apply them after refreshing the data from the DB. The Edit Data feature was designed to revert back to the way a Edit Data session starts, which is without any user changes (user changes include formulas).

2) Not a bug, Edit Data sessions were designed to lock the worksheet where the session is opened and to only permit changes within the editing range. MySQL for Excel functionality is restricted to what Microsoft's VSTO allows. So the area to the right and below the editing range will stay locked. However I created an enhancement request (that will be reviewed to see if it is implemented) to allow the insertion and deletion of columns that would translate to ALTER TABLE statements.

3) This sounds like a bug, but no specific steps to reproduce were given. Please reply back with steps to duplicate this issue consistently. If steps to duplicate may not be enough and you need to provide a SQL script to recreate database tables and an Excel spreadsheet with sample data, you can attach them to this bug.

4) Filed 2 enhancement requests (that will be reviewed to see if they are implemented), one to allow MySQL Views to be editable, and another to add an auto-update feature for Excel tables created from imported MySQL data.}

5) Filed an enhancement request for this (that will be reviewed to see if they are implemented).

6) Filed an enhancement request for this (that will be reviewed to see if they are implemented).

We will treat this bug as the one for the issue #3 you reported.
Please get back to us with the steps to reproduce this problem.

Raised internal improvements for all of the points that are not bugs but enhancement requests.

The only point that seems like a bug may be related to bug:
Bug #18142293 - CELLS WOULD TURN ORANGE AT INAPPROPRIATE TIMES