Error 500 Internal Server Error in /admin/api-admin-menu #1119

ricpelo · 2016-12-20T21:19:34Z

What steps will reproduce the problem?

$ composer create-project luyadev/luya-kickstarter:@dev
$ cd luya-kickstarter
$ cd configs
$ cp env.php.dist env.php
$ cp env-local-db.php.dist env-local-db.php
$ vi env-local-db.php
(configure DB connection for a PostgreSQL database)
$ cd ..
$ ./vendor/bin/luya migrate
$ ./vendor/bin/luya import
$ ./vendor/bin/luya setup

Configure /etc/hosts and Apache virtualhost luya.local.
Go to http://luya.local/admin and enter admin user credentials.

What is the expected result?

The admin page rendered without errors.

What do you get instead? (A Screenshot can help us a lot!)

A 500 (Internal Server Error) message.

If I go to http://luya.local/admin/api-admin-menu, I get the following XML output:

<response>
<name>Unauthorized</name>
<message>Your request was made with invalid credentials.</message>
<code>0</code>
<status>401</status>
<type>yii\web\UnauthorizedHttpException</type>
</response>

LUYA Check ouput (run this script and post the result: luyacheck.php)

1: [in_array('mod_rewrite', apache_get_modules())] true
2: [ini_get('short_open_tag')] ''
3: [ini_get('error_reporting')] '22527'
4: [phpversion()] '7.0.8-0ubuntu0.16.04.3'
5: [php_ini_loaded_file()] '/etc/php/7.0/apache2/php.ini'
6: [php_sapi_name()] 'apache2handler'

Q	A
LUYA Version	e2f7db7
PHP Version	7.0.8-0ubuntu0.16.04.3
Platform	Apache
Operating system	Ubuntu 16.04.1 LTS

The text was updated successfully, but these errors were encountered:

nadar · 2016-12-21T07:56:32Z

Could you please post the RESPONSE of the erroring xhr request => Click on the Network Tab in the Dev Tools.
When you just open the url http://luya.local/admin/api-admin-menu in your browser you must add the access token in order to see the content: http://luya.local/admin/api-admin-menu?access-token=XXX. You can find the token in the table admin_user field auth_token.

With those 2 things we can see the true response of the api and make sure its not an issue about authentication headers.

ricpelo · 2016-12-21T18:58:26Z

Hi, @nadar. I think the problem is, again, a PostgreSQL incompatibility.

Look at the Response you request me:

{"name":"Database Exception","message":"SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near \")\"\nLINE 1: ...admin_group_auth as t2 LEFT JOIN (admin_auth as t3) ON (t2.a...\n                                                             ^\nThe SQL being executed was: SELECT * FROM admin_user_group AS t1 LEFT JOIN(admin_group_auth as t2 LEFT JOIN (admin_auth as t3) ON (t2.auth_id = t3.id)) ON (t1.group_id=t2.group_id) WHERE t1.user_id=1","code":42601,"type":"yii\\db\\Exception","file":"/home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Schema.php","line":631,"stack-trace":["#0 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Command.php(915): yii\\db\\Schema->convertException(Object(PDOException), 'SELECT * FROM a...')","#1 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Command.php(362): yii\\db\\Command->queryInternal('fetchAll', NULL)","#2 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-module-admin/src/components/Auth.php(47): yii\\db\\Command->queryAll()","#3 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-module-admin/src/components/Auth.php(81): luya\\admin\\components\\Auth->getPermissionTable(1)","#4 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-module-admin/src/components/Auth.php(168): luya\\admin\\components\\Auth->getRouteTable(1, 'cmsadmin/defaul...')","#5 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-module-admin/src/components/AdminMenu.php(83): luya\\admin\\components\\Auth->matchRoute(1, 'cmsadmin/defaul...')","#6 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-module-admin/src/apis/MenuController.php(23): luya\\admin\\components\\AdminMenu->getModules()","#7 [internal function]: luya\\admin\\apis\\MenuController->actionIndex()","#8 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/InlineAction.php(55): call_user_func_array(Array, Array)","#9 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Controller.php(154): yii\\base\\InlineAction->runWithParams(Array)","#10 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Module.php(454): yii\\base\\Controller->runAction('index', Array)","#11 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/web/Application.php(100): yii\\base\\Module->runAction('admin/api-admin...', Array)","#12 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Application.php(375): yii\\web\\Application->handleRequest(Object(luya\\web\\Request))","#13 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-core/base/Boot.php(168): yii\\base\\Application->run()","#14 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-core/base/Boot.php(133): luya\\base\\Boot->applicationWeb()","#15 /home/ricardo/web/luya-kickstarter/public_html/index.php(8): luya\\base\\Boot->run()","#16 {main}"],"error-info":["42601",7,"ERROR:  syntax error at or near \")\"\nLINE 1: ...admin_group_auth as t2 LEFT JOIN (admin_auth as t3) ON (t2.a...\n                                                             ^"],"previous":{"name":"Exception","message":"SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near \")\"\nLINE 1: ...admin_group_auth as t2 LEFT JOIN (admin_auth as t3) ON (t2.a...\n                                                             ^","code":"42601","type":"PDOException","file":"/home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Command.php","line":900,"stack-trace":["#0 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Command.php(900): PDOStatement->execute()","#1 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Command.php(362): yii\\db\\Command->queryInternal('fetchAll', NULL)","#2 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-module-admin/src/components/Auth.php(47): yii\\db\\Command->queryAll()","#3 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-module-admin/src/components/Auth.php(81): luya\\admin\\components\\Auth->getPermissionTable(1)","#4 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-module-admin/src/components/Auth.php(168): luya\\admin\\components\\Auth->getRouteTable(1, 'cmsadmin/defaul...')","#5 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-module-admin/src/components/AdminMenu.php(83): luya\\admin\\components\\Auth->matchRoute(1, 'cmsadmin/defaul...')","#6 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-module-admin/src/apis/MenuController.php(23): luya\\admin\\components\\AdminMenu->getModules()","#7 [internal function]: luya\\admin\\apis\\MenuController->actionIndex()","#8 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/InlineAction.php(55): call_user_func_array(Array, Array)","#9 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Controller.php(154): yii\\base\\InlineAction->runWithParams(Array)","#10 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Module.php(454): yii\\base\\Controller->runAction('index', Array)","#11 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/web/Application.php(100): yii\\base\\Module->runAction('admin/api-admin...', Array)","#12 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Application.php(375): yii\\web\\Application->handleRequest(Object(luya\\web\\Request))","#13 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-core/base/Boot.php(168): yii\\base\\Application->run()","#14 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-core/base/Boot.php(133): luya\\base\\Boot->applicationWeb()","#15 /home/ricardo/web/luya-kickstarter/public_html/index.php(8): luya\\base\\Boot->run()","#16 {main}"]}}

When I add the access-token to the URL, I get:

http://luya.local/admin/api-admin-menu?access-token=ab90da39b698bfc85de62ce60063af396e81fadbe5f5eb12755e2ba638f471ba93uRXLqM7666ghHH3jiCaaXt8atikpG8

<response>
<name>Database Exception</name>
<message>
SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near ")" LINE 1: ...admin_group_auth as t2 LEFT JOIN (admin_auth as t3) ON (t2.a... ^ The SQL being executed was: SELECT * FROM admin_user_group AS t1 LEFT JOIN(admin_group_auth as t2 LEFT JOIN (admin_auth as t3) ON (t2.auth_id = t3.id)) ON (t1.group_id=t2.group_id) WHERE t1.user_id=1
</message>
<code>42601</code>
<type>yii\db\Exception</type>
<file>
/home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Schema.php
</file>
<line>631</line>
<stack-trace>
<item>
#0 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Command.php(915): yii\db\Schema->convertException(Object(PDOException), 'SELECT * FROM a...')
</item>
<item>
#1 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Command.php(362): yii\db\Command->queryInternal('fetchAll', NULL)
</item>
<item>
#2 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-module-admin/src/components/Auth.php(47): yii\db\Command->queryAll()
</item>
<item>
#3 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-module-admin/src/components/Auth.php(81): luya\admin\components\Auth->getPermissionTable(1)
</item>
<item>
#4 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-module-admin/src/components/Auth.php(168): luya\admin\components\Auth->getRouteTable(1, 'cmsadmin/defaul...')
</item>
<item>
#5 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-module-admin/src/components/AdminMenu.php(83): luya\admin\components\Auth->matchRoute(1, 'cmsadmin/defaul...')
</item>
<item>
#6 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-module-admin/src/apis/MenuController.php(23): luya\admin\components\AdminMenu->getModules()
</item>
<item>
#7 [internal function]: luya\admin\apis\MenuController->actionIndex()
</item>
<item>
#8 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/InlineAction.php(55): call_user_func_array(Array, Array)
</item>
<item>
#9 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Controller.php(154): yii\base\InlineAction->runWithParams(Array)
</item>
<item>
#10 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Module.php(454): yii\base\Controller->runAction('index', Array)
</item>
<item>
#11 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/web/Application.php(100): yii\base\Module->runAction('admin/api-admin...', Array)
</item>
<item>
#12 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Application.php(375): yii\web\Application->handleRequest(Object(luya\web\Request))
</item>
<item>
#13 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-core/base/Boot.php(168): yii\base\Application->run()
</item>
<item>
#14 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-core/base/Boot.php(133): luya\base\Boot->applicationWeb()
</item>
<item>
#15 /home/ricardo/web/luya-kickstarter/public_html/index.php(8): luya\base\Boot->run()
</item>
<item>#16 {main}</item>
</stack-trace>
<error-info>
<item>42601</item>
<item>7</item>
<item>
ERROR: syntax error at or near ")" LINE 1: ...admin_group_auth as t2 LEFT JOIN (admin_auth as t3) ON (t2.a... ^
</item>
</error-info>
<previous>
<name>Exception</name>
<message>
SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near ")" LINE 1: ...admin_group_auth as t2 LEFT JOIN (admin_auth as t3) ON (t2.a... ^
</message>
<code>42601</code>
<type>PDOException</type>
<file>
/home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Command.php
</file>
<line>900</line>
<stack-trace>
<item>
#0 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Command.php(900): PDOStatement->execute()
</item>
<item>
#1 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Command.php(362): yii\db\Command->queryInternal('fetchAll', NULL)
</item>
<item>
#2 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-module-admin/src/components/Auth.php(47): yii\db\Command->queryAll()
</item>
<item>
#3 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-module-admin/src/components/Auth.php(81): luya\admin\components\Auth->getPermissionTable(1)
</item>
<item>
#4 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-module-admin/src/components/Auth.php(168): luya\admin\components\Auth->getRouteTable(1, 'cmsadmin/defaul...')
</item>
<item>
#5 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-module-admin/src/components/AdminMenu.php(83): luya\admin\components\Auth->matchRoute(1, 'cmsadmin/defaul...')
</item>
<item>
#6 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-module-admin/src/apis/MenuController.php(23): luya\admin\components\AdminMenu->getModules()
</item>
<item>
#7 [internal function]: luya\admin\apis\MenuController->actionIndex()
</item>
<item>
#8 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/InlineAction.php(55): call_user_func_array(Array, Array)
</item>
<item>
#9 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Controller.php(154): yii\base\InlineAction->runWithParams(Array)
</item>
<item>
#10 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Module.php(454): yii\base\Controller->runAction('index', Array)
</item>
<item>
#11 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/web/Application.php(100): yii\base\Module->runAction('admin/api-admin...', Array)
</item>
<item>
#12 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Application.php(375): yii\web\Application->handleRequest(Object(luya\web\Request))
</item>
<item>
#13 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-core/base/Boot.php(168): yii\base\Application->run()
</item>
<item>
#14 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-core/base/Boot.php(133): luya\base\Boot->applicationWeb()
</item>
<item>
#15 /home/ricardo/web/luya-kickstarter/public_html/index.php(8): luya\base\Boot->run()
</item>
<item>#16 {main}</item>
</stack-trace>
</previous>
</response>

nadar · 2016-12-21T23:12:10Z

Yes its a problem with not compatible querys, as we can see we get the same response, so it has nothing to do with authorizations. I am going to fix that!

nadar · 2016-12-25T16:04:46Z

@ricpelo Now the fixed statements should be removed, could you please try again? Thank you for your time, i am really looking forward to see luya running on a postgre sql setup ;-)

(The Subsplit will run in 1 hour, then the changes are available)

ricpelo · 2016-12-25T18:26:57Z

@nadar, sure! I'll wait for the subsplit and I'll tell you ;).

ricpelo · 2016-12-26T15:37:30Z

Changes aren't still available... Is it normal?

nadar · 2016-12-26T15:39:23Z

just update the composer.json files as described and run composer update:

https://luya.io/guide/install#require-the-dev-master

ricpelo · 2016-12-26T15:52:43Z

Works better! But still errors:

http://luya.local/admin/api-admin-storage/data-folders:

{"name":"Database Exception","message":"SQLSTATE[42883]: Undefined function: 7 ERROR:  operator does not exist: boolean = integer\nLINE 1: SELECT MAX(id) FROM admin_storage_folder WHERE is_deleted=0\n                                                                 ^\nHINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.\nThe SQL being executed was: SELECT MAX(id) FROM admin_storage_folder WHERE is_deleted=0","code":42883,"type":"yii\\db\\Exception","file":"/home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Schema.php","line":631,"stack-trace":["#0 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Command.php(915): yii\\db\\Schema->convertException(Object(PDOException), 'SELECT MAX(id) ...')","#1 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Command.php(376): yii\\db\\Command->queryInternal('fetch', NULL)","#2 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/caching/DbDependency.php(58): yii\\db\\Command->queryOne()","#3 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/caching/Dependency.php(54): yii\\caching\\DbDependency->generateDependencyData(Object(yii\\caching\\DummyCache))","#4 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/caching/Cache.php(212): yii\\caching\\Dependency->evaluateDependency(Object(yii\\caching\\DummyCache))","#5 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-core/traits/CacheableTrait.php(98): yii\\caching\\Cache->set('storageApiDataF...', Array, 0, Object(yii\\caching\\DbDependency))","#6 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-module-admin/src/apis/StorageController.php(59): luya\\admin\\apis\\StorageController->setHasCache('storageApiDataF...', Array, Object(yii\\caching\\DbDependency), 0)","#7 [internal function]: luya\\admin\\apis\\StorageController->actionDataFolders()","#8 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/InlineAction.php(55): call_user_func_array(Array, Array)","#9 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Controller.php(154): yii\\base\\InlineAction->runWithParams(Array)","#10 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Module.php(454): yii\\base\\Controller->runAction('data-folders', Array)","#11 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/web/Application.php(100): yii\\base\\Module->runAction('admin/api-admin...', Array)","#12 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Application.php(375): yii\\web\\Application->handleRequest(Object(luya\\web\\Request))","#13 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-core/base/Boot.php(168): yii\\base\\Application->run()","#14 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-core/base/Boot.php(133): luya\\base\\Boot->applicationWeb()","#15 /home/ricardo/web/luya-kickstarter/public_html/index.php(8): luya\\base\\Boot->run()","#16 {main}"],"error-info":["42883",7,"ERROR:  operator does not exist: boolean = integer\nLINE 1: SELECT MAX(id) FROM admin_storage_folder WHERE is_deleted=0\n                                                                 ^\nHINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts."],"previous":{"name":"Exception","message":"SQLSTATE[42883]: Undefined function: 7 ERROR:  operator does not exist: boolean = integer\nLINE 1: SELECT MAX(id) FROM admin_storage_folder WHERE is_deleted=0\n                                                                 ^\nHINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.","code":"42883","type":"PDOException","file":"/home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Command.php","line":900,"stack-trace":["#0 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Command.php(900): PDOStatement->execute()","#1 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Command.php(376): yii\\db\\Command->queryInternal('fetch', NULL)","#2 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/caching/DbDependency.php(58): yii\\db\\Command->queryOne()","#3 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/caching/Dependency.php(54): yii\\caching\\DbDependency->generateDependencyData(Object(yii\\caching\\DummyCache))","#4 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/caching/Cache.php(212): yii\\caching\\Dependency->evaluateDependency(Object(yii\\caching\\DummyCache))","#5 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-core/traits/CacheableTrait.php(98): yii\\caching\\Cache->set('storageApiDataF...', Array, 0, Object(yii\\caching\\DbDependency))","#6 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-module-admin/src/apis/StorageController.php(59): luya\\admin\\apis\\StorageController->setHasCache('storageApiDataF...', Array, Object(yii\\caching\\DbDependency), 0)","#7 [internal function]: luya\\admin\\apis\\StorageController->actionDataFolders()","#8 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/InlineAction.php(55): call_user_func_array(Array, Array)","#9 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Controller.php(154): yii\\base\\InlineAction->runWithParams(Array)","#10 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Module.php(454): yii\\base\\Controller->runAction('data-folders', Array)","#11 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/web/Application.php(100): yii\\base\\Module->runAction('admin/api-admin...', Array)","#12 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Application.php(375): yii\\web\\Application->handleRequest(Object(luya\\web\\Request))","#13 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-core/base/Boot.php(168): yii\\base\\Application->run()","#14 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-core/base/Boot.php(133): luya\\base\\Boot->applicationWeb()","#15 /home/ricardo/web/luya-kickstarter/public_html/index.php(8): luya\\base\\Boot->run()","#16 {main}"]}}

http://luya.local/admin/api-admin-storage/data-files:

{"name":"Database Exception","message":"SQLSTATE[42883]: Undefined function: 7 ERROR:  operator does not exist: boolean = integer\nLINE 1: SELECT MAX(id) FROM admin_storage_file WHERE is_deleted=0\n                                                               ^\nHINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.\nThe SQL being executed was: SELECT MAX(id) FROM admin_storage_file WHERE is_deleted=0","code":42883,"type":"yii\\db\\Exception","file":"/home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Schema.php","line":631,"stack-trace":["#0 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Command.php(915): yii\\db\\Schema->convertException(Object(PDOException), 'SELECT MAX(id) ...')","#1 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Command.php(376): yii\\db\\Command->queryInternal('fetch', NULL)","#2 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/caching/DbDependency.php(58): yii\\db\\Command->queryOne()","#3 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/caching/Dependency.php(54): yii\\caching\\DbDependency->generateDependencyData(Object(yii\\caching\\DummyCache))","#4 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/caching/Cache.php(212): yii\\caching\\Dependency->evaluateDependency(Object(yii\\caching\\DummyCache))","#5 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-core/traits/CacheableTrait.php(98): yii\\caching\\Cache->set('storageApiDataF...', Array, 0, Object(yii\\caching\\DbDependency))","#6 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-module-admin/src/apis/StorageController.php(100): luya\\admin\\apis\\StorageController->setHasCache('storageApiDataF...', Array, Object(yii\\caching\\DbDependency), 0)","#7 [internal function]: luya\\admin\\apis\\StorageController->actionDataFiles()","#8 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/InlineAction.php(55): call_user_func_array(Array, Array)","#9 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Controller.php(154): yii\\base\\InlineAction->runWithParams(Array)","#10 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Module.php(454): yii\\base\\Controller->runAction('data-files', Array)","#11 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/web/Application.php(100): yii\\base\\Module->runAction('admin/api-admin...', Array)","#12 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Application.php(375): yii\\web\\Application->handleRequest(Object(luya\\web\\Request))","#13 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-core/base/Boot.php(168): yii\\base\\Application->run()","#14 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-core/base/Boot.php(133): luya\\base\\Boot->applicationWeb()","#15 /home/ricardo/web/luya-kickstarter/public_html/index.php(8): luya\\base\\Boot->run()","#16 {main}"],"error-info":["42883",7,"ERROR:  operator does not exist: boolean = integer\nLINE 1: SELECT MAX(id) FROM admin_storage_file WHERE is_deleted=0\n                                                               ^\nHINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts."],"previous":{"name":"Exception","message":"SQLSTATE[42883]: Undefined function: 7 ERROR:  operator does not exist: boolean = integer\nLINE 1: SELECT MAX(id) FROM admin_storage_file WHERE is_deleted=0\n                                                               ^\nHINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.","code":"42883","type":"PDOException","file":"/home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Command.php","line":900,"stack-trace":["#0 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Command.php(900): PDOStatement->execute()","#1 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Command.php(376): yii\\db\\Command->queryInternal('fetch', NULL)","#2 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/caching/DbDependency.php(58): yii\\db\\Command->queryOne()","#3 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/caching/Dependency.php(54): yii\\caching\\DbDependency->generateDependencyData(Object(yii\\caching\\DummyCache))","#4 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/caching/Cache.php(212): yii\\caching\\Dependency->evaluateDependency(Object(yii\\caching\\DummyCache))","#5 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-core/traits/CacheableTrait.php(98): yii\\caching\\Cache->set('storageApiDataF...', Array, 0, Object(yii\\caching\\DbDependency))","#6 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-module-admin/src/apis/StorageController.php(100): luya\\admin\\apis\\StorageController->setHasCache('storageApiDataF...', Array, Object(yii\\caching\\DbDependency), 0)","#7 [internal function]: luya\\admin\\apis\\StorageController->actionDataFiles()","#8 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/InlineAction.php(55): call_user_func_array(Array, Array)","#9 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Controller.php(154): yii\\base\\InlineAction->runWithParams(Array)","#10 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Module.php(454): yii\\base\\Controller->runAction('data-files', Array)","#11 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/web/Application.php(100): yii\\base\\Module->runAction('admin/api-admin...', Array)","#12 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Application.php(375): yii\\web\\Application->handleRequest(Object(luya\\web\\Request))","#13 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-core/base/Boot.php(168): yii\\base\\Application->run()","#14 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-core/base/Boot.php(133): luya\\base\\Boot->applicationWeb()","#15 /home/ricardo/web/luya-kickstarter/public_html/index.php(8): luya\\base\\Boot->run()","#16 {main}"]}}

nadar · 2016-12-26T17:40:43Z

Hmm ok, I know why I am going to check how to fix this, but temporary could you please just remove the caching component in your config and tell me the result?

https://github.com/luyadev/luya-kickstarter/blob/master/configs/env-local.php#L117-L119

(Just remove the whole entry)

ricpelo · 2016-12-26T17:51:03Z

After removing the cache component all seems to work OK!

nadar · 2016-12-26T17:59:40Z

Because as far as I can see, those sql depencies can not be provided with a query builder. I am going to investigate on this.

But happy to hear it seems to work with pgsql 👍

ricpelo · 2016-12-26T18:00:20Z

Great, thank you!

nadar · 2016-12-26T18:16:07Z

yiisoft/yii2#13278

ricpelo · 2016-12-26T22:40:51Z

Hi, @nadar, I've got another problem in http://luya.local/admin/api-cms-navitempageblockitem/create:

Database Exception – yii\db\Exception

SQLSTATE[42803]: Grouping error: 7 ERROR: column "cms_nav_item_page_block_item.sort_index" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...T(*) FROM "cms_nav_item_page_block_item" ORDER BY "sort_inde...
^
The SQL being executed was: SELECT COUNT(*) FROM "cms_nav_item_page_block_item" ORDER BY "sort_index"
Error Info: Array
(
    [0] => 42803
    [1] => 7
    [2] => ERROR:  column "cms_nav_item_page_block_item.sort_index" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...T(*) FROM "cms_nav_item_page_block_item" ORDER BY "sort_inde...
                                                             ^
)
↵
Caused by: PDOException

SQLSTATE[42803]: Grouping error: 7 ERROR: column "cms_nav_item_page_block_item.sort_index" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...T(*) FROM "cms_nav_item_page_block_item" ORDER BY "sort_inde...
^

in /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Command.php at line 900

nadar · 2016-12-27T10:38:09Z

Maybe try again after the subsplit (in 25min) but i think it was not the reason for your problem. Did you made different comments? Could you please post the full trace? Cause i was not able to find a groupby or counting query in the mentioned model. When does the problem appeard, when dropping a new block?

ricpelo · 2016-12-31T16:37:28Z

Hi, @nadar, sorry for the delay. The problem is still here, and yes, it appears when dropping a new block.

ricpelo · 2016-12-31T16:45:55Z

POST http://luya.local/admin/api-cms-navitempageblockitem/create 500 (Internal Server Error)
(anonymous) @ angular.js:11756
m @ angular.js:11517
g @ angular.js:11227
(anonymous) @ angular.js:15961
$eval @ angular.js:17229
$digest @ angular.js:17045
$apply @ angular.js:17337
(anonymous) @ angular.js:19166
e @ angular.js:5869
(anonymous) @ angular.js:6145

ricpelo · 2016-12-31T16:46:22Z

Database Exception – yii\db\Exception

SQLSTATE[42803]: Grouping error: 7 ERROR: column "cms_nav_item_page_block_item.sort_index" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...T(*) FROM "cms_nav_item_page_block_item" ORDER BY "sort_inde...
^
The SQL being executed was: SELECT COUNT(*) FROM "cms_nav_item_page_block_item" ORDER BY "sort_index"
Error Info: Array
(
    [0] => 42803
    [1] => 7
    [2] => ERROR:  column "cms_nav_item_page_block_item.sort_index" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...T(*) FROM "cms_nav_item_page_block_item" ORDER BY "sort_inde...
                                                             ^
)
↵
Caused by: PDOException
SQLSTATE[42803]: Grouping error: 7 ERROR: column "cms_nav_item_page_block_item.sort_index" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...T(*) FROM "cms_nav_item_page_block_item" ORDER BY "sort_inde...
^

in /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Command.php at line 900
1. in /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Schema.php at line 631
622623624625626627628629630631632633634635636637638639640 
        $exceptionClass = '\yii\db\Exception';
        foreach ($this->exceptionMap as $error => $class) {
            if (strpos($e->getMessage(), $error) !== false) {
                $exceptionClass = $class;
            }
        }
        $message = $e->getMessage()  . "\nThe SQL being executed was: $rawSql";
        $errorInfo = $e instanceof \PDOException ? $e->errorInfo : null;
        return new $exceptionClass($message, $errorInfo, (int) $e->getCode(), $e);
    }
 
    /**
     * Returns a value indicating whether a SQL statement is for read purpose.
     * @param string $sql the SQL statement
     * @return boolean whether a SQL statement is for read purpose.
     */
    public function isReadQuery($sql)
    {
2. in /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Command.php at line 915 – yii\db\Schema::convertException(PDOException, 'SELECT COUNT(*) FROM "cms_nav_it...')
3. in /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Command.php at line 388 – yii\db\Command::queryInternal('fetchColumn', 0)
4. in /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Query.php at line 396 – yii\db\Command::queryScalar()
5. in /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/ActiveQuery.php at line 334 – yii\db\Query::queryScalar('COUNT(*)', null)
6. in /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Query.php at line 303 – yii\db\ActiveQuery::queryScalar('COUNT(*)', null)
7. in /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-module-admin/src/ngrest/base/Api.php at line 65 – yii\db\Query::count()
59606162636465666768697071        if ($this->modelClass === null) {
            throw new InvalidConfigException("The property `modelClass` must be defined by the Controller.");
        }
 
        // pagination is disabled by default, lets verfy if there are more then 400 rows in the table and auto enable
        if ($this->pagination === false && $this->autoEnablePagination) {
            if ($this->model->find()->count() > 200) {
                $this->pagination = ['pageSize' => 100];
            }
        }
    }
 
    private $_model = null;
8. in /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Object.php at line 107 – luya\admin\ngrest\base\Api::init()
9. in /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Controller.php at line 84 – yii\base\Object::__construct([])
10. yii\base\Controller::__construct('api-cms-navitempageblockitem', luya\admin\Module, [])
11. in /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/di/Container.php at line 373 – ReflectionClass::newInstanceArgs(['api-cms-navitempageblockitem', luya\admin\Module, []])
12. in /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/di/Container.php at line 154 – yii\di\Container::build('luya\cms\admin\apis\NavItemPageB...', ['api-cms-navitempageblockitem', luya\admin\Module], [])
13. in /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/BaseYii.php at line 340 – yii\di\Container::get('luya\cms\admin\apis\NavItemPageB...', ['api-cms-navitempageblockitem', luya\admin\Module])
14. in /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Module.php at line 509 – yii\BaseYii::createObject('luya\cms\admin\apis\NavItemPageB...', ['api-cms-navitempageblockitem', luya\admin\Module])
15. in /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Module.php at line 514 – yii\base\Module::createController('create')
16. in /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Module.php at line 448 – yii\base\Module::createController('api-cms-navitempageblockitem/cre...')
17. in /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/web/Application.php at line 100 – yii\base\Module::runAction('admin/api-cms-navitempageblockit...', [])
18. in /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Application.php at line 375 – yii\web\Application::handleRequest(luya\web\Request)
19. in /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-core/base/Boot.php at line 169 – yii\base\Application::run()
163164165166167168169170171172173174175    public function applicationWeb()
    {
        $config = $this->getConfigArray();
        $this->includeYii();
        $this->app = new WebApplication(ArrayHelper::merge(['bootstrap' => ['luya\web\Bootstrap']], $config));
        if (!$this->mockOnly) {
            return $this->app->run();
        }
    }
 
    /**
     * Returns the path to luya core files
     *
20. in /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-core/base/Boot.php at line 134 – luya\base\Boot::applicationWeb()
128129130131132133134135136137138139140    public function run()
    {
        if ($this->getSapiName() === 'cli') {
            return $this->applicationConsole();
        }
 
        return $this->applicationWeb();
    }
 
    /**
     * Run Cli-Application based on the provided config file.
     *
     * @return string|integer
21. in /home/ricardo/web/luya-kickstarter/public_html/index.php at line 8 – luya\base\Boot::run()
2345678// composer autoload include
require(__DIR__ . '/../vendor/autoload.php');
 
// use the luya boot wrapping class
$boot = new \luya\Boot();
$boot->setYiiPath(__DIR__ . '/../vendor/yiisoft/yii2/Yii.php');
$boot->run();
 $_COOKIE = [
    '_csrf' => '88ca4b29b747dc642f8dbba72a1925f8b532ef48b30a85df48075e37d2f61426a:2:{i:0;s:5:"_csrf";i:1;s:32:"3wmka9NySubiaoQx6zd9YpPA36cgSMtm";}',
    'PHPSESSID' => '98g0p49e58fv329o0dltehc4v1',
];
Yii Framework
2016-12-31, 17:36:04

Apache/2.4.18 (Ubuntu)
Yii Framework/2.0.10

nadar · 2017-01-01T08:40:54Z

Thanks for the trace, now i can see the problem.

nadar · 2017-02-02T09:12:02Z

@ricpelo Would please so kind to test again? (Maybe after the composer update you have to run the migrate and import command as well) Thank you!

ricpelo · 2017-02-07T09:41:42Z

Hi! I have the following problem. After migrate and import I'm trying to do setup but:

~/web/luya-kickstarter ᐅ vendor/bin/luya setup 
Unknown command: setup

Did you mean "serve"?

nadar · 2017-02-07T09:42:56Z

it will be admin/setup in future, as setup is part of the admin module.

So use: ./vendor/bin/luya admin/setup

ricpelo · 2017-02-07T09:43:47Z

Oh, great! I'll do it and I'll tell you ;).

ricpelo · 2017-04-06T17:14:44Z

Any news, @nadar? It seems to be a type problem in WHERE is_deleted=0, because is_deleted is boolean and 0 is integer. Tell me if you need more info ;).

nadar · 2017-04-08T07:56:49Z

hi @ricpelo, i am little bit busy though i want to fix this 👍 could you verify this very quickly in your vendor folder, cause if this is the case i have to fix a few statements. Thanks

nadar · 2017-05-02T09:23:51Z

@ricpelo could you test this problem very quickly in your vendor folder? This would help me lot, as there are a FEW such statements.

ricpelo · 2017-05-02T09:35:35Z

Yeah, I'll try that today, sorry for the delay!

ricpelo · 2017-05-02T09:51:12Z

@nadar, I'd just edited vendor/luyadev/luya-module-admin/src/apis/StorageController.php on lines 62 & 103, replacing WHERE is_deleted=0 with WHERE NOT is_deleted and now it works great ;).

nadar · 2017-05-02T09:52:58Z

and what about WHERE is_deleted=FALSE as u suggedsted in #1119 (comment) ?

ricpelo · 2017-05-02T09:54:14Z

WHERE is_deleted = FALSE works OK, too.

nadar · 2017-05-02T09:56:11Z

Ok Thanks! I am going to change it. :-)

ricpelo · 2017-05-02T09:56:40Z

Great! ;)

nadar · 2017-05-02T09:57:53Z

Hopefully we have catched all those exceptions then.

ricpelo · 2017-05-02T09:59:49Z

Hopefully Luya is now PostgreSQL-compatible ;).

#1119

nadar · 2017-05-02T11:53:49Z

@ricpelo i did some changes, but this boolean/int validation "thing" is more complicated then i tought, then as now the models must validate boolean values as well but the yii gii model generator threats boolean as tinyint(1) (as there is no boolean in mysql), a little bit wired overall. Cause the angular does not post boolean values, it post int values as well - but lets see what happens. Let me know what happens now, maybe even make a fresh setup.

nadar · 2017-05-02T11:55:26Z

@ricpelo maybe it was also not necessary to changes this everywhere and it was only a problem of the "hardcoded" caching statement?

ricpelo · 2017-05-02T12:37:09Z

@nadar, I'll try it, but obviously the boolean = integer expression is wrong in PostgreSQL because boolean is a real data type for PostgreSQL and it doesn't coerce boolean into integer or vice versa.

ricpelo · 2017-05-02T12:45:02Z

Using HEAD seems to work OK ;).

nadar · 2017-05-02T12:51:15Z

including installation process, droping block, editing pages, working with crud? Would be great!

ricpelo · 2017-05-02T12:54:24Z

All of that seems to works as expected, but this night I'll check deeper.

ricpelo · 2017-05-03T10:17:28Z

@nadar, I'm unable to get crash the app after many use. To me, it works perfectly :).

nadar · 2017-05-03T10:19:16Z

@ricpelo Sounds great! So i am going to make a changelog entry as "PostgreSQL Compatible" and close this issue, if you have any further Problems please create another Issue. Thanks for your help.

ricpelo · 2017-05-03T10:20:11Z

@nadar Superb! :).

nadar added the question & support label Dec 21, 2016

nadar self-assigned this Dec 21, 2016

nadar added the bug label Dec 21, 2016

nadar added this to the 1.0.0 milestone Dec 21, 2016

nadar added a commit that referenced this issue Dec 25, 2016

fixed static sql statements #1119

d934ac7

nadar added a commit that referenced this issue Dec 27, 2016

fix query builder issues #1119

cce3744

nadar added a commit that referenced this issue Feb 2, 2017

fixed postgreq sql count query #1119

a865a65

nadar removed this from the 1.0.0 milestone Mar 14, 2017

nadar removed the question & support label May 2, 2017

nadar modified the milestones: 1.0.0, 1.x May 2, 2017

nadar added a commit that referenced this issue May 2, 2017

replace tinyint values with booleans on boolean defined migration fields

7564ab6

#1119

nadar added a commit that referenced this issue May 2, 2017

Due to boolean values, the validator needs to be changed. #1119

05bbe9c

nadar added architecture wip labels May 2, 2017

nadar closed this as completed in eca433a May 3, 2017

Error 500 Internal Server Error in /admin/api-admin-menu #1119

Error 500 Internal Server Error in /admin/api-admin-menu #1119

Comments

ricpelo commented Dec 20, 2016

What steps will reproduce the problem?

What is the expected result?

What do you get instead? (A Screenshot can help us a lot!)

LUYA Check ouput (run this script and post the result: luyacheck.php)

nadar commented Dec 21, 2016 • edited Loading

ricpelo commented Dec 21, 2016

nadar commented Dec 21, 2016

nadar commented Dec 25, 2016 • edited Loading

ricpelo commented Dec 25, 2016

ricpelo commented Dec 26, 2016

nadar commented Dec 26, 2016 • edited Loading

ricpelo commented Dec 26, 2016

nadar commented Dec 26, 2016

ricpelo commented Dec 26, 2016

nadar commented Dec 26, 2016

ricpelo commented Dec 26, 2016

nadar commented Dec 26, 2016

ricpelo commented Dec 26, 2016

nadar commented Dec 27, 2016

ricpelo commented Dec 31, 2016

ricpelo commented Dec 31, 2016

ricpelo commented Dec 31, 2016

nadar commented Jan 1, 2017

nadar commented Feb 2, 2017

ricpelo commented Feb 7, 2017

nadar commented Feb 7, 2017 • edited Loading

ricpelo commented Feb 7, 2017

ricpelo commented Apr 6, 2017

nadar commented Apr 8, 2017

nadar commented May 2, 2017 • edited Loading

ricpelo commented May 2, 2017

ricpelo commented May 2, 2017

nadar commented May 2, 2017 • edited Loading

ricpelo commented May 2, 2017

nadar commented May 2, 2017

ricpelo commented May 2, 2017

nadar commented May 2, 2017

ricpelo commented May 2, 2017

nadar commented May 2, 2017 • edited Loading

nadar commented May 2, 2017

ricpelo commented May 2, 2017

ricpelo commented May 2, 2017

nadar commented May 2, 2017 • edited Loading

ricpelo commented May 2, 2017

ricpelo commented May 3, 2017

nadar commented May 3, 2017

ricpelo commented May 3, 2017

nadar commented Dec 21, 2016 •

edited

Loading

nadar commented Dec 25, 2016 •

edited

Loading

nadar commented Dec 26, 2016 •

edited

Loading

nadar commented Feb 7, 2017 •

edited

Loading

nadar commented May 2, 2017 •

edited

Loading

nadar commented May 2, 2017 •

edited

Loading

nadar commented May 2, 2017 •

edited

Loading

nadar commented May 2, 2017 •

edited

Loading