Description: The ebmedded firebird engine is configured for the TIME data type (Dialect 3 for dates) but during migration a type of DATETIME. Steps to Reproduce: 1. Download and open attched odb file 2. select Tables and when prompted to migrate select yes. 3. The migration function generates an error 'set var type fails' during data move. Actual Results: LibO displayes error message: Incorrect type for setValue Expected Results: table and data migrate. Reproducible: Always User Profile Reset: No Additional Info: do not save the file. Open it in hsql mode. delete the single record in the table. close and reopen, click on tables. Answer yes for migration and it runs to completion check the table structure, column type changed to DateTime. User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Ubuntu Chromium/65.0.3325.181 Chrome/65.0.3325.181 Safari/537.36
Created attachment 141723 [details] example odb file, single table, two columns test file.
Confirmed on ubuntu 16.04 x64 with Version: 6.1.0.0.alpha1+ Build ID: e9ac3a9c1ee7689c4d591a68250666c95632bd2a CPU threads: 2; OS: Linux 4.4; UI render: default; VCL: gtk2; TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2018-04-27_00:59:01 Locale: en-US (en_US.UTF-8); Calc: You also might get the error: firebird_sdbc error: *unsuccessful metadata update *CREATE TABLE tbl_time failed *Table tbl_time already exists caused by 'CREATE TABLE "tbl_time" ( "id" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH -1) PRIMARY KEY, "tst_data" TIME)'
(In reply to MM from comment #2) > Confirmed on ubuntu 16.04 x64 with Version: 6.1.0.0.alpha1+ > Build ID: e9ac3a9c1ee7689c4d591a68250666c95632bd2a > CPU threads: 2; OS: Linux 4.4; UI render: default; VCL: gtk2; > TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: > 2018-04-27_00:59:01 > Locale: en-US (en_US.UTF-8); Calc: > > You also might get the error: > > firebird_sdbc error: > *unsuccessful metadata update > *CREATE TABLE tbl_time failed > *Table tbl_time already exists > caused by > 'CREATE TABLE "tbl_time" ( "id" INTEGER GENERATED BY DEFAULT AS IDENTITY > (START WITH -1) PRIMARY KEY, "tst_data" TIME)' Ah - another one of those. No this is different, what happens is that during the migration SOMETIMES when you back out a copy of the firebird schema is left in the ODB, still configured to use the HSQLdb sdbc, since the hsql data schema also still in the ODB everythig seems fine till you try to auto migrate again and then you get that table exists error. A fix, is just to open the ODB as a zip file and remove the firebird schema in the database directory. As for an issue in bugzilla, I just can't yet say how to make that happen for on demand (set of steps).
I could confirm the message Incorrect type for setValue appears when importing a table with TIME and values in this field. Works well with DATE and DATETIME and values in the field. The TIME-field isn't changed to any other field here. When importing without values it is imported as TIME. I opened the tableeditor and see TIME, I could save TIME-values without any problem. Could be there is a different behavior when doing all this with LO 6.1. I created the database for this test with LO 6.0.4.1 every time before importing it. Doesn't test the side effects of LO 6.1 for HSQLDB.
ah- went through this here again and now the field is being created as TIME, both for an empty table (migration runs without error) and when an error is generated on importing the data, at which point if you save the file as firebird and reopen it the empty table is there with a time field. Changing summary, to reflect the error is only when there is data in that field.
I submitted https://gerrit.libreoffice.org/#/c/54581/ for review on gerrit.
Julien Nabet committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=cb4e2aea81b85a134d35bc54b7c49081f899bd78 tdf#117298: migration Firebird, fix time data It will be available in 6.1.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
so verified this with Version: 6.1.0.0.alpha1+ Build ID: 6e7e4d9f02f286ccb817cb2c1f54a951dcebffad CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk2; TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2018-05-21_01:55:12 Locale: en-US (en_US.UTF-8); Calc: group This is is verified in that having data in the source file no longer generates and error message. However, and this might already be a known issue (I'll check), the value in the field is altered. The test file includes data of 06:00:00 PM for the time. After migration the data is 04:47:44 AM That is quite a change. At this point should this issue be left open for the data change, or would folks prefer a separate issue for that?
(In reply to Drew Jensen from comment #8) >... > The test file includes data of 06:00:00 PM for the time. > After migration the data is 04:47:44 AM When opening the file and refuse migration, the time I got is 12AM Still, in both cases, there's indeed a difference with 04:47:44 AM >... > At this point should this issue be left open for the data change, or would > folks prefer a separate issue for that? Personally, I'd say as you want but perhaps there are rules about this.
Lionel/Tamas: after some debugging, here's a location to start: 332 case DataType::TIME: 333 { 334 sal_Int64 value = 0; 335 m_pStream->ReadInt64(value); 336 css::util::Time time((value % 1000) * 1000000, value / 1000, 0, 0, true); 337 aData.push_back(makeAny(time)); 338 } from dbaccess/source/filter/hsqldb/rowinputbinary.cxx gdbsession gives: (gdb) p value $4 = 82800000 (gdb) s com::sun::star::util::Time::Time (this=0x7fffffff0510, NanoSeconds_=@0x7fffffff0550: 0, Seconds_=@0x7fffffff04e0: 17264, Minutes_=@0x7fffffff04e8: 0, Hours_=@0x7fffffff04f0: 0, IsUTC_=@0x7fffffff04dc: 1 '\001') at /home/julien/lo/libreoffice/workdir/UnoApiHeadersTarget/offapi/normal/com/sun/star/util/Time.hpp:26 26 : NanoSeconds(NanoSeconds_) 17264 comes from wrong conversion of 82800 (82800000 / 1000) in sal_uInt16 (2^16 = 65536) Any thoughts?
(In reply to Julien Nabet from comment #9) > (In reply to Drew Jensen from comment #8) > >... > > The test file includes data of 06:00:00 PM for the time. > > After migration the data is 04:47:44 AM > When opening the file and refuse migration, the time I got is 12AM > Still, in both cases, there's indeed a difference with 04:47:44 AM > >... Ah, I think the difference between 12Am and 6PM was me attaching that file to two different issues and with different data in each. I downloaded the file from this issue for this test today. > > At this point should this issue be left open for the data change, or would > > folks prefer a separate issue for that? > Personally, I'd say as you want but perhaps there are rules about this. Just asked over on the QA irc channel, I'll go with whatever the recommend. Thanks for all your work on this.
For the second patch, I submitted https://gerrit.libreoffice.org/#/c/54646/ for review. Since Time function accepts nanosecs, just give it the value already in nanosecs.
Don't know if its a good idea to mix the bugs. I get at this moment only a difference from 1 hour between the internal HSQLDB and the migrated file to Firebird. Missing 1 hour in Firebird looks like summertime/wintertime or something else.
it is not just an hour difference. Just did a fresh test, new file with date, time and datetime fields. The dates migrate fine, no change. The time field data and the time part of the datetime field do not. They are off by an 1 hour, and the minutes and seconds have changed in the two test records. so...
Drew/Robert: A bit lost here :-( I'm in France and it's summer hour, so UTC+2. would you have some time to take a look to https://bugs.documentfoundation.org/show_bug.cgi?id=116980#c12 and give your results?
OK - I figured how to make the date change also, it is still a time problem. The change in time during migration can be large enough to flip the date. So April 1, 2010 23:30:30 will become April 2, 2010 But April 1, 2010 13:30:30 will not. The time difference interval between the input and output is the same with both records but only second rolls over to the April 2. I have both the input and output odb in a zip file and I think at this point it really is worth a separate issue. I'll wait half an hour in case anyone thinks I should just attach it here, otherwise I'll open that new issue and attach the file.
(In reply to Drew Jensen from comment #16) > ... > I'll wait half an hour in case anyone thinks I should just attach it here, > otherwise I'll open that new issue and attach the file. Please go ahead to open a brand new bugtracker. It'll be clearer indeed.
New issue https://bugs.documentfoundation.org/show_bug.cgi?id=117732