Mar 22, 2016

Level 1

Json column problem with Mysql5.7 and laravel5.2

How to retrieve json column data from the json column type in mysql 5.7?

Here is my Eloquent model and migration

class AdminLoginHistory extends Model
{
    protected $table = 'admin_login_history';
    protected $fillable = ['file_path', 'url', 'user_id', 'client_details'];
    protected $casts = ['client_details' => 'json'];
}

Database Migration Table
  public function up()
    {
        Schema::create('admin_login_history', function (Blueprint $table) {
            $table->increments('id');
            $table->string('url');
            $table->json('client_details');
            $table->integer('user_id')->nullable();
            $table->timestamps();
        });
    }

Iam converting  array of client_details  to json and inserting in to data base.

Now,when trying to retrieve the data iam getting error

File Path: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Database/Connection.php
Error Code: HY000, 
 Line Number: 669
Message: SQLSTATE[HY000]: General error: 2036 (SQL: select * from `admin_login_history` where `user_id` = 1 order by `created_at` desc limit 3)

Iam using laravel 5.2,php-5.6.17 and MySQL 5.7.

d3xt3r

10 years ago

Level 29

Please use three back ticks (`) to format your code. How are fetching the row and paste the complete error trace ....

veerendra

10 years ago

Level 1

my error trace

File Path: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Database/Connection.php
Error Code:HY000,  Line Number:669
Message:SQLSTATE[HY000]: General error: 2036 (SQL: select * from `admin_login_history` where `user_id` = 1 order by `created_at` desc limit 3)
                                                                                                                            Trace

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Database/Connection.php
Line:629,  Function:runQueryCallback,  Class:Illuminate\Database\Connection,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Database/Connection.php
Line:342,  Function:run,  Class:Illuminate\Database\Connection,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php
Line:1461,  Function:select,  Class:Illuminate\Database\Connection,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php
Line:1447,  Function:runSelect,  Class:Illuminate\Database\Query\Builder,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php
Line:569,  Function:get,  Class:Illuminate\Database\Query\Builder,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php
Line:303,  Function:getModels,  Class:Illuminate\Database\Eloquent\Builder,

File: /home/storage/elivio/InvizoAdmin/app/Http/Controllers/HomeController.php
Line:38,  Function:get,  Class:Illuminate\Database\Eloquent\Builder,

File: - - -
Line:- - -,  Function:profile,  Class:App\Http\Controllers\HomeController,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Routing/Controller.php
Line:80,  Function:call_user_func_array,  Class:----,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php
Line:146,  Function:callAction,  Class:Illuminate\Routing\Controller,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php
Line:94,  Function:call,  Class:Illuminate\Routing\ControllerDispatcher,

File: - - -
Line:- - -,  Function:Illuminate\Routing\{closure},  Class:Illuminate\Routing\ControllerDispatcher,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php
Line:52,  Function:call_user_func,  Class:----,

File: /home/storage/elivio/InvizoAdmin/app/Http/Middleware/Authenticate.php
Line:44,  Function:Illuminate\Routing\{closure},  Class:Illuminate\Routing\Pipeline,

File: - - -
Line:- - -,  Function:handle,  Class:App\Http\Middleware\Authenticate,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php
Line:124,  Function:call_user_func_array,  Class:----,

File: - - -
Line:- - -,  Function:Illuminate\Pipeline\{closure},  Class:Illuminate\Pipeline\Pipeline,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php
Line:32,  Function:call_user_func,  Class:----,

File: - - -
Line:- - -,  Function:Illuminate\Routing\{closure},  Class:Illuminate\Routing\Pipeline,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php
Line:103,  Function:call_user_func,  Class:----,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php
Line:96,  Function:then,  Class:Illuminate\Pipeline\Pipeline,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php
Line:54,  Function:callWithinStack,  Class:Illuminate\Routing\ControllerDispatcher,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Routing/Route.php
Line:174,  Function:dispatch,  Class:Illuminate\Routing\ControllerDispatcher,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Routing/Route.php
Line:140,  Function:runController,  Class:Illuminate\Routing\Route,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Routing/Router.php
Line:724,  Function:run,  Class:Illuminate\Routing\Route,

File: - - -
Line:- - -,  Function:Illuminate\Routing\{closure},  Class:Illuminate\Routing\Router,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php
Line:52,  Function:call_user_func,  Class:----,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/View/Middleware/ShareErrorsFromSession.php
Line:49,  Function:Illuminate\Routing\{closure},  Class:Illuminate\Routing\Pipeline,

File: - - -
Line:- - -,  Function:handle,  Class:Illuminate\View\Middleware\ShareErrorsFromSession,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php
Line:124,  Function:call_user_func_array,  Class:----,

File: - - -
Line:- - -,  Function:Illuminate\Pipeline\{closure},  Class:Illuminate\Pipeline\Pipeline,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php
Line:32,  Function:call_user_func,  Class:----,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Session/Middleware/StartSession.php
Line:62,  Function:Illuminate\Routing\{closure},  Class:Illuminate\Routing\Pipeline,

File: - - -
Line:- - -,  Function:handle,  Class:Illuminate\Session\Middleware\StartSession,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php
Line:124,  Function:call_user_func_array,  Class:----,

File: - - -
Line:- - -,  Function:Illuminate\Pipeline\{closure},  Class:Illuminate\Pipeline\Pipeline,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php
Line:32,  Function:call_user_func,  Class:----,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/AddQueuedCookiesToResponse.php
Line:37,  Function:Illuminate\Routing\{closure},  Class:Illuminate\Routing\Pipeline,

File: - - -
Line:- - -,  Function:handle,  Class:Illuminate\Cookie\Middleware\AddQueuedCookiesToResponse,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php
Line:124,  Function:call_user_func_array,  Class:----,

File: - - -
Line:- - -,  Function:Illuminate\Pipeline\{closure},  Class:Illuminate\Pipeline\Pipeline,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php
Line:32,  Function:call_user_func,  Class:----,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/EncryptCookies.php
Line:59,  Function:Illuminate\Routing\{closure},  Class:Illuminate\Routing\Pipeline,

File: - - -
Line:- - -,  Function:handle,  Class:Illuminate\Cookie\Middleware\EncryptCookies,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php
Line:124,  Function:call_user_func_array,  Class:----,

File: - - -
Line:- - -,  Function:Illuminate\Pipeline\{closure},  Class:Illuminate\Pipeline\Pipeline,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php
Line:32,  Function:call_user_func,  Class:----,

File: - - -
Line:- - -,  Function:Illuminate\Routing\{closure},  Class:Illuminate\Routing\Pipeline,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php
Line:103,  Function:call_user_func,  Class:----,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Routing/Router.php
Line:726,  Function:then,  Class:Illuminate\Pipeline\Pipeline,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Routing/Router.php
Line:699,  Function:runRouteWithinStack,  Class:Illuminate\Routing\Router,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Routing/Router.php
Line:675,  Function:dispatchToRoute,  Class:Illuminate\Routing\Router,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php
Line:246,  Function:dispatch,  Class:Illuminate\Routing\Router,

File: - - -
Line:- - -,  Function:Illuminate\Foundation\Http\{closure},  Class:Illuminate\Foundation\Http\Kernel,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php
Line:52,  Function:call_user_func,  Class:----,

File: /home/storage/elivio/InvizoAdmin/vendor/barryvdh/laravel-debugbar/src/Middleware/Debugbar.php
Line:49,  Function:Illuminate\Routing\{closure},  Class:Illuminate\Routing\Pipeline,

File: - - -
Line:- - -,  Function:handle,  Class:Barryvdh\Debugbar\Middleware\Debugbar,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php
Line:124,  Function:call_user_func_array,  Class:----,

File: - - -
Line:- - -,  Function:Illuminate\Pipeline\{closure},  Class:Illuminate\Pipeline\Pipeline,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php
Line:32,  Function:call_user_func,  Class:----,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/CheckForMaintenanceMode.php
Line:44,  Function:Illuminate\Routing\{closure},  Class:Illuminate\Routing\Pipeline,

File: - - -
Line:- - -,  Function:handle,  Class:Illuminate\Foundation\Http\Middleware\CheckForMaintenanceMode,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php
Line:124,  Function:call_user_func_array,  Class:----,

File: - - -
Line:- - -,  Function:Illuminate\Pipeline\{closure},  Class:Illuminate\Pipeline\Pipeline,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php
Line:32,  Function:call_user_func,  Class:----,

File: - - -
Line:- - -,  Function:Illuminate\Routing\{closure},  Class:Illuminate\Routing\Pipeline,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php
Line:103,  Function:call_user_func,  Class:----,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php
Line:132,  Function:then,  Class:Illuminate\Pipeline\Pipeline,

File: /home/storage/elivio/InvizoAdmin/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php
Line:99,  Function:sendRequestThroughRouter,  Class:Illuminate\Foundation\Http\Kernel,

File: /home/storage/elivio/InvizoAdmin/public/index.php
Line:54,  Function:handle,  Class:Illuminate\Foundation\Http\Kernel,

File: /home/storage/elivio/InvizoAdmin/server.php
Line:21,  Function:require_once,  Class:----,

d3xt3r

10 years ago

Level 29

try running the query directly in workbench/phpmyadmin and see what error you get ...

select * from `admin_login_history` where `user_id` = 1 order by `created_at` desc limit 3

veerendra

10 years ago

Level 1

The query is executing successfully in phpmyadmin.But when using in laravel 5.2 getting error.

$values=DB::table('admin_login_history')->where('user_id',1)->orderBy('created_at','desc')->take(3)->get();

The above query gets error when retrieving the values from table.The table contains one json column which is newly used in mysql5.7 and laravel 5.2.

But when querying the results without selecting the json column, the query retrieving the results.the problem is with json column.

example:
$values=DB::table('admin_login_history')->select('id')->where('user_id',1)->orderBy('created_at','desc')->take(3)->get();

This executes successfully and retrieving results.

The main problem is with json column type.iam using laravel 5.2 and mysql5.7

joseph.d

10 years ago

Level 8

Did you managed to solve this @veerendra ? I am also having the same issue.

veerendra

10 years ago

Level 1

I managed in this way

AdminLoginHistory::select(\DB::raw("CAST(client_details as CHAR) as client"), 'created_at', 'url')->orderBy('id', 'desc')->paginate(5);

clara

9 years ago

Level 8

Same problem over here.

Using: Laravel 5.2 | PHP 7.0.10 | MySql 5.7.14 | CentOS 7

I resolved it by following these steps:

sudo yum remove php70w-mysql
sudo yum install php70w-mysqlnd
sudo service httpd restart

Looks like the issue is related to this bug: https://bugs.php.net/bug.php?id=70384

I think it must have been fixed in the library mysqlnd but not the libmysqlclient

See here for more information about the difference: http://php.net/manual/en/mysqlinfo.library.choosing.php

4 likes

gibex

9 years ago

Level 2

My case: Ubuntu | Laravel 5.2 | php 5.6.23|Mysql 5.7

Solution (native driver instead of default php5-mysql)

apt-get install php5-mysqlnd

Hope this helps

1 like

lvstross

8 years ago

Level 1

To add on to @jeramy , (which was super helpful btw) those who are running nginx and not apache, there is no need to restart httpd since you are not using apache. Just restart php-fpm instead.

~ sudo service php-fpm restart

or one of the following from this link.

https://www.cyberciti.biz/faq/unix-linux-restart-php-service-command/

clara

8 years ago

Level 8

LOL

I've just spent the last three hours trying to figure out this problem again after upgrading to php72w.

Thank you past me, your solution works perfectly :')

1 like

felixpenrose

7 years ago

Level 3

@jeramy you're a life saver - don't know how long I'd have been searching to find this obscure fix! Thanks

Please or to participate in this conversation.