db:: 4.20::SQLSTATE[42000]: Syntax error or access violation: 1286 Unknown table engine InnoDB
Widget settings form goes here在 SegmentFault，学习技能、解决问题
每个月，我们帮助 1000 万的开发者解决各种各样的技术问题。并助力他们在技术能力、职业生涯、影响力上获得提升。
标签：至少1个，最多5个
public function uploads($count)
$items = \DB::table('article')
-&select('pcode.name', \DB::raw('count(*) as upload_count'))
-&leftJoin('pcode', 'article.organization_id', '=', 'pcode.id')
['pcode.type', '=', "organization_type"],
['article.status', '=', 1]
-&groupBy('organization_id');
return $items-&paginate($count);
上面这样一段代码, 测试服务器很好, 上线后报错了.Syntax error or access violation: 1055 Error : MySQL : isn't in GROUP BY云云
后来Google了一下说是sql_mode设置导致的问题, 修改 config/database.php这个配置 strict =& false
'mysql' =& [
'driver' =& 'mysql',
'host' =& env('DB_HOST', 'localhost'),
'port' =& env('DB_PORT', '3306'),
'database' =& env('DB_DATABASE', 'forge'),
'username' =& env('DB_USERNAME', 'forge'),
'password' =& env('DB_PASSWORD', ''),
'charset' =& 'utf8',
'collation' =& 'utf8_unicode_ci',
'prefix' =& '',
'strict' =& false,
'engine' =& null,
然后试了一下 果然问题解决了 ,然后仔细查阅了一下原因:
查询mysql 1055错误码发现问题为在mysql的配置中如果设置了sql_mode包含ONLY_FULL_GROUP_BY值得话，在进行查询时需要将select的字段都包含在group by 中。即 select x,y from xxx group by x,y否则就会报错
laravel 5.3 是默认开启 mysql严格模式的.mysql在严格模式下, 并且开启了ONLY_FULL_GROUP_BY的情况下,group by 的字段没有出现在 select 的语句中会报错.关闭了严格模式就不会报错.
2 收藏&&|&&3
你可能感兴趣的文章
21 收藏，8.3k
2 收藏，1.8k
我也遇到这个坑了.谢谢你的答案!
我也遇到这个坑了.谢谢你的答案!
分享到微博？
我要该，理由是：
在 SegmentFault，学习技能、解决问题
每个月，我们帮助 1000 万的开发者解决各种各样的技术问题。并助力他们在技术能力、职业生涯、影响力上获得提升。当前位置： &
求翻译：SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL check the manual that corresponds to your MySQL server version for the right syntax to use near 'c4fb17bc500e01ca02) LIMIT 1' at line 1是什么意思？
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL check the manual that corresponds to your MySQL server version for the right syntax to use near 'c4fb17bc500e01ca02) LIMIT 1' at line 1
问题补充：
SQLSTATE [42000]：语法错误或访问冲突：1064您有一个在您的SQL语法错误，请检查您的MySQL服务器版本的手册对应正确的语法附近使用“c4fb17bc500e01ca02）LIMIT 1”1号线
正在翻译，请等待...
SQLSTATE(42000): Syntax error or access violation: 1064 You have an error in your SQL check the manual that corresponds to your MySQL server version for the right syntax to use near 'c4fb17bc500e01ca02) LIMIT 1' at line 1
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL check the manual that corresponds to your MySQL server version for the right syntax to use near 'c4fb17bc500e01ca02) LIMIT 1' at line 1
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL check the manual that corresponds to your MySQL server version for the right syntax to use near 'c4fb17bc500e01ca02) LIMIT 1' at line 1
我来回答：
参考资料：
* 验证码：
登录后回答可以获得积分奖励，并可以查看和管理所有的回答。 |
我要翻译和提问
请输入您需要翻译的文本！magento enterprise - SQL Syntax error or access violation: 1142 TRIGGER command denied to user - Magento Stack Exchange
Stack Exchange Network
Stack Exchange network consists of 172 Q&A communities including , the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.
Magento Stack Exchange is a question and answer site for users of the Magento e-Commerce platform. J it only takes a minute:
Here's how it works:
Anybody can ask a question
Anybody can answer
The best answers are voted up and rise to the top
If I want to update a configurable product I do get an error message:
SQLSTATE[42000]: Syntax error or access violation: 1142 TRIGGER command denied to user 'dbadmin_magento'@'localhost' for table 'catalog_product_entity_url_key', query was: INSERT INTO `catalog_product_entity_url_key` (`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`) VALUES (?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE `value` = VALUES(`value`)
I already checked the grants:
+------------------------------------------------------------------------------------------------------------------------+
| Grants for dbadmin_magento@localhost
+------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dbadmin_magento'@'localhost' IDENTIFIED BY PASSWORD '*4563' |
| GRANT ALL PRIVILEGES ON `dbadmin\_magento\_prod`.* TO 'dbadmin_magento'@'localhost'
+------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Does anyone has an idea how to fix it? I ran out of ideas.
5,03693265
If you created the grant recently you may not have flushed the privileges:
& FLUSH PRIVILEGES;
Otherwise, the issue here is the Magento Enterprise requires MySQL Triggers to run the indexing in the background. The triggers are created with the original user of the database and then when mysqldump is run it recreates the function with that user.
If the user does not exist, the dump can complete but will give warnings. It's only when the triggers are executed to you receive warnings of this type.
My suggestion is to drop all MySQL functions and reimport them/recreate them with the appropriate user.
32.3k570139
I learned the database user has changed and I didn't notice/check it:(
If you do not have enough permission to update the trigger/db user (or don't know how to do it), here is an easy solution:
dump database
replace the old db user with the new one (run in command line)
sed -i -- 's/olderDatabaseUser/newDatabaseUser/g' db_backup.sql
reimport the database
This command is going to replace every string "olderDatabaseUser" with "newDatabaseUser" in the entire sql dump. You can only use it if your db username is really unique and not something like "magento". Otherwise you gonna break your dump.
Your Answer
Sign up or
Sign up using Google
Sign up using Facebook
Post as a guest
Post as a guest
By posting your answer, you agree to the
Not the answer you're looking for?
Browse other questions tagged
Magento Stack Exchange works best with JavaScript enabled