I’m working on a project to pull information into one of our client databases from Piwik, which is set up in Microsoft SQL server 2012, but I’m having issues copying all the data across.
I’ve set up a MyODBC dataconnection to the MySQL piwik instance, and then configured this as a linked server in MSSQL. This has very nearly worked perfectly, and 99% of the time, the data is easily accessible. However, there is a record in there that MSSQL doesn’t like, and I’ve been struggling to find out why. If I try and select the whole table, I get the following error…
OLE DB provider “MSDASQL” for linked server “piwik_Instance1” returned message “[MySQL][ODBC 5.3(a) Driver][mysqld-5.5.41-0ubuntu0.14.04.1]Invalid time(hours) format. Use interval types instead”.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider “MSDASQL” for linked server “piwik_Instance1”.
Has anyone else had to bring Piwik MySQL data in MSSQL, and if so, what solution did you find worked?
I think I’ve got to the bottom of this issue, so posting the solution in case anyone else has the same issue.
There is a single row in my Piwik data with an invalid time, which is set as 252:49:33. This cannot be loaded into a MSSQL time field, causing the whole selection to fail. This is the only row affected in over 4m rows of data
There is a single row in my Piwik data with an invalid time, which is set as 252:49:33. This cannot be loaded into a MSSQL time field, causing the whole selection to fail. This is the only row affected in over 4m rows of data
Thanks for the report. we could prevent this issue in the future. can you please create a bug report at: https://github.com/piwik/piwik/issues/ and note there which database field had the wrong time value?