Bug 120384

Summary:	EDITING EDIT SHEET Mouse error or user slip onto sheet tab causes complete empty of that idle sheet contents and breaks auto-update of all Charts on the active sheet.
Product:	LibreOffice	Reporter:	matthewnote
Component:	Calc	Assignee:	Not Assigned <libreoffice-bugs>
Status:	RESOLVED DUPLICATE
Severity:	normal	CC:	ilmari.lauhakangas, matthewnote
Priority:	medium
Version:	6.0.6.2 release
Hardware:	x86-64 (AMD64)
OS:	Linux (All)
Whiteboard:
Crash report or crash signature:		Regression By:
Attachments:	Sample of large spreadsheet with broken (and working) Charts 1. Spreadsheet open on two UHD monitors 2. User prepares to scroll horizontally 3. User clicks sheet tab instead of scroll bar 4. Small movement left or right adds markers to sheet tab 5. Release mouse left button, All cell data/formulae are wiped 6. The sheet empties; Undo is possible. Please see comment

Description matthewnote 2018-10-07 16:19:14 UTC

Description:
The Charts affected are no longer able to be modified and checked unless the user moves the graphic around the sheet. Although that has been mentionned for several years, this bug reports two reproduceable steps to cause it.

Steps to Reproduce:
1.Opened on a sheet, when using the bottom scroll bar to slide the view from left to right, if the user misses the scrollbar by 1mm and instead click/slides on a sheet tab underneath a very small amount, a page icon appears, the sheet accidentally clicked is abruptly emptied (all contents of all cells are deleted!).
2.Continuing work on the active sheet - all the charts on that sheet are found to be broken(see below).
3.Another way to break a Chart is to add a new fresh x,y scatter Chart (which will update), save the spreadsheet, restart Calc in Safe Mode (to attempt to restore the previous broken Charts), choose each successive option unsuccessfully, then finally "factory" defaults all (choice furthest down the safe mode list) then open the spreadsheet with dozens of broken Charts and one that was working - and now it's broken too.

Actual Results:
All contents of the idle sheet that was slid by accidental touching of it's tab are gone - emptied, deleted.
All Charts of the active sheet being worked on are broken and unable to update in any automated way.
All Charts used for diagnostic purposes (what happens on the Chart when data is modified) have to be reconstructed from scratch.
Stirring each graphic will provoke a modification to show yet the spreadsheet truly is unusable and impossible to pass on to a colleague. It has to be rebuilt.

A small black arrow and bold line sometimes appears on the tab affected (now emptied). The user may not notice because the sheet actually being worked on is at an empty area of cells too, that's why it was being slid using cursor. If the user notices because the charts are being worked on - it's nearly impossible to realise the deleted contents of sheets to the left were the cause (to know to go back) because they're closed. If the document is saved, none of the charts work at the next opening. Saving at intervals is always a good idea, yet the user doesn't know that weeks or months of work have been added after the broken Charts. Broken means that any change to data, style, Area, border will only cause a change of the plot if the graphic Area is moved around on the sheet. Although one commentator mentioned on a different Bug that this is professionally acceptable - I admit that doing 400 graphics in Cancer Research (workstation with two UltraHD monitors and a third possible) is nearly killing me first. There are other causes for breaking Charts, but I've read everything on the internet I can find (and all Bugs here) and not yet, not once been able to repair a sheet or a single graphic. Every safe mode was/is attempted, setting tools/options to always recalculate on loading (seemed to work, but not after this bug).

Expected Results:
Automatic update of Charts is, perhaps (for many users) what a spreadsheet is best at. The graphical possibilities are fabulous and the ease of inserting functions in the columns makes it an analytical tool (else than for doing powerpoint presentations).

Reproducible: Always

User Profile Reset: Yes

Additional Info:
LO 6.0.6.2 build 1:6.0.6-0ubuntu0.18.04.1 (Bionic Beaver) on a 64bit KabyLake Shuttle PC and two giant UltraHD DP monitors.

Versions 6.04 and 6.0.6 and 6.10 were all used with Light Ubuntu this year and there were regular broken Charts (nearly every day) without being able to find the causes. In the end, the reload and repair work brought the Cancer research to a standstill - so I can't add much evidence about that history (four years).

Calc 6.0.6.2 is now being used here three weeks with Bionic Beaver and it is marvellous and stable. It's evident now just how much suffering was involved before (With LightUbuntu KDE and 6.10 there were ten stoppages per day with or without OpenGL and the stress level as user was extremely high). Ended - over!

Calc 6.0.6.2 is nearly rock steady (keeps running), perhaps because the Gnome environment really is in favour of Users.

Comment 1 matthewnote 2018-10-07 16:52:54 UTC

Created attachment 145450 [details]
Sample of large spreadsheet with broken (and working) Charts

The three sheets are part of a (very much larger) work. All data is in rows 1 to 248, calculation factors are in rows 250 to 280 (cells highlighted in yellow for the user, who also has a user guide). A grey row is at row 249 to visually separate the work area for the user.   Sheet "SWITCH" has three Charts of identical ranges and series placed below row 280 in the empty space of columns AI to BS.   

The Chart to the left will update when any pertinent factor is changed - example is cell BS273 at value 2.2 originally;  changing it to 4.4 moves one series of the chart data automatically.  The Chart in the middle was working then broken after saving/reopening once in safe mode without any other manipulation.  The Chart to the right was broken some weeks ago (without knowing how, yet probably the same cause - sliding a sheet tab accidentally instead of the cursor empties the contents of closed sheets to the left;  they may or may not be with data used for the graphic;  even if not, the Chart is broken).

Comment 2 matthewnote 2018-10-07 17:33:42 UTC

With the sample file attached I thought it worth trying out one idea.
The .ods file was exported to Excel .xlsx format using 'Save as'.
Then closed Calc, then started Calc, then open the .xlsx, then save as 'ODF'.
The Charts update when data BS273 is modified.
Unbelievable relief.
I'll be checking if this workaround keeps all the colours and graphics of all sheets.  Comment to follow about the workaround. . . . .

Comment 3 matthewnote 2018-10-07 17:42:28 UTC

The Workaround. This is the first time an export to Excel format has been done here with this research.   Although repairing Charts by exporting a large spreadsheet to .xlsx then re-opening to ODF (.ods) seems a risk - for this spreadsheet all but two Charts were kept intact by the procedure.  On only two, the colours were kept but the symbols chosen for x,y scatter plots were reverted to squares instead of the original asterisk stars. Very good (from this point of view).  I can repair them in a couple of minutes rather than the four weeks work necessary to re-create the auto-updating Charts.

Comment 4 matthewnote 2018-10-08 12:59:59 UTC

Another way to break an x,y scatter plot Chart.

(prevents updating when series data is modified).

1.On a Bionic Beaver+LO 6.0.6.2 spreadsheet, several scatter plot Charts are inserted on one sheet using columns for the series.
2.The toolbar for drawing is enabled.
3.Two ordinary lines are added on one Chart (on the data plots area). A typical procedure is then to need one line parallel; one of the two lines is selected for copy, then pasted onto the same plots area of the same Chart.
4.Clicking off the plots grid, off the Chart Zone area, elsewhere on the sheet to continue with other work - the Chart is broken (updating of parent cells fails to move the plots).
5.The other Charts on the same sheet still autoupdate.
6.Workaround (with difficulty). The damaged Chart needs to be reconstructed. If much work has been done and there are dozens of Charts discovered later to be broken, the whole spreadsheet is "saved as" format .xlsx.
7.Working on the .xlsx file - all the Charts function (autoupdates).
8.If the user tries to save that working .xlsx file by "save as" new.ods under a new name, when opening the new.ods all data plot symbols on all Charts are changed to coloured diamonds - except for the broken Chart (which has it's original plot shapes and colours). The Chart (that worked in .xlxs) is still broken in the new .ods file.
9.Restarting Calc in safe mode enables all use of the Charts, yet in normal mode the Chart in question is still broken.
10.All restart in safe mode proposed changes are attempted; the one that makes the new.ods file fully functional is "Reset to factory settings - Reset settings and user interface modifications".
11.The user can then restart in normal mode, prepare their profile (measure in cm, autospellcheck off etcetera) and edit the whole new.ods spreadsheet to correct the symbols and colours used in scatter plots.

Comment 5 matthewnote 2018-10-14 05:56:50 UTC

Created attachment 145693 [details]
1.  Spreadsheet open on two UHD monitors

Comment 6 matthewnote 2018-10-14 05:57:59 UTC

Created attachment 145694 [details]
2.  User prepares to scroll horizontally

Comment 7 matthewnote 2018-10-14 06:00:08 UTC

Created attachment 145695 [details]
3.  User clicks sheet tab instead of scroll bar

Comment 8 matthewnote 2018-10-14 06:01:36 UTC

Created attachment 145696 [details]
4.  Small movement left or right adds markers to sheet tab

Comment 9 matthewnote 2018-10-14 06:03:40 UTC

Created attachment 145697 [details]
5.  Release mouse left button, All cell data/formulae are wiped

Comment 10 matthewnote 2018-10-14 06:18:42 UTC

Created attachment 145698 [details]
6.  The sheet empties; Undo is possible.  Please see comment

The problem is. . . .

When completing/creating/working on a new sheet (that is empty), this "click/drag/don't move/wipe content" of a sheet on the left presents an empty screen also (which seems "normal").  The user is expecting an empty sheet so usually continues to pick up the scroll bar to do the manipulation intended (without noticing the error).  Once scrolled to the right, the user may notice it's not the sheet name/tab he/she was working on before;  user clicks again correctly on the newest sheet, but doesn't receive any warning nor notification that the earlier sheet is now empty.  Some days or weeks later the user discovers that many early sheets are now deleted.  The sheet tabs are there but the data is gone.  

6.  Expected result.  "Warning:  All Sheet #calcpercap# contents will be deleted.  Are you sure?".

Comment 11 Buovjaga 2018-11-25 17:36:24 UTC

Fixed in 6.0.7

*** This bug has been marked as a duplicate of bug 118302 ***