Since update from self hosted (binary) v7.1.4 to v7.4 I get some SQL erros like this one
error [2022-11-01 09:03:47.248 +01:00] caller=“web/context.go:117” path=/api/v4/teams/1feds8s91if69my9gjqkdxdqde/top/channels request_id=m9h81w7pjbraj8k8un5p3ihaph ip_addr=127.0.0.1 user_id=zxhkqiim7td778hue5us5u5jua method=GET err_where=PostCountsByDuration http_code=500 error=“PostCountsByDuration: , failed to get post counts by duration: sql: Scan error on column index 1, name “duration”: converting NULL to string is unsupported”
can you please post the output of the following SQL query? I cannot find a column named duration in the current 7.4.0 schema, at least not on a fresh install so I assume that something during the DB migration happened and you’re not up2date:
mm740=# SELECT MAX(version) FROM db_migrations;
max
-----
93
(1 row)
Hi and sorry for my late reply, I was busy the last few weeks.
Before we dig deeper here, let me ask if this problem is still active and also I wanted to ask if this is just an error being logged or if this is something that you notice during working with your Mattermost installation; any implications like missing features or error messages in the GUI?
I reported the error I still have to support but no solution yet. I’m an admin so I check my logfiles and report even if I do not have problems using the application.
I don’t understand why Mattermost ist logging errors and afterwards I’m told that that they do not matter as long as all is running fine. This happens repeatedly I suggest to ffag such non-errors as ‘warnings’ instead.
Yes, flagging them as warnings or informational is definitely the way to go if these aren’t really errors, or fixing the code so that such queries (in your case) don’t get fired at all, but in order to find out if it’s save to “mute” the error or if there’s an underlying issue that needs to be fixed, some questions need to be asked - one of them is to try to find out the impact an error message has. If it’s just cosmetic or noisy, then the priority for fixing it will be lower as opposed to a message that gets logged because of a UI error or an impact in the functionality of the software, so sorry for trying to find ouf if this really impacts you.
If you already have a support case open with Mattermost I think we can stop working on this issue in this topic here then, because the official support knows better how to deal with such messages than I do here.
I appreciate your help! I’m just tired of trying to understand errors in the log when the result is endless back and forth messages with support and the final result is “Ignore that error if it does not affect your experience with Mattermost”.
Can you try to run the following query on the database, please?
select Id FROM Posts where CreateAT IS NULL OR UpdateAt IS NULL or DeleteAt IS NULL;
Does this query return any Post IDs? If so, can you please check one of them in particular to see why it has an invalid value for the dates (should be an integer and not a NULL value)?
Thanks, can you run this query please?
One of the developers assumes that this message is related to an unexpected or invalid timezone for the given user:
select Timezone from Users where id = 'zxhkqiim7td778hue5us5u5jua';
I think we need to see what location that user is passing into SQL. If you go to System Console > Database and set SQL Statement Logging to true temporarily and have the user in question load Insights we’ll get a log entry similar to this:
{"timestamp":"2022-12-01 10:18:43.255 -05:00","level":"debug","msg":"SELECT Posts.ChannelId AS channelid, DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(Posts.CreateAt / 1000), 'GMT', 'America/Toronto'),'%Y-%m-%d') AS duration, count(Posts.Id) AS postcount FROM Posts LEFT JOIN Channels ON Posts.ChannelId = Channels.Id WHERE (Posts.DeleteAt = ? AND Posts.CreateAt > ? AND Posts.Type = ? AND Channels.Id IN (?,?)) AND (JSON_EXTRACT(Posts.Props, '$.from_bot') IS NULL OR JSON_EXTRACT(Posts.Props, '$.from_bot') = 'false') AND (JSON_EXTRACT(Posts.Props, '$.from_webhook') IS NULL OR JSON_EXTRACT(Posts.Props, '$.from_webhook') = 'false') AND (JSON_EXTRACT(Posts.Props, '$.from_plugin') IS NULL OR JSON_EXTRACT(Posts.Props, '$.from_plugin') = 'false') AND (JSON_EXTRACT(Posts.Props, '$.from_oauth_app') IS NULL OR JSON_EXTRACT(Posts.Props, '$.from_oauth_app') = 'false') GROUP BY channelid, duration ORDER BY channelid, duration","caller":"sqlstore/sqlx_wrapper.go:456","duration":"3.108834ms","arg0":[0,1669352400000,"","q1bcg1yffb839nzp6qrej1ezzy","eapxhpwp9pbh8juqucmx31a13e"]}
This example is using America/Toronto as the location, which SQL recognizes. I’m wondering if there’s somehow a location string being used that’s not recognized by MySQL?
No. Thanks for trying to help!
I stopped hunting errors in the Mattermost logfile as long as users do not report problems. It is very time consuming to go back and forth with support and did not produce satisfactory solutions most of the time.
I disabled the feature.
just wanted to follow up here - not sure if it’s 100% related, but there has been an issue identified with Insights in 7.7.0 with users not having named timezones in the MySQL DB; 7.7.1 has that fixed, so maybe this also applied to this problem.
Since we already did the perceived fix to this issue in your other topic, I’m inclined to close this one here, but, just to verify that, can you please try to re-enable the Insights feature and see if this problem still persists?