Download it now!
Bug 137735 - FILESAVE: Chart defects when reopening : loss of backwards compatibility
Summary: FILESAVE: Chart defects when reopening : loss of backwards compatibility
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: medium normal
Assignee: Not Assigned
Keywords: bibisectRequest, regression
Depends on:
Reported: 2020-10-25 14:42 UTC by VLB
Modified: 2020-11-20 17:03 UTC (History)
15 users (show)

See Also:
Crash report or crash signature:

Screenshot; Comparing unzipped odf content.xml between Calc 5 and Calc 4 (144.81 KB, image/png)
2020-10-26 12:22 UTC, matthewnote
Report of LO versions tests (including 32bit with xml format error messages) (97.43 KB, application/pdf)
2020-11-20 17:03 UTC, matthewnote

Note You need to log in before you can comment on or make changes to this bug.
Description VLB 2020-10-25 14:42:49 UTC
See also issue 86321 comment 80, where 2 issues are present and the bug is split.
Comment 1 VLB 2020-10-25 15:08:05 UTC
When the user saves the file, Calc 4,6,7 save a faulty "register".  On re-opening the file any or many charts fail to update (not always true and not always the same charts).

Saving with all Calc versions 5.*.*.* does something more thorough before writing.  The register is "revised" or "cleaned up" in some way before writing.  Some charts visible to the user are still frozen yet closing/re-opening the file finds the new content.xml and object/content.xml compatible, correct and all charts working.  With further Calc 5 user activity, the first Bug will, again, cause new freezes of charts.

Saving with format .xlsx causes a similar "revise" or "clean up" as Calc 5 with an additional final "redraw" of objects that had changes during the user session.  Therefore, unlike Calc 5, the user sees the presentations jump to the correct data values.

First and second Bug together.  If the user opens two or three .ods at the same time and does edits, the "register"  (apparently, it seems) becomes corrupted across any or many sheets on any of the open files.  Then it's necessary to File_Save with Calc 5 (then reopen each) or File_Save as .xlsx for all open files.
Comment 2 matthewnote 2020-10-26 12:22:00 UTC
Created attachment 166732 [details]
Screenshot; Comparing unzipped odf content.xml between Calc 5 and Calc 4

This (user) comment attempts to provide information about :
Which versions of Calc can restore a file with Chart Update defects using File>open File>save (close file) File>open.
Which versions tested did not do this.

All versions of Calc 4,5,6,7,Fresh can cause Objects (charts) to fail to update the scatter plots when their Data Series have changed.  To do this deliberately, the Step by Step is at Bug 86321 Comment 78.  A file with several sheets and three or four x,y scatter Charts on each sheet is given a new inserted sheet (empty) between others.  Then (mostly) charts elsewhere fail to update or change their x axes incorrectly (using any version of Calc 4,5,6,7).  This provides a test file for this Bug Report.  

The fault is often (but not always) permanent when using Calc 4,6,7,Fresh.  
With Calc 5, the defect is the same, yet most versions 5 have a save or open (or both) method/organisation that displays all charts working again.  Only Calc 5 can do this every time.

Ubuntu parallel Calc Versions reactions to File, Open - Save - Close - Open  The test file opens with charts frozen. Save, close then re-opens with charts  still frozen.  Repair/restore failed.  The test file opens with charts all working.  Save, close then re-opens with charts frozen again.  Repair/restore half successful (the Open works but not the Save).  [Note:Usually, with all Calc versions, moving the Chart area a few pixels will cause a scatter plot to update to the real series' values. did not do this workaround when tested here.]  The test file opens with charts all working. To test more, the file is deliberately broken using Insert>Sheet between two sheets that have charts.  Bug 86321 expressed again, various charts frozen or with incorrect x-axes format.  Save then re-opens the file with all charts working.  Earliest version Repair/restore successful.  Same as  Repair/restore successful.  While working, editing or deliberately causing frozen charts, is like;  moving the Chart area a few pixels did not do a workaround.  However Save re-opens same as  Repair/restore successful.,,, 5.3.6, 5.4.7 Repair/restore successful. 

All 6.0.0+, all versions up to and Fresh.  Test file usually opens defective.  More insertions of empty sheets usually causes more charts frozen everywhere.  File Save Close Re-open usually does not repair the frozen charts.  

Exception A.  Sometimes a file generated on (for example Linux+ with defective redraw of Charts may Open with charts working on a different computer (for example Windows+  Sent back to the source computer, the file may also have charts working before Bug 86321 expresses again.

Exception B. Upgrading from Calc (official Ubuntu 18.04 LTS companion) to Calc (using ppa outside the Ubuntu support): Calc did something unique and unusual.  Unlike all versions Calc 4,6 the upgrade to 7 once (and only once) generated ObjectReplacements for each every chart. Usually only Calc 5 always does this on Linux.  [The broken file was else than used here for these Bug reports and is too large to attach]. File>Open showed all Charts working immediately and they all still do for that file. Downgrading from Calc 7 and reinstalling official then doing the upgrade process again to LO7 did not repair any other defective file.

Coincidental information:  this may not be the indicator of the File>Open and File>Save File>close File>Open success of Calc 5 versions.  It's a coincidence that Calc 4, Calc 6.0.0 and Calc 7 do not share this save behaviour.

Description of the attachment using QXmlEdit. 

The screenshot (right hand side in red) shows that the Calc 4 used to save a defective charts file, wrote content.xml using draw:object
<table:table-row><table:table-cell><draw:frame><draw:object> Attributes:
draw:notify-on-update-of-ranges=roX09 . . . . . .

Calc 5 up to 5.4.7 changes the strategy.   The screenshot (left hand side in green) shows that Calc 5 has opened the version 4 file, reads that Object 17, updates the Chart correctly when data series cell values change.  Saving the file, Calc 5 deletes the original Attribute (gone), then adds a completely new element in content.xml one level up at draw:frame
<table:table-row><table:table-cell><draw:frame><loext:p> Attributes:
draw:notify-on-update-of-ranges=roX09 . . . . . .
Calc 5 also adds another completely new attribute into Object 17/content.xml

Calc 6 and 7.  These versions remove the Calc 5 strategy (the entries are deleted ) and saves with the same xml organisation as for version 4.   This is deliberate or a regression?

Please note a hope. Even if Bug 86321 is fixed and Calc has a new Open/Save method to fix this Bug, the ability to "refresh/reorganise/repair" a former file is the reason for this Report.  I, personally can generate files with and repair them daily with 5.4.7 because the Project doesn't use newer functions.  Not always possible when working with Calc 7 and statistics-double-exponential and so forth.

Test computer.  Ubuntu Bionic Beaver 18.04 using Parallel Versions of LibreOffice, remembering to start early versions 5 with 
SAL_USE_VCLPLUGIN=gen ./soffice
(I find here that the ./soffice is usually in opt/libreoffice5.0/program or 5.1)
Comment 3 matthewnote 2020-10-26 14:10:12 UTC
User Workaround (Windows 10) using LibreOffice 5 until bug fix.
User Workaround (Linux) uses AppImage or parallel installation.

Parallel LibreOffice version 5 on Windows computer for file repair (every day or week or only when it's got chart problems).
For Windows, "without touching the stable (your actual) installation"
suggests using a GUI Graphic User Interface (a window for selecting which version 5 you wish to try or use)
"The Fastest/Easiest Way: The Separate Install GUI is a powerful tool that will help you manage multiple installations of LibreOffice on Windows."
"The program (SI-GUI) can be downloaded from"

A separate icon or .exe will be created somewhere that will not disturb your actual permanent installation.  LibreOffice 5.4.7 is the latest and last version tested here that refreshes files.

If the file to be reviewed (using calc 5) is from Calc 6 or 7, User checks if version 5.4.7 has all the FUNCTIONs the file includes.  For example, very new Libreoffice calc versions may have a statistics averaging function like double exponential. A project with that particular new function may not be able to File>Save, File>Close, File>Open with Calc5 without losing the statistics cell contents.
Comment 4 matthewnote 2020-10-27 10:44:05 UTC
Additional information for debug and User workaround.  Excel format saves.

With a file already open (Calc 5,6,7,Fresh) a chart may become frozen.
The Calc 5 workaround is to Save, Close.  Only after Re-Open we see all the charts correctly, including on all sheets.  The "close" is necessary.

File, Save As, Excel format xlsx behaves differently.  It prepares the "save" format of a broken-chart situation (cache) and (for some reason) that causes a redraw.  The charts will jump to the correct series' values visually during the write process.  Therefore it confirms to the user what is being saved, which helps a lot on a busy project.  The "close, re-Open" is unnecessary.

Furthermore, frozen charts workaround saving in Excel format is possible in any version of Calc 6,7 (not limited to Calc 5).  Users are best choosing latest calc version for xlsx, to include the maximum compatibility in the details.  For example "conditional formats" have received attention and corrections to the translation.
Comment 5 Lars Jødal 2020-10-30 07:14:00 UTC
Reproduction of the bug (copied from bug 86321 comment #28):

1. Open attachment 114246 [details]
2. Go to sheet "roX0_9"
3. Go to sheet "invoer"
4. Delete H19:L19
5. Go back to sheet "rox0_9"

The diagram at A52:57 has not updated, but shows the updated values when edited.

The bug showed up in LO (not present in LO
Comment 6 matthewnote 2020-11-20 17:03:02 UTC
Created attachment 167424 [details]
Report of LO versions tests (including 32bit with xml format error messages)

One medium size file of the project here was being edited with additional histograms. The file is fully working on Calc  It was then tested with many different versions of Calc/OS.(pdf attachment).

Only that file has produced error messages about content.xml format (using Calc 32 bit application).  I hope to spend time generating a smaller file with a method to break more charts . . . . this report is offered if xml format might be the problem. 

The pdf has links to the actual Cancer project file used, it's content.xml (zipped, becomes 1.1 Gbyte when unzipped) for anyone to download (Google Drive no password required). This pdf also replaces the first draft.

[If someone intends to reproduce the problem.  The sheets have X,Y scatter plots and histograms.  Choose the data for each axis using the orange background control panel at the upper left of the sheet and there's a remote control to multiply or add factors to that data. On an i7 processor at 4.2 GHZ, Calc needs five or six seconds to display a change.

A quicker check for frozen graphics is to click "Nation labels" to OFF(0) or back ON(1) again.  If the cell value changes immediately, the redraw will not be done.  Patience. Even on an i7 processor at 4.2 GHZ, Calc needs five or six seconds to display a successful redraw.  Criteria for the Bug:  PLOTFILTER the graph is usually frozen when the file is opened.  Same thing PLOTXY1 and XY2.  On PLOTforSCAN sheet, the x,y plotter on the left is working, yet the two scatter diagrams on the right are the ones that freeze].