Error on selecting conversation

[musonza]

Error on selecting conversation

[Martin-Ha]

Same error here, I get two error messages:

(1/2) PDOExceptionSQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '>'$."message_id"' = `mc_messages`.`id` where `mc_messages`.`conversation_id` in ' 
in /vendor/laravel/framework/src/Illuminate/Database/Connection.php line 319

(2/2) QueryExceptionSQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '>'$."message_id"' = `mc_messages`.`id` where `mc_messages`.`conversation_id` in ' at line 1 (SQL: select `notifications`.*, `mc_messages`.* from `mc_messages` inner join `notifications` on `notifications`.`data`->'$."message_id"' = `mc_messages`.`id` where `mc_messages`.`conversation_id` in (1) order by `mc_messages`.`id` desc)
in /vendor/laravel/framework/src/Illuminate/Database/Connection.php (line 647)

[musonza]

@lloricode What version of MariaDB do you have? The issue maybe the support for JSON columns.

[musonza]

@Martin-Ha What type of database are you using. You may also have an issue with JSON columns. You can however use an older version of the package if the issue is not resolved soon. https://github.com/musonza/chat/tree/1.0

[Martin-Ha]

@musonza Here's my config via phpmyadmin:

Server: Localhost via UNIX socket
Server type: MySQL
Server version: 5.5.47-0+deb8u1 - (Debian)
Protocol version: 10
Server charset: UTF-8 Unicode (utf8)
Web server

Apache/2.4.10 (Debian)
Database client version: libmysql - 5.5.47

[Martin-Ha]

@musonza Also, here's the config I have on development machine where it works flawlessly (homestead):

Database server

Server: Localhost via UNIX socket
Server type: MySQL
Server version: 5.7.17-0ubuntu0.16.04.2 - (Ubuntu)
Protocol version: 10
User: homestead@localhost
Server charset: UTF-8 Unicode (utf8)

nginx/1.11.9
Database client version: libmysql - mysqlnd 5.0.12-dev - 20150407 - $Id: b396954eeb2d1d9ed7902b8bae237b287f21ad9e $
PHP extension: mysqliDocumentation curlDocumentation mbstringDocumentation
PHP version: 7.1.9-1+ubuntu16.04.1+deb.sury.org+1

[tolgatasci]

look '>'$."message_id"'
problem '$."

"$message_id"

[musonza]

@Martin-Ha @tolgatasci I will do a few tests later on today and also look at lloricode/e-ttendance-laravel to recreate the issue. Otherwise if you have a fix you can create a pull request. Can you show me a snippet of code that you are running to select the conversation etc?

[Martin-Ha]

@musonza Basically this:

    $count = 0;
    
    $conversations = Chat::conversations()->for($this->user)->get();
    foreach ($conversations as $conversation)
    {
      $count += $conversation->messages()->count();
    }
    
    return $count;

[musonza]

@Martin-Ha It works on the other environment because you are using at least MySQL 5.7 which supports JSON columns but on the other environment it looks like you are using MySQL 5.5
https://dev.mysql.com/doc/refman/5.7/en/json.html

[lloricode]

@musonza thanks response, the problem is on the xampp user.. while on mine looks great no issue im using msql-workbench. the other user who create issue on my repo has a prblem they using xampp on windows.

[lloricode]

hI! @musonza, this is where the code occurred an error.

https://github.com/lloricode/e-ttendance-laravel/blob/master/app/Repositories/Backend/ChatRepository.php#L46

[musonza]

Yes the package uses laravel notifications which takes advantage of the Json columns. Can you verify the version of MySQL being used is 5.5 whereever you get the error? Xampp or workbench etc shouldn't matter

I will/may however add capability to use custom notifications like I had in the version 1 of the chat package for users with older MySQL versions

[lloricode]

sorry for a long replay, I ask the other user but did not replay, they just install work-bench without any know what is the version of mysql in their issue,

[lloricode]

hmm btw thanks :)

best regard @musonza

[lloricode]

@musonza

they just got replay, they used version 4.7.0