Download it now!
Bug 127093 - MariaDB/MySQL native connector: Import of Autovalue from HSQLDB sets '0' to '1'
Summary: MariaDB/MySQL native connector: Import of Autovalue from HSQLDB sets '0' to '1'
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.3.0.4 release
Hardware: x86-64 (AMD64) All
: high major
Assignee: Tamas Bunth
URL:
Whiteboard: target:6.4.0 target:6.3.2
Keywords: bibisectRequest, dataLoss, regression
Depends on:
Blocks:
 
Reported: 2019-08-22 06:53 UTC by Robert Großkopf
Modified: 2019-09-04 10:20 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2019-08-22 06:53:12 UTC
Download https://bugs.documentfoundation.org/attachment.cgi?id=153565
Try to import the table from HSQLDB to MariaDB/MySQL.
Produces an empty table - see bug 127092.
No try it again and paste only the content to the created table.
An error occurs:
"Duplicate entry '1' for key 'Primary'"
Ignore this error and have a look at the new table.
ID = '0' isn't there.
ID = '1' shows the content of ID = '0' of the HSQLDB. The content of ID = '1' of the HSQLDB is lost.

This works in LO 6.2.6.2, fails in LO 6.3.0.4 and LO Version: 6.4.0.0.alpha0+ Time: 2019-08-09_06:28:42.

All tested with OpenSUSE 15 64bit rpm Linux.
Comment 1 Alex Thurgood 2019-08-22 07:49:06 UTC
Confirming with

Version: 6.3.0.4
Build ID: 057fc023c990d676a43019934386b85b21a9ee99
Threads CPU : 8; OS : Mac OS X 10.14.6; UI Render : par défaut; VCL: osx; 
Locale : fr-FR (fr_FR.UTF-8); Langue IHM : fr-FR
Calc: threaded
Comment 2 Tamas Bunth 2019-08-31 17:16:07 UTC
> This works in LO 6.2.6.2

So with LO 6.2.6.2 the result is the same table?

I am asking, because it seems to me that the HSQLDB IDENTITY column has the initial value 0, and MySQL's AUTO_INCREMENT defaults to 1.

I looked at the implementation of the table copy. The different auto increment behaviors are not yet implemented, or at least I did not find it. Sadly, the capabilities, keywords and initial values of the auto increment varies between DBMS's.

I guess the correct behavior would be to paste the very same values (that means starting from 0). We also have to set the initial value to the same. The problem is that the query of that meta-information is not standardized. There might be even a step value defined - e.g. Firebird 3 is capable of that.

Anyway, I will have a look at what is happening with LO 6.2.6.2.
Comment 3 Tamas Bunth 2019-08-31 17:42:44 UTC
Also, the sdbc driver is not capable of providing information about the initial value of the auto incremented column.

The ideal solution might be to update the XResultSetMetadata interface to provide it, but this interface is so ancient that I don't dare touching it.

The sdbcx::Column service would be suitable as well.
Comment 4 Commit Notification 2019-08-31 17:44:43 UTC
Tamas Bunth committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/fa177231cd20bf3c3f4bb9b50f6646da139c6766%5E%21

tdf#127093, tdf#127092 Fix pasting autoincremented

It will be available in 6.4.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 5 Robert Großkopf 2019-09-01 06:42:47 UTC
(In reply to Tamas Bunth from comment #2)
> > This works in LO 6.2.6.2
> 
> So with LO 6.2.6.2 the result is the same table?
> 
> I am asking, because it seems to me that the HSQLDB IDENTITY column has the
> initial value 0, and MySQL's AUTO_INCREMENT defaults to 1.

Yes, the table will have the ID=0 and the whole data. The allowed values for ID will be defined by the datataype. And the datataype is INTEGER. So it could have negative values in signed mode (default) or will begin with '0' in unsigned mode.
Comment 6 Commit Notification 2019-09-02 13:20:43 UTC
Tamas Bunth committed a patch related to this issue.
It has been pushed to "libreoffice-6-3":

https://git.libreoffice.org/core/+/2ddd1a5ca306ed6bfdcd375af094cdbd9e6212ca%5E%21

tdf#127093, tdf#127092 Fix pasting autoincremented

It will be available in 6.3.2.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 7 Commit Notification 2019-09-03 18:00:03 UTC
Tamas Bunth committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/d783017c1ccb4e62e99f26b42250ac4e15780cff%5E%21

Revert "tdf#127093, tdf#127092 Fix pasting autoincremented"

It will be available in 6.4.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 8 Commit Notification 2019-09-03 18:00:26 UTC
Tamas Bunth committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/91a97c5e0b7b760ec0b46ad38b28ec1419c7d48f%5E%21

tdf#127093 mysqlc: set NO_AUTO_VALUE_ON_ZERO

It will be available in 6.4.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 9 Commit Notification 2019-09-04 09:56:33 UTC
Tamas Bunth committed a patch related to this issue.
It has been pushed to "libreoffice-6-3":

https://git.libreoffice.org/core/+/8a9ce8164aedda16e6732845adfdae03a7c810de%5E%21

Revert "tdf#127093, tdf#127092 Fix pasting autoincremented"

It will be available in 6.3.2.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 10 Commit Notification 2019-09-04 10:20:01 UTC
Tamas Bunth committed a patch related to this issue.
It has been pushed to "libreoffice-6-3":

https://git.libreoffice.org/core/+/8483f48aa90d9c487320af855e333c1e6c9453c2%5E%21

tdf#127093 mysqlc: set NO_AUTO_VALUE_ON_ZERO

It will be available in 6.3.2.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.