Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[Bug]: Table Input Fails for MySQL datetime field #3759

Closed
beccon4 opened this issue Mar 25, 2024 · 3 comments
Closed

[Bug]: Table Input Fails for MySQL datetime field #3759

beccon4 opened this issue Mar 25, 2024 · 3 comments
Assignees
@hansva
Labels
bug Database Hop Gui Hop Run P1 Critical Issue
Milestone
2.11

Comments

@beccon4
Copy link

beccon4 commented Mar 25, 2024

Apache Hop version?

2.8

Java version?

"11.0.22" 2024-01-16

Operating system

Linux

What happened?

Reading data from a MySQL/MariaDB table containing a datetime field fails with the following message for every row read:

2024/03/25 17:26:50 - Row Preview - ERROR: org.apache.hop.core.exception.HopValueException: 
2024/03/25 17:26:50 - Row Preview - zeit_d None : Unknown type 0 specified.
2024/03/25 17:26:50 - Row Preview - 
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.core.row.value.ValueMetaBase.getString(ValueMetaBase.java:2133)
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.ui.core.dialog.PreviewRowsDialog.getDataForRow(PreviewRowsDialog.java:373)
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.ui.core.dialog.PreviewRowsDialog.lambda$getData$7(PreviewRowsDialog.java:342)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.RunnableLock.run(RunnableLock.java:40)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.Synchronizer.runAsyncMessages(Synchronizer.java:132)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.Display.runAsyncMessages(Display.java:5040)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:4520)
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.ui.core.dialog.BaseDialog.defaultShellHandling(BaseDialog.java:519)
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.ui.core.dialog.PreviewRowsDialog.open(PreviewRowsDialog.java:244)
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.pipeline.transforms.tableinput.TableInputDialog.preview(TableInputDialog.java:574)
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.pipeline.transforms.tableinput.TableInputDialog.lambda$open$3(TableInputDialog.java:327)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:89)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.Display.sendEvent(Display.java:5855)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1529)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:5065)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:4517)
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.ui.core.dialog.BaseDialog.defaultShellHandling(BaseDialog.java:519)
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.pipeline.transforms.tableinput.TableInputDialog.open(TableInputDialog.java:336)
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.ui.hopgui.file.pipeline.delegates.HopGuiPipelineTransformDelegate.editTransform(HopGuiPipelineTransformDelegate.java:174)
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.ui.hopgui.file.pipeline.HopGuiPipelineGraph.editTransform(HopGuiPipelineGraph.java:3400)
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.ui.hopgui.file.pipeline.HopGuiPipelineGraph.mouseDoubleClick(HopGuiPipelineGraph.java:597)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:200)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:89)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.Display.sendEvent(Display.java:5855)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1529)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:5065)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:4517)
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.ui.hopgui.HopGui.open(HopGui.java:473)
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.ui.hopgui.HopGui.main(HopGui.java:351)
2024/03/25 17:26:50 - Row Preview - ERROR: org.apache.hop.core.exception.HopValueException: 
2024/03/25 17:26:50 - Row Preview - zeit_d None : Unknown type 0 specified.
2024/03/25 17:26:50 - Row Preview - 
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.core.row.value.ValueMetaBase.getString(ValueMetaBase.java:2133)
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.ui.core.dialog.PreviewRowsDialog.getDataForRow(PreviewRowsDialog.java:373)
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.ui.core.dialog.PreviewRowsDialog.lambda$getData$7(PreviewRowsDialog.java:342)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.RunnableLock.run(RunnableLock.java:40)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.Synchronizer.runAsyncMessages(Synchronizer.java:132)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.Display.runAsyncMessages(Display.java:5040)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:4520)
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.ui.core.dialog.BaseDialog.defaultShellHandling(BaseDialog.java:519)
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.ui.core.dialog.PreviewRowsDialog.open(PreviewRowsDialog.java:244)
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.pipeline.transforms.tableinput.TableInputDialog.preview(TableInputDialog.java:574)
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.pipeline.transforms.tableinput.TableInputDialog.lambda$open$3(TableInputDialog.java:327)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:89)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.Display.sendEvent(Display.java:5855)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1529)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:5065)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:4517)
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.ui.core.dialog.BaseDialog.defaultShellHandling(BaseDialog.java:519)
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.pipeline.transforms.tableinput.TableInputDialog.open(TableInputDialog.java:336)
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.ui.hopgui.file.pipeline.delegates.HopGuiPipelineTransformDelegate.editTransform(HopGuiPipelineTransformDelegate.java:174)
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.ui.hopgui.file.pipeline.HopGuiPipelineGraph.editTransform(HopGuiPipelineGraph.java:3400)
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.ui.hopgui.file.pipeline.HopGuiPipelineGraph.mouseDoubleClick(HopGuiPipelineGraph.java:597)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:200)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:89)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.Display.sendEvent(Display.java:5855)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1529)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:5065)
2024/03/25 17:26:50 - Row Preview - 	at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:4517)
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.ui.hopgui.HopGui.open(HopGui.java:473)
2024/03/25 17:26:50 - Row Preview - 	at org.apache.hop.ui.hopgui.HopGui.main(HopGui.java:351)
2024/03/25 17:26:50 - Row Preview - ERROR: org.apache.hop.core.exception.HopValueException: 
2024/03/25 17:26:50 - Row Preview - zeit_d None : Unknown type 0 specified.
2024/03/25 17:26:50 - Row Preview -

In the result set all rows have values in that field.

Table Definition from database schema:

| i_log | CREATE TABLE `i_log` (
  `logid_i` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `saveid_i` int(10) unsigned NOT NULL DEFAULT '0',
  `bewid_i` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `userid_i` smallint(5) unsigned NOT NULL DEFAULT '0',
  `actid_i` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `tabid_i` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `zeit_d` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `prkey_i` int(10) unsigned NOT NULL DEFAULT '0',
  `userid_i_portal` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`logid_i`),
  KEY `bewid_i` (`bewid_i`),
  KEY `tabid_i` (`tabid_i`),
  KEY `prkey_i` (`prkey_i`),
  KEY `userid_i` (`userid_i`),
  KEY `userid_i_portal` (`userid_i_portal`),
  KEY `action` (`tabid_i`,`actid_i`),
  KEY `saveid_i` (`saveid_i`),
  KEY `zeit_d` (`zeit_d`),
  KEY `tabid_i_2` (`tabid_i`,`actid_i`,`bewid_i`,`prkey_i`),
  KEY `actid_i` (`actid_i`,`tabid_i`,`bewid_i`),
  KEY `tabid_i_3` (`tabid_i`,`actid_i`,`bewid_i`,`zeit_d`),
  KEY `tabid_i_4` (`tabid_i`,`bewid_i`),
  KEY `zeit_d_2` (`prkey_i`,`zeit_d`,`userid_i`,`tabid_i`,`actid_i`),
  KEY `actid_i_2` (`actid_i`,`tabid_i`,`prkey_i`),
  KEY `bewid_i_2` (`bewid_i`,`tabid_i`),
  KEY `tabid_i_5` (`tabid_i`,`zeit_d`,`bewid_i`,`saveid_i`)
) ENGINE=InnoDB AUTO_INCREMENT=164262970 DEFAULT CHARSET=latin1 PACK_KEYS=1 |

What's wrong?

Issue Priority

Priority: 1

Issue Component

Component: Database, Component: Hop Gui, Component: Hop Run
@beccon4
Copy link
Author

beccon4 commented Mar 25, 2024

Further analysis shows that timestamp values cause the same errors

2024/03/25 18:26:09 - Row Preview - ERROR: org.apache.hop.core.exception.HopValueException: 
2024/03/25 18:26:09 - Row Preview - create_time None : Unknown type 0 specified.
2024/03/25 18:26:09 - Row Preview - 
2024/03/25 18:26:09 - Row Preview - 	at org.apache.hop.core.row.value.ValueMetaBase.getString(ValueMetaBase.java:2133)
2024/03/25 18:26:09 - Row Preview - 	at org.apache.hop.ui.core.dialog.PreviewRowsDialog.getDataForRow(PreviewRowsDialog.java:373)
2024/03/25 18:26:09 - Row Preview - 	at org.apache.hop.ui.core.dialog.PreviewRowsDialog.lambda$getData$7(PreviewRowsDialog.java:342)
2024/03/25 18:26:09 - Row Preview - 	at org.eclipse.swt.widgets.RunnableLock.run(RunnableLock.java:40)
2024/03/25 18:26:09 - Row Preview - 	at org.eclipse.swt.widgets.Synchronizer.runAsyncMessages(Synchronizer.java:132)
2024/03/25 18:26:09 - Row Preview - 	at org.eclipse.swt.widgets.Display.runAsyncMessages(Display.java:5040)
2024/03/25 18:26:09 - Row Preview - 	at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:4520)
2024/03/25 18:26:09 - Row Preview - 	at org.apache.hop.ui.core.dialog.BaseDialog.defaultShellHandling(BaseDialog.java:519)
2024/03/25 18:26:09 - Row Preview - 	at org.apache.hop.ui.core.dialog.PreviewRowsDialog.open(PreviewRowsDialog.java:244)
2024/03/25 18:26:09 - Row Preview - 	at org.apache.hop.pipeline.transforms.tableinput.TableInputDialog.preview(TableInputDialog.java:574)
2024/03/25 18:26:09 - Row Preview - 	at org.apache.hop.pipeline.transforms.tableinput.TableInputDialog.lambda$open$3(TableInputDialog.java:327)
2024/03/25 18:26:09 - Row Preview - 	at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:89)
2024/03/25 18:26:09 - Row Preview - 	at org.eclipse.swt.widgets.Display.sendEvent(Display.java:5855)
2024/03/25 18:26:09 - Row Preview - 	at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1529)
2024/03/25 18:26:09 - Row Preview - 	at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:5065)
2024/03/25 18:26:09 - Row Preview - 	at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:4517)
2024/03/25 18:26:09 - Row Preview - 	at org.apache.hop.ui.core.dialog.BaseDialog.defaultShellHandling(BaseDialog.java:519)
2024/03/25 18:26:09 - Row Preview - 	at org.apache.hop.pipeline.transforms.tableinput.TableInputDialog.open(TableInputDialog.java:336)
2024/03/25 18:26:09 - Row Preview - 	at org.apache.hop.ui.hopgui.file.pipeline.delegates.HopGuiPipelineTransformDelegate.editTransform(HopGuiPipelineTransformDelegate.java:174)
2024/03/25 18:26:09 - Row Preview - 	at org.apache.hop.ui.hopgui.file.pipeline.HopGuiPipelineGraph.editTransform(HopGuiPipelineGraph.java:3400)
2024/03/25 18:26:09 - Row Preview - 	at org.apache.hop.ui.hopgui.file.pipeline.HopGuiPipelineGraph.mouseDoubleClick(HopGuiPipelineGraph.java:597)
2024/03/25 18:26:09 - Row Preview - 	at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:200)
2024/03/25 18:26:09 - Row Preview - 	at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:89)
2024/03/25 18:26:09 - Row Preview - 	at org.eclipse.swt.widgets.Display.sendEvent(Display.java:5855)
2024/03/25 18:26:09 - Row Preview - 	at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1529)
2024/03/25 18:26:09 - Row Preview - 	at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:5065)
2024/03/25 18:26:09 - Row Preview - 	at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:4517)
2024/03/25 18:26:09 - Row Preview - 	at org.apache.hop.ui.hopgui.HopGui.open(HopGui.java:473)
2024/03/25 18:26:09 - Row Preview - 	at org.apache.hop.ui.hopgui.HopGui.main(HopGui.java:351)

... but not always. Retrieving data from a different MySQL Server works as expected (for datatime values at least)

The reason is the option "supports timestamp data type" in the "Relational Database Connection" setting, which is rather unintuitive as it deals with timestamp and we're reading datetime values from the table.

For me it is not clear what the option is for in the first place. Either MySQL/MariaDB supports these data types or not. And we're telling the "Relational Database Connection" which RDBMS we're connecting to. What would be the consequences of having this option selected and the RDBMS doesn't support timestamp?

So would you please either set this option by default or at least provide a more speaking error message. Thank you very much.

@hansva
Copy link
Contributor

hansva commented Nov 8, 2024

.take-issue

@github-actions github-actions bot added this to the 2.11 milestone Nov 8, 2024
@hansva
Copy link
Contributor

hansva commented Nov 8, 2024

Enabled the option by default on MySql and MariaDB as they support this.
The codebase has grown and this comes originally from a time when Hop (or at least its predecessor) did not support timestamp (which is date + time up to nanosecond). and we only had Dates (Date + time). There were and are databases which do not support the conversion and that's why the option is there.

I also updated the error messages to include the description for the datatype as the integer value does not mean much to the end user.

@hansva hansva closed this as completed in 088571f Nov 8, 2024
hansva added a commit that referenced this issue Nov 8, 2024
@hansva
Merge pull request #4547 from hansva/main
7b4f7a9 
couple of fixes, #3699, #3759, #4541
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@hansva hansva

Labels
bug Database Hop Gui Hop Run P1 Critical Issue
Projects
None yet
Milestone
2.11
Development

No branches or pull requests
2 participants
@hansva @beccon4