Hot answers tagged

128 votes

Reset password with MySQL

Future me, looks like you forgot your password again and locked yourself out of Craft. You also made your way back to this Stack Exchange question with @chris' perfect answer. Good! And to make your ...
  • 35.9k
98 votes

Getting this SQL Error: GROUP BY incompatible with sql_mode=only_full_group_by

MySQL 5.7.5+ changed the way GROUP BY behaved in order to be SQL99 compliant (where in previous versions it was not). There is a good overview of the changes here. Unfortunately, this affects some ...
  • 66.5k
41 votes

Getting this SQL Error: GROUP BY incompatible with sql_mode=only_full_group_by

Updated 05/03/16: I had a struggle getting this to work with Homestead 5.2. The MySQL error everyone else is seeing, as well as mcrypt error, as mycrypt is not installed by default in Vagrant box ...
  • 865
36 votes
Accepted

Reset password with MySQL

Craft uses Yii's CPasswordHelper::hashPassword which is a Blowfish hash algorithm, using PHP's crypt function with a "blowFishHashCost", which is a setting that can be found through craft()->config-...
  • 886
19 votes

Getting this SQL Error: GROUP BY incompatible with sql_mode=only_full_group_by

To fix this I have created a .my.cnf in my home-directory with the following content: [mysqld] sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,...
16 votes

Craft CLI RC1 Connectivity Issues with MAMP Pro

I gave everything a try, but the only thing that worked for me was ensuring "Allow network access to MySQL" was ticked in MAMP. Screenshot: http://c42.co/Ur30EJ
14 votes

Filtering entries based on category?

Craft has a pretty solid engine for relational queries, so unless what you want to do is totally outside the box, there's a good chance you can accomplish what you want by using the relatedTo ...
14 votes
Accepted

Craft CLI RC1 Connectivity Issues with MAMP Pro

After playing with this a little further it seems the unixSocket setting is necessary to get this working so I added: 'unixSocket' => getenv('DB_SOCKET') To config/db.php and then added: ...
  • 1,022
11 votes

Getting this SQL Error: GROUP BY incompatible with sql_mode=only_full_group_by

For UBUNTU 16.04 LTS commands: cd /etc/mysql sudo nano my.cnf add these two lines: [mysqld] sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,...
9 votes
Accepted

PHP notice unserialize(): Unexpected end of serialized data

Update: Craft 2.6.2957 added support for PHP 7.1 There's a lot going on here, but 1st thing is 1st. The vagrant box is running PHP 7.1 Craft 2 (and the current latest version of the Yii ...
  • 66.5k
8 votes

Getting this SQL Error: GROUP BY incompatible with sql_mode=only_full_group_by

We have the same problem. MySQL version (Ver 14.14 Distrib 5.7.9, for osx10.11 (x86_64) using EditLine wrapper) Had problems SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1055 ...
6 votes
Accepted

How do I build a LIKE query with DbCommand/Query Builder?

Just before posting the question, I had the good mind to read the Yii CDbCommand docs again, and I figured this out myself: $query->orWhere(array( 'like', 'somecolumn', '%foo%' ));
6 votes

Getting this SQL Error: GROUP BY incompatible with sql_mode=only_full_group_by

Here are some fairly specific instructions if you happen to be hosted on a server configured through ServerPilot. https://serverpilot.io/community/articles/how-to-disable-strict-mode-in-mysql-5-7....
6 votes

Reset password with MySQL

If you don't want to mess about trying to insert code in the Craft/Yii stack, v3 just calls password_hash(): password_hash($your_password_string, PASSWORD_DEFAULT, ['cost' => 13]); As a possibly ...
  • 161
6 votes
Accepted

MySQL Collation: utf8mb4_unicode_ci (emojis)

When you create a database in MySQL in Craft 2, you can set the default charset and collation, so that any SQL statements that get executed that don't explicitly specify what charset/collation to use, ...
  • 66.5k
6 votes

Dump SQL query in Twig template

In Craft 3 you can use getRawSql() at the end of the element query. {% set entries = craft.entries.section('blog').getRawSql() %} {{ entries }}
5 votes
Accepted

Does Craft CMS support MySQL over SSL?

Update: As of the next Craft 2.6 release (post 2.6.2945), Craft has exposed an attributes database config setting that you can pass arbitrary PDO attribute key => value pairs into. So, assuming ...
  • 66.5k
5 votes

Craft CMS with Google Cloud SQL as backend database

Craft currently won't operate properly if you have GTID enabled because GTID enforces that every table involved in a SQL statements in one transaction have to utilize the same storage engine. While ...
  • 66.5k
5 votes

Randomly getting the "Integrity constraint violation" sql error on saving entry

It's not a Craft bug. The error means that the data in your craft_relations table is out of sync with the data in the your craft_elements table. i.e. an ID exists in craft_relations.targetId that ...
  • 66.5k
5 votes
Accepted

Local DB SQL 'CDbException' Errors on Craft 2 site

this is a MySQL/i version issue on Craft 2 sites. You'll need to add this to your database config (craft/config/db.php): 'initSQLs' => array("SET SESSION sql_mode='STRICT_TRANS_TABLES,...
  • 1,731
5 votes

Is there a way to convert a Craft MySQL database to PostgreSQL?

First, I'm glad you've realized what a mistake you made. There's not a fool-proof way to do this, other than to do a fresh installation of Craft using PostgreSQL and migrate the content over. There is ...
  • 66.5k
4 votes

Getting this SQL Error: GROUP BY incompatible with sql_mode=only_full_group_by

MySQL 5.7.5+ changed the way GROUP BY behaved in order to be SQL99 compliant (where in previous versions it was not). Either modify the existing mysql queries to be compatible to the new mysql version ...
4 votes

Getting this SQL Error: GROUP BY incompatible with sql_mode=only_full_group_by

The proper fixes have already been answered here; but I'd like to note that another fix is to use MariaDB instead of MySQL. It's a drop-in replacement for it, it's more performant for a number of ...
  • 11.1k
4 votes
Accepted

Alternatives to Mamp Pro – which works with Craft CMS

I'd highly recommend you check out Laravel Valet. Here's an article on it that may be useful to you as well: Running Craft CMS 3 on Laravel Valet
  • 11.1k
3 votes

createCommand sql query not working in plugin

Thanks to @carlcs for pointers managed to get this working with the following: $criteria = craft()->elements->getCriteria(ElementType::User); $criteria->limit = NULL; $query = craft()->...
  • 2,023
3 votes

entry-overview superslow after update?

okay, guess I got it. as soon as I kick the Preparse Field-plugin, everything works smooth. but i guess it's not the plugin that causes the trouble, it's the way craft is writing its logs for ...
3 votes

Getting this SQL Error: GROUP BY incompatible with sql_mode=only_full_group_by

I faced this problem when I upgraded MySQL to version >= 5.7.5 and I'v fixed it by following these steps: SSH into your server. Add sql-mode="" to your MySQL config: echo -e '[mysqld]\nsql-mode=""' |...
3 votes
Accepted

Internal Server Error: SQLSTATE[42000] 1118 Row size too large

You're running into a MySQL/InnoDB server limitation that commonly occurs with a significant number of columns in a single table. Fortunately, there are several documented workarounds for it: https:/...
  • 66.5k
3 votes
Accepted

Tracking SQL queries from CP changes

Lots of smart people have tried to solve this problem and it's a tough one to solve, not just in Craft land but WP, and others. And because of Craft's flexible nature, a change in one table usually ...
  • 6,502
3 votes
Accepted

map values from checkbox field into category field using SQL

FeedMe plugin imports categories, so you could create an xml template file that outputs your entries and their checkbox values(separated by commas), then use FeedMe to import that file back in and map ...
  • 1,517

Only top scored, non community-wiki answers of a minimum length are eligible