Bug 123020 - Migration to Firebird db fails on "unknown token isc_dsql_prepare"
Summary: Migration to Firebird db fails on "unknown token isc_dsql_prepare"
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.1.4.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Firebird-Migration
  Show dependency treegraph
 
Reported: 2019-01-28 17:26 UTC by Thomas Nilefalk
Modified: 2019-04-20 00:59 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Error dialog on failed migration (20.45 KB, image/png)
2019-01-28 17:27 UTC, Thomas Nilefalk
Details
The database (147.58 KB, application/vnd.oasis.opendocument.database)
2019-01-28 18:02 UTC, Thomas Nilefalk
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Thomas Nilefalk 2019-01-28 17:26:46 UTC
Description:
Opening a Base project with HSQL storage I attempted a conversion and was met with:

firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Token unknown - line 1, column 23
*ID
caused by
'isc_dsql_prepare'

I have no idea what this means. As always the "More" info is completely lacking.

Steps to Reproduce:
1.Open the database
2.Answer 'yes' to the question about migration
3.

Actual Results:
Error message as in the attachment, and a non-working database (and slow as molassess...)

Expected Results:
A working, migrated database


Reproducible: Always


User Profile Reset: No



Additional Info:
Comment 1 Thomas Nilefalk 2019-01-28 17:27:44 UTC
Created attachment 148715 [details]
Error dialog on failed migration
Comment 2 Julien Nabet 2019-01-28 17:37:40 UTC
Would it be possible you attach the database?
Of course, make a copy in order to remove any private/confidential part from  it.
Comment 3 Thomas Nilefalk 2019-01-28 18:02:58 UTC
Created attachment 148716 [details]
The database
Comment 4 Julien Nabet 2019-01-29 09:08:20 UTC
Thank you Thomas for your feedback.
I could reproduce this on pc Debian x86-64 with master sources updated yesterday.
Comment 5 Thomas Nilefalk 2019-01-29 10:23:20 UTC
Good. Thanks. Looking forward to any progress. I suppose it should be fairly high prio to get every bug out of the Firebird migration, hugh?

Good that it's not blocking me. Not before HSQL is pulled, that is...
Comment 6 Julien Nabet 2019-01-30 08:29:56 UTC
I retested this and had another result.
First, when I downloaded this, I got this name on my laptop (Debian x86-64 with French UI):
'Poängdatabasen - kopia (2).odb'

Here are the actions I done:
- rename the file to avoid special characters
- open the file and disable macros
- accept migration when trying to open tables
=> errors
Here are console logs:
warn:connectivity.firebird:11528:11528:connectivity/source/drivers/firebird/Util.cxx:55: firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Name longer than database column size
caused by
'isc_dsql_prepare'

warn:connectivity.firebird:11528:11528:connectivity/source/drivers/firebird/Statement.cxx:122: isc_dsql_execute failed
warn:connectivity.firebird:11528:11528:connectivity/source/drivers/firebird/Util.cxx:55: firebird_sdbc error:
*unsuccessful metadata update
*CREATE VIEW Transaktionssumma failed
*Dynamic SQL Error
*SQL error code = -206
*Column unknown
*Ins\u00e4ttning
caused by
'CREATE VIEW "Transaktionssumma" ("Transaktionsnr","Kontonr","Datum","Beskrivning","Ins\u00e4ttning","Uttag","Ej utnyttjat","Tillgodo") AS SELECT "Transaktionsnr" AS "Transaktionsnr", "Kontonr" AS "Kontonr", "Datum" AS "Datum", "Beskrivning" AS "Beskrivning", "Ins\u00e4ttning", "Uttag", "Ej utnyttjat", "Ins\u00e4ttning" - "Uttag" - "Ej utnyttjat" AS "Tillgodo" FROM "Transaktioner"'

warn:connectivity.firebird:11528:11528:connectivity/source/drivers/firebird/Statement.cxx:122: isc_dsql_execute failed
warn:connectivity.firebird:11528:11528:connectivity/source/drivers/firebird/Util.cxx:55: firebird_sdbc error:
*unsuccessful metadata update
*CREATE VIEW \u00c5rstransaktioner failed
*Dynamic SQL Error
*SQL error code = -206
*Column unknown
*Ins\u00e4ttning
caused by
'CREATE VIEW "\u00c5rstransaktioner" ("Kontonr","\u00c5r","Ins\u00e4ttningar","Uttag") AS SELECT "Kontonr", CAST( LEFT( "Datum", 4 ) AS INTEGER ) AS "\u00c5r", SUM( "Ins\u00e4ttning" ) AS "Ins\u00e4ttningar", SUM( "Uttag" ) AS "Uttag" FROM "Transaktioner" GROUP BY "Kontonr", LEFT( "Datum", 4 ) ORDER BY "Kontonr" ASC, "\u00c5r" ASC'

warn:connectivity.firebird:11528:11528:connectivity/source/drivers/firebird/Statement.cxx:122: isc_dsql_execute failed
warn:connectivity.firebird:11528:11528:connectivity/source/drivers/firebird/Util.cxx:55: firebird_sdbc error:
*unsuccessful metadata update
*CREATE VIEW Konto och transaktioner failed
*Dynamic SQL Error
*SQL error code = -206
*Column unknown
*Transaktioner.Ins\u00e4ttning
caused by
'CREATE VIEW "Konto och transaktioner" ("Kontonamn","Datum","Beskrivning","Ins\u00e4ttning","Uttag") AS SELECT "Konton"."Kontonamn", "Transaktioner"."Datum", "Transaktioner"."Beskrivning", "Transaktioner"."Ins\u00e4ttning", "Uttag" + "Ej utnyttjat" AS "Uttag" FROM "Transaktioner", "Konton" WHERE "Transaktioner"."Kontonr" = "Konton"."Kontonr"'

warn:dbaccess:11528:11528:dbaccess/source/filter/hsqldb/hsqlimport.cxx:384: Error during migration

Tamás: thought you might be interested in this one. It seems there are still part which need to take into account encoding.
Comment 7 Julien Nabet 2019-01-30 08:47:26 UTC
Unzipping the odb file, I noticed that in content.xml, I got:
"Vald person i formulär Konton"
but in database/script, I got "Vald person i formul\u00e4r Konton"
Comment 8 Alex Thurgood 2019-01-30 08:54:44 UTC
@Julien : sounds like a DUP of bug 121469 ?
Comment 9 Julien Nabet 2019-01-30 08:55:35 UTC
Indeed!

*** This bug has been marked as a duplicate of bug 121469 ***
Comment 10 Julien Nabet 2019-02-01 18:13:37 UTC
Now that tdf#121469 has been fixed, let's recycle this tracker.
Indeed an empty error message appears and console indicates this:
warn:dbaccess:18254:18254:dbaccess/source/filter/hsqldb/hsqlimport.cxx:390: Error during migration

Some debugging shows it's due to the fact that an index fails to be parsed because it contains more that one word.
SET TABLE "Vald person i formul\u00e4r Konton" INDEX'110032 0'

See https://opengrok.libreoffice.org/xref/core/dbaccess/source/filter/hsqldb/parseschema.cxx?r=3fb5f718#getIndexes
Comment 12 Julien Nabet 2019-02-01 20:17:33 UTC
I submitted a patch on gerrit: https://gerrit.libreoffice.org/#/c/67259/
Comment 13 Commit Notification 2019-02-02 06:51:35 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

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

tdf#123020: migration to Firebird fix tablename for indexes

It will be available in 6.3.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 14 Julien Nabet 2019-02-02 06:53:26 UTC
Patch on gerrit for 6.2 branch: https://gerrit.libreoffice.org/#/c/67274/
Patch on gerrit for 6.1 branch: https://gerrit.libreoffice.org/#/c/67275/
Comment 15 Julien Nabet 2019-02-03 06:27:48 UTC
Sorry, patch was wrong.
Comment 16 Commit Notification 2019-02-03 06:28:15 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

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

Revert "tdf#123020: migration to Firebird fix tablename for indexes"

It will be available in 6.3.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 17 Julien Nabet 2019-02-05 17:46:12 UTC
Just for the record, new patch waiting for review is here:
https://gerrit.libreoffice.org/#/c/67301/
Comment 18 Commit Notification 2019-02-11 19:38:53 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

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

tdf#123020: migration to Firebird fix tablename for indexes (2)

It will be available in 6.3.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 19 Julien Nabet 2019-02-19 22:43:30 UTC
The second patch was wrong too.
Comment 20 Commit Notification 2019-02-20 06:09:51 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

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

Revert "tdf#123020: migration to Firebird fix tablename for indexes (2)"

It will be available in 6.3.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 21 Thomas Nilefalk 2019-03-19 21:23:34 UTC
I'm now at 6.2.1 and an attempted migration gives a completely empty error dialog, "more" gives "SQL-status: invalid map<K, T> key"