You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by Ar...@ldbv.bayern.de on 2016/08/01 11:48:45 UTC
POI xlsx indeterminism
Dear POI dev team,
we've been experiencing an indeterminism problem with POI's xlsx format, when generating hash values with the following method in testng test cases:
FileTest:
@Test(enabled = true) // indeterminism at random iterations, such as 400 or 1290
public void emptyXLSXTest() throws IOException, NoSuchAlgorithmException {
final Hasher hasher = new HasherImpl();
boolean differentSHA256Hash = false;
for (int i = 0; i < 10000; i++) {
final ByteArrayOutputStream excelAdHoc1 = BusinessPlanInMemory.getEmptyExcel("xlsx");
final ByteArrayOutputStream excelAdHoc2 = BusinessPlanInMemory.getEmptyExcel("xlsx");
byte[] expectedByteArray = excelAdHoc1.toByteArray();
String expectedSha256 = hasher.sha256(expectedByteArray);
byte[] actualByteArray = excelAdHoc2.toByteArray();
String actualSha256 = hasher.sha256(actualByteArray);
if (!expectedSha256.equals(actualSha256)) {
differentSHA256Hash = true;
System.out.println("ITERATION: " + i);
System.out.println("EXPECTED HASH: " + expectedSha256);
System.out.println("ACTUAL HASH: " + actualSha256);
break;
}
}
Assert.assertTrue(differentSHA256Hash, "Indeterminism did not occur");
}
Referenced Hasher and POI code:
HasherImpl:
public String sha256(final InputStream stream) throws IOException, NoSuchAlgorithmException {
final MessageDigest digest = MessageDigest.getInstance("SHA-256");
final byte[] bytesBuffer = new byte[300000];
int bytesRead = -1;
while ((bytesRead = stream.read(bytesBuffer)) != -1) {
digest.update(bytesBuffer, 0, bytesRead);
}
final byte[] hashedBytes = digest.digest();
return bytesToHex(hashedBytes);
}
We tried to eliminate indeterminism due to meta data like creation time, to no avail:
public static ByteArrayOutputStream getEmptyExcel(final String fileextension) throws IOException {
Workbook wb;
if (fileextension.equals("xls")) {
wb = new HSSFWorkbook();
}
else {
wb = new XSSFWorkbook();
final POIXMLProperties props = ((XSSFWorkbook) wb).getProperties();
final POIXMLProperties.CoreProperties coreProp = props.getCoreProperties();
coreProp.setCreated("");
coreProp.setIdentifier("1");
coreProp.setModified("");
}
wb.createSheet();
final ByteArrayOutputStream excelStream = new ByteArrayOutputStream();
wb.write(excelStream);
wb.close();
return excelStream;
}
Indeterminism occurs at random iterations, such as 400 or 1290, and we've not found out, why this would happen, yet.
Do you have any clue, what might be causing the problem, maybe some meta data flag we've not been addressing, internal compression or anything else?
HSSF / ".xls" instead seems not to have the same issues, btw.
Best regards,
Armin
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: POI xlsx indeterminism
Posted by Dominik Stadler <do...@gmx.at>.
One simple approach could use the text extraction functionality of POI and
only compare the extracted text of the two workbooks, maybe that already
provides the wanted "is equal" functionality?
Dominik
On Aug 1, 2016 6:35 PM, "Javen O'Neal" <on...@apache.org> wrote:
Within your !hash1.equals(hash2) code, you could save the byte streams to
disk, extract the zip files, and use a diff utility to figure out why
they're different.
We use a lot of hash maps, which have nondeterministic order. If those maps
are serialized out as <node attr1="value1" attr2="value2"/>, then attrs
could be swapped. Some values are saved as <node
attr1="key1=value1;key2=value2" />, which would also have those problems.
Not only do you need to diff your xml in a canonical form (order of XML
nodes and attributes, whitespace, self-closing vs paired closing tags,
etc), but any data in attributes (such as the serialized map above, ids
that link portions of an XML document or between multiple XML documents)
also needs to be considered. For example, do you consider two workbooks
that print identically where one work book's style table has an extra
unused style in its StyleTable to be different? How about to workbooks
where two cell styles are swapped, and all references to those styles are
updated. The answer might be no for some purposes and yes for others.
On Aug 1, 2016 06:10, "Nick Burch" <ap...@gagravarr.org> wrote:
> On Mon, 1 Aug 2016, Armin.Winterer@ldbv.bayern.de wrote:
>
>> we've been experiencing an indeterminism problem with POI's xlsx format,
>> when generating hash values with the following method in testng test
cases:
>>
>
> XLSX uses Zip files, which contain within them file dates. If you're
> comparing the outer zip file, you would expect an otherwise identical file
> to change every second as the datetimes move on
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
> For additional commands, e-mail: dev-help@poi.apache.org
>
>
Re: POI xlsx indeterminism
Posted by Javen O'Neal <on...@apache.org>.
Within your !hash1.equals(hash2) code, you could save the byte streams to
disk, extract the zip files, and use a diff utility to figure out why
they're different.
We use a lot of hash maps, which have nondeterministic order. If those maps
are serialized out as <node attr1="value1" attr2="value2"/>, then attrs
could be swapped. Some values are saved as <node
attr1="key1=value1;key2=value2" />, which would also have those problems.
Not only do you need to diff your xml in a canonical form (order of XML
nodes and attributes, whitespace, self-closing vs paired closing tags,
etc), but any data in attributes (such as the serialized map above, ids
that link portions of an XML document or between multiple XML documents)
also needs to be considered. For example, do you consider two workbooks
that print identically where one work book's style table has an extra
unused style in its StyleTable to be different? How about to workbooks
where two cell styles are swapped, and all references to those styles are
updated. The answer might be no for some purposes and yes for others.
On Aug 1, 2016 06:10, "Nick Burch" <ap...@gagravarr.org> wrote:
> On Mon, 1 Aug 2016, Armin.Winterer@ldbv.bayern.de wrote:
>
>> we've been experiencing an indeterminism problem with POI's xlsx format,
>> when generating hash values with the following method in testng test cases:
>>
>
> XLSX uses Zip files, which contain within them file dates. If you're
> comparing the outer zip file, you would expect an otherwise identical file
> to change every second as the datetimes move on
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
> For additional commands, e-mail: dev-help@poi.apache.org
>
>
Re: POI xlsx indeterminism
Posted by Nick Burch <ap...@gagravarr.org>.
On Mon, 1 Aug 2016, Armin.Winterer@ldbv.bayern.de wrote:
> we've been experiencing an indeterminism problem with POI's xlsx format,
> when generating hash values with the following method in testng test
> cases:
XLSX uses Zip files, which contain within them file dates. If you're
comparing the outer zip file, you would expect an otherwise identical file
to change every second as the datetimes move on
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: POI xlsx indeterminism
Posted by kiwiwings <ki...@apache.org>.
Hi Armin,
just some random thoughts on this:
- the zip format is not deterministic, so don't compare zip streams
(google for "zip stream deterministic", e.g. [1])
- xml is not deterministic, you would need to use a canonical form - look at
the xml signature mechanism
- we try to preserve the records with the native formats, so they are much
more likely to be deterministic
... but it's also not 100% sure ...
- I would sign the xml files and extract the hashes from the signature files
Andi
[1]
https://blog.pivotal.io/labs/labs/barriers-deterministic-reproducible-zip-files
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/POI-xlsx-indeterminism-tp5724269p5724270.html
Sent from the POI - Dev mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org