search

ciknowles / mqtt-cogs

Wordpress mqtt plugin
MIT License
22 stars 9 forks source link

mysql 8 support #16

Closed TheGer closed 2 years ago

TheGer commented 2 years ago

Hello! Do you have plans to implement mysql 8 support? I'm getting some issues with queries and blank date strings.

ciknowles commented 2 years ago

Hi,

Hadn't realised there was problem. Could you post some errors into the ticket?

Thanks

Chris

On 10/05/2022 17:48, Gerry Said wrote:

Hello! Do you have plans to implement mysql 8 support? I'm getting some issues with queries and blank date strings.

— Reply to this email directly, view it on GitHub https://github.com/ciknowles/mqtt-cogs/issues/16, or unsubscribe https://github.com/notifications/unsubscribe-auth/AD5TPI62UFPVYMRRGT2QGSDVJKHNPANCNFSM5VSGX6XQ. You are receiving this because you are subscribed to this thread.Message ID: @.***>

TheGer commented 2 years ago

Sure, it's related to empty string comparison in this set of queries. I'm attaching a partial fix that doesn't take dates into account, but I'm working on fixing it properly by setting a default value for the date.

The query SELECT topic as topic,utc as dtm,(payload) as payload from wp_s3zggf_mqttcogs_plugin_data WHERE topic LIKE '' AND ((utc>='' OR ''='') AND (utc<='' OR ''='')) AND payload IS NOT NULL order by utc DESC limit 100 returns an error due to datetime string comparison.

MqttCogs_Plugin.php.txt

TheGer commented 2 years ago

https://bugs.mysql.com/bug.php?id=93513

TheGer commented 2 years ago

AM actually thinking perhaps just removing the AND...s if there are no datetime values included would probably do the trick. Thanks for the great plugin btw

ciknowles commented 2 years ago

Hmm,

Just swapping around the values in the OR might do it? It would work in other languages as the statements after the first in the OR wouldn't be evaluated?

''='' OR utc>=''

Chris

On 10/05/2022 20:59, Gerry Said wrote:

AM actually thinking perhaps just removing the AND...s if there are no datetime values included would probably do the trick. Thanks for the great plugin btw

— Reply to this email directly, view it on GitHub https://github.com/ciknowles/mqtt-cogs/issues/16#issuecomment-1122805290, or unsubscribe https://github.com/notifications/unsubscribe-auth/AD5TPI5QPHYKJC7TXYV75DLVJK53HANCNFSM5VSGX6XQ. You are receiving this because you commented.Message ID: @.***>

TheGer commented 2 years ago

it worked! Thank you very much.